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" />
<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>
Good work Ajay!!!!
ReplyDelete