Table of Contents [Hide/Show]
Overview Sample Data-Access Code Constructor of the Derived Class Sample Database Call from the Derived Class Code for the Base Class Visual Basic C#
SqlDatabase
SqlConnection
OleDbConnection
SqlCommand
OleDbCommand
Sub New()
_conn
app.config
web.config
Public Class MyDatabase Inherits SqlDatabase ... Public Sub New() MyBase.New("SmtDatabase") End Sub ... End Class
<configuration> ... <connectionStrings> <add name="SmtDatabase" connectionString="Data Source=MyServer;uid=MyUser;pwd=MyPassword;database=MyDatabase;"/> </connectionStrings> ... </configuration>
FillTable
ExecuteNonQuery
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
System.Configuration
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
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; } } }
ScrewTurn Wiki version 3.0.1.400. Some of the icons created by FamFamFam. Except where noted, all contents Copyright © 1999-2024, Patrick Jasinski.