SQL Server 2008 Table Valued Parameters

Sun, Dec 23, 2007

ADO.net, SQL, c#, visual studio 2008

Last two weeks I have been working on developing bulk data manipulations. And even before that I always wondered “What’s the best way to pass an array of values into a SQL Server stored procedure.” One option I found was OPENXML, which I blogged here.

And I was trying out visual studio 2008 and SQL server 2008 these days. And last night I found out that SQL server now support Tabled valued Parameters which allow us to send data tables as parameters to stored procedures. It still uses the same ADO.Net API.

Now I’m going to show how it works. For this I’m using the same scenario which I used in my previous post (reading contents of a folder and saving their information). here i have used sql server 2008 november ctp and visual c# 2008 express edition.

First create the table given below:

CREATE TABLE FileDetails(
FileName varchar(50) PRIMARY KEY,
CreatedDate varchar(50) ,
Size decimal(18, 0) )

Then, we should declare a new Table User Defined Type in the database:

create type FileDetailsType as table
(
FileName varchar(50),
CreatedDate varchar(50),
Size decimal(18,0)
)

Then create a stored procedure that gets a parameter of the above type, and inserts many rows in a single command.

create procedure InsertFileDetails
(
@FileDetails FileDetailsType readonly
)
as

insert
into FileDetails (FileName, CreatedDate, Size)
select FileName, CreatedDate, Size
from @FileDetails;

Then to execute this procedure, we can create a data table and add the rows in to it. And then pass this data table as parameter to the database.

private static void SaveFileDetail(List<FileInfo> info)

