Introduction
This application is for reference and not to teach. I am sharing a small part of my code in this article for beginners like me. I would be pleased to get your suggestions or any comments.
This article tries to explain the basics about the browser databases that comes in handy when working with the HTML 5 Offline application. How can a developer develop a web application that would interact with the local databases of browsers, insert new records, modify or edit the currently existing records of a table, get all or a specific record from the table and delete the records of a table.
Background
To make our web application work offline, we require two things:
CACHE.APPCACHE
: To get all the required files (HTML pages, JavaScript files, images, etc.) on the local machine browser DATABASE
: A database to store user work locally
CACHE.APPCACHE
First point can be implemented using the cache.appcache file, a new feature of HTML 5. You can easily find some useful tutorials for this cache.appcache file, and this is very easy to implement cache using this file. For our project, we are not going to write a hard coded file, in this project, we will implement an Action Result which will be a server for this file.
DATABASE
Now this is the most important part of this article. Any application requires database to store information/records like SQL server on the server side. As we are implementing an offline application, we need any database so that we can store the information/records. For offline applications, we have two types of databases supported by different browsers.
WebSql
for Safari like browsers IndexedDB
for Mozilla, Chrome, Internet Explorer like browsers.
WebSql
: This is a query based database like SQL server. You need to write queries to insert
, update
and delete
the records like you did in SQL server. This database is used for the Safari browser.
IndexedDB
: This database works on objects, we are not required to write queries for this database. It simply works to add
, update
and delete
the objects. For this database, we will use a db.js wrapper file provided by aaron powell. We will use this database for all the browsers other than Safari.
Using the Code
Implementation for cache manifest Action
public ActionResult Manifest()
{
var manifest = "CACHE MANIFEST" + Environment.NewLine +
"# App Version: " + System.IO.File.GetLastWriteTime
(Server.MapPath("~/Views/Home/Index.cshtml")) + Environment.NewLine +
"# Server Assembly Version: " + this.GetType().Assembly.GetName().Version +
Environment.NewLine +
"NETWORK:" + Environment.NewLine +
"*" + Environment.NewLine +
"CACHE:" + Environment.NewLine +
Url.Action("Index", "Home") + Environment.NewLine +
Url.Content("~/Content/site.css") + Environment.NewLine +
Url.Content("~/Content/bootstrap.min.css") + Environment.NewLine +
Url.Content("~/scripts/jquery-1.7.1.js") + Environment.NewLine +
Url.Content("~/scripts/bootstrap.min.js") + Environment.NewLine +
Url.Content("~/scripts/bootbox.min.js") + Environment.NewLine +
Url.Content("~/scripts/db.js") + Environment.NewLine +
Url.Content("~/scripts/Config.js") + Environment.NewLine +
Url.Content("~/scripts/DbManager.js") + Environment.NewLine +
Url.Content("~/scripts/index.js") + Environment.NewLine +
Url.Content("~/scripts/jquery.blockUI.js") + Environment.NewLine +
Url.Content("~/scripts/cache.js") + Environment.NewLine;
return Content(manifest, "text/cache-manifest");
}
This Action
result has three main lines of code.
This line sets the version for the cache.appcache file. Whenever you change anything in your main view, this will force the browser to get the new files from the server.
"# App Version: " + System.IO.File.GetLastWriteTime
(Server.MapPath("~/Views/Home/Index.cshtml")) + Environment.NewLine +
To add new files, you can add the files using this line of code:
Url.Content("~/Content/site.css") + Environment.NewLine +
The return type of this Action is set of type "text/cache-manifest
".
return Content(manifest, "text/cache-manifest");
Now to add this manifest to your layout or page, you simply add this line to your page html
tag.
html manifest="@Url.Action("Manifest", "Home")"
That's it, we are done with the cache manifest code.
Implementation for Databases
Now we will have two code block sections, one for websql
and the other for indexedDb
. First of all, we are required to recognise whether it a Safari browser or not. If it is a Safari browser, we will use websql
, otherwise indexedDB
.
if (navigator.userAgent.indexOf("Safari") >= 0
&& navigator.userAgent.indexOf("Chrome") < 0) {
}else{
}
Now, we need to initialize our databases:
if (navigator.userAgent.indexOf("Safari") >= 0
&& navigator.userAgent.indexOf("Chrome") < 0) {
var DataBaseManager = {
Offlinedb: openDatabase("OfflineDB", '1', 'my first database',
2 * 1024 * 1024)
}
}else{
}
Now before going further, first we will declare a global schema object for indexedDB
, in this we have UserData
Table with UserID
as autoincrement true
.
var OfflineConfiguration = {
Db_VERSION: 1,
DB_NAME: "OfflineDB",
SCHEMA: {
UserData: { key: { keyPath: 'UserID', autoIncrement: true } }
}
};
Now we have a dataserver to store our application database, we need to define the schema for database. For WebSql
, we will define our table structure with query and for IndexedDb
, we will define the schema with objects. Our application uses a single table, i.e., UserData
and UserID
is an autoincrement identity column.
if (navigator.userAgent.indexOf("Safari") >= 0
&& navigator.userAgent.indexOf("Chrome") < 0) {
var DataBaseManager = {
Offlinedb: openDatabase("OfflineDB", '1', 'my first database', 2 * 1024 * 1024),
initializeDataBase: function () {
var self = this;
self.Offlinedb.transaction(function (tx) {
tx.executeSql('CREATE TABLE IF NOT EXISTS UserData
(UserID INTEGER PRIMARY KEY AUTOINCREMENT, Name, Email, Technology)');
});
},
}
}else{
var DataBaseManager = {
}
}
Data Operations
Now, we have our table so we can add our data operations ADD
, GET
, UPDATE
and DELETE
.
1. Add User
if (navigator.userAgent.indexOf("Safari") >= 0
&& navigator.userAgent.indexOf("Chrome") < 0) {
var DataBaseManager = {
Offlinedb: openDatabase("OfflineDB", '1', 'my first database', 2 * 1024 * 1024),
initializeDataBase: function () {
var self = this;
self.Offlinedb.transaction(function (tx) {
tx.executeSql('CREATE TABLE IF NOT EXISTS UserData
(UserID INTEGER PRIMARY KEY AUTOINCREMENT, Name, Email, Technology)');
});
},
AddnewUser: function (data, callback) {
this.initializeDataBase();
var self = this;
self.Offlinedb.transaction(function (tx) {
var query = "insert into UserData(Name,Email,Technology) values(?,?,?)";
tx.executeSql(query, [data.Name, data.Email, data.Technology],
function (tx, results) {
if (callback) callback("User Saved");
});
});
}
}
}else{
var DataBaseManager = {
AddnewUser: function (data, callback) {
db.open({
server: OfflineConfiguration.DB_NAME,
version: OfflineConfiguration.Db_VERSION,
schema: OfflineConfiguration.SCHEMA
}).done(function (s) {
self.Server = s;
self.Server.UserData.add(data).done(function (results) {
if (callback) callback("Data added into UserData");
});
});
},
}
}
2. Get User
if (navigator.userAgent.indexOf("Safari") >=0
&& navigator.userAgent.indexOf("Chrome") < 0) {
var DataBaseManager = {
.....
GetSingleUser: function (data, callback) {
try {
var self = this;
this.initializeDataBase();
var query1 = "SELECT * from UserData where UserID=" + data;
self.Offlinedb.transaction(function (tx) {
tx.executeSql(query1, [], function (tx, results) {
if (results.rows.length > 0) {
var v = results.rows.item(0);
if (callback) callback(results.rows.item(0));
} else {
if (callback) callback("Not Found");
}
});
});
}
catch (e) {
console.log(" error occurred in selecting data");
}
},
....
}
}else{
var DataBaseManager = {
....
GetSingleUser: function (data, callback) {
db.open({
server: OfflineConfiguration.DB_NAME,
version: OfflineConfiguration.Db_VERSION,
schema: OfflineConfiguration.SCHEMA
}).done(function (s) {
self.Server = s
self.Server.UserData
.query()
.all()
.filter(function (result) {
return result.UserID === parseInt(data);
})
.execute()
.done(function (finalResult) {
if (callback) callback(finalResult);
});
});
},
....
}
}
3. Update User
if (navigator.userAgent.indexOf("Safari") >=0
&& navigator.userAgent.indexOf("Chrome") < 0) {
var DataBaseManager = {
.....
UpdateUser: function (data, callback) {
try {
this.initializeDataBase();
var self = this;
var query1 = "update UserData set Name=?,Email=?,Technology=? where UserID=?";
self.Offlinedb.transaction(function (tx) {
tx.executeSql(query1, [data.Name, data.Email,
data.Technology, parseInt(data.UserID)], function (tx, results) {
if (callback) callback("Response updated");
});
});
}
catch (e) {
console.log(" error occurred in selecting data");
}
},
....
}
}else{
var DataBaseManager = {
....
UpdateUser: function (data, callback) {
db.open({
server: OfflineConfiguration.DB_NAME,
version: OfflineConfiguration.Db_VERSION,
schema: OfflineConfiguration.SCHEMA
}).done(function (s) {
self.Server = s
self.Server.UserData.update(data).done(function (item) {
if (callback) callback("response updated to database");
});
});
},
....
}
}
4. Delete User
if (navigator.userAgent.indexOf("Safari") >=0
&& navigator.userAgent.indexOf("Chrome") < 0) {
var DataBaseManager = {
.....
DeleteUser: function (data, callback) {
try {
this.initializeDataBase();
var self = this;
self.Offlinedb.transaction(function (tx) {
tx.executeSql("Delete from UserData where UserID=?",
[data], function (tx, results) {
if (callback) callback("Data deleted");
});
});
}
catch (e) {
}
}
....
}
}else{
var DataBaseManager = {
....
DeleteUser: function (data, callback) {
var self = this;
db.open({
server: OfflineConfiguration.DB_NAME,
version: OfflineConfiguration.Db_VERSION,
schema: OfflineConfiguration.SCHEMA
}).done(function (s) {
self.Server = s
self.Server.UserData.remove(data).done(function (a) {
if (callback) callback("Data deleted");
});
});
}
....
}
}
That's it! We are done with our four data operations.
Use of These Operations
You can refer to the index.js file in which I have implemented the use of these operations.
1. Use of Add New User Operation
var data = '{"Name":"Vinu","Email":"itvinay12@gmail.com",
"Technology":".NET"}';
data = JSON.parse(data);
DataBaseManager.AddnewUser(data, GetAllUser);
2. Use of Get User Operation
DataBaseManager.GetSingleUser(1, anycallbackfunction);
3. Use of Update User Operation
var data = '{"UserID":1,"Name":"Vinu",
"Email":"itvinay12@gmail.com","Technology":".NET"}';
data = JSON.parse(data);
DataBaseManager.UpdateUser(data, GetAllUser);
4. Use of Delete User Operation
DataBaseManager.DeleteUser(1,GetAllUser);
Points of Interest
I just want to suggest a point that before starting these types of applications, please choose the best JavaScript framework and read about the compatibility of the browsers.
History
- 18th September, 2014: First post