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.