Creating Sqlconnection in multiple threads RRS feed

  • Question


    Hi all,


    Greetings! I have to create a sql connection and that is been loaded in each thread (multi threading) upto 100 threads. As am new to Threading concept, please any one help me in this issue...



    Thanks in advance...

    Monday, April 28, 2008 6:42 AM

All replies

  • Don't bother sharing connections between threads, sharing connections is all handled behind the scenes for you anyway by the connection pool so you won't improve performance anyway. Just create a connection when you need it then dispose of it when done, e.g.

    using (var connection = new SqlConnection("your connection string"))
        // use the connection here, then it is disposed of at the end of the using block

    Then you don't have to worry about any shared state issues. Threading is hard enough without introducing unnecessary complexities.
    Monday, April 28, 2008 7:58 AM
  • Hi,


    Thanx for your reply. Am doing this multi threading with Sql connections to check the stress test in our application. More than 500 persons are going to acess the server, by this time I have to check the stress occurs in the application. For this, we are creating a sample test and where I have to create/ open more than 100 connections which should run in each thread and process some job in the database.


    Thanking You.


    Monday, April 28, 2008 9:19 AM
  • The C# Language forum is for questions specifically related to the C# language such as how inheritance works or how to use anonymous methods.  Please use the C# General forum for general questions related to using C# to accomplish some task.  Use the other forums when the question is more specific (such as WinForms for UI questions).  I'm moving this question to the C# General forums.  Thanks.


    As an addition to Greg's posting.  Note that ADO.NET has an upper limit of the # of connections it will allow at any one time.  I can't remember the maximum but I'm thinking it is 100.  Therefore you should ensure that your app is actually creating 100 connections.  You might have to increase the # of connections ADO.NET will allow. 


    Also be aware that DBs generally have an upper limit on the # of connections they'll allow.  SQL Dev edition, for example, only allows 10 or so.  SQL Workgroup has less than that.  You should ensure that you are not hitting this limit either.


    Finally be aware that connection pooling, as Greg mentioned, is used to share the same connection across multiple calls.  It works by comparing the connection string (case sensitive) to any existing open connections ADO.NET might have.  If it finds a string-compared match then it'll reuse the connection (if it is not currently in use).  Therefore your performance testing will skip over the overhead of setting up a connection if you reuse connection strings.  You will need to keep each of the connection you open alive for the life of your test to truly test all the connection requests.


    Michael Taylor - 4/28/08



    Monday, April 28, 2008 1:13 PM