Executing multiple SQL statements as one against SQL Server

Executing multiple SQL statements

The first variation uses SqlCommand.ExecuteNonQuery to execute two separate SQL statements against the test table. The first one updates the field somevalue by one and the second by two. The method looks like:

      /// <summary>
      /// Executes two separate updates against the the connection
      /// </summary>
      /// <param name="connectionString">Connection string to use</param>
      /// <param name="generateError">Should the statement generate an error</param>
      /// <returns>True if succesful</returns>
      public static bool ExecuteMultipleUpdates(string connectionString, bool generateError = false) {
         System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection();
         System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand();
         int rowsAffected;

         connection.ConnectionString = connectionString;
         command.CommandText = @"
UPDATE MultiStatementTest SET somevalue = somevalue + 1;
UPDATE MultiStatementTest SET" + (generateError ? "WONTWORK" : "") + " somevalue = somevalue + 2;";
         command.CommandType = System.Data.CommandType.Text;
         command.Connection = connection;

         try {
            connection.Open();
            rowsAffected = command.ExecuteNonQuery();
         } catch (System.Exception exception) {
            System.Windows.MessageBox.Show(exception.Message, "Error occurred");
            return false;
         } finally {
            command.Dispose();
            connection.Dispose();
         }
         System.Windows.MessageBox.Show(string.Format("{0} rows updated",
            rowsAffected, "Operation succesful"));

         return true;
      }

So the CommandText property contains all the statements that are going to be executed in this batch. The statements are separated by a semicolon.

After the batch has been executed the rows have been updated twice so the contents of the table look something like:

id      somevalue
---     ---------
1       857
2       76
3       735
4       549
5       270 

One important thing to notice is that the amount of affected rows returned by the ExecuteNonQuery is 10. There were five rows in the table and each one of them got updated twice so the total amount of updates is 10. So even with batches it's possible to check that correct amount of rows got updated regardless which statement makes the update.
Executing two SELECT statements using a data reader

The next test is to execute two different SELECT statements and read the results using a SqlDataReader class. The method is:

      /// <summary>
      /// Executes two separate select statements against the the connection using data reader
      /// </summary>
      /// <param name="connectionString">Connection string to use</param>
      /// <param name="generateError">Should the statement generate an error</param>
      /// <returns>True if succesful</returns>
      public static bool ExecuteReader(string connectionString, bool generateError = false) {
         System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection();
         System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand();
         System.Data.SqlClient.SqlDataReader dataReader;
         System.Text.StringBuilder stringBuilder;
         bool loopResult = true;

         connection.ConnectionString = connectionString;
         command = new System.Data.SqlClient.SqlCommand();
         command.CommandText = @"
SELECT somevalue FROM MultiStatementTest WHERE somevalue%2 = 1;
SELECT somevalue FROM MultiStatementTest " + (generateError ? "WONTWORK" : "WHERE") + " somevalue%2 = 0;";
         command.CommandType = System.Data.CommandType.Text;
         command.Connection = connection;

         try {
            connection.Open();
            dataReader = command.ExecuteReader();
            while (loopResult) {
               stringBuilder = new System.Text.StringBuilder();
               while (dataReader.Read()) {
                  stringBuilder.AppendLine(dataReader.GetInt32(0).ToString());
               }
               System.Windows.MessageBox.Show(stringBuilder.ToString(), "Data from the result set");
               loopResult = dataReader.NextResult();
            }

Read more: Codeproject
QR:

Posted via email from Jasper-net