C# connection becomes null

Question

Hi

I'm developing a class to manage the operations on a Mysql database. I have the following code:

 using System;
using MySql.Data.MySqlClient;

public class MysqlAccess
{
    private MySqlConnection pCnn;
    public enum OperationType {Select = 1,Insert = 2,Update = 3,Delete = 4};

    public MysqlAccess()
    {

        MySqlConnection Connection=new MySqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["LocalMySqlServer"].ConnectionString);


    }

   public MySqlConnection Connection { get {return pCnn;} set {pCnn=value;} }


    public int Connect()
    {
        try
        {

            Connection.Open();

            if (Connection.State == System.Data.ConnectionState.Open)
            {
                return 1;
            }
            else
            {
                return 0;
            }

        }
        catch (Exception e)
        {
            return -1;

        }
    }



    }

}

Page Load Code

 protected void Page_Load(object sender, EventArgs e)
    {

        MysqlAccess DBManager = new MysqlAccess();


        DBManager.Connect();
        Response.Write("Connection State:" + DBManager.Connection.State.ToString());
    }

When i do the response.write the connection is null, why?

Thanks in advance!

Answer 1

Well, it is null because you never really initialize the Connection property and it will be null until you initialize it. So instead of:

public MysqlAccess()
{
    // Here you are initializing a local variable 
    // that is subject to GC and goes into the void of forgetfulness
    MySqlConnection Connection=new MySqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["LocalMySqlServer"].ConnectionString);
}

initialize the property:

public MysqlAccess()
{
    var connectionString = ConfigurationManager.ConnectionStrings["LocalMySqlServer"].ConnectionString;
    // The 'this' keyword is optional here and its usage is a 
    // matter of personal preference
    this.Connection = new MySqlConnection(connectionString);
}

While this might fix the NullReferenceException you are getting you should be aware that MySqlConnection implements IDisposable meaning that you should make sure to call the Dispose method to avoid leaking connections or creating new connections on each request which could be particularly catastrophic in a web application.

Answer 2

In the constructor, you are not setting the property Connection. You're are declaring a local variable Connection. So, you've never initializes the property Connection.

Correct the code, using:

this.Connection=new MySqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["LocalMySqlServer"].ConnectionString);

Answer 3

Because you declared Connection as a local variable here:

public MysqlAccess()
{

    MySqlConnection Connection=new MySqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["LocalMySqlServer"].ConnectionString);


}

Try

public MysqlAccess()
{

    Connection=new MySqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["LocalMySqlServer"].ConnectionString);


}

Answer 4

Change your constructor to:

public MysqlAccess()
{
    Connection=new MySqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["LocalMySqlServer"].ConnectionString);
}

Answer 5

In the MysqlAccess constructor you assign to a new local variable:

MySqlConnection Connection=new MySqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["LocalMySqlServer"].ConnectionString);

...then later on you refer to a property, which happens to have the same name as the local variable:

Response.Write("Connection State:" + DBManager.Connection.State.ToString());

Changing the MySqlConnection Connection to just Connection in the constructor should fix it.