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
