Passing a DataSet to a SQL CLR Stored Procedure

// ============================================================================
// Passing a DataSet to a SQL CLR Stored Procedure
//
// 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.
//
// https://blogs.msdn.microsoft.com/jpapiez/2005/09/26/passing-a-dataset-to-a-sqlclr-stored-procedure/
// ============================================================================

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 partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    publicstaticvoid uspProcessCustomers(string customerData)
    {
        // Need to convert the inbound string to a byte array
        UTF8Encoding encoding = new UTF8Encoding();
        DataSet ds = new DataSet();

        // Convert the string to a byte array, then load into a MemoryStream
        using (MemoryStream ms = new MemoryStream(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 = new SqlCommand())
                {
                    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(new SqlParameter("@customerID", dr["CustomerID"].ToString()));
                            cmd.Parameters.Add(new SqlParameter("@companyName", dr["CompanyName"].ToString()));
                            cmd.Parameters.Add(new SqlParameter("@contactName", dr["ContactName"].ToString()));
                            cmd.Parameters.Add(new SqlParameter("@contactTitle", dr["ContactTitle"].ToString()));
                            cmd.Parameters.Add(new SqlParameter("@address", dr["Address"].ToString()));
                            cmd.Parameters.Add(new SqlParameter("@city", dr["City"].ToString()));
                            cmd.Parameters.Add(new SqlParameter("@region", dr["Region"].ToString()));
                            cmd.Parameters.Add(new SqlParameter("@postalCode", dr["PostalCode"].ToString()));
                            cmd.Parameters.Add(new SqlParameter("@country", dr["Country"].ToString()));
                            cmd.Parameters.Add(new SqlParameter("@phone", dr["Phone"].ToString()));
                            cmd.Parameters.Add(new SqlParameter("@fax", dr["Fax"].ToString()));

                            break;
                        caseDataRowState.Deleted:
                            procName = "uspDelCustomer";

                            // Populate the Parameters collection
                            cmd.Parameters.Add(new SqlParameter("@customerID", dr["CustomerID"].ToString()));

                            break;
                    }

                    using (SqlConnection conn = new SqlConnection("context connection=true"))
                    {
                        cmd.Connection = conn;
                        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;
// ============================================================================

// ============================================================================
// Usage
//
// 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:
// ============================================================================

private void Click()
{
   // Create the dataset
   DataSet ds = new DataSet();

   // Create the data table
   DataTable dt = new DataTable("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 conn = new SqlConnection(strConnection))
   {
      SqlCommand cmd = new SqlCommand("uspProcessCustomers", conn);
      cmd.CommandTimeout = 1800;
      cmd.CommandType = CommandType.StoredProcedure;

      // SqlParameter to hold the dataset
       SqlParameter param1 = new SqlParameter("@customerData", SqlDbType.NVarChar);

      // Create a temporary MemoryStream to hold the output
      // of the WriteXml method of the DataSet
      using (MemoryStream memoryStream = new MemoryStream())
      {
         ds.WriteXml(memoryStream);
         UTF8Encoding encoding = new UTF8Encoding();
         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();
   }
}