{

Console.WriteLine(“**********updating with tablevalued parameters****”);

DataTable dt = preparedatatable();

foreach (FileInfo file in info)

{

DataRow dr = dt.NewRow();

dr[0] = file.Name;

dr[1] = file.CreationTime.ToShortDateString();

dr[2] = (decimal)file.Length;

dt.Rows.Add(dr);

}

using (SqlConnection conn = new SqlConnection(“your connection string”))

{

SqlCommand cmd = conn.CreateCommand();

cmd.CommandType = System.Data.CommandType.StoredProcedure;

cmd.CommandText = “dbo.InsertFileDetails”;

SqlParameter param = cmd.Parameters.AddWithValue(“@FileDetails”, dt);

conn.Open();

cmd.ExecuteNonQuery();

Console.WriteLine(“Completed Updating the database”);

}

Now that’s cool isn’t it????

Download the code

Popularity: 27% [?]

OPENXML, SQL, SQL Server 2008, Table Valued Parameters, UDT

This post was written by:

Aneef Fashir - who has written 32 posts on Aneef.Net.

Software Engineer, Sri Lanka.

Contact the author

10 Comments For This Post

mike Says:
December 24th, 2007 at 4:31 am

thats interesting

Carl Says:
March 26th, 2008 at 8:25 pm

Its just what i am trying to do but im using vb and i keep getting a error saying there is no mapping between the datatable and the database ???

i’ve downloaded your code and your doing nothing different than me, So any ideas ???

Many Thanks
Carl

Aneef Fashir Says:
March 27th, 2008 at 4:35 am

Hi Carl, did u check your database columns, and their types, and see whether they match with the columns and types of the datatable.

And are you sure you are using SQL Server 2008 November CTP or later. because passing datatables is not supported prior versions.

mb Says:
July 11th, 2008 at 2:03 pm

FINALLY!!!! A real sample!!! All other examples always leave out the client code part of the equation.

This is awesome, your example got me started – the key being the need to convert entities into the datatable… I assume your preparedatatable() function simply creates the datatable with the same types as your UDT.

Now what we need next is the ability to generate the design time database from the UDT automatically to have it typed beforehand.. but we can worry about that later, I’m just thrilled now to have this working… no more XML parsing! yeeehaaa!!!

borith Says:
February 5th, 2009 at 10:08 am

cool, i am looking for this for weeks. i’ll try this.
thanks

Ryan Daulton Says:
June 16th, 2009 at 2:09 am

Hi Aneef,

I like your example very much! I’ve been looking for a Table UDT example for a long time. I tried your solution, however, the difference with my code is that my table type has the first column as an Identity column.
So when I try to execute the InsertFileDetails SP, C# VS 2008 debug says “Insert into IDENTITY column isn’t allowed on table variables.” How do I fix this? Here is a snippet of my code:
”
DataTable dt = new DataTable();
String[] header = myStringArray[0].Split(‘|’);

DataRow dr = dt.NewRow();
DataColumn dc = new DataColumn();
dc.DataType = typeof(Int32);
dc.ColumnName = “NumCols”;

dt.Columns.Add(dc);
DataColumn dc2 = new DataColumn();
dc2.DataType = typeof(string);
dc2.ColumnName = “strRow”;

dt.Columns.Add(dc2);
dt.Rows.Add(myStringArray[1]);
”

But the exception doesn’t get caught until I exec the ExecuteNonQuery function.

Thanks!

Aneef Fashir Says:
June 16th, 2009 at 5:11 am

Hi Ryan,

If you supply a value for an identity column in a table-valued parameter, you must issue the SET IDENTITY_INSERT statement for the session.

Hope it Helps

Ryan Daulton Says:
June 16th, 2009 at 10:43 am

Thanks Aneef,

But is there any way to have identity column autoincrement without me changing IDENTITY_INSERT? Otherwise, how do i get the IDENTITY_INSERT value to remain on?
I tried setting the IDENTITY_INSERT to ON from SQL Management Studio:
”
SET IDENTITY_INSERT ParentTable ON
GO
DECLARE @InputFile parseInputFile
INSERT INTO @InputFile(NumCols, strRow) VALUES (1, ‘A3|BB|C|DDD’)
select * from @InputFile
”
But then compiler returned following error:

”
Msg 1077, Level 16, State 1, Line 2
INSERT into an identity column not allowed on table variables.
”

And this is using same table UDT, parseInputFile, which is defined as:
”
CREATE TYPE [dbo].[parseInputFile] AS TABLE(
[NumCols] [int] IDENTITY(1,1) NOT NULL,
[strRow] [varchar](500) NOT NULL,
PRIMARY KEY CLUSTERED
”

Since I’m getting this error in Management Studio, I know it won’t work in VS 2008 either. However, I did try this in VS 2008 and I am still getting same exception. So what exactly should I change?

Aneef Fashir Says:
June 16th, 2009 at 11:59 am

Ryan,

Try doing this in your SQL Statement:

SET IDENTITY_INSERT ON

at the beginning of your SQL and

SET IDENTITY_INSERT OFF

because if you read the following : it seems we need to set this for each session of update when we use table valued parameters, and Identity columns. update me if it works. I haven’t tested it as I’M at work right now.

http://msdn.microsoft.com/en-us/library/bb675163.aspx
Thanks

Adam Says:
December 1st, 2009 at 8:05 pm

I know this is an old comment string and an even older article, but the solution si simply to not insert into the identity column.

INSERT INTO @InputFile(strRow) VALUES (‘A3|BB|C|DDD’)

If this is the first record in the table it will populate with 1. If you then ran this a second time you would get a second row inserted with the same “strRow” value, but a 2 in the “numCols” column. The identity values need to be left out of the stored proc inserting into the table. If they need to be added you would have to do the part of setting identity insert off/on for the run. The tablein your code would not even need the identity column included.

mike Says:
December 24th, 2007 at 4:31 am
thats interesting
Carl Says:
March 26th, 2008 at 8:25 pm
Its just what i am trying to do but im using vb and i keep getting a error saying there is no mapping between the datatable and the database ???

i’ve downloaded your code and your doing nothing different than me, So any ideas ???

Many Thanks
Carl
Aneef Fashir Says:
March 27th, 2008 at 4:35 am
Hi Carl, did u check your database columns, and their types, and see whether they match with the columns and types of the datatable.

And are you sure you are using SQL Server 2008 November CTP or later. because passing datatables is not supported prior versions.
mb Says:
July 11th, 2008 at 2:03 pm
FINALLY!!!! A real sample!!! All other examples always leave out the client code part of the equation.

This is awesome, your example got me started – the key being the need to convert entities into the datatable… I assume your preparedatatable() function simply creates the datatable with the same types as your UDT.

Now what we need next is the ability to generate the design time database from the UDT automatically to have it typed beforehand.. but we can worry about that later, I’m just thrilled now to have this working… no more XML parsing! yeeehaaa!!!
borith Says:
February 5th, 2009 at 10:08 am
cool, i am looking for this for weeks. i’ll try this.
thanks
Ryan Daulton Says:
June 16th, 2009 at 2:09 am
Hi Aneef,

I like your example very much! I’ve been looking for a Table UDT example for a long time. I tried your solution, however, the difference with my code is that my table type has the first column as an Identity column.
So when I try to execute the InsertFileDetails SP, C# VS 2008 debug says “Insert into IDENTITY column isn’t allowed on table variables.” How do I fix this? Here is a snippet of my code:
”
DataTable dt = new DataTable();
String[] header = myStringArray[0].Split(‘|’);

DataRow dr = dt.NewRow();
DataColumn dc = new DataColumn();
dc.DataType = typeof(Int32);
dc.ColumnName = “NumCols”;

dt.Columns.Add(dc);
DataColumn dc2 = new DataColumn();
dc2.DataType = typeof(string);
dc2.ColumnName = “strRow”;

dt.Columns.Add(dc2);
dt.Rows.Add(myStringArray[1]);
”

But the exception doesn’t get caught until I exec the ExecuteNonQuery function.

Thanks!
Aneef Fashir Says:
June 16th, 2009 at 5:11 am
Hi Ryan,

If you supply a value for an identity column in a table-valued parameter, you must issue the SET IDENTITY_INSERT statement for the session.

Hope it Helps
Ryan Daulton Says:
June 16th, 2009 at 10:43 am
Thanks Aneef,

But is there any way to have identity column autoincrement without me changing IDENTITY_INSERT? Otherwise, how do i get the IDENTITY_INSERT value to remain on?
I tried setting the IDENTITY_INSERT to ON from SQL Management Studio:
”
SET IDENTITY_INSERT ParentTable ON
GO
DECLARE @InputFile parseInputFile
INSERT INTO @InputFile(NumCols, strRow) VALUES (1, ‘A3|BB|C|DDD’)
select * from @InputFile
”
But then compiler returned following error:

”
Msg 1077, Level 16, State 1, Line 2
INSERT into an identity column not allowed on table variables.
”

And this is using same table UDT, parseInputFile, which is defined as:
”
CREATE TYPE [dbo].[parseInputFile] AS TABLE(
[NumCols] [int] IDENTITY(1,1) NOT NULL,
[strRow] [varchar](500) NOT NULL,
PRIMARY KEY CLUSTERED
”

Since I’m getting this error in Management Studio, I know it won’t work in VS 2008 either. However, I did try this in VS 2008 and I am still getting same exception. So what exactly should I change?
Aneef Fashir Says:
June 16th, 2009 at 11:59 am
Ryan,

Try doing this in your SQL Statement:

SET IDENTITY_INSERT ON

at the beginning of your SQL and

SET IDENTITY_INSERT OFF

because if you read the following : it seems we need to set this for each session of update when we use table valued parameters, and Identity columns. update me if it works. I haven’t tested it as I’M at work right now.

http://msdn.microsoft.com/en-us/library/bb675163.aspx
Thanks
Adam Says:
December 1st, 2009 at 8:05 pm
I know this is an old comment string and an even older article, but the solution si simply to not insert into the identity column.

INSERT INTO @InputFile(strRow) VALUES (‘A3|BB|C|DDD’)

If this is the first record in the table it will populate with 1. If you then ran this a second time you would get a second row inserted with the same “strRow” value, but a 2 in the “numCols” column. The identity values need to be left out of the stored proc inserting into the table. If they need to be added you would have to do the part of setting identity insert off/on for the run. The tablein your code would not even need the identity column included.

Aneef.Net