Imports System.Data.SQLite

Module Module1

    Private _myDatabase As CustomerDatabase

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

        UpdateCustomer()
        PrintCustomers()

        DeleteCustomer()
        PrintCustomers()

        InputLoop()
    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 InputLoop()
        Dim lastInput As String = ""
        Do
            Console.WriteLine("Type a string, and I'll try to find a customer with that last name. (!q to quit).")
            lastInput = Console.ReadLine()

            If lastInput <> "!q" Then
                Dim foundCustomers() As Customer = _myDatabase.GetLastNamesLike(lastInput)
                Select Case foundCustomers.Length
                    Case 0
                        Console.WriteLine("No customers found.")
                    Case 1
                        Console.WriteLine("Found 1 customer.")
                    Case Else
                        Console.WriteLine("Found {0} customers.", foundCustomers.Length)
                End Select

                PrintCustomers(foundCustomers)
                Console.WriteLine()
            End If
        Loop Until lastInput = "!q"
    End Sub

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

        Dim customers() As Customer = _myDatabase.GetAll()
        PrintCustomers(customers)
    End Sub

    Private Sub PrintCustomers(ByVal customers() As Customer)
        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))
        ' Lesson 3: the query is converted to use parameters.
        Dim insertStatement As String = "INSERT INTO customers (firstName, lastname) VALUES (:firstName, :lastName)"

        Using connection As SQLiteConnection = CreateConnection()
            Using command As SQLiteCommand = connection.CreateCommand()
                command.CommandText = insertStatement
                command.Parameters.Add(New SQLiteParameter("firstName"))
                command.Parameters.Add(New SQLiteParameter("lastName"))

                For Each customer In customers
                    command.Parameters("firstName").Value = customer.FirstName
                    command.Parameters("lastName").Value = customer.LastName

                    If command.ExecuteNonQuery() = -1 Then
                        Console.WriteLine("An INSERT failed!")
                    End If
                Next
            End Using
        End Using

    End Sub

    ' Lesson 3: So much of Insert(Customer) had to be absorbed into the above method, it disappeared.

    ' 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

    ' New for Lesson 3: seeks customers with names similar to the input string.
    Public Function GetLastNamesLike(ByVal input As String) As Customer()
        Dim selectQuery As String = "SELECT id, firstName, lastName FROM customers WHERE lastName LIKE :lastName"

        ' The LIKE operator needs the % on the end of the string. That can't really be part of the query, and
        ' there's not a way to make the parameter respect it either. So you can fudge it by manually adding
        ' the %. If the user's being malicious, it's not like it'll hurt anything.
        Dim inputWithWildcard As String = input & "%"

        Dim customers As New List(Of Customer)()
        Using connection As SQLiteConnection = CreateConnection()
            Using command As SQLiteCommand = connection.CreateCommand()
                command.CommandText = selectQuery
                command.Parameters.Add(New SQLiteParameter("lastName", inputWithWildcard))

                Using reader As IDataReader = 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

    ' Lesson 3: Updated to use parameters.
    Public Sub Update(ByVal customer As Customer)
        Dim updateQuery As String = "UPDATE customers SET " &
            "firstName = :firstName, lastName = :lastName " &
            "WHERE id = :id"

        Using connection As SQLiteConnection = CreateConnection()
            Using command As SQLiteCommand = connection.CreateCommand()
                command.CommandText = updateQuery
                command.Parameters.Add(New SQLiteParameter("firstName", customer.FirstName))
                command.Parameters.Add(New SQLiteParameter("lastName", customer.LastName))
                command.Parameters.Add(New SQLiteParameter("id", customer.Id))

                If command.ExecuteNonQuery() = -1 Then
                    Console.WriteLine("An UPDATE failed!")
                End If
            End Using
        End Using
    End Sub

    ' Lesson 3: Updated to use parameters.
    Public Sub Delete(ByVal customer As Customer)
        Dim deleteQuery As String = "DELETE FROM customers WHERE id = :id"
        Using connection As SQLiteConnection = CreateConnection()
            Using command As SQLiteCommand = connection.CreateCommand()
                command.CommandText = deleteQuery
                command.Parameters.Add(New SQLiteParameter("id", customer.Id))

                If command.ExecuteNonQuery() = -1 Then
                    Console.WriteLine("A DELETE failed!")
                End If
            End Using
        End Using
    End Sub

    ' Lesson 3: This is only used for the CREATE TABLE query now, because it can't handle parameters in a general fashion.
    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