• (2 Pages)
  • +
  • 1
  • 2

Using SQLite with C#

#16 LPT.Evil.Inc  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 1
  • Joined: 15-November 12

Posted 15 November 2012 - 04:47 AM

I want to encode an DQLite database into UTF-8, what should I do ?
Was This Post Helpful? 0
  • +
  • -

#17 nattelip  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 1
  • Joined: 02-February 13

Posted 02 February 2013 - 07:25 AM

thanks , good start for a starter in sql language with sqlite
Was This Post Helpful? 0
  • +
  • -

#18 steven.davisworth  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 3
  • Joined: 16-July 13

Posted 16 July 2013 - 05:47 PM

Does anyone know how to implement
select last_insert_rowid()

within the Insert helper class?
Was This Post Helpful? 0
  • +
  • -

#19 Kati Maya  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 1
  • Joined: 10-November 13

Posted 10 November 2013 - 05:15 PM

I liked this article and it helped me create a decent database layer abstraction class for sqlite. The main problem with your code is that it doesn't protect against SQL Injection. My code uses a similar syntax for inserting and updates and whatnot but uses parameterization instead and keeps the data in its original datatype as much as possible.

I also added some features like for returning a Dictionary object to use as a quick and dirty hash lookup.

I feel like your code is good for learning the basics, but what I really needed was something that was production worthy, so I wrote my own class for that. I prefer VB syntax but there's a converter on this site somewhere if you want to do C# instead:

Imports System.Collections.Generic
Imports System.Data
Imports System.Data.SQLite
Imports System.Windows.Forms

Class sqlite
    Private db As String
    Private debug As Boolean = False

    Public Sub New()

        db = My.Settings.database

#If DEBUG Then
        debug = True
