0

If i face null reference exception in code like that :

  DBConnection ifx_conn = new DBConnection(con, false);
  Dictionary<string, string> paramList1 = new Dictionary<string, string>();
  paramList1.Add("from_date", from_date.ToShortDateString());
  paramList1.Add("to_date", to_date.ToShortDateString());


  string cmdText = "select * from permission where emp_num in( " + emplyeeRange + " ) and perm_date>=? and perm_date <=?";
  DataTable permissionDT = ifx_conn.Return_DataTable(cmdText, CommandType.Text, paramList1);//The Exception 


NOTES :

  • This's a part of a long method .
  • I use the same connection ifx_conn with multiple queries.
  • This method exist in loop of 10 iterations .
  • Sometimes it works without exceptions and sometimes after a while i get Null Reference Exception .

MY DBConnection :

public class DBConnection
    {
        #region parameters and properties

            IfxConnection connection;
            IfxCommand command = new IfxCommand();
            IfxTransaction m_current_trans;
            IfxDataReader DR;
            IfxParameter param;
            ConnectionState connectionstate;
            string connection_name;
            //CONSTANT FOR GENERAL EXCEPTION
            int ExCodeConst = -111111;

            public enum stmtOrder
            {
                First = 1,
                inBetween = 2,
                Last = 3,
            }
            public IfxConnection Connection
            {
                get { return connection; }
                set { connection = value; }
            }
            public IfxTransaction current_trans
            {
                get { return m_current_trans; }
                set { m_current_trans = value; }
            }
            public ConnectionState connectionState
            {
                get { return connectionstate; }
                set { connectionstate = value; }
            }
            public string ConnectionName
            {
                get { return connection_name; }
                set { connection_name = value; }
            }

        #endregion

        #region Connection Creation and handeling

                private void Set_Isolation(bool with_transaction)
                {
                    try
                    {
                        Open_Connection();
                        command = new IfxCommand("SET ISOLATION TO DIRTY READ");
                        command.Connection = connection;
                        command.ExecuteNonQuery();
                        if (!with_transaction)
                        {
                            connection.Close();
                            connectionstate = ConnectionState.Closed;
                        }
                    }
                    catch (Exception ex)
                    {
                        ErrMapping.WriteLog(ex.Message);
                    }
                }
                private void Open_Connection()
                {
                    try
                    {
                        if (connection.State == ConnectionState.Closed)
                        {
                            connection.Open();
                            connectionstate = ConnectionState.Open;
                        }

                    }
                    catch (Exception ex)
                    {
                        ErrMapping.WriteLog(ex.Message);
                    }
                }

                /// <summary>
                /// constructor
                /// </summary>
                /// <param name="ConnectionName">Connection Name in your application web.config file in ConnectionStrings section  </param>
                /// <param name="with_transaction">True  : if you will use transaction
                ///                                False : if you will not use transaction </param>
                public DBConnection(string ConnectionName, bool with_transaction)
                {
                    connection = new IfxConnection(ConfigurationManager.ConnectionStrings[ConnectionName].ConnectionString);
                    this.connection_name = ConfigurationManager.ConnectionStrings[ConnectionName].Name;
                    command = new IfxCommand();
                    Set_Isolation(with_transaction);
                }
                /// <summary>
                /// Begin Transaction
                /// </summary>
                public void Begin_Transaction()
                {
                    if (this.connection.State == ConnectionState.Open)
                    {
                        this.current_trans = this.connection.BeginTransaction(IsolationLevel.Serializable);
                    }
                }
                /// <summary>
                /// Close Connection
                /// </summary>
                public void Close_Connection()
                {
                    if (connection!= null && connection.State == ConnectionState.Open)
                    {
                        connection.Close();
                        connectionstate = ConnectionState.Closed;
                    }
                }
                /// <summary>
                /// Destructor
                /// </summary>
                 ~DBConnection()
                {
                    Close_Connection();
                     if(connection!= null)
                    connection.Dispose();
                }
        #endregion
  }

What may cause this exception ?and how to resume after failure ?

