Implementation of database access using transactions

Hi All,

I have written the following code to read/write/update/delete from a database using transactions.

I'd like feedback on how it's implemented and if there is anything that can be done to improve the code or make it more efficient.

Any and all feedback is welcome.

Note:  This code does handle grouped arguments, ie SELECT * FROM MyTABLE WHERE ID='1' AND (Name='MyName' OR Name='YourName')
by adding the '(' to the arguments

I used arraylists as argument containers because it allowed for 0 to an unlimited number of filters, rather than limiting to the number of programmed optional arguments.

It's a little confusing at first, but here's an example of the usage:

dim c,v,a,read as new arraylist
dim db as new TransDB("my_connection_string")

c.add("ID")
c.add("(Name") ' <- adding '(' here will start a group
c.add("Name")

v.add(1)
v.add("MyName")
v.add("YourName)") ' <- adding ')' will end the group

a.add("AND")
a.add("OR")

read = db.readfromdb("MyTable","*",c,v,a)

will produce:

SELECT * FROM 'MyTABLE' WHERE ID='1' AND (Name='MyName' OR Name='YourName')

The only downside, that i can see, to this approach is that data with '(' or ')' will produce errors.
ie if you try to store 'Daniel (aka BigDan)' in Name, it will error out because the code searches for ')' in a certain position and Replace()'s the ')', placing it in the proper position to get the correct syntax.

I've gotten around that by replacing '(' with '{' and ')' with '}' before writing/updating, and doing the opposite when reading.



************* Begin TransDB.vb ********************
Imports System.Data.SqlClient
' ****************************************************************************************************************
' * A transactional database interface
' *
' ** Methods:
' ** ReadFromDB: Returns an ArrayList containing the read results from the specified table based on columns and
'    filters
' ** GetColumnCountFromTable: Returns an integer with the number of columns in the specified table
' ** GetColumnNamesFromTable: Returns an arraylist containing the names of the columns in the specified table
' ** WriteToDB: Constructs a SQL command string to write to the specified table.  Returns a boolean value
'    indicating the validity of the SQL Command.  Adds the command to the queue.
' ** DeleteFromDB: Constructs a SQL command string to delete from the specified table.  Returns a boolean value
'    indicating the validity of the SQL Command.  Adds the command to the queue.
' ** UpdateDB: Constructs a SQL command string to update the specified table.  Returns a boolean value indicating
'    the validity of the SQL Command.  Adds the command to the queue.
' ** ProcessTransactionQueue: Processes all the SQL commands in the queue in a loop, commiting if they are all
'    successful, rolling back if one fails.  Returns a boolean value indicating success or failure.
' *
' ** CloseDB: closes the db connection
' ** OpenDB: opens the db connection
' ** Wait: waits the specified number of milliseconds before returning
' *
' ** FlushQueue: Emptys the transaction queue of all commands
' ** AddToQueue:  Adds a SQL command to the queue
' ** CheckState: Checks the connection string of the db, sets it if it is "", checks the state of the db,
'    opens it if closed
' *
' ** GetState: Returns the state of the connection (Open, Closed, Etc).
' ** ConnectionString: Gets or sets the db connection string.
' ** GetErrors: returns
' ****************************************************************************************************************
Public Class TransDB

#Region "Variables"
    Private mydb As New SqlConnection()
    Private con_str As String = ""
    Private reader As SqlDataReader
    Private queue As ArrayList
    Public Error_Log as Errors
#End Region


    Public Sub New(ByVal ConnectionString As String)
        con_str = ConnectionString
        queue = New ArrayList
        mydb.ConnectionString = con_str
        Error_Log = New Errors
    End Sub

