Using statement in c# does not close the connection. Why?

Question

I am creating a crud gridview webpage and I am using the "using" statement in ASP.NET in my code because I learned that it automatically converts the code that is inside it so I won't have to use connect.Close();

But still, I am getting an error:

System.InvalidOperationException: The connection was not closed. The connection's current state is open.

I tried to put connection.Close(); but still, the same error occurs.

This is my code. Can anyone please help me solve the problem? Thank you so much

void PopulateGridView()
{
    using (connect)
    {
        connect.Open();
        adapter = new SqlDataAdapter("SELECT * FROM RetailInfo", connect);
        table = new DataTable();
        adapter.Fill(table);
    }

    if(table.Rows.Count > 0)
    {
        RetailInfoGridView.DataSource = table;
        RetailInfoGridView.DataBind();
    } 
    else
    {
        table.Rows.Add(table.NewRow());
        RetailInfoGridView.DataSource = table;
        RetailInfoGridView.DataBind();
        RetailInfoGridView.Rows[0].Cells.Clear();
        RetailInfoGridView.Rows[0].Cells.Add(new TableCell());
        RetailInfoGridView.Rows[0].Cells[0].ColumnSpan = table.Columns.Count;
        RetailInfoGridView.Rows[0].Cells[0].Text = "No record Found";
    }
}

that connection variable is used again somewhere else in your application — Circle Hsiao, Dec 20 '19 at 06:23
Does this answer your question? [InvalidOperationException The connection was not closed. The connection's current state is open](https://stackoverflow.com/questions/11053731/invalidoperationexception-the-connection-was-not-closed-the-connections-curren) — Jawad, Dec 20 '19 at 06:25
On which line does the error occur? Is `connect` of type `System.Data.SqlClient.SqlConnection`? — Sweeper, Dec 20 '19 at 06:28
`using (connect)` should be `using (var connect = new SqlConnection(connectionString))` BTW, `adapter.Fill` will open the connection if it's closed, so you don't need the `connect.Open()` line. — Zohar Peled, Dec 20 '19 at 06:30
Thank you so much for your help. I solved it. He is right. I used the variable connection 3 times. THANK YOU SO MUCH! — NoobNoob, Dec 20 '19 at 06:35
Thank you for all your help. I really appreciated it. Thank you so much! — NoobNoob, Dec 20 '19 at 06:36

score 0 · Accepted Answer · answered Dec 20 '19 at 06:50

A simple fix is before connect.Open(), you can do the following:

if (connect.State == ConnectionState.Open)
{
    connect.Close();
}

Also, as Zohar Peled indicated that its good to use using (var connect = new SqlConnection(connectionString)) instead of declaring objects at the class level.

This way there are no leaks and connection objects are disposed as soon as their work is finished.

score 0 · Answer 2 · answered Dec 20 '19 at 07:02

Replace this part

 using (connect)
    {
       connect.Open();
        adapter = new SqlDataAdapter("SELECT * FROM RetailInfo", connect);
        table = new DataTable();
        adapter.Fill(table);
     }

to

using (connect)
    {
        if (connect.State == ConnectionState.Open)
        connect.Close();
        connect.Open();
        adapter = new SqlDataAdapter("SELECT * FROM RetailInfo", connect);
        table = new DataTable();
        adapter.Fill(table);
        connect.Close();
    }

Using statement in c# does not close the connection. Why?

2 Answers2