SqlDatabase Base Class - SQL Server and .NET Framework

This page is part of the Class Library Pages collection.
Click the icon to see the index.

This class is not thread safe, and not for use with ASP.NET. Use the SqlDatabaseWeb class instead for ASP.NET or applications where thread safety is an issue.

{outline||<1> - |

.<1> - }

Overview

Writing data-access code tends to be tedious and repetitive, so I developed a base class for accessing a SQL Server database: SqlDatabase. This base class could easily be adapted to other databases, using the appropriate classes. For example, to change the class to work with an OLE DB database, the SqlConnection objects would be changed to OleDbConnection and the SqlCommand objects would be changed to OleDbCommand.

Sample Data-Access Code

Constructor of the Derived Class

To use the SqlDatabase class, simply create a class that derives from it. Then create a public constructor (Sub New() in Visual Basic) that specifies the name of the connection string. For example, the following code simply initializes the _conn SqlConnection object (defined in the SqlDatabase base class) with the "SmtDatabase" connection string found in the app.config or web.config file.

Public Class MyDatabase
Inherits SqlDatabase
...
    Public Sub New()

        MyBase.New("SmtDatabase")

    End Sub
...
End Class

Snippet of Sample App.Config File

<configuration>
...
    <connectionStrings>
        <add name="SmtDatabase" 
            connectionString="Data Source=MyServer;uid=MyUser;pwd=MyPassword;database=MyDatabase;"/>
    </connectionStrings>
...
</configuration>

Sample Database Call from the Derived Class

To retrieve rows of data from the database via a stored procedure, we simply call the FillTable generic method, like this. The SqlDatabase class also has a method for a non-row-returning stored procedure — ExecuteNonQuery — and also has equivalent methods for using SQL statements instead of stored procedures.

Private _authorId As Integer = 0

Public Function GetBooksForAuthor(authorId As Integer) As DataTable

    _authorId = authorId

    Return MyBase.FillTable(Of DataTable)( _
                "dbo.GetBooksForAuthor", _
                New SqlCommandDelegate(AddressOf GetBooksForAuthor_PreCommandExecute))

End Function
Private Sub GetBooksForAuthor_PreCommandExecute(ByVal cmd As SqlCommand)

    cmd.Parameters.AddWithValue("AuthorId", _authorId)

End Sub

Code for the Base Class

Visual Basic

Note: Be sure to have a reference to System.Configuration in your project.

Imports Microsoft.VisualBasic
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
Imports System.Diagnostics

