1

I am working on a project, while doing coding I got confused, as to where to initialize the connection in the constructor of the class or in the function I am using it.

My code in the constructor is as below:

public class clsTest
{
    private readonly MySql.Data.MySqlClient.MySqlConnection _dbInstance;

    public clsTest()
    {
        _dbInstance = new MySql.Data.MySqlClient.MySqlConnection(ClsConnectionString.connectionString);
    }

    public bool insert(object model)
    {
        using (_dbInstance)
        {
            //do what ever you want to achieve
            return true;
        }
    }
}

and the second approach (i.e. in the function only) is:

public List<tEntity> Getts()
{
    using (var db = new MySql.Data.MySqlClient.MySqlConnection(ClsConnectionString.connectionString))
    {
        // do whatever you want to do
    }
}

Which approach is better?

CDspace
  • 2,639
  • 18
  • 30
  • 36
Ram Singh
  • 6,664
  • 35
  • 100
  • 166
  • From my understanding, when declaring a Object only the reference is allocate on the stack with value null, as it is default value for reference types. – Drag and Drop Jan 19 '18 at 12:41
  • I prefer the 2nd method for *most* situations (where DI is not being used). It helps prevent `SqlConnections` leaks. The first method has significant problems as someone points out in the answer below. – Bradley Uffner Jan 19 '18 at 13:12

2 Answers2

0

When you use using you are disposing of the object in the closing }. You will then have problems if you try to use the disposed Connection object again.

Go with the second approach to avoid this.

Update

To show you what I mean:

First call:

public bool insert(object model)
{
    using (_dbInstance) // This works
    {
        //do what ever you want to achieve
        return true;
    }//_dbInstance is disposed
}

Second call:

public bool insert(object model)
{
    using (_dbInstance) //This does not work as _dbInstance was disposed in the first call
    {
        //do what ever you want to achieve
        return true;
    }
}
SBFrancies
  • 3,987
  • 2
  • 14
  • 37
  • even in the first, i am taking advantage of "using".. – Ram Singh Jan 19 '18 at 12:35
  • Yes but the problem is that the using will dispose of the instance and then you will then try to use than same instance the next time you call the DB. I've updated my answer. – SBFrancies Jan 19 '18 at 12:38
  • i have just checked the state of connection is always Closed, in constructor – Ram Singh Jan 19 '18 at 12:44
  • 2
    Closed and Disposed are different things. You can Open the Connection by calling _dbinstance.Open() but once it's disposed the resources are released and it's gone. – SBFrancies Jan 19 '18 at 12:46
0

Do not create an object untill you need it. It's better to initiate your ConnectionString in Using block. Using call Dispose() method of ConnectionString and tell GC to collect it.

More infos:

Establishing the Connection

in a “using” block is a SqlConnection closed on return or exception?

Mazaher Bazari
  • 421
  • 5
  • 12