Walkthrough: Debug a SQL Server CLR Integration User-Defined Table-Valued Function

This topic applies to:

Edition	Visual Basic	C#	C++	Web Developer
Express
Standard
Pro and Team

This example shows how to debug an SQL Server common language run-time (SQL Server CLR) integration User Defined Table-Valued Function (UDF).

If, when you try to debug an SQL Server CLR integration object, the message "Canceled by user" appears, you must manually configure both the computer on which you are running Visual Studio as well as the computer that is running SQL Server. For more information, see How to: Configure Your Computers to Enable Transact-SQL and SQL Server CLR Integration Debugging.

Note

Your computer might show different names or locations for some of the Visual Studio user interface elements in the following instructions. The Visual Studio edition that you have and the settings that you use determine these elements. For more information, see Visual Studio Settings.

To debug an SQL Server CLR integration user-defined table-valued function

1. In a new SQL Server CLR integration project, establish a connection to a database. For more information, see How to: Connecting to a Database.

2. Create a new function using the code from the first example section below and name it TableOfPrimes.cs. For more information, see How to: Develop with the SQL Server Project Type.

3. Add a script that tests the function by including it in a SELECT statement. In Solution Explorer, right-click the TestScripts directory, click Add Test Script, and insert the code from the second of the following Example sections. Save the file with the name TestPrime.sql. Right-click the file name, and click Set as Default Debug Script.

4. Set breakpoints in TableOfPrimes.cs, and then on the Debug menu, click Start to compile, deploy, and unit test the project. When the instruction pointer, designated by a yellow arrow, appears on a breakpoint, you are debugging the SQL Server CLR integration code.

5. Try out different debugging features.

   1. On the Debug menu, click Step Into repeatedly to observe line-by-line execution of the function.

   2. As you step through the function, you can use the Locals and Watch windows to observe the values of different members.

   3. Click Continue again to finish debugging the function.

   4. In the Output window, select Database Output from the Show output from drop-down list, and you can observe the results of executing the two queries in the TestPrimes.sql script.

Example

This is the code that reads the Event Log.

using System;
using System.Data;
using System.Data.Sql;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Collections;

public partial class UserDefinedFunctions
{
    struct primeIndex
    {
        public int n;
        public int p;
        public primeIndex(int n, int p)  
        { 
            this.n = n; this.p = p;
        }
    }

    static bool isPrime(int p)
    {
        if (p < 2) return false;
        if (p == 2) return true;
        if (p % 2 == 0) return false;
        for (int d = 3; d * d <= p; d+=2)
        {
            if (p % d == 0) return false;
        }
        return true;
    }

    static int nextPrime(int p)
    {
        int result = p + 1;
        while (!isPrime(result)) result++;
        return result;
    }

    [SqlFunction(FillRowMethodName = "Fill", TableDefinition = "n int,p int,est float")]
    public static IEnumerable TableOfPrimes(int n)
    {
        int p = 1;
        for (int i = 1; i <= n; i++)
        {
            p = nextPrime(p);
            yield return new primeIndex(i, p);
        }
    }

    private static void Fill(object source, out int n, out int p, out SqlDouble est)
    {
        primeIndex pi = (primeIndex)source;
        n = pi.n;
        p = pi.p;
        if (n <5)
            est = SqlDouble.Null;
        else
        {
            double log = Math.Log(n);
            double loglog = Math.Log(log);
            est = n * (log + loglog - 1 + loglog / log - 2 / log); 
        }
    }
}

This is the test script that calls the function.

SELECT n,p,est FROM dbo.TableOfPrimes(50)

SELECT TOP 10 n, p, est, est/p AS factor FROM dbo.TableOfPrimes(500) ORDER BY factor DESC

SELECT TOP 10 n, p, est, est/p AS factor FROM dbo.TableOfPrimes(1000) WHERE n>500 ORDER BY factor DESC

See Also

Tasks

How to: Create and Run a SQL Server User-Defined Function by using Common Language Run-time Integration