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