Passing Table Valued Parameter to Stored Procedure: Part 2


This is Part 2 of passing a table valued parameter to a stored procedure, a 2-part series.

In Part 1, I described how to pass a DataTable into a stored procedure using a structured type. So the question is, are there any other types you can pass as a structured parameter? The answer is yes.

You can also pass a collection object to a structured type parameter. The collection must implement IEnumerable<SqlDataRecord> in order to be compatible with a structured parameter.
 
In this article I am taking the same example to explain the use of a collection in a structured parameter.

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 for 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 the collection to be passed into the stored procedure and call the stored procedure.

In order to pass a structured type you need to create the collection with the same schema that the user defined table type has. First create a class and add properties; ideally the properties should have the same data type as you have defined in the user defined table type.

public class CustomerDetail
   {
      public string FirstName { get; set; }
      public string LastName { get; set; }
      public string Address { get; set; }
      public string Email { get; set; }
      public string Phone { get; set; }
}

Define the collection class; the collection class must be inherited from List<CustomerDetail> and it should also implement IEnumerable<SqlDataRecord>.

public class CustomerCollection : List<CustomerDetail>, IEnumerable<SqlDataRecord>
   {
      IEnumerator<SqlDataRecord> IEnumerable<SqlDataRecord>.GetEnumerator()
      {
            var sqlRow = new SqlDataRecord(
                  new SqlMetaData("FirstName", SqlDbType.VarChar, 50),
                  new SqlMetaData("LastName", SqlDbType.VarChar, 50),
                  new SqlMetaData("Address", SqlDbType.VarChar, 100),
                  new SqlMetaData("Email", SqlDbType.VarChar, 100),
                  new SqlMetaData("Phone", SqlDbType.VarChar, 10));

            foreach (CustomerDetail cust in this)
            {
                  sqlRow.SetString(0, cust.FirstName);
                 sqlRow.SetString(1, cust.LastName);
                  sqlRow.SetString(2, cust.Address);
                  sqlRow.SetString(3, cust.Email);
                  sqlRow.SetString(4, cust.Phone);

                  yield return sqlRow;
            }
}
}
Note that you have defined SqlMetaData the same as the user defined table type, the column name and data type should exactly match.

Create a collection object and add test data.

CustomerCollection custColl = new CustomerCollection();

custColl.Add(new CustomerDetail { FirstName = "AAAA", LastName = "BBBB", Address = "Address1", Email = "aaa@aaa.com", Phone = "99999999999"});
custColl.Add(new CustomerDetail { FirstName = "BBBB", LastName = "BBBB", Address = "Address1", Email = "bbb@bbb.com", Phone = "99999999999"});
custColl.Add(new CustomerDetail { FirstName = "CCCC", LastName = "CCCC", Address = "Address1", Email = "ccc@ccc.com", Phone = "99999999999"});
custColl.Add(new CustomerDetail { FirstName = "DDDD", LastName = "DDDD", Address = "Address1", Email = "ddd@ddd.com", Phone = "99999999999"});
Now pass the structured type to the stored procedure.

SqlParameter param = new SqlParameter();
   param.ParameterName = "CustDtl";
   param.SqlDbType = SqlDbType.Structured;
   param.Value = custColl;
   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();
}
If you think in terms of performance, a collection is a better option for a table valued parameter.