Is it possible to pass a DataSet to SQL and process the data in a SQLCLR stored procedure? This is the question I set out to answer.
The .NET Framework 2.0 adds some features to the DataSet that make this scenario possible (WriteXml, ReadXml). I created a sample that proves this concept.
First we need the CLR stored proc to handle the passed in dataset. Start by creating a new "SQL Server Project" in Visual Studio 2005. This example uses the Northwind database installed with SQLExpress.
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.IO;
using System.Xml.Serialization;
using System.Text;
public partialclassStoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
publicstaticvoid uspProcessCustomers(string customerData)
{
// Need to convert the inbound string to a byte array
UTF8Encoding encoding = newUTF8Encoding();
DataSet ds = newDataSet();
// Convert the string to a byte array, then load into a MemoryStream
using (MemoryStream ms = newMemoryStream(encoding.GetBytes(customerData)))
{
// Populate the DataSet from the MemoryStream
ds.ReadXml(ms);
}
// Iterate through the dataset and process each record.
foreach (DataRow dr in ds.Tables["customers"].Rows)
{
if (dr.RowState != DataRowState.Unchanged &&
dr.RowState != DataRowState.Detached)
{
string procName = string.Empty;
// Instantiate a SqlCommand object to perform the
// required DB operation
using (SqlCommand cmd = newSqlCommand())
{
cmd.CommandType = CommandType.StoredProcedure;
// determine which procedure to execute
// based on the state of the row
switch (dr.RowState)
{
caseDataRowState.Added:
caseDataRowState.Modified:
if (dr.RowState == DataRowState.Added)
{
procName = "uspInsCustomer";
}
else
{
procName = "uspUpdCustomer";
}
// Populate the Parameters collection
cmd.Parameters.Add(
newSqlParameter("@customerID", dr["CustomerID"].ToString()));
cmd.Parameters.Add(
newSqlParameter("@companyName", dr["CompanyName"].ToString()));
cmd.Parameters.Add(
newSqlParameter("@contactName", dr["ContactName"].ToString()));
cmd.Parameters.Add(
newSqlParameter("@contactTitle", dr["ContactTitle"].ToString()));
cmd.Parameters.Add(
newSqlParameter("@address", dr["Address"].ToString()));
cmd.Parameters.Add(
newSqlParameter("@city", dr["City"].ToString()));
cmd.Parameters.Add(
newSqlParameter("@region", dr["Region"].ToString()));
cmd.Parameters.Add(
newSqlParameter("@postalCode", dr["PostalCode"].ToString()));
cmd.Parameters.Add(
newSqlParameter("@country", dr["Country"].ToString()));
cmd.Parameters.Add(
newSqlParameter("@phone", dr["Phone"].ToString()));
cmd.Parameters.Add(
newSqlParameter("@fax", dr["Fax"].ToString()));
break;
caseDataRowState.Deleted:
procName = "uspDelCustomer";
// Populate the Parameters collection
cmd.Parameters.Add(
newSqlParameter("@customerID", dr["CustomerID"].ToString()));
break;
}
using (SqlConnection cxn = newSqlConnection("context connection=true"))
{
cmd.Connection = cxn;
cmd.CommandText = procName;
cmd.Connection.Open();
cmd.ExecuteNonQuery();
cmd.Connection.Close();
}
}
}
}
}
}
If you use Visual Studio to deploy the stored procedure to the database you'll need to drop and recreate the procedure. This is because the procedure gets created with the customerData parameter as NVarChar(4000). In order to accept even a minimal DataSet (in my test I populated the DS with 10 records), 4000 chars isn't going to be large enough. To get around this you can use the new variable text types in SQL Server 2005, in this case NVarChar(max):
CREATE PROCEDURE uspProcessCustomers
@CustomerData AS NVarChar(max)
AS
EXTERNAL NAME SQLData.StoredProcedures.uspProcessCustomers
The procedures for inserting/updating/deleting aren't that interesting, so I'll leave those to your imagination. Now we need a client app that consumes this stored procedure. Start by creating a new Windows Forms client. Add to the client a button, then add the following to the button click event handler:
privatevoid button1_Click(object sender, EventArgs e)
{
this.Cursor = Cursors.WaitCursor;
this.button1.Enabled = false;
// Create the dataset
DataSet ds = newDataSet();
// Create the data table
DataTable dt = newDataTable("Customers"); ds.Tables.Add(dt);
// Create the table schema
DataColumn dc = dt.Columns.Add("CustomerID", typeof(string));
// the first column is the primary key
dc.AllowDBNull = false; dc.Unique = true;
dt.Columns.Add("CompanyName", typeof(string));
dt.Columns.Add("ContactName", typeof(string));
dt.Columns.Add("ContactTitle", typeof(string));
dt.Columns.Add("Address", typeof(string));
dt.Columns.Add("City", typeof(string));
dt.Columns.Add("Region", typeof(string));
dt.Columns.Add("PostalCode", typeof(string));
dt.Columns.Add("Country", typeof(string));
dt.Columns.Add("Phone", typeof(string));
dt.Columns.Add("Fax", typeof(string));
// Add a few records to the data table.
for (int i = 1; i <= 10; i++) {
// create a new row
DataRow dr = dt.NewRow();
// populate the fields
dr[0] = "C" + i.ToString(); dr[1] = "Company Name " + i.ToString();
dr[2] = "Contact Name " + i.ToString();
dr[3] = "Contact Title " + i.ToString();
dr[4] = "Address " + i.ToString();
dr[5] = "City " + i.ToString();
dr[6] = "Region " + i.ToString();
dr[7] = "POSTL " + i.ToString();
dr[8] = "Country " + i.ToString();
dr[9] = "Phone " + i.ToString();
dr[10] = "FAX " + i.ToString();
// add the row to the table
dt.Rows.Add(dr); }
string strConnection = "Data Source=.\\sqlexpress;" +
"Initial Catalog=Northwind;Integrated Security=True";
// Now, connect to the database and pass the dataset
using (SqlConnection cxn = newSqlConnection(strConnection))
{
SqlCommand cmd = newSqlCommand("uspProcessCustomers", cxn);
cmd.CommandTimeout = 1800;
cmd.CommandType = CommandType.StoredProcedure;
// SqlParameter to hold the dataset
SqlParameter param1 = newSqlParameter("@customerData", SqlDbType.NVarChar);
// Create a temporary MemoryStream to hold the output
// of the WriteXml method of the DataSet
using (MemoryStream memoryStream = newMemoryStream())
{
ds.WriteXml(memoryStream);
UTF8Encoding encoding = newUTF8Encoding();
param1.Value = encoding.GetString(memoryStream.ToArray());
}
// Add the SqlParameter to the Parameters collection
cmd.Parameters.Add(param1);
// Open the connection to the database
cmd.Connection.Open();
// Execute the query
cmd.ExecuteNonQuery();
// Finally, close the connection
cmd.Connection.Close();
}
this.button1.Enabled = true;
this.Cursor = Cursors.Arrow;
}
That's all it takes. I welcome your comments and suggestions on this post.
J.
