Walkthrough: Debugging a SQL Server CLR Integration User-Defined Type

This topic applies to:

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

This example shows how to debug a SQL Server Common Language Run-time (SQL CLR) user-defined type. It creates a new SQL CLR type in the AdventureWorks sample database. The type is then used in a table definition, an INSERT statement, and then a SELECT statement.

If, when you try to debug a SQL CLR 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.

To debug a CLR SQL user-defined type

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

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

3. Add a script that tests the type. In Solution Explorer, right-click the TestScripts directory, click Add Test Script, and insert the code from the second Example section below. Save the file with the name Point.sql. Right-click the file name, and click Set as Default Debug Script.

4. Add breakpoints.

   1. In the Types folder in Server Explorer, open Point.

   2. Place a breakpoint inside each method so you can observe the flow of control inside the type.

5. From the Debug menu, choose Start to compile, deploy, and unit test the project. When the instruction pointer, designated by a yellow arrow, appears on the breakpoint, you are debugging your function.

6. Try out different debugging features.

   1. The Parse method is executed once for each INSERT statement in the script in Point.sql. By repeatedly clicking Step Into on the Debug menu, you can watch how the method converts a colon-delimited number pair into a Point object.

   2. In the Locals window, open the variable pt, which contains the current Point being built.

   3. In the Text Editor, double-click the pt variable to select it. Drag pt to any location on the Watch window. pt is now added to the list of watched variables, and you can observe it as the Point is built.

   4. Step through the class several times and observe the differences between the path an INSERT and a SELECT take.

   5. Press Continue again to finish debugging the function.

Example

This is the code defining the type used in this sample. This code creates a table named Points, inserts rows into it, and prints out the table contents. Note that you do not have to include the batch command GO between creating the table and accessing it. In fact Visual Studio 2005 will interpret the GO as an invalid SQL command.

using System;
using System.Data.Sql;
using System.Data.SqlTypes;
using System.Runtime.Serialization;

[Serializable, SqlUserDefinedTypeAttribute(Format.Native)]
public struct Point: INullable
{
    private bool m_isNull;
    private double m_x;
    private double m_y;

    public bool IsNull {
        get { return (m_isNull); }
    }

    public override string ToString()
    {
        if (this.IsNull) { return "NULL"; }
        else { return this.m_x + ":" + this.m_y; }
    }

    public static Point Parse(SqlString s)
    {
        if (s.IsNull) { return Null; }
        else
        {
            // Parse input string here to separate out points:
            Point pt = new Point();
            string str = Convert.ToString(s);
            string[] xy = str.Split(':');

            pt.X = Convert.ToDouble(xy[0]);
            pt.Y = Convert.ToDouble(xy[1]);
            return (pt);
        }
    }

    public static Point Null
    {
        get
        {
            Point pt = new Point();
            pt. m_isNull = true;
            return (pt);
        }
    }

    public double X
    {
        get { return (this.m_x); }
        set { m_x = value; }
    }

    public double Y
    {
        get { return (this.m_y); }
        set { m_y = value; }
    }
}

This is the test script that calls the function.

CREATE TABLE dbo.Points ( 
            ID int IDENTITY(1,1) PRIMARY KEY, 
            Pnt Point) 
INSERT INTO dbo.Points (Pnt) VALUES (CONVERT(Point, '3:4')) 
INSERT INTO dbo.Points (Pnt) VALUES (CONVERT(Point, '-1:5')) 
INSERT INTO dbo.Points (Pnt) VALUES (CAST ('1:99' AS Point)) 
SELECT ID, 
        Pnt.ToString() as StringPoint, 
        Pnt.X as X, 
        Pnt.Y as Y      
FROM dbo.Points

See Also

Tasks

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