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 = "[email protected]", Phone = "99999999999"});
custColl.Add(new CustomerDetail { FirstName = "BBBB", LastName = "BBBB", Address = "Address1", Email = "[email protected]", Phone = "99999999999"});
custColl.Add(new CustomerDetail { FirstName = "CCCC", LastName = "CCCC", Address = "Address1", Email = "[email protected]", Phone = "99999999999"});
custColl.Add(new CustomerDetail { FirstName = "DDDD", LastName = "DDDD", Address = "Address1", Email = "[email protected]", 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.

3 Comments

Join the conversation! Your thoughts help the community grow.

Leo Gurdian8y ago
Thank you for the article Praadeep. Very helpful!
shibin koshy9y ago
Great article. Saved my day
spam kuturm15y ago
only the last item in the collection saved to the table? what could be wrong i followed even copy pasted everything in your tutorial. Can you please explain?