Using C # transactions to insert data into the database and return "Object reference not set to an instance of an object"

[Hi, Shirley]

DbConnection _dbConn = new SqlConnection();
_dbConn.ConnectionString = "Pooling=true;Max Pool Size=1000;Min Pool Size=0;uid=sa;server=10.110.102.55;database=Test;connect Timeout=5";
_dbConn.Open();

I used above to create a DB connection and refresh it at a frequency of 1s. When data is inserted, I use this already created DB connection.

string sqlStatements = "INSERT INTO [dbo].[Data](DataID,DataText) VALUES(N'{DB5B8377-3D22-5667-8903-90F0DE9D09FD}',N'Data Message')";
DbCommand _dbCommand = new SqlCommand(sqlStatements, _dbConn as SqlConnection);
if(_dbCommand == null)
{
return;
}
try
{
_dbCommand.Transaction = _dbConn.BeginTransaction();
_dbCommand.CommandText = sqlStatements;
_dbCommand.CommandTimeout = 0;
_dbCommand.ExecureNonQuery();
_dbCommand.Transaction.Commit();
}
catch(Exception e)
{
try
{
_dbCommand.Transaction.Rollback();
}
catch(Exception e1)
{
}
}

The problem is that when I insert data into database using this DB connection sequentially in the same thread,sometime appears e1.Message = Object reference not set to an instance of an object.


And, at this time,

HasLocalTransaction = false and HasLocalTransactionFromAPI = false in _dbConnection.

Does this mean this DB connection has failed? Or is it just the transaction is unusable? This has troubled me a lot.

Continue reading...