Pass the Table Type Data to SQL CLR Object

Introduction

In SQL Server, we are able to create CLR database objects (functions, Stored Procedures, triggers and so on). Sometimes these CLR objects are faster than T-SQL. Generally a CLR database object can be used when we want to implement logic that is not possible with T-SQL or it can use more resources.

A Table Valued Parameter helps us to pass multiple rows of data from a client application to SQL Server without multiple round trips. Using a Table Valued Parameter we can pass multiple rows to a Stored Procedure also. But a Table Valued Parameter is not supported with CLR database objects.

Problem statement

We must pass some business calculations as multiple rows of data to a CLR database object. Table Valued Parameters are not supported in CLR database objects, so how can we pass multiple rows of data to a CLR database object?

Solution

This can be done in one of two ways. This article explains it one by one.

  1. Pass table data to CLR object using XML

    A select query returns a row set as result. A FOR XML clause helps to retrieve results of a SQL query in the form of XML. When we specify the "PATH" with a FOR XML clause, column names or alias names are treated as XPath expressions and these indicate how the column values are mapped with XML.

    Example: The following SELECT query retrieves information from a temporary table and this query is specified with a PATH mode in the FOR XML clause.

    create temptest table

    There are many ways to read XML in C# applications (using XmlDocument, DataSet.ReadXml and so on) and using these methods we can read the XML and convert it into the format we want.
    In this approach, I have combined the previously described method. From the SQL Server, I have converted a data row to XML and from the CLR function, the C# code converts the XML to an object.

    Example

    CLR Procedure Code
    1. [SqlFunction(DataAccess = DataAccessKind.Read)]  
    2. public static string GetDataFromTable(string testPoints)  
    3. {  
    4.     DataTable dt = new DataTable();  
    5.     dt.ReadXml(new StringReader(testPoints));  
    6.     return "Table has " + dt.Rows.Count + " Rows";  
    7. }  
    Before creating a Stored Procedure we need to deploy the assembly to the SQL Server as in the following:
    1. IF (EXISTS(select * from sys.assemblies where name = 'SQLCLR'))    
    2. BEGIN    
    3.     -- Before drop assembly remove the references    
    4.     DROP ASSEMBLY SQLCLR    
    5. END   
    6.   
    7. --<<Local folder path>>  
    8. CREATE ASSEMBLY SQLCLR FROM 'D:\CLRTest\SQLCLR.dll' with PERMISSION_SET =UNSAFE;  
    Create CLR Stored Procedure
    1. IF(EXISTS(select * from sys.objects where name = 'GetTableRowCount' and type='PC'))    
    2.     DROP PROCEDURE GetTableRowCount    
    3. GO    
    4. CREATE PROCEDURE [dbo].[GetTableRowCount]  
    5. @testPoints NVARCHAR (4000), @returnString NVARCHAR (4000) OUTPUT  
    6. AS EXTERNAL NAME [SQLCLR].[StoredProcedures].[GetTableRowCount]  
    Example Code
    1. CREATE TABLE #TempTest  
    2. (  
    3.     Id int,  
    4.     Name varchar(50)  
    5. )  
    6.   
    7. INSERT INTO #TempTest VALUES (1,'Tejas'),  
    8. (2,'Jignesh'),  
    9. (3,'Rakesh')  
    10.   
    11. DECLARE @inputString NVARCHAR(MAX)  
    12. DECLARE @returnString NVARCHAR(MAX)  
    13. set @inputString = (SELECT * FROM #TempTest FOR XML PATH('Node'), ROOT('Root'))  
    14.   
    15. EXEC [dbo].[GetTableRowCount] @inputString, @returnString OUT  
    16. PRINT @returnString  
    Output



  2. Pass table data to CLR object using intermediate table

    We may also be able to pass a table using an intermediate table. Here the idea is to copy all the required data to one intermediate table with a single GUID and pass this GUID to the CLR Stored Procedure. The CLR procedure retrieves the data from the database and proceeds further. Once the operation is finished, we can clean up the intermediate table.

    Example

    Intermediate table definition
    1. CREATE TABLE IntermediateTable  
    2. (  
    3.     RowId UNIQUEIDENTIFIER,  
    4.     Id INT,  
    5.     Name VARCHAR(50)  
    6. )  
    CLR Procedure Code
    1. [Microsoft.SqlServer.Server.SqlProcedure]  
    2. public static void GetTableRowCountTwo(Guid inputGuid, out string returnString)  
    3. {  
    4.     DataSet dt = new DataSet();  
    5.     SqlConnection con = new SqlConnection("context connection=true");  
    6.     con.Open();  
    7.     SqlCommand cmd = new SqlCommand("select * from IntermediateTable where RowId = '" + inputGuid + "'", con);  
    8.     SqlDataAdapter adp = new SqlDataAdapter(cmd);  
    9.     adp.Fill(dt);  
    10.     returnString = "Table has " + dt.Tables[0].Rows.Count + " Rows";  
    11. }  
    Create CLR Stored Procedure
    1. IF(EXISTS(select * from sys.objects where name = 'GetTableRowCountTwo ' and type='PC'))    
    2.     DROP PROCEDURE GetTableRowCountTwo   
    3. GO    
    4. CREATE PROCEDURE [dbo].[GetTableRowCountTwo]  
    5. @inputGuid UNIQUEIDENTIFIER, @returnString NVARCHAR (4000) OUTPUT  
    6. AS EXTERNAL NAME [SQLCLR].[StoredProcedures].[GetTableRowCountTwo]  
    Example Code
    1. DECLARE @returnString NVARCHAR(MAX)  
    2. DECLARE @Guid UNIQUEIDENTIFIER = newid()  
    3. INSERT INTO IntermediateTable VALUES (@Guid,1,'Tejas'),  
    4. (@Guid,2,'Jignesh'),  
    5. (@Guid,3,'Rakesh')  
    6.   
    7. EXEC [dbo].[GetTableRowCountTwo] @Guid, @returnString OUT  
    8. PRINT @returnString  
    9.   
    10. -- clean up intermediate table    
    11.  Delete IntermediateTable  where  RowId = @Guid  
    Output

    code output

I hope this helps!

Up Next
    Ebook Download
    View all
    Learn
    View all