SqlCommand, CommandType.Text and Parameters to make a big Batch

[Chris Bilson]

I keep telling myself I am thinking of ways to implement things that
could help make my application more efficient, but really just having
fun with ADO.NET and SQL Server.

I use DataSets to keep track of work users have done, until they are
ready to save their work and I need to call a stored procedure for each
row the user has changed in the DataSet.

Currently, this is done using one SqlCommand.Execute* (via a
DataAdapter) per row. What I was thinking was, what if I created a batch
with lots of parameters, for doing all the updates in one database call:

SqlCommand cmd = new SqlCommand();
StringWriter writer = new StringWriter();
int nParameterID = 0;
....
foreach (DataTable t in ds.GetChanges()) {
foreach (DataRow r in t.GetChanges().Rows) {

// determine the name of the stored proc to call
string storedProcName
= string.Format("usp_{0}_{1}_{2}",
r.DataRowState, ds.DataSetName,
t.TableName);

// start writing this line of the batch
writer.Write("exec {0} ", storedProcName);

// Get the set of params for this command from
// somewhere else, which creates a new collection // of SqlParameters.
ArrayList paramNames = new ArrayList();
foreach (SqlParameter p in
GetParamsForProcFromSomewhere(storedProcName)) {
cmd.Parameters.Add(p);
if (DataRowState.Deleted == r.DataRowState)
p.Value = r[p.Name.TrimStart("@"),
DataRowVersion.Original];
else
p.Value = r[p.Name.TrimStart("@")];


paramNames.Add(string.Format("@P{0}",
nParameterID++);
}

// Finish this line in the batch by adding all
// the params
writer.WriteLine(string.Join(", ",
(string[]) paramNames.ToArray(typeof(string)));
}
}

The only gotchas I have thought of so far are:
1. If the DataSet I am updating contains parent child rows, I need the
value of the parent's primary key before I can update the child row (if
they are inserts). I can work around this by reusing the same parameter
for those values, and have the insert proc for the parent row treat it
as an out parameter.

2. I use rowversions for optimistic concurrency. I need the
inserted/updated row selected back out by the stored proc doing the
updating. This works nicely with SqlDataReader's ability to return
multiple resultsets, but I need to be kind of smart about how I handle
updating the DataSet. SqlDataAdapter's Update wouldn't cut it, so I
think I would keep a vector of rows being updated, and reconcile them
when they come back.

Has anybody else ever tried something like this? Maybe I should get some
sleep instead. Thanks in advance to anyone who (encourages my madness ||
shoots me down in flames).


--Chris Bilson

 

[William \(Bill\) Vaughn]

This approach has been tried before and IIRC, successfully. There are a
number of custom CommandBuilders and DataAdapter configuration wizards that
do similar things. We're going to see more of these as 2.0 approaches as it
does nothing to fix the problems with the way that the Framework's
CommandBuilder handles concurrency. I would make sure to include logic to
deal with timestamp concurrency. Yes, dealing with identity is an issue, and
you still have to add parents first and delete children first. Once you add
parents, you need to return the new identity and update the links to the
children.

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

I keep telling myself I am thinking of ways to implement things that
could help make my application more efficient, but really just having
fun with ADO.NET and SQL Server.

I use DataSets to keep track of work users have done, until they are
ready to save their work and I need to call a stored procedure for each
row the user has changed in the DataSet.

Currently, this is done using one SqlCommand.Execute* (via a
DataAdapter) per row. What I was thinking was, what if I created a batch
with lots of parameters, for doing all the updates in one database call:

SqlCommand cmd = new SqlCommand();
StringWriter writer = new StringWriter();
int nParameterID = 0;
...
foreach (DataTable t in ds.GetChanges()) {
foreach (DataRow r in t.GetChanges().Rows) {

// determine the name of the stored proc to call
string storedProcName
= string.Format("usp_{0}_{1}_{2}",
r.DataRowState, ds.DataSetName,
t.TableName);

// start writing this line of the batch
writer.Write("exec {0} ", storedProcName);

// Get the set of params for this command from
// somewhere else, which creates a new collection // of SqlParameters.
ArrayList paramNames = new ArrayList();
foreach (SqlParameter p in
GetParamsForProcFromSomewhere(storedProcName)) {
cmd.Parameters.Add(p);
if (DataRowState.Deleted == r.DataRowState)
p.Value = r[p.Name.TrimStart("@"),
DataRowVersion.Original];
else
p.Value = r[p.Name.TrimStart("@")];


paramNames.Add(string.Format("@P{0}",
nParameterID++);
}

// Finish this line in the batch by adding all
// the params
writer.WriteLine(string.Join(", ",
(string[]) paramNames.ToArray(typeof(string)));
}
}

The only gotchas I have thought of so far are:
1. If the DataSet I am updating contains parent child rows, I need the
value of the parent's primary key before I can update the child row (if
they are inserts). I can work around this by reusing the same parameter
for those values, and have the insert proc for the parent row treat it
as an out parameter.

2. I use rowversions for optimistic concurrency. I need the
inserted/updated row selected back out by the stored proc doing the
updating. This works nicely with SqlDataReader's ability to return
multiple resultsets, but I need to be kind of smart about how I handle
updating the DataSet. SqlDataAdapter's Update wouldn't cut it, so I
think I would keep a vector of rows being updated, and reconcile them
when they come back.

Has anybody else ever tried something like this? Maybe I should get some
sleep instead. Thanks in advance to anyone who (encourages my madness ||
shoots me down in flames).


--Chris Bilson