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