#Region "Functions"
#Region "Public"

    Public Function WriteToDB(ByVal TABLE_NAME As String, ByVal COL_NAMES As ArrayList, ByVal COL_VALS As ArrayList) As Boolean
        If COL_NAMES.Count <> COL_VALS.Count Then
            Error_Log.AddErrorMessage("TRANSDB: WriteToDB FAIL.  COL_NAMES.COUNT <> COL_VALS.COUNT")
            Return False
            Exit Function
        End If
        Dim cmd_str As String = ""

        cmd_str = "INSERT INTO " & TABLE_NAME & " ("
        For c As Integer = 0 To COL_NAMES.Count - 1
            cmd_str = cmd_str & COL_NAMES.Item(c).ToString & ","
        Next
        cmd_str = Mid(cmd_str, 1, cmd_str.Length - 1) & ") VALUES('"
        For v As Integer = 0 To COL_VALS.Count - 1
            cmd_str = cmd_str & Replace(COL_VALS.Item(v).ToString, "'", "''") & "', '"
        Next
        cmd_str = Mid(cmd_str, 1, cmd_str.Length - 3) & ")"

        AddToQueue(cmd_str)
        Return True
    End Function

    Public Function UpdateDB(ByVal TABLE_NAME As String, ByVal COL_NAMES As ArrayList, ByVal COL_VALS As ArrayList, Optional ByVal FILTER_COLS As ArrayList = Nothing, Optional ByVal FILTER_VALS As ArrayList = Nothing, Optional ByVal FILTER_ARGS As ArrayList = Nothing) As Boolean
        If COL_NAMES.Count <> COL_VALS.Count Then
            Error_Log.AddErrorMessage("TRANSDB: UpdateDB FAIL.  COL_NAMES.COUNT <> COL_VALS.COUNT")
            Return False
            Exit Function
        End If
        If Not FILTER_COLS Is Nothing Then
            If FILTER_COLS.Count <> FILTER_VALS.Count And FILTER_ARGS.Count <> FILTER_COLS.Count - 1 Then
                Error_Log.AddErrorMessage("TRANSDB: UpdateDB FAIL.  FILTER_COLS.Count <> FILTER_VALS.Count or FILTER_ARGS.Count <> FILTER_COLS.Count - 1 ")
                Return False
                Exit Function
            End If
        End If
        Dim cmd_str As String = ""
        cmd_str = "UPDATE " & TABLE_NAME & " SET "
        For i As Integer = 0 To COL_NAMES.Count - 1
            cmd_str = cmd_str & COL_NAMES.Item(i).ToString & "='" & Replace(COL_VALS.Item(i).ToString, "'", "''") & "', "
        Next
        ' strip off the last ', '
        cmd_str = Mid(cmd_str, 1, cmd_str.Length - 2)
        If Not FILTER_COLS Is Nothing Then
            cmd_str = cmd_str & " WHERE "
            For i As Integer = 0 To FILTER_COLS.Count - 1
                cmd_str = cmd_str & FILTER_COLS.Item(i) & "='" & Replace(FILTER_VALS.Item(i).ToString, "'", "''") & "'"
                If cmd_str.Contains(")'") Then
                    cmd_str = Replace(cmd_str, ")'", "'") & ")"
                End If
                If i >= 0 And i < FILTER_COLS.Count - 1 Then
                    cmd_str = cmd_str & " " & FILTER_ARGS.Item(i) & " "
                End If
            Next
        End If
        AddToQueue(cmd_str)
        Return True
    End Function

    Public Function DeleteFromDB(ByVal TABLE_NAME As String, ByVal FILTER_COLS As ArrayList, ByVal FILTER_VALS As ArrayList, ByVal FILTER_ARGS As ArrayList, Optional ByVal SILENT As Boolean = False) As Boolean
        Dim answer As Boolean = False
        If Not SILENT Then
            If MsgBox("Are you sure you want to delete from the database?" & vbLf & "All deletes are permanent!", MsgBoxStyle.YesNo, "Confirm delete") = MsgBoxResult.No Then
                MsgBox("No data was deleted.", MsgBoxStyle.OkOnly, "Alert")
                Return True
                Exit Function
            End If
        End If
        If FILTER_COLS.Count <> FILTER_VALS.Count And FILTER_ARGS.Count <> FILTER_COLS.Count - 1 Then
            Error_Log.AddErrorMessage("TRANSDB: UpdateDB FAIL.  FILTER_COLS.Count <> FILTER_VALS.Count or FILTER_ARGS.Count <> FILTER_COLS.Count - 1 ")
            Return False
            Exit Function
        End If

        Dim cmd_str As String = ""
        cmd_str = "DELETE FROM " & TABLE_NAME & " WHERE "
        For i As Integer = 0 To FILTER_COLS.Count - 1
            cmd_str = cmd_str & FILTER_COLS.Item(i) & "='" & Replace(FILTER_VALS.Item(i).ToString, "'", "''") & "'"
            If cmd_str.Contains(")'") Then
                cmd_str = Replace(cmd_str, ")'", "'") & ")"
            End If
            If i >= 0 And i < FILTER_COLS.Count - 1 Then
                cmd_str = cmd_str & " " & FILTER_ARGS.Item(i) & " "
            End If
        Next

        AddToQueue(cmd_str)
        Return True
    End Function

    Public Function ReadFromDB(ByVal TABLE_NAME As String, ByVal COL_NAME As String, Optional ByVal FILTER_COLS As ArrayList = Nothing, Optional ByVal FILTER_VALS As ArrayList = Nothing, Optional ByVal FILTER_ARGS As ArrayList = Nothing, Optional ByVal ORDER_BY As String = "", Optional ByVal SORT As Boolean = False, Optional ByVal SORT_ORDER As String = "ASC") As ArrayList
        If Not FILTER_COLS Is Nothing Then
            If FILTER_COLS.Count <> FILTER_VALS.Count And FILTER_ARGS.Count <> FILTER_COLS.Count - 1 Then
                Error_Log.AddErrorMessage("TRANSDB: ReadFromDB FAIL.  FILTER_COLS.Count <> FILTER_VALS.Count or FILTER_ARGS.Count <> FILTER_COLS.Count - 1 ")
                Return New ArrayList
                Exit Function
            End If
        End If
        CheckState()
        Dim cmd_str As String = ""
        Dim temp_array As New ArrayList
        temp_array.Clear()

        cmd_str = "SELECT " & COL_NAME & " FROM " & TABLE_NAME
        If Not FILTER_COLS Is Nothing Then
            If FILTER_COLS.Count > 0 Then
                cmd_str = cmd_str & " WHERE "
                For i As Integer = 0 To FILTER_COLS.Count - 1
                    cmd_str = cmd_str & FILTER_COLS.Item(i) & "='" & Replace(FILTER_VALS.Item(i).ToString, "'", "''") & "'"
                    If cmd_str.Contains(")'") Then
                        cmd_str = Replace(cmd_str, ")'", "'") & ")"
                    End If
                    If i >= 0 And i < FILTER_COLS.Count - 1 Then
                        cmd_str = cmd_str & " " & FILTER_ARGS.Item(i) & " "
                    End If

                Next
            End If

        End If
        If ORDER_BY <> "" Then
            cmd_str = cmd_str & " ORDER BY " & ORDER_BY
        End If
        If SORT Then
            cmd_str = cmd_str & " " & SORT_ORDER
        End If
        Dim cmd As New SqlCommand(cmd_str, mydb)
        Try

            temp_array.Clear()

            reader = cmd.ExecuteReader
            While reader.Read
                For i As Integer = 0 To reader.FieldCount - 1
                    temp_array.Add(reader.Item(i).ToString)
                Next
            End While
        Catch ex As Exception
            temp_array = New ArrayList
            Error_Log.AddErrorMessage("TRANSDB: Failed reading from database: " & vbLf & vbLf & ex.ToString)
        End Try

        Try
            reader.Close()
        Catch ex As Exception
            Error_Log.AddErrorMessage("TRANSDB: Failed closing database reader: " & vbLf & vbLf & ex.ToString)
        End Try

        Return temp_array
    End Function

    Public Function GetColumnCountFromTable(ByVal TABLE_NAME As String) As Integer
        CheckState()
        Dim str As String = ""
        Dim count As Integer = 0
        str = "SELECT column_name FROM information_schema.columns as c, information_schema.tables as t WHERE c.table_name = t.table_name AND c.table_name = '" & TABLE_NAME & "'"
        Dim cmd As New SqlCommand(str, mydb)
        Try
            reader = cmd.ExecuteReader

            While reader.Read
                count = count + 1
            End While
            reader.Close()
        Catch ex As Exception
            Error_Log.AddErrorMessage("TRANSDB: Failed getting column count: " & vbLf & vbLf & ex.ToString)
            count = 0
        End Try
        Return count
    End Function

    Public Function GetColumnNamesFromTable(ByVal TABLE_NAME As String) As ArrayList
        CheckState()
        Dim str As String = ""
        Dim temp_array As New ArrayList
        temp_array.Clear()

        str = "SELECT column_name FROM information_schema.columns as c, information_schema.tables as t WHERE c.table_name = t.table_name AND c.table_name = '" & TABLE_NAME & "'"
        Dim cmd As New SqlCommand(str, mydb)
        Try

            reader = cmd.ExecuteReader
            While reader.Read
                For i As Integer = 0 To reader.FieldCount - 1
                    temp_array.Add(reader.Item(i).ToString)
                Next
            End While
            reader.Close()
        Catch ex As Exception
            Error_Log.AddErrorMessage("TRANSDB: Failed getting column names: " & vbLf & vbLf & ex.ToString)
            temp_array = New ArrayList
        End Try
        Return temp_array
    End Function


    Public Function ProcessTransactionQueue() As Boolean
        CheckState()
        Using mydb
            Dim sqlTran As SqlTransaction = mydb.BeginTransaction()
            Dim command As SqlCommand = mydb.CreateCommand()
            command.Transaction = sqlTran
            Try
                For i As Integer = 0 To queue.Count - 1
                    command.CommandText = queue.Item(i).ToString
                    command.ExecuteNonQuery()
                Next
                sqlTran.Commit()
                FlushQueue()
            Catch ex As Exception
                Error_Log.AddErrorMessage("TRANSDB: Failed processing command queue: " & vbLf & vbLf & ex.ToString)
                For i As Integer = 0 To queue.Count - 1
                    Error_Log.AddErrorMessage("QUEUE #" & i & " : " & queue.Item(i).ToString)
                Next
                sqlTran.Rollback()
                FlushQueue()
                Return False
            End Try
        End Using
        Return True
    End Function

    Public Function CloseDB() As Boolean
        ' close the database connection
        Try
            If Not mydb.State = Data.ConnectionState.Closed Then mydb.Close()
            If Not reader.IsClosed Then reader.Close()
            reader = Nothing
            mydb.Dispose()
            mydb = Nothing
            Return True
        Catch ex As Exception
            MsgBox(ex.ToString)
            Return False
        End Try
    End Function

    Public Function CreateCMDString(ByVal TABLE_NAME As String, ByVal COL_NAME As String, Optional ByVal FILTER_COLS As ArrayList = Nothing, Optional ByVal FILTER_VALS As ArrayList = Nothing, Optional ByVal FILTER_ARGS As ArrayList = Nothing, Optional ByVal ORDER_BY As String = "", Optional ByVal SORT As Boolean = False, Optional ByVal SORT_ORDER As String = "ASC") As String
        If Not FILTER_COLS Is Nothing Then
            If FILTER_COLS.Count <> FILTER_VALS.Count And FILTER_ARGS.Count <> FILTER_COLS.Count - 1 Then
                Error_Log.AddErrorMessage("TRANSDB: ReadFromDB FAIL.  FILTER_COLS.Count <> FILTER_VALS.Count or FILTER_ARGS.Count <> FILTER_COLS.Count - 1 ")
                Return ""
                Exit Function
            End If
        End If
        CheckState()
        Dim cmd_str As String = ""
        Dim temp_array As New ArrayList
        temp_array.Clear()

        cmd_str = "SELECT " & COL_NAME & " FROM " & TABLE_NAME
        If Not FILTER_COLS Is Nothing Then
            If FILTER_COLS.Count > 0 Then
                cmd_str = cmd_str & " WHERE "
                For i As Integer = 0 To FILTER_COLS.Count - 1
                    cmd_str = cmd_str & FILTER_COLS.Item(i) & "='" & Replace(FILTER_VALS.Item(i).ToString, "'", "''") & "'"
                    If cmd_str.Contains(")'") Then
                        cmd_str = Replace(cmd_str, ")'", "'") & ")"
                    End If
                    If i >= 0 And i < FILTER_COLS.Count - 1 Then
                        cmd_str = cmd_str & " " & FILTER_ARGS.Item(i) & " "
                    End If

                Next
            End If

        End If
        If ORDER_BY <> "" Then
            cmd_str = cmd_str & " ORDER BY " & ORDER_BY
        End If
        If SORT Then
            cmd_str = cmd_str & " " & SORT_ORDER
        End If

        Return cmd_str
    End Function
