AIOX_DBHelper Class (WIP…Very Alpha/Beta phase) VB.Net

So, coming to work on both my GIGeeks Database program and my Security Solutions Database, I found that I needed something a bit more modular than I normally make. Normally, my projects are pretty defined in what they’re going to do, systems they’re going to run on, and overall how they need to work. While dealing with both a Military and Private Security Database project, I realized that the end user’s computer is going to be a variable as well as the server that it’s going to run on. The military typically runs, from what I’ve seen, all Windows with SQLServer as the database system, while Private Companies normally range from Linux, Windows, Mac, or some other flavor of choice. So I’ve decided to start creating a Helper class that’ll allow me to code once, and choose the best way to deal with the data based on settings used during install. Currently the idea takes up SQLServer, Access, and SQLite, with the ultimate goal to bring in a plugin archtiecture so that further additions can be made while exposing only the functions that need to be used. Here’s what I have so far, and be mindful, it’s very primitive and I’ve done NOTHING in terms of making it secure:

Imports System
Imports System.Text
Imports System.Security.Cryptography
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SQLite
Imports System.Data.OleDb
Imports System.Dynamic

Module AIOX_DBHelper

    Class AIOX_DB
        Public dbtype As String = ""
        Public dbconnectionstr As String = ""
        Public dbAccessCon As New OleDbConnection()
        Public dbMSSQLCon As New SqlConnection
        Public dbSQLIteCon As New SQLiteConnection
        Public dbcommands As String = ""

        Public Sub New(Optional ByVal dbtype As String = vbNullString, Optional ByVal sqlfile As String = vbNullString, Optional ByVal createnew As Boolean = False)
            Select Case dbtype
                Case "Access"
                    Me.dbtype = dbtype
                    Me.dbconnectionstr = String.Format("Provider=Microsoft.ACE.OLEDB.12.0;DataSource={0};", sqlfile)
                    Me.open()
                Case "SQLite"
                    Me.dbtype = dbtype
                    If createnew Then
                        If System.IO.File.Exists(sqlfile) Then
                            ' This is missing a "Are you Sure question!!!! IDIOT!
                            If Me.CreateDatabaseFile(sqlfile) Then
                                Me.dbconnectionstr = String.Format("Data Source={0};", sqlfile)
                            End If
                        Else
                            If Me.CreateDatabaseFile(sqlfile) Then
                                Me.dbconnectionstr = String.Format("Data Source={0};", sqlfile)
                            End If
                        End If
                    Else
                        If System.IO.File.Exists(sqlfile) Then
                            Me.dbconnectionstr = String.Format("Data Source={0};", sqlfile)
                        Else
                            If Me.CreateDatabaseFile(sqlfile) Then
                                Me.dbconnectionstr = String.Format("Data Source={0};", sqlfile)
                            End If
                        End If
                        End If
                        Me.open()
                Case "SQLServer"
                        Dim dbname As String = ""
                        Dim password As String = ""
                        Dim userid As String = ""
                        Me.dbtype = dbtype
                        Me.dbconnectionstr = String.Format("Server={0};Database={1};Integrated Security=SSPI", sqlfile, dbname, userid, password) 'User Id={2};Password={3};
                        Me.open()
                Case Else
                        Throw New ArgumentException("No DBType Declared | AIOXperts Error")
            End Select
        End Sub
        Public Function destroy()
            Select Case Me.dbtype
                Case "Access"
                    If dbAccessCon.State <> ConnectionState.Closed Then dbAccessCon.Close()
                Case "SQLite"
                    If dbSQLIteCon.State <> ConnectionState.Closed Then dbSQLIteCon.Close()
                Case "SQLServer"
                    If dbMSSQLCon.State <> ConnectionState.Closed Then dbMSSQLCon.Close()
            End Select
        End Function
        Public Sub open()
            Try
                Select Case Me.dbtype
                    Case "Access"
                        Me.dbAccessCon = New OleDbConnection(dbconnectionstr)
                        dbAccessCon.Open()
                    Case "SQLite"
                        Me.dbSQLIteCon = New SQLiteConnection(dbconnectionstr)
                        dbSQLIteCon.Open()

                    Case "SQLServer"
                        If dbMSSQLCon.State <> ConnectionState.Closed Then dbMSSQLCon.Close()
                        Me.dbMSSQLCon = New SqlClient.SqlConnection(dbconnectionstr)
                        dbMSSQLCon.Open()
                End Select
            Catch ex As Exception
                MsgBox(ex.Message)
            End Try
        End Sub

        Public Function GetDataTable(ByVal sql As String) As DataTable
            Dim dt As New DataTable
            Try
                Select Case Me.dbtype
                    Case "Access"

                    Case "SQLite"
                        If dbSQLIteCon.State <> ConnectionState.Open Then Me.open()
                        Dim cmd As New SQLiteCommand(dbSQLIteCon)
                        cmd.CommandText = sql
                        Dim reader As SQLiteDataReader = cmd.ExecuteReader()
                        dt.Load(reader)
                        reader.Close()
                        Me.destroy()
                    Case "SQLServer"
                        If dbMSSQLCon.State <> ConnectionState.Open Then Me.open()
                        Dim cmd As New SqlCommand(dbMSSQLCon.ToString)
                        cmd.CommandText = sql
                        Dim reader As SqlDataReader = cmd.ExecuteReader()
                        dt.Load(reader)
                        reader.Close()
                        Me.destroy()
                End Select
            Catch ex As Exception
                MsgBox("failed getdatatable")
            End Try
            Return dt
        End Function
        Public Function executenonquery(ByVal sql As String) As Integer
            Try
                Select Case Me.dbtype
                    Case "Access"

                    Case "SQLite"
                        If dbSQLIteCon.State <> ConnectionState.Open Then Me.open()
                        Dim cmd As New SQLiteCommand(dbSQLIteCon)
                        cmd.CommandText = sql
                        Dim rowsupdated As Integer = cmd.ExecuteNonQuery()
                        Me.destroy()
                        Return rowsupdated
                    Case "SQLServer"
                        If dbMSSQLCon.State <> ConnectionState.Open Then Me.open()
                        Dim cmd As New SqlCommand(dbMSSQLCon.ToString)
                        cmd.CommandText = sql
                        Dim rowsupdated As Integer = cmd.ExecuteNonQuery()
                        Me.destroy()
                        Return rowsupdated
                End Select
            Catch ex As Exception
                MsgBox("failed getdatatable")
            End Try
        End Function
        Public Function testconnection() As Boolean
            Try
                Select Case Me.dbtype
                    Case "Access"

                    Case "SQLite"

                    Case "SQLServer"

                End Select
            Catch ex As Exception

            End Try

        End Function

        Public Function ExecuteReader(ByVal strSQL As String) As Object
            Try
                Select Case Me.dbtype
                    Case "Access"

                    Case "SQLite"
                        If dbSQLIteCon.State <> ConnectionState.Open Then Me.open()
                        Dim cmd As New SQLiteCommand(strSQL, dbSQLIteCon)
                        cmd.CommandType = CommandType.Text
                        Dim retval As Object = cmd.ExecuteReader()
                        'Me.destroy()
                        Return retval
                    Case "SQLServer"

                End Select
            Catch ex As Exception
                MsgBox("failed executereader" + vbNewLine + ex.Message)
            End Try
        End Function

        Public Function ExecuteScalar(ByVal strSQL As String) As Object
            Try
                Select Case Me.dbtype
                    Case "Access"

                    Case "SQLite"
                        If dbSQLIteCon.State <> ConnectionState.Open Then Me.open()
                        Dim cmd As New SQLiteCommand(strSQL, dbSQLIteCon)
                        cmd.CommandType = CommandType.Text
                        Dim retval As Object = cmd.ExecuteScalar()
                        Me.destroy()
                        Return retval
                    Case "SQLServer"
                        If dbMSSQLCon.State <> ConnectionState.Open Then Me.open()
                        Dim cmd As New SqlCommand(strSQL, dbMSSQLCon)
                        cmd.CommandType = CommandType.Text
                        Dim retval As Object = cmd.ExecuteScalar()
                        Me.destroy()
                        Return retval
                End Select
            Catch ex As Exception
                MsgBox("failed executescalar" + vbNewLine + ex.Message)
            End Try
        End Function
        Public Function InsertSQL(ByVal table As String, ByVal params As String(,)) As Integer
            Try
                Select Case Me.dbtype
                    Case "Access"

                    Case "SQLite"
                        If dbSQLIteCon.State <> ConnectionState.Open Then Me.open()
                        Dim bound0 As Integer = params.GetUpperBound(0)
                        Dim bound1 As Integer = params.GetUpperBound(1)
                        Dim values, data As String
                        For x As Integer = 0 To bound0
                            Dim param As String = params(x, 1)
                            If x <> bound0 Then
                                data = String.Format("{0}'{1}', ", data, param)
                            Else : data = String.Format("{0}'{1}'", data, param)
                            End If
                            'cmd.Parameters.Add(params(i))
                        Next
                        For i As Integer = 0 To bound0
                            Dim param As String = params(i, 0)
                            If i <> bound0 Then
                                values = String.Format("{0}{1}, ", values, param)
                            Else : values = values + param
                            End If
                        Next
                        Dim retval As Double = Me.ExecuteScalar(String.Format("insert into {0} ({1}) VALUES ({2}); SELECT last_insert_rowid();", table, values, data))
                        Me.destroy()
                        Return retval
                    Case "SQLServer"
                        If dbMSSQLCon.State <> ConnectionState.Open Then Me.open()
                        Dim bound0 As Integer = params.GetUpperBound(0)
                        Dim bound1 As Integer = params.GetUpperBound(1)
                        Dim values, data As String
                        For x As Integer = 0 To bound0
                            Dim param As String = params(x, 1)
                            If x <> bound0 Then
                                data = String.Format("{0}'{1}', ", data, param)
                            Else : data = String.Format("{0}'{1}'", data, param)
                            End If
                        Next
                        For i As Integer = 0 To bound0
                            Dim param As String = params(i, 0)
                            If i <> bound0 Then
                                values = String.Format("{0}{1}, ", values, param)
                            Else : values = values + param
                            End If
                        Next
                        Dim retval As Guid = Me.ExecuteScalar(String.Format("insert OUTPUT INSERTED into {0} ({1}) VALUES ({3});", table, values, data))
                        Me.destroy()
                        Return retval.ToString
                End Select
            Catch ex As Exception
                MsgBox("failed executescalar")
            End Try
        End Function

        Public Function InsertSQLMulti(ByVal table As String, ByVal params As List(Of Dictionary(Of String, String))) As Boolean
            Try
                Select Case Me.dbtype
                    Case "Access"

                    Case "SQLite"
                        If dbSQLIteCon.State <> ConnectionState.Open Then Me.open()
                        Dim sql, keys, dats As String
                        For Each value As Dictionary(Of String, String) In params
                            For key = 0 To value.Keys.Count - 1
                                If key = value.Keys.Count - 1 Then
                                    keys = String.Format("{0}'{1}'", keys, value.Keys.ElementAt(key))
                                Else : keys = String.Format("{0}'{1}', ", keys, value.Keys.ElementAt(key))
                                End If
                            Next
                            For dat = 0 To value.Values.Count - 1
                                If dat = value.Values.Count - 1 Then
                                    dats = String.Format("{0}'{1}'", dats, (value.Values.ElementAt(dat)))
                                Else : dats = String.Format("{0}'{1}', ", dats, (value.Values.ElementAt(dat)))
                                End If
                            Next
                            sql = String.Format("{0}Insert into {1} ({2}) VALUES ({3}); ", sql, table, keys, dats)
                            keys = ""
                            dats = ""
                        Next
                        Me.ExecuteScalar(sql)
                        Me.destroy()
                        Return True
                    Case "SQLServer"
                        If dbMSSQLCon.State <> ConnectionState.Open Then Me.open()
                        Dim sql, keys, dats As String
                        For Each value As Dictionary(Of String, String) In params
                            For key = 0 To value.Keys.Count - 1
                                If key = value.Keys.Count - 1 Then
                                    keys = String.Format("{0}'{1}'", keys, value.Keys.ElementAt(key))
                                Else : keys = String.Format("{0}'{1}', ", keys, value.Keys.ElementAt(key))
                                End If
                            Next
                            For dat = 0 To value.Values.Count - 1
                                If dat = value.Values.Count - 1 Then
                                    dats = String.Format("{0}'{1}'", dats, (value.Values.ElementAt(dat)))
                                Else : dats = String.Format("{0}'{1}', ", dats, (value.Values.ElementAt(dat)))
                                End If
                            Next
                            sql = String.Format("{0}Insert into {1} ({2}) VALUES ({3}); ", sql, table, keys, dats)
                            keys = ""
                            dats = ""
                        Next
                        Me.ExecuteScalar(sql)
                        Me.destroy()
                        Return True
                End Select
            Catch ex As Exception
                MsgBox("failed InsertSQLMulti")
                Return False
            End Try
        End Function
        Public Function UpdateSQL(ByVal table As String, ByVal params As List(Of Dictionary(Of String, String)), ByVal whereat As List(Of Dictionary(Of String, String)))
            Try
                Select Case Me.dbtype
                    Case "Access"

                    Case "SQLite"
                        If dbSQLIteCon.State <> ConnectionState.Open Then Me.open()
                        Dim sql, keys, wheres As String
                        For Each where As Dictionary(Of String, String) In whereat
                            wheres = String.Format("'{0}'='{1}'", where.Keys.ElementAt(0), where.Values.ElementAt(0))
                        Next
                        For Each value As Dictionary(Of String, String) In params
                            For key = 0 To value.Keys.Count - 1
                                If key = value.Keys.Count - 1 Then
                                    keys = String.Format("{0}'{1}'='{2}'", keys, value.Keys.ElementAt(key), value.Values.ElementAt(key))
                                Else : keys = String.Format("{0}'{1}'='{2}', ", keys, value.Keys.ElementAt(key), value.Values.ElementAt(key))
                                End If
                            Next

                            sql = String.Format("{0}Update {1} SET {2} WHERE {3}; ", sql, table, keys, wheres)
                            keys = ""
                        Next
                        Me.ExecuteScalar(sql)
                        Me.destroy()
                        Return True
                    Case "SQLServer"
                        If dbMSSQLCon.State <> ConnectionState.Open Then Me.open()
                        Dim sql, keys, wheres As String
                        For Each where As Dictionary(Of String, String) In whereat
                            wheres = String.Format("'{0}'='{1}'", where.Keys.ElementAt(0), where.Values.ElementAt(0))
                        Next
                        For Each value As Dictionary(Of String, String) In params
                            For key = 0 To value.Keys.Count - 1
                                If key = value.Keys.Count - 1 Then
                                    keys = String.Format("{0}'{1}'='{2}'", keys, value.Keys.ElementAt(key), value.Values.ElementAt(key))
                                Else : keys = String.Format("{0}'{1}'='{2}', ", keys, value.Keys.ElementAt(key), value.Values.ElementAt(key))
                                End If
                            Next

                            sql = String.Format("{0}Update {1} SET {2} WHERE {3}; ", sql, table, keys, wheres)
                            keys = ""
                        Next
                        Me.ExecuteScalar(sql)
                        Me.destroy()
                        Return True
                End Select
            Catch ex As Exception
                MsgBox("failed UpdateSQL")
                Return False
            End Try
        End Function

        Public Function CreateDatabaseFile(ByVal file As String) As Boolean
            Try
                Select Case Me.dbtype
                    Case "Access"
                        Return True
                    Case "SQLite"
                        SQLiteConnection.CreateFile(file)
                        Return True
                    Case "SQLServer"
                        Return True
                End Select
            Catch ex As Exception
                MsgBox("Failed CreateDatabaseFile")
                Return False
            End Try
        End Function
    End Class
End Module

 

 

Leave a comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.