C# and Table Value Parameters

http://www.codeproject.com/KB/cs/CSharpAndTableValueParams.aspx

 

• Download source - 1.82 MB

Introduction

In this article, we explore the SQL server 2008 Table value parameters. Many articles have been published on this subject, some describe the SQL server side of things, others discuss how to access the table value functions from .NET.

I was unable to find one crisp article just describing a simple example of how to use Table value parameters using C# and SQL Server 2008 and after some research, I wrote this article so that other individuals may benefit from my findings.

Table value parameters are a simple mechanism to pass bulk of data from ADO.NET to SQL Server. They allow data to be available on the SQL Server as a temporary table.

This task in the past was done using XML in which the bulk data was modelled in a hierarchical structure and passed to the stored procedure. The stored procedure then converted back the structure into a temporary table to process the data in a relational manner.

Audience

The audience of this article is expected to be aware of ADO.NET, C# and SQL Server 2008.

Objective

To demonstrate an end to end example of table value parameters.

The Code

We will directly jump into the code for this article. We shall discuses each layer from the SQL Server Table to the C# code.

The Database Table

In this section, we describe the table schema for the sample problem. The table in this code will be a simple table with an identity column along with a nvarchar and integer column. The nvarchar column will not allow nulls and the integer will allow nulls. The schema creations script for the same is:

--This is the database table
CREATE TABLE dbo.SampleTable
(
Id int NOT NULL IDENTITY (1, 1),
SampleString nvarchar(64) NOT NULL,
SampleInt int NULL
) ON [PRIMARY]

The Table Value Parameter Type

To pass the data along as a table value parameter, a new database type has to be created. This type definition in essence describes what one row will look like and we will pass around a bunch of such rows. The script for the same is:

-- Create a table data type
CREATE TYPE [dbo].[SampleDataType] As Table
(
--This type has structure similar to the DB table 
SampleString Nvarchar(64) Not Null -- Having one String
, SampleInt Int -- and one int
)

One may observe that the structure of this type is similar to the table, however what is important is that we need to create the type definition most convenient to pass along the data which may mean de-normalization.

The Stored Procedure

The stored procedure is a rather simple piece of code which takes the SampleDataType type as input parameter. Inside the stored procedure, the parameter is available as a temporary table. This table however has to be readonly.

--This is the Stored Procedure
CREATE PROCEDURE [dbo].[SampleProcedure]
(
-- which accepts one table value parameter. 
-- It should be noted that the parameter is readonly
@Sample As [dbo].[SampleDataType] Readonly
)
AS

Begin
-- We simply insert values into the DB table from the parameter
-- The table value parameter can be used like a table with only read rights
Insert Into SampleTable(SampleString,SampleInt)
Select SampleString, SampleInt From @Sample
End

The DB Test Script

At this stage, we should quickly test our database for any errors using this script, which also describe the mechanism using which table value parameters may be passed along from one stored procedure to other.

-- This is the sample script to test the SP
-- An instance of the Table parameter type is created
Declare @SampelData As [dbo].[SampleDataType]
-- and then filled with the set of values
Insert Into @SampelData(SampleString, SampleInt) Values('1',1);
Insert Into @SampelData(SampleString, SampleInt) Values('2',null);
Insert Into @SampelData(SampleString, SampleInt) Values('3',3);
Select * From @SampelData
-- we then call the SP to store the values
Exec SampleProcedure @SampelData
Select * From SampleTable

The C# Code

In the C# code, we have to resolve two problems to use the required stored procedure.

1. Create a data structure which is equivalent to the table value parameter - just the way a nvarchar is represented as string on the C# end.
2. Pass this data to the Stored procedure.

Representing the Data

There are many mechanisms available to represent the data such as representing the data as a DataTable, IEnumerable, Linq object, Data reader, etc. In this article, we will focus on the DataTable. All we do is create a DataTable, define columns parallel to our Table data type and fill them up.

//To represent the table parameter in C#, we need to either 
//have a set of entities which are IEnumreable 
//or a data reader or a Data table.
//In this example we create a data table with same name as the type we have in the DB 
DataTable dataTable = new DataTable("SampleDataType"); 
//we create column names as per the type in DB 
dataTable.Columns.Add("SampleString", typeof(string)); 
dataTable.Columns.Add("SampleInt", typeof(Int32)); 
//and fill in some values 
dataTable.Rows.Add("99", 99); 
dataTable.Rows.Add("98", null); 
dataTable.Rows.Add("97", 99); 

Passing the Data

To pass the data, it is to be represented as a SqlParameter. The type of this parameter is Structured. The details are as shown in the code snippet. The other code to call the SP is trivial and may be seen in the given code.

SqlParameter parameter = new SqlParameter(); 
//The parameter for the SP must be of SqlDbType.Structured 
parameter.ParameterName="@Sample"; 
parameter.SqlDbType = System.Data.SqlDbType.Structured; 
parameter.Value = dataTable; 
command.Parameters.Add(parameter); 

Please note that the attached code is developed in Visual Studio 2010 + SQL Server 2008 however it will work with Visual Studio 2005 and SQL Server 2008.