Sergey Maskalik

Sergey Maskalik's blog

In the pursuit of mastery

Summary

SQLite is one of the little databases that you can use in your project when you don’t want to have a full blown database and want something simple, quick and awesome. It’s very easy to distribute or share with your team since it’s just a single file and can be checked into source control. Could be used as a development database, one off database for your desktop applications or just about anything else. It’s definitely nice to keep in your development tool belt.

Installation

There is none, just add reference to SQLite C# drivers and it will create a database for your on first run. Simplest way is to go to Nuget package manager console and run:

Install-Package System.Data.SQLite

This will install both versions for x86 and x64 so you can build it for any CPU. There are also specific versions as well in case if you don’t want extra files Install-Package System.Data.SQLite.x86 or System.Data.SQLite.x64.

Model

In our sample we’ll save and retrieve customers from the database so we’ll create a folder “Model” and add a Customer class which will represent our data model.

namespace SQLiteDemo.Model
{
    public class Customer
    {
        public int Id { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public DateTime DateOfBirth { get; set; }
    }
}

Simple Data Layer

For this demo I’ll use a console application and for simplicity sake I’ll create a folder “Data” for data access layer. In you production application you will most likely want to put your data access layer into a separate project.

We’ll also use a repository pattern for our Data Access Layer(DAL) since it’s the most commonly used and easily to understand.

We’ll add an interface ICustomerRepository which will have two methods to save and retrive customers and create a base class SqLiteBaseRepository which will handle common logic between our repositories like creating database connections.

public interface ICustomerRepository
{
    Customer GetCustomer(long id);
    void SaveCustomer(Customer customer);
}

public class SqLiteBaseRepository
{
    public static string DbFile
    {
        get { return Environment.CurrentDirectory + "\\SimpleDb.sqlite"; }
    }

