Click here to Skip to main content
13,197,548 members (51,347 online)
Click here to Skip to main content
Add your own
alternative version

Stats

6.2K views
772 downloads
29 bookmarked
Posted 11 Oct 2017

Using SQLite in C#/.Net

, 11 Oct 2017
Rate this:
Please Sign up or sign in to vote.
A quick guide to managing 100000'ish records efficiently with SQLite and C#/.Net.





 

Index

Introduction

The recent gains in memory and processing capabilities of PC, server, and laptop computers make the demand for manipulating and displaying complex data structures in everyday applications more and more common. This article provides a C#/.Net 101 style introduction to a well known and widely used open source database called SQLite (note that the spelling is not SQLLite or SQL Light).

The SQLite database engine is available for multiple operating systems (Android, IOS, Linux, Windows), programming languages, and can be used as in memory engine or as a local file based database engine. The engine requires no additional Set-up since it is implemented in a set of DLLs that are referenced in a given VS project.

Using an embedded database engine like SQLite enables us to work efficiently with complex data structures without having to either worry about:

  1. re-inventing the wheel when it comes to implementing complex structures, such as, an additional index or
  2. having additional Set-up/maintenance/security efforts for running a dedicated database server.

The application of embedded databases includes (but is not limited to):

  1. storing and retrieving data structures in an optimal format (Application File Format)
  2. computing difficult analysis on the fly without having to use an additional server

SQLite Limitations

A file based database cannot be accessed by 2 threads at any given time which is why it is important to always close a database after its usage since a potentially hanging thread could block a later call to Open().

Prerequisite

The SQLite database engine can be used via NuGet. Just create a new VS project (eg.: Console) and look for the System.Data.SQLite package. Install the package and start coding.

Background

I have created a series of Advanced WPF TreeView articles which resulted in some feedback along the line:

How do I store or retrieve TreeView based data to/from a file?

 

To answer this with SQlite in an applied way, I am doing this side-step to explain the basics of SQLite, and later on, build on it with a separate article that will explain storing tree structured data in a relational database engine.

Additional Tools

A relational database system includes usually some sort of (text based) SQL query application (eg.: SQL Management Studio or VS for SQL Server). The SQLite ecosystem contains tons of such client query applications. There is, for example, a SQLite / SQL Server Compact Toolbox Plug-in for Visual Studio that can be used to state SQL queries or review the current Data Definition of the database.

I am on the other hand a big fan of open source and so I am preferring for this and other reasons the SQLite Manager Plug-in for FireFox, since its available on all platforms and does not change my VS settings.

So, you can use either of these tools to check-up on your database whenever you perform any of the steps described below.

Using the Code

The sample applications in this tutorial are simple WPF projects with textual output. The code can be found in the MainWindow.xaml.cs file (unless stated differently).

 

A 'Hello World' SQLite Database

  1. Download 00_SQLite_tut.zip
  2. Download 01_SQLite.zip

The SQLite code in the sample projects is pretty self explanatory but it might raise questions for those who are not used to working with relational database systems. So, here are some snippets to explain the basics:

Creating a database (file)

You can use the following snippet to create a SQLite database file:

// create a new database connection:
SQLiteConnection sqlite_conn =
  new SQLiteConnection("Data Source=database.sqlite;Version=3;Compress=True;");

// open the connection:
SQLiteCommand sqlite_conn.Open();

The first line creates a SQLiteConnection object with a connection string as parameter to the constructor. This statement creates a file called database.sqlite in the current directory under Debug/bin or Release/bin. The minimal required version of SQLite is version 3 and the information stored in the file will be compressed.

// create a new database connection:
SQLiteConnection sqlite_conn =
  new SQLiteConnection("Data Source=:memory:;Version=3;");

// open the connection:
SQLiteCommand sqlite_conn.Open();

The above statement creates a SQLite database in-memory. Each in-memory database instance is unique and ceases to exist when the connection is closed.

