Join the social network of Tech Nerds, increase skill rank, get work, manage projects...
 
  • CRUD Operations Using WebSQL In HTML5 And jQuery

    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 760
    Comment on it

    Hello!!

    HTML 5 provides an API, called "Web SQL" API. It is new feature in HTML5 and it also helps to the developer for doing CRUD operation on the client side. It give the permission to client side for doing some operation:-

    • Create a Database.
    • Transaction.
    • Execute sql query.
    • Creating a Table.
    • Inserting Data to Table.
    • Deleting Data from Table.
    • Reading Data.  

     Different Type of Method:- 

    1. openDatabase:- It  has the some parameter like Database Name, Version, Description, Size and Creation Callback.
    2. transaction.
    3. executeSql.

    Here is the example which describe  all the operation in mention above:-

    Code:-

    <!DOCTYPE html>  
    <html>  
    <head>  
        <title>Open DataBase</title>  
        <script src="https://ajax.googleapis.com/ajax/libs/jquery/1.12.4/jquery.min.js"></script>  
         
        <script>  
      
            var Database_Name = 'MyDatabase';  
            var Version = 1.0;  
            var Text_Description = 'My First Web-SQL Example';  
            var Database_Size = 2 * 1024 * 1024;  
            var dbObj = openDatabase(Database_Name, Version, Text_Description, Database_Size);  
            dbObj.transaction(function (tx) {  
      
                tx.executeSql('CREATE TABLE IF NOT EXISTS Employee_Table (id unique, Name, Location,did)');  
                tx.executeSql('CREATE TABLE IF NOT EXISTS dept_Table (did unique, dName,estd)');  
      
      
                var today = new Date();  
                var dd = today.getDate();  
                var mm = today.getMonth() + 1; //January is 0!  
      
                var yyyy = today.getFullYear();  
                if (dd < 10) {  
                    dd = '0' + dd  
                }  
                if (mm < 10) {  
                    mm = '0' + mm  
                }  
                var today = dd + '/' + mm + '/' + yyyy;  
      
                tx.executeSql('insert into dept_Table(did, dName, estd) values(1,"IT","' + today + '")');  
                tx.executeSql('insert into dept_Table(did, dName, estd) values(2,"Accountant","' + today + '")');  
                tx.executeSql('insert into dept_Table(did, dName, estd) values(3,"Claerk","' + today + '")');  
                tx.executeSql('insert into dept_Table(did, dName, estd) values(4,"Management","' + today + '")');  
                alldetails();  
            });  
      
      
            function Insert() {  
                
                var id = document.getElementById("tbID").value;  
                var name = document.getElementById("tbName").value;  
                var location = document.getElementById("tbLocation").value;  
                var did = document.getElementById("tbLdept").value;  
                dbObj.transaction(function (tx) {  
                    tx.executeSql('insert into Employee_Table(id, Name, Location,did) values(' + id + ',"' + name + '","' + location + '",' + did + ')');  
      
      
                });  
      
                alldetails();  
            }  
      
            function del() {  
                var id = document.getElementById("ddlid").value;  
              //  alert(id);  
                dbObj.transaction(function (tx) {  
                    tx.executeSql('delete from Employee_Table where id=' + id + '');  
                });  
      
                empidbind();  
      
                alldetails();  
            }  
      
      
            function myFunction()  
            {  
                var id = document.getElementById("ddlid").value;  
      
                dbObj.transaction(function (tx) {  
                    tx.executeSql('SELECT * from Employee_Table where id=' + id + '', [], function (tx, results)  
                    {  
                        document.getElementById("tbName").value = results.rows.item(0).Name;  
                        document.getElementById("tbLocation").value = results.rows.item(0).Location;  
                        document.getElementById("tbLdept").value = results.rows.item(0).did;  
      
                          
                    }, null);  
                });  
                 
            }  
      
            function showdel() {  
      
                empidbind();  
      
                $('#tdorginal').hide();  
                $('#tdid').show();  
                $('#btnupdate').hide();  
                $('#btnInsert').hide();  
                $('#btndel').show();  
                $('#btninsertshw').show();  
                $('#btnupdateshw').show();  
                $('#btndeleteshw').hide();  
                ////////////  
                $('#rowName').hide();  
                $('#rowLocation').hide();  
                $('#rowdept').hide();  
            }  
      
            function showin()  
            {  
                $('#tdid').hide();  
                $('#tdorginal').show();  
                
                $('#btnupdate').hide();  
                $('#btnInsert').show();  
                $('#btndel').hide();  
                $('#btninsertshw').hide();  
                $('#btnupdateshw').show();  
                $('#btndeleteshw').show();  
                ////////////  
                $('#rowName').show();  
                $('#rowLocation').show();  
                $('#rowdept').show();  
      
                /////////////  
              document.getElementById("tbID").value='';  
              document.getElementById("tbName").value='';  
              document.getElementById("tbLocation").value='';  
              document.getElementById("tbLdept").value='1';  
      
                   empidbind();  
            }  
      
      
            function empidbind()  
            {  
                dbObj.transaction(function (tx) {  
                    tx.executeSql('SELECT * from Employee_Table', [], function (tx, results) {  
                        var len = results.rows.length, i;  
                        document.getElementById("ddlid").innerHTML = '';  
                        var str = '';  
                        for (i = 0; i < len; i++) {  
                            str += "<option value=" + results.rows.item(i).id + ">" + results.rows.item(i).id + "</option>";  
                            document.getElementById("ddlid").innerHTML += str;  
                            str = '';  
                        }  
                    }, null);  
                });  
      
            }  
      
            function showupdte()  
            {  
                empidbind();  
      
      
                $('#tdorginal').hide();  
                $('#tdid').show();  
                $('#btnupdate').show();  
                $('#btnInsert').hide();  
                $('#btndel').hide();  
                $('#btninsertshw').show();  
                $('#btnupdateshw').hide();  
                $('#btndeleteshw').show();  
                $('#rowName').show();  
                $('#rowLocation').show();  
                $('#rowdept').show();  
                  
            }  
      
            function updte() {  
                
                var id = document.getElementById("ddlid").value;  
                var name = document.getElementById("tbName").value;  
                var location = document.getElementById("tbLocation").value;  
                var did = document.getElementById("tbLdept").value;  
      
                dbObj.transaction(function (tx) {  
                    tx.executeSql('update Employee_Table set Name="' + name + '",Location="' + location + '",did=' + did + ' where id=' + id + '');  
                });  
      
                alldetails();  
            }  
      
            function alldetails()  
            {  
                dbObj.transaction(function (tx) {  
                    tx.executeSql('SELECT e.id,e.Name,e.Location,d.dName,d.did FROM Employee_Table e inner join dept_Table d on e.did=d.did ', [], function (tx, results) {  
                        var len = results.rows.length, i;  
                        // document.getElementById("tblGrid").innerHTML = '';  
                        $("#tblGrid").find("tr:gt(0)").remove();  
                        var str = '';  
                        for (i = 0; i < len; i++) {  
                            str += "<tr>";  
                            str += "<td>" + results.rows.item(i).id + "</td>";  
                            str += "<td>" + results.rows.item(i).Name + "</td>";  
                            str += "<td>" + results.rows.item(i).Location + "</td>";  
                            str += "<td>" + results.rows.item(i).dName + "</td>";  
                            str += "</tr>";  
                            document.getElementById("tblGrid").innerHTML += str;  
                            str = '';  
                        }  
                    }, null);  
                });  
      
            }  
      
            dbObj.transaction(function (tx) {  
                tx.executeSql('SELECT * from dept_Table', [], function (tx, results) {  
                    var len = results.rows.length, i;  
                    var str = '';  
                    for (i = 0; i < len; i++) {  
                        str += "<option value=" + results.rows.item(i).did + ">" + results.rows.item(i).dName + "</option>";  
                        document.getElementById("tbLdept").innerHTML += str;  
                        str = '';  
                    }  
                }, null);  
            });  
      
      
             
        </script>  
    </head>  
    <body>  
        <p id="hh"></p>  
        <form id="frm1">  
            <table id="tblinsert">  
                <tr>  
                    <td>ID:</td>  
                    <td id="tdorginal"><input type="text" id="tbID" /><span style="color:red">*ID must be unique</span></td>  
                    <td id="tdid" style="display:none">  
                        <select id="ddlid" onchange="myFunction()"></select>  
                    </td>  
                </tr>  
                <tr id="rowName">  
                    <td>Name:</td>  
                    <td><input type="text" id="tbName" /></td>  
      
                </tr>  
                <tr id="rowLocation">  
                    <td>Location:</td>  
                    <td><input type="text" id="tbLocation" /></td>  
                </tr>  
      
                <tr id="rowdept">  
                    <td>Dept:</td>  
                    <td>  
                        <select id="tbLdept"></select>  
                    </td>  
                </tr>  
      
                <tr>  
                     
                </tr>  
            </table>  
        </form>  
        <br />  
        <button id="btnInsert" onclick="Insert()" style="color:green;display:block">Save</button>  
        <button id="btnupdate" onclick="updte()" style="color:blue;display:none">Update</button>  
        <button id="btndel" onclick="del()" style="color:red;display:none">Delete</button>  
          
        <br /><br />  
        <button id="btnupdateshw" onclick="showupdte()" style="color:red">update Employee details</button>  
        <button id="btndeleteshw" onclick="showdel()" style="color:blue">Delete Employee details</button>  
        <button id="btninsertshw" onclick="showin()" style="color:green;display:none">save Employee details</button>  
        <br /><br />  
        <table id="tblGrid" cellpadding="10px" cellspacing="0" border="1">  
            <tr style="background-color:black;color:white;font-size:18px;">  
                <td >  
                    ID  
                </td>  
                <td >  
                    Name  
                </td>  
                <td >  
                    Location  
                </td>  
                <td >  
                    Department  
                </td>  
            </tr>  
        </table>  
        <br />  
         
       
    </body>  
    </html

     

    For Create Database:-

    var Database_Name = 'MyDatabase';  
            var Version = 1.0;  
            var Text_Description = 'My First Web-SQL Example';  
            var Database_Size = 2 * 1024 * 1024;  
            var dbObj = openDatabase(Database_Name, Version, Text_Description, Database_Size);

     

    Creating transaction:-

    dbObj.transaction(function (tx)  
     {  
    tx.executeSql('CREATE TABLE IF NOT EXISTS Employee_Table (id unique, Name, Location,did)');  
                tx.executeSql('CREATE TABLE IF NOT EXISTS dept_Table (did unique, dName,estd)');  
    }); 

     

    Execute Sql:-

    dbObj.transaction(function (tx)  
     {  
    tx.executeSql('CREATE TABLE IF NOT EXISTS Employee_Table (id unique, Name, Location,did)');  
                tx.executeSql('CREATE TABLE IF NOT EXISTS dept_Table (did unique, dName,estd)');  
    });

     

 0 Comment(s)

Sign In
                           OR                           
                           OR                           
Register

Sign up using

                           OR                           
Forgot Password
Fill out the form below and instructions to reset your password will be emailed to you:
Reset Password
Fill out the form below and reset your password: