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.
The audience of this article is expected to be aware of ADO.NET, C# and SQL Server 2008.
To demonstrate an end to end example of table value parameters.
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
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
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.
- Create a data structure which is equivalent to the table value parameter - just the way a
nvarcharis represented as
stringon the C# end.
- 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
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.