Hi everyone in this article I’m explaining about Syncing offline Database with Online Database.

Description:

In my previous article we have implemented crud operation on HTML5 and IndexedDB database to provide offline support. The next step is to synchronize offline and online databases. In this article, we are going to implement synchronization of IndexedDB and SQL Server databases using ASP.NET Web API.

Server DB Structure:

Here CustomerID is primary key and Email is unique key.

Web API:

    1.       Create an empty ASP.NET MVC Project.

   2.       Add “ADO.NET Entity Data Model” give the name CustomerModel.edmx and add Customer table.

   3.       Add controller “ServiceController” and select template “Empty API Controller”.

   4.       To get the updated data from server, we will pass revision parameter and Get action will return all the updated data after the revision.

ServiceController:

publicdynamic Get(int revision)
        {
            using (KAMLAKAREntities1 context = newKAMLAKAREntities1())
            {
                int currentRevision = context.Customers.Max(x => x.Revision) ?? 0;
                if (revision == -1)
                {
                    returnnew
                    {
                        Revision = currentRevision,
                        Customers = context.Customers.Select(x => new
                        {
                            CustomerID = x.CustomerID,
                            Name = x.Name,
                            Email = x.Email,
                            Phone = x.Phone,
                            Revision = x.Revision ?? 0,
                            IsDeleted = x.IsDeleted ?? false
 
                        }).ToList()
                    };
                }
                elseif (revision == currentRevision)
                {
                    returnnew { Revision = currentRevision };
                }
                else
                {
                    returnnew
                    {
                        Revision = currentRevision,
                        Customers = context.Customers.Where(x => x.Revision > revision).Select(x => new
                        {
                            CustomerID = x.CustomerID,
                            Name = x.Name,
                            Email = x.Email,
                            Phone = x.Phone,
                            Revision = x.Revision,
                            IsDeleted = x.IsDeleted ?? false
                        }).ToList()
                    };
                }
            }
        }

If there is no change in data then only revision is returned. On client side, we will check if returned revision is equal to sent revision then display no change message to the user.

5. We are using unique Email criteria for saving data means record is updated if email already exists else it is inserted.

privatereadonlyobject _updatePointsLock = newobject();
        publicdynamic Post(JObject data)
        {
            dynamic json = data;
            int revision = json.revision;
            int appID = json.appID;
            IList<Customer> customers = ((JArray)json.customers).Select(t => newCustomer
            {
                CustomerID = ((dynamic)t).CustomerID ??-1,
                Name = ((dynamic)t).Name,
                Email = ((dynamic)t).Email,
                Phone = ((dynamic)t).Phone,
                Revision = ((dynamic)t).Revision,
                IsDeleted = ((dynamic)t).IsDeleted ?? false
            }).ToList(); ;
 
            lock (_updatePointsLock)
            {
                using (KAMLAKAREntities1 context = newKAMLAKAREntities1())
                {
                    int currentRevision =context.Customers.Max(x => x.Revision) ?? 0;
                    //check version
                    if (currentRevision ==revision)
                    {
                        foreach (Customer cust in customers)
                        {
                            Customer obj =context.Customers.Where(x => x.Email == cust.Email).FirstOrDefault();
                            if (obj == null)
                            {
                                cust.Revision =currentRevision + 1;
                                 cust.LastModifiedDate = DateTime.Now.ToString();
                                 cust.LastModifiedBy = appID.ToString();
                                 context.Customers.Add(cust);
                            }
                            else
                            {
                                obj.Name =cust.Name;
                                obj.Email = cust.Email;
                                obj.Phone =cust.Phone;
                                obj.IsDeleted =cust.IsDeleted;
                                obj.Revision =currentRevision + 1;
                                obj.LastModifiedDate= DateTime.Now.ToString();                                 obj.LastModifiedBy = appID.ToString();
 
                            }
 
                        }
                        context.SaveChanges();
                        returnnew
                        {
                            Revision =currentRevision + 1,
                            Customers =context.Customers.Where(x => x.Revision > revision).Select(x => new
                            {
                                CustomerID =x.CustomerID,
                                Name = x.Name,
                                Email =x.Email,
                                Phone =x.Phone,
                                Revision =x.Revision,
                                IsDeleted = x.IsDeleted ?? false
                            }).ToList()
                        };
                    }
                    else
                    {
                        returnnew { Revision = revision };
                    }
                }
            }
 
        }

For simplicity, we are taking same app to consume web api.

6. Add jQuery, jQuery UI and Modernizr in the project. you can easily install from NuGet.

7. Install Linq2IndexedDB, run the following command in the Package Manager Console:

Install-Package Linq2IndexedDB

8. Controllers > Add > Controller > Select Template “Empty MVC Controller” and give name “HomeController” > ADD.

9. Right Click on Index method > Add View > index > Add

10. Copy HTML and JS code from my previous article and put HTML mark-up in view, Create new Customers.js file and add copied JS code. Here we are adding two more buttons for 2 way synchronization.