Create a Table in the SQLite File

The statement below creates a new file based database and creates a table within it (The SQLite system does not (by default) recreate a table. You should receive a corresponding exception, if you execute the statement twice):

// create a new database connection:
SQLiteConnection sqlite_conn =
  new SQLiteConnection("Data Source=database.sqlite;Version=3;Compress=True;");

// open the connection:
SQLiteCommand sqlite_conn.Open();

SQLiteCommand sqlite_cmd = sqlite_conn.CreateCommand();

// Let the SQLiteCommand object know our SQL-Query:
sqlite_cmd.CommandText = "CREATE TABLE test (id integer primary key, text varchar(100));";

// Now lets execute the SQL ;-)
sqlite_cmd.ExecuteNonQuery();

You can use the last 2 lines to perform pretty much any SQL on the SQLite system. Typically, You end-up using the ExecuteNonQuery() method to perform operations on the data dictionary (create, drop etc.), while other methods, such as, ExecuteReader() can be used to retrieve (extended) results of that query defined in the CommandText property.

Use the create table statement below to create a table only for the first time. The table is otherwise re-used each time you insert or select data on it (see 01_SQLite_tut.zip).

sqlite_cmd.CommandText =
  @"CREATE TABLE IF NOT EXISTS
  [Mytable] (
  [Id]     INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  [NAME]   NVARCHAR(2048) NULL)";

Insert a Record into a Table

Here is a 'Hello World' example that writes 1 record with 2 values into an existing table:

SQLiteConnection sqlite_conn =
  new SQLiteConnection("Data Source=:memory:;Version=3;New=True;");

SQLiteCommand sqlite_conn.Open();

SQLiteCommand sqlite_cmd = sqlite_conn.CreateCommand();

sqlite_cmd.CommandText = "INSERT INTO test (id, text) VALUES (1, 'Hello World');";

sqlite_cmd.ExecuteNonQuery();

Read a Record from a Table

Here is a 'Hello World' example that reads records with 2 values from an existing table:

SQLiteConnection sqlite_conn;          // Database Connection Object
SQLiteCommand sqlite_cmd;             // Database Command Object
SQLiteDataReader sqlite_datareader;  // Data Reader Object

sqlite_conn = new SQLiteConnection("Data Source=database.sqlite;Version=3;New=True;Compress=True;");

sqlite_conn.Open();

sqlite_cmd = sqlite_conn.CreateCommand();

sqlite_cmd.CommandText = "SELECT * FROM test";

sqlite_datareader = sqlite_cmd.ExecuteReader();

// The SQLiteDataReader allows us to run through each row per loop
while (sqlite_datareader.Read()) // Read() returns true if there is still a result line to read
{
    // Print out the content of the text field:
    // System.Console.WriteLine("DEBUG Output: '" + sqlite_datareader["text"] + "'");

    object idReader = sqlite_datareader.GetValue(0);
    string textReader = sqlite_datareader.GetString(1);

    OutputTextBox.Text += idReader + " '" + textReader +"' "+ "\n";
}

The while loop in the above code sample executes until the query runs out of result data - or never executes, if the query cannot be satisfied with any results.

The GetValue(1) method shown above returns a .Net object value that can be converted into other types using reflection. You can also use the field index based syntax to get the same result: sqlite_datareader["text"].

You can also use alternative get value methods of the SQLiteDataReader object, such as, GetString(1) or GetInt32(1), if you know what the targeting .Net data-type should be based on the SQLite data:

bool GetBoolean(int i);
byte GetByte(int i);
char GetChar(int i);
DateTime GetDateTime(int i);
decimal GetDecimal(int i);
double GetDouble(int i);
float GetFloat(int i);
Guid GetGuid(int i);
short GetInt16(int i);
int GetInt32(int i);
long GetInt64(int i);

You either know the correct data type for a SQLiteDataReader column (since you just created it) or you can use a method below to determine the correct data type of a given column in a deterministic way:

// Retrieves the name of the back-end data-type of the column
string GetDataTypeName(int i);

//  Returns the .NET type of a given column
Type GetFieldType(int i);

// Retrieves the name of the column
string GetName(int i);

// Retrieves the i of a column, given its name
int GetOrdinal(string name);

// Returns the original name of the specified column.
string GetOriginalName(int i);

A SQLite Wrapper Class

A C#/.Net application usually comes with its own requirements and architectural patterns. This section discusses SQLite properties and methods that might be worth be wrapped in a first software version.

We will briefly review the Models.SQLiteDatabase database wrapper class, which hides some implementation details, such as, the name of the database file:

private const string _DBfileName = "database.sqlite";

The Models.SQLiteDatabase class also has custom properties for error logging and status display:

public Exception Exception { get; set; }

public string Status { get; private set; }

public string ExtendendStatus{ get; set; }

Overwriting an existing Database File

The database class also hides how a database connection is technically established and whether the SQLite database file is re-created on database connect or not:

private void ConstructConnection(bool overwriteFile = false)
{
    if (overwriteFile == true)
    {
        if (System.IO.File.Exists(@".\" + _DBfileName) == true)
        {
          // Overwrites a file if it is already there
          SQLiteConnection.CreateFile(_DBfileName);
        }
    }

     _Connection = new SQLiteConnection("Data Source=" + _DBfileName);

    Status = "Database is open.";
}

Pragma User Version

The SQLite database file specification has a user version property that can be readout or set via a non-SQL proprietary statement as shown below:

  1. pragma user_version; or
  2. pragma user_version = 1;

The user_version; attribute can be used by the client software to determine whether a given data file is current and how it could gracefully be handled if the file version appears to be too old or new.

public long UserVersion()
{
  using (SQLiteCommand cmd = new SQLiteCommand(_Connection))
  {
      cmd.CommandText = "pragma user_version;";
      return (long)cmd.ExecuteScalar();
  }
}

public long UserVersionIncrease()
{
  long version = UserVersion();

  using (SQLiteCommand cmd = new SQLiteCommand(_Connection))
  {
      cmd.CommandText = string.Format("pragma user_version = {0};"
                                    , version + 1);
      cmd.ExecuteNonQuery();
  }

  return UserVersion();
}

The 02_SQLite_tut.zip demo application uses the above code to write a new user version each time when it inserts data (re-using the existing database file). We can see that the SQLite user version default value is 0.

Please review the links below to learn more about pragma statements in SQLite:

  1. sqlite.org - PRAGMA Statements
  2. www.tutorialspoint.com - SQLite PRAGMA

Using a (Pojo) Model Class with SQLite

The DataRaeder section shows that SQLite can convert its data directly into a typed safe .Net object. The inverse direction - writing types safe .Net data objects into the database - is also supported by SQLite.

List<CategoryModel> values = new List<CategoryModel>();
values.Add(new CategoryModel(1,"ELECTRONICS"));
values.Add(new CategoryModel(2,"TELEVISIONS",1));
values.Add(new CategoryModel(3,"TUBE",2));

string query = "INSERT INTO category ([category_id],[name],[parent])VALUES(@category_id,@name,@parent)";

using (SQLiteCommand cmd = new SQLiteCommand(query, DB.Connection))
{
    int result = 0;
    using (var transaction = cmd.Connection.BeginTransaction())
    {
        foreach (var item in values)
        {
            cmd.Parameters.AddWithValue("@category_id", item.ID);
            cmd.Parameters.AddWithValue("@name", item.Name);
            cmd.Parameters.AddWithValue("@parent", item.Parent);
            result += cmd.ExecuteNonQuery();
        }

        transaction.Commit();
    }
}

The above statements create a list of CategoryModel objects and writes that contents of that list into the database table called category.

 

The SQLite system implements by default one implicit begin transaction and transaction commit per changed (insert, update, delete) record. This behaviour can be a significant slow down if you have to change 100s or more records at a time. A simple optimization that can be had here is to increase a transaction over multiple records.

The above sample code implements BeginTransaction and transaction.Commit() statements to change the default transaction behaviour. A transaction is now multiple record changes long and ensures that multiple inserts are performed more efficiently.

-- Efficiently insert/update records

using (SQLiteCommand cmd = new SQLiteCommand(query, DB.Connection))
{
    using (var transaction = cmd.Connection.BeginTransaction())
    {
    ...

        transaction.Commit();
    }
}

Converting Relational Data to XML

The sample application in this section describes a solution that can be used to write XML files from data stored in a relational database. The background of this application is that I wanted to have some interesting sample data to play with when it comes to writing article series on tree structures and other data related topics. So, I got myself a little data conversion tool that outputs XML data based on a SQLLite database.

The SQLite database was generated from PL-SQL statements that originated from a data project about all the cities, regions, and countries in the world of 2012: lokasyon.sql.gz. I converted the file linked above into a file that did not have the Insert and Values statement part as in the original posting above. From there, we are using the following code to read the data into a SQLite database (see ViewModels/AppViewModel.cs):

string createQuery =
    @"CREATE TABLE IF NOT EXISTS
    [meta_location] (
    [id]           INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    [iso]          VARCHAR(50)   DEFAULT NULL,
    [local_name]   varchar(255)  DEFAULT NULL,
    [type]         char(2)       DEFAULT NULL,
    [in_location]  unsigned int  DEFAULT NULL,
    [geo_lat]      double(18,11) DEFAULT NULL,
    [geo_lng]      double(18,11) DEFAULT NULL,
    [db_id]        varchar(50)   DEFAULT NULL)";

using (SQLiteCommand cmd = new SQLiteCommand(db.Connection))
{
    cmd.CommandText = createQuery;
    cmd.ExecuteNonQuery();
}

var cmdDeleteTable = new SQLiteCommand("delete from meta_location", db.Connection);
cmdDeleteTable.ExecuteNonQuery();

var lines = System.IO.File.ReadLines(@".\Resources\lokasyon.txt");

query = "INSERT INTO meta_location "
      + "([id], [iso], [local_name], [type], [in_location], [geo_lat], [geo_lng], [db_id])VALUES";

using (var transaction = db.Connection.BeginTransaction())
{
    foreach (var line in lines) // Write data out to database
    {
        if (line.Trim().Length > 0)  // Ignore empty lines
        {
            // Get rid of comma at the end of the line
            var valueLine = line.Replace("),", ")");

            // Adjust single quote escape from \' to SQLite ''
            valueLine = valueLine.Replace("\\'", "''");

            valueLine = valueLine.Replace("\\r", ""); // Get ride of these
            valueLine = valueLine.Replace("\\n", "");

            queryString = query + valueLine;

            var cmdInsert = new SQLiteCommand(queryString, db.Connection);
            cmdInsert.ExecuteNonQuery();
            iLines++;
        }
    }

    transaction.Commit();
}

The code snippet above reads the input file line by line into a lines string collection. The lines collection is then processed to generate a query string that performs a SQLite Insert statement, which in turn, is executed, row by row, in the last foreach loop shown above.

To generate a one file for all items (cities, regions, and countries) was not a clever choice because the resulting file would be too large and not every project might need all data items. Therefore, every data level (cities, regions, and countries) is written into a separate cs text file, which I then included into the project at: Models/Generate*.cs.

Here is the C# sample code generated from the SQLite database:

public static List<MetaLocationModel> Countries(List<MetaLocationModel> c)
{
  
  ...
  c.Add(new MetaLocationModel(78, "GE", "Georgia", LocationType.Country, -1, 42, 43.5, "GE"));
  c.Add(new MetaLocationModel(56, "DE", "Germany", LocationType.Country, -1, 51, 9, "DE"));
  c.Add(new MetaLocationModel(81, "GH", "Ghana", LocationType.Country, -1, 8, -2, "GH"));
  ...

  return c;
}

The C# code requires a PoJo class called MetaLocationModel. The MetaLocationModel class and the collection above can then be used to generate the XML output file:

private void WriteCityXmlModels(string filename)
{
    var items = new List<MetaLocationModel>();
    items = GenerateCity.Items(items);

    using (StreamWriter sw = new StreamWriter(filename))
    {
        using (TextWriter writer = TextWriter.Synchronized(sw))
        {
            new XmlSerializer(typeof(List<MetaLocationModel>)).Serialize(writer, items);
        }
    }
}

There is one method per generated XML file and there are 3 XML files in the end:

  1. bin\<Debug or Release>\Resources\countries.xml
  2. bin\<Debug or Release>\Resources\regions.xml
  3. bin\<Debug or Release>\Resources\cities.xml

The above data model gives a brief overview on the structure that is based around the iso column. The iso can be used as connecting element since the iso of a country is a sub-string of the iso in a region and the iso of a region is a sub-string of the iso in a city.

Conclusions

Using SQLite with large datasets (beyond 100.000 records) is easy, reliable and flexible, if you have a beginners understanding in relational databases, but using this embedded database system can also be recommended for non-database minded people since its application is not complex and it never hurts to have another technology stack ready for application.

The conversion application at the end of the article is a real plus and good future investment for data centered evaluations coming up in the future. Please let me know if you know other free data sources that contain more accurate city, region, and country data items than this database from 2012.

The points visited above show clearly that a custom (proprietary) file format can be a waste of time, if you are dealing with small projects budgets and large data structures that cannot be stored and retrieved in such a flexible and efficient manner using standard techniques like XML.

The SQLite eco-system is extremely large and also available in Java or Objectiv-C (on Apple computers). Taking this together with the available performance and stability is one single reasons (among many) for using this system more often then not.

References

  1. SQLite References
    1. sqlite.org
    2. www.tutorialspoint.com
  2. CodeProject - SQLite Helper (C#)
     
  3. Cities of the World Database
    Hints on StackOverflow
    lokasyon.sql.gz

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Dirk Bahle
Germany Germany
The Windows Presentation Foundation (WPF) and C# are among my favorites since I started developing Edi:

https://github.com/Dirkster99/Edi

and a few other projects on GitHub. I am normally an algorithms and structure type person but WPF has such interesting UI sides that I cannot help myself but get into this and MVVM.

https://de.linkedin.com/in/dirkbahle

You may also be interested in...

Pro
Pro

Comments and Discussions

 
QuestionSQLite multithreading and journal_mode Pin
svansickle13-Oct-17 7:50
membersvansickle13-Oct-17 7:50 
AnswerRe: SQLite multithreading and journal_mode Pin
Dirk Bahle13-Oct-17 8:10
memberDirk Bahle13-Oct-17 8:10 
QuestionCity database Pin
Peter Hagen13-Oct-17 2:22
memberPeter Hagen13-Oct-17 2:22 
AnswerRe: City database Pin
Dirk Bahle13-Oct-17 8:06
memberDirk Bahle13-Oct-17 8:06 
GeneralRe: City database Pin
Peter Hagen14-Oct-17 2:01
memberPeter Hagen14-Oct-17 2:01 
QuestionGood article Pin
Jyoti Kumari9611-Oct-17 20:10
memberJyoti Kumari9611-Oct-17 20:10 
PraiseRe: Good article Pin
Dirk Bahle11-Oct-17 20:24
memberDirk Bahle11-Oct-17 20:24 
PraiseRe: Good article Pin
Dirk Bahle13-Oct-17 8:01
memberDirk Bahle13-Oct-17 8:01 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.171020.1 | Last Updated 12 Oct 2017
Article Copyright 2017 by Dirk Bahle
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid