Database Reference
In-Depth Information
Listing 12-17.
Inserting a batch of rows: Using attribute-centric XML
create proc dbo.InsertDataXmlAttributeCentric
(
@Data xml
)
as
-- @Data is in the following format:
-- <Rows><R ID="{0}" C1="{1}" C2="{2}"..C20="{20}"/></Rows>
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
rows.n.value('@ID', 'int')
,rows.n.value('@C1', 'varchar(20)')
,rows.n.value('@C2', 'varchar(20)')
/* other 17 columns */
,rows.n.value('@C20', 'varchar(20)')
from
@Data.nodes('//Rows/R') rows(n)
The fourth method uses a
SqlBulkCopy
.Net class with
DataTable
as the source using row-level locks. The code
for this is shown in Listing 12-18.
Listing 12-18.
Inserting a batch of rows: Using SqlBulkCopy .Net class
TruncateTable();
using (SqlConnection conn = GetConnection())
{
/* Creating and populating DataTable object with dummy data */
DataTable table = new DataTable();
.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");
/* Saving data into the database */
using (SqlBulkCopy bc = new SqlBulkCopy(conn))
{
bc.BatchSize = packetSize;
bc.DestinationTableName = "dbo.Data";
bc.WriteToServer(table);
}
}
Finally, the last method uses table-valued parameters. Listing 12-19 shows the T-SQL code and Listing 12-20
shows the .Net part of the implementation.
