Click here to Skip to main content
Click here to Skip to main content

C# and Table Value Parameters

, 20 Aug 2009
Rate this:
Please Sign up or sign in to vote.
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:

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

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)

Share

About the Author

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

Comments and Discussions

 
Questionhow to get back the identity insert values PinmemberD_Ana16-Jul-14 10:04 
AnswerRe: how to get back the identity insert values Pinmembergaurav_verma_mca16-Jul-14 17:34 
GeneralRe: how to get back the identity insert values PinmemberD_Ana21-Jul-14 9:48 
GeneralMy vote of 5 PinmemberAshkan M. Khiabani (3665458)18-Sep-13 10:53 
GeneralSimilar article with .net sample using table valued parameter Pinmemberiamonweb11-Jul-13 0:21 
QuestionProblem and question [modified] PinmemberMember 101351541-Jul-13 9:42 
GeneralMy vote of 4 PinmemberM Rayhan9-May-13 1:37 
GeneralMy vote 5 PinmemberNitin Sawant25-Sep-12 20:43 
QuestionEnterprise Library PinmemberDesiFella20-Aug-12 14:30 
GeneralVery nice article PinmemberMember 82580251-Apr-12 22:20 
Questioni think you forgot a line Pinmembermad8vskillz14-Feb-12 7:06 
AnswerRe: i think you forgot a line Pinmemberashokvan26-Aug-12 20:25 
GeneralManClear as water Pinmembergonza414-Apr-11 10:37 
GeneralMy vote of 4 PinmemberMember 341395223-Feb-11 22:56 
QuestionQuestion on sending the bulk data Pinmemberalwaysprav2003@gmail.com19-May-10 5:43 
AnswerRe: Question on sending the bulk data Pinmembergaurav_verma_mca19-May-10 7:25 
GeneralRe: Question on sending the bulk data Pinmemberalwaysprav2003@gmail.com19-May-10 7:37 
Hello Gaurav,
 
Here is where I'm creating a datatable with the same name in the DB
 
DataTable IDSequence = new DataTable("@CodeIDsCollectionIDs");
IDSequence.Columns.Add("CodeID", typeof(Int32));
IDSequence.Columns.Add("CollectionID", typeof(Int32));
for (int i = 0; i < codeIDList.Count; i++)
{
IDSequence.Rows.Add(codeIDList[i], collectionID);
}
 
Sql Parameter
List paramList = new List();
SqlParameter parameter = new SqlParameter();
parameter.ParameterName = "@CodeIDsCollectionIDs";
parameter.SqlDbType = System.Data.SqlDbType.Structured;
parameter.Value = IDSequence;
paramList.Add(parameter);
 
XXXXX.ExecuteScalar("DAL.MemberADDList", paramList);
 

Stored Proc
 
PROCEDURE [DAL].[MemberADDList]
@CodeIDsCollectionIDs MemberBulkInsert READONLY
,@AuditPointID BIGINT = 0
AS
BEGIN
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON;
 
Create Table IDSequence(PKID INT IDENTITY(1,1), CollectionID INT, CodeID INT)
Insert Into IDSequence(CollectionID, CodeID)
Select CollectionID, CodeID From @CodeIDsCollectionIDs
 
************EXECUTE SCALAR METHOD******************
 
//some code
_connection.Open();
returnValue = dbCmd.ExecuteScalar();
_connection.Close();
 
At Execute Scalar it fails and gives me the following error.
There is already an object named 'IDSequence' in the database.
 
Please let me know if you need any further information.
 
Thanks,
Praveen.
GeneralRe: Question on sending the bulk data Pinmembergaurav_verma_mca20-May-10 1:09 
GeneralHi PinmemberNiladri_Biswas30-Sep-09 20:11 
GeneralNice article PingroupMd. Marufuzzaman20-Aug-09 6:10 

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

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

| Advertise | Privacy | Mobile
Web04 | 2.8.140814.1 | Last Updated 20 Aug 2009
Article Copyright 2009 by gaurav_verma_mca
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid