Introduction
Table Valued Parameter is a new feature introduced with SQL Server 2008. Table Valued Parameters help to pass multiple rows of data from a client application to SQL Server without multiple round trips. Using a Table Valued Parameter we can pass multiple rows to a stored procedure.
Table Valued Parameter is a mechanism to pass bulk data from ADO.NET to SQL Server. In the following example we learn how to pass a Table Valued Parameter to a stored procedure. Using a Table Valued Parameter, we can pass a table as a single object instead of row by row.
Step 1: Create a table and a User Defined table type:
CREATE TABLE CUSTOMER
(
CustomerId INT NOT NULL,
CustomerName VARCHAR(MAX),
Isdeleted BIT,
PRIMARY KEY (CustomerId)
)
CREATE TYPE dbo.TableValuedTypeExample AS TABLE
(
CustomerId INT NOT NULL,
CustomerName VARCHAR(MAX),
PRIMARY KEY (CustomerId)
)
Now create a procedure to receive data for the Table Valued Parameter and insert it into our original table:
CREATE PROC InsertValue
(@TempTable AS dbo.TableValuedTypeExample READONLY)
AS
BEGIN
INSERT INTO CUSTOMER (CustomerId,CustomerName ,Isdeleted )
SELECT CustomerId, CustomerName, 0 AS Isdeleted FROM @TempTable
END
Please refer Table-Valued Parameter in SQL Server 2008.
Step 2: Create a DataTable or structure the same as your Table Valued Parameter. Remember that all columns of the DataTable are parallel to the Table Data type:
static DataTable CreateTable()
{
DataTable dt = new DataTable();
dt.Columns.Add("CustomerId", typeof(Int32));
dt.Columns.Add("CustomerName", typeof(string));
return dt;
}
Step 3: To pass the data to the stored procedure, it must be represented as a SqlParameter and the type of this parameter must be structured:
//Create Table
DataTable myTable = CreateTable();
// Add New Rowto table
myTable.Rows.Add(1, "Jignesh Trivedi");
myTable.Rows.Add(2, "Tejas Trivedi");
myTable.Rows.Add(3, "Rakesh Trivedi");
SqlConnection connection = new SqlConnection("Data Source= DatabaseName;Initial Catalog=AdventureWorks;User
Id=sa;Password=password;");
connection.Open();
SqlCommand cmd = new SqlCommand("InsertValue", connection);
cmd.CommandType = CommandType.StoredProcedure;
//Pass table Valued parameter to Store Procedure
SqlParameter sqlParam = cmd.Parameters.AddWithValue("@TempTable", myTable);
sqlParam.SqlDbType = SqlDbType.Structured;
cmd.ExecuteNonQuery();
connection.Close();
Console.Write("Data Save Successfully.");
Step 4: You can also do this using a generic list. Here the myDataCollection class is inherited from the List<myData> class and
the IEnumerable<SqlDataRecord> interface. The implementation IEnumerable <SqlDataRecord> will be used to convert our List data to our user
defined table:
public class myData
{
public int CustomerId { get; set; }
public string CustomerName { get; set; }
}
public class myDataCollection : List<myData>, IEnumerable<SqlDataRecord>
{
IEnumerator<SqlDataRecord> IEnumerable<SqlDataRecord>.GetEnumerator()
{
SqlDataRecord ret = new SqlDataRecord(
new SqlMetaData("CustomerId", SqlDbType.Int),
new SqlMetaData("CustomerName", SqlDbType.VarChar, 20)
);
foreach (myData data in this)
{
ret.SetInt32(0, data.CustomerId);
ret.SetString(1, data.CustomerName);
yield return ret;
}
}
}
myDataCollection myTable = new myDataCollection();
myTable.Add(new myData { CustomerId = 4, CustomerName = "Jignesh" });
myTable.Add(new myData { CustomerId = 5, CustomerName = "Tejas" });
myTable.Add(new myData { CustomerId = 6, CustomerName = "Rakesh" });
SqlConnection connection = new SqlConnection("Data Source= DatabaseName;Initial Catalog=AdventureWorks;User
Id=sa;Password=password;");
connection.Open();
SqlCommand cmd = new SqlCommand("InsertValue", connection);
cmd.CommandType = CommandType.StoredProcedure;
//Pass table Valued parameter to Store Procedure
SqlParameter sqlParam = cmd.Parameters.AddWithValue("@TempTable", myTable);
sqlParam.SqlDbType = SqlDbType.Structured;
cmd.ExecuteNonQuery();
connection.Close();
Console.Write("Data Save Successfully.");
Conclusion
The table-valued parameter feature introduced with SQL Server 2008 has been truly needed. This will help developers to write code that provides better performance to applications by decreasing round-trips to the server.