C# and Table Value Parameters

转载 2010年06月01日 09:57:00




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.

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

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

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

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.SqlDbType = System.Data.SqlDbType.Structured; 
parameter.Value = dataTable; 

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.


  • highplayer
  • highplayer
  • 2011年06月22日 08:32
  • 19206

MySQL - Entity : The value for column 'IsPrimaryKey' in table 'TableDetails' is DBNull

Entity Framework (version 6.1.3) and MySQL Server (5.7) One way to resolve the issue is, 1. Open...
  • yw1688
  • yw1688
  • 2016年03月12日 16:23
  • 842

Scala 面向对象编程

一 定义一个简单的类 1.1定义类,包含field以及方法 classHelloWorld {   private var name = "leo"   def sayHello() { pr...
  • zhanglh046
  • zhanglh046
  • 2016年12月21日 15:25
  • 375


string sql = "Select id,userName,passWord,[rule] from [User] where userName=@name and passWord=@pwd"...
  • ppdtts520
  • ppdtts520
  • 2012年06月03日 13:14
  • 5627


按照C#操作Sql Server数据库的方式是不一样的,使用时发现占位符数据会混乱。 查阅相关资料及测试后,总结出这样一套使用方法。 方法: public bool Tsql...
  • xianglikai1
  • xianglikai1
  • 2017年07月31日 16:15
  • 412


因为做应用系统肯定要和数据库打交道 而我最常接触的就是SQL 2000数据库 常规的方式就是写存储过程类然后和数据库的存储过程进行交互 执行命令或者取得数据 相信大家都知道.net中有四个关于参数传入...
  • hustsay23
  • hustsay23
  • 2007年11月25日 20:36
  • 2740

VC编译没有错误,exe文件操作数据库时报错,No value given for one or more required parameters

 --------------------------- example16_2 --------------------------- No value given for one or...
  • txwtech
  • txwtech
  • 2016年03月16日 23:58
  • 713

C#操作SQLite数据库时出现“Insufficient parameters supplied to the command”的错误

将如下SQL语句: insert into tbl_stock(Title,Barcode,Price) values(@Title,@Barcode,@Price) 改成: insert ...
  • diligentcat
  • diligentcat
  • 2014年07月22日 01:03
  • 2314

org.springframework.dao.InvalidDataAccessApiUsageException:No value supplied forthe SQL parameter已解决

引起org.springframework.dao.InvalidDataAccessApiUsageException: No value supplied for the SQL parame...
  • qq_30908729
  • qq_30908729
  • 2017年08月17日 11:18
  • 102

Table-Valued Parameters(表值类型参数,Sqlserver中表值类型参数应用包括前台,数据库)

Table-Valued Parameters Table-valued parameters provide an easy way to marshal multiple rows of dat...
  • jzt_designer
  • jzt_designer
  • 2013年03月09日 09:15
  • 2629
您举报文章:C# and Table Value Parameters