Send DataSet/DataTable From Sql Clr Stored Procedure

In this post I will show you how to send a datatable or dataset from a sql clr procedure. Firstly create a new sql clr project. If you have no idea how to create a sql clr project then see the following post.

Creating a sql clr project

After creating sql clr project go to Add New Item and add a new stored procedure template. This will create the following code: 

public partial class StoredProcedures  
 {  
   [Microsoft.SqlServer.Server.SqlProcedure]  
   public static void SendData()  
   {      
      
   }  
 };  

Now I will write two functions SendDataSet and SendDataTable. In SendDataSet We will check whether DataSet is null or not, if dataset is not null then we will call the SendDataTable function. In SendDataTable function we will first determine the datatypes of datacolumns, for this we have a function ExtractDataTableColumnMetaData. This function will return the metadata of the given datatable. Now we will assign the metadata to the SqlDataRecord. After assigning it we will use the SqlContext.Pipe methods to Send the row. Below is the complete code.

public partial class StoredProcedures  
 {  
   [Microsoft.SqlServer.Server.SqlProcedure]  
   public static void SendData()  
   {  
     string connectionString = "Data Source=.;Initial Catalog=Northwind;Integrated Security=SSPI;";  
     string cmd = "select * from categories; select * from customers";  
     using (SqlConnection conn = new SqlConnection(connectionString))  
     {  
       SqlCommand sqlCmd = new SqlCommand(cmd, conn);  
       SqlDataAdapter adapter = new SqlDataAdapter(sqlCmd);  
   
       DataSet ds = new DataSet("Test");  
       adapter.Fill(ds);  
       SendDataSet(ds);  
     }  
   }  
   
   public static void SendDataSet(DataSet ds)  
   {  
     if (ds == null)  
     {  
       throw new ArgumentException("SendDataSet requires a non-null data set.");  
     }  
     else  
     {  
       foreach (DataTable dt in ds.Tables)  
       {  
         SendDataTable(dt);  
       }  
     }  
   }  
   
   
   public static void SendDataTable(DataTable dt)  
   {  
     bool[] coerceToString; // Do we need to coerce this column to string?  
     SqlMetaData[] metaData = ExtractDataTableColumnMetaData(dt, out coerceToString);  
   
     SqlDataRecord record = new SqlDataRecord(metaData);  
     SqlPipe pipe = SqlContext.Pipe;  
     pipe.SendResultsStart(record);  
     try  
     {  
       foreach (DataRow row in dt.Rows)  
       {  
         for (int index = 0; index < record.FieldCount; index++)  
         {  
           object value = row[index];  
           if (null != value && coerceToString[index])  
             value = value.ToString();  
           record.SetValue(index, value);  
         }  
          
         pipe.SendResultsRow(record);  
       }  
     }  
     finally  
     {  
       pipe.SendResultsEnd();  
     }  
   }  
   
   private static SqlMetaData[] ExtractDataTableColumnMetaData(DataTable dt, out bool[] coerceToString)  
   {  
     SqlMetaData[] metaDataResult = new SqlMetaData[dt.Columns.Count];  
     coerceToString = new bool[dt.Columns.Count];  
     for (int index = 0; index < dt.Columns.Count; index++)  
     {  
       DataColumn column = dt.Columns[index];  
       metaDataResult[index] = SqlMetaDataFromColumn(column, out coerceToString[index]);  
     }  
   
     return metaDataResult;  
   }  
   
   private static Exception InvalidDataTypeCode(TypeCode code)  
   {  
     return new ArgumentException("Invalid type: " + code);  
   }  
   
   private static Exception UnknownDataType(Type clrType)  
   {  
     return new ArgumentException("Unknown type: " + clrType);  
   }  
   
   private static SqlMetaData SqlMetaDataFromColumn(DataColumn column, out bool coerceToString)  
   {  
     coerceToString = false;  
     SqlMetaData smd = null;  
     Type clrType = column.DataType;  
     string name = column.ColumnName;  
     switch (Type.GetTypeCode(clrType))  
     {  
       case TypeCode.Boolean: smd = new SqlMetaData(name, SqlDbType.Bit); break;  
       case TypeCode.Byte: smd = new SqlMetaData(name, SqlDbType.TinyInt); break;  
       case TypeCode.Char: smd = new SqlMetaData(name, SqlDbType.NVarChar, 1); break;  
       case TypeCode.DateTime: smd = new SqlMetaData(name, SqlDbType.DateTime); break;  
       case TypeCode.DBNull: throw InvalidDataTypeCode(TypeCode.DBNull);  
       case TypeCode.Decimal: smd = new SqlMetaData(name, SqlDbType.Decimal, 18, 0); break;  
       case TypeCode.Double: smd = new SqlMetaData(name, SqlDbType.Float); break;  
       case TypeCode.Empty: throw InvalidDataTypeCode(TypeCode.Empty);  
       case TypeCode.Int16: smd = new SqlMetaData(name, SqlDbType.SmallInt); break;  
       case TypeCode.Int32: smd = new SqlMetaData(name, SqlDbType.Int); break;  
       case TypeCode.Int64: smd = new SqlMetaData(name, SqlDbType.BigInt); break;  
       case TypeCode.SByte: throw InvalidDataTypeCode(TypeCode.SByte);  
       case TypeCode.Single: smd = new SqlMetaData(name, SqlDbType.Real); break;  
       case TypeCode.String: smd = new SqlMetaData(name, SqlDbType.NVarChar, column.MaxLength);  
         break;  
       case TypeCode.UInt16: throw InvalidDataTypeCode(TypeCode.UInt16);  
       case TypeCode.UInt32: throw InvalidDataTypeCode(TypeCode.UInt32);  
       case TypeCode.UInt64: throw InvalidDataTypeCode(TypeCode.UInt64);  
       case TypeCode.Object:  
         smd = SqlMetaDataFromObjectColumn(name, column, clrType);  
         if (smd == null)  
         {  
           // Unknown type, try to treat it as string;  
           smd = new SqlMetaData(name, SqlDbType.NVarChar, column.MaxLength);  
           coerceToString = true;  
         }  
         break;  
   
       default: throw UnknownDataType(clrType);  
     }  
   
     return smd;  
   }  
   
   private static SqlMetaData SqlMetaDataFromObjectColumn(string name, DataColumn column, Type clrType)  
   {  
     SqlMetaData smd = null;  
     if (clrType == typeof(System.Byte[]) || clrType == typeof(SqlBinary) || clrType == typeof(SqlBytes) ||  
   clrType == typeof(System.Char[]) || clrType == typeof(SqlString) || clrType == typeof(SqlChars))  
       smd = new SqlMetaData(name, SqlDbType.VarBinary, column.MaxLength);  
     else if (clrType == typeof(System.Guid))  
       smd = new SqlMetaData(name, SqlDbType.UniqueIdentifier);  
     else if (clrType == typeof(System.Object))  
       smd = new SqlMetaData(name, SqlDbType.Variant);  
     else if (clrType == typeof(SqlBoolean))  
       smd = new SqlMetaData(name, SqlDbType.Bit);  
     else if (clrType == typeof(SqlByte))  
       smd = new SqlMetaData(name, SqlDbType.TinyInt);  
     else if (clrType == typeof(SqlDateTime))  
       smd = new SqlMetaData(name, SqlDbType.DateTime);  
     else if (clrType == typeof(SqlDouble))  
       smd = new SqlMetaData(name, SqlDbType.Float);  
     else if (clrType == typeof(SqlGuid))  
       smd = new SqlMetaData(name, SqlDbType.UniqueIdentifier);  
     else if (clrType == typeof(SqlInt16))  
       smd = new SqlMetaData(name, SqlDbType.SmallInt);  
     else if (clrType == typeof(SqlInt32))  
       smd = new SqlMetaData(name, SqlDbType.Int);  
     else if (clrType == typeof(SqlInt64))  
       smd = new SqlMetaData(name, SqlDbType.BigInt);  
     else if (clrType == typeof(SqlMoney))  
       smd = new SqlMetaData(name, SqlDbType.Money);  
     else if (clrType == typeof(SqlDecimal))  
       smd = new SqlMetaData(name, SqlDbType.Decimal, SqlDecimal.MaxPrecision, 0);  
     else if (clrType == typeof(SqlSingle))  
       smd = new SqlMetaData(name, SqlDbType.Real);  
     else if (clrType == typeof(SqlXml))  
       smd = new SqlMetaData(name, SqlDbType.Xml);  
     else  
       smd = null;  
   
     return smd;  
   }  
   
 };  

After writing the whole code, Deploy the solution.Before deploying Must remember to change the Permission Level to external or unsafe, and also remember to give trustworthy right to the database by running the following command on the sql server management studio.

alter database DataBaseName set trustworthy on  

and one more thing CLR integration must be enabled, if it is not then run this command

sp_configure 'clr enabled', 1     
 GO     
 RECONFIGURE     
 GO  

Download the whole solution from skydrive, just change the database reference. Thanks.
SendDataSet

References:
The below msdn post helps me alot
http://msdn.microsoft.com/en-us/library/ff878201.aspx

SQL CLR Table Valued Function

SQL CLR project allows the developers to write code in .Net. In this post I will create a SQL CLR function that will return a table as a result.
For those people who did not knew how to create a Sql Clr project first take a look at the following post
Creating Sql Clr Stored Procedure
After creating a SQL CLR project. From the Project Menu or Add New Item dialog box add a new class file. In the class create a function having return type IEnumerable and add the SqlFunction attribute to header of your function as shown below:

public partial class Functions  
 {  
   [Microsoft.SqlServer.Server.SqlFunction]  
   public static IEnumerable fn_getData()  
   {  
      ArrayList rowsArray = new ArrayList();  
      return rowsArray;  
   }  
 }  

Now add the FillRowMethodName and TableDefinition parameters to the SqlFunction attribute. Set FillRowMethodName = "FillRow". The FillRow method is implicity called by the SQL CLR framework each time the MoveNext() on the returned IEnumerable ( type that implements IEnumerable interface ) is called. In TableDefinition specify the columns of the table. See the below code

