Can a .NET SqlConnection object cause a memory lea

Question

I understand that you need to call .Close() on a SqlConnection object to release the underlying SQL connection back to the pool when you are done with it; but if you refrain from doing so, does the .NET object remain in memory even after going out of scope? I ask because I am working with some code that is experiencing memory leaks and I noticed that the SqlConnection objects are not being closed or disposed (they are created, opened, then simply allowed to go out of scope).

Answer 1

The issue isn't a memory leak. The issue is that the connection to the SQL server remains open, meaning that connection isn't available for something else that needs to communicate with that server.

The connection will be closed eventually if it goes out of scope and gets garbage collected and disposed, but there's no telling when that will happen. Your application can only have so many SQL connections open at a given time, and the SQL server itself can only support so many connections.

Think of it like a responsible person borrowing a book from the library. If you don't return the book it will eventually get back to the library, because one day you'll die and when someone cleans up your house they'll find the book and send it back to the library. But if everyone did that then it would be really hard to find books at the library. So we don't check out the book until we're actually ready to read it, and we return it as soon as we're done.

Same thing with SQL connections. Don't open them until you need them, and close them as soon as possible when you're done with them. And, as shown in other answers, using simplifies it by ensuring that the connection will be disposed (which also closes it) without having to use try/finally.

Answer 2

Always use using(...) to dispose sqlConn and sqlCmd. If there are still memory leaks, then bug report to microsoft... If you do the dispose properly, there should be no memory leaks there

using (SqlConnection sqlConn = new SqlConnection(....))
{
  using (SqlCommand sqlCmd = new SqlCommand(....))
  {
    .... do something here with your sqlConn and sqlCmd
  }  // sqlCmd will be properly disposed here
}  // sqlConn will be properly disposed here

Answer 3

there are 2 preferred ways on how instructing a database to update "using" and "try catch"

public void PerformStoredProcedure()
{
    string cs = System.Configuration.ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString; // reading by name DBCS out of the web.config file
    using (SqlConnection connection = new SqlConnection(cs))
    {
        SqlCommand cmd = new SqlCommand("spDoMyStoredProcudere", connection);
        cmd.CommandType = System.Data.CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@good", TextBox1.Text + "%"); // input for stored procedure
        connection.Open();

        //--
        GridView1.DataSource = cmd.EndExecuteReader();
        GridView1.DataBind();
    }
} 

the bennefit of using is that it auto closes.

the other mehod is a try catch construction

protected void Page_Load(object sender, EventArgs e)
{
    string cs; // conection string.
    cs = "data source=.; "; //servername (.) = local database password. 
 // cs = cs + "user id=sa; password=xxxxx"; using sql passwords authentication.
    cs = cs + "integrated security=SSPI"; // using windows nt authentication.

    //its better to store connnection in web.config files. so all form pages can use it.

    cs = System.Configuration.ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString; // reading by name DBCS out of the web.config file

    SqlConnection con = new SqlConnection(cs);
    try
    {  // if any problem ocures we still close the connection in finally.
        //SqlCommand cmd = new SqlCommand("Select * from tblSample", con);  //execture this command on this coneection on this table


        SqlCommand cmd = new SqlCommand("Select title, good from tblSample",con);
        con.Open(); 

        GridView1.DataSource = cmd.ExecuteReader(); //execure reader T_SQL statement that returns more then 1 value
        //cmd.ExecuteNonQuery  //for insert or update or delete
        //cmd.ExecuteScalar //for single value return
        GridView1.DataBind();
    }
    catch
    {
        Response.Write("uh oh we got an error");
    }
    finally
    {
        con.Close();
    }

Though using is mostly used for exotic scenario's the catch option might be nice as well, it requires though more typing.

And if i remind correctly after 20 minutes or so a IIS session by default is terminated if no actions occur.