Export (0) Print
Expand All

Walkthrough: Debugging a SQL CLR User-Defined Aggregate

This topic applies to:

Visual Studio Edition

Visual Basic

C#

C++

J#

Visual Web Developer

Express

No

No

No

No

No

Standard

No

No

No

No

No

Pro/Team

Yes

Yes

Yes

Yes

Yes

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.

NoteNote

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 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 below. Save the file with the name Concatenate.sql. Right-click the file name, and click Set as Default Debug Script.

  4. Place a breakpoint in Concatenate.cs inside the Accumulate method on the if statement. To do so, click in the gray 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 out different debugging features.

    1. The Accumulate method is executed once 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 gets 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 of 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 each time through the method, with an additional store name concatenated onto the end.

    6. Click the break point to remove it, and add a break point 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 used in this example.

using System;
using System.Data.Sql;
using System.Data.SqlTypes;
using System.Data.SqlServer;
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

Community Additions

ADD
Show:
© 2015 Microsoft