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

Aspire 81 Reputation points
2020-07-08T11:11:38.363+00:00

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?

Not Monitored
Not Monitored
Tag not monitored by Microsoft.
37,624 questions
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. Richard Zhang-MSFT 6,936 Reputation points
    2020-07-08T13:03:19.757+00:00

    Hello,

    Welcome to Microsoft Q&A.

    Your question is related to SQL Server, Q&A does not support this topic yet.

    The supported products are listed over here https://learn.microsoft.com/en-us/answers/products (more to be added later on).

    You can ask in the forum below, the technical staff is actively answering related questions there:

    Thanks.

    0 comments No comments

  2. Peter Fleischer (former MVP) 19,311 Reputation points
    2020-07-08T14:34:19.523+00:00

    Hi,
    your szenario works fine. Try following code:

    <Page  
        x:Class="App1.Page09"  
        xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"  
        xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"  
        xmlns:local="using:App09"  
        xmlns:d="http://schemas.microsoft.com/expression/blend/2008"  
        xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"  
        mc:Ignorable="d"  
        Background="{ThemeResource ApplicationPageBackgroundThemeBrush}">  
      <Page.DataContext>  
        <local:ViewModel/>  
      </Page.DataContext>  
      <Grid Height="600">  
        <Grid.RowDefinitions>  
          <RowDefinition Height="auto"/>  
          <RowDefinition/>  
        </Grid.RowDefinitions>  
        <Button Content="Start" Command="{Binding Cmd}"/>  
        <ListBox Grid.Row="1" ItemsSource="{Binding View}"/>  
      </Grid>  
    </Page>  
      
    

    Code:

    using App1;  
    using App1.Core;  
    using System;  
    using System.Collections.ObjectModel;  
    using System.Data.SqlClient;  
    using System.Threading;  
    using System.Windows.Input;  
    using Windows.UI.Xaml.Controls;  
      
    namespace App09  
    {  
      public class ViewModel  
      {  
        public ObservableCollection<string> View { get; set; } = new ObservableCollection<string>();  
      
        public ICommand Cmd { get => new RelayCommand(CmdExec); }  
      
        private void CmdExec(object state)  
        {  
          for (int i = 0; i < 100; i++)  
          {  
            var m = new Model() { Result = View };  
            var pts = new ParameterizedThreadStart(m.GetData);  
            var t = new Thread(pts);  
            t.Start();  
          }  
        }  
      }  
      
      internal class Model  
      {  
        private SynchronizationContext sc = SynchronizationContext.Current;  
        internal ObservableCollection<string> Result { get; set; }  
        internal int ThreadNumber { get; set; }  
        internal void GetData(object state)  
        {  
          try  
          {  
            using (SqlConnection cn = new SqlConnection(Settings.CnSql))  
            {  
              cn.Open();  
              using (SqlCommand cmd = new SqlCommand("SELECT * FROM Tab1", cn))  
              {  
                var rdr = cmd.ExecuteReader();  
                if (rdr.Read()) Report(rdr[0]);  
                cmd.Cancel();  
                rdr.Close();  
              }  
            }  
          }  
          catch (Exception ex)  
          {  
            Report(ex.Message);  
          }  
        }  
      
        private void Report(object msg) =>   
          sc.Post(new SendOrPostCallback((par) => Result.Add($"{ThreadNumber} - {par}")), msg);  
      }  
    }  
    

    ConnectionString:

      public static class Settings  
      {  
        public static string CnSql { get; } = "Data Source=192.168.0.111;Initial Catalog=DemoDB;Integrated Security=True;";  
      }  
    

    Set Authentication:
    11566-x.png

    0 comments No comments