Passing Table Valued Parameter to Stored Procedure: Part 1

Before SQL Server 2008, there were three options to pass bulk data into a stored procedure, but each had their own drawbacks.

1. Pass each record one by one to stored procedure - this slows down entire process.
2. BulkCopy - this works only for insert operations.
3. Pass XML string - parsing XML string on SQL Server is very costly operation in terms of performance.

With the ability to accept table valued parameters, now you can pass structured type parameters to a SQL Server 2008 stored procedure from your .Net code. Table valued parameters allows passing entire sets of rows from ADO.Net code to SQL Server 2008.

In this demonstration I am taking a simple scenario where I will pass customer information to a stored procedure and insert customer rows into the database. Here are the steps to implement passing a structure type to a stored procedure.

1. Create a CustomerDetail table in your database; this will be used to store Customer records.

CREATE TABLE dbo.CustomerDetail(
      CustomerId INT IDENTITY(1,1) NOT NULL,
      FirstName VARCHAR(50),
      LastName VARCHAR(50),
      Address VARCHAR(100),
      Email  VARCHAR(100),
      Phone VARCHAR(10)
)
2. Create a user defined table type which will be used as a table valued parameter in the stored procedure.

CREATE TYPE dbo.CustomerDetailType AS TABLE(

   FirstName VARCHAR(50),

   LastName VARCHAR(50),

   Address VARCHAR(100),

   Email  VARCHAR(100),
   Phone VARCHAR(10)
)

3. Create the SaveCustomerDetails stored procedure. This stored procedure will accept a table valued parameter. Note that the READONLY keyword is required to declare a table valued parameter; you cannot perform DML operations on a table valued parameter.

CREATE PROCEDURE dbo.SaveCustomerDetail
   @CustDtl dbo.CustomerDetailType READONLY
AS
BEGIN
   SET NOCOUNT ON
  
   INSERT INTO dbo.CustomerDetail (FirstName, LastName, Address, Email, Phone)
   SELECT FirstName, LastName, Address, Email, Phone FROM @CustDtl
END

4. Write C# code to create a DataTable to be passed into the stored procedure and call the stored procedure.
In order to pass a structured type you need to create the DataTable with the same schema as the user defined table type has.

DataTable custDT = new DataTable();
DataColumn col = null;

col = new DataColumn("FirstName");
custDT.Columns.Add(col);
col = new DataColumn("LastName");
custDT.Columns.Add(col);
col = new DataColumn("Address");
custDT.Columns.Add(col);
col = new DataColumn("Email");
custDT.Columns.Add(col);
col = new DataColumn("Phone");
custDT.Columns.Add(col);
Pass structured type (DataTable) to the stored procedure.

SqlParameter param = new SqlParameter();
   param.ParameterName = "CustDtl";
   param.SqlDbType = SqlDbType.Structured;
   param.Value = custDT;
   param.Direction = ParameterDirection.Input;

   String dbConnStr =     ConfigurationManager.ConnectionStrings["DBConStr"].ConnectionString;
   SqlConnection conn = null;

   using (conn = new SqlConnection(dbConnStr))
   {
      SqlCommand sqlCmd = new SqlCommand("dbo.SaveCustomerDetail");
     conn.Open();
      sqlCmd.Connection = conn;
      sqlCmd.CommandType = CommandType.StoredProcedure;

      sqlCmd.Parameters.Add(param);

     sqlCmd.ExecuteNonQuery();
}