In my previous tutorial, we have implemented CRUD operations on HTML5 IndexedDB database to provide offline support. The next step is to synchronize offline and online databases. In this tutorial, 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 also unique field.
Web API:
1. Create an Empty ASP.NET MVC Project.
2. Add "ADO.NET Entity Data Model" say CustomerModel.edmx and add Customer table.
3. Right click Controllers folder > Add > Controller > Select Template "Empty API Controller" and give name "ServiceController" > Add
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.
public dynamic Get(int revision)
{
using (DBCustomerEntities context = new DBCustomerEntities())
{
int currentRevision = context.Customers.Max(x => x.Revision) ?? 0;
if (revision == -1)
{
return new
{
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()
};
}
else if (revision == currentRevision)
{
return new { Revision = currentRevision };
}
else
{
return new
{
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.
private readonly object _updatePointsLock = new object();
public dynamic Post(JObject data)
{
dynamic json = data;
int revision = json.revision;
int appID = json.appID;
IList<Customer> customers = ((JArray)json.customers).Select(t => new Customer
{
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 (DBCustomerEntities context = new DBCustomerEntities())
{
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;
cust.LastModifiedBy = appID;
context.Customers.AddObject(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;
obj.LastModifiedBy = appID;
}
}
context.SaveChanges();
return new
{
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
{
return new { Revision = revision };
}
}
}
}
View:
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 > clear "use a layout or master page" option > 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.
<button id="btnAddCustomer">
Add Customer</button>
<button id="btnDeleteDB">
Clear Local DB</button>
<button id="btnSyncLocal">
Sync Local DB from Server DB</button>
<button id="btnSyncServer">
Sync Server DB from Local DB</button>
11. 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);
}
}
});
});
12. 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);
});
}
Data Synchronization:
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.
You can download source code from Github.
Hope you like it. If you enjoy this post, Don't forget to share it with your friends.