Imports System.Data.SQLite

Module Module1

    Private _myDatabase As CustomerDatabase

    Sub Main()
        InitializeDatabase()
        InsertCustomers()
        PrintCustomers()

        UpdateCustomer()
        PrintCustomers()

        DeleteCustomer()
        PrintCustomers()
    End Sub

    Private Sub InitializeDatabase()
        Dim fileName As String = "test.db"
        _myDatabase = New CustomerDatabase(fileName)
        _myDatabase.Reset()
        Console.WriteLine("Database created!")
    End Sub

    Private Sub InsertCustomers()
        Dim customers As New List(Of Customer)

        ' For an INSERT, the Id will be auto-generated. So I do not initialize the Id property.
        customers.Add(New Customer() With {.FirstName = "First", .LastName = "Last"})
        customers.Add(New Customer() With {.FirstName = "Alice", .LastName = "Example"})
        customers.Add(New Customer() With {.FirstName = "Bob", .LastName = "Example"})
        customers.Add(New Customer() With {.FirstName = "Janice", .LastName = "LastName"})

        _myDatabase.Insert(customers)
        Console.WriteLine("Customers inserted!")
    End Sub

    ' Change the information in one Customer object, then update it in the database.
    Private Sub UpdateCustomer()
        Dim customers() As Customer = _myDatabase.GetAll()
        Dim theCustomer As Customer = customers(2)
        theCustomer.LastName = "Banana"

        _myDatabase.Update(theCustomer)
        Console.WriteLine("Updated Bob Example!")
    End Sub

    Private Sub DeleteCustomer()
        Dim customers() As Customer = _myDatabase.GetAll()
        Dim theCustomer As Customer = customers(0)

        _myDatabase.Delete(theCustomer)
        Console.WriteLine("Deleted a customer!")
    End Sub

    Private Sub PrintCustomers()
        Console.WriteLine("Current customer database:")

        Dim customers() As Customer = _myDatabase.GetAll()
        For Each customer In customers
            Console.WriteLine(" {0,3}|{1,10}|{2,10}", customer.Id, customer.FirstName, customer.LastName)
        Next
    End Sub

End Module

Public Class CustomerDatabase

    Private Const __ConnectionStringTemplate As String = "Data Source = {0}; Version = 3;"

    Private _fileName As String

    Private ReadOnly Property ConnectionString As String
        Get
            Return String.Format(__ConnectionStringTemplate, _fileName)
        End Get
    End Property

    Public Sub New(ByVal fileName As String)
        _fileName = fileName
    End Sub

    Public Sub Reset()
        System.IO.File.Delete(_fileName)
        CreateTable()
    End Sub

    ' Creating the table is a 'non-query' command, because it doesn't really return results. It might
    ' return a number, but mostly I expect it to throw an exception if something goes wrong.
    Private Sub CreateTable()
        Dim tableSchema As String = "CREATE TABLE customers (" &
            "id INTEGER PRIMARY KEY," &
            "firstName TEXT," &
            "lastName TEXT" &
            ")"

        ' Note how having the helper function makes this method very simple! It's just a string and a call.
        If ExecuteNonQuery(tableSchema) = -1 Then
            Console.WriteLine("Whoa. Why did the table creation return -1?")
        End If
    End Sub

    ' Inserting a lot of customers involves converting each customer to an INSERT query and executing it.
    ' SQLite does allow multiple inserts in one batch, but it has a limit on the number and it's easier
    ' to do one at a time.
    Public Sub Insert(ByVal customers As IEnumerable(Of Customer))
        For Each customer In customers
            Insert(customer)
        Next
    End Sub

    Public Sub Insert(ByVal customer As Customer)
        Dim insertTemplate As String = "INSERT INTO customers (firstName, lastname) VALUES ('{0}', '{1}')"
        Dim insertStatement = String.Format(insertTemplate, customer.FirstName, customer.LastName)
        If ExecuteNonQuery(insertStatement) <> 1 Then
            Console.WriteLine("Whoa, inserting customer (firstname: {0}) failed.", customer.FirstName)
        End If
    End Sub

    ' This is a bit ugly because you have to do some work to hide the ugly created by
    ' IDataReader.
    Public Function GetAll() As Customer()
        Dim selectQuery = "SELECT id, firstName, lastName FROM customers"
        Dim customers As New List(Of Customer)()

        Using connection As SQLiteConnection = CreateConnection()
            Using command As SQLiteCommand = connection.CreateCommand()
                command.CommandText = selectQuery
                Using reader As SQLiteDataReader = command.ExecuteReader()
                    While reader.Read()
                        customers.Add(GetCustomer(reader))
                    End While
                End Using
            End Using
        End Using

        Return customers.ToArray()
    End Function

    ' Reads a customer row and converts it to a Customer. Note that since the database gives
    ' us the id, it's valid to keep it around.
    Private Function GetCustomer(ByVal reader As IDataReader) As Customer
        Dim customer As New Customer
        customer.Id = CInt(reader("id"))
        customer.FirstName = reader("firstName").ToString()
        customer.LastName = reader("lastName").ToString()

        Return customer
    End Function

    ' To update a customer, it must have an ID!
    Public Sub Update(ByVal customer As Customer)
        Dim updateQueryTemplate As String = "UPDATE customers SET " &
            "firstName = '{0}', lastName = '{1}' " &
            "WHERE id = {2}"
        Dim updateQuery As String = String.Format(updateQueryTemplate, customer.FirstName, customer.LastName, customer.Id)

        ExecuteNonQuery(updateQuery)
    End Sub

    Public Sub Delete(ByVal customer As Customer)
        Dim deleteQueryTemplate As String = "DELETE FROM customers WHERE id = {0}"
        Dim deleteQuery As String = String.Format(deleteQueryTemplate, customer.Id)

        ExecuteNonQuery(deleteQuery)
    End Sub

    Private Function ExecuteNonQuery(ByVal commandText As String) As Integer
        Using connection As SQLiteConnection = CreateConnection()
            Using command As SQLiteCommand = connection.CreateCommand()
                command.CommandText = commandText
                Dim result As Integer = command.ExecuteNonQuery()
                Return result
            End Using
        End Using
    End Function

    Private Function CreateConnection() As SQLiteConnection
        Dim connection As New SQLiteConnection(ConnectionString)
        connection.Open()
        Return connection
    End Function


End Class

Public Class Customer
    Public Property FirstName As String
    Public Property LastName As String

    ' It's always worth keeping the ID around for a type you're
    ' sending to the database. That way it's easier to write
    ' UPDATE or DELETE queries.
    Public Property Id As Integer
End Class