Anyname Donotcare
  • 11,113
  • 66
  • 219
  • 392
  • Sure that the exception isn't in `Return_DataTable`, can you show it? – Tim Schmelter Dec 13 '16 at 08:27
  • Connections are null because you don't set them. Where is the exception? Where is it thrown? Post *only* the relevant code, and the *full* exception, including the call stack that shows where it was thrown. You can get it with `Exception.ToString()`. – Panagiotis Kanavos Dec 13 '16 at 08:28
  • 2
    PS why are you creating a class called DbConnection? This is asking for serious trouble - that's the name of the abstract class from which all provider-specific connections should inherit – Panagiotis Kanavos Dec 13 '16 at 08:29
  • @TimSchmelter : yeah the exception appear in `ifx_conn` i use it with multiple queries and this happens after a long time in one of iterations . – Anyname Donotcare Dec 13 '16 at 08:29
  • 1
    @AnynameDonotcare why are you using the same name as ADO.NET's abstract connection class? Apart from the fact that IBM probably never bothered to create an ADO.NET 2.0 provider. Even so, you should implement ADO.NET's classes, not invent your own – Panagiotis Kanavos Dec 13 '16 at 08:30
  • @PanagiotisKanavos : i will run it again to get the exception details – Anyname Donotcare Dec 13 '16 at 08:31
  • @PanagiotisKanavos : it's a library used from all developers in the company i work in – Anyname Donotcare Dec 13 '16 at 08:32
  • Apart from the naming conflict, you should better not use such helper classes. They often introduce nasty issues. Informix by default uses connection pooling, so you should always close/dispose the connection as soon as possible. But these helper methods and the encapsulated connection suggest a different usage. It even doesnt implement `IDispoable` so you can't use the `using`-statement anymore. – Tim Schmelter Dec 13 '16 at 08:32
  • @AnynameDonotcare your code just delegates to IfxConnection without adding any functionality, only making the code more brittle. If it's a company-wide library - that's bad. Tim already explained *some* the serious problems, beyond the current one, that it isn't robust enough – Panagiotis Kanavos Dec 13 '16 at 08:33
  • @TimSchmelter : Could you recommend some guides to this code so that i can resolve this problem please – Anyname Donotcare Dec 13 '16 at 08:34
  • @AnynameDonotcare the loss of connection pooling is a *serious* problem with old databases like Informix, as connections are rather more expensive compared to modern databases. And it's just bad code to use a global connection – Panagiotis Kanavos Dec 13 '16 at 08:35
  • @PanagiotisKanavos : yeah it's a company-wide library , Could you recommend some tips to fix this problem please – Anyname Donotcare Dec 13 '16 at 08:36
  • 1
    @AnynameDonotcare: another bad thing: you're passing the sql parameters as `Dictionary` to the method. So everything is treated as string even if it's a `DateTime`(as in this case). That's asking for even more troubles. If i would help to fix your class i would delete it. Use the `using`-statement wherever you need to access the database on everything implementing `IDisposable`(f.e. the connection). [Read](http://stackoverflow.com/questions/9705637/executereader-requires-an-open-and-available-connection-the-connections-curren/9707060#9707060) – Tim Schmelter Dec 13 '16 at 08:37
  • @AnynameDonotcare not without the exception, or even knowing where the exception occurred. Although a NullReferenceException is crystal-clear. You have a null field or variable. – Panagiotis Kanavos Dec 13 '16 at 08:39
  • @AnynameDonotcare to put it another way. With the exception you know where the problem is immediately. With 100 lines of code, you can only guess. Have you tried *debugging* this code? – Panagiotis Kanavos Dec 13 '16 at 08:40
  • 1
    @AnynameDonotcare and please, listen to Tim, or any experienced .NET developer for that matter. This class *is* the bug. It adds one bad idea on top of another, without adding *ANYTHING* on top of ADO.NET. – Panagiotis Kanavos Dec 13 '16 at 08:44
  • 1
    And that `DIRTY READ` is simply criminal negligence. It also means that you have serious blocking issues caused by eternal connections that your company covers up by *disabling* transactions. The `Serializable` setting doesn't prevent transactions that work on `DIRTY READ` from reading dirty data. – Panagiotis Kanavos Dec 13 '16 at 08:45
  • @TimSchmelter Really thanks a lot for your advices – Anyname Donotcare Dec 13 '16 at 09:02
  • @PanagiotisKanavos : Really Thanks a lot , i'm grateful – Anyname Donotcare Dec 13 '16 at 09:02

0 Answers0