Create SQLite Database and table

Question

Within C# application code, I would like to create and then interact with one or more SQLite databases.

How do I initialize a new SQLite database file and open it for reading and writing?

Following the database's creation, how do I execute a DDL statement to create a table?

Answer 1

The next link will bring you to a great tutorial, that helped me a lot!

How to SQLITE in C#: I nearly used everything in that article to create the SQLite database for my own C# Application.

Preconditions

1. Download the SQLite.dll

   • either by addding the SQLite DLL's manually
   • or by using NuGet

2. Add it as a reference to your project

3. Refer to the dll from your code using the following line on top of your class: using System.Data.SQLite;

Code sample

The code below creates a database file and inserts a record into it:

// this creates a zero-byte file
SQLiteConnection.CreateFile("MyDatabase.sqlite");

string connectionString = "Data Source=MyDatabase.sqlite;Version=3;";
SQLiteConnection m_dbConnection = new SQLiteConnection(connectionString);
m_dbConnection.Open();

// varchar will likely be handled internally as TEXT
// the (20) will be ignored
// see https://www.sqlite.org/datatype3.html#affinity_name_examples
string sql = "Create Table highscores (name varchar(20), score int)";
// you could also write sql = "CREATE TABLE IF NOT EXISTS highscores ..."
SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);
command.ExecuteNonQuery();

sql = "Insert into highscores (name, score) values ('Me', 9001)";
command = new SQLiteCommand(sql, m_dbConnection);
command.ExecuteNonQuery();

m_dbConnection.Close();

After you created a create script in C#, you might want to add rollback transactions. It will ensure that data will be committed at the end in one big piece as an atomic operation to the database and not in little pieces, where it could fail at 5th of 10th query for example.

Example on how to use transactions:

using (TransactionScope transaction = new TransactionScope())
{
   //Insert create script here.

   // Indicates that creating the SQLiteDatabase went succesfully,
   // so the database can be committed.
   transaction.Complete();
}

3rd party edit

To read records you can use ExecuteReader()

sql = "SELECT score, name, Length(name) as Name_Length 
       FROM highscores WHERE score > 799";
command = new SQLiteCommand(sql, m_dbConnection);
SQLiteDataReader reader = command.ExecuteReader();

while(reader.Read())
{
   Console.WriteLine(reader[0].ToString()  + " " 
                  +  reader[1].ToString()  + " " 
                  +  reader[2].ToString());            
}
dbConnection.Close();

See also this transactionscope example