Database Reference
In-Depth Information
Listing 12-19.
Inserting a batch of rows: Table-valued parameters T-SQL code
create type dbo.tvpData as table
(
ID int not null,
Col1 varchar(20) not null,
Col2 varchar(20) not null,
/* Seventeen more columns: Col3 - Col19 */
Col20 varchar(20) not null,
primary key(ID)
)
go
create proc dbo.InsertDataTVP
(
@Data dbo.tvpData readonly
)
as
insert into dbo.Data(ID,Col1,Col2,Col3,Col4,Col5,Col6,Col7,Col8,Col9,Col10
,Col11,Col12,Col13,Col14,Col15,Col16,Col17,Col18,Col19,Col20)
select ID,Col1,Col2,Col3,Col4,Col5,Col6,Col7,Col8,Col9,Col10
,Col11,Col12,Col13, Col14,Col15,Col16,Col17,Col18,
Col19,Col20
from @Data
Listing 12-20.
Inserting a batch of rows: Table-valued parameters .Net code
TruncateTable();
using (SqlConnection conn = GetConnection())
{
/* Creating and populating DataTable object with dummy data */
DataTable table = new DataTable();
table.Columns.Add("ID", typeof(Int32));
for (int i = 1; i <= 20; i++)
table.Columns.Add("Col" + i.ToString(), typeof(string));
for (int i = 0; i < packetSize; i++)
table.Rows.Add(i, "Parameter: 1", "Parameter: 2", /* Other columns
*/ "Parameter: 20");
/* Calling SP with TVP parameter */
SqlCommand insertCmd = new SqlCommand("dbo.InsertDataTVP", conn);
insertCmd.Parameters.Add("@Data", SqlDbType.Structured);
insertCmd.Parameters[0].TypeName = "dbo.tvpData";
insertCmd.Parameters[0].Value = table;
insertCmd.ExecuteNonQuery();
}
I ran two series of tests measuring average execution time for the different methods and different batch sizes.
In the first test, the application ran on the same server as SQL Server. On the second test, the application connected to
SQL Server over a network. You can see the execution time for these two tests in milliseconds in Tables
12-1
and
12-2
.
