Closing a C# MySQL database connection leads to a "System.NullReferenceException"

Question

I am trying to implement a function to close my connection to a database. This function is named "closeDB()" and in the first draft contained one line of code:this.conn.Close(); Afterwards I implemented a simple select to try the database connection, which returned a list of available databases, as seen in this code:

List<string> brokers = new List<string>();

        connectToSpecificDB(database);

        String query = "SHOW DATABASES LIKE 'pricedata%'";
        using var cmd = new MySqlCommand(query, conn);
        cmd.Prepare();
        using MySqlDataReader rdr = cmd.ExecuteReader();

        while (rdr.Read())
        {
            brokers.Add(rdr.GetString(0));
            //Console.WriteLine(brokers[brokers.Count - 1]);
        }

        closeDB();

        return brokers;

The line of code that has raised the "System.NullReferenceException" is closeDB();. If I comment the line, the error does not pop up, however I am not certain if the connector would be available for further connections if it's not closed when I need it to. After some testing with good old Console.WriteLine, here's the new closeDB() function:

public void closeDB()
    {
        try
        {
            if (this.conn.State == ConnectionState.Open)
            {
                Console.WriteLine("Then close it!");
            }

            if (this.conn != null && this.conn.State == ConnectionState.Closed)
            {
                this.conn.Close();
            }
            else
            {
                Console.WriteLine("Why the fuck is it null?");
            }

        }
        catch (Exception e)
        {
            Console.WriteLine(e.Message);
        }
        finally
        {
            this.conn.Close();
        }
    }

The code still raises the exception, mainly due to the finally block, however the output is even more perplexing to me:

Can anyone explain what's going on?

The null check `this.conn != null && this.conn.State == ConnectionState.Closed` is useless as you don't null check when checking if it's open, and you don't check in the `finally` either, so if it is null you'll throw an exception before that null check (and after). If you expect that the connection may be null, you should check it in all the relevant places. — Martin Costello, Apr 19 '21 at 07:32
The duplicate contains all the relevant information to track down a NRE. I would add also an advice. Do not hide the management of your connection inside a bunch of methods. Try always to build the connection on the spot where it is needed inside a _using statement_. In this way you will never encounter problems with the connection lifetime — Steve, Apr 19 '21 at 07:33
@Martin Costello The connection is open since I could read the data from the database. The problem I have is when I try to actually close the connection to the database. The connector is a property inside a class, hence the functions to open (connectToSpecificDatabase) and close (closeDB). — Rus Paul Adrian, Apr 19 '21 at 08:02

Closing a C# MySQL database connection leads to a "System.NullReferenceException"

0 Answers0