Search code examples
asp.net-mvcofflineisolatedstorageoffline-caching

ASP.NET MVC application with SQLLite as offline storage

I am developing a web application using asp.net MVC. I need to be able to bring the entire application offline upon user's demand via HTML5.

Though I can create manifest and have the files such as CSS/JS/image files offline. I am not sure how to have database connectivity offline (something like SQLLite I suppose?)

I need this supported in IE 8+, Safari (IOS) and chrome (android).

I cannot use just local storage as there are a lot of look up values that need to be made available when browsing the application offline. This means that lot of data needs to be stored offline by the approval of the user.

Any thoughts?

Solution

  • For the IE8+, Safari, & Chrome support, you should go with SequelSphere: www.SequelSphere.com

    SequelSphere is the only relational database product that has truly broad browser and platform support. It also works "offline" by using the application cache manifest, and will store its data in IndexedDB or LocalStorage (in that order based on availability).

    As for WebSQL (SQLLite), it isn't supported in IE and Firefox: WebSQL Usage by Browser

    Concerning SequelSphere, creating tables couldn't be easier:

    db.catalog.createTable({
    tableName: "EMPL",
    columns: [ "EMPL_ID", "NAME", "AGE", "DEPT_ID" ],
    primaryKey: [ "EMPL_ID" ],
    data: [
        [0,"Bob",32,0],
        [1,"John",37,2],
        [2,"Fred",28,1]
    ]
});

    Querying the data is easy:

    var res = db.query("SELECT name, age, dept_id FROM empl WHERE Dept_ID = 2");

    Inserting / Updating / Deleteing data is easy:

    db.insertRow("empl", [3, "Sue", 26, 2]);
db.updateRow("empl", [3, "Suzy", 26, 2]);
db.deleteRow("empl", [3, "Suzy", 26, 2]);

    One thing to be aware of: Since this will be used in an offline application, make sure you set the "Persistence Scope" of either the entire Catalog, or each Table to be "SCOPE_LOCAL":

    db.catalog.setPersistenceScope(db.SCOPE_LOCAL);
//  -or-
db.catalog.getTable("empl").setPersistenceScope(db.SCOPE_LOCAL);

    If you have any questions, just email the support for SequelSphere.

    For complete transparency, I am part of SequelSphere, and it does seem to answer your question very well... ;)