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!