I would like to execute multiple select, insert, update statements in one batch, using one SqlCommand, for improved performance.
The CommandText is assembled dynamically from multiple predefined insert, update statements (SqlParams are assembled dynamically, too). The inserts sometimes works on tables with autoid fields, so getting back the last inserted id is necessary. I use 'SELECT SCOPE_IDENTITY()' for that.
A typically assembled CommandText looks like this:
INSERT xxxxx ;
SELECT SCOPE_IDENTITY();
INSERT yyyyy ;
SELECT SCOPE_IDENTITY();
INSERT zzzzz ;
UPDATE xxxxx ;
INSERT wwwww ;
SELECT SCOPE_IDENTITY();
I would like to retreive all the scope_identity values, as well as the number of affected rows. ExecuteNonQuery and ExecuteScalar is not usable, because they give back only single values. ExecuteReader is better, it gives back information from the selects, (gives 3 results in the example above), but I needs the affected rows as well (for checking the execution).
public IList Execute()
{
var results = new ArrayList();
using (var command = connection.CreateCommand())
{
command.Transaction = transaction;
var commandText = new StringBuilder();
.... building commandtext dynamically
command.CommandText = commandText.ToString();
command.Parameters.AddRange(... adding paramters dynamically);
var reader = command.ExecuteReader();
do
{
if (reader.Read())
results.Add(reader.IsDBNull(0) ? null : reader.GetValue(0));
} while (reader.NextResult());
reader.Close();
}
return results;
}
Any ideas how to get it?
