is it possible to execute several Sql Inserts in the same batch the same way as its possible to execute several selects using SqlDataReader.NextResult()?

Question

i want to execute several inserts at the smame batch to speed up the program. so its possible to do the following?

Insert Into Table1 (a) Values (@a);
Insert Into Table2 (b Values (@b);
Insert Into Table3 (c) Values (@c);

and if its possible then how should i pass the parametrs to the Sql Parameters? basicly i want to do like this example link text

but for Inserts instead of selects

Answer 1

SqlCommand cmd = new SqlCommand(@"Insert Into Table1 (a) Values (@a);Insert Into Table2 (b Values (@b);Insert Into Table3 (c) Values (@c);", connection);

// Set your paramters values
SqlCommand.Parameters.Add(new SqlParameter("@a", aValue));
SqlCommand.Parameters.Add(new SqlParameter("@b", bValue));
SqlCommand.Parameters.Add(new SqlParameter("@c", cValue));

cmd.ExecuteNonQuery();

Answer 2

Another example:

var sql = StringBuilder();

sql.Append "Insert Table1(a)"
sql.AppendFormat "\nSelect @a"
sql.AppendFormat "\nUnion All Select @b"
sql.AppendFormat "\nUnion All Select @b"
sql.AppendFormat "\nUnion All Select @c"
...

// Set your paramters values
var cmd = new SqlCommand( sql.ToString() );
SqlCommand.Parameters.Add(new SqlParameter("@b", aValue));
SqlCommand.Parameters.Add(new SqlParameter("@b", bValue));
SqlCommand.Parameters.Add(new SqlParameter("@c", cValue));
...

cmd.ExecuteNonQuery();

The line breaks are not necessary. They are only there so the output is nicely formatted. If you put your values into a hashtable, you could even do it in a loop like so:

var values = new Hashtable();
values.Add("a", avalue);
values.Add("b", bvalue);
values.Add("c", cvalue);
...


var sql = StringBuilder();
var cmd = new SqlCommand();

foreach( var value in values )
{
    if (sql.Length == 0)
    {
     sql.Append "Insert Table1(columnName)"
     sql.AppendFormat "\nSelect @" + value.Key 
     cmd.Parameters.Add(new SqlParameter("@" + value.Key, value.Value));
    }
    else
    {
     sql.AppendFormat "\nUnion All Select @" + value.Key
     cmd.Parameters.Add(new SqlParameter("@" + value.Key, value.Value));
    }
}

cmd.CommandText = sql.ToString()
cmd.ExecuteNonQuery();