Store Local Storage Data Into Database Using Ajax

Introduction

The Local Storage is designed for storing the data at user side. In local storage data exist with no expiration date. The data will be available even when the browser / browsing tab is closed or reopened. Storage of data for temporary time at user side is a useful approach when we required to store data in database with a high frequent rate. Using local storage mechanism we can store a large amount data at user side storage and later we can insert all these data into database in a single interaction with database such that we can reduce the traffic and increase the performance of application.

Today I will tell you how to insert data into database using Ajax that is locally stored.

Firstly, create an Employee Table in database.

  1. CREATE TABLE [dbo].[Employee](  
  2.    [Emp_Name] [nvarchar](max)NULL,  
  3.    [Emp_Age] [intNULL,  
  4.    [Emp_Salary] [intNULL,  
  5.    [Emp_City] [nvarchar](max)NULL  
  6. )  
table design

We will insert data into above Employee table.

Now we create a web page as below.

create a web page

HTML and CSS code for above page is:

Html Code
  1. <body>  
  2.     <formid="form1" runat="server">  
  3.         <divid="div1">  
  4.             <divid="head">Store Local Storage Data into Database Using Ajax</div>  
  5.                 <table>  
  6.                     <tr>  
  7.                         <td><span>Name:</span></td>  
  8.                         <td>  
  9.                             <inputid="txtName" type="text" />  
  10.                         </td>  
  11.                     </tr>  
  12.                     <tr>  
  13.                         <td><span>Age:</span></td>  
  14.                         <td>  
  15.                             <inputid="txtAge" type="text" />  
  16.                         </td>  
  17.                     </tr>  
  18.                     <tr>  
  19.                         <td><span>Salary:</span></td>  
  20.                         <td>  
  21.                             <inputid="txtSalary" type="text" />  
  22.                         </td>  
  23.                     </tr>  
  24.                     <tr>  
  25.                         <td><span>City:</span></td>  
  26.                         <td>  
  27.                             <inputid="txtCity" type="text" />  
  28.                         </td>  
  29.                     </tr>  
  30.                 </table>  
  31.                 <div>  
  32.                     <inputtype="button" id="btnStore" value="Store Data" />  
  33.                     <br/>  
  34.                     <inputtype="button" id="btnSave" value="Save Data" />  
  35.                     <br/>  
  36.                     <inputtype="button" id="btnClear" value="Clean Data" /> </div>  
  37.                 </div>  
  38.              </form>  
  39. </body>  
CSS Code
  1. <style>  
  2.     #div1 {  
  3.         width100%;  
  4.         height500px;  
  5.         background-color: darkkhaki;  
  6.         font-size30px;  
  7.         font-styleoblique;  
  8.         padding-top20px;  
  9.     }  
  10.       
  11.     span {  
  12.         margin-left30px;  
  13.         margin-left150px;  
  14.     }  
  15.       
  16.     input {  
  17.         margin-right30px;  
  18.         width400px;  
  19.         height40px;  
  20.         font-size24px;  
  21.     }  
  22.       
  23.     #btnStore {  
  24.         margin-left300px;  
  25.         margin-top10px;  
  26.         font-size24px;  
  27.         background-color: blueviolet;  
  28.         width200px;  
  29.         height50px;  
  30.     }  
  31.       
  32.     #btnSave {  
  33.         margin-left300px;  
  34.         margin-top10px;  
  35.         font-size24px;  
  36.         background-color: lightcoral;  
  37.         width200px;  
  38.         height50px;  
  39.     }  
  40.       
  41.     #btnClear {  
  42.         margin-left300px;  
  43.         margin-top10px;  
  44.         font-size24px;  
  45.         background-color: AppWorkspace;  
  46.         width200px;  
  47.         height50px;  
  48.     }  
  49.       
  50.     #head {  
  51.         margin-left200px;  
  52.         margin-bottom20px;  
  53.         colorblue  
  54.     }  
  55. </style>  
In above page we created 4 textbox through which we will insert the employee's basic detail.

Insert data into local storage:
  1. $("#btnStore").click(function()  
  2. {  
  3.     if (localStorage)  
  4.     {  
  5.         var Employee_ = {};  
  6.         Employee_.Name = $("#txtName").val();  
  7.         Employee_.Age = $("#txtAge").val();  
  8.         Employee_.Salary = $("#txtSalary").val();  
  9.         Employee_.City = $("#txtCity").val();  
  10.         varItemId = "Emp" + Employee_.Name;  
  11.         localStorage.setItem(ItemId, JSON.stringify(Employee_));  
  12.     }  
  13.     else  
  14.     {  
  15.         alert("OOPS! Your Browser Not Supporting LocalStorage Please Update It!")  
  16.     }  
  17. });  
Above code will execute when a user click on the “Store Data” button. On click event we will first check that browser support the local storage or not. Here “localStorage” returns true if browser support local storage otherwise we will show an alert message. We created a jsonobject (Employee) in which we store the value of Employee Name, Age, Salary, City in key value pairs. At last we store this JSON Object into local storage using the “localStorage.setItem” method. This method store data that is associated with a key.

Let us try to insert some data.

insert some data

After insertion of values now we check the local storage of browser and check the status of retrieved data.

insertion of values

Above image show that data is successfully stored in local storage. We can see that data is stored as key value pair. At last “length” is showing total numbers of records. Till now our first phase has been completed. Now we will retrieve data from local storage and insert into SQL Server database using Ajax.

Ajax code

When user click on the “Store Data” button first we retrieved data from local storage and after that we use Ajax to store this data into database.

Firstly, we will retrieve data from local storage.
  1. //Retrieve Data from Local Storage  
  2. varEmployee_Detail = {}  
  3. Employee_Detail.Emp_Name = "";  
  4. Employee_Detail.Emp_Age = "";  
  5. Employee_Detail.Emp_Salary = "";  
  6. Employee_Detail.Emp_City = "";  
  7. for (i = 0; i < localStorage.length; i++)  
  8. {  
  9.     varobj = localStorage.getItem(localStorage.key(i));  
  10.     var Employee = JSON.parse(obj);  
  11.     Employee_Detail.Emp_Name += Employee["Name"] + ",";  
  12.     Employee_Detail.Emp_Age += Employee["Age"] + ",";  
  13.     Employee_Detail.Emp_Salary += Employee["Salary"] + ",";  
  14.     Employee_Detail.Emp_City += Employee["City"] + ",";  
  15. }  
In above code we retrieved data using the “getItem” method of localStorage. We created an Employee_Deatil object and maintained four fields  in this JSON object. We retrieved data using for loop. Here, “localStorage.length” return the number (count) of stored data. We retrieve all values from local storage and created a single string for each field. In each field we stored all values of that particular field in a comma separated manner.

Call Web Method using Ajax
  1. $.ajax({  
  2.    type: "POST",  
  3.    url: "default.aspx/Insert_Data",  
  4.    dataType: "json",  
  5.    contentType: "application/json; charset=utf-8",  
  6.    data: JSON.stringify(Employee_Detail),  
  7.    success: function () {  
  8.       alert("Data Has Inserted")  
  9.    }  
  10.    })  
  11. });  
We will call a Web Method (Insert_Data) using the Ajax. We will pass the JSON object as the parameter in Web Method. Here's the code of Web Method,
  1. [WebMethod]  
  2. publicstaticvoidInsert_Data(StringEmp_Name, StringEmp_Age, StringEmp_Salary, StringEmp_City)  
  3. {  
  4.     try  
  5.     {  
  6.         SqlConnection con = newSqlConnection("Data Source=Temp; Initial Catalog=Catalog;Integrated Security=true;");  
  7.         con.Open();  
  8.         SqlCommandCmd = newSqlCommand("[usp_Employee_Insert]", con);  
  9.         Cmd.CommandType = CommandType.StoredProcedure;  
  10.         Cmd.Parameters.AddWithValue("@Emp_Name", Emp_Name);  
  11.         Cmd.Parameters.AddWithValue("@Emp_Age", Emp_Age);  
  12.         Cmd.Parameters.AddWithValue("@Emp_Salary", Emp_Salary);  
  13.         Cmd.Parameters.AddWithValue("@Emp_City", Emp_City);  
  14.         Cmd.ExecuteNonQuery();  
  15.         con.Close();  
  16.     }  
  17.     catch  
  18.     {}  
  19. }  
