Main menu

SQL Server 2008 Table Valued Parameters

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

Aneef Fashir (38 Posts)

Software Architect @ Assette, Sri Lanka


  • mike

    thats interesting

  • mike

    thats interesting

  • Carl

    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

  • Carl

    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

  • http://www.aneef.net/ Aneef Fashir

    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.

  • http://www.aneef.net Aneef Fashir

    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

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

  • mb

    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

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

  • borith

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

  • Ryan Daulton

    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!

  • Ryan Daulton

    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!

  • http://www.aneef.net/ Aneef Fashir

    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

  • http://www.aneef.net Aneef Fashir

    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

    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?

  • Ryan Daulton

    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?

  • http://www.aneef.net/ Aneef Fashir

    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

  • http://www.aneef.net Aneef Fashir

    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

  • http://myhrfamily.net/ Adam

    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.

  • http://myhrfamily.net Adam

    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.

Tags

Archives