#End If

    End Sub

    'grab a single value from command
    Public Function getValue(sql As SQLiteCommand) As Object
        If debug Then SqlString(sql)
        Dim conn As New SQLiteConnection(db)
        conn.Open()
        sql.Connection = conn
        Dim value As Object = sql.ExecuteScalar()
        conn.Close()
        If value IsNot Nothing Then
            Return value
        Else
            Return False
        End If
    End Function

    'grab a single value from string query
    Public Function getValue(sql As String) As Object
        If debug Then Console.WriteLine(sql)
        Dim conn As New SQLiteConnection(db)
        conn.Open()
        Dim cmd As New SQLiteCommand(conn)
        cmd.CommandText = sql
        Dim value As Object = cmd.ExecuteScalar()
        conn.Close()
        If value IsNot Nothing Then
            Return value
        Else
            Return False
        End If
    End Function

    'returns a DataTable from a command
    Public Function getData(sql As SQLiteCommand) As DataTable
        If debug Then SqlString(sql)
        Dim dt As New DataTable()
        Try
            Dim conn As New SQLiteConnection(db)
            conn.Open()
            sql.Connection = conn
            Dim reader As SQLiteDataReader = sql.ExecuteReader()
            dt.Load(reader)
            reader.Close()
            conn.Close()
        Catch e As Exception
            Throw New Exception(e.Message)
        End Try
        Return dt
    End Function

    'returns a DataTable from a string query
    Public Function getData(sql As String) As DataTable
        If debug Then Console.WriteLine(sql)
        Dim dt As New DataTable()
        Try
            Dim conn As New SQLiteConnection(db)
            conn.Open()
            Dim cmd As New SQLiteCommand(conn)
            cmd.CommandText = sql
            Dim reader As SQLiteDataReader = cmd.ExecuteReader()
            dt.Load(reader)
            reader.Close()
            conn.Close()
        Catch e As Exception
            Throw New Exception(e.Message)
        End Try
        Return dt
    End Function

    'executes sql statement returning # of rows affected
    Public Function execute(sql As String) As Integer
        Dim rowsUpdated As Integer = -1
        Try
            Dim conn As New SQLiteConnection(db)
            conn.Open()
            Dim cmd As New SQLiteCommand(conn)
            cmd.CommandText = sql
            rowsUpdated = cmd.ExecuteNonQuery()
            conn.Close()
        Catch e As Exception
            Throw New Exception(e.Message)
        Finally
            If debug Then
                Console.WriteLine(sql)
                Console.WriteLine(String.Format("Rows Updated: {0}", rowsUpdated))
                Console.WriteLine(vbNewLine)
            End If
        End Try
        Return rowsUpdated
    End Function

    'executes sql statement returning # of rows affected
    Public Function execute(sql As SQLiteCommand) As Integer

        Dim rowsUpdated As Integer = -1
        Try
            Dim conn As New SQLiteConnection(db)
            conn.Open()
            sql.Connection = conn
            rowsUpdated = sql.ExecuteNonQuery()
            conn.Close()
        Catch e As Exception
            Throw New Exception(e.Message)
        Finally
            If debug Then SqlString(sql, rowsUpdated)
        End Try
        Return rowsUpdated
    End Function


    'return dictionary record, assuming a table structure of id,value, to speed up lookups by loading tables into memory
    Public Function getDictionary(ByRef table As String, ByRef column As String) As Dictionary(Of String, Integer)



        Dim out As New Dictionary(Of String, Integer)
        Dim dt As New DataTable()
        Try
            Dim conn As New SQLiteConnection(db)
            conn.Open()
            Dim cmd As New SQLiteCommand(conn)
            cmd.CommandText = String.Format("select id,{0} from {1}", column, table)


            If debug Then Console.WriteLine(cmd.CommandText)


            Dim reader As SQLiteDataReader = cmd.ExecuteReader()
            dt.Load(reader)
            reader.Close()
            conn.Close()

            Dim row As DataRow

            For Each row In dt.Rows
                out.Add(row.Item(column), row.Item("id"))
            Next


        Catch e As Exception
            Throw New Exception(e.Message)
        End Try
        Return out


    End Function


    'insert data, returns last inserted id
    Public Function insert(table As String, data As Dictionary(Of String, Object)) As Integer

        Dim lastID As Integer = 0
        Try
            If data.Count >= 1 Then

                Dim conn As New SQLiteConnection(db)
                Dim columns As String = ""
                Dim values As String = ""
                Dim sql As String = ""
                Dim cmd As New SQLiteCommand

                sql = "insert into {0} ({1}) values({2});"

                For Each val As KeyValuePair(Of String, Object) In data
                    columns &= val.Key & ","
                    values &= "@" & val.Key & ","
                    cmd.Parameters.AddWithValue("@" & val.Key, val.Value)
                Next
                columns = columns.Substring(0, columns.Length - 1)
                values = values.Substring(0, values.Length - 1)

                cmd.CommandText = String.Format(sql, table, columns, values)

                If debug Then SqlString(cmd)

                conn.Open()
                cmd.Connection = conn
                cmd.ExecuteNonQuery()


                cmd.CommandText = "select last_insert_rowid();"

                lastID = cmd.ExecuteScalar

                conn.Close()

            End If

        Catch e As Exception
            Throw New Exception(e.Message)
        End Try
        Return lastID

    End Function


    'insert or ignore data, returns rows updated
    Public Function insertOrIgnore(table As String, data As Dictionary(Of String, Object)) As Integer

        Dim rowsUpdated As Integer = -1
        Try
            If data.Count >= 1 Then

                Dim columns As String = ""
                Dim values As String = ""
                Dim sql As String = ""
                Dim cmd As New SQLiteCommand

                sql = "insert or ignore into {0} ({1}) values({2});"

                For Each val As KeyValuePair(Of String, Object) In data
                    columns &= val.Key & ","
                    values &= "@" & val.Key & ","
                    cmd.Parameters.AddWithValue("@" & val.Key, val.Value)
                Next
                columns = columns.Substring(0, columns.Length - 1)
                values = values.Substring(0, values.Length - 1)

                cmd.CommandText = String.Format(sql, table, columns, values)

                rowsUpdated = execute(cmd)

                If debug Then SqlString(cmd, rowsUpdated)

            End If

        Catch e As Exception
            Throw New Exception(e.Message)
        End Try
        Return rowsUpdated

    End Function


    'update a table, returns rows updated.  probably best to only use on simple integer primary key-based where clauses
    Public Function update(table As String, data As Dictionary(Of String, Object), where As String) As Integer

        Dim rowsUpdated As Integer = -1

        Try
            If data.Count >= 1 Then

                Dim cmd As New SQLiteCommand

                Dim sql As String = "update " & table & " set "

                For Each val As KeyValuePair(Of String, Object) In data

                    sql &= val.Key & "=@" & val.Key & ","

                    cmd.Parameters.AddWithValue("@" & val.Key, val.Value)

                Next

                cmd.CommandText = sql.Substring(0, sql.Length - 1) & " where " & where & ";"

                If debug Then SqlString(cmd)

                rowsUpdated = execute(cmd)

            End If

        Catch e As Exception
            Throw New Exception(e.Message)
        End Try
        Return rowsUpdated
    End Function


    Public Function SqlString(ByVal cmd As SQLiteCommand, Optional ByVal rows As Integer = 0)

        Dim builder As New System.Text.StringBuilder(vbNewLine & "----------------")

        For Each item As SQLiteParameter In cmd.Parameters
            builder.Append(vbNewLine)
            builder.AppendFormat("{0} = [{1}] ({2})", item.ParameterName, item.Value, item.DbType)
        Next

        builder.AppendLine(vbNewLine)
        builder.AppendLine(cmd.CommandText)

        If rows > 0 Then
            builder.AppendFormat("Rows Updated: {0}", rows)
            builder.Append(vbNewLine)
        End If
        builder.AppendLine("----------------")

        Console.WriteLine(builder.ToString)

        Return builder

    End Function

