Purpose : Check the application is on line or off line. If application is off line then save the data into the sqlite database. And when application is online then save the sqlite data into sqlserver first and delete from sqlite data.
Steps :
Step1: Create Html Page.
<!DOCTYPE html PUBLIC > <html> <head> <title>Exercise 3</title> <link rel="Stylesheet" href="../Styles/ExercisePage3StyleSheet.css"/> <script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.7.0/jquery.min.js"></script> <body> <h1>CONTACT FORM</h1> <form id="mycontact"> <fieldset> <legend>Your details</legend> <ol> <li> <label for="username">Name</label> <input id="username" type="text" placeholder="First and last name" required autofocus pattern=" "> <input type="hidden" id="id"/> </li> <li> <label for="useremail">Email</label> <input id="useremail" type="text" placeholder="example@domain.com" required pattern=" "> </li> </ol> </fieldset> <button id="btnReset" type=submit>Reset</button> <button id="submitButton" type="submit">SAVE</button> <button id="btnUpdate" type=submit>UPDATE</button> <button id="btnDrop" type=submit>DROP</button> </form> <br /> <div id="results"></div> </body> <script type="text/javascript" src="../Scripts/ExercisePage3JS.js"> </script> </html>
Step 2 : Mank a Style in CSS.
html, body, h1, form, fieldset, legend, ol, li
{
margin: 0;
padding: 0;
}
body
{
background: #ffffff;
color: #111111;
font-family: Georgia, "Times New Roman", Times, serif;
padding : 20px;
}
form#mycontact
{
background: #9cbc2c;
-moz-border-radius: 5px;
-webkit-border-radius: 5px;
padding: 20px;
width: 400px;
height: 150px;
}
form#mycontact fieldset
{
border: none;
margin-bottom: 10px;
}
form#mycontact fieldset:last-of-type
{
margin-bottom: 0;
}
form#mycontact legend
{
color: #384313;
font-size: 16px;
font-weight: bold;
padding-bottom: 10px;
}
form#mycontact > fieldset > legend:before
{
content: "Step " counter(fieldsets) ": ";
counter-increment: fieldsets;
}
form#mycontact fieldset fieldset legend
{
color: #111111;
font-size: 13px;
font-weight: normal;
padding-bottom: 0;
}
form#mycontact ol li
{
background: #b9cf6a;
background: rgba(255,255,255,.3);
border-color: #e3ebc3;
border-color: rgba(255,255,255,.6);
border-style: solid;
border-width: 2px;
-moz-border-radius: 5px;
-webkit-border-radius: 5px;
line-height: 30px;
list-style: none;
padding: 5px 10px;
margin-bottom: 2px;
}
form#mycontact ol ol li
{
background: none;
border: none;
float: left;
}
form#mycontact label
{
float: left;
font-size: 13px;
width: 110px;
}
form#mycontact fieldset fieldset label
{
background:none no-repeat left 50%;
line-height: 20px;
padding: 0 0 0 30px;
width: auto;
}
form#mycontact fieldset fieldset label:hover
{
cursor: pointer;
}
form#mycontact textarea
{
background: #ffffff;
border: none;
-moz-border-radius: 3px;
-webkit-border-radius: 3px;
-khtml-border-radius: 3px;
font: italic 13px Georgia, "Times New Roman", Times, serif;
outline: none;
padding: 5px;
width: 200px;
}
form#mycontact input:not([type=submit]):focus,
form#mycontact textarea:focus
{
background: #eaeaea;
}
form#mycontact button
{
background: #384313;
border: none;
float:left;
-moz-border-radius: 20px;
-webkit-border-radius: 20px;
-khtml-border-radius: 20px;
border-radius: 20px;
color: #ffffff;
display: block;
font: 14px Georgia, "Times New Roman", Times, serif;
letter-spacing: 1px;
margin: 7px 0 0 5px;
padding: 7px 20px;
text-shadow: 0 1px 1px #000000;
text-transform: uppercase;
}
form#mycontact button:hover
{
background: #1e2506;
cursor: pointer;
}
Step 3 : Write the JQuery Code.
// Query for SQLite
var createStatement = "CREATE TABLE IF NOT EXISTS Contacts (id INTEGER PRIMARY KEY AUTOINCREMENT, username TEXT, useremail TEXT)";
var selectAllStatement = "SELECT * FROM Contacts";
var insertStatement = "INSERT INTO Contacts (username, useremail) VALUES (?, ?)";
var db = openDatabase("AddressBook", "1.0", "Address Book", 200000); // Open the SQLite database
var dataset;
var DataType;
function initDatabase()
{
try {
if (!window.openDatabase) // Check Browser Support SQLite Database or Not.
{
alert('Databases are not supported in this browser.');
}
else {
createTable();
}
}
catch (e) {
if (e == 2) {
// Version number mismatch.
console.log("Invalid database version.");
} else {
console.log("Unknown error " + e + ".");
}
return;
}
}
function createTable() {
db.transaction(function (tx) { tx.executeSql(createStatement, [], showRecords, onError); });
}
function insertRecord() {
if (navigator.onLine) // Check internet is online or Off-line.
{
db.transaction(function (tx)
{
tx.executeSql(selectAllStatement, [], function (tx, result) // Fetch records from SQLite
{
dataset = result.rows;
for (var i = 0, item = null; i < dataset.length; i++) {
item = dataset.item(i);
var useridinsert = item['id'];
var usernameinsert = item['username'];
var useremailinsert = item['useremail'];
InsertRecordOnServerFunction(useridinsert, usernameinsert, useremailinsert); // Call Function for insert Record into SQl Server through WCF service.
}
});
});
var usernametemp = $('input:text[id=username]').val();
var useremailtemp = $('input:text[id=useremail]').val();
var useridtemp = 0;
InsertRecordOnServerFunction(useridtemp, usernametemp, useremailtemp);
}
else
{
var usernametemp = $('input:text[id=username]').val();
var useremailtemp = $('input:text[id=useremail]').val();
db.transaction(function (tx) { tx.executeSql(insertStatement, [usernametemp, useremailtemp], loadAndReset, onError); }); // If Off-line, then insert into SQLite.
}
}
function InsertRecordOnServerFunction(useridinsert, usernameinsert, useremailinsert) // Function for insert Record into SQl Server through WCF service.
{
var userinsertinfo = { UserInformation: { userId: useridinsert, userName: usernameinsert, userEmail: useremailinsert} };
var jsondata = JSON.stringify(userinsertinfo); // Convert String into JSON format.
$.ajax({
type: "POST",
url: "http://localhost/HTML5Service/LoginService.svc/AddUser",
data: jsondata,
contentType: "application/json; charset=utf-8",
dataType: "json",
processdata: true,
success: function (msg)
{
//On Successfull service call
InsertServiceSucceeded(msg);
},
error: function (msg) {
alert("Error In Service");
}
});
}
function InsertServiceSucceeded(result) // Sucess Handler Function..
{
resultObject = result.InsertUserInformationResult;
if (resultObject)
{
alert("User Id for Deleted : " + resultObject.ErrorDesc);
var inserteduserid = Number(resultObject.userId);
db.transaction(function (tx) { tx.executeSql(deleteStatement, [inserteduserid], showRecords, onError); alert("Delete User : " + resultObject.userName + " Sucessfully"); });
}
else
{
alert("User Not Deleted");
$('#txtUserName').val("");
$('#txtPassword').val("");
$("#id").val("");
}
}
$(document).ready(function () // Function call when document is ready...
{
$("body").fadeIn(2000);
initDatabase();
$("#submitButton").click(insertRecord);
});
ASP.NET 4.0 Side
Step 4 : Write a WCF Service
private void AddUser(UserInformation userInformation)
{
try
{
SqlConnection con = new SqlConnection("Data Source=SG-IND-J-212;Initial Catalog=Employee;Persist Security Info=True;User ID=sa;Password=sa");
SqlCommand cmd = new SqlCommand("InserUpdateUser", con);
cmd.Parameters.Add("@UserName", SqlDbType.VarChar).Value = userInformation.userName;
cmd.Parameters.Add("@UserPassword", SqlDbType.VarChar).Value = userInformation.passWord;
cmd.Parameters.Add("@FirstName", SqlDbType.VarChar).Value = userInformation.firstName;
cmd.Parameters.Add("@MiddleName", SqlDbType.VarChar).Value = userInformation.middleName;
cmd.Parameters.Add("@LastName", SqlDbType.VarChar).Value = userInformation.lastName;
cmd.Parameters.Add("@UserEmail", SqlDbType.VarChar).Value = userInformation.userEmail;
cmd.CommandType = CommandType.StoredProcedure;
con.Open();
cmd.ExecuteNonQuery();
con.Close();
userInformation.ErrorDesc = "Success";
}
catch (Exception ex)
{
userInformation.ErrorDesc = ex.Message;
}
}
}
[DataContract]
public class UserInformation : ErrorInfo
{
[DataMember]
public int userId;
[DataMember]
public string passWord;
[DataMember]
public string firstName;
[DataMember]
public string lastName;
[DataMember]
public string middleName;
[DataMember]
public string userName;
[DataMember]
public string userEmail;
}
[DataContract]
public class ErrorInfo
{
[DataMember]
public string ErrorDesc;
}
Step 5 : Define the Interface for Operation Contract.
namespace SampleService
{
[ServiceContract]
public interface ILoginService
{
[OperationContract]
[WebInvoke(Method = "POST", BodyStyle = WebMessageBodyStyle.Wrapped, RequestFormat = WebMessageFormat.Json, ResponseFormat = WebMessageFormat.Json)]
void AddUser(UserInformation userInformation);
}
}
Step 6 : Set Header Response in Globel.asmx
protected void Application_BeginRequest(object sender, EventArgs e)
{
HttpContext.Current.Response.Cache.SetCacheability(HttpCacheability.NoCache);
HttpContext.Current.Response.Cache.SetNoStore();
HttpContext.Current.Response.AddHeader("Access-Control-Allow-Origin", "*");
if (HttpContext.Current.Request.HttpMethod == "OPTIONS")
{
HttpContext.Current.Response.AddHeader("Access-Control-Allow-Methods", "POST, GET");
HttpContext.Current.Response.AddHeader("Access-Control-Allow-Headers", "Content-Type, Authorization, Accept");
HttpContext.Current.Response.AddHeader("Access-Control-Max-Age", "1728000");
HttpContext.Current.Response.End();
}
}
Step7 : Set Web.config file
<?xml version="1.0" encoding="UTF-8"?> <configuration> <system.web> <compilation debug="true" /> <identity impersonate="false" /> </system.web> <system.serviceModel> <behaviors> <endpointBehaviors> <behavior name="EndpBehavior"> <webHttp /> </behavior> </endpointBehaviors> <serviceBehaviors> <behavior name="ServiceBehavior"> <serviceMetadata httpGetEnabled="true" /> <serviceDebug includeExceptionDetailInFaults="true" /> </behavior> </serviceBehaviors> </behaviors> <serviceHostingEnvironment aspNetCompatibilityEnabled="true" multipleSiteBindingsEnabled="true" /> <services> <service behaviorConfiguration="ServiceBehavior" name="SampleService.Service1"> <endpoint address="" behaviorConfiguration="EndpBehavior" binding="webHttpBinding" contract="SampleService.IService1" /> </service> <service behaviorConfiguration="ServiceBehavior" name="SampleService.LoginService"> <endpoint address="" behaviorConfiguration="EndpBehavior" binding="webHttpBinding" contract="SampleService.ILoginService" /> </service> </services> <standardEndpoints> <webHttpEndpoint> <!-- Use this element to configure the endpoint --> <standardEndpoint name="" /> </webHttpEndpoint> </standardEndpoints> </system.serviceModel> </configuration>
Step 8 : Create a Store Procedure in MS Sql Server 2008
USE [Employee] GO /****** Object: StoredProcedure [dbo].[InserUpdateUser] Script Date: 12/30/2011 14:48:48 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: tejas patel -- Create date: <Create Date,,> -- Description: Employee Data -- ============================================= ALTER PROCEDURE [dbo].[InserUpdateUser] -- Add the parameters for the stored procedure here @UserId int = 0, @UserName varchar(100) = NULL, @UserPassword varchar(100) = NULL, @FirstName varchar(100) = NULL, @MiddleName varchar(100) = NULL, @LastName varchar(100) = NULL, @UserEmail varchar(200) = NULL AS BEGIN tran BEGIN IF NOT EXISTS (SELECT * FROM UserProfile WHERE UserId = @UserId ) OR @UserId = 0 BEGIN -- INSERT INSERT INTO UserProfile VALUES (@UserName,@UserPassword,@FirstName,@MiddleName,@LastName,@UserEmail) END ELSE BEGIN -- UPDATE UPDATE UserProfile SET UserName = @UserName, UserPassword = @UserPassword, FirstName = @FirstName, MiddleName = @MiddleName, LastName = @LastName, UserEmail = @UserEmail WHERE UserId = @UserId END --IF @@error<>0 --BEGIN -- ROLLBACK --END COMMIT tran END
tejasrpatel 