Table of Contents [Hide/Show]
Overview Sample Implementation Prep Work Configuration File Settings Derived Class ExecuteNonQuery VB.NET C# FillTable VB C# FillDataSet VB.NET C# Source Code VB.NET C#
SqlDatabaseWeb
System.Configuration
Parser Class
web.config
app.config
<configuration> <appSettings> <add key="Environment.default" value="Dev"/> <add key="Environment.debugger" value="Dev"/> <add key="Environment.DEVELOPMENTWEBSERVER" value="Dev"/> <add key="Environment.PRODUCTIONWEBSERVER" value="Prod"/> . . . </appSettings> <connectionStrings> <add name="DatabaseName.Dev" connectionString="DevelopmentConnectionString"/> <add name="DatabaseName.Prod" connectionString="ProductionConnectionString"/> </connectionStrings> . . .
_dbName
Public Class MyDb Inherits SqlDatabaseWeb Private Const _dbName As String = "MYDB" Public Function GetUsers(ByVal nameContains As String) As DataTable Dim conn As SqlConnection = Nothing Dim cmd As SqlCommand = Nothing Dim dt As DataTable = Nothing Try conn = MyBase.OpenConnection(_dbName) cmd = MyBase.PrepCommand(conn, "dbo.GetUsers") cmd.Parameters.AddWithValue("NameContains", nameContains) dt = MyBase.FillTable(cmd) Return dt Catch ex As Exception MyBase.DebugTable(dt) Throw ex Finally MyBase.CleanUp(conn, cmd) End Try End Function
Dim conn As SqlConnection = Nothing Dim cmd As SqlCommand = Nothing Try conn = MyBase.OpenConnection(_dbName) cmd = MyBase.PrepCommand(conn, "") ' TODO: Specify stored proc here 'TODO: Specify parameters cmd.ExecuteNonQuery() Catch ex As Exception Throw ex Finally MyBase.CleanUp(conn, cmd) End Try
SqlConnection conn = null; SqlCommand cmd = null; try { conn = base.OpenConnection(_dbName); cmd = base.PrepCommand(conn, ""); // TODO: Insert stored proc // TODO: Specify parameters cmd.ExecuteNonQuery(); } catch (Exception ex) { throw ex; } finally { base.CleanUp(ref conn, ref cmd); }
Dim conn As SqlConnection = Nothing Dim cmd As SqlCommand = Nothing Dim dt As MyDataTable = Nothing Try conn = MyBase.OpenConnection(_dbName) cmd = MyBase.PrepCommand(conn, "") ' TODO: Specify stored proc here 'TODO: Specify parameters 'dt = MyBase.FillTable(cmd) 'dt = MyBase.FillTable(of MyDataTable)(cmd) Return dt Catch ex As Exception MyBase.DebugTable(dt) Throw ex Finally MyBase.CleanUp(conn, cmd) End Try
SqlConnection conn = null; SqlCommand cmd = null; DataTable dt = null; // TODO: Specify table type try { conn = base.OpenConnection(_dbName); cmd = base.PrepCommand(conn, ""); // TODO: Specify stored proc // TODO: specify parameters //dt = base.FillTable(cmd); //dt = base.FillTable<DataTable>(cmd); // TODO: Specify table type return dt; } catch (Exception ex) { base.DebugTable(dt); throw ex; } finally { base.CleanUp(conn, cmd); }
Dim conn As SqlConnection = Nothing Dim cmd As SqlCommand = Nothing Dim ds As MyDataSet = Nothing Try conn = MyBase.OpenConnection(_dbName) cmd = MyBase.PrepCommand(conn, "") ' TODO: Specify stored proc here 'TODO: Specify parameters 'ds = MyBase.FillDataSet(cmd) 'ds = MyBase.FillDataSet(of MyDataSet)(cmd) Return ds Catch ex As Exception MyBase.DebugDataSet(ds) Throw ex Finally MyBase.CleanUp(conn, cmd) End Try
SqlConnection conn = null; SqlCommand cmd = null; MyDataSet ds = null; // TODO: Specify dataset type try { conn = base.OpenConnection(_dbName); cmd = base.PrepCommand(conn, ""); // TODO: Specify stored proc // TODO: specify parameters ds = base.FillDataSet<MyDataSet>(cmd); // TODO: Specify dataset type return ds; } catch (Exception ex) { base.DebugDataSet(ds); throw ex; } finally { base.CleanUp(ref conn, ref cmd); }
Imports Microsoft.VisualBasic Imports System.Data Imports System.Data.SqlClient Imports System.Diagnostics ''' <summary> ''' This class is a helper class for working with SqlConnection and SqlCommand objects ''' </summary> ''' <remarks></remarks> Public Class SqlDatabaseWeb Protected Function OpenConnection(ByVal dbName As String) As SqlConnection Dim conn As SqlConnection = Nothing Try Dim connName As String = GetConnName(dbName) Dim css As ConnectionStringSettings = ConfigurationManager.ConnectionStrings(connName) If css Is Nothing OrElse css.ConnectionString.Length = 0 Then Throw New Exception("Connection string [" & connName & _ "] not specified in the configuration file.") End If conn = New SqlConnection() conn.ConnectionString = css.ConnectionString conn.Open() Return conn Catch ex As Exception If conn IsNot Nothing Then If conn.State <> ConnectionState.Closed Then conn.Close() End If conn.Dispose() conn = Nothing End If Throw ex End Try End Function '---------------------------------------------------------------------------------------------- Protected Function PrepCommand(ByVal conn As SqlConnection, ByVal cmdText As String, Optional _ ByVal cmdTimeout As Integer = 30, Optional ByVal cmdType As CommandType = _ CommandType.StoredProcedure) As SqlCommand Dim cmd As SqlCommand = Nothing Try cmd = New SqlCommand() cmd.Connection = conn cmd.CommandType = cmdType cmd.CommandTimeout = cmdTimeout cmd.CommandText = cmdText Return cmd Catch ex As Exception If cmd IsNot Nothing Then cmd.Dispose() cmd = Nothing End If Throw ex End Try End Function '---------------------------------------------------------------------------------------------- Protected Function FillDataSet(ByVal cmd As SqlCommand) As DataSet Return FillDataSet(Of DataSet)(cmd) End Function '---------------------------------------------------------------------------------------------- Protected Function FillTable(ByVal dbName As String, ByVal cmdText As String, Optional ByVal _ cmdTimeout As Integer = 30, Optional ByVal cmdType As CommandType = _ CommandType.StoredProcedure) As DataTable Return FillTable(Of DataTable)(dbName, cmdText, cmdTimeout, cmdType) End Function '---------------------------------------------------------------------------------------------- Protected Function FillTable(ByVal cmd As SqlCommand) As DataTable Return FillTable(Of DataTable)(cmd) End Function '---------------------------------------------------------------------------------------------- Protected Function GetFirstRow(ByVal cmd As SqlCommand) As DataRow Return GetFirstRow(Of DataTable, DataRow)(cmd) End Function '---------------------------------------------------------------------------------------------- Protected Function GetFirstRow(ByVal dbName As String, ByVal cmdText As String, Optional _ ByVal cmdTimeout As Integer = 30, Optional ByVal cmdType As CommandType = _ CommandType.StoredProcedure) As DataRow Return GetFirstRow(Of DataTable, DataRow)(dbName, cmdText, cmdTimeout, cmdType) End Function '---------------------------------------------------------------------------------------------- Public Sub CleanUp(ByRef conn As SqlConnection, ByRef cmd As SqlCommand) If cmd IsNot Nothing Then cmd.Dispose() cmd = Nothing End If If conn IsNot Nothing Then If conn.State <> ConnectionState.Closed Then conn.Close() End If conn.Dispose() conn = Nothing End If End Sub '---------------------------------------------------------------------------------------------- Public Function CreateParameter(ByVal name As String, ByVal type As SqlDbType, Optional _ ByVal size As Integer = -1, Optional ByVal direction As ParameterDirection = _ ParameterDirection.Output) As SqlParameter Dim result As SqlParameter = New SqlParameter(name, type) result.Direction = direction If size > 0 Then result.Size = size End If Return result End Function '---------------------------------------------------------------------------------------------- Public Function CreateParameter(ByVal name As String, ByVal value As Object, Optional ByVal _ direction As ParameterDirection = ParameterDirection.Output) As SqlParameter Dim result As SqlParameter result = New SqlParameter(name, value) result.Direction = direction Return result End Function '---------------------------------------------------------------------------------------------- Public 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 '---------------------------------------------------------------------------------------------- Public Sub DebugDataSet(ByVal ds As DataSet) If ds IsNot Nothing Then For Each dt As DataTable In ds.Tables DebugTable(dt) Next End If End Sub '---------------------------------------------------------------------------------------------- Public Function GetCommandText(ByVal cmd As SqlCommand) As String Dim result As String = "" Try Select Case cmd.CommandType Case CommandType.StoredProcedure result = "exec " result &= cmd.CommandText result &= " " Dim imax As Integer = cmd.Parameters.Count - 1 For i As Integer = 0 To imax If i > 0 Then result &= ", " End If Dim p As SqlParameter = cmd.Parameters(i) If p.Value Is DBNull.Value Then result &= "NULL" Else Select Case p.DbType Case DbType.AnsiString, DbType.AnsiStringFixedLength, _ DbType.Guid, DbType.String, DbType.StringFixedLength, DbType.Xml result &= "'" result &= p.Value.ToString().Replace("'", "''") result &= "'" Case DbType.Date, DbType.DateTime, DbType.DateTime2, _ DbType.DateTimeOffset, DbType.Time result &= "'" result &= Convert.ToDateTime(p.Value).ToString( _ "MM/dd/yyyy hh:mm:ss tt") result &= "'" Case Else result &= p.Value.ToString() End Select End If Next Case CommandType.Text result = cmd.CommandText End Select Return result Catch ex As Exception Throw ex End Try End Function '---------------------------------------------------------------------------------------------- ''' <summary> ''' For the specified database, gets the name of the connection string from the ''' app.config/web.config file, based on the local machine name and whether the debugger is ''' attached. ''' </summary> Public Shared Function GetConnName(ByVal dbName As String) As String Dim result As String = "" Dim envKey As String = GetEnvironmentKey() If envKey.Length > 0 Then result = ConfigurationManager.AppSettings(envKey) If result IsNot Nothing AndAlso result.Length > 0 Then result = dbName & "." & result End If End If Return result End Function '---------------------------------------------------------------------------------------------- ''' <summary> ''' For the specified database, gets the connection string from the app.config/web.config ''' file, based on the local machine name and whether the debugger is attached. ''' </summary> Public Shared Function GetConnString(ByVal dbName As String) As String Dim result As String = "" Dim name As String = GetConnName(dbName) Dim css As ConnectionStringSettings = ConfigurationManager.ConnectionStrings(name) If css IsNot Nothing Then result = css.ConnectionString End If Return result End Function '---------------------------------------------------------------------------------------------- Public Function DbNullable(Of T As Structure)(ByVal input As Nullable(Of T)) As Object If input.HasValue Then Return input.Value Else Return DBNull.Value End If End Function '---------------------------------------------------------------------------------------------- Private Shared Function GetEnvironmentKey() As String Dim result As String = "" If System.Diagnostics.Debugger.IsAttached Then result = "Environment.debugger" Else result = "Environment." + Environment.MachineName.ToUpper() End If If result Is Nothing Then result = "" End If Return result End Function '== Primitives ================================================================================ Protected Function FillDataSet(Of DataSetType As {DataSet, New})(ByVal cmd As SqlCommand) As _ DataSetType Dim ds As DataSetType = Nothing Dim da As SqlDataAdapter = Nothing Try ds = New DataSetType() da = New SqlDataAdapter(cmd) 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) Return ds Catch ex As Exception If ds IsNot Nothing Then For Each t As DataTable In ds.Tables DebugTable(t) Next End If Throw ex Finally If da IsNot Nothing Then da.Dispose() da = Nothing End If End Try End Function '---------------------------------------------------------------------------------------------- Protected Function FillTable(Of DataTableType As {DataTable, New})(ByVal dbName As String, _ ByVal cmdText As String, Optional ByVal cmdTimeout As Integer = 30, Optional ByVal cmdType _ As CommandType = CommandType.StoredProcedure) As DataTableType Dim conn As SqlConnection = Nothing Dim cmd As SqlCommand = Nothing Dim dt As DataTableType = Nothing Try conn = OpenConnection(dbName) cmd = PrepCommand(conn, cmdText, cmdTimeout, cmdType) dt = FillTable(Of DataTableType)(cmd) Return dt Catch ex As Exception DebugTable(dt) Throw ex Finally CleanUp(conn, cmd) End Try End Function '---------------------------------------------------------------------------------------------- Protected Function FillTable(Of DataTableType As {DataTable, New})(ByVal cmd As _ SqlCommand) As DataTableType Dim dt As DataTableType = Nothing Dim da As SqlDataAdapter = Nothing Try dt = New DataTableType() da = New SqlDataAdapter(cmd) da.Fill(dt) Return dt Catch ex As Exception DebugTable(dt) Throw ex Finally If da IsNot Nothing Then da.Dispose() da = Nothing End If End Try End Function '---------------------------------------------------------------------------------------------- Protected Function GetFirstRow(Of DataTableType As {DataTable, New}, DataRowType As DataRow)( _ ByVal cmd As SqlCommand) As DataRowType Dim result As DataRowType = Nothing Dim dt As DataTableType = FillTable(Of DataTableType)(cmd) If dt IsNot Nothing AndAlso dt.Rows.Count > 0 Then result = TryCast(dt.Rows(0), DataRowType) End If Return result End Function '---------------------------------------------------------------------------------------------- Protected Function GetFirstRow(Of DataTableType As {DataTable, New}, DataRowType As DataRow)( _ ByVal dbName As String, ByVal cmdText As String, Optional ByVal cmdTimeout As Integer = 30, _ Optional ByVal cmdType As CommandType = CommandType.StoredProcedure) As DataRowType Dim result As DataRowType = Nothing Dim dt As DataTableType = FillTable(Of DataTableType)(dbName, cmdText, cmdTimeout, cmdType) If dt IsNot Nothing AndAlso dt.Rows.Count > 0 Then result = TryCast(dt.Rows(0), DataRowType) End If Return result End Function End Class
using System; using System.Collections.Generic; using System.Text; using System.Data; using System.Data.SqlClient; using System.Configuration; using System.Diagnostics; namespace LibSystem.Data { public class SqlDatabaseWeb { public SqlConnection OpenConnection(string dbName) { SqlConnection conn = null; try { string connName = GetConnName(dbName); ConnectionStringSettings css = ConfigurationManager.ConnectionStrings[connName]; if (css == null || css.ConnectionString.Length == 0) throw new Exception("Connection string [" + connName + "] not specified in " + "the configuration file."); conn = new SqlConnection(css.ConnectionString); conn.Open(); return conn; } catch (Exception ex) { if (conn != null) { if (conn.State != ConnectionState.Closed) conn.Close(); conn.Dispose(); conn = null; } throw ex; } } //----------------------------------------------------------------------------------------- public SqlCommand PrepCommand(SqlConnection conn, string cmdText) { return PrepCommand(conn, cmdText, 30, CommandType.StoredProcedure); } //----------------------------------------------------------------------------------------- public SqlCommand PrepCommand(SqlConnection conn, string cmdText, int cmdTimeout) { return PrepCommand(conn, cmdText, cmdTimeout, CommandType.StoredProcedure); } //----------------------------------------------------------------------------------------- public SqlCommand PrepCommand(SqlConnection conn, string cmdText, int cmdTimeout, CommandType cmdType) { SqlCommand cmd = null; try { cmd = new SqlCommand(cmdText, conn); cmd.CommandType = cmdType; cmd.CommandTimeout = cmdTimeout; return cmd; } catch (Exception ex) { if (cmd != null) { cmd.Dispose(); cmd = null; } throw ex; } } //----------------------------------------------------------------------------------------- public DataSet FillDataSet(SqlCommand cmd) { return FillDataSet<DataSet>(cmd); } //----------------------------------------------------------------------------------------- public DataTableType FillTable<DataTableType>(string dbName, string cmdText) where DataTableType : DataTable, new() { return FillTable<DataTableType>(dbName, cmdText, 30, CommandType.StoredProcedure); } //----------------------------------------------------------------------------------------- public DataTableType FillTable<DataTableType>(string dbName, string cmdText, int cmdTimeout) where DataTableType : DataTable, new() { return FillTable<DataTableType>(dbName, cmdText, cmdTimeout, CommandType.StoredProcedure); } //----------------------------------------------------------------------------------------- public DataTable FillTable(string dbName, string cmdText, int cmdTimeout, CommandType cmdType) { return FillTable<DataTable>(dbName, cmdText, cmdTimeout, cmdType); } //----------------------------------------------------------------------------------------- public DataTable FillTable(string dbName, string cmdText, int cmdTimeout) { return FillTable<DataTable>(dbName, cmdText, cmdTimeout, CommandType.StoredProcedure); } //----------------------------------------------------------------------------------------- public DataTable FillTable(string dbName, string cmdText) { return FillTable<DataTable>(dbName, cmdText, 30, CommandType.StoredProcedure); } //----------------------------------------------------------------------------------------- public DataTable FillTable(SqlCommand cmd) { return FillTable<DataTable>(cmd); } //----------------------------------------------------------------------------------------- public DataRow GetFirstRow(SqlCommand cmd) { return GetFirstRow<DataTable, DataRow>(cmd); } //----------------------------------------------------------------------------------------- public T ExecuteScalar<T>(SqlCommand cmd) { T result; if (!Parser<T>.TryParse(cmd.ExecuteScalar().ToString(), out result)) result = default(T); return result; } //----------------------------------------------------------------------------------------- public T ExecuteScalar<T>(string dbName, string cmdText, int cmdTimeout) { return ExecuteScalar<T>(dbName, cmdText, cmdTimeout, CommandType.StoredProcedure); } //----------------------------------------------------------------------------------------- public string ExecuteScalar(string dbName, string cmdText, int cmdTimeout, CommandType cmdType) { return ExecuteScalar<string>(dbName, cmdText, cmdTimeout, cmdType); } //----------------------------------------------------------------------------------------- public string ExecuteScalar(string dbName, string cmdText, int cmdTimeout) { return ExecuteScalar<string>(dbName, cmdText, cmdTimeout, CommandType.StoredProcedure); } //----------------------------------------------------------------------------------------- public string ExecuteScalar(string dbName, string cmdText) { return ExecuteScalar<string>(dbName, cmdText, 30, CommandType.StoredProcedure); } //----------------------------------------------------------------------------------------- public T ExecuteScalar<T>(string dbName, string cmdText) { return ExecuteScalar<T>(dbName, cmdText, 30, CommandType.StoredProcedure); } //----------------------------------------------------------------------------------------- public DataRow GetFirstRow<DataTableType, DataRowType>(string dbName, string cmdText, int cmdTimeout) where DataTableType : DataTable, new() where DataRowType : DataRow { return GetFirstRow<DataTableType, DataRowType>(dbName, cmdText, cmdTimeout, CommandType.StoredProcedure); } //----------------------------------------------------------------------------------------- public DataRow GetFirstRow<DataTableType, DataRowType>(string dbName, string cmdText) where DataTableType : DataTable, new() where DataRowType : DataRow { return GetFirstRow<DataTableType, DataRowType>(dbName, cmdText, 30, CommandType.StoredProcedure); } //----------------------------------------------------------------------------------------- public DataRow GetFirstRow(string dbName, string cmdText, int cmdTimeout, CommandType cmdType) { return GetFirstRow<DataTable, DataRow>(dbName, cmdText, cmdTimeout, cmdType); } //----------------------------------------------------------------------------------------- public DataRow GetFirstRow(string dbName, string cmdText, int cmdTimeout) { return GetFirstRow(dbName, cmdText, cmdTimeout, CommandType.StoredProcedure); } //----------------------------------------------------------------------------------------- public DataRow GetFirstRow(string dbName, string cmdText) { return GetFirstRow(dbName, cmdText, 30, CommandType.StoredProcedure); } //----------------------------------------------------------------------------------------- public void CleanUp(ref SqlConnection conn, ref SqlCommand cmd) { if (cmd != null) { cmd.Dispose(); cmd = null; } if (conn != null) { if (conn.State != ConnectionState.Closed) conn.Close(); conn.Dispose(); conn = null; } } //----------------------------------------------------------------------------------------- public SqlParameter CreateParameter(string name, SqlDbType type) { return CreateParameter(name, type, -1, ParameterDirection.Output); } //----------------------------------------------------------------------------------------- public SqlParameter CreateParameter(string name, SqlDbType type, int size) { return CreateParameter(name, type, size, ParameterDirection.Output); } //----------------------------------------------------------------------------------------- public SqlParameter CreateParameter(string name, SqlDbType type, int size, ParameterDirection direction) { SqlParameter result = new SqlParameter(name, type); result.Direction = direction; if (size > 0) result.Size = size; return result; } //----------------------------------------------------------------------------------------- public SqlParameter CreateParameter(string name, object value) { return CreateParameter(name, value, ParameterDirection.Input); } //----------------------------------------------------------------------------------------- public SqlParameter CreateParameter(string name, object value, ParameterDirection direction) { SqlParameter result = new SqlParameter(name, value); result.Direction = direction; return result; } //----------------------------------------------------------------------------------------- public void DebugTable(DataTable t) { if (t != null && t.HasErrors) { Debug.Print("Errors in the [" + t.TableName + "] table..."); 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)); } } } //----------------------------------------------------------------------------------------- public void DebugDataSet(DataSet ds) { if (ds != null) foreach (DataTable dt in ds.Tables) DebugTable(dt); } //----------------------------------------------------------------------------------------- public string GetCommandText(SqlCommand cmd) { string result = ""; try { switch (cmd.CommandType) { case CommandType.StoredProcedure: result = "exec "; result += cmd.CommandText; result += " "; int imax = cmd.Parameters.Count - 1; for (int i = 0; i <= imax; i++) { if (i > 0) result += ", "; SqlParameter p = cmd.Parameters[i]; if (p.Value == DBNull.Value) result += "NULL"; else switch (p.DbType) { case DbType.AnsiString: case DbType.AnsiStringFixedLength: case DbType.Guid: case DbType.String: case DbType.StringFixedLength: case DbType.Xml: result += "'"; result += p.Value.ToString().Replace("'", "''"); break; case DbType.Date: case DbType.DateTime: case DbType.DateTime2: case DbType.DateTimeOffset: case DbType.Time: result += "'"; result += Convert.ToDateTime(p.Value).ToString("MM/dd/yyyy hh:mm:ss tt"); result += "'"; break; default: result += p.Value.ToString(); break; } } break; case CommandType.Text: result = cmd.CommandText; break; } return result; } catch (Exception ex) { throw ex; } } //----------------------------------------------------------------------------------------- public static string GetConnName(string dbName) { string result = ""; string envKey = GetEnvironmentKey(); if (envKey.Length > 0) { result = ConfigurationManager.AppSettings[envKey]; if (result != null && result.Length > 0) result = dbName + "." + result; } return result; } //----------------------------------------------------------------------------------------- public string GetConnString(string dbName) { string result = ""; string name = GetConnName(dbName); ConnectionStringSettings css = ConfigurationManager.ConnectionStrings[name]; if (css != null) result = css.ConnectionString; return result; } //----------------------------------------------------------------------------------------- public static string GetEnvironmentKey() { string result = ""; if (System.Diagnostics.Debugger.IsAttached) result = "Environment.debugger"; else result = "Environment." + Environment.MachineName.ToUpper(); if (result == null) result = ""; return result; } //----------------------------------------------------------------------------------------- public object DbNullable<T>(Nullable<T> input) where T : struct { if (input.HasValue) return input.Value; else return DBNull.Value; } //= Primitives ============================================================================ public DataSetType FillDataSet<DataSetType>(SqlCommand cmd) where DataSetType : DataSet, new() { DataSetType ds = null; SqlDataAdapter da = null; try { ds = new DataSetType(); da = new SqlDataAdapter(cmd); 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); return ds; } catch (Exception ex) { if (ds != null) foreach (DataTable t in ds.Tables) DebugTable(t); throw ex; } finally { if (da != null) { da.Dispose(); da = null; } } } //----------------------------------------------------------------------------------------- public DataTableType FillTable<DataTableType>(string dbName, string cmdText, int cmdTimeout, CommandType cmdType) where DataTableType : DataTable, new() { SqlConnection conn = null; SqlCommand cmd = null; DataTableType dt = null; try { conn = OpenConnection(dbName); cmd = PrepCommand(conn, cmdText, cmdTimeout, cmdType); dt = FillTable<DataTableType>(cmd); return dt; } catch (Exception ex) { DebugTable(dt); throw ex; } finally { CleanUp(ref conn, ref cmd); } } //----------------------------------------------------------------------------------------- public DataTableType FillTable<DataTableType>(SqlCommand cmd) where DataTableType : DataTable, new() { DataTableType dt = null; SqlDataAdapter da = null; try { dt = new DataTableType(); da = new SqlDataAdapter(cmd); da.Fill(dt); return dt; } catch (Exception ex) { DebugTable(dt); throw ex; } finally { if (da != null) { da.Dispose(); da = null; } } } //----------------------------------------------------------------------------------------- public DataRowType GetFirstRow<DataTableType, DataRowType>(SqlCommand cmd) where DataTableType : DataTable, new() where DataRowType : DataRow { DataRowType result = null; DataTableType t = FillTable<DataTableType>(cmd); if (t != null && t.Rows.Count > 0) result = t.Rows[0] as DataRowType; return result; } //----------------------------------------------------------------------------------------- public DataRow GetFirstRow<DataTableType, DataRowType>(string dbName, string cmdText, int cmdTimeout, CommandType cmdType) where DataTableType : DataTable, new() where DataRowType : DataRow { DataRowType result = null; DataTableType t = FillTable<DataTableType>(dbName, cmdText, cmdTimeout, cmdType); if (t != null && t.Rows.Count > 0) result = t.Rows[0] as DataRowType; return result; } //----------------------------------------------------------------------------------------- public T ExecuteScalar<T>(string dbName, string cmdText, int cmdTimeout, CommandType cmdType) { SqlConnection conn = null; SqlCommand cmd = null; try { conn = OpenConnection(dbName); cmd = PrepCommand(conn, cmdText, cmdTimeout, cmdType); return ExecuteScalar<T>(cmd); } catch (Exception ex) { throw ex; } finally { CleanUp(ref conn, ref cmd); } } } }
ScrewTurn Wiki version 3.0.1.400. Some of the icons created by FamFamFam. Except where noted, all contents Copyright © 1999-2024, Patrick Jasinski.