#End Region

#Region "Private"
    Private Function OpenDB() As Boolean
        ' Opens the database connection
        Try
            Dim open As Boolean = False
            Dim count As Integer = 0
            Do
                count = count + 1
                Select Case mydb.State
                    Case Data.ConnectionState.Closed
                        mydb.Open()
                        open = True
                    Case Data.ConnectionState.Open
                        If count >= 3 Then
                            open = True
                        Else
                            Wait(Now, 10000)
                        End If
                    Case Data.ConnectionState.Broken
                        mydb.Close()
                        Wait(Now, 3000)
                        mydb.Open()
                    Case Data.ConnectionState.Connecting
                        Wait(Now, 5000)
                    Case Data.ConnectionState.Executing
                        Wait(Now, 5000)
                    Case Data.ConnectionState.Fetching
                        Wait(Now, 5000)
                End Select
            Loop While Not open
        Catch ex As Exception
            Error_Log.AddErrorMessage("TRANSDB: Error connecting to " & mydb.ConnectionString & vbLf & ex.ToString)
            Return False
            Exit Function
        End Try
        Return True
    End Function
 
    Private Function Wait(ByVal Start As Date, ByVal pause As Integer) As Boolean
        Do
        Loop While Now.Second < (Start.Second + pause / 1000)
        Return True
    End Function
