.<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;
}
}
}