I have a huge list of INSERT INTO ...
strings. Currently I run them with:
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
foreach (var commandString in sqlCommandList)
{
SqlCommand command = new SqlCommand(commandString, connection);
command.ExecuteNonQuery();
}
}
I see that each ExecuteNonQuery()
also executes commit.
To run a query with multiple statements, ensure that each statement is separated by a semicolon; then set the DSQEC_RUN_MQ global variable to 1 and run the query. When the variable is set to zero, all statements after the first semicolon are ignored.
The addBatch() method of Statement, PreparedStatement, and CallableStatement is used to add individual statements to the batch. The executeBatch() is used to start the execution of all the statements grouped together.
Semicolon is the standard way to separate each SQL statement in database systems that allow more than one SQL statement to be executed in the same call to the server.
Its recommended to use SQL transaction in case you are executing Multiple queries in one thread , you can have it like this :
SqlTransaction trans;
try
{
SqlConnection connection = new SqlConnection(connectionString);
connection.Open();
trans = connection.BeginTransaction();
foreach (var commandString in sqlCommandList)
{
SqlCommand command = new SqlCommand(commandString, connection,trans);
command.ExecuteNonQuery();
}
trans.Commit();
}
catch (Exception ex) //error occurred
{
trans.Rollback();
//Handel error
}
You might probably gain some performance by using just one single transaction and command, as follows:
using (SqlConnection connection = new SqlConnection(connectionString))
{
try
{
connection.Open();
using (SqlTransaction trans = connection.BeginTransaction())
{
using (SqlCommand command = new SqlCommand("", connection,trans))
{
command.CommandType = System.Data.CommandType.Text;
foreach (var commandString in sqlCommandList)
{
command.CommandText = commandString;
command.ExecuteNonQuery();
}
}
trans.Commit();
}
}
catch (Exception ex) //error occurred
{
//Handel error
}
}
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With