none
Manage Connection RRS feed

  • Question

  •  

    Hi experts, I would like to ask regarding on how to manage the connection in Visual C#, connect and disconnect so that connection pooling will not be an issue and becomes bottleneck to our server.
    Saturday, September 29, 2007 2:46 AM

All replies

  • You need to be more specific in your question.

     

    What kind of connection are you talking about?  SQL connections, TCP connections, etc. are managed differently and have different pooling behavior.

     

    What kind of issue are you expecting?  What is your server load, etc?

     

    Saturday, September 29, 2007 4:52 AM
  •  

    Thanks to your effort, but my apology for not explaining it correctly. My concern is how to do it in Visual C# that if I created a connection and execute an Insert, Update, Delete, or even retrieve records this will only create one connection in connection pool? Because I observed that everytime I executed those activity it always create an entry in connection pool. I'm using the SQL Profiler to check the Login/Logout activity.

     

    public void InsertRow(string myConnectionString)
    {
        // If the connection string is null, use a default.
        if(myConnectionString == "")
        {
           myConnectionString = "Initial Catalog=Northwind;Data Source=localhost;Integrated Security=SSPI;";
        }
        SqlConnection myConnection = new SqlConnection(myConnectionString);
        string myInsertQuery = "INSERT INTO Customers (CustomerID, CompanyName) Values('NWIND', 'Northwind Traders')";
        SqlCommand myCommand = new SqlCommand(myInsertQuery);
        myCommand.Connection = myConnection;
        myConnection.Open();
        myCommand.ExecuteNonQuery();
        myCommand.Connection.Close();
    }

     

    So form this code, if I added delete, or update it will create another connections? Hope you will get my point. Thanks


    Saturday, September 29, 2007 5:19 AM
  • The number of entries in the connection pool is the max number of concurrent connections you have had up to the max number of connections allowed to be pooled.

     

    Because connections are expensive to create compared to the time to execute the operation, each time you are done with a connection it is returned to the pool.  When a connection is needed again, if one is available in the pool it is re-used - saving the cost of creating the connection.  However, the pool itself has a cost, so the SQL team thought quite a bit about the pooling algorithm and behavior.

     

    The connection string has to match for the connection pool to reuse a connection.  Otherwise a new connection pool is created.

     

    The reason you see some new connections in the pool each time is that the pool has a minimum connection size as well.  Until there are the minimum connections, it will add vs. reuse. 

     

    My experience has been that connection pooling in SQL works well and other issues with the code are performance culprits before connection pooling.  In the enterprise work I do (very high scale), I rarely have to worry about connection pooling as it works so well.  

     

    A few things I would suggest based on your sample regarding designing for performance:

    1. Rather than take a connection string in the parameter, I suggest making it a configuration value.

    2. Consider using a stored procedure rather than dynamic SQL.  SPs are compiled by SQL server and are much, much faster.

    3. Take a look at the ADO.NET QueryAdapter and TableAdapter classes.  They are good at doing insert, update, and delete operations.  They also have some great designer integration in VS.NET.

    4. If you code it yourself, put a using() clause around the connection object to ensure it is closed early.

     

    Check out http://msdn2.microsoft.com/en-us/library/8xx3tyca(VS.71).aspx for a good overview.

     

     

     

    Saturday, September 29, 2007 5:06 PM
  • It is always better to maintain connection in App.config (if it is web application the maintain the connection in web.config)

    Please try to use microsoft enterprise library

     

    http://www.codeproject.com/useritems/Using_DAAB.asp

     

    http://www.daveandal.net/articles/EntLibASPNET/EnterpriseLibraryInASPNET-2.htm

     

     

    use the above website to know abt microsoft enterprise library

     

    Thursday, October 4, 2007 12:33 PM