Trusted answers to developer questions
Trusted Answers to Developer Questions

Related Tags

sql
transaction
c#
databases

C# select using TransactionScope

Educative Answers Team

TransactionScope in C# makes multiple tasks seem like one giant task. This is achieved because, with transaction scope, either all the tasks successfully execute or none do. If even a single task fails, the whole transaction rolls back.

SELECT is used in SQL to query a table. It can also be used with Transaction Scope to execute multiple SELECT commands and ensure that all of them execute successfully.

svg viewer

Code

try
{
  using (TransactionScope scope = new TransactionScope())
  {
      string connString1 = "..."; // Make appropriate connection string here
      using (conn1 = new SqlConnection(connString1))
      {   
          // Creating Select command
          MySQLCommand command1 = conn1.CreatCommand();
          command1.CommandText = "SELECT <name_of_column> FROM <name_of_table> WHERE <specific_row>";

          conn1.Open();

          // Executing Select Command
          MySQLDataReader ReadCommand1 = command1.ExecuteReader();

          // Reading until endoffile
          while(ReadCommand1.Read())
          {
            Console.WriteLine(ReadCommand1["<name_of_column>"].ToString());
          }

          // Pauses until a key is pressed
          Console.ReadKey(true);
                    
          // If we reach here, means that above statements succeded.

          string connString2 = "..." // Make appropriate connection string here
          using (conn2 = new SqlConnection(connString2))
          {
            // Creating Select command
            MySQLCommand command2 = conn2.CreatCommand();
            command2.CommandText = "SELECT <name_of_column> FROM <name_of_table> WHERE <specific_row>";

            conn2.Open();

            // Executing Select Command
            MySQLDataReader ReadCommand2 = command2.ExecuteReader();

            // Reading until endoffile
            while(ReadCommand2.Read())
            {
              Console.WriteLine(ReadCommand2["<name_of_column>"].ToString());
            }

            // Pauses until a key is pressed
            Console.ReadKey(true);
          }
      }

      scope.Complete();
  }
}

catch(TransactionAbortedException ex)
{
  string ErrorString = String.Format("Error Message: {0}" , ex.Message); 
  Console.WriteLine(ErrorString);
}

string SuccessString = String.Format("Tasks Completed"); 
Console.WriteLine(SuccessString);

The above code executes two SELECT commands on two separate SQL connections. If both of the SELECT commands, command1 and command2, execute successfully, then the tasks are carried through. Otherwise, it rolls back.

RELATED TAGS

sql
transaction
c#
databases
Copyright ©2022 Educative, Inc. All rights reserved
RELATED COURSES

View all Courses

Keep Exploring