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.
   
   
   
   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
   

   [SqlFunction(DataAccess = DataAccessKind.Read)]
   public static string GetDataFromTable(string testPoints)
   {
       DataTable dt = new DataTable();
       dt.ReadXml(new StringReader(testPoints));
       return "Table has " + dt.Rows.Count + " Rows";
   }

   Before creating a Stored Procedure we need to deploy the assembly to the SQL Server as in the following:
   

   IF (EXISTS(select * from sys.assemblies where name = 'SQLCLR'))
   BEGIN
       -- Before drop assembly remove the references
       DROP ASSEMBLY SQLCLR
   END
   
   --<<Local folder path>>
   CREATE ASSEMBLY SQLCLR FROM 'D:\CLRTest\SQLCLR.dll' with PERMISSION_SET =UNSAFE;

   Create CLR Stored Procedure
   

   IF(EXISTS(select * from sys.objects where name = 'GetTableRowCount' and type='PC'))
       DROP PROCEDURE GetTableRowCount
   GO
   CREATE PROCEDURE [dbo].[GetTableRowCount]
   @testPoints NVARCHAR (4000), @returnString NVARCHAR (4000) OUTPUT
   AS EXTERNAL NAME [SQLCLR].[StoredProcedures].[GetTableRowCount]

   Example Code
   

   CREATE TABLE #TempTest
   (
       Id int,
       Name varchar(50)
   )
   
   INSERT INTO #TempTest VALUES (1,'Tejas'),
   (2,'Jignesh'),
   (3,'Rakesh')
   
   DECLARE @inputString NVARCHAR(MAX)
   DECLARE @returnString NVARCHAR(MAX)
   set @inputString = (SELECT * FROM #TempTest FOR XML PATH('Node'), ROOT('Root'))
   
   EXEC [dbo].[GetTableRowCount] @inputString, @returnString OUT
   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
   

   CREATE TABLE IntermediateTable
   (
       RowId UNIQUEIDENTIFIER,
       Id INT,
       Name VARCHAR(50)
   )

   CLR Procedure Code
   

   [Microsoft.SqlServer.Server.SqlProcedure]
   public static void GetTableRowCountTwo(Guid inputGuid, out string returnString)
   {
       DataSet dt = new DataSet();
       SqlConnection con = new SqlConnection("context connection=true");
       con.Open();
       SqlCommand cmd = new SqlCommand("select * from IntermediateTable where RowId = '" + inputGuid + "'", con);
       SqlDataAdapter adp = new SqlDataAdapter(cmd);
       adp.Fill(dt);
       returnString = "Table has " + dt.Tables[0].Rows.Count + " Rows";
   }

   Create CLR Stored Procedure
   

   IF(EXISTS(select * from sys.objects where name = 'GetTableRowCountTwo ' and type='PC'))
       DROP PROCEDURE GetTableRowCountTwo
   GO
   CREATE PROCEDURE [dbo].[GetTableRowCountTwo]
   @inputGuid UNIQUEIDENTIFIER, @returnString NVARCHAR (4000) OUTPUT
   AS EXTERNAL NAME [SQLCLR].[StoredProcedures].[GetTableRowCountTwo]

   Example Code
   

   DECLARE @returnString NVARCHAR(MAX)
   DECLARE @Guid UNIQUEIDENTIFIER = newid()
   INSERT INTO IntermediateTable VALUES (@Guid,1,'Tejas'),
   (@Guid,2,'Jignesh'),
   (@Guid,3,'Rakesh')
   
   EXEC [dbo].[GetTableRowCountTwo] @Guid, @returnString OUT
   PRINT @returnString
   
   -- clean up intermediate table
    Delete IntermediateTable  where  RowId = @Guid

   Output
   
   

I hope this helps!