SQL Server 2008 Table Valued Parameters

2 בדצמבר 2007

9 comments

SQL Server 2008 Table Valued Parameters

SQL Server 2008 Table Valued Parameters

A problem the data applications commonly have is when they have a grid of records that the clients creates (like a list of items in a store) and they want to insert in into the database in the most efficient way. The naive way is to insert the rows one by one, and another approach would be to create a stored procedure that takes a number of parameters with long strings or arrays of values and create a table from them. Another approach would be to use an Xml block parameter and parse it inside the procedure.

Assuming we have a Customers table:

create table Customers

(

    CustomerID    int not null primary key identity(1,1),

    Name          varchar(50) not null,

    City          varchar(50) null,

    Phone         varchar(10) null,

)

and a stored procedure that inserts customers rows:

create procedure CustomerInsert

    @Name        varchar(50),

    @City        varchar(50),

    @Phone       varchar(10)

as

    insert

    into Customers (Name, City, Phone)

    values (@Name, @City, @Phone)

go

We would go and call it for each customer row we want to insert:

using (SqlConnection conn = new SqlConnection("…"))

{

  SqlCommand cmd = conn.CreateCommand();

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

  cmd.CommandText = "dbo.CustomerInsert";

  cmd.Parameters.AddWithValue("@Name", (string)row["Name"]);

  cmd.Parameters.AddWithValue("@City", (string)row["City"]);

  cmd.Parameters.AddWithValue("@Phone", (string)row["Phone"]);

 

  conn.Open();

  cmd.ExecuteNonQuery();

}

Nowdays, we can use SQL Server 2008 Table Valued Parameters and solve this problem in the most efficient way, and still use the same ADO.Net API you use in your application.

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

create type CustomerTableType as table

(

    Name        varchar(50),

    City        varchar(50),

    Phone       varchar(10)

)

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

create procedure CustomersInsertMany

(

    @CustomersTable CustomerTableType readonly

)

as

    insert

    into   Customers (Name, City, Phone)

    select Name, City, Phone

    from   @CustomersTable;

To execute this procedure using script, I declare a new instance of the Table Valued Parameter type, and insert rows into it. Then I execute the store procedure in order to insert all the rows all together.

declare @customers_temp CustomerTableType

insert into @customers_temp values ('Customer #4', 'Jerusalem', '2343245')

insert into @customers_temp values ('Customer #5', 'Tel Aviv', '0987345')

insert into @customers_temp values ('Customer #6', 'Haifa', '275466')

 

execute CustomersInsertMany @customers_temp

For ADO.Net, the code really looks similar to the above. First I create a data table, and fill it with data:

// Create a data table, and provide its structure

DataTable customerTable = new DataTable();

customerTable.Columns.Add("Name", typeof(string));

customerTable.Columns.Add("City", typeof(string));

customerTable.Columns.Add("Phone", typeof(string));

 

// Fill with rows

customerTable.Rows.Add("Customer #1", "Berlin", "123");

customerTable.Rows.Add("Customer #2", "Paris", "567");

customerTable.Rows.Add("Customer #3", "London", "789");

Then, I call the above stored procedure and pass the data table as a parameter.

using (SqlConnection conn = new SqlConnection("…"))

{

  SqlCommand cmd = conn.CreateCommand();

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

  cmd.CommandText = "dbo.CustomersInsertMany";

  SqlParameter param = cmd.Parameters.AddWithValue("@CustomersTable", customerTable);

 

  conn.Open();

  cmd.ExecuteNonQuery();

}

Enjoy!

Add comment
facebook linkedin twitter email

9 comments

  1. Nir Kovalio2 בדצמבר 2007 ב 22:42

    פיצ'ר מעניין, היום אני עושה את זה עם BulkInsert.

  2. ламинат24 באוגוסט 2008 ב 7:59

    9yI'll thingk about it.3q I compleatly agree with last post. hjb
    ламинированный паркет 8a

  3. ламинат24 באוגוסט 2008 ב 12:21

    9aThank's for greate post.9w I compleatly agree with last post. mtf
    паркет 6x

  4. ламинат24 באוגוסט 2008 ב 21:03

    6dGood idea.4k I compleatly disagree with last post . lyo
    купить ламинат 0c

  5. ламинат24 באוגוסט 2008 ב 23:24

    4zGood idea.3q I compleatly agree with last post. hff
    ламинат 0o

  6. сайдинг25 באוגוסט 2008 ב 10:49

    5uI'll thingk about it.5l I compleatly agree with last post.
    сайдинг канадский 7k
    под сайдинг 7c

  7. Jose Garci29 בנובמבר 2008 ב 14:16

    HEllo,
    Could I get the DataSet.Update method to use table-valued parameters automatically ?

    How?

  8. Balaji14 במאי 2010 ב 14:23

    Excellent article. Very neatly explained.. It helped a lot for me… Thnx a lot buddy…

Comments are closed.