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

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?

Answer 1

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:

• Sql Server Data Access

Thanks.

Answer 2

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: