SQL-Server freezes when connecting (C#)
Introduction
When connecting to a SQL-Server database in Windows Forms when a server is unavailable or an incorrect connection string leads to a lengthy time before returning with a runtime exception. Learn how to properly deal these situations along with keeping an application user interface responsive.
Unresponsive application
Note the time elapsed, 15 seconds and the entire time the application will remain unresponsive.
Much better using code in the solution section below.
Separation of concerns
Although many coders write data operations directly in a windows form, in the following code samples data operations are performed in a data class. Although not relevant to working with unresponsive connections this is good practice to follow.
Basic code sample
Most developers will use a connection and command object to populate a DataSet, DataTable or list<T> to return data to a user interface. To keep focus on a connection issue data is returned in a DataTable.
- The connection string is private rather than local to the read method as in real applications there are several methods a connection string is needed like for CRUD operations.
- Several class level properties are used to inform the caller if there were issues returning data.
- A delegate/event is used to present the caller with how much time was used to connect or fail to connect to the server.
Data class
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace SqlServerConventional.Classes
{
public class DataOperations
{
private static string _connectionString = "";
public static bool RunWithoutIssues = false;
private static bool HasException { get; set; }
public static bool IsSuccessful => HasException == false;
public static string ExceptionMessage { get; set; }
public delegate void OnConnectionFinished(string timeSpent);
public static event OnConnectionFinished ConnectionFinished;
public static DataTable ReadProducts()
{
HasException = false;
var table = new DataTable();
_connectionString = RunWithoutIssues ?
"Data Source=.\\sqlexpressISSUE;Initial Catalog=NorthWind2020;Integrated Security=True" :
"Data Source=.\\sqlexpress;Initial Catalog=NorthWind2020;Integrated Security=True";
using (var cn = new SqlConnection(_connectionString))
{
using (var cmd = new SqlCommand() {Connection = cn})
{
cmd.CommandText = SelectStatement();
var timer = new Stopwatch();
timer.Start();
try
{
cn.Open();
table.Load(cmd.ExecuteReader());
}
catch (Exception e)
{
HasException = true;
ExceptionMessage = e.Message;
}
timer.Stop();
TimeSpan timeTaken = timer.Elapsed;
ConnectionFinished?.Invoke(timeTaken.ToString(@"m\:ss\.fff"));
}
}
return table;
}
private static string SelectStatement()
{
return "SELECT P.ProductID, P.ProductName, P.SupplierID, S.CompanyName, P.CategoryID, " +
"C.CategoryName, P.QuantityPerUnit, P.UnitPrice, P.UnitsInStock, P.UnitsOnOrder, " +
"P.ReorderLevel, P.Discontinued, P.DiscontinuedDate " +
"FROM Products AS P INNER JOIN Categories AS C ON P.CategoryID = C.CategoryID " +
"INNER JOIN Suppliers AS S ON P.SupplierID = S.SupplierID";
}
}
}
Form code
using System;
using System.Threading.Tasks;
using System.Windows.Forms;
using SqlServerConventional.Classes;
namespace SqlServerConventional
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
DataOperations.ConnectionFinished += DataOperationsConnectionFinished;
Shown += OnShown;
}
private void DataOperationsConnectionFinished(string timeSpent)
{
finishedLabel.Text = timeSpent;
}
private async void OnShown(object sender, EventArgs e)
{
await LoadData();
}
private void Form1_Load(object sender, EventArgs e)
{
}
private async Task LoadData()
{
dataGridView1.DataSource = null;
await Task.Delay(1000);
DataOperations.RunWithoutIssues = NoIssuesCheckBox.Checked;
var table = DataOperations.ReadProducts();
if (DataOperations.IsSuccessful)
{
dataGridView1.DataSource = table;
}
else
{
MessageBox.Show($"Ran into issues\n{DataOperations.ExceptionMessage}");
}
LoadProductsButton.Enabled = true;
NoIssuesCheckBox.Checked = false;
}
private async void LoadProductsButton_Click(object sender, EventArgs e)
{
await LoadData();
}
}
}
Although there is exception handling, for a a bad connection because of unavailable server or incorrect connection string the application will remain unresponsive for roughly 15 seconds.
Solution
The proper way to deal with a bad connection because of unavailable server or incorrect connection string is to
- First determine an acceptable time to wait before cancelling accessing a server, in the code sample which follows four seconds is used to timeout which could be what seems appropriate for a timeout.
- Wrap data operations in a Task
- Implement a CancellationToken (see figure 1).
- Wrap code to access a server with try-catch
- Pass the CancellationToken to the OpenAsyc method rather than using the standard Open method
- Unlike in the basic example write the runtime issue to a log file, see the following code for a basic logging system
Figure 1
private CancellationTokenSource _cancellationTokenSource =
new CancellationTokenSource(TimeSpan.FromSeconds(4));
Form code
using System;
using System.Threading;
using System.Threading.Tasks;
using System.Windows.Forms;
using SqlServerAsyncRead.Classes;
namespace SqlServerAsyncRead
{
public partial class Form1 : Form
{
private CancellationTokenSource _cancellationTokenSource =
new CancellationTokenSource(TimeSpan.FromSeconds(4));
public Form1()
{
InitializeComponent();
}
private async void LoadProductsButton_Click(object sender, EventArgs e)
{
dataGridView1.DataSource = null;
await Task.Delay(1000);
await LoadData();
}
private async void Form1_Load(object sender, EventArgs e)
{
DataOperations.RunWithoutIssues = NoIssuesCheckBox.Checked;
await LoadData(true);
NoIssuesCheckBox.Checked = false;
}
private async Task LoadData(bool firstTime = false)
{
if (!firstTime)
{
if (_cancellationTokenSource.IsCancellationRequested)
{
_cancellationTokenSource.Dispose();
_cancellationTokenSource = new CancellationTokenSource(TimeSpan.FromSeconds(4));
}
}
var dataResults = await DataOperations.ReadProductsTask(_cancellationTokenSource.Token);
if (dataResults.HasException)
{
MessageBox.Show(dataResults.ConnectionFailed ? @"Connection failed" : dataResults.GeneralException.Message);
}
else
{
dataGridView1.DataSource = dataResults.DataTable;
}
LoadProductsButton.Enabled = true;
}
private void NoIssuesCheckBox_CheckedChanged(object sender, EventArgs e)
{
DataOperations.RunWithoutIssues = NoIssuesCheckBox.Checked;
}
}
}
- The check in LoadData method _cancellationTokenSource.IsCancellationRequested which is only required for a secondary attempt at a connection.
- A CheckBox is used to use a good or incorrect connection string.
Data class
As stated before, to keep an application responsive a task is needed for asynchronous operations rather than synchronous which would freeze the user interface until a runtime exception is thrown.
using System;
using System.Data;
using System.Data.SqlClient;
using System.Threading;
using System.Threading.Tasks;
namespace SqlServerAsyncRead.Classes
{
public class DataOperations
{
private static string _connectionString = "";
public static bool RunWithoutIssues = false;
public static async Task<DataTableResults> ReadProductsTask(CancellationToken ct)
{
var result = new DataTableResults() {DataTable = new DataTable()};
_connectionString = RunWithoutIssues ?
"Data Source=.\\sqlexpressISSUE;Initial Catalog=NorthWind2020;Integrated Security=True" :
"Data Source=.\\sqlexpress;Initial Catalog=NorthWind2020;Integrated Security=True";
return await Task.Run(async () =>
{
using (var cn = new SqlConnection(_connectionString))
{
using (var cmd = new SqlCommand() { Connection = cn })
{
cmd.CommandText = SelectStatement();
try
{
await cn.OpenAsync(ct);
}
catch (TaskCanceledException tce)
{
Exceptions.Write(tce, ExceptionLogType.ConnectionFailure,
$"Connection string '{_connectionString}'" );
result.ConnectionFailed = true;
result.ExceptionMessage = "Connection Failed";
return result;
}
catch (Exception ex)
{
Exceptions.Write(ex, ExceptionLogType.General);
result.GeneralException = ex;
return result;
}
result.DataTable.Load(await cmd.ExecuteReaderAsync(ct));
}
}
return result;
});
}
private static string SelectStatement()
{
return "SELECT P.ProductID, P.ProductName, P.SupplierID, S.CompanyName, P.CategoryID, " +
"C.CategoryName, P.QuantityPerUnit, P.UnitPrice, P.UnitsInStock, P.UnitsOnOrder, " +
"P.ReorderLevel, P.Discontinued, P.DiscontinuedDate " +
"FROM Products AS P INNER JOIN Categories AS C ON P.CategoryID = C.CategoryID " +
"INNER JOIN Suppliers AS S ON P.SupplierID = S.SupplierID";
}
}
}
Canceling other operations
Connections are not the only thing that can have a constraint on, here is a simple example in a console application. This should provide incentive to work with other operations too like downloading information from a service or scrapping web pages and so forth.
using System;
using System.Threading;
using System.Threading.Tasks;
using static System.Console;
namespace ConsoleBasics
{
class Program
{
public static async Task Main()
{
WriteLine("Greetings");
try
{
WriteLine("Before work");
await PerformDoNothingWorkTask(1000);
WriteLine("After work");
}
catch
{
WriteLine("Timed out");
}
WriteLine("Finished");
await Task.Delay(1000);
WriteLine("Close me");
ReadLine();
}
public static async Task PerformDoNothingWorkTask(int time)
{
using var ct = new CancellationTokenSource(time);
var wasteTimeTask = Task.Run(async () =>
{
await Task.Delay(2000, ct.Token);
WriteLine("Task Running");
await Task.Delay(2000, ct.Token);
WriteLine("still running");
await Task.Delay(2000, ct.Token);
WriteLine("Not dead yet");
await Task.Delay(2000, ct.Token);
WriteLine("Task done");
await Task.Delay(2000, ct.Token);
}, ct.Token);
bool success;
try
{
await wasteTimeTask;
success = true;
}
catch (OperationCanceledException)
{
success = false;
}
WriteLine(success ? "Task finished in time" : "Task took too long");
}
}
}
Summary
Code has been presented to properly deal with issues connecting to a SQL-Server server and the same can apply to any data provider like OleDb which has a OpenAsync method which takes a CancellationTokenSource.
Important, asynchronous operations do take longer than their synchronous counterparts which means when implementing be sure to keep things short time wise and in some cases look to other solutions.
See also
SQL Server database login for windows forms
Defensive data programming part 3
External resources
Source code
Use Visual Studio to clone the following repository or Git Desktop.
Requires
- Microsoft Visual Studio 2019
- .NET Framework 4.8 or higher