Nov 08, 2012

Offline Add, Edit, Delete Data in HTML5 IndexedDB

You can use HTML5 web storage, IndexedDB or File access API in your app to provide offline support. It allows user to work offline when the network isn't available. An IndexedDB is a persistent data store in the browser means a database on the client side. It allows you to create app with rich query abilities in both offline and online mode. If you are beginner with IndexedDB, you should first read Using IndexedDB.

In this tutorial, we will implement add, edit, delete operations on IndexedDB using Linq2IndexedDB library. Being a .NET guy, I am fan of LINQ, this library provides a way to use LINQ type syntax to perform operations on IndexedDB. It saves bunch of time and code. We are going to implement following thing to save customer data:

HTML5 IndexedDB

HTML Structure:

1. Add jQuery, jQuery UI and Modernizer references in the page.

2. Download Linq2IndexedDB library and add indexeddb.shim.js and Linq2IndexedDB.js files.


<link rel="stylesheet" href="http://ajax.googleapis.com/ajax/libs/jqueryui/1.8.9/themes/base/jquery-ui.css" type="text/css" media="all" />
<script src="//ajax.googleapis.com/ajax/libs/jquery/1.7.1/jquery.min.js"></script>
<script src="http://ajax.googleapis.com/ajax/libs/jqueryui/1.8.9/jquery-ui.min.js" type="text/javascript"></script>
<script type="text/javascript" src="Scripts/modernizr-2.6.2.js"> </script>
<script type="text/javascript" src="Scripts/indexeddb.shim.js"> </script>
<script type="text/javascript" src="Scripts/Linq2IndexedDB.js"> </script>

in body tag, we have following structure:


<div style="width:100%;text-align:center;clear:both;padding-top:10px">
    <div id="contentHolder" style="margin:0 auto;width:425px">
    </div>
    <button id="btnAddCustomer">
        Add Customer</button>
    <button id="btnDeleteDB">
        Clear Local DB</button>
    <div id="dialog-form" title="Add new customer">
        <form>
        <fieldset>
            <label for="Id" id="lblId" class="invisible">
                ID</label>
            <input type="number" name="Id" id="txtId" value="" class="text ui-widget-content ui-corner-all invisible" />
            <label for="Name" id="lblName">
                Name</label>
            <input type="text" name="Name" id="txtName" class="text ui-widget-content ui-corner-all" />
            <label for="Email" id="lblEmail">
                Email</label>
            <input type="email" name="Email" id="txtEmail" value="" class="text ui-widget-content ui-corner-all" />
            <label for="Phone" id="lblPhone">
                Phone</label>
            <input type="tel" name="Phone" id="txtPhone" value="" class="text ui-widget-content ui-corner-all" />
        </fieldset>
        </form>
    </div>
</div>

We'll generate a grid dynamically in contentHolder. btnDeleteDB is used to clear local IndexedDB.

Configure the Database:

The first logical step is to configure database and create instance of the linq2indexeddb object:


var config = {
    dbName: 'CustomerDB',
    version: 1,
    objectStoreName: 'Customers',
    appID: 123
};

var databaseDefinition = [{
    version: config.version,
    objectStores: [{ name: config.objectStoreName, objectStoreOptions: { autoIncrement: false, keyPath: "Email"}}],
    indexes: [{ objectStoreName: config.objectStoreName, propertyName: "Email", indexOptions: { unique: true, multirow: false}}]
}];

var dbConfig = {
    version: config.version,
    definition: databaseDefinition
};

if (!localStorage.customerRevision) {
    localStorage.customerRevision = -1;
}

var db = window.linq2indexedDB(config.dbName, dbConfig, false);

Here we define database name CustomerDB and ObjectStore name Customers.

Check documentation for more about database configuration parameters. Here localStorage is used to save current revision of database.

Load Gridview:


   db.initialize().then(function () {
        InitializeData();
    }, handleError);

	function InitializeData() {
    var content = $('#contentHolder');
    var table = $('<table id="tblCustomer" class="ui-widget ui-widget-content"></table>');
    content.append(table);
    var thead = $('<thead></thead>');
    table.append(thead);
    var tr = $('<tr class="ui-widget-header"></tr>');
    thead.append(tr);
    tr.append('<th>Name</th><th>Email</th><th>Phone</th><th>Actions</th>');
    var tbody = $('<tbody id="customer-data"></tbody>');
    table.append(tbody);

    $('#customer-data').empty();
    db.linq.from(config.objectStoreName).where("IsDeleted").equals("0").orderBy("Email").select().then(function () {
    }, handleError, function (sdata) {
        showCustomer(sdata);
    });

}
	
	function showCustomer(data) {
    var row = $('tr[value="' + data.Email + '"]');
    if (row.length > 0) {
        row.empty();
    } else {
        row = $('<tr value="' + data.Email + '">');
    }
    row.append('<td>' + data.Name + '</td><td>' + data.Email + '</td><td>' + data.Phone + '</td>');
    var upd = $('<button type="button" value="' + data.Email + '">Update</button>');
    upd.button().click(function () {
        getCustomer(this);
    });

    var del = $('<button type="button" value="' + data.Email + '">Delete</button>');
    del.button().click(function () {
        deleteCustomer(this);
    });

    var col = $('<td></td>');
    col.append(upd);
    col.append(del);
    row.append(col);
    $('#customer-data').append(row);
}

