How to work with many connection in c# and ASP.NET?

Question

This is how I do my connection

SqlConnection conn = new SqlConnection(connectionstring);
conn.open();

SqlCommand comando = new SqlCommand(/*my query update/delete/insert/select o execute sp*/,conn);
comando.Parameters.Add("@parameter1","value1")
comando.Parameters.Add("@parameter2","value2")
comando.Parameters.Add("@parameterN","valueN")
comando.ExecuteNonQuery()

conn.close();

but server administrator says there are many connections.

Then, how can I execute my queries?

Would it be better if I do not close the connection?

Mark Maslar · Accepted Answer · 2013-04-02T17:02:48.573

2

No, it would not be better to leave the connection open. Use "using" commands to manage system resources.

using(SqlConnection conn = new SqlConnection(stringconection))
{
    conn.Open();
    SqlCommand comando = new SqlCommand(/*my query update/delete/insert/select o execute sp*/,conn);
    comando.Parameters.Add("@parameter1","value1");
    comando.Parameters.Add("@parameter2","value2");
    comando.Parameters.Add("@parameterN","valueN");
    comando.ExecuteNonQuery();
}

edited Apr 02 '13 at 17:02

answered Apr 02 '13 at 16:48

Mark Maslar

1,121
4
16
28

but if i have the code i wrote up, in a method for example public string executequery(string query){the code i wrote up with sqlconnection here } /*then the sqlconnection which is created in a method exist only inside this method, doesn't it let than exist when method finish?*/ – angel Apr 02 '13 at 16:54
i know all object created inside a method only exist there, when it finishes the object created inside now then do not exist, but what happened with the sqlconnection ? – angel Apr 02 '13 at 16:57
If you not call the Dispose or Close method, the connection might not be added or returned to the pool. – Vyacheslav Volkov Apr 02 '13 at 17:01
how is it of pool? the connectionstring need some value more? pooling=true? or something as it? – angel Apr 02 '13 at 17:07
You can read about connection pooling [here](http://msdn.microsoft.com/en-us/library/8xx3tyca(v=vs.100).aspx). You don't need to do anything specific to enable connection pooling (although you can disable it by setting Pooling=false in your connection string). – Vyacheslav Volkov Apr 02 '13 at 17:12

score 0 · Answer 2 · answered Apr 02 '13 at 16:55

Here is a quote from the documentation:

It is recommended that you always close the Connection when you are finished using it in order for the connection to be returned to the pool. This can be done using either the Close or Dispose methods of the Connection object. Connections that are not explicitly closed might not be added or returned to the pool. For example, a connection that has gone out of scope but that has not been explicitly closed will only be returned to the connection pool if the maximum pool size has been reached and the connection is still valid.

score 0 · Answer 3 · answered Apr 02 '13 at 17:06

You can use "using" like Mark mentioned above (my preference). You can also use a try-catch-finally block.

try
{
    SqlConnection conn = new SqlConnection(stringconection);

    conn.Open();

    SqlCommand comando = new SqlCommand(/*my query update/delete/insert/select o execute sp*/,conn);
    comando.Parameters.Add("@parameter1","value1");
    comando.Parameters.Add("@parameter2","value2");
    comando.Parameters.Add("@parameterN","valueN");
    comando.ExecuteNonQuery();
}
catch(Exception ex) 
{
   // catch exceptions here 
}
finally
{
    if(comando != null)
    {
        comando.Dispose();
    }
    if(conn != null)
    {
        conn.Dispose();
    }
}

How to work with many connection in c# and ASP.NET?

3 Answers3