What is Table – Valued Parameter in SQL Server 2008?
Table-valued parameters provide an easy way to marshal multiple rows of data from a client application to SQL Server without requiring multiple round trips or special server-side logic for processing the data. You can use table-valued parameters to encapsulate rows of data in a client application and send the data to the server in a single parameterized command. The incoming data rows are stored in a table variable that can then be operated on by using Transact-SQL.
What we are going to do with Table – Valued Parameter?
We are going to demonstrate a very simple example for using Table – Valued parameter. In this sample project we will insert bulk amount of data into the table by passing a bulk data using datatable in C# to SQL stored procedure.
Create a Table for insert data using Table – Valued Parameter
Here we are having a table named Officer and having three fields ID,Name and Salary. We are going to fill the table with bulk data.
CREATE TABLE Officer( ID INT PRIMARY KEY IDENTITY(1,1), NAME VARCHAR(50), SALARY DECIMAL(18, 0))
Stored Procedure for insert data by accepting Table Valued Parameter
Now we are going to create a Stored Procedure that accepting a table type as parameter and insert values in this type into the table.
CREATE PROCEDURE InsertOfficerDetails ( @OfficerData OfficerDetails readonly ) AS INSERT INTO Officer (Name, Salary) SELECT Name, Salary FROM @OfficerData;
C# code to call Stored Procedure to insert data in to the table using Table – Valued Parameter
We are creating a simple ASPX page with a single button. When we click this button we are calling above stored procedure by creating and passing some amount of sample data to the stored procedure as Table – Valued Parameter.
ASPX Page
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="CallSP.aspx.cs" Inherits="ExperimentLab.CallSP" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head id="Head1" runat="server"> <title></title> </head> <body> <form id="form1" runat="server"> <div> <asp:Button ID="btnCallSP" runat="server" Text="Call SP" OnClick="btnCallSP_Click" /> </div> </form> </body> </html>
Code Behind
protected void btnCallSP_Click(object sender, EventArgs e)
{
try
{
DataTable dt = new DataTable();
DataColumn dtCol = new DataColumn();
dtCol.ColumnName = "ID";
dt.Columns.Add(dtCol);
dtCol = new DataColumn();
dtCol.ColumnName = "Name";
dt.Columns.Add(dtCol);
dtCol = new DataColumn();
dtCol.ColumnName = "Salary";
dt.Columns.Add(dtCol);
for (int i = 0; i < 10; i++)
{
DataRow dr = dt.NewRow();
dr["Name"] = "Name " + i;
dr["Salary"] = 1000 + i;
dr["ID"] = i;
dt.Rows.Add(dr);
}
string connStr = ConfigurationManager.
AppSettings["LocalSqlServer"].ToString();
SqlConnection con = new SqlConnection(connStr);
using (var conn = new SqlConnection(connStr))
using (var cmd = conn.CreateCommand())
{
cmd.Connection = con;
con.Open();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "dbo.InsertOfficerDetails";
SqlParameter param = cmd.Parameters.AddWithValue("@OfficerData", dt);
cmd.ExecuteNonQuery();
}
}
catch (Exception a)
{
Response.Write(a.Message);
}
}
Web.Config
<appSettings> <add key="LocalSqlServer" value="Database=testDB;Server=Servername\SQLEXPRESS;User Id=userid;Password=password"/> </appSettings>
Hence we discussed about how to create and use Table valued parameters, how to create a store procedure with table type as parameter, how to pass table to stored procedure in C#/Asp.Net, how to insert multiple rows of data to a table with table valued parameter in SQL, how to insert bulk data to SQL table using Table Valued Parameter in SQL Server 2008 etc..
