C# connection becomes null

Question

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