Monday 19 August 2013

CRUD in WebSQL



Here is example of WebSQL, for Offline database at client side. The database will stored at client’s browser and only Google Chrome and Safari are the browser which support WebSQL

Create Database :
var db = openDatabase('TestDB', '1.0', 'Test DB', 2 * 1024 * 1024, DBCreated);
    //var db = openDatabase('Name', 'Version', 'DB Description', size in bytes, Callback);

function DBCreated(e) {
        alert("DB Created");
    }

Create Table and Insert/Update data :
$(document).ready(function () {
    $("#btnSave").click(function () {
       db.transaction(function (tx) {
           tx.executeSql('CREATE TABLE IF NOT EXISTS Person (id INTEGER PRIMARY KEY  
     AUTOINCREMENT,Name TEXT NOT NULL DEFAULT "", City TEXT NOT NULL   
     DEFAULT "", Contact INTEGER NOT NULL DEFAULT 0)', [],
     function (trans, msg) {

                          }, function (trans, error) {
                          alert(error.message);
                   });
 //tx.executeSql('Query',[Parameter],function(trans,error){ onSuccess},    
                  function(trans,error){onError});

                var Name = $("#txtName").val();
                var City = $("#txtCity").val();
                var Contact = $("#txtContact").val();

                if ($("#btnSave").val() != "Update") {
                    tx.executeSql('INSERT INTO Person (Name, City, Contact)
VALUES ("' + Name + '","' + City + '","' + Contact + '")',
[],
                        function (trans, result) {
                                   if (result.rowsAffected > 0) {
                                       $("#txtName").val("");
                                      $("#txtCity").val("");
                                      $("#txtContact").val("");
                                  }
                                   $("#btnShow").click();
                               },
  function (trans, error) {
                               alert(error.message);
                       });
                }
                else {
                    tx.executeSql('UPDATE Person SET  Name = ?, City = ?, Contact = ?  
                                   WHERE id = ?', [Name, City, Contact,
                                  $("#btnSave")[0].name],
                        function (trans, result) {
                            if (result.rowsAffected > 0) {
                                $("#txtName").val("");
                                $("#txtCity").val("");
                                $("#txtContact").val("");
                            }
                            $("#btnShow").click();
                        }, function (trans, error) {
                            alert(error.message);
                        });
                }

            });

    $("#btnShow").click(function () {
            $("#tblList").html("");
            db.transaction(function (tx) {
                tx.executeSql('Select * from Person order by Name', [],
                    function (trans, result) {
                    var Len = result.rows.length;
                    $("#spnMsg").html("Total Record found : " + Len);
                    var THead = $("#tblList")[0].createTHead();
                    var row = THead.insertRow(0);
                    row.insertCell(0).innerHTML = "ID";
                    row.insertCell(1).innerHTML = "Name";
                    row.insertCell(2).innerHTML = "City";
                    row.insertCell(3).innerHTML = "Contact";
                    row.insertCell(4).innerHTML = "Edit";
                    row.insertCell(5).innerHTML = "Delete";

                    for (var i = 0; i < Len; i++) {
                        var row = $("#tblList")[0].insertRow(i + 1);
                        row.insertCell(0).innerHTML = result.rows.item(i).id;
                        row.insertCell(1).innerHTML = result.rows.item(i).Name;
                        row.insertCell(2).innerHTML = result.rows.item(i).City;
                        row.insertCell(3).innerHTML = result.rows.item(i).Contact;
                        row.insertCell(4).innerHTML = "<input type='button' value='Edit'
onclick='Edit(" + result.rows.item(i).id + ")' >"
                        row.insertCell(5).innerHTML = "<input type='button' value='Delete'
onclick='Delete(" + result.rows.item(i).id + ")' >"
                    }
                }, function (trans, error) {
                    alert(error.message);
                })
            });
        });
});

/// For Edit Record
    function Edit(ID) {
        db.transaction(function (tx) {
            tx.executeSql("Select * from person where id=?", [ID], function (trans, e) {
                if (e.rows.length > 0) {
                    debugger;
                    $("#txtName").val(e.rows.item(0).Name);
                    $("#txtCity").val(e.rows.item(0).City);
                    $("#txtContact").val(e.rows.item(0).Contact);
                    $("#btnSave").val("Update");
                    $("#btnSave")[0].name = ID;
                }
            }, function (trans, e) {
                alert(e.message)
            })
        });
    }
/// For Delete Record
    function Delete(ID) {
        db.transaction(function (tx) {
            tx.executeSql("Delete from Person where id = ?", [ID], function (trans, error) {
                $("#btnShow").click();
            }, function (trans, error) {
                alert(error.message);
            })
        });
    }
HTML :
<div>
            <table>
                <tr>
                    <td>Name :</td>
                    <td>
                        <input type="text" id="txtName" />
                    </td>
                </tr>
                <tr>
                    <td>City</td>
                    <td>
                        <input type="text" id="txtCity" />
                    </td>
                </tr>
                <tr>
                    <td>Contact No :</td>
                    <td>
                        <input type="text" id="txtContact" />
                    </td>
                </tr>
                <tr>
                    <td colspan="2" align="center">
                        <input type="button" id="btnSave" value="Add" />
                        &nbsp;&nbsp;                   
                        <input type="button" id="btnShow" value="Show" /></td>
                </tr>
            </table>
            <br />
            <span id="spnMsg"></span>
            <table id="tblList" style="width: 300px" cellpadding="5">
            </table>
        </div>

1 comment: