Using SQLite with C#

Question

I am trying to develop a simple application with a simple SQLite database. I am new to C# so I may have missed something obvious. When I run the following code, it returns the error:

SQL logic error or missing database.No such table: Customer (edit: Yes I have created that table within the database, I performed/confirmed this using the sqlite command prompt

Here is my code:

using System;
using System.Collections;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SQLite;
using System.Diagnostics;
using System.Drawing;
using System.IO;
using System.Text;
using System.Windows.Forms;

namespace TestPersonDatabase
{
public partial class DBconnection : Form
{
    SQLiteDatabase sqliteDb = new SQLiteDatabase();

    public DBconnection()
    {
        InitializeComponent();
    }

    // Using SQLite
    private void btnInsert_Click(object sender, EventArgs e)
    {

        Dictionary<String, String> data = new Dictionary<String, String>();
        data.Add("CustomerId", this.fieldInsertId.Text);
        data.Add("FirstName", this.fieldInsertFName.Text);
        data.Add("LastName", this.fieldInsertLName.Text);
        data.Add("MobileNumber", this.fieldInsertDob.Text);

        try 
        {
            sqliteDb.Insert("Customer", data);
        }
        catch(Exception error)
        {
            MessageBox.Show(error.Message);
        }
    }
}


class SQLiteDatabase
{    String dbConnection;

public SQLiteDatabase()
{
    dbConnection = "Data Source=" + (global::TestPersonDatabase.Properties.Resources.database);
}


    public bool Insert(String tableName, Dictionary<String, String> data)
{
    String columns = "";
    String values = "";
    Boolean returnCode = true;
    foreach (KeyValuePair<String, String> val in data)
    {
        columns += String.Format(" {0},", val.Key.ToString());
        values += String.Format(" '{0}',", val.Value);
    }
    columns = columns.Substring(0, columns.Length - 1);
    values = values.Substring(0, values.Length - 1);
    try
    {
        this.ExecuteNonQuery(String.Format("insert into {0}({1}) values({2});", tableName, columns, values));
    }
    catch (Exception fail)
    {
        MessageBox.Show(fail.Message);
        returnCode = false;
    }
    return returnCode;
}

Obviously the code above is two different classes put together. Just made it easier for you to read.

It seems like it cannot find the database file. But I appear to have linked it up correctly (its in the solution resources). Any help would be very much appreciated as I am a bit stumped! Thanks :)

Answer 1

It doesn't look like you've created the table.

Before you input any data you'll need to create the table using something like:

this.ExecuteNonQuerySQL("CREATE TABLE Customers(CustomerID INTEGER PRIMARY KEY, sPassword TEXT, CustomerName TEXT);");

Once you've created the table the insert code you have should work fine.

Answer 2

You never opened your sql connection try:

  dbConnection.Open();  //Initiate connection to the db