C# SqlConnection inside a loop

Question

first time on stackoverflow. I'm learning how to manage SqlConnection in my WebForm pages, and I want to reach the best practice to do that. In my specific case, I have a loop and there's no way for me to run the code without errors if I don't set a new SqlConnection for every iteration of the loop (the error is about an attempt to read when reader is close). So I declare this in the PageLoad method:

private SqlConnection con;
protected void Page_Load(object sender, EventArgs e)
{
    con = new SqlConnection(connectionString);
}

Then I have this:

private int conta(int padre)
{
    string SQL = "SELECT * FROM categories WHERE idp=@idpadre";
    SqlCommand cd = new SqlCommand(SQL, con);
    cd.Parameters.AddWithValue("@idpadre", padre);
    int sub=0;
    try
    {                
        if ((con.State & ConnectionState.Open) <= 0)
        {
            con.Open();
        }

        using (SqlDataReader reader = cd.ExecuteReader())
        {
            while (reader.Read())
            {
                sub++;
            }
        }
    }
    catch (Exception err)
    {
        lbl.Text = "Errore conta!";
        lbl.Text += err.Message;
    }
    finally
    {
        con.Close();

    }
    return sub;
}

protected void buildParent(int padre, int level)
{
    StringBuilder sb = new StringBuilder();
    sb.Append(" ");
    for (int i = 0; i < level; i++)
    {
        sb.Append(HttpUtility.HtmlDecode("&nbsp;&nbsp;&nbsp;&nbsp;"));
    }
    sb.Append("|--");
    selectSQL = "SELECT * FROM categories WHERE idp=@idpadre";
    SqlConnection cn = new SqlConnection(connectionString);
    cmd = new SqlCommand(selectSQL, cn);
    cmd.Parameters.AddWithValue("@idpadre", padre);

    try
    {
        cn.Open();

        using (SqlDataReader read = cmd.ExecuteReader())
        {
            while (read.Read())
            {

                dlParent.Items.Add(new ListItem { Text = sb.ToString() + read["cat"].ToString(), Value = read["idcat"].ToString() });
                int sub = conta(Convert.ToInt32(read["idcat"]));
                //int sub = 0;
                if (sub > 0)
                {
                    buildParent(Convert.ToInt32(read["idcat"]), level + 1);
                }

            }
            read.Close();
        }

    }
    catch (Exception err)
    {
        lbl.Text = "Errore buildParent!";
        lbl.Text += err.Message;
    }
    finally
    {
        cn.Close();
        if (s != null)
        {
            if (!this.IsPostBack)
            {
                buildPage();
                buildLang();
                buildImage();
            }
        }
    }
}

In buildParent in the while loop i call "conta", but if I use the same SqlConnection (con) with both the methods I have an error about an attempt to read when reader is close. I'm worried about the connection pool on the web server, particularly concerning the max connection reach. So, where am I wrong? What's the best practice to manage SqlConnection? Thank you.

Filip De Vos · Accepted Answer · 2013-03-27 20:12:40Z

5

You open the connection as late as possible, and you dispose as soon as possible. Let the connection pool deal with reclaiming the connections.

I usually write my code like this:

using (var conn = new SqlConnection(connectionString))
using (var cmd = new SqlCommand(commandToRun, conn))
{
    cmd.Parameters.AddRange(new[]
        {
            new SqlParameter("myParam", "myvalue"),
            new SqlParameter("myParam", "myvalue")
        });

    conn.Open(); // opened as late as possible
    using (SqlDataReader reader = cd.ExecuteReader())
    {
        while (reader.Read())
        {
            // do stuff.
        }
    }
} // disposed here.

Note: To get a count from a SQL database you better use

SELECT count(*) FROM categories WHERE idp=@idpadre

And execute the query with ExecuteScalar()

edited Mar 27, 2013 at 20:12

answered Mar 27, 2013 at 17:34

Filip De Vos

12k1 gold badge50 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

recursive Over a year ago

Also, when you are done, make sure you dispose the connection. IT doesn't look like that's happening now.

Mohammed Hossain Over a year ago

It is, in the finally block.

Fabio Garzoli Over a year ago

but it's correct to create a different sqlconnection object in the loop? and when I close the connection, I have to dispose it also?

Scott Chamberlain Over a year ago

@FabioGarzoli yes, look at the link i added to the post. Behind the scenes .NET is leaving the connection open even though you disposed the connection and will re-use the connection instead of creating a new one if a timeout has not expired. (also if you dispose the connection you do not need to close it, as that will close the connection as part of the disposing process)

Fabio Garzoli Over a year ago

@ScottChamberlain thanks for the link, I'll study it. In a simple way I need to change con.Close(); in con.Dispose(); and I'm fine and sure that the connection pool max size is not reached, am I right? Another question: is there a way to check in Visual Studio (I'm using Visual Web Developer) how many connections are opened?

|

Collectives™ on Stack Overflow

C# SqlConnection inside a loop

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related