Click here to Skip to main content
13,771,292 members
Click here to Skip to main content
Add your own
alternative version

Stats

90.7K views
6K downloads
38 bookmarked
Posted 18 Sep 2014
Licenced CPOL

A Very Simple Example of HTML 5 OFFLINE Database (indexedDB and WebSql)

, 18 Sep 2014
Rate this:
Please Sign up or sign in to vote.
This article explains the basic knowledge about the browser databases that comes in handy when working with the HTML 5 Offline application to create, edit, modify or delete the data of a table in databases (indexedDB and WebSql).

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

// Cache 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) {
    //SAFARI BROWSER SO WEBSQL IMPLEMENTATION HERE
}else{
    //OTHER BROWSERS SO INDEXEDDB IMPLEMENTATION HERE
}

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)//this accept four parameters 1. database name,
 //2. version, 3. Comments, 4.initial Size
    }
}else{
    //For indexedDB the db.js file automatically initialize the database.
    //So we just need to provide the schema and database name during our database operations.
}

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 = {
        //For indexedDB we will not have any initialize function 
        //as we already define the schema for this globally.
    }
}

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) {//data: contains the object of user ,
                        // callback: is a function will execute after the addition

                    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 = {
        //For indexedDB we will not have any initialize function as we already define 
        //the schema for this globally.
        AddnewUser: function (data, callback) {//data: contains the object of user ,
                    // callback: is a function will execute after the addition
                    db.open({
                        server: OfflineConfiguration.DB_NAME,//database name defined globally
                        version: OfflineConfiguration.Db_VERSION,//version defined globally
                        schema: OfflineConfiguration.SCHEMA//schema defined globally

                    }).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) {//data: contains the UserID ,
                // callback: is a function will execute after the selection
                    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) {//data: contains the UserID ,
                //callback: is a function will execute after the selection
                    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) {//data: contains the object of user ,
                // callback: is a function will execute after the updation
                    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) {//data: contains the object of user ,
                // callback: is a function will execute after the updation
                    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) {//data: contains the UserID of user ,
                    // callback: is a function will execute after the deletion
                    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) {//data: contains the UserID of user ,
                // callback: is a function will execute after the deletion
                    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"}';//user object
        data = JSON.parse(data);
        DataBaseManager.AddnewUser(data, GetAllUser);// GetAllUser is a 
                //function defined in index.js you can refer it.

2. Use of Get User Operation

DataBaseManager.GetSingleUser(1, anycallbackfunction);// Get the details of user with id 1.

3. Use of Update User Operation

    var data = '{"UserID":1,"Name":"Vinu",
"Email":"itvinay12@gmail.com","Technology":".NET"}';//user object  with UserID
        data = JSON.parse(data);
        DataBaseManager.UpdateUser(data, GetAllUser);// GetAllUser is a 
                //function defined in index.js you can refer it.

4. Use of Delete User Operation

DataBaseManager.DeleteUser(1,GetAllUser);// delete the details of user with id 1.

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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Vinu Sorout
Software Developer
India India
No Biography provided

You may also be interested in...

Comments and Discussions

 
GeneralGood Explain all about for beginner. my vote 5 Pin
joginder-banger27-Sep-16 2:18
professionaljoginder-banger27-Sep-16 2:18 
QuestionNice Explanation Pin
Member 1240147111-Sep-16 20:22
memberMember 1240147111-Sep-16 20:22 
AnswerRe: Nice Explanation Pin
Vinu Sorout12-Sep-16 0:30
professionalVinu Sorout12-Sep-16 0:30 
Questionoffline apllication in asp.net c# using sql Pin
Vipin Mudgal11-Apr-16 21:20
memberVipin Mudgal11-Apr-16 21:20 
AnswerRe: offline apllication in asp.net c# using sql Pin
Vinu Sorout12-Apr-16 1:24
professionalVinu Sorout12-Apr-16 1:24 
Questionto synchronize this information? Pin
Member 1044632121-Sep-14 21:32
memberMember 1044632121-Sep-14 21:32 
AnswerRe: to synchronize this information? Pin
Vinu Sorout21-Sep-14 21:51
professionalVinu Sorout21-Sep-14 21:51 
GeneralRe: to synchronize this information? Pin
Member 1044632121-Sep-14 22:19
memberMember 1044632121-Sep-14 22:19 
GeneralRe: to synchronize this information? Pin
Member 1044632121-Sep-14 22:23
memberMember 1044632121-Sep-14 22:23 
GeneralRe: to synchronize this information? Pin
Vinu Sorout22-Sep-14 0:02
professionalVinu Sorout22-Sep-14 0:02 
QuestionLots of Security Issue its open database Pin
saineshwar bageri18-Sep-14 20:16
membersaineshwar bageri18-Sep-14 20:16 
AnswerRe: Lots of Security Issue its open database Pin
Vinu Sorout22-Sep-14 0:06
professionalVinu Sorout22-Sep-14 0:06 
GeneralMy vote of 5 Pin
Humayun Kabir Mamun18-Sep-14 5:15
memberHumayun Kabir Mamun18-Sep-14 5:15 
GeneralRe: My vote of 5 Pin
Vinu Sorout22-Sep-14 0:06
professionalVinu Sorout22-Sep-14 0:06 
GeneralMy vote of 5 Pin
Er. Tarun Pachauri18-Sep-14 4:33
memberEr. Tarun Pachauri18-Sep-14 4:33 
GeneralRe: My vote of 5 Pin
Vinu Sorout22-Sep-14 0:07
professionalVinu Sorout22-Sep-14 0:07 
General5 vote for simple explanation. Pin
Er. Tarun Pachauri18-Sep-14 4:32
memberEr. Tarun Pachauri18-Sep-14 4:32 
GeneralRe: 5 vote for simple explanation. Pin
Vinu Sorout22-Sep-14 0:37
professionalVinu Sorout22-Sep-14 0:37 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Cookies | Terms of Use | Mobile
Web01-2016 | 2.8.181119.1 | Last Updated 18 Sep 2014
Article Copyright 2014 by Vinu Sorout
Everything else Copyright © CodeProject, 1999-2018
Layout: fixed | fluid