.NET database connection pooling error occurred?

Question

I have made connection string in web.config file.

<connectionStrings>

     <add name="Test_registration" connectionString="Data Source=DOTNET\SA;Initial Catalog=Test2009;User ID=trainee;Password=trainee123" providerName="System.Data.SqlClient"/>
    </connectionStrings>

And in CS file make a constructor for opening a connection.

  public main_connection()
    {
    sql_conn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["Test_registration"].ConnectionString);
    sql.conn.open();
    }

But I am getting connection pooling error at runtime of the program on server.

Can anyone tell me where I should close the connection? In web.config file of my project cs file where i open the connection?

I want to write that close() statement only one time as I start the connection only one time.

Answer 1

I think you should open a connection, execute your sql statement and close the connection immediately. The provider takes care of connection pooling.

Answer 2

Like Raj said I'd suggest opening the connection whenever you have a statement to run, and then close it again afterwards - it's the most common way to do DB queries.

That being said, if you prefer to have a Connection open at all time, you should Close it when your program exits (or in the case of a web site, close it in Global.Asax on Application Close, Unload and similar (can't remember how many there are, since I never do it this way)

Your web.config will not close it automatically!

With all this said I still think you should consider a construction like this instead (pseudo code):

using (var conn = OpenConnection())
{
    using (var cmd = CreateCommand(conn))
    {
     RunQuery();
    }
}

When you put Connection and Command inside using statements, they'll be properly closed (including your connection) and you don't risk having dead connections on your DB server, in case of exceptions in your code.

Obviously a try-finally construction will do the same, using is just prettier :-)

Answer 3

set connection string here.

then use following code. ( sql_conn is the name of connection object)

try { sql_conn.Open(); }
            catch (Exception)
            {
                if (sql_conn.State != ConnectionState.Closed)
                {
                    sql_conn.Close();
                    sql_conn.Open();
                }
            }