Create and Connect to an SQLite database in C#

Submitted by rinvizle on Thursday, June 30, 2016 - 15:36.

Hello guiz this tutorial is to teach you how to create a new SQLite database from scratch, create a new table in it, insert and read values from it. This is merely an entry level example to give you an idea on how to start. First you will need System.Data.SQLite library from system.data.sqlite.org. Head over to their download section and download the libraries that best suit your need depending on the .NET Framework you want to target and the Windows bit version.

Finally, the snippet below should give you the general idea on the main functions you will need to learn first, mainly.

Creating a file for your database Creating a table in your database Inserting information in the database Retrieving information from the database

string createTableQuery = @"CREATE TABLE IF NOT EXISTS [MyTable] (
                          [ID] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
                          [Key] NVARCHAR(2048)  NULL,
                          [Value] VARCHAR(2048)  NULL
                          )";
 
System.Data.SQLite.SQLiteConnection.CreateFile("databaseFile.db3");
using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=databaseFile.db3"))
{
    using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con))
    {
        con.Open();
 
        com.CommandText = createTableQuery;
        com.ExecuteNonQuery();
 
        com.CommandText = "INSERT INTO MyTable (Key,Value) Values ('key one','value one')";
        com.ExecuteNonQuery();
        com.CommandText = "INSERT INTO MyTable (Key,Value) Values ('key two','value value')";
        com.ExecuteNonQuery();
 
        com.CommandText = "Select * FROM MyTable";
 
        using (System.Data.SQLite.SQLiteDataReader reader = com.ExecuteReader())
        {
            while (reader.Read())
            {
                Console.WriteLine(reader["Key"] + " : " + reader["Value"]);
            }
        }
        con.Close();
    }
}

And the output will be.

key one : value one
key two : value value

Add new comment

163 views