C# SqlTransaction.Commit throw exception because Connection is null

Question

I have a problem using SqlTransaction. Here's the code

SqlConnection conn = new SqlConnection(connectionString);
// assume that at this point I add try/catch and the connection is successfully opened.
conn.Open();
SqlTransaction trans = conn.BeginTransaction(IsolationLevel.RepeatableRead);

//.........
// Here I do my action with database, change it, commit, call ExecuteNonQuery, //ExecuteScalar,etc...

// And after that long processing with this transaction, I don't know why trans.Connection //is null. and therefore when I call trans.commit(), an exception was raised?


trans.commit();

Does anybody have any clues?

I assume this might be because of memory of database server was ate up because of too much connections. But I'm not sure.

How can I resolve this?

Is it real code? You have SqlConnection conn but then use con — x2., May 11 '12 at 10:40

score 1 · Answer 1 · edited May 11 '12 at 10:42

1

Seems like typo

con.BeginTransaction(IsolationLevel.RepeatableRead)

should be

conn.BeginTransaction(IsolationLevel.RepeatableRead)

edited May 11 '12 at 10:42

x2.

9,554
6
41
62

answered May 11 '12 at 10:41

Tilak

30,108
19
83
131

score 1 · Answer 2 · answered May 11 '12 at 10:42

1

make use of using

using (SqlConnection connection = new SqlConnection(connectionString))
    {
        connection.Open();
        Console.WriteLine("ServerVersion: {0}", connection.ServerVersion);
        Console.WriteLine("State: {0}", connection.State);
    }

answered May 11 '12 at 10:42

Pranay Rana

175,020
35
237
263

SqlConnection was successfully opened, and this bug occurs inconsitently, meaning normally my application runs well but sometimes, it throw exception. – Minh Vu May 11 '12 at 11:45

score 0 · Answer 3 · answered May 11 '12 at 10:45

0

use a using statement:

using (SqlConnection connection = new SqlConnection(connectionString))
{
//your code here
}

answered May 11 '12 at 10:45

Diego

34,802
21
91
134

score 0 · Answer 4 · edited May 23 '17 at 12:06

c# - SqlTransaction after catch transaction connection is null - Stack Overflow SqlTransaction after catch transaction connection is null

Edyn said that: Ran into a similar issue. In my case it was happening for a specific SqlException. Most exceptions would be caught and handled just fine, but whenever I got a conversion error (such as trying to convert a string to a number) it would automatically end the transaction.

In my solution, I rebuild the transacton if found the underlying connection is null. But it's a dirty work.

C# SqlTransaction.Commit throw exception because Connection is null

4 Answers4