Public db As New SimpleDataAccess

Public RS As New DataTable



db.ConnectionStringName = "DEV"

db.ConnectDatabase()

db.Execute_Query(RS, "SELECT * FROM whatever WHERE IntColumn = " & tools.parseint(EmployeeID, 0) & " or TextColumn = '" & db.Escape("bla'blabla") & "'")

For Each DB_Row As DataRow In RS.Rows

   response.write(DB_Row("IntColumn"))

Next

db.CloseDatabase()

Imports Microsoft.VisualBasic

Imports System.Data

Imports System.Data.SqlClient

Imports System.Net.Mail



Public Class SimpleDataAccess

    Private m_db As New SqlConnection

    Private m_db_Command As New SqlCommand

    Private m_db_DataAdapter As New SqlDataAdapter

    Private m_db_ConnectionStringName As String

    Private m_db_ConnectionServerName As String

    Private m_db_ConnectionDatabaseName As String

    Private m_db_ConnectionUserName As String

    Private m_db_ConnectionPassword As String

    Private m_ErrorDesc As String

    Private m_EmailSender As New SendEmail

    Private m_InvokingWebPage As String

    Private m_SQLstr As String



    Property ConnectionStringName() As String

        Get

            Return m_db_ConnectionStringName

        End Get

        Set(ByVal Value As String)

            m_db_ConnectionStringName = Value

        End Set

    End Property

    Property SqlQueryToRun() As String

        Get

            Return m_SQLstr

        End Get

        Set(ByVal value As String)

            m_SQLstr = value

        End Set

    End Property

    Property InvokingWebPage() As String

        Get

            Return m_InvokingWebPage

        End Get

        Set(ByVal value As String)

            m_InvokingWebPage = value

        End Set

    End Property



    ReadOnly Property GetError() As String

        Get

            Return m_ErrorDesc

        End Get

    End Property



    ReadOnly Property ConnectionStatus() As System.Data.ConnectionState

        Get

            Return m_db.State

        End Get

    End Property

    Public Sub SetConnectionDetails(ByVal ServerName As String, ByVal DatabaseName As String, ByVal UserName As String, ByVal Password As String)

        m_db_ConnectionStringName = ""

        m_db_ConnectionServerName = ServerName

        m_db_ConnectionDatabaseName = DatabaseName

        m_db_ConnectionUserName = UserName

        m_db_ConnectionPassword = Password

    End Sub

    Public Sub CloseDatabase()

        '#### Closes the DB connection in the application pool if its open

        Try

            If m_db.State = ConnectionState.Open Then

                m_db.Close()

            End If

        Catch ex As Exception



        End Try

    End Sub

    Public Function Escape(ByVal StrValue As String) As String

        Return Replace(StrValue, "'", "''")

    End Function

    Public Function PrepDate(ByVal StrDate As String, Optional ByVal DateType As String = "DATE") As String

        If Trim(StrDate) = "" Then

            Return "NULL"

        Else

            Return "CONVERT(" & DateType & ", '" & Escape(Trim(StrDate)) & "', 103)"

        End If

    End Function

    Public Function ConnectDatabase() As Boolean

        '#### Check the connection is not already open

        If m_db.State = ConnectionState.Closed Then

            '#### Do we have a valid connection string?

            If m_db_ConnectionStringName = "" Then

                '#### The connection string name has not been set, have bespoke paramaters been provided?

                If m_db_ConnectionServerName = "" Or m_db_ConnectionDatabaseName = "" Or m_db_ConnectionUserName = "" Then

                    m_ErrorDesc = "ERROR: When leaving ConnectionStringName() blank, you must set ConnectionDetails() instead."

                    Return False

                Else

                    m_db.ConnectionString = "Data Source=" & m_db_ConnectionServerName & ";Initial Catalog=" & m_db_ConnectionDatabaseName & ";User Id=" & m_db_ConnectionUserName & ";Password=" & m_db_ConnectionPassword & ";"

                End If

            Else

                '#### Check the connection string exists

                Try

                    m_db.ConnectionString = ConfigurationManager.ConnectionStrings(m_db_ConnectionStringName).ConnectionString

                Catch ex As Exception

                    m_ErrorDesc = "Error setting connection string from ConfigurationManager.ConnectionStrings(" & m_db_ConnectionStringName & ").ConnectionString: " & ex.Message

                    EmailError(m_ErrorDesc)

                    Return False

                End Try

            End If

            '#### Its closed, attempt to open

            Try

                m_db.Open()

                Return True

            Catch ex As Exception

                m_ErrorDesc = "Error connecting database:<br />" & ex.Message & "<br /><br />Connection String:<br />" & m_db.ConnectionString

                EmailError(m_ErrorDesc)

                Return False

            End Try

        Else

            '#### state is not closed, check its open

            If m_db.State = ConnectionState.Open Then

                Return True

            Else

                '#### its not closed, and not open suggesting some bad mid way status, alert and fail:

                m_ErrorDesc = "Error connecting database: The Connection state was neither Open nor Closed, This confused the ConnectDatabase() method. State was '" & m_db.State.ToString '"

                EmailError(m_ErrorDesc)

                Return False

            End If

        End If

    End Function



    Public Function Execute_NonQuery(Optional ByVal StrQuery As String = "", Optional ByRef RowsAffected As Integer = 0) As Boolean

        If StrQuery <> "" Then

            m_SQLstr = StrQuery

        End If

        If m_SQLstr = "" Then

            m_ErrorDesc = "Error running query: Query text was blank"

            EmailError(m_ErrorDesc)

            Return False

        Else

            Try

                m_db_Command.Connection = m_db

                m_db_Command.CommandText = m_SQLstr

                RowsAffected = m_db_Command.ExecuteNonQuery()

                Return True

            Catch ex As Exception

                m_ErrorDesc = "Error running query: " & ex.Message & "<br /><br />Query:<hr />" & m_SQLstr

                EmailError(m_ErrorDesc)

                Return False

            End Try

        End If



    End Function



    Public Function Execute_Query(ByRef TargetDataTable As DataTable, Optional ByVal StrQuery As String = "") As Boolean

        If StrQuery <> "" Then

            m_SQLstr = StrQuery

        End If

        If m_SQLstr = "" Then

            m_ErrorDesc = "Error running query: Query text was blank"

            EmailError(m_ErrorDesc)

            Return False

        Else

            Try

                NukeDataTable(TargetDataTable)

                m_db_Command.CommandText = m_SQLstr

                m_db_Command.Connection = m_db

                m_db_DataAdapter.SelectCommand = m_db_Command

                m_db_DataAdapter.Fill(TargetDataTable)

                m_db_DataAdapter.Dispose()

                Return True

            Catch ex As Exception

                m_ErrorDesc = "Error running query: " & ex.Message & "<br /><br />Query:<hr />" & m_SQLstr

                EmailError(m_ErrorDesc)

                Return False

            End Try

        End If

    End Function



    Public Function GetDataTable(ByVal StrQuery As String) As DataTable

        Dim RS_ToReturn As New DataTable

        Execute_Query(RS_ToReturn, StrQuery)

        Return RS_ToReturn

    End Function



    Public Sub NukeDataTable(ByRef DataTable As DataTable)

        Try

            DataTable.Rows.Clear()

            DataTable.Columns.Clear()

            DataTable.Clear()

            DataTable.Reset()

        Catch ex As Exception



        End Try

    End Sub



    Private Sub EmailError(ByVal MessageStr As String)

        m_EmailSender.AddRecipient("")

        m_EmailSender.SendEmailMessage("[email protected]", "Data Error", "An Error Occured in " & m_InvokingWebPage & "<br /><br />The Error was:<br />" & MessageStr & ".")

    End Sub



End Class