locked
C# Multiple thread connect to one SQL Server database, dbconnection.open hang RRS feed

  • Question

  • my code process is as below:
    1. create database connection
    2. create database dbCommand
    3. call ExecuteReader and get reader (parse database data)
    4. dbCommand cancel
    5. reader close
    6. database connection close

    my connection string is as below:
    string SQLConnection = "Server=192.168.0.111;Database=MySqlDB;Intefrated Security=SSPI;Connect Timeout=5";
    SqlConnection sqlConnection = new SqlConnection(SQLConnection);

    The above processing will be done in parallel in multiple threads, one time hang in the db.open()
    So why?

    Wednesday, July 8, 2020 11:16 AM

All replies

  • The above processing will be done in parallel in multiple threads, one time hang in the db.open()

    So why?

    Hello,

    please provide more information.

    How exactly do you call db.open() or define the parameter for your variable db ? Normally, there will be a timeout when the connection cannot be established, and you get a return value.

    Does your database allow many multiple connections? Maybe there is a maximum amount of users who can connect?

    Regards, Guido


    Wednesday, July 8, 2020 2:11 PM
  • Connections, commands and readers are not thread safe. So if you intend to run this code in multiple threads each thread will need to create its own connection, command and reader. If you have all this wrapped in a single method then it will be fine. However alarmingly people seem to be putting connections and commands in fields of a class and then attempting to reuse instances of that class. That will not work in threads at all and isn't even good practice in general.

    class Database
    {
       private SqlConnection _connection;
    
       public void CallDatabase ()
       {
          _connection.Open();
          ...
       }
    }
    
    //Defined in some global variable somewhere
    Database _database;
    
    //Called in a thread - always wrong
    _database.CallDatabase();
    
    //Better approach
    class Database 
    {
       public void CallDatabase ()
       {
          using (var conn = new SqlConnection(...))
          {
             var cmd = conn.CreateCommand();
             ...
          };
       }
    }
    
    //Works in any thread even at the same time, ignoring DB concurrency limits
    var db = new Database();
    db.CallDatabase();
    
    



    Michael Taylor http://www.michaeltaylorp3.net

    • Proposed as answer by BonnieBMVP Saturday, July 11, 2020 12:38 AM
    Thursday, July 9, 2020 2:34 PM
  • Hi,

    Has your issue been resolved?

    If so, please click on the "Mark as answer" option of the reply that solved your question, so that it will help other members to find the solution quickly if they face a similar issue.

    Best Regards,

    Timon


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, July 21, 2020 7:58 AM