Jasinski Technical Wiki

Navigation

Home Page
Index
All Pages

Quick Search
»
Advanced Search »

Contributor Links

Create a new Page
Administration
File Management
Login/Logout
Your Profile

Other Wiki Sections

Software

PoweredBy

SqlDatabaseWeb Class - ASP.NET

RSS
Modified on Fri, Mar 19, 2010, 2:44 PM by Administrator Categorized as ASP·NET Web Forms, Class Library, SQL Server
This page is part of the Class Library Pages collection.
Click the icon to see the index.

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#


Overview

The SqlDatabaseWeb is a concrete base class intended to be inherited by a "database class" in your specific implementation. In addition, it has a few shared/static methods which can be used to change the connection for a table adapter. The following methods are provided by the SqlDatabaseWeb class.

MethodDescription
OpenConnection 
PrepCommand 
FillDataSet 
FillTable 
CleanUp 
CreateParameter 
DebugTable 
DebugDataSet 
GetCommandText 
GetConnName (shared/static)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. See the following sections for details on configuration file settings.
GetConnString (shared/static)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. Allows the developer to specify all application connection strings by environment, rather than having to keep changing the connection string when deploying to production, then changing it back to continue development work.

Sample Implementation

Prep Work

  • Set a reference to System.Configuration in your project.
  • Include the Parser Class in your project.

Configuration File Settings

The following is an excerpt from the web.config or app.config file. Note that machine names are expected to be entirely in uppercase.

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

Go here for sample connection strings.

Derived Class

Key Elements

  • Inherit from SqlDatabaseWeb
  • Define the private constant _dbName
  • Use the code snippets in the following sections for your various method calls.

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

ExecuteNonQuery

VB.NET

{copytext|ExecuteNonQueryVb}
        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

C#

{copytext|ExecuteNonQueryCs}
        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);
        }

FillTable

VB

{copytext|FillTableVb}
        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

C#

{copytext|FillTableCs}
        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);
        }

FillDataSet

VB.NET

{copytext|FillDataSetVb}
        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

C#

{copytext|FillDataSetCs}
        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);
        }

Source Code

VB.NET

{copytext|SourceVb}
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

C#

{copytext|SourceCs}
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.