<buttonid="btnSyncLocal">
        Sync Local DB from Server DB</button>
    <buttonid="btnSyncServer">
        Sync Server DB from Local DB</button>

To sync local DB from Server DB:

//to Sync local db from server db
 
$('#btnSyncLocal').click(function () {
    $.ajax({
        url: 'api/service?revision=' + localStorage.customerRevision,
        type: 'GET',
        dataType: 'json',
        success: function (data) {
            if (data.Revision == localStorage.customerRevision) {
                alert('You are already working on the latest version.');
            }
            else {
                syncData(data);
            }
        }
    });
});

To sync server DB from Local DB:

//to Sync server db from local db
 
$('#btnSyncServer').click(function () {
    var customers = [];
    db.linq.from(config.objectStoreName).select().then(function () {
        if (customers.length > 0) {
            var postData = { revision:parseInt(localStorage.customerRevision, 10), appID: config.appID, customers:customers };
            $.ajax({
                url: 'api/service',
                type: 'POST',
                dataType: 'json',
                contentType: "application/json",
                data: JSON.stringify(postData),
                success: function (data) {
                    if (data.Revision ==localStorage.customerRevision) {
                        alert('There is newer version on the server. Please Sync from server first.');
                    }
                    else {
                        syncData(data);
                    }
                }
            });
        }
        else {
            alert('There is no change in data after your last synchronization.');
        }
    }, handleError, function (data) {
        if (data.Revision == -1) {
            customers.push(data);
        }
    });
});

Here syncData method is used to update local DB data and draw UI.

function syncData(data) { 
    var emails = []; 
    db.linq.from(config.objectStoreName).select(["Email"]).then(function () { 
        $.each(data.Customers, function () { 
            if ($.inArray(this.Email, emails) > -1) { 
                //update 
                db.linq.from(config.objectStoreName).update(this).then(function (data) { 
                }, handleError); 
            } 
            else { 
                //insert 
                db.linq.from(config.objectStoreName).insert(this).then(function (data) { 
                }, handleError); 
            } 
        }); 
        //Rebind Grid 
        $('#tblCustomer').remove(); 
        InitializeData(); 
        localStorage.customerRevision = data.Revision; 
        alert('The synchronization has been completed successfully.'); 
    }, handleError, function (data) { 
        emails.push(data.Email); 
    }); 
}

Output:

Consider request flow from left to right in the above diagram.
1. Suppose server DB and Client A are initially in sync with Revision 3 and have P and Q records. On client side, revision is stored using localstorage.
2. Now Client A modifies Q details then for Q record: Revision = -1.
3. Client A adds new record R then for R record: Revision = -1 and CustomerID = -1
4. Client A clicks on “Sync Server DB from LocalDB” button then All Revision = -1 of data are passed to web api with the Revision = 3(DB revision comes from localstorage).
5. On server, it compares revision with its own revision. Both are equal to 3, So, it goes for saving data.
6. On server, email is checked for individual record. The record is inserted for new email and updated for existing emails with incremented revision = 4.
7. The server gives new revision with modified records response to the client.
8. Client checks the new revision if it is higher than existing one then it updates local data and UI, sets new revision(4) in localstorage.

9. Suppose another Client B adds new record S first time so Revision =-1

10. Now Client B clicks on “Sync Server DB from LocalDB” button then All Revision = -1 of data(only S in this case) are passed to web api with the Revision = -1

11. Server compares revision and it is not same (Server Revision = 4, Client Revision = -1) so it gives same revision(-1) as response without modifying data.

12. Client checks the response revision with its own revision, it is same so it alerts user to update data from server first.

13. Now client B clicks on “Sync Client DB from Server DB” button then server checks revision. if it is -1 then gives all data else gives the data after the revision as response. It also gives Revision number in response.

14. Client B checks revision if it is same then gives “no change” message to user. But in our case it is not same so updates local DB data and UI, revision in localstorage also.

15. Now Client B clicks on “Sync Server DB from LocalDB” button then All Revision = -1 of data(only S in this case) are passed to web api with the Revision = 4

16. Server compares revision and adds S record with Revision =5 and gives data and Revision as response.

17. Client B checks the new revision if it is higher than existing one(5 > 4) then it updates local data and UI, sets new revision(5) in localstorage.

18. Now If Client A clicks “Sync Client DB from Server DB“, it is updated with Revision 5.

What about delete?

Suppose Client A deletes record Q since it is already saved in database so it is marked as IsDeleted =1 in Local DB. On “Sync Server DB from LocalDB”, it is updated on server DB also. All IsDeleted=1 are not displayed in the grid, so When Client B syncs, the Q will be updated with IsDeleted = 1 and disappeared from the grid.

Note: If Client A adds Q again then the existing deleted Q will be updated with IsDeleted = 0 means when any exising email is inserted, the record will be updated automatically. You can change it as per your requirement. you can give alert message if any existing email is inserted depends on your requirement. 

in my next post i'll discuss about Scalable Vector Graphics (svg) in HTML5