''' <summary>
''' This class encapsulates connecting to and executing SQL commands against a SQL Server database
''' </summary>
Public Class SqlDatabase

    Public Delegate Sub SqlCommandDelegate(ByVal cmd As SqlCommand)

    Protected _conn As SqlConnection
    Protected _defaultTimeout As Integer
    Protected _openConnectionForEachQuery As Boolean
    Protected _connectionName As String

    '== Initialization and Disposal ===============================================================
    Protected Sub Init(ByVal connectionName As String)

        _connectionName = connectionName
        _openConnectionForEachQuery = True
        OpenConnection(Not _openConnectionForEachQuery)

    End Sub
    Protected Sub Init(ByVal connectionName As String, ByVal openConnectionForEachQuery As Boolean)

        _connectionName = connectionName
        _openConnectionForEachQuery = openConnectionForEachQuery
        OpenConnection(Not _openConnectionForEachQuery)

    End Sub
    Private Sub OpenConnection(ByVal open As Boolean)

        If open Then

            Dim css As ConnectionStringSettings = ConfigurationManager.ConnectionStrings(_connectionName)

            If css Is Nothing OrElse css.ConnectionString.Length = 0 Then
                Throw New Exception("Connection string [" & _connectionName & _
                    "] not specified in the configuration file.")
            End If

            _conn = New SqlConnection()
            Dim b As SqlConnectionStringBuilder = New SqlConnectionStringBuilder(css.ConnectionString)
            b.ConnectTimeout = 60
            _conn.ConnectionString = b.ConnectionString
            _conn.Open()
            _defaultTimeout = 30

        End If

    End Sub
    Private Sub CloseConnection()

        CloseConnection(True)

    End Sub
    Private Sub CloseConnection(ByVal close As Boolean)

        If close AndAlso _conn IsNot Nothing AndAlso _conn.State <> ConnectionState.Closed Then

            Try
                _conn.Close()
            Catch ex As Exception

            End Try
            Try
                _conn.Dispose()
            Catch ex As Exception

            End Try
            _conn = Nothing

        End If

    End Sub
    Public Function TestConnection() As String

        Dim t As DataTable = FillTableFromSql(Of DataTable)("SELECT GETDATE() as RESULT")
        Return CType(t.Rows(0)(0), DateTime).ToString("MM/dd/yyyy hh:mm:sstt")

    End Function
    Public Sub Dispose()

        CloseConnection(True)

    End Sub

    '== Wrappers for FillTable ====================================================================
    Protected Function FillTable(Of TableType As {DataTable, New})(ByVal storedProc As String, _
    Optional ByVal preCommandExecute As SqlCommandDelegate = Nothing) As TableType

        Return FillTable(Of TableType)(storedProc, CommandType.StoredProcedure, preCommandExecute)

    End Function
    Protected Function FillTable(ByVal storedProc As String, ByVal preCommandExecute As _
    SqlCommandDelegate) As DataTable

        Return FillTable(Of DataTable)(storedProc, CommandType.StoredProcedure, preCommandExecute)

    End Function

    '== Wrappers for FillTableFromSql =============================================================
    Protected Function FillTableFromSql(Of TableType As {DataTable, New})(ByVal sql As String, _
    Optional ByVal preCommandExecute As SqlCommandDelegate = Nothing) As TableType

        Return FillTable(Of TableType)(sql, CommandType.Text, preCommandExecute)

    End Function
    Protected Function FillTableFromSql(ByVal sql As String) As DataTable

        Return FillTable(Of DataTable)(sql, CommandType.Text, Nothing)

    End Function

    '== Wrappers for ExecuteNonQuery ==============================================================
    Protected Sub ExecuteNonQuery(ByVal storedProc As String, Optional ByVal preCommandExecute As _
    SqlCommandDelegate = Nothing, Optional ByVal postCommandExecute As SqlCommandDelegate = Nothing)

        ExecuteNonQuery(storedProc, CommandType.StoredProcedure, preCommandExecute, postCommandExecute)

    End Sub

    '== Wrappers for ExecuteNonQueryForSql ========================================================
    Protected Sub ExecuteNonQueryForSql(ByVal sql As String, Optional ByVal preCommandExecute As _
    SqlCommandDelegate = Nothing, Optional ByVal postCommandExecute As SqlCommandDelegate = Nothing)

        ExecuteNonQuery(sql, CommandType.Text, preCommandExecute, postCommandExecute)

    End Sub

    '== Primitives ================================================================================
    Protected Function ExecuteScalar(ByVal cmdText As String, ByVal preCommandExecute As _
    SqlCommandDelegate) As Object

        Dim cmd As SqlCommand = Nothing
        Dim result As Object = Nothing

        Try

            OpenConnection(_openConnectionForEachQuery)
            cmd = New SqlCommand()
            cmd.Connection = _conn
            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandTimeout = 0
            cmd.CommandText = cmdText

            If preCommandExecute IsNot Nothing Then
                preCommandExecute(cmd)
            End If

            result = cmd.ExecuteScalar()

            CloseConnection(_openConnectionForEachQuery)

        Catch ex As Exception

            If cmd IsNot Nothing Then
                cmd.Dispose()
            End If

            CloseConnection(_openConnectionForEachQuery)
            Throw ex

        End Try

        Return result

    End Function
    Private Sub ExecuteNonQuery(ByVal cmdText As String, ByVal cmdType As CommandType, ByVal _
    preCommandExecute As SqlCommandDelegate, ByVal postCommandExecute As SqlCommandDelegate)

        Dim cmd As SqlCommand = Nothing

        Try
            OpenConnection(_openConnectionForEachQuery)
            cmd = New SqlCommand()
            cmd.Connection = _conn
            cmd.CommandType = cmdType
            cmd.CommandTimeout = _defaultTimeout
            cmd.CommandText = cmdText

            If preCommandExecute IsNot Nothing Then
                preCommandExecute(cmd)
            End If

            cmd.ExecuteNonQuery()

            If postCommandExecute IsNot Nothing Then
                postCommandExecute(cmd)
            End If

            CloseConnection(_openConnectionForEachQuery)

        Catch ex As Exception

            If cmd IsNot Nothing Then
                cmd.Dispose()
            End If
            CloseConnection(_openConnectionForEachQuery)
            Throw ex

        End Try

    End Sub
    Private Function FillTable(Of TableType As {DataTable, New})(ByVal cmdText As String, ByVal _
    cmdType As CommandType, ByVal preCommandExecute As SqlCommandDelegate) As TableType

        Dim cmd As SqlCommand = Nothing
        Dim da As SqlDataAdapter = Nothing
        Dim t As TableType = Nothing

        Try

            OpenConnection(_openConnectionForEachQuery)
            cmd = New SqlCommand()
            cmd.Connection = _conn
            cmd.CommandType = cmdType
            cmd.CommandTimeout = _defaultTimeout
            cmd.CommandText = cmdText

            If preCommandExecute IsNot Nothing Then
                preCommandExecute(cmd)
            End If

            da = New SqlDataAdapter(cmd)
            t = New TableType()
            da.Fill(t)
            cmd.Dispose()
            CloseConnection(_openConnectionForEachQuery)
            Return t

        Catch ex As Exception

            If cmd IsNot Nothing Then
                cmd.Dispose()
            End If

            If da IsNot Nothing Then
                da.Dispose()
            End If

            SqlDatabase.DebugTable(t)
            CloseConnection(_openConnectionForEachQuery)
            Throw ex

        End Try

    End Function
    Protected Function FillTables(Of DataSetType As {DataSet, New})(ByVal cmdText As String, ByVal _
    cmdType As CommandType, ByVal preCommandExecute As SqlCommandDelegate, Optional ByVal _
    postCommandExecute As SqlCommandDelegate = Nothing) As DataSetType

        Dim cmd As SqlCommand = Nothing
        Dim da As SqlDataAdapter = Nothing
        Dim ds As DataSetType = Nothing

        Try
            OpenConnection(_openConnectionForEachQuery)
            cmd = New SqlCommand()
            cmd.Connection = _conn
            cmd.CommandType = cmdType
            cmd.CommandTimeout = _defaultTimeout
            cmd.CommandText = cmdText

            If preCommandExecute IsNot Nothing Then
                preCommandExecute(cmd)
            End If

            da = New SqlDataAdapter(cmd)
            ds = New DataSetType()

            For i As Integer = 0 To ds.Tables.Count - 1

                Dim tableName As String = "Table"
                If i > 0 Then
                    tableName &= i.ToString()
                End If

                da.TableMappings.Add(tableName, ds.Tables(i).TableName)

            Next

            da.Fill(ds)

            If postCommandExecute IsNot Nothing Then
                postCommandExecute(cmd)
            End If

            cmd.Dispose()
            CloseConnection(_openConnectionForEachQuery)
            Return ds

        Catch ex As Exception

            If cmd IsNot Nothing Then
                cmd.Dispose()
            End If

            If da IsNot Nothing Then
                da.Dispose()
            End If

            If ds IsNot Nothing Then

                For Each t As DataTable In ds.Tables
                    SqlDatabase.DebugTable(t)
                Next

            End If
            CloseConnection(_openConnectionForEachQuery)
            Throw ex

        End Try

    End Function

    '== Shared Helper Functions ===================================================================
    Protected Shared Function CreateOutputParameter(ByVal name As String, ByVal type As SqlDbType, _
    Optional ByVal size As Integer = -1) As SqlParameter

        Dim result As SqlParameter = New SqlParameter(name, type)
        result.Direction = ParameterDirection.Output
        If size > 0 Then
            result.Size = size
        End If
        Return result

    End Function
    Public Shared Sub DebugTable(ByVal t As DataTable)

        If t IsNot Nothing AndAlso t.HasErrors Then

            Debug.Print("Errors in the [" + t.TableName + "] table...")

            Dim rows As DataRow() = t.GetErrors()

            For Each row As DataRow In rows

                Dim cols As DataColumn() = row.GetColumnsInError()

                For Each col As DataColumn In cols

                    Debug.Print(col.ColumnName & " -- " & row.GetColumnError(col))

                Next

            Next

        End If

    End Sub

End Class

C#

Note: Be sure to have a reference to System.Configuration in your project.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Diagnostics;

namespace LibSystem.Data
{
    public class SqlDatabase
    {
        protected static SqlConnection _conn = null;
        protected delegate void SqlCommandDelegate(SqlCommand cmd);
        protected int _defaultTimeout;
        protected bool _openConnectionForEachQuery;
        protected string _connectionName;

        public void Init(string connectionName)
        {
            Init(connectionName, false);
        }
        public void Init(string connectionName, bool openConnectionForEachQuery)
        {
            _connectionName = connectionName;
            _openConnectionForEachQuery = openConnectionForEachQuery;
            OpenConnection(!_openConnectionForEachQuery);
        }
        private void OpenConnection()
        {
            OpenConnection(true);
        }
        private void OpenConnection(bool open)
        {
            if (open)
            {
                ConnectionStringSettings css = ConfigurationManager.ConnectionStrings[_connectionName];

                if (css == null || css.ConnectionString.Length == 0)
                    throw  new Exception("Connection string [" + _connectionName + 
                        "] not specified in configuration file.");

                _conn = new SqlConnection();
                SqlConnectionStringBuilder b = new SqlConnectionStringBuilder(css.ConnectionString);
                b.ConnectTimeout = 60;
                _conn.ConnectionString = b.ConnectionString;
                _conn.Open();
                _defaultTimeout = 30;
            }
        }
        private void CloseConnection()
        {
            CloseConnection(true);
        }
        private void CloseConnection(bool close)
        {
            if (close && _conn != null && _conn.State != ConnectionState.Closed)
            {
                try{    _conn.Close();        } catch{}
                try{    _conn.Dispose();    } catch{}
                _conn = null;
            }
        }

        /// <summary>
        /// This method tests the connection by querying the database for its current system date.
        /// </summary>
        public string TestConnection()
        {
            DataTable t = FillTableFromSql<DataTable>("SELECT GETDATE() as RESULT");
            return ((DateTime)t.Rows[0][0]).ToString("MM/dd/yyyy hh:mm:sstt");
        }

        public void Dispose()
        {
            _conn.Close();
            _conn.Dispose();
            _conn = null;
        }

        protected DataTable FillTable(string storedProc, SqlCommandDelegate preCommandExecute)
        {
            return FillTable<DataTable>(storedProc, CommandType.StoredProcedure, preCommandExecute);
        }
        protected DataTable FillTableFromSql(string sql)
        {
            return FillTable<DataTable>(sql, CommandType.TableDirect, null);
        }

        /// <summary>
        /// Fills a typed DataTable from a stored procedure, assuming no parameters.
        /// </summary>
        /// <typeparam name="TableType">The typed dataset's table type to return</typeparam>
        protected TableType FillTable<TableType>(string storedProc)
            where TableType : DataTable, new()
        {
            return FillTable<TableType>(storedProc, CommandType.StoredProcedure, null);
        }
        
        protected TableType FillTable<TableType>(string storedProc, SqlCommandDelegate 
            preCommandExecute)
            where TableType : DataTable, new()
        {
            return FillTable<TableType>(storedProc, CommandType.StoredProcedure, preCommandExecute);
        }
        
        protected TableType FillTableFromSql<TableType>(string sql)
            where TableType : DataTable, new()
        {
            return FillTable<TableType>(sql, CommandType.Text, null);
        }
        
        /// <summary>
        /// Fills a typed DataTable from a stored procedure.
        /// </summary>
        /// <param name="preCommandExecute">The method that populates the Parameters collection of 
        /// the SqlCommand object</param>
        protected TableType FillTable<TableType>(string commandText, CommandType cmdType, 
            SqlCommandDelegate preCommandExecute) where TableType : DataTable, new()
        {
            SqlCommand cmd = null;
            SqlDataAdapter da = null;
            TableType t = null;

            try
            {
                OpenConnection(_openConnectionForEachQuery);
                cmd = new SqlCommand();
                cmd.Connection = _conn;
                cmd.CommandText = commandText;
                cmd.CommandType = cmdType;
                cmd.CommandTimeout = _defaultTimeout;

                if (preCommandExecute != null)
                    preCommandExecute(cmd);

                da = new SqlDataAdapter(cmd);
                t = new TableType();
                da.Fill(t);
                da.Dispose();
                cmd.Dispose();
                CloseConnection(_openConnectionForEachQuery);
                return t;
            }
            catch (Exception ex)
            {
                if (cmd != null)
                    cmd.Dispose();

                if (da != null)
                    da.Dispose();

                DebugTable(t);
                CloseConnection(_openConnectionForEachQuery);
                throw ex;
            }
        }

        /// <summary>
        /// Fills a typed DataSet from a stored procedure returning one or more resultsets.
        /// </summary>
        /// <param name="preCommandExecute">The method that populates the Parameters collection of 
        /// the SqlCommand object</param>
        protected DataSetType FillTables<DataSetType>(string commandText, CommandType cmdType, 
            SqlCommandDelegate preCommandExecute) where DataSetType : DataSet, new()
        {
            SqlCommand cmd = null;
            SqlDataAdapter da = null;
            DataSetType ds = null;

            try
            {
                OpenConnection(_openConnectionForEachQuery);
                cmd = new SqlCommand();
                cmd.Connection = _conn;
                cmd.CommandText = commandText;
                cmd.CommandType = cmdType;

                if (preCommandExecute != null)
                    preCommandExecute(cmd);

                da = new SqlDataAdapter(cmd);
                ds = new DataSetType();

                for (int i = 0; i < ds.Tables.Count; i++)
                {
                    string tableName = "Table";
                    if (i >  0)
                        tableName += i.ToString();
                    da.TableMappings.Add(tableName, ds.Tables[i].TableName);
                }

                da.Fill(ds);
                cmd.Dispose();
                CloseConnection(_openConnectionForEachQuery);
                return ds;
            }
            catch (Exception ex)
            {
                if (cmd != null)
                    cmd.Dispose();

                if (da != null)
                    da.Dispose();

                if (ds != null)
                    foreach (DataTable t in ds.Tables)
                        DebugTable(t);

                CloseConnection(_openConnectionForEachQuery);
                throw ex;
            }
        }

        protected int ExecuteNonQuery(string storedProc, SqlCommandDelegate preCommandExecute)
        {
            return ExecuteNonQuery(storedProc, preCommandExecute, null);
        }
        protected int ExecuteNonQuery(string storedProc, SqlCommandDelegate preCommandExecute, 
            SqlCommandDelegate postCommandExecute)
        {
            return ExecuteNonQuery(storedProc, preCommandExecute, postCommandExecute, _defaultTimeout);
        }
        protected int ExecuteNonQuery(string storedProc, SqlCommandDelegate preCommandExecute, 
            SqlCommandDelegate postCommandExecute, int cmdTimeout)
        {
            SqlCommand cmd = null;
            int result = -1;

            try
            {
                OpenConnection(_openConnectionForEachQuery);
                cmd = new SqlCommand();
                cmd.Connection = _conn;
                cmd.CommandText = storedProc;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandTimeout = cmdTimeout;
                
                if (preCommandExecute != null)
                    preCommandExecute(cmd);

                result = cmd.ExecuteNonQuery();

                if (postCommandExecute != null)
                    postCommandExecute(cmd);

                cmd.Dispose();
                CloseConnection(_openConnectionForEachQuery);
                return result;

            }
            catch (Exception ex)
            {
                if (cmd != null)
                    cmd.Dispose();

                CloseConnection(_openConnectionForEachQuery);
                throw ex;
            }

        }
        protected object ExecuteScalar(string storedProc, SqlCommandDelegate preCommandExecute)
        {
            SqlCommand cmd = null;
            object result = null;

            try
            {
                OpenConnection(_openConnectionEachQuery);
                cmd = new SqlCommand();
                cmd.Connection = _conn;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandTimeout = 0;
                cmd.CommandText = storedProc;
                
                if (preCommandExecute != null)
                    preCommandExecute(cmd);

                result = cmd.ExecuteScalar();

                CloseConnection(_openConnectionEachQuery);
            }
            catch (Exception ex)
            {
                if (cmd != null)
                    cmd.Dispose();

                CloseConnection(_openConnectionEachQuery);

                throw ex;
            }
            return result;
        }

        //- Helper Functions ----------------------------------------------------------------------
        public void DebugTable(DataTable t)
        {
            if (t != null && t.HasErrors)
            {
                Debug.Print("Errors in table [" + t.TableName + "]");
                DataRow[] rows = t.GetErrors();
                foreach (DataRow row in rows)
                {
                    DataColumn[] cols = row.GetColumnsInError();

                    foreach (DataColumn col in cols)
                    {
                        Debug.Print(col.ColumnName + " -- " + row.GetColumnError(col));
                    }
                }
            }
        }

        protected object GetColumnValue(DataRow row, string columnName)
        {
            if (row.IsNull(columnName))
                return DBNull.Value;
            else
                return row[columnName];
        }

        protected SqlParameter CreateOutputParameter(string name, SqlDbType type)
        {
            return CreateOutputParameter(name, type, -1);
        }

        protected SqlParameter CreateOutputParameter(string name, SqlDbType type, int size)
        {
            SqlParameter result = new SqlParameter(name, type);
            result.Direction = ParameterDirection.Output;

            if (size > 0)
                result.Size = size;

            return result;
        }
    }
}