#End Region
#End Region
#Region "Subs"

    Private Sub FlushQueue()
        queue.Clear()
    End Sub

    Private Sub AddToQueue(ByVal cmd As String)
        queue.Add(cmd)
    End Sub

    Private Sub CheckState()
        If mydb.ConnectionString = "" Then
            mydb.ConnectionString = con_str
        End If
        If mydb.State = ConnectionState.Closed Then
            OpenDB()
        End If
    End Sub
#End Region
#Region "Properties"
    Public ReadOnly Property GetState() As String
        Get
            GetState = mydb.State.ToString
        End Get
    End Property
    Public Property ConnectionString() As String
        Get
            ConnectionString = con_str
        End Get
        Set(ByVal value As String)
            con_str = value
        End Set
    End Property
Public ReadOnly Property GetErrors() As Errors
        Get
            GetErrors = Error_Log
        End Get
    End Property
#End Region
End Class

'************* End TransDB.vb ********************

'************* Begin Errors.vb ********************
Public Class Errors
    Private ErrorArray As ArrayList
   
    Public Sub New()
        ErrorArray = New ArrayList
    End Sub
   
    Public Sub AddErrorMessage(ByVal msg As String)
        ErrorArray.Add(msg)
        Debug.Print(msg)
    End Sub
   
    Public Sub ClearErrorLog()
        ErrorArray.Clear()
    End Sub
   
    Public Sub ShowErrors(Optional ByVal header As String = "")
        If ErrorArray.Count = 0 Then Exit Sub
        If header <> "" Then header = header & vbLf
        Dim s As String = header
        For i As Integer = 0 To ErrorArray.Count - 1
            s = s & " *****************" & ErrorArray.Item(i) & vbLf
        Next
        MsgBox(s)
    End Sub
End Class
'************* End Errors.vb ********************Start Free Trial