function handleError(error) {
    alert(error);
}

InitializeData: To draw table structure

showCustomer: to add customer details in rows with update and delete buttons.

Add Customer:


	
	 $('#btnAddCustomer').click(function () {
        $("#txtId").val(0);
        $("#txtName").val('');
        $("#txtEmail").val('');
        $("#txtEmail").removeAttr('disabled').css({ 'color': 'black' }); ;
        $("#txtPhone").val('');
        $("#dialog-form").dialog("open");
    });
	
	
    $("#dialog-form").dialog({
        autoOpen: false,
        height: 300,
        width: 350,
        modal: true,
        buttons: {
            "Save": function () {
                var bValid = true;

                if (bValid) {
                    var id = parseInt($("#txtId").val());
                    var customer = {};
                    if (id != 0) {
                        customer.CustomerID = id;
                    }
                    customer.Name = $("#txtName").val();
                    customer.Email = $("#txtEmail").val();
                    customer.Phone = $("#txtPhone").val();
                    customer.Revision = -1;
                    customer.IsDeleted = 0;
                    saveCustomer(customer);
                    $(this).dialog("close");
                }
            },
            Cancel: function () {
                $(this).dialog("close");
            }
        },
        close: function () {
        }
    });

When a new customer is added, It will have CustomerID = 0. the above dialog is used for both add and edit operations. when any change(add/edit) is made, the Revision property gets reset to -1. We'll use these Revision and other properties in the next post to sync with online server database.

Edit Customer:

We are using Email property as unique criteria, So user can't modify Email during editing.


function getCustomer(btn) {
    db.linq.from(config.objectStoreName).get($(btn).val()).then(InitializeUpdate, handleError);
}


function InitializeUpdate(customer) {
    $("#txtId").val(customer.CustomerID);
    $("#txtName").val(customer.Name);
    $("#txtEmail").val(customer.Email);
    $("#txtPhone").val(customer.Phone);
    $("#txtEmail").attr('disabled', 'disabled').css({ 'color': 'gray' });
    $("#dialog-form").dialog("open");
}


function saveCustomer(customer) {
    var emails = [];
    //get all localDB emails
    db.linq.from(config.objectStoreName).select(["Email"]).then(function () {
        if ((customer.CustomerID && customer.CustomerID != 0) || $.inArray(customer.Email, emails) > -1) {
            db.linq.from(config.objectStoreName).update(customer).then(function (data) {
                showCustomer(data.object);
            }, handleError);
        } else {
            customer.CustomerID = -1;
            db.linq.from(config.objectStoreName).insert(customer).then(function (data) {
                showCustomer(data.object);
            }, handleError);
        }
    }, handleError, function (data) {
        emails.push(data.Email);
    });
}

When user clicks on update button, getCustomer method is called to get data and InitializeUpdate sets the data in dialog's controls. On save click in the dialog, saveCustomer method (common in both add/ edit operations) is called. In online database, we are not deleting record, only setting IsDeleted flag. If user creates new customer with same deleted email then we have to reactivate old account and update information, that's why, first all emails are fetched and if there is valid CustomerID OR Email already exists, update operation is performed in local database else Insert is peformed after setting CustomerID =-1 means all new inserted customers will have CustomerID -1 .

Delete Customer:


/* Delete Customer*/
function deleteCustomer(btn) {

    db.linq.from(config.objectStoreName).get($(btn).val()).then(function (data) {

        if (data.CustomerID == -1) {
            //Delete local record which is not saved on server yet
            db.linq.from(config.objectStoreName).remove(data.Email).then(function () {
                $(btn).parents('tr').remove();
            }, handleError);
        }
        else {
            data.IsDeleted = 1;
            data.Revision = -1;
            db.linq.from(config.objectStoreName).update(data).then(function (data) {
                $(btn).parents('tr').remove();
            }, handleError);
        }
    }, handleError);
}

We are deleting records which are created offline(CustomerID = -1) and never deployed on the server. If any server side data is deleted, we are marking IsDeleted = 1.

Clear Database:


//Reset Local IndexedDB
 $('#btnDeleteDB').click(function () {
        db.deleteDatabase().then(function () {
            db.initialize().then(function () {
                $('#tblCustomer').remove();
                localStorage.customerRevision = -1;
                InitializeData();
            }, handleError);
        });
    });

The above method is used to clear local IndexedDB.

Enjoy HTML5 !!!