End Class



View Poststeven.davisworth, on 16 July 2013 - 05:47 PM, said:

Does anyone know how to implement
select last_insert_rowid()

within the Insert helper class?

Please check out the code I just posted; my insert method does return the new ID field.
Was This Post Helpful? 0
  • +
  • -

#20 Taximaniac  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 1
  • Joined: 26-February 17

Posted 26 February 2017 - 01:24 PM

Here is the class ported to vb.net also on pastebin: http://pastebin.com/H7sPRB6u

'This file is a SQLite Database helper Class
'This file was orginally written in C# by #1 brennydoogles and some functions by Mike Duncan
'I Taximaniac have ported it over to vb.net
'ENJOY
'orginal code at http://www.dreamincode.net/forums/topic/157830-using-sqlite-with-c%23/
 
Imports System
Imports System.Collections.Generic
Imports System.Data
Imports System.Data.SQLite
Imports System.Windows.Forms
 
Public Class SQLiteDatabase
 
    Private dbConnection As String
 
    ''' <summary>
    '''  Default Constructor for SQLiteDatabase Class.
    ''' </summary>
    Public Sub New()
        dbConnection = "Data Source=CCMemberStats.s3db"
    End Sub
 
    ''' <summary>
    ''' Single Param Constructor for specifying the DB File
    ''' </summary>
    ''' <param name="inputFile">The file containing the DB</param>
    Public Sub New(ByVal inputFile As String)
        dbConnection = String.Format("Data Source={0}", inputFile)
    End Sub
 
    ''' <summary>
    ''' Single Param Constructor for specifying advanced connection options
    ''' </summary>
    ''' <param name="connectionOpts">A dictionary containing all desired options and their values.</param>
    Public Sub New(ByVal connectionOpts As Dictionary(Of String, String))
        Dim str As String = ""
 
        For Each row As KeyValuePair(Of String, String) In connectionOpts
            str += String.Format("{0}={1};", row.Key, row.Value)
        Next
 
        str = str.Trim().Substring(0, str.Length - 1)
        dbConnection = str
    End Sub
 
    ''' <summary>
    ''' Allows the programmer to run a query against the database.
    ''' </summary>
    ''' <param name="SQL">The SQL to run.</param>
    ''' <returns>A datatable containing the result set.</returns>
    Public Function GetDataTable(ByVal SQL As String) As DataTable
        Dim dt As DataTable = New DataTable()
 
        Try
            Using cnn As New SQLiteConnection(dbConnection)
                cnn.Open()
                Using mycommand As New SQLiteCommand(cnn)
                    mycommand.CommandText = SQL
                    Dim reader As SQLiteDataReader = mycommand.ExecuteReader()
                    dt.Load(reader)
                    reader.Close()
                End Using
                cnn.Close()
            End Using
 
            Return dt
        Catch ex As Exception
            ShowDBError("GetDataTable", ex)
            Return Nothing
        End Try
    End Function
 
    ''' <summary>
    ''' Allows the programmer to interact with the database for purposes other than query.
    ''' </summary>
    ''' <param name="SQL">The sql to be run</param>
    ''' <returns>An integer containing the number of rows affected. if error it returns -1</returns>
    Public Function ExecuteNonQuery(ByVal SQL As String) As Integer
        Dim rowsUpdated As Integer
        Try
            Using cnn As New SQLiteConnection(dbConnection)
                cnn.Open()
                Using mycommand As New SQLiteCommand(cnn)
                    mycommand.CommandText = SQL
                    rowsUpdated = mycommand.ExecuteNonQuery()
                End Using
                cnn.Close()
            End Using
            Return rowsUpdated
        Catch ex As Exception
            ShowDBError("ExecuteNonQuery", ex)
            Return -1
        End Try
    End Function
 
    ''' <summary>
    ''' Allows the programmer to retrieve single items from the db.
    ''' </summary>
    ''' <param name="SQL">the sql to run</param>
    ''' <returns>a string</returns>
    Public Function ExecuteScalar(ByVal SQL As String) As String
        Dim value As Object
 
        Try
            Using cnn As New SQLiteConnection(dbConnection)
                cnn.Open()
                Using mycommand As New SQLiteCommand(cnn)
                    mycommand.CommandText = SQL
                    value = mycommand.ExecuteScalar()
                End Using
                cnn.Close()
            End Using
 
            If value IsNot Nothing Then
                Return value.ToString()
            End If
 
            Return ""
        Catch ex As Exception
            ShowDBError("ExecuteScalar", ex)
            Return ""
        End Try
    End Function
 
    ''' <summary>
    ''' Allows the programmer to easily update rows in the db.
    ''' </summary>
    ''' <param name="TableName">The table to update</param>
    ''' <param name="Data">A dictionary containing column names and their new values</param>
    ''' <param name="where">the where clause for the update statement</param>
    ''' <returns>a boolean true or false to signify success or failure.</returns>
    Public Function Update(ByVal TableName As String, ByVal Data As Dictionary(Of String, String), ByVal where As String) As Boolean
        Dim vals As String = ""
        Dim returnCode As Boolean = True
 
        If Data.Count >= 1 Then
            For Each val As KeyValuePair(Of String, String) In Data
                vals += String.Format("{0}='{1}',", val.Key.ToString(), val.Value.ToString())
            Next
            vals = vals.Substring(0, vals.Length - 1)
        End If
 
        Try
            Me.ExecuteNonQuery(String.Format("UPDATE {0} SET {1} WHERE {2};", TableName, vals, where))
        Catch ex As Exception
            ShowDBError("Update", ex)
            returnCode = False
        End Try
        Return returnCode
    End Function
 
    ''' <summary>
    ''' Allows the programmer to easily delete rows from the db.
    ''' </summary>
    ''' <param name="TableName">the table from wich to delete.</param>
    ''' <param name="Where">the where clause for the delete.</param>
    ''' <returns>a boolean true or false to signify success or failure.</returns>
    Public Function Delete(ByVal TableName As String, ByVal Where As String) As Boolean
        Dim returnCode As Boolean = True
        Try
            Me.ExecuteNonQuery(String.Format("DELETE FROM {0} WHERE {1};", TableName, Where))
        Catch ex As Exception
            ShowDBError("Delete", ex)
            returnCode = False
        End Try
        Return returnCode
    End Function
 
    ''' <summary>
    ''' Allows the programmer to insert into the db.
    ''' </summary>
    ''' <param name="TableName">The table into wich we insert the data.</param>
    ''' <param name="data">a dictionary containing the column names and data.</param>
    ''' <returns>a boolean true or false to signify success or failure.</returns>
    Public Function Insert(ByVal TableName As String, ByVal data As Dictionary(Of String, String)) As Boolean
        Dim columns As String = ""
        Dim values As String = ""
        Dim returnCode As Boolean = True
 
        For Each val As KeyValuePair(Of String, String) In data
            columns += String.Format("{0},", val.Key.ToString())
            values += String.Format("{0},", val.Value.ToString())
        Next
 
        columns = columns.Substring(0, columns.Length - 1)
        values = values.Substring(0, values.Length - 1)
 
        Try
            Me.ExecuteNonQuery(String.Format("INSERT INTO {0}({1}) VALUES({2});", TableName, columns, values))
        Catch ex As Exception
            ShowDBError("Insert", ex)
            returnCode = False
        End Try
 
        Return returnCode
    End Function
 
    ''' <summary>
    ''' Allows the programmer to easily delete all data from the db.
    ''' </summary>
    ''' <returns>a boolean true or false to signify success or failure.</returns>
    Public Function ClearDB()
        Dim tables As DataTable
 
        Try
            tables = Me.GetDataTable("SELECT NAME FROM SQLITE_MASTER WHERE type='table' order by NAME;")
 
            For Each table As DataRow In tables.Rows
                Me.ClearTable(table("NAME").ToString())
            Next
            Return True
        Catch ex As Exception
            ShowDBError("ClearDB", ex)
            Return False
        End Try
    End Function
 
    ''' <summary>
    ''' Allows the user to easily clear all data from a specific table.
    ''' </summary>
    ''' <param name="table">the name of the table to clear</param>
    ''' <returns></returns>
    Public Function ClearTable(ByVal table As String)
        Try
            Me.ExecuteNonQuery(String.Format("DELETE FROM {0};", table))
            Return True
        Catch ex As Exception
            ShowDBError("ClearTable", ex)
            Return False
        End Try
    End Function
 
    ''' <summary>
    ''' Show a standard error message using messagebox
    ''' </summary>
    ''' <param name="SubName">Name of the sub or function where you add this sub</param>
    ''' <param name="ex">the error in the catch</param>
    Private Sub ShowDBError(ByVal SubName As String, ByVal ex As Exception)
        MessageBox.Show(ex.Message, "Database Error in SQLiteDatabase." & SubName & "()", MessageBoxButtons.OK, MessageBoxIcon.Error)
    End Sub
End Class


Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2