    public static SQLiteConnection SimpleDbConnection()
    {
        return new SQLiteConnection("Data Source=" + DbFile);
    }
}

Using Dapper as our simple Object-relational mapping (ORM)

Dapper is easy to use, fast and fits well for our simple data access layer. It has a simple API on top of already familiar SQL statements.

In your package manager console

Install-Package Dapper

In Customer Repository implementation (SqlLiteCustomerRepository) “SaveCustomer” method will first check if database file already exists and execute a create table script which if nothing is found, it will output a file to the environment runtime directory where your executable is located.

public void SaveCustomer(Customer customer)
{
    if (!File.Exists(DbFile))
    {
        CreateDatabase();
    }

    using (var cnn = SimpleDbConnection())
    {
        cnn.Open();
        customer.Id = cnn.Query<long>(
            @"INSERT INTO Customer 
            ( FirstName, LastName, DateOfBirth ) VALUES 
            ( @FirstName, @LastName, @DateOfBirth );
            select last_insert_rowid()", customer).First();
    }
}

private static void CreateDatabase()
{
    using (var cnn = SimpleDbConnection())
    {
        cnn.Open();
        cnn.Execute(
            @"create table Customer
              (
                 ID                                  integer identity primary key AUTOINCREMENT,
                 FirstName                           varchar(100) not null,
                 LastName                            varchar(100) not null,
                 DateOfBirth                         datetime not null
              )");
    }
}

Above Dapper maps our object to table field names in the query and also returns a long datatype with a newly inserted scoped identity. With SqLite scope identity has a different method last_insert_rowid() and we use that to get inserted customer id and assign it to our customer.

To retrieve customer we first check if database file exists first and then let dapper handle the rest.

public Customer GetCustomer(long id)
{
    if (!File.Exists(DbFile)) return null;

    using (var cnn = SimpleDbConnection())
    {
        cnn.Open();
        Customer result = cnn.Query<Customer>(
            @"SELECT Id, FirstName, LastName, DateOfBirth
            FROM Customer
            WHERE Id = @id", new { id }).FirstOrDefault();
        return result;
    }
}

And there you have it, an easy way to have a single file database with a simple ORM in your repository layer.

Source code

Here is the link to the source code if you would like to run the solution and try it for yourself. Source Code

If you are working with a heartbeat or keep alive from client side that goes between protocols (http to https) you will soon discover that it’s not as straightforward as simply sending an ajax request due to cross domain browser policy restrictions. So if your https version of the site has a secure cookie when you send an ajax request from unsecure page your request won’t have any cookies in the header since browser blocks this communications.

One solution that I though was quickest and most effective was to replace Ajax call with creating of a hidden iframe that requests a page which then refreshes your secure session. Since we don’t need any data and this is a fire and forget situation we don’t really care about any communication from secure iframe to the unsecure page.

Below is the sample javascript that checks if the user has been active on the page (tracking hovering over links and textboxes and then creating a invisible iframe and attaching it to the body of the DOM.

var SM = SM || {};
SM.keepAlive = function () {
    var aliveUrl = 'https://blog.maskalik.com/keep-me-alive.aspx',
        interval = 60 * 1000 * 10,
        that = {},
        alive = false,
        timer = {};

    that.isActive = function () {
        alive = true;
    };

    that.removeTrackingIframeIfPresent = function () {
        var frame = document.getElementById('keep-alive-iframe');
        if (frame) {
            document.body.removeChild(frame);
        }
    };
    that.sendKeepAlive = function () {
        if (alive) {
            that.removeTrackingIframeIfPresent();
            var frame = document.createElement('iframe');
            frame.setAttribute('src', aliveUrl);
            frame.setAttribute('id', 'keep-alive-iframe');
            frame.setAttribute('style', 'display:none');
            document.body.appendChild(frame);

            alive = false;
        }
    };

    that.start = function () {
        var allTextareas = $('textarea');
        allTextareas.live('keyup', that.isActive);

        var allLinks = $('a');
        allLinks.live('click mouseover', that.isActive);

        timer = setInterval(that.sendKeepAlive, interval);
    };

    return that;
};

And you start it on your page load or when you need it.

var alive = SM.keepAlive();
alive.start();

Also don’t forget that your requests can also be cached by the browser so you keep alive could just be served from browser cache without extending your session. To fix that we can set HttpResponse to send no cache headers.

Response.Cache.SetNoStore();
Response.Cache.AppendCacheExtension("no-cache");

And there you have it a simple solution to a somewhat common problem.

WebMethods are still very convenient when you are working with WebForms and need a quick way to return json to your client javascript application.

There is one caveat with webmethod arguments they cannot be nullable and you cannot make them optional. For example, method below would still expect limit argument and if you don’t pass anything it will return an error.

$.ajax({
    type: 'POST',
    url: 'Default.aspx/TestNullableArgument',
    **data: '{}',**
    contentType: 'application/json; charset=utf-8',
    dataType: 'json',
    success: function (msg) {
        callback(msg.d);
    }
});

[WebMethod]
public static bool TestNullableArgument(int? limit)
{
    return true;
}

Will result in:

Invalid web service call, missing value for parameter

One way we can fix is to add send a null parameter like this:

$.ajax({
    type: 'POST',
    url: 'Default.aspx/TestNullableArgument',
    **data: '{"limit":null}',**
    contentType: 'application/json; charset=utf-8',
    dataType: 'json',
    success: function (msg) {
        callback(msg.d);
    }
});

However it still does not make int? limit argument optional and webmethod would always expect to have that parameter. A nice pattern if you need optional WebMethod arguements is to send argument data as serialized json object string and then de-serialize it in your webmethod.

For example we have a grid that needs to be searched, sorted and paged. However you don’t always want to send all those parameters as null. A lot of times you want it paged but not sorted or filtered and you don’t want to duplicate the code and overload all possible webmethod arguments. So by sending json string we create a dynamic parameters object which can have optional properties.

First we define our query object:

public class GridQuery
{
    public int? Limit { get; set; }
    public int? Offset { get; set; }
    public string FilterType { get; set; }
    public string FilterTypeValue { get; set; }
    public DateTime? Start { get; set; }
    public DateTime? End { get; set; }
}

Modify our WebMethod to accept string gridQuery argument:

[WebMethod]
public static Result GetGridData(string gridQuery)
{
    var query = JsonConvert.DeserializeObject<GridQuery>(gridQuery);
    ...
}

Now in our client javascript we can create a query object without having to specify all other options. And then send it as a serilized json parameter

var params = { limit: 10, offset: 20 }

$.ajax({
    type: 'POST',
    url: 'Default.aspx/GetGridData',
    **data: "{'gridQuery':'" + JSON.stringify(params) + "'}",**
    contentType: 'application/json; charset=utf-8',
    dataType: 'json',
    success: function (msg) {
        console.log(msg.d);
    }
});

Now when our object gets deserialized we have optional arguments. Simple and effective.