C# select using TransactionScope

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.

Free Resources

Copyright ©2025 Educative, Inc. All rights reserved