The Insert_Data web method have four parameters. First parameter (Emp_Name) contain string of employee name. Second parameter(Emp_Age) contain string of employee age. Third parameter (Emp_Salary) contain string of employee salary. Fourth parameter (Emp_City) contain string of employee city.

Here we retrieve the string that contain that are comma separated employee information.

retrieve the string t

In above image we can see that name of all employees are stored in Emp_Name string and each name is separated by comma. Such that age of employees is stored in Emp_Age, Salary stored in Emp_Salary and city stored in Emp_City.

In above we used a stored procedure to insert the data into employee table. Code of this stored procedure is.

Stored Procedure
  1. ALTER PROCEDURE usp_Employee_Insert(@Emp_Namenvarchar(max) = NULL, @Emp_Agenvarchar(max) = NULL, @Emp_Salarynvarchar(max) = NULL, @Emp_Citynvarchar(max) = NULL)  
  2. AS  
  3. BEGIN  
  4. DECLARE@ EmpName_[nvarchar](max);  
  5. DECLARE@ EmpAge_[nvarchar](max);  
  6. DECLARE@ EmpSalary_[nvarchar](max);  
  7. DECLARE@ EmpCity_[nvarchar](max);  
  8. DECLARE@ EmpName_Indexint;  
  9. DECLARE@ EmpAge_Indexint;  
  10. DECLARE@ EmpSalary_Indexint;  
  11. DECLARE@ EmpCity_Indexint;  
  12.   
  13. WHILE(LEN(@Emp_Name) > 0) AND(LEN(@Emp_Age) > 0) AND(LEN(@Emp_Salary) > 0) AND(LEN(@Emp_City) > 0)  
  14. BEGIN  
  15. SET@ EmpName_Index = CHARINDEX(',', @Emp_Name);  
  16. SET@ EmpAge_Index = CHARINDEX(',', @Emp_Age);  
  17. SET@ EmpSalary_Index = CHARINDEX(',', @Emp_Salary);  
  18. SET@ EmpCity_Index = CHARINDEX(',', @Emp_City);  
  19.   
  20. SET@ EmpName_ = SUBSTRING(@Emp_Name, 0, @EmpName_Index);  
  21. SET@ EmpAge_ = SUBSTRING(@Emp_Age, 0, @EmpAge_Index);  
  22. SET@ EmpSalary_ = SUBSTRING(@Emp_Salary, 0, @EmpSalary_Index);  
  23. SET@ EmpCity_ = SUBSTRING(@Emp_City, 0, @EmpCity_Index);  
  24.   
  25.   
  26.   
  27. INSERT INTOdbo.Employee(  
  28.     Emp_Name,  
  29.     Emp_Age,  
  30.     Emp_Salary,  
  31.     Emp_City  
  32. )  
  33. VALUES  
  34.     (@EmpName_, --Emp_Name - nvarchar CONVERT(int, @EmpAge_), --Emp_Age - int CONVERT(int, @EmpSalary_), --Emp_Salary - int@ EmpCity_--Emp_City - nvarchar)  
  35.   
  36. SET@ Emp_Name = SUBSTRING(@Emp_Name, @EmpName_Index + 1, LEN(@Emp_Name));  
  37. SET@ Emp_Age = SUBSTRING(@Emp_Age, @EmpAge_Index + 1, LEN(@Emp_Age));  
  38. SET@ Emp_Salary = SUBSTRING(@Emp_Salary, @EmpSalary_Index + 1, LEN(@Emp_Salary));  
  39. SET@ Emp_City = SUBSTRING(@Emp_City, @EmpCity_Index + 1, LEN(@Emp_City));  
  40.   
  41. PRINT@ Emp_Name  
  42. END  
  43. END  
In the preceding store procedure we take employee name, age, salary and city as comma separated string and we split these string here and insert the data into table .

After successful execution of Meb Method we get an alert message that will show data has been inserted.

Meb Method

Now we check the Employee Table.

table

We can see the data has been inserted into Employee table.

Clear Data
  1. $("#btnClear").click(function () {  
  2.    localStorage.clear();  
  3. })  
We can clear the localStorage data using the localStorage.clear method. This method will call when user click on “Clean Data” button. The localStorage.clear method clear all the data that is stored in local storage.

Today we learned how to store data into local storage and insert this locally stored data into database using Ajax.

I hope you liked this article. Thanks for reading the article. 


Similar Articles