CODEWITHSUNDEEP
c#database
Shashikant Tantarpale
Shashikant Tantarpale

Reputation: 1

Does Transaction.Commit() closes the underlying connection as well. C#

After Transaction.Commit()` the connection property of my transactions gets set to null. I wonder what happens to the connection object, does it get Closed too?

I want to make sure the connection gets closed too. Please suggest.

So In Calling method I am coding as followed.

System.Data.SQLClient.SQLTransaction trans = null;
System.Data.SQLClient.SQLConnection oConn = null;
trans = oConn.GetIWSTransaction();

trans.commit();

and in GetIWSTransaction() as
Private System.Data.SQLClient.SQLTransaction m_oIWSTransaction = 
null;
Private System.Data.SQLClient.SQLConnection m_oIWSConnection = null;

if(this.m_oIWSConnection.State = System.Data.ConnectionState.Closed)
this.m_oIWSConnection.Open();
this.m_oIWSTransaction = this.m_oIWSConnection.BeginTransaction();

return this.m_oIWSTransaction;

Now the question is, does trans.commit() in calling method terminates the connection as well or it just sets it to null. If it just sets it to null then how can we handle connection closure?(any alternative than Using block)

Upvotes: 0

Views: 3163

Answers (1)

JHBonarius
JHBonarius

Reputation: 11271

No it does not. If you want to close the connection, the suggested way is to put the connection in a using block. E.g.

using (DbConnection db = GetDbConnection())
{
  db.Open();
}

The connection will get closed when the object is disposed.

edit: like Charlie said, the transaction should ideally also be enclosed in a using block.

You can even use a TransactionScope

#edit: If you are learning, follow a guide. E.g. this one.

Also, these kinds of low level SQL access (SqlClient and ADO.NET) is not suited for starters. You should look into leaning Entity Framework, or maybe Dapper (although the latter is already for more advanced/experienced developers).

Back to your code. It's design doesn't seem well organized. Try to follow the idiomatic design and keep things together.

And about the using blocks. System.Data.SqlClient.SqlConnection derives from DbConnection, thus is IDisposable. Same for SqlTransaction and DbTransaction and IDisposable.

So your code could look like this:

using System.Data.SqlClient;

using (SqlConnection connection = new SqlConnection(...connection string...))
{
    connection.Open();

    SqlCommand command = connection.CreateCommand();
    using (SqlTransaction transaction = connection.BeginTransaction("SampleTransaction"))
    {
        command.Transaction = transaction;
        command.CommandText = ...your SQL...;

        try
        {
            command.ExecuteNonQuery();
            transaction.Commit();
        }
        catch (Exception ex)
        {
            try
            {
                transaction.Rollback();
            }
            catch (Exception ex2)
            {
                // This catch block will handle any errors that may have occurred
                // on the server that would cause the rollback to fail, such as
                // a closed connection.
            }
        }
    }
}

Upvotes: 3

Related Questions