ASP.NET/SQL Server 2008 – Using Table-Valued Parameters with .NET Application

With the release of SQL Server 2008, Microsoft introduced a new parameter type called 'Table-Valued Parameters', which provided the flexibility for the SQL developers to send multiple rows of data to a stored procedure/function or inline T-SQL code.

Table-Valued parameters can be used to transfer multiple rows of data without requiring the developer to construct custom string parsing routines, temporary table or multiple parameters which for a complex stored procedure can become difficult to maintain.

Table-Valued parameters are declared in series of steps:

• Create a Table type and define the table structure based on the requirement
• Declare a local variable to use this Table type
• Initialize the local variable with the data
• Pass the local variable to the stored procedure/function to accept this table parameter, make sure the input parameter is 'READONLY'

Table-Valued parameter goes out of scope once the stored procedure/function/T-SQL is executed. Table-Valued parameter can only be modified in the scope it was created, due to this criteria when the parameter is passed to a different stored procedure/function it is passed as READONLY.

For more details on Table-Valued parameters visit: MSDN

Let us consider a real-world example using NorthWind database. You can download and install NorthWind database from the following location.

The article will demonstrate working of Table-Valued Parameters by creating a custom stored-procedure and calling the stored-procedure code from the .NET application (.NET 3.5 required).

NorthWind database contains 'Shippers' table, this table stores shipping company contact details used in the Order fulfillment operation.

We are going to insert 2 new Shippers record in the table, using Table-Valued Parameter enabled stored procedure, the stored procedure will be invoked from .NET code.

a) Create User-Defined Table type

CREATE TYPE Shippers AS TABLE (CompanyName nvarchar(80), phone nvarchar(48))

b) Creating the Table-Valued Parameter enabled Stored procedure

CREATE Procedure CreateShippers(@Shippers Shippers READONLY)
AS
BEGIN
INSERT INTO Shippers SELECT CompanyName, Phone FROM @Shippers
END
GO

Notice @Shippers variable of Shippers type is declared with READONLY qualifier.

c) Calling the Stored procedure through .NET application

I have created a test web form to demonstrate how we can call the Table-Valued Parameter enable stored procedure from .NET application.

Web form displays original data in the Shippers table and a 'Load Shippers' button, once the button is clicked 2 new rows of data are inserted into the Shippers table and the web form reloads to display the newly inserted data (I am using hard coded values in the OnClick event handler of the button to initialize data, the code can be easily modified to accept user keyed data by adding text box controls to the web form).

Screen shot showing Web-form with Original and Inserted data:



ASP.NET Web form Code (C#):

protected void Button1_Click(object sender, EventArgs e)
{
string path = Request.CurrentExecutionFilePath;
path = path.Substring(0, path.LastIndexOf('/'));
Configuration rootWebConfig = WebConfigurationManager.OpenWebConfiguration(path);
ConnectionStringSettings connString = rootWebConfig.ConnectionStrings.ConnectionStrings["NorthwindConnectionString"];

//create DataTable
DataTable dt = new DataTable("MyData");
DataColumn dbCol1 = new DataColumn("CompanyName", System.Type.GetType("System.String"));
DataColumn dbCol2 = new DataColumn("Phone", System.Type.GetType("System.String"));
//add columns
dt.Columns.Add(dbCol1);
dt.Columns.Add(dbCol2);

//create data
DataRow dr;
dr = dt.NewRow();
dr["CompanyName"] = "ACME Express";
dr["Phone"] = "(503) 111-1111";
dt.Rows.Add(dr);
//new row
dr = dt.NewRow();
dr["CompanyName"] = "Courier Express";
dr["Phone"] = "(503) 222-2222";
dt.Rows.Add(dr);
//save
dt.AcceptChanges();

//open Database connection
using (SqlConnection dbConn = new SqlConnection(connString.ConnectionString))
{
dbConn.Open();
using (SqlCommand dbCommand = new SqlCommand("CreateShippers", dbConn))
{
dbCommand.CommandType = CommandType.StoredProcedure;
//Create a parameter using the new type
SqlParameter param = dbCommand.Parameters.Add("@Shippers", SqlDbType.Structured);
//Set the value of the parameter
param.Value = dt;
//Execute the query
dbCommand.ExecuteNonQuery();
}
}

//get new inserted data
SqlDataSource1.ConnectionString = connString.ConnectionString;
SqlDataSource1.SelectCommand = "Select * from Shippers";
SqlDataSource1.SelectCommandType = SqlDataSourceCommandType.Text;
GridView1.DataSourceID = "SqlDataSource1";
//show confirmation
Msg.Visible = true;
//hide button
Button1.Visible = false;
}