Walkthrough: Debugging a SQL CLR User-Defined Aggregate

This topic applies to:

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

Table legend:

	Applies
	Does not apply
	Command or commands hidden by default.

This example shows how to debug a CLR SQL user-defined aggregate. It creates a new CLR SQL aggregate function named Concatenate in the AdventureWorks sample database. When this function is invoked in a SQL statement, it will concatenate together all the values for the column specified as its input parameter.

Note

The dialog boxes and menu commands you see might differ from those described in Help depending on your active settings or edition. To change your settings, choose Import and Export Settings on the Tools menu. For more information, see Visual Studio Settings.

To debug a CLR SQL aggregate function

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

2. Create a new function by using the code from the first of the following example sections and name it Concatenate.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, select Add Test Script, and insert the code from the second Example section in this walkthrough. Save the file with the name Concatenate.sql. Right-click the file name, and then click Set as Default Debug Script.

4. Place a breakpoint in Concatenate.csinside the Accumulate method on the if statement. To do this, click in the shaded left margin of the Text Editor window, and 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 the breakpoint, you are debugging your function.

5. Try different debugging features.

   1. The Accumulate method is executed one time for each row that makes up the GROUP BY clause in the script in Concatenate.sql. By repeatedly clicking Step Into from the Debug menu, you can watch how the method result is built.

   2. In the Locals window, open the variable value, which contains the current store name being processed.

   3. Click the variable this. The child node intermediateResult will be returned from this function, and it contains all the store names up to the current one concatenated together and separated by commas.

   4. In the Text editor, double-click the intermediateResult variable to select it. Drag intermediateResult to the Watch window and drop it anywhere in the window. The variable is now added to the list of watched variables.

   5. Step through the method several times. The value of intermediateResult will change every time that through the method, with an additional store name concatenated onto the end.

   6. Click the breakpoint to remove it, and add a breakpoint to the first statement inside the Terminate method. This method returns the result to the caller. To step into it, on the Debug menu, click Start. You can now step through it by clicking Step Into on the Debug menu. Stop when you hit the return statement.

   7. Click Continue again to finish debugging the function.

Example

This is the code for the aggregate function that is used in this example.

using System;
using System.Data.Sql;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.IO;
using System.Text;
[Serializable]
[SqlUserDefinedAggregate( 
    //use CLR serialization to serialize the intermediate result. 
    Format.UserDefined, 
    //Optimizer property: 
    IsInvariantToNulls=true,
    //Optimizer property: 
    IsInvariantToDuplicates=false,
    //Optimizer property: 
    IsInvariantToOrder=false,
    //Maximum size in bytes of persisted value: 
    MaxByteSize=8000)
] 
public class Concatenate: IBinarySerialize 
{ 
    /// <summary> 
    /// Variable holds intermediate result of the concatenation 
    /// </summary> 
    private StringBuilder intermediateResult; 
    /// <summary> 
    /// Initialize the internal data structures 
    /// </summary> 
    public void Init( ) 
    { 
        intermediateResult = new StringBuilder(); 
    } 
    /// <summary> 
    /// Accumulate the next value, nop if the value is null 
    /// </summary> 
    /// <param name="value"></param> 
    public void Accumulate(SqlString value) 
    { 
        if(value.IsNull) 
        { 
            return; 
        } 
        intermediateResult.Append(value.Value).Append(','); 
    } 
    /// <summary> 
    /// Merge the partially computed aggregate with this aggregate. 
    /// </summary> 
    /// <param name="other"></param> 
    public void Merge( Concatenate other) 
    { 
        intermediateResult.Append(other.intermediateResult); 
    } 
    /// <summary> 
    /// Called at end of aggregation, to return results. 
    /// </summary> 
    /// <returns></returns> 
    public SqlString Terminate() 
    { 
        string output = string.Empty; 
        //Delete the trailing comma, if any .
        if (intermediateResult != null && intermediateResult.Length > 0) 
            output = intermediateResult.ToString(0, intermediateResult.Length-1); 
        return new SqlString(output); 
    } 
    public void Read(BinaryReader r) 
    { 
        intermediateResult = new StringBuilder(r.ReadString()); 
    } 
    public void Write(BinaryWriter w) 
    { 
        w.Write(intermediateResult.ToString()); 
    } 
}

This is the test script that calls the function.

SELECT scu.SalesPersonID, dbo.Concatenate(sst.Name)
FROM Sales.Customer as scu 
INNER JOIN Sales.Store as sst
    ON scu.CustomerID    = sst.CustomerID
INNER JOIN Sales.SalesPerson as spr
    ON scu.SalesPersonID = spr.SalesPersonID
WHERE    scu.SalesPersonID = 283
GROUP BY scu.SalesPersonID

See Also

Tasks

How to: Create and Run a CLR SQL Server Aggregate

Other Resources

SQL CLR Database Debugging