SQLite and Dapper in C#

In this article I am going to cover the basics of running SQLite and Dapper in a C# console application. There is a lot of code below which may be highly opinionated (so please bear that in mind while reading it) with links to an example application on Github below should you wish to see it all in action.

Tools and Resources

While creating this article I made use of the following online resources and tools, and feel that the original authors should get the credit for the bits and pieces I used.

First Steps

First thing we need to do is create a new console application in Visual Studio and install SQLite and Dapper via the package manager console.

Install-Package System.Data.SQLite.Core
Install-Package Dapper

This will install the required files for SQLite and Dapper into your solution along with any dependencies that they might have.

Connecting to the Database

The conection strings used in SQLite are pretty straight forward seeing that it is a server-less database engine and for this project is as simple as Data Source=FilePath.sqlite;Version=3;. The connection does support additional parameters like credentials, but that is beyond the scope of this tutorial.

To connect to our database (and create it, if missing) add a CreateAndOpenDb()method with the following body:

private static SQLiteConnection _dbConnection;
...
static void CreateAndOpenDb()
{
    var dbFilePath = "./TestDb.sqlite";
    if (!File.Exists(dbFilePath))
    {
        SQLiteConnection.CreateFile(dbFilePath);
    }
    _dbConnection = new SQLiteConnection(string.Format(
        "Data Source={0};Version=3;", dbFilePath));
    _dbConnection.Open();
}

Quickly running the code confirms that I am able to create and connect to my new database.

Extension methods

I would suggest creating some extension/helper methods for common SQLitetasks to help speed up development, and will start off by creating a new SQLiteExtensions class as listed below:

public static void ExecuteNonQuery(
    this SQLiteConnection connection,
    string commandText)
{
    // Ensure we have a connection
    if (connection == null)
    {
        throw new NullReferenceException("Please provide a connection");
    }

    // Ensure that the connection state is Open
    if (connection.State != ConnectionState.Open)
    {
        connection.Open();
    }

    // Use Dapper to execute the given query
    connection.Execute(commandText);
}

Initial Database Seeding

After creating the DB you will need to create all the table and objects that your application needs in order to run, I would suggest creating a seed method that gets called when the database is first created. Below is the contents of my SeedDatabase() method.

