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

  • Question

  • 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.

    Friday, April 3, 2020 2:41 PM

All replies

  • Hello,

    1. Which object is not set to an instance of an object.
    2. If you think the connection is the problem then refactor the code and create the connection in the code shown.
    3. Either remove the empty catch or do something useful like append to a text based log file.

    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange


    Friday, April 3, 2020 2:47 PM
    Moderator
  • Did you check e, not only e1? Maybe BeginTransaction failed.

    Friday, April 3, 2020 4:03 PM
  • A class must be instanced into an object, and the object has memory allocated for it. If the object has not be instanced, then the object has a null value. When code tries to reference an object that has not been instanced, then an exception is going to be thrown.

    Any data that you are trying to save to the database that doesn't allow null values and the data is null can cause the exception to be thrown,  or a container object that null data is being saved to, container object like a dataset with datatable, the DT column doesn't all null is going to cause the exception.

    Everything in .NET is based on an object, the object has to be instanced and living in memory is the basic explanation.

    You need to use the Visual Stuido debugger, start single stepping in code unit the code blows up and use Quickwatch to discover what object is null and why.

    OO is OO Java or .NET.

    https://alfredjava.wordpress.com/2008/07/08/class-vs-object-vs-instance/


    Friday, April 3, 2020 4:07 PM
  • Hi Shirley,

    >>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<<

    First, that is a bad idea. Always recreate the connection, or you won't actually get the benefit of connection pooling.

    Also, I recommend using TransactionScope rather than SQL Transactions. I think it's a lot cleaner. You should also use "using blocks". 

    First a easy way to get a good instance of TransactionScope, since instantiating a new TransactionScope with no parameters is too restrictive):

    public class Utils
    {
        /// <summary>
        /// The reason for this method to return a TransactionScope is because the default IsolationLevel 
        /// when not specified is Serializable, which is the most restrictive level and will cause all kinds
        /// of deadlock problems with Sql Server!!
        /// 
        /// We can add more overloads later if we want more options.
        /// The TransactionScoope that gets returned here is Required and IsolationLevel.ReadCommitted.
        /// </summary>
        /// <returns></returns>
        public static TransactionScope GetTransactionScope()
        {
            return new TransactionScope(TransactionScopeOption.Required, new TransactionOptions(){IsolationLevel = IsolationLevel.ReadCommitted});
        }
    }

    And here's a refactored version of your code:

    string MyConnectionString = "Pooling=true;Max Pool Size=1000;Min Pool Size=0;uid=sa;server=10.110.102.55;database=Test;connect Timeout=5";
    string sqlStatements = "INSERT INTO [dbo].[Data](DataID,DataText) VALUES(N'{DB5B8377-3D22-5667-8903-90F0DE9D09FD}',N'Data Message')";
    
    using (TransactionScope scope = Utils.GetTransactionScope())
    {
        try
        {
            // Not sure why you're using DbConnection instead of SqlConnection??
            using (SqlConnection dbConn = new SqlConnection(MyConnectionString))
            {
                // Not sure why you're using DbCommand instead of SqlCommand??
                SqlCommand _dbCommand = new SqlCommand(sqlStatements, dbConn);
                _dbCommand.CommandTimeout = 0;
                _dbCommand.ExecuteNonQuery();
                
                scope.Complete();
            }
        }
        catch(Exception ex)
        {
            // log the Exception if you wish
            // re-throw the Exception if you wish
            
            // the transaction will be rolled back, since the scope.Complete() was not executed
        }
    }

    Hope that helps.    

    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com


    Friday, April 3, 2020 4:21 PM
    Moderator