SHARE
TWEET

SQLiteDatabase Helper Class

Taximaniac Feb 26th, 2017 (edited) 115 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. 'This file is a SQLite Database helper Class
  2. 'This file was orginally written in C# by #1 brennydoogles and some functions by Mike Duncan
  3. 'I Taximaniac have ported it over to vb.net
  4. 'ENJOY
  5. 'orginal code at http://www.dreamincode.net/forums/topic/157830-using-sqlite-with-c%23/
  6.  
  7. Imports System
  8. Imports System.Collections.Generic
  9. Imports System.Data
  10. Imports System.Data.SQLite
  11. Imports System.Windows.Forms
  12.  
  13. Public Class SQLiteDatabase
  14.  
  15.     Private dbConnection As String
  16.  
  17.     ''' <summary>
  18.     '''  Default Constructor for SQLiteDatabase Class.
  19.     ''' </summary>
  20.     Public Sub New()
  21.         dbConnection = "Data Source=CCMemberStats.s3db"
  22.     End Sub
  23.  
  24.     ''' <summary>
  25.     ''' Single Param Constructor for specifying the DB File
  26.     ''' </summary>
  27.     ''' <param name="inputFile">The file containing the DB</param>
  28.     Public Sub New(ByVal inputFile As String)
  29.         dbConnection = String.Format("Data Source={0}", inputFile)
  30.     End Sub
  31.  
  32.     ''' <summary>
  33.     ''' Single Param Constructor for specifying advanced connection options
  34.     ''' </summary>
  35.     ''' <param name="connectionOpts">A dictionary containing all desired options and their values.</param>
  36.     Public Sub New(ByVal connectionOpts As Dictionary(Of String, String))
  37.         Dim str As String = ""
  38.  
  39.         For Each row As KeyValuePair(Of String, String) In connectionOpts
  40.             str += String.Format("{0}={1};", row.Key, row.Value)
  41.         Next
  42.  
  43.         str = str.Trim().Substring(0, str.Length - 1)
  44.         dbConnection = str
  45.     End Sub
  46.  
  47.     ''' <summary>
  48.     ''' Allows the programmer to run a query against the database.
  49.     ''' </summary>
  50.     ''' <param name="SQL">The SQL to run.</param>
  51.     ''' <returns>A datatable containing the result set.</returns>
  52.     Public Function GetDataTable(ByVal SQL As String) As DataTable
  53.         Dim dt As DataTable = New DataTable()
  54.  
  55.         Try
  56.             Using cnn As New SQLiteConnection(dbConnection)
  57.                 cnn.Open()
  58.                 Using mycommand As New SQLiteCommand(cnn)
  59.                     mycommand.CommandText = SQL
  60.                     Dim reader As SQLiteDataReader = mycommand.ExecuteReader()
  61.                     dt.Load(reader)
  62.                     reader.Close()
  63.                 End Using
  64.                 cnn.Close()
  65.             End Using
  66.  
  67.             Return dt
  68.         Catch ex As Exception
  69.             ShowDBError("GetDataTable", ex)
  70.             Return Nothing
  71.         End Try
  72.     End Function
  73.  
  74.     ''' <summary>
  75.     ''' Allows the programmer to interact with the database for purposes other than query.
  76.     ''' </summary>
  77.     ''' <param name="SQL">The sql to be run</param>
  78.     ''' <returns>An integer containing the number of rows affected. if error it returns -1</returns>
  79.     Public Function ExecuteNonQuery(ByVal SQL As String) As Integer
  80.         Dim rowsUpdated As Integer
  81.         Try
  82.             Using cnn As New SQLiteConnection(dbConnection)
  83.                 cnn.Open()
  84.                 Using mycommand As New SQLiteCommand(cnn)
  85.                     mycommand.CommandText = SQL
  86.                     rowsUpdated = mycommand.ExecuteNonQuery()
  87.                 End Using
  88.                 cnn.Close()
  89.             End Using
  90.             Return rowsUpdated
  91.         Catch ex As Exception
  92.             ShowDBError("ExecuteNonQuery", ex)
  93.             Return -1
  94.         End Try
  95.     End Function
  96.  
  97.     ''' <summary>
  98.     ''' Allows the programmer to retrieve single items from the db.
  99.     ''' </summary>
  100.     ''' <param name="SQL">the sql to run</param>
  101.     ''' <returns>a string</returns>
  102.     Public Function ExecuteScalar(ByVal SQL As String) As String
  103.         Dim value As Object
  104.  
  105.         Try
  106.             Using cnn As New SQLiteConnection(dbConnection)
  107.                 cnn.Open()
  108.                 Using mycommand As New SQLiteCommand(cnn)
  109.                     mycommand.CommandText = SQL
  110.                     value = mycommand.ExecuteScalar()
  111.                 End Using
  112.                 cnn.Close()
  113.             End Using
  114.  
  115.             If value IsNot Nothing Then
  116.                 Return value.ToString()
  117.             End If
  118.  
  119.             Return ""
  120.         Catch ex As Exception
  121.             ShowDBError("ExecuteScalar", ex)
  122.             Return ""
  123.         End Try
  124.     End Function
  125.  
  126.     ''' <summary>
  127.     ''' Allows the programmer to easily update rows in the db.
  128.     ''' </summary>
  129.     ''' <param name="TableName">The table to update</param>
  130.     ''' <param name="Data">A dictionary containing column names and their new values</param>
  131.     ''' <param name="where">the where clause for the update statement</param>
  132.     ''' <returns>a boolean true or false to signify success or failure.</returns>
  133.     Public Function Update(ByVal TableName As String, ByVal Data As Dictionary(Of String, String), ByVal where As String) As Boolean
  134.         Dim vals As String = ""
  135.         Dim returnCode As Boolean = True
  136.  
  137.         If Data.Count >= 1 Then
  138.             For Each val As KeyValuePair(Of String, String) In Data
  139.                 vals += String.Format("{0}='{1}',", val.Key.ToString(), val.Value.ToString())
  140.             Next
  141.             vals = vals.Substring(0, vals.Length - 1)
  142.         End If
  143.  
  144.         Try
  145.             Me.ExecuteNonQuery(String.Format("UPDATE {0} SET {1} WHERE {2};", TableName, vals, where))
  146.         Catch ex As Exception
  147.             ShowDBError("Update", ex)
  148.             returnCode = False
  149.         End Try
  150.         Return returnCode
  151.     End Function
  152.  
  153.     ''' <summary>
  154.     ''' Allows the programmer to easily delete rows from the db.
  155.     ''' </summary>
  156.     ''' <param name="TableName">the table from wich to delete.</param>
  157.     ''' <param name="Where">the where clause for the delete.</param>
  158.     ''' <returns>a boolean true or false to signify success or failure.</returns>
  159.     Public Function Delete(ByVal TableName As String, ByVal Where As String) As Boolean
  160.         Dim returnCode As Boolean = True
  161.         Try
  162.             Me.ExecuteNonQuery(String.Format("DELETE FROM {0} WHERE {1};", TableName, Where))
  163.         Catch ex As Exception
  164.             ShowDBError("Delete", ex)
  165.             returnCode = False
  166.         End Try
  167.         Return returnCode
  168.     End Function
  169.  
  170.     ''' <summary>
  171.     ''' Allows the programmer to insert into the db.
  172.     ''' </summary>
  173.     ''' <param name="TableName">The table into wich we insert the data.</param>
  174.     ''' <param name="data">a dictionary containing the column names and data.</param>
  175.     ''' <returns>a boolean true or false to signify success or failure.</returns>
  176.     Public Function Insert(ByVal TableName As String, ByVal data As Dictionary(Of String, String)) As Boolean
  177.         Dim columns As String = ""
  178.         Dim values As String = ""
  179.         Dim returnCode As Boolean = True
  180.  
  181.         For Each val As KeyValuePair(Of String, String) In data
  182.             columns += String.Format("{0},", val.Key.ToString())
  183.             values += String.Format("{0},", val.Value.ToString())
  184.         Next
  185.  
  186.         columns = columns.Substring(0, columns.Length - 1)
  187.         values = values.Substring(0, values.Length - 1)
  188.  
  189.         Try
  190.             Me.ExecuteNonQuery(String.Format("INSERT INTO {0}({1}) VALUES({2});", TableName, columns, values))
  191.         Catch ex As Exception
  192.             ShowDBError("Insert", ex)
  193.             returnCode = False
  194.         End Try
  195.  
  196.         Return returnCode
  197.     End Function
  198.  
  199.     ''' <summary>
  200.     ''' Allows the programmer to easily delete all data from the db.
  201.     ''' </summary>
  202.     ''' <returns>a boolean true or false to signify success or failure.</returns>
  203.     Public Function ClearDB()
  204.         Dim tables As DataTable
  205.  
  206.         Try
  207.             tables = Me.GetDataTable("SELECT NAME FROM SQLITE_MASTER WHERE type='table' order by NAME;")
  208.  
  209.             For Each table As DataRow In tables.Rows
  210.                 Me.ClearTable(table("NAME").ToString())
  211.             Next
  212.             Return True
  213.         Catch ex As Exception
  214.             ShowDBError("ClearDB", ex)
  215.             Return False
  216.         End Try
  217.     End Function
  218.  
  219.     ''' <summary>
  220.     ''' Allows the user to easily clear all data from a specific table.
  221.     ''' </summary>
  222.     ''' <param name="table">the name of the table to clear</param>
  223.     ''' <returns></returns>
  224.     Public Function ClearTable(ByVal table As String)
  225.         Try
  226.             Me.ExecuteNonQuery(String.Format("DELETE FROM {0};", table))
  227.             Return True
  228.         Catch ex As Exception
  229.             ShowDBError("ClearTable", ex)
  230.             Return False
  231.         End Try
  232.     End Function
  233.  
  234.     ''' <summary>
  235.     ''' Show a standard error message using messagebox
  236.     ''' </summary>
  237.     ''' <param name="SubName">Name of the sub or function where you add this sub</param>
  238.     ''' <param name="ex">the error in the catch</param>
  239.     Private Sub ShowDBError(ByVal SubName As String, ByVal ex As Exception)
  240.         MessageBox.Show(ex.Message, "Database Error in SQLiteDatabase." & SubName & "()", MessageBoxButtons.OK, MessageBoxIcon.Error)
  241.     End Sub
  242. End Class
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top