• (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
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2