View Thread
 Print Thread
Simple .net class to help Classic ASP developers move to ado.net (Crude)
admin
Usage:


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()


Class:

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("whatever@whatever.com", "Data Error", "An Error Occured in " & m_InvokingWebPage & "<br /><br />The Error was:<br />" & MessageStr & ".")
    End Sub

End Class

Edited by admin on 01-03-2013 16:37
Jordon Pilling | Heavencore Administrator
 
Jump to Forum