Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

using statement with connection.open

Tags:

People also ask

Does using statement close SQL connection?

The using statement will call conn. Close() automatically due to the using statement (using (SqlConnection conn = new SqlConnection(connString)) and the same for a SqlDataReader object. And also if any exception occurs it will close the connection automatically.

Do I need to open SqlConnection with using?

Open method call. The using statement creates a read-only variable of type SqlConnection. You need to call Open() on the SqlConnection instance before using it in an SqlCommand. And The SqlConnection is passed as the parameter to the SqlCommand. In this way we specify that the SqlCommand "uses" the SqlConnection.

Does using statement close SQL connection C#?

The using statement handles the disposal and subsequent closing of my objects for me. Even if an exception is thrown, as soon as execution leaves the using block the DIspose method of the object is invoked under the covers. For objects like the SqlConnection, this also closes the connection too.

What is SqlConnection and SqlCommand?

A SqlConnection object represents a unique session to a SQL Server data source. With a client/server database system, it is equivalent to a network connection to the server. SqlConnection is used together with SqlDataAdapter and SqlCommand to increase performance when connecting to a Microsoft SQL Server database.


I was looking at some code and discussing it with co-workers.

Specifically a section of code that looks like this.

    [Test]
    public void TestNormalWay()
    {
        using(var cn = GetConnection())
        {
            cn.Open();
            // do stuff
        }
    }

The question came up:

"why not move the cn.Open into the GetConnection method."

I said that if "Open" throws an exception dispose would not get called. His response was

"So what. The connection wasn't opened so why would it need to get closed (or disposed)?"

For me it is just a matter of not wanting to know if or if not I NEED to dispose/close so I would repeat the cn.Open in the code instead of moving it into the shared function.

BUT it is interesting... so I did some reading at SQL Server Connection Pooling (ADO.NET)

To me it isn't clear if there exists a scenario in which calling cn.Open and it throws and exception where dispose would need to be called.

So in my example below is there any difference really between "TestNormalWay" and "WhyNotDoItThisWay"

    protected static DbConnection GetConnection()
    {
        DbConnection cn = new SqlConnection("SomeConnecitonstring... ");
        return cn; 
    }

    protected static DbConnection GetConnectionDangerousVersion()
    {
        DbConnection cn = new SqlConnection("SomeConnecitonstring... ");
        cn.Open();  // this will throw.. .dispose not called
        return cn; 
    }

    [Test]
    public void TestNormalWay()
    {
        using(var cn = GetConnection())
        {
            cn.Open();
            // do stuff
        }
    }

    [Test]
    public void WhyNotDoItThisWay()
    {
        using(var cn = GetConnectionDangerousVersion())
        {
            // do stuff
        }
    }