.NET: Defensive data programming (Part 3)
Series
.NET: Defensive data programming (part 1)
NET: Defensive data programming (Part 2)
Introduction
When distributing a Visual Studio solution to customers within an organization where all users reside in the same location when there is an issue with connecting to a database, in this case SQL-Server which resides on a network server diagnosing connection issues first begin with, is the issue isolated to all users or some users e.g. in a specific group or section of the building. Other considerations, could the user(s) connection before or not. Resolving the issue could be a remote connection into a machine to check the validity of the application installation which includes conflicts with other software. What happens if there is an issue where the solution has been installed not in an organization? Remote connections are possible but not always. Other things to consider, is the database available, was it removed or renamed? Are required tables available. Are there firewall issues?
In both cases one thing which can be done is to have code in place which can determine if a SQL-Server is available, is the database available and are the tables found in the database.
This article will walk through being proactive to determine if the server is available, the database exists and tables exists.
Walkthrough
High level, when the application first runs code presented later will determine if the server is available, database exists and tables exists. The fictitious application in this case reads data from SQL-Server which displays reports and queries behind them to read data for a reporting system.
The first time the application runs the assertion required takes time so all code for the assertion is performed asynchronously which is inherently slow yet keeps the application responsive while performing server, database and table checks. If the server exists, database exists and the table exists then a file is written to the application folder. With the file written the next time the application starts if the file exists server, database and table checks are skipped and the data is loaded. If the file does not exists next startup then the check for server, database and tables is done again.
When multiple attempts are performed and the user is responsive to get the application running they email or call for support and diagnostics can begin. The developer can ask the user if the file exists, if not walk through diagnostic steps to determine the cause of the issue.
Common causes of a server issue, under services in Windows Task Manager the service MSSQLServer status needed to be “Running”, if not start the service via remote login or walk the user through starting the service. If the user does not have permissions for this their support desk may need to be involved to start the service. If MSSQLServer is not present then an installation is required. An installation can be done through your setup or by the user (not a preferred method).
If the database is not present or the table(s) are not present a process needs to be in place suitable for the organization or have a utility based on code presented in the class project SqlServerCheckLibrary.
Note, the code in SqlServerCheckLibrary is C# which is invoked in VB.NET code so by following how the code in SqlServerCheckLibrary is used in VB.NET provides you the developer a pattern to use in a support utility to discover issues by showing available servers, databases and tables.
Code break down
There are two base classes, BaseExceptionHandler and BaseSqlServerConnection which are inherited by DataOperations.
BaseExceptionHander which provides properties that are used in DataOperations to assist with propagating runtime exceptions all the way back to DataOperations then to the DataOperations caller, in the case a form.
Imports System.Data.SqlClient
Namespace Classes
Public Class BaseExceptionsHandler
' ReSharper disable once InconsistentNaming
Protected mHasException As Boolean
''' <summary>
''' Indicate the last operation thrown an
''' exception or not
''' </summary>
''' <returns></returns>
Public ReadOnly Property HasException() As Boolean
Get
Return mHasException
End Get
End Property
' ReSharper disable once InconsistentNaming
Protected mException As Exception
''' <summary>
''' Provides access to the last exception thrown
''' </summary>
''' <returns></returns>
Public ReadOnly Property LastException() As Exception
Get
Return mException
End Get
End Property
''' <summary>
''' Indicates if there was a sql related exception
''' </summary>
Public ReadOnly Property HasSqlException() As Boolean
Get
If LastException IsNot Nothing Then
Return TypeOf LastException Is SqlException
Else
Return False
End If
End Get
End Property
''' <summary>
''' If you don't need the entire exception as in
''' LastException this provides just the text of the exception
''' </summary>
''' <returns></returns>
Public ReadOnly Property LastExceptionMessage() As String
Get
Return LastException.Message
End Get
End Property
''' <summary>
''' Indicate for return of a function if there was an
''' exception thrown or not.
''' </summary>
''' <returns></returns>
Public ReadOnly Property IsSuccessFul() As Boolean
Get
Return Not HasException
End Get
End Property
' ReSharper disable once InconsistentNaming
Protected mDiagnosticErrors As String
Public ReadOnly Property DiagnosticErrors() As String
Get
Return mDiagnosticErrors
End Get
End Property
''' <summary>
''' Returns an array of the entire exception list in reverse order
''' (innermost to outermost exception)
''' </summary>
''' <param name="ex">The original exception to work off</param>
''' <returns>Array of Exceptions from innermost to outermost</returns>
Public Function InnerExceptions(ex As Exception) As Exception()
Dim exceptions As New List(Of Exception)()
exceptions.Add(ex)
Dim currentEx As Exception = ex
Do While currentEx.InnerException IsNot Nothing
exceptions.Add(currentEx)
Loop
' Reverse the order to the innermost is first
exceptions.Reverse()
Return exceptions.ToArray()
End Function
End Class
End Namespace
BaseSqlServerConnection which is responsible for building connections in DataOperations along with code to write the confirmation file (which indicates is exists that server, database and tables did exists).
Namespace Classes
Public MustInherit Class BaseSqlServerConnection
Inherits BaseExceptionsHandler
''' <summary>
''' This points to your database server - change to match
''' your server.
''' </summary>
Protected Server As String = "KARENS-PC"
''' <summary>
''' Name of database containing required tables
''' </summary>
Protected DefaultCatalog As String = "NorthWindAzure"
Protected ConfirmationFileName As String =
IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Database.txt")
Public ReadOnly Property LoadWithCheck As Boolean
Get
Return IO.File.Exists(ConfirmationFileName)
End Get
End Property
Public Function WriteConfirmation() As Boolean
Try
IO.File.WriteAllText(ConfirmationFileName, "")
Return True
Catch ex As Exception
Return False
End Try
End Function
Public Function RemoveConfirmationFile() As ConfirmationRemove
If IO.File.Exists(ConfirmationFileName) Then
Try
IO.File.Delete(ConfirmationFileName)
Return ConfirmationRemove.Successful
Catch e As Exception
mHasException = True
mException = e
Return ConfirmationRemove.Failed
End Try
End If
Return ConfirmationRemove.FileNotFound
End Function
Public ReadOnly Property ConnectionString() As String
Get
Return $"Data Source={Server};Initial Catalog={DefaultCatalog};Integrated Security=True"
End Get
End Property
End Class
End Namespace
When the main form loads a call is made to the following method in DataOperations to determine server, database and tables are available.
Public Async Function GetReportListAsync() As Task(Of List(Of Report))
mHasException = False
Dim reportList As New List(Of Report)
' this section checks to see if the server and catalog exists
Dim diag As New DatabaseDiagnostics(Server, DefaultCatalog)
If Not Await diag.Check Then
If Not String.IsNullOrWhiteSpace(diag.Errors) Then
mDiagnosticErrors = diag.Errors
mHasException = True
End If
If diag.HasException Then
mDiagnosticErrors = diag.Errors
mHasException = True
End If
Return reportList
Else
' get table names
mAvailableTableNames = diag.TableNames
ReportListTablesAvailable()
Using cn As New SqlConnection With {.ConnectionString = ConnectionString}
Using cmd As New SqlCommand With {.Connection = cn}
cmd.CommandText =
<SQL>
SELECT
r.id, r.Name,
r.Description,
rs.Statement,
rs.id
FROM
Report AS r INNER JOIN ReportStatements AS rs ON r.id = rs.ReportId
</SQL>.Value
Try
cn.Open()
Dim reader As SqlDataReader = cmd.ExecuteReader
While reader.Read
reportList.Add(New Report With
{
.Id = reader.GetInt32(0),
.Name = reader.GetString(1),
.Description = reader.GetString(2),
.Statement = reader.GetString(3),
.StatementId = reader.GetInt32(4)
})
End While
Catch ex As Exception
mHasException = True
mException = ex
End Try
End Using
End Using
End If
Return reportList
End Function
Calls are made into the C# library to determine if the server, database and tables are available.
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Linq;
using System.Threading.Tasks;
namespace SqlServerCheckLibrary
{
/// <summary>
/// Permits checking if a server and database exists.
/// </summary>
public class Utilities
{
string _exceptionMessage;
public string ExceptionMessage => _exceptionMessage;
bool _hasException;
public bool HasException => _hasException;
/// <summary>
/// Determine if a specific SQL-Server is available
/// </summary>
/// <param name="pServerName">Server name to work with</param>
/// <returns></returns>
public async Task<bool> SqlServerIsAvailableAsync(string pServerName)
{
bool success = false;
try
{
await Task.Run(() =>
{
var sqlDataSourceEnumeratorInstance = SqlDataSourceEnumerator.Instance;
DataTable dt = sqlDataSourceEnumeratorInstance.GetDataSources();
if (dt != null)
{
if (dt.Rows.Count > 0)
{
var row = dt
.AsEnumerable()
.FirstOrDefault
(
r => r.Field<string>("ServerName") == pServerName.ToUpper()
);
success = row != null;
}
else
{
success = false;
}
}
}).ConfigureAwait(false);
}
catch (Exception ex)
{
_hasException = true;
_exceptionMessage = ex.Message;
}
return success;
}
/// <summary>
/// Determines if a catalog/database exist on a specific instance of SQL-Server
/// </summary>
/// <param name="pServer"></param>
/// <param name="pDatabase"></param>
/// <returns></returns>
public async Task<bool> DatabaseExistsAsync(string pServer, string pDatabase)
{
bool success = false;
var testServer = await SqlServerIsAvailableAsync(pServer).ConfigureAwait(false);
if (!testServer)
{
return false;
}
try
{
var connectionString = ("Data Source=" + (pServer + ";Initial Catalog=master;" +
"Integrated Security=True;"));
var commandText =
("select * from master.dbo.sysdatabases where name='" + (pDatabase + "'"));
using (var cn = new SqlConnection { ConnectionString = connectionString })
{
using (var cmd = new SqlCommand { Connection = cn, CommandText = commandText })
{
cn.Open();
var reader = await cmd.ExecuteReaderAsync().ConfigureAwait(false);
success = reader.HasRows;
}
}
}
catch (Exception e)
{
_hasException = true;
_exceptionMessage = e.Message;
}
return success;
}
/// <summary>
/// Get table names for a database that exists on an available SQL-Server
/// </summary>
/// <param name="pServer">Server name</param>
/// <param name="pDatabase">Database name</param>
/// <returns></returns>
public List<string> TableNames(string pServer, string pDatabase)
{
var tableNames = new List<string>();
var connectionString = $"Data Source={pServer};Initial Catalog={pDatabase};Integrated Security=True";
using (var cn = new SqlConnection { ConnectionString = connectionString })
{
using (var cmd = new SqlCommand() { Connection = cn })
{
cmd.CommandText =
@"SELECT s.name, o.name
FROM sys.objects o WITH(NOLOCK)
JOIN sys.schemas s WITH(NOLOCK)
ON o.schema_id = s.schema_id
WHERE o.is_ms_shipped = 0 AND RTRIM(o.type) = 'U'
ORDER BY s.name ASC, o.name ASC";
cn.Open();
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
var tableName = reader.GetString(1);
if (!tableName.Contains("sysdiagrams"))
{
tableNames.Add(tableName);
}
}
}
}
}
return tableNames;
}
}
}
Returning to VB.NET, in form load after the checks are done the code will report issues, delete the confirmation file is found and disable controls so nothing can be done as there is no data to work with. If server, database and tables are found and the tables are loaded the application presents the data and writes the confirmation file. Next time the application runs no checks are done and the data is simply loaded. If another issue arises remove the confirmation file and run through the checks again.
Private Async Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
Label1.Text = ""
Dim reportList As List(Of Report)
If ops.LoadWithCheck Then
AdvancedPanel1.Dispose()
reportList = ops.GetReportList
Else
reportList = Await ops.GetReportListAsync
AdvancedPanel1.Dispose()
If ops.HasException Then
ops.RemoveConfirmationFile()
ActiveControl = closeApplicationButton
MessageBox.Show(ops.DiagnosticErrors)
Exit Sub
End If
If ops.HasException Then
ops.RemoveConfirmationFile()
ActiveControl = closeApplicationButton
MessageBox.Show($"Error: {ops.LastException.Message}")
Exit Sub
End If
If Not ops.RequiredTablesAvailable Then
ops.RemoveConfirmationFile()
ActiveControl = closeApplicationButton
MessageBox.Show($"Contact support with error code A1027Y{Environment.NewLine}Press Ok to exit.")
Exit Sub
End If
End If
bsReports.DataSource = reportList
ListBox1.DataSource = bsReports
ListBox1.DisplayMember = "Name"
txtSqlStatement.DataBindings.Add("Text", bsReports, "Statement")
Label1.DataBindings.Add("Text", bsReports, "Description")
AddHandler bsReports.PositionChanged, AddressOf PositonChanged
GetParameters(CType(bsReports.Current, Report).StatementId)
executeScriptButton.Enabled = True
GetDataSynchronouslyButton.Enabled = True
cmdRemoveConfirmationFile.Enabled = True
ops.WriteConfirmation()
End Sub
When server, database and tables are available a synchronous method is used to read the data.
Public Function GetReportList() As List(Of Report)
mHasException = False
Dim reportList As New List(Of Report)
' this section checks to see if the server and catalog exists
Dim diag As New DatabaseDiagnostics(Server, DefaultCatalog)
' get table names
mAvailableTableNames = diag.TableNames
Using cn As New SqlConnection With {.ConnectionString = ConnectionString}
Using cmd As New SqlCommand With {.Connection = cn}
cmd.CommandText =
<SQL>
SELECT
r.id, r.Name,
r.Description,
rs.Statement,
rs.id
FROM
Report AS r INNER JOIN ReportStatements AS rs ON r.id = rs.ReportId
</SQL>.Value
Try
cn.Open()
Dim reader As SqlDataReader = cmd.ExecuteReader
While reader.Read
reportList.Add(New Report With
{
.Id = reader.GetInt32(0),
.Name = reader.GetString(1),
.Description = reader.GetString(2),
.Statement = reader.GetString(3),
.StatementId = reader.GetInt32(4)
})
End While
Catch ex As Exception
mHasException = True
mException = ex
End Try
End Using
End Using
Return reportList
End Function
Usage instructions
Add the following classes from Code sample project to your project.
BaseExceptionsHandler, BaseSqlServerConnection, DatabaseDiagnostics. There is also ConfirmationRemove which is used for determining success of the confirmation file but would highly suggest instead of writing a blank text file write diagnostics into this which each programmer will have a different idea of what to include in this file. In addition if users have permissions to write to the computer's application events consider adding an error event entry.
Add the project SqServerCheckLibrary to your Visual Studio solution which contains your project.
In your data class, inherit from BaseSqlServerConnection which you can see how this is done in DataOperations class.
In your class method for reading data (you should not be coding data operations in your form) set the function signature to a Async Function. In this example the result is a List, you could do the same for a DataTable. Create an instance of the class DatabaseDiagnostics passing as the first parameter the SQL-Server name instance (or SQLEXPRESS) for reading data, the second parameter is the catalog (database) to which read is to be read from. Invoke CheckServerDatabase method of the variable just created for DatabaseDiagnostics. If CheckServerDatabase returns false, integrate Errors property of the variable for DatabaseDiagnostics, assign them to mDiagnosticErrors (from BaseExceptionsHandler) which can be used by your data class which inherits from BaseSqlServerConnection.
Back in your form, check to see if there were exceptions thrown by checking HasException property of the data class. HasException comes from BaseExceptionHandler through BaseSqlServerConnection class. If there are exceptions inform the user, if no exceptions load your data into controls such as TextBox, DataGridView, CheckedListBox etc.
Review Form1’s load event to get a sound picture of how this all fits together.
Testing
Compile the solution and execute CodeSample project. As is this will error out as the server is pointing to KARENS-PC. Next change the server to your server, run the app again, this time an exception will be thrown for not finding the database. Create the database, run again, more errors because there are no tables. In the Code sample project under the folder DatabaseScripts grab the SQL to create the tables and populate them. Run the app again and data should load. Close the app, run again, this time the app will be much faster loading data as there are no server, database or table checks. To repeat this simply press the third button, this deletes the check/confirmation file. Opening the app again will invoke checks of availability on server, database and tables.
Another way to check the code, open task manager to the services tab, find MSSQLSERVER, right click and stop the service then run the code followed by restarting the service and running the code once more.
Summary
In this article techniques have been presented to assist with diagnosing issues where a server is not available, a database is not available on an existing available server or tables are not available on an existing available server with an available database. From here there are others things which can be done such as write to a log file of issues found where the log file is appended too so that history is maintained and if this is happening frequently there may be other outside things interfering with why server, database or tables are not ready.
See also
Understanding and Configuring Microsoft SQL Server Services
Source code
https://github.com/karenpayneoregon/SqlServerCheckIfServerDatabaseTableExists
Resources
MSDN: Determine Whether the Database Engine Is Installed and Started
MSDN: Start, Stop, Pause, Resume, Restart SQL Server Services
MSDN: Troubleshoot Connecting to the SQL Server Database Engine