static void SeedDatabase()
{
    // Create a Users table
    _dbConnection.ExecuteNonQuery(@"
        CREATE TABLE IF NOT EXISTS [Users] (
            [Id] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
            [Username] NVARCHAR(64) NOT NULL,
            [Email] NVARCHAR(128) NOT NULL,
            [Password] NVARCHAR(128) NOT NULL,
            [DateCreated] TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )");

    // Insert an ADMIN user
    _dbConnection.ExecuteNonQuery(@"
        INSERT INTO Users
            (Username, Email, Password)
        VALUES
            ('admin', 'niemand.richard@gmail.com', 'test')");
}

This will create and seed a Users table with an admin user.

2015-04-24-004

Simple User model

Next we will create a User class to represent an entry in the Users table. This class should have a property per column you wish to interact with from the target database table.

public class User
{
    public int Id { get; set; }
    public string Username { get; set; }
    public string Email { get; set; }
    public string Password { get; set; }
    public DateTime DateCreated { get; set; }
}

We will now make use of Dappers Query<T> method to map our admin user to the newly created User class.

var adminUser = _dbConnection.Query<User>(
    "SELECT * FROM Users WHERE Username = 'admin'");

Which results in the user being mapped like so:

2015-04-24-005

Dapper is amazing!

Creating a new user

Now it’s time to add a new user to the database, but only if the user doesn’t already exist. In order to do this I will need to preform the following actions:

  • Create a new User object to save to the database
  • Check to see that the user doesn’t already exist
  • Insert the user into the database

I will need to modify my ExecuteNonQuery() method a bit to make things easier.

public static void ExecuteNonQuery(
    this SQLiteConnection connection,
    string commandText,
    object param = null)
{
    // Ensure we have a connection
    if (connection == null)
    {
        throw new NullReferenceException(
            "Please provide a connection");
    }

    // Ensure that the connection state is Open
    if (connection.State != ConnectionState.Open)
    {
        connection.Open();
    }

    // Use Dapper to execute the given query
    connection.Execute(commandText, param);
}

This now allows for an optional object to be passed into the method, which is used by Dapper to replace any @parameter placeholders in the given command text.

To help make working with Users easier I added a new UserExtensions class to the project to house all database/user related code.

public static class UserExtensions
{
    public static void SaveAsNewUser(
        this User user,
        SQLiteConnection connection)
    {
        connection.ExecuteNonQuery(@"
            INSERT INTO Users (Username, Email, Password)
            VALUES (@Username, @Email, @Password)",
            user);
    }

    public static bool ExistsInDb(
        this User user,
        SQLiteConnection connection)
    {
        var rows = connection.Query(string.Format(
            "SELECT COUNT(1) as 'Count' FROM Users WHERE Username = '{0}'",
            user.Username));

        return (int) rows.First().Count > 0;
    }
}

This now allows me to add a CreateSecondUser() method to my Program class with the following body:

static void CreateSecondUser()
{
    var secondUser = new User
    {
        Username = "rachel",
        Email = "1@2.com",
        Password = "password"
    };

    if (!secondUser.ExistsInDb(_dbConnection))
    {
        secondUser.SaveAsNewUser(_dbConnection);
    }
}

This approach allows for more readable code, and helps a lot with code refactoring, as all of your management code is in one single place. After compiling and running the code I now have my second Rachel user.

2015-04-24-006.PNG

Updating a user

I now add a GetUserByName() and SaveChanges() method to UserExtensions, allowing me to easily save any changes I make to a user back to the database.

public static User GetUserByName(
    this SQLiteConnection connection,
    string username)
{
    var userCollection = connection.Query<User>(
        "SELECT * FROM Users WHERE Username = @UserName",
        new {Username = username});

    return userCollection.FirstOrDefault();
}

public static void SaveChanges(
    this User user,
    SQLiteConnection connection)
{
    connection.ExecuteNonQuery(@"
        UPDATE Users
        SET
        Email = @Email, Password = @Password
        WHERE Id = @Id", user);
}

Next I add a ModifyAdminUser() method to Program with the following body.

static void ModifyAdminUser()
{
    var adminUser = _dbConnection.GetUserByName("admin");
    adminUser.Password = string.Format(
        "pass_{0}", DateTime.Now.Millisecond);
    adminUser.SaveChanges(_dbConnection);
}

Compiling and running the project results in my admin users password being updated.

2015-04-24-007

More, more, more users

I add a AddMoreUsers() method to Program to allow me to add user accounts easily. This method takes in a number of users to add and makes use of a RandomString() method I found online to help generate some unique usernames.

static void AddMoreUsers(int amount)
{
    var baseUsername = string.Format(
        "{0}{1}{2}{3}{4}{5}",
        DateTime.Now.Year,
        DateTime.Now.Month.ToString().PadLeft(2, '0'),
        DateTime.Now.Day.ToString().PadLeft(2, '0'),
        DateTime.Now.Hour.ToString().PadLeft(2, '0'),
        DateTime.Now.Minute.ToString().PadLeft(2, '0'),
        DateTime.Now.Second.ToString().PadLeft(2, '0'));

    for (var i = 0; i < amount; i++)
    {
        var tempUser = new User
        {
            Username = string.Format("{0}{1}",
                baseUsername, RandomString(4)),
            Password = RandomString(10),
            Email = string.Format("{0}@{1}.com",
                RandomString(12), RandomString(5))
        };

        tempUser.SaveAsNewUser(_dbConnection);
    }
}

Now I have loads of user accounts to mess around with.

2015-04-24-008

Removing a User

The last thing I would like to cover is deleting users from the DB. To do this I added the GetLastUser() and RemoveFromDb() methods to the UserExtensions class with the following body.

public static User GetLastUser(
    this SQLiteConnection connection)
{
    var userCollection = connection.Query<User>(
        "SELECT * FROM Users ORDER BY Id DESC LIMIT 1");
    return userCollection.FirstOrDefault();
}

public static void RemoveFromDb(
    this User user,
    SQLiteConnection connection)
{
    connection.ExecuteNonQuery(
        "DELETE FROM Users WHERE Id = @Id", user);
}

These methods allow me to add a new RemoveLastNonAdminUser() method to the Program class with the following body.

static void RemoveLastNonAdminUser()
{
    var user = _dbConnection.GetLastUser();
    if (user.Username == "admin") return;
    user.RemoveFromDb(_dbConnection);
}

Each time this method is run the last user in the database is dropped.

2015-04-24-009

After another run of RemoveLastNonAdminUser()

2015-04-24-010.PNG

Source Code

I have added the source code for this project on Github Blog.Demos.SQLite-Dapper (version 1.0.0) should you wish to see it all in action.

I welcome feedback and pull requests and am always open to any suggestions you may have.

I hope that you found this informative.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s