[Microsoft.SqlServer.Server.SqlFunction(FillRowMethodName="FillRow",TableDefinition="Name nvarchar(30), Age int, City nvarchar(30)")]  
   public static IEnumerable fn_getData()  
   {  
     ArrayList rowsArray = new ArrayList();  
     return rowsArray;  
   }  

Now we will create a function SetData in which we will fill the arraylist with some data.

 public static void SetData(ArrayList rowsArray)
     {
        object[] column = new object[3];
        column[0] = "John";
        column[1] = 18;
        column[2] = "Newyork";
        rowsArray.Add(column);
     }

After this we will finally write the FillRow method. This method will write the row by row data of ArrayList to the table. It has an object parameter which will give the row of an arrayList. The signature of FillRow method is
private static void FillRow(Object obj, out [col1_type] [column Name], ... )

In FillRow method we will store the obj in an object Array and cast the columns to their respective types as shown below:

public static void FillRow(object obj, out string Name, out int Age, out string City)  
    {  
     object[] row = (object[])obj;  
     Name = (string)row[0];  
     Age = (int)row[1];  
     City = (string)row[2];  
   }  

It gets called once each time the framework calls .MoveNext() on the underlying ArrayList method (which implements the inherited IEnumerable interface)

The complete code is below:

public partial class Functions  
 {  
   [Microsoft.SqlServer.Server.SqlFunction(FillRowMethodName="FillRow",TableDefinition="Name nvarchar(30), Age int, City nvarchar(30)")]  
   public static IEnumerable fn_getData()  
   {  
     ArrayList rowsArray = new ArrayList();  
     SetData(rowsArray);  
     return rowsArray;  
   }  
    public static void FillRow(object obj, out string Name, out int Age, out string City)  
    {  
     object[] row = (object[])obj;  
     Name = (string)row[0];  
     Age = (int)row[1];  
     City = (string)row[2];  
   }  
    public static void SetData(ArrayList rowsArray)  
    {  
     object[] column = new object[3];  
     column[0] = "John";  
     column[1] = 18;  
     column[2] = "Newyork";  
     rowsArray.Add(column);  
     object[] column1 = new object[3];  
     column1[0] = "Scott";  
     column1[1] = 25;  
     column1[2] = "Washington";  
     rowsArray.Add(column1);  
   }  
 }  

Now deploy the project and move to Sql Server. Open the database in which you have deployed the function. I have used NorthWind database. I ran this query and got the desired output

References:
http://msdn.microsoft.com/en-us/library/ms131103(v=sql.90).aspx
http://blogs.msdn.com/b/stuartpa/archive/2005/07/21/441468.aspx