C# and SQLite Introduction

SQLite is a database that can be used in conjunction with C# to create desktop, web, mobile and console based application. It can be used to store data, as well as configuration information. C#, together with SQL, can be used to query the data stored within an SQLite database, as well as insert, update and delete the data.

Connecting to an SQLite Database

In order to access data within an SQLite database using C#, a connection to the database must first be established. To facilitate this, two packages, 'Microsoft.Data.Sqlite.Core' and 'SQLitePCLRaw.bundle_e_sqlcipher', first need to be added to the project. This can be done in a number of different ways, depending on what Integrated Development Environment (IDE) is being used. Visual Studio incorporates NuGet Package Manager, which allows for packages to be searched for and installed. It also has an integrated package manager console, where these packages can be added using the following commands.

Install-Package Microsoft.Data.Sqlite.Core
Install-Package SQLitePCLRaw.bundle_e_sqlcipher

For IDEs that don't have a built in package manager, Powershell can be used. The following commands can be used to install the above mentioned packages. Before running these commands it is necessary to navigate to the folder where the project resides.

dotnet add package Microsoft.Data.Sqlite.Core
dotnet add package SQLitePCLRaw.bundle_e_sqlcipher

Once added, a ‘using‘ statement for the ‘Microsoft.Data.Sqlite’ namespace needs to be included.

Unlike other databases, such as MySQL, SQLite does not require a separate server process, but instead exists as a stand-alone file, that can be embedded directly into an application.

The example connection below uses the encrypted version of SQLite, which accounts for the need for a password. The regular version of SQLite does not require a password for entry to the database, but instead relies on file permissions to provide security.

// Database.
string database = @"C:\Demo\DemoDB.db";
string password = @"DemoPW";
SqliteConnection connect;

// Check if the database exists.
if (!File.Exists(database))
{

    // Message confirming incorrect database location.
    Console.WriteLine("Error locating database.");

    // Stop program execution.
    System.Environment.Exit(1);

}

// Database connection.
connect = new SqliteConnection("Data Source=" + database + ";" +
                                "Password=" + password);

try
{

    // Connect to database.
    connect.Open();

    // Message confirming successful database connection.
    Console.WriteLine("Database connection successful.");

}
catch (Exception e)
{

    // Message confirming unsuccessful database connection.
    Console.WriteLine("Database connection unsuccessful.");

    // Stop program execution.
    System.Environment.Exit(1);

}

Firstly, in this example, a check is made to see if the database file actually exists. If it doesn’t, a message is displayed and execution of the program is halted. If successfully found, an attempt is made to connect to the database, which is enclosed in a ‘try-catch’ block. The ‘try-catch’ block is used to catch any exceptions that arise in trying to connect to the database and deal with them in a user friendly manner. Again, execution of the program is stopped after the confirmation of an unsuccessful database connection.

Further Resources