Post
Ask Question

CRUD operation Using Web Service With JSON

We create web service and method for CRUD operation.
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Web;  
  5. using System.Web.Services;  
  6. using System.Data;  
  7. using System.Data.SqlClient;  
  8. using System.Web.Configuration;  
  9.   
  10.   
  11. [WebService(Namespace = "http://tempuri.org/")]  
  12. [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]  
  13.   
  14. [System.Web.Script.Services.ScriptService]  
  15. public class hr_webservice : System.Web.Services.WebService  
  16. {  
  17.     // getting connection string  
  18.     string conStr = WebConfigurationManager.ConnectionStrings["TESTDB"].ConnectionString;  
  19.   
  20.     //Method for Get Employee List  
  21.     [WebMethod]  
  22.     public string GetEmployeeDetails()  
  23.     {  
  24.         DataTable dt = new DataTable();  
  25.         using (SqlConnection conn = new SqlConnection(conStr))  
  26.         {  
  27.             string sql = string.Format(@"Select * from [SampleInfoTable]");  
  28.             SqlDataAdapter da = new SqlDataAdapter(sql, conn);  
  29.             da.Fill(dt);  
  30.         }  
  31.         //var lst = dt.AsEnumerable().ToList();  
  32.         var lst = dt.AsEnumerable()  
  33.                           .Select(r => r.Table.Columns.Cast<DataColumn>()  
  34.                                   .Select(c => new KeyValuePair<string, object>(c.ColumnName, r[c.Ordinal])  
  35.                                  ).ToDictionary(z => z.Key, z => z.Value)  
  36.                           ).ToList();  
  37.   
  38.         //now serialize it  
  39.         var serializer = new System.Web.Script.Serialization.JavaScriptSerializer();  
  40.         return serializer.Serialize(lst);  
  41.     }  
  42.   
  43.     // Method For Delete  
  44.     [WebMethod]  
  45.     public string DeleteEmployeeData(string employeeId)  
  46.     {  
  47.         int rowsInserted = 0;  
  48.         using (SqlConnection conn = new SqlConnection(conStr))  
  49.         {  
  50.             // Creating insert statement  
  51.             string sql = string.Format(@"Delete [SampleInfoTable] WHERE id='" + employeeId + "'");  
  52.             SqlCommand cmd = new SqlCommand(sql, conn);  
  53.             cmd.Connection = conn;  
  54.             cmd.CommandText = sql;  
  55.             cmd.CommandType = CommandType.Text;  
  56.             conn.Open();  
  57.             rowsInserted = cmd.ExecuteNonQuery();  
  58.             conn.Close();  
  59.             cmd = null;  
  60.         }  
  61.         return GetEmployeeDetails();  
  62.   
  63.     }  
  64.   
  65.     // Edit Employee Data     
  66.     [WebMethod]  
  67.     public string EditEmployeeData(string employeeId, string editedName, string editedEmail, string editedPhone, string editedAddress, string action_mode)  
  68.     {  
  69.         int rowsInserted = 0;  
  70.         // Creating Sql Connection  
  71.         using (SqlConnection conn = new SqlConnection(conStr))  
  72.         {  
  73.             SqlCommand cmd = new SqlCommand();  
  74.             cmd.Connection = conn;  
  75.             cmd.CommandText = "sp_sample_info";  
  76.             cmd.CommandType = CommandType.StoredProcedure;  
  77.   
  78.             cmd.Parameters.Add("@id", employeeId);  
  79.             cmd.Parameters.Add("@Name", editedName);  
  80.             cmd.Parameters.Add("@Email", editedEmail);  
  81.             cmd.Parameters.Add("@Phone", editedPhone);  
  82.             cmd.Parameters.Add("@Address", editedAddress);  
  83.             cmd.Parameters.Add("@Action", action_mode);  
  84.   
  85.   
  86.             conn.Open();  
  87.             rowsInserted = cmd.ExecuteNonQuery();  
  88.             conn.Close();  
  89.             cmd = null;  
  90.         }  
  91.   
  92.         return GetEmployeeDetails();  
  93.   
  94.     }  
  95. }  
This is HTML page where we are calling web service with JSON format.
  1. <%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>  
  2.   
  3. <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">  
  4. <html xmlns="http://www.w3.org/1999/xhtml">  
  5. <head runat="server">  
  6.     <title></title>  
  7.     <style type="text/css">  
  8.         .tableStyle  
  9.         {  
  10.             background-color: White;  
  11.             border: 1px solid rgb(204, 204, 204);  
  12.             font-family: Tahoma;  
  13.             font-size: 14px;  
  14.             width: 1000px;  
  15.             border-collapse: collapse;  
  16.         }  
  17.   
  18.         .tableHeader  
  19.         {  
  20.             color: White;  
  21.             height: 30px;  
  22.             background-color: rgb(15, 159, 15);  
  23.             font-weight: bold;  
  24.         }  
  25.   
  26.         .tableRaw  
  27.         {  
  28.             border: 1px solid rgb(204, 204, 204);  
  29.         }  
  30.   
  31.         input.button  
  32.         {  
  33.             font: bold 12px Arial, Sans-serif;  
  34.             height: 24px;  
  35.             margin: 0;  
  36.             padding: 2px 3px;  
  37.             color: black;  
  38.             border: none;  
  39.         }  
  40.     </style>  
  41.   
  42.     <script src="scripts/jquery-1.4.3.min.js" type="text/javascript"></script>  
  43.   
  44.     <script type="text/javascript">  
  45.   
  46.         $(document).ready(function () {  
  47.             GetEmployeeDetails();  
  48.         });  
  49.   
  50.         function GetEmployeeDetails() {    // This is select method for JSON    
  51.             $.ajax({  
  52.                 type: "POST",  
  53.                 url: "hr_webservice.asmx/GetEmployeeDetails",  
  54.                 data: "{}",  
  55.                 contentType: "application/json; charset=utf-8",  
  56.                 dataType: "json",  
  57.                 success: function (response) {  
  58.                     BindTable(JSON.parse(response.d));  
  59.                 },  
  60.                 failure: function (msg) {  
  61.                     alert(msg);  
  62.                 }  
  63.             });  
  64.         }  
  65.   
  66.         function BlankValidation(editedName, editedEmail, editedPhone, editedAddress) {   // Validation check before insert  
  67.             
  68.             if (editedName == '') {  
  69.                 alert('Blank is not allow');  
  70.                 editedName.focus();  
  71.                 returnVal = false;  
  72.                 return returnVal;  
  73.             }  
  74.             else if (editedEmail == '') {  
  75.                 alert('Blank is not allow');  
  76.                 editedEmail.focus();  
  77.                 returnVal = false;  
  78.                 return returnVal;  
  79.             }  
  80.             else if (editedPhone == '') {  
  81.                 alert('Blank is not allow');  
  82.                 editedPhone.focus();  
  83.                 returnVal = false;  
  84.                 return returnVal;  
  85.             }  
  86.             else if (editedAddress == '') {  
  87.                 alert('Blank is not allow');  
  88.                 editedAddress.focus();  
  89.                 returnVal = false;  
  90.                 return returnVal;  
  91.             }  
  92.             var returnVal = true;  
  93.             return returnVal;  
  94.         }  
  95.   
  96.         function EditEmployeeData() {        // Edit Employee Data   
  97.   
  98.             var editedName = $('#txEditedName').val();  
  99.             var editedEmail = $('#txEditedEmail').val();  
  100.             var editedPhone = $('#txEditedPhone').val();  
  101.             var editedAddress = $('#txEditedAddress').val();  
  102.             var hfEditedId = $('#hfEditedId').val();  
  103.             var hfActionMode = $('#hfActionMode').val();  
  104.   
  105.             // Blank validation check  
  106.             if (BlankValidation(editedName, editedEmail, editedPhone, editedAddress) == true) {  
  107.   
  108.                 // call ajax JSON method          
  109.                 $.ajax({  
  110.                     type: "POST",  
  111.                     url: "hr_webservice.asmx/EditEmployeeData",  
  112.                     data: "{ 'employeeId': '" + hfEditedId  
  113.                             + "','editedName': '" + editedName  
  114.                             + "', 'editedEmail': '" + editedEmail  
  115.                             + "','editedPhone':'" + editedPhone  
  116.                             + "','editedAddress':'" + editedAddress  
  117.                             + "','action_mode':'" + hfActionMode + "'}",  
  118.                     contentType: "application/json; charset=utf-8",  
  119.                     dataType: "json",  
  120.                     success: function (response) {  
  121.                         BindTable(JSON.parse(response.d));  
  122.                     },  
  123.                     failure: function (msg) {  
  124.                         alert(msg);  
  125.                     }  
  126.                 });  
  127.                 ResetText();  
  128.             }  
  129.         }  
  130.   
  131.         function ResetText() {                               // Method for Reset Control  
  132.             var editedName = $('#txEditedName').val('');  
  133.             var editedEmail = $('#txEditedEmail').val('');  
  134.             var editedPhone = $('#txEditedPhone').val('');  
  135.             var editedAddress = $('#txEditedAddress').val('');  
  136.             var hfEditedId = $('#hfEditedId').val('');  
  137.             var hfActionMode = $('#hfActionMode').val('NEW');  
  138.             var btnEntry = $('#btnEntry').val('New Entry');  
  139.         }  
  140.   
  141.         function EditMode(empId, empName, empEmail, empPhone, empAddress) {  
  142.   
  143.             var hfEditedId = $('#hfEditedId').val(empId);  
  144.             var txEditedName = $('#txEditedName').val(empName);  
  145.             var txEditedEmail = $('#txEditedEmail').val(empEmail);  
  146.             var txEditedPhone = $('#txEditedPhone').val(empPhone);  
  147.             var txEditedAddress = $('#txEditedAddress').val(empAddress);  
  148.             var hfActionMode = $('#hfActionMode').val('UPDATE');  
  149.             var btnEntry = $('#btnEntry').val('Update');  
  150.         }  
  151.   
  152.         function DeleteMode(deleteEmpId) {  
  153.   
  154.             var agree = confirm("Are you sure you want to delete this information ?");  
  155.             if (agree) {  
  156.                 $.ajax({  
  157.                     type: "POST",  
  158.                     url: "hr_webservice.asmx/DeleteEmployeeData",  
  159.                     data: "{ 'employeeId': '" + deleteEmpId + "'}",  
  160.                     contentType: "application/json; charset=utf-8",  
  161.                     dataType: "json",  
  162.                     success: function (response) {  
  163.                         BindTable(JSON.parse(response.d));  
  164.                     },  
  165.                     failure: function (msg) {  
  166.                         alert(msg);  
  167.                     }  
  168.                 });  
  169.             }  
  170.         }  
  171.   
  172.         function BindTable(Employees) {  
  173.   
  174.             var root = document.getElementById('mydiv');  
  175.             try {  
  176.                 var tblId = document.getElementById('tblGridValue');  
  177.                 if (tblId != null) {  
  178.                     root.removeChild(tblId);  
  179.                 }  
  180.             }  
  181.             catch (e) {  
  182.   
  183.             }  
  184.             var tab = document.createElement('table');  
  185.             tab.setAttribute("id", "tblGridValue");  
  186.             tab.setAttribute("class", "tableStyle");  
  187.             tab.setAttribute("cellspacing", "3px");  
  188.             var tbo = document.createElement('tbody');  
  189.             var row, cell;  
  190.             // the list object now extract the value for each row  
  191.             $.each(Employees, function (index, employee) {  
  192.                 row = document.createElement('tr');  
  193.                 row.setAttribute("class", "tableRaw");  
  194.   
  195.   
  196.                 // the object of LIST is now extract the each cell of row  
  197.   
  198.                 for (var j = 0; j < 5; j++) {  
  199.                     cell = document.createElement('td');  
  200.                     cell.setAttribute("width", "200px");  
  201.                     var empId = employee.id;  
  202.                     var empName = employee.Name;  
  203.                     var empEmail = employee.Email;  
  204.                     var empPhone = employee.Phone;  
  205.                     var empAddress = employee.Address;  
  206.                     if (j == 0) {  
  207.   
  208.                         //Create an input type dynamically.  
  209.                         var hiddenId = document.createElement("input");  
  210.                         //Assign different attributes to the element.  
  211.                         hiddenId.setAttribute("type", "hidden");  
  212.                         hiddenId.setAttribute("id", "hfRow_" + employee.Id);  
  213.                         hiddenId.setAttribute("value", employee.Id);  
  214.                         cell.appendChild(hiddenId);  
  215.                         cell.appendChild(document.createTextNode(employee.Name));  
  216.                     }  
  217.                     else if (j == 1) {  
  218.                         var spanValue = document.createElement("span");  
  219.                         cell.setAttribute("width", "200px");  
  220.                         spanValue.setAttribute("display", "inline-block");  
  221.                         spanValue.appendChild(document.createTextNode(employee.Email));  
  222.                         cell.appendChild(spanValue);  
  223.                     }  
  224.                     else if (j == 2) {  
  225.                         cell.setAttribute("width", "200px");  
  226.                         cell.appendChild(document.createTextNode(employee.Phone));  
  227.                     }  
  228.                     else if (j == 3) {  
  229.                         cell.setAttribute("width", "200px");  
  230.                         cell.appendChild(document.createTextNode(employee.Address));  
  231.                     }  
  232.                     else if (j == 4) {  
  233.   
  234.                         // in this state loop generates Edit and Delete button for each row  
  235.                         //  
  236.                         var element = document.createElement("img");  
  237.                         element.setAttribute("src", "images/edit-icon.gif");  
  238.                         element.setAttribute("width", "15px");  
  239.                         cell.setAttribute("width", "100px");  
  240.   
  241.   
  242.                         // This loop also adding a click event EditMode()                         
  243.                         element.setAttribute("onclick", "EditMode('"  
  244.                                                             + empId + "','"  
  245.                                                             + empName + "','"  
  246.                                                             + empEmail + "','"  
  247.                                                             + empPhone + "','"  
  248.                                                             + empAddress + "')");  
  249.   
  250.                         cell.appendChild(element);  
  251.   
  252.   
  253.                         // Same way the row created Delete button                        
  254.                         var elementDelete = document.createElement("img");  
  255.                         elementDelete.setAttribute("src", "images/DeleteRed.png");  
  256.                         elementDelete.setAttribute("width", "15px");  
  257.   
  258.   
  259.                         // Also created the Delete Method in onclick event                         
  260.                         elementDelete.setAttribute("onclick", "return DeleteMode('" + empId + "')");  
  261.                         cell.appendChild(elementDelete);  
  262.                     }  
  263.                     row.appendChild(cell);  
  264.                 }  
  265.                 tbo.appendChild(row);  
  266.             });  
  267.             tab.appendChild(tbo);  
  268.             root.appendChild(tab);  
  269.         }  
  270.   
  271.     </script>  
  272. </head>  
  273.   
  274.   
  275. <body style="background-color: #cccccc;">  
  276.     <form id="form1" runat="server">  
  277.   
  278.         <div style="float: left; background-color: White; width: 1050px;">  
  279.   
  280.             <div style="background-color: White">  
  281.                 <h3>Sample Task Grid (Add/Edit/Update/Delete)Using web Service with JSON</h3>  
  282.             </div>  
  283.             <div style="width: 1000px; background-color: White;">  
  284.   
  285.                 <table class="tableStyle" width="1000px">  
  286.   
  287.                     <tr style="border: 1px solid black;">  
  288.   
  289.                         <td style="width: 200px">  
  290.                             <asp:HiddenField ID="hfEditedId" runat="server" />  
  291.                             <asp:HiddenField ID="hfActionMode" Value="NEW" runat="server" />  
  292.                             <input id="txEditedName" type="text" name="name" value=" " style="width: 98%" />  
  293.                         </td>  
  294.   
  295.                         <td style="width: 200px">  
  296.                             <input id="txEditedEmail" type="text" name="name" value=" " style="width: 98%" />  
  297.                         </td>  
  298.   
  299.                         <td style="width: 200px">  
  300.                             <input id="txEditedPhone" type="text" name="name" value=" " style="width: 98%" />  
  301.                         </td>  
  302.   
  303.                         <td style="width: 200px">  
  304.                             <input id="txEditedAddress" type="text" name="name" value=" " style="width: 98%" />  
  305.                         </td>  
  306.   
  307.                         <td style="width: 100px; padding-left: 10px;">  
  308.                             <div style="padding-bottom: 5px">  
  309.                                 <input id="btnEntry" type="button" class="button" name="editAjaxGrid" value="New Entry" style="width: 80px" onclick="EditEmployeeData()" />  
  310.                             </div>  
  311.   
  312.                             <div style="padding-bottom: 5px">  
  313.                                 <input id="Button1" type="button" class="button" name="reset" value="Reset" style="width: 80px" onclick=" ResetText()" />  
  314.                             </div>  
  315.                         </td>  
  316.                     </tr>  
  317.   
  318.   
  319.                     <tr class="tableHeader">  
  320.                         <td style="width: 200px">Name</td>  
  321.                         <td style="width: 200px">Email Address</td>  
  322.                         <td style="width: 200px">Mobile Number</td>  
  323.                         <td style="width: 200px">Address</td>  
  324.                         <td style="width: 100px; padding-left: 10px;">Action</td>  
  325.                     </tr>  
  326.   
  327.                 </table>  
  328.   
  329.                 <div id="mydiv"></div>  
  330.             </div>  
  331.         </div>  
  332.     </form>  
  333. </body>  
  334. </html>  
CRUD Oparetion using web service.
 
Find database script attached.