Database Reference
In-Depth Information
/* Running individual insert statements in the loop within explicit transaction */
using (SqlTransaction tran = conn.BeginTransaction(IsolationLevel.ReadCommitted))
{
try
{
insertCmd.Transaction = tran;
for (int i = 0; i < packetSize; i++)
{
insertCmd.Parameters[0].Value = i;
for (int p = 1; p <= 20; p++)
insertCmd.Parameters[p].Value =
"Parameter: " + p.ToString();
insertCmd.ExecuteNonQuery();
}
tran.Commit();
}
catch (Exception ex)
{
tran.Rollback();
}
}
The second method is to send the entire batch at once in an element-centric XML format using the stored
procedure to parse it. The .Net code is omitted, and the stored procedure is shown in Listing 12-16.
Listing 12-16.
Inserting a batch of rows: Using element-centric XML
create proc dbo.InsertDataXmlElementCentric
(
@Data xml
)
as
-- @Data is in the following format:
-- <Rows><R><ID>{0}</ID><C1>{1}</C1><C2>{2}</C2>..<C20>{20}</C20></R></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/text())[1]', 'int')
,rows.n.value('(C1/text())[1]', 'varchar(20)')
,rows.n.value('(C2/text())[1]', 'varchar(20)')
/* other 17 columns */
,rows.n.value('(C20/text())[1]', 'varchar(20)')
from
@Data.nodes('//Rows/R') rows(n)
The third method is very similar to the second, but it uses attribute-centric XML instead. The code for this is
shown in Listing 12-17.
