Click here to Skip to main content
15,867,594 members
Articles / Database Development / SQL Server / SQL Server 2008

C# and Table Value Parameters

Rate me:
Please Sign up or sign in to vote.
4.78/5 (30 votes)
20 Aug 2009CPOL3 min read 325.3K   4.3K   50   22
How to send bulk data using table value parameters from C#

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:

SQL
--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:

SQL
-- 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.

SQL
--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.

SQL
-- 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.

C#
//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.

C#
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.

History

  • 20th August, 2009: Initial post

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Architect Imfinity
India India
Hi I have been working on enterprise applications for last six years.

Comments and Discussions

 
SuggestionJust a little note Pin
Stefano Manni15-Oct-18 2:24
Stefano Manni15-Oct-18 2:24 
PraiseThank you for your clear and useful example: exactly what I needed Pin
Geri Reshef16-Jun-17 8:56
Geri Reshef16-Jun-17 8:56 
Questionhow to get back the identity insert values Pin
D_Ana16-Jul-14 10:04
D_Ana16-Jul-14 10:04 
AnswerRe: how to get back the identity insert values Pin
gaurav_verma_mca16-Jul-14 17:34
gaurav_verma_mca16-Jul-14 17:34 
GeneralRe: how to get back the identity insert values Pin
D_Ana21-Jul-14 9:48
D_Ana21-Jul-14 9:48 
GeneralMy vote of 5 Pin
Ashkan M. Khiabani (3665458)18-Sep-13 10:53
Ashkan M. Khiabani (3665458)18-Sep-13 10:53 
GeneralSimilar article with .net sample using table valued parameter Pin
iamonweb11-Jul-13 0:21
iamonweb11-Jul-13 0:21 
QuestionProblem and question Pin
Mirna_1-Jul-13 9:42
Mirna_1-Jul-13 9:42 
GeneralMy vote of 4 Pin
M Rayhan9-May-13 1:37
M Rayhan9-May-13 1:37 
GeneralMy vote 5 Pin
Nitin S25-Sep-12 20:43
professionalNitin S25-Sep-12 20:43 
QuestionEnterprise Library Pin
DesiFella20-Aug-12 14:30
DesiFella20-Aug-12 14:30 
GeneralVery nice article Pin
Member 82580251-Apr-12 22:20
Member 82580251-Apr-12 22:20 
Questioni think you forgot a line Pin
mad8vskillz14-Feb-12 7:06
mad8vskillz14-Feb-12 7:06 
AnswerRe: i think you forgot a line Pin
ashokvan26-Aug-12 20:25
ashokvan26-Aug-12 20:25 
GeneralManClear as water Pin
gonza414-Apr-11 10:37
gonza414-Apr-11 10:37 
GeneralMy vote of 4 Pin
Chintan S Pandya23-Feb-11 22:56
Chintan S Pandya23-Feb-11 22:56 
QuestionQuestion on sending the bulk data Pin
alwaysprav2003@gmail.com19-May-10 5:43
alwaysprav2003@gmail.com19-May-10 5:43 
AnswerRe: Question on sending the bulk data Pin
gaurav_verma_mca19-May-10 7:25
gaurav_verma_mca19-May-10 7:25 
GeneralRe: Question on sending the bulk data Pin
alwaysprav2003@gmail.com19-May-10 7:37
alwaysprav2003@gmail.com19-May-10 7:37 
GeneralRe: Question on sending the bulk data Pin
gaurav_verma_mca20-May-10 1:09
gaurav_verma_mca20-May-10 1:09 
Your SP has this statement

Create Table IDSequence(PKID INT IDENTITY(1,1), CollectionID INT, CodeID INT)

Thus you are trying to create a table on the fly in the SP. Why are you doing this?

You can't create a table
GeneralHi Pin
Niladri_Biswas30-Sep-09 20:11
Niladri_Biswas30-Sep-09 20:11 
GeneralNice article Pin
Md. Marufuzzaman20-Aug-09 6:10
professionalMd. Marufuzzaman20-Aug-09 6:10 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.