I need to use transactions with a ADO.NET provider.
Below is a simple example of a connection, transaction and command being created. When I create a command using connection.CreateCommand()
do I need to assign the transaction to the command? Or, is the transaction set because I'm using connection.CreateCommand()
vs newing up a command object?
var connection = Database.GetConnection();
connection.Open();
var transaction = connection.BeginTransaction();
var command = connection.CreateCommand();
command.Transaction = transaction; // Is this line needed when using connection.CreateCommand()?
*Update*
When I test the reference of both objects, they are the same. I'd assume that means connection.CreateCommand()
is returning a command with the transaction assigned. Or maybe that is not a valid test.
using (var connection = Database.GetConnection())
{
connection.Open();
var transaction = connection.BeginTransaction();
var command = connection.CreateCommand();
if (object.ReferenceEquals(transaction, command.Transaction))
Debug.WriteLine("EQUAL");
}
The ADO Command object is used to execute a single query against a database. The query can perform actions like creating, adding, retrieving, deleting or updating records.
In ADO.NET, you control transactions with the Connection object. You can initiate a local transaction with the BeginTransaction method. Once you have begun a transaction, you can enlist a command in that transaction with the Transaction property of a Command object.
If you want to access a database multiple times, you should establish a connection using the Connection object. You can also make a connection to a database by passing a connection string via a Command or Recordset object. However, this type of connection is only good for one specific, single query.
The Command class is provided in several provider-specific varieties, including SqlCommand and OleDbCommand . To execute a Command , you use one of the Command object methods, including ExecuteNonQuery( ) , ExecuteReader( ) , and ExecuteScalar( ) , depending on the type of Command .
You have to explicitly set the transaction for each SqlCommand
instance. Here is the source code of System.Data.SqlClient.SqlConnection.cs
(Line: 782) of CreateCommand
:
new public SqlCommand CreateCommand() {
return new SqlCommand(null, this);
}
As you see; It pass null
for CommandText
and this
(itself) for SqlConnection
arguments.
Yes, the transaction and command need to be associated with one another.
Some redacted sample code:
// Connect to the database.
SqlConnection connection = new SqlConnection(Database.ConnectionString);
connection.Open();
// Start a transaction.
SqlCommand command = new SqlCommand();
command.Connection = connection;
command.Transaction = connection.BeginTransaction(System.Data.IsolationLevel.Serializable, "ryan");
// Delete any previously associated targets.
command.CommandType = System.Data.CommandType.StoredProcedure;
command.CommandText = "FirstSP";
command.Parameters.AddWithValue("@Id", this.Id);
command.ExecuteNonQuery();
// Add the specified targets to the product.
command.CommandText = "SecondSP";
command.Parameters.Add("@Id", SqlDbType.Int);
foreach (int Id in Ids)
{
command.Parameters["@Id"].Value = Id;
command.ExecuteNonQuery();
}
// Commit the transaction.
command.Transaction.Commit();
// Houseclean.
connection.Close();
If you use TransactionScope
you do not need to attach anything to the command object.
Just take a look at the example from the documentation of TransactionScope
.
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