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

Creating a SQL CLR Stored Procedure using VS 2010 and Sql Server 2008

SQL CLR give us the ability to write code in .Net Framework. In Visual Studio 2005,8 and 10 there is a database project namely SQL CLR Database project as shown in the below image.

Before creating the project please change the .net framework version to .net Framework 3.5 from the drop down menu.
After choosing the template you have to choose a database in which you will deploy the solution. The following window will open when you press OK button.

We will use Northwind database. Press the OK button. The following window will open

To enable the debugging press  the Yes button otherwise No. Now the project is created. The project consists of following folders and files.

Now from the project menu select stored procedure to add a code file.

Add the stored procedure and give a name to it. We gave a name sp_WriteFile to it. We use the File Class method WriteAllText to write a file to the Sytem. The complete code consists of only one line as shown in the below image.

The sp has two parameters FileName and FileContent. Now go to project properties select Database tab and set the Permission Level to external or Unsafe. By default permission level is set to Safe.

We changed the permission level because we have to access the File System. In Safe permission level we can 't access the file system.
Read more about the permission levels at MSDN

Now deploy the solution.

When you deploy the project the following error may appears.

//C:\Code\CLRProject\CLRProject\bin\Debug\CLRProject.sql(106-106): Deploy error SQL01268: .Net SqlClient Data Provider: Msg 10327, Level 14, State 1, Line 1 CREATE ASSEMBLY for assembly 'CLRProject' failed because assembly 'CLRProject' is not authorized for PERMISSION_SET = EXTERNAL_ACCESS. The assembly is authorized when either of the following is true: the database owner (DBO) has EXTERNAL ACCESS ASSEMBLY permission and the database has the TRUSTWORTHY database property on; or the assembly is signed with a certificate or an asymmetric key that has a corresponding login with EXTERNAL ACCESS ASSEMBLY permission.  
// An error occurred while the batch was being executed.  
// Build FAILED.  

As the message explains, SQLCLR code with the EXTERNAL_ACCESS permission set must either be signed or the database must be given the TRUSTWORTHY attribute. Let's make the database TRUSTWORTHY with this ALTER DATABASE statement:

ALTER DATABASE Northwind SET TRUSTWORTHY ON  

Another problem is of database ownership as mentioned in the message "database owner (DBO) has EXTERNAL ACCESS ASSEMBLY permission" so change the owner of the database to sa. Run the following command

EXEC sp_changedbowner sa  

CLR Integration must be enabled in order to use SQL CLR objects. By Default CLR Intergration is disabled so we first have to enable it.
Run the following script to enable Sql CLR integation.

sp_configure 'show advanced options', 1;  
 GO  
 RECONFIGURE;  
 GO  
 sp_configure 'clr enabled', 1;  
 GO  
 RECONFIGURE;  
 GO  

Now again deploy the solution. After deployment open the Sql Server Management Studio. Select Northwind database and open the stored procedures folder and the assemblies folder. See the below image.

As shown in the image sp_WriteFile is added to stored procedures and an assembly of CLRProject is added to the Assemblies folder.

References:
Enabling CLR Integration
CLR Integration Code Access Security
http://www.mssqltips.com/sqlservertip/1662/writing-to-an-operating-system-file-using-the-sql-server-sqlclr/

Steps to enable CLR Integration, OLE Automation and xp_cmdshell procedures in sql server 2005 and 2008

For SQL Server 2005:
Follow these steps:
1) From start menu open Sql Server 2005 surface are configuration

2) Select Surface Area Configuration For Features

Select OLE Automation from Database Engine node and then checked the Enable OLE Automation checked box to enable the feature. Follow the same step for CLR Integration and xp_cmdshell. Then click the apply button and close the window. The CLR Integration, OLE Automation features and xp_cmdshell features are now enabled.


For SQL Server 2008:
1) Open the SQL Server 2008 and follow the below steps

After performing the above steps click he OK button.  The OLE Automation features and xp_cmdshell features are now enabled.
To enable CLR Integration select the server configuration from the drop down menu.

Change the ClrIntegrationEnabled value from false to true to enable it. Now CLR Integration is enabled.
or run the following script to enable CLR Integration

sp_configure 'show advanced options', 1;   
  GO   
  RECONFIGURE;   
  GO   
  sp_configure 'clr enabled', 1;   
  GO   
  RECONFIGURE;   
  GO   

Reference:
http://msdn.microsoft.com/en-us/library/ms161956.aspx