Post
Ask Question

CRUD Operations Using AJAX/JSON

This article will explain CRUD operations using AJAX calls and returning the JSON data with the help of jQuery. By using this we don't need to reload the whole page to perform any of the CRUD operations.

In Code Behind, every event is a postback. This means for every button click, the whole page is reloaded. So it's very difficult for programmers to maintain their state on every operation call. To overcome this in jQuery, an Ajax call is taking place. The main advantage of this is that it only calls what you require and what you want to change. There is no need for PostBack to reload the whole page. So, let us start to learn something about this by doing CRUD operations.  

First, we will understand what an AJAX call is. AJAX is not a programming language. It is used to exchange the data between the server and the browser. It uses an XMLHttpRequest object in your browser to request the data from the server. By default, AJAX is an asynchronous call which means there is no delay between the requests. It's a method of jQuery having many parameters. We will see some of the main parameters to call.

Prerequisite File
  1. add jquery =  <script src="http://ajax.googleapis.com/ajax/libs/jquery/1.11.0/jquery.min.js"></script>  
  2. add bootstrap = <link rel="stylesheet" href="http://netdna.bootstrapcdn.com/bootstrap/3.0.3/css/bootstrap.min.css" />   

Let's start from a simple page having two fields,

  1. <div class="form-group">  
  2.     <label class="control-label" for="input-name"> Category Code <sup style="color: Red">*</sup>  
  3.     </label>  
  4.     <input type="text" name="filter_name" placeholder="Category Code" id="c_code" class="form-control noslash" autocomplete="off" maxlength="50" />  
  5. </div>  
  6. <div class="form-group">  
  7.     <label class="control-label" for="input-email"> Category Name <sup style="color: Red">*</sup>  
  8.     </label>  
  9.     <input type="text" name="filter_email" value="" placeholder="Category Name" id="c_name" class="form-control noslash" autocomplete="off" maxlength="100" />  
  10. </div>  
  11. <div class="col-lg-4">  
  12.     <button type="button" id="reset" class="btn btn-primary pull-right"> Reset </button>  
  13.     <button type="button" id="save" class="btn btn-primary pull-right"> Save </button>  
  14.     <button type="button" id="update" class="btn btn-primary pull-right"> Update </button>  
  15. </div>  
CRUD Operations Using AJAX/JSON 

 

Now, using jQuery we call a click method to write document.ready. Get the code and name of the  field value and call the AJAX method- 
  1. <script type="text/javascript" language="javascript">    
  2.         $(document).ready(function () {    
  3.     
  4.             bind();  
  5. ----------------------------------------------------------- Insertion --------------------------------------------------------------
  6.             $('#save').click(function () {    
  7.                 var code = $('#c_code').val();    
  8.                 var name = $('#c_name').val();    
  9.                 if (code == "") {    
  10.                     alert('Please Fill Category Code !!!');    
  11.                     return false;    
  12.                 }    
  13.                 if (name == "") {    
  14.                     alert('Please Fill Category Name !!!');    
  15.                     return false;    
  16.                 }    
  17.             var data = { code: code, name: name };    
  18.             var stringData = JSON.stringify(data);    
  19.             $.ajax({    
  20.                 type: "POST",    
  21.                 url: "category.aspx/insertion",    
  22.                 data: stringData,    
  23.                 contentType: "application/json; charset=utf-8",    
  24.                 dataType: "json",    
  25.                 success: OnSucces,    
  26.                 error: OnError    
  27.             });     
  28.                 function OnSucces(response) {    
  29.                     if (response == 1) {    
  30.                         alert('Category Added Successfully !!!');    
  31.                         reset();    
  32.                     }    
  33.                     else {    
  34.                         alert(response);    
  35.                     }    
  36.                 }    
  37.                 function OnError(response) {    
  38.                     alert(response);    
  39.                                     }    
  40.             });  
  41.       });  
  42. </script>  
Let's discuss in brief the AJAX parameters used in this call,

Type - Mention the type we used. Here in this, we mention POST and we use GET also
  • GET - Requests data from a specified resource
  • POST - Submits data to be processed to a specified resource
URL - puts the URL of the method either from the same application or from a different application. 

Data - parameters need to send to web method. Here we send code and name parameters and while sending data, data has to be a string so we use JSON.stringify()  to convert a javascript object to string.

ContentType - the type of data you send to a server.

DataType - the type of data you receive from the server.

Success - called when the request has succeeded.

Error-  called when the request fails.

WEB Method For Insert
  1. [System.Web.Services.WebMethod(EnableSession = true), ScriptMethod(ResponseFormat = ResponseFormat.Json)]  
  2.     public static string insertion(string code, string name)  
  3.     {  
  4.         string r = string.Empty;  
  5.         SqlConnection con = (SqlConnection)HttpContext.Current.Session["conn"];  
  6.         try  
  7.         {  
  8.             using (SqlCommand cmd = new SqlCommand())  
  9.             {  
  10.                 con.Open();  
  11.                 cmd.CommandText = "insert into category(category_code,category_name,uid)values(@code,@name,@uid)";  
  12.                 cmd.Parameters.AddWithValue("@code", code);  
  13.                 cmd.Parameters.AddWithValue("@name", name);  
  14.                 cmd.Parameters.AddWithValue("@uid", HttpContext.Current.Session["uid"].ToString());  
  15.                 cmd.Connection = con;  
  16.                 cmd.ExecuteNonQuery();  
  17.                 con.Close();  
  18.             }  
  19.             return "1";  
  20.         }  
  21.         catch (SqlException exx)  
  22.         {  
  23.             string ab = string.Empty;  
  24.             if (exx.Number == 2627)  
  25.             {  
  26.                 r = "Category Code or Name already exist !!!";  
  27.   
  28.             }  
  29.             return r;  
  30.   
  31.         }  
  32.         catch (Exception ex)  
  33.         {  
  34.             return ex.Message;  
  35.         }  
  36.         finally  
  37.         {  
  38.             if (con.State == ConnectionState.Open)  
  39.                 con.Close();  
  40.   
  41.         }    
  42.     }  
Above, we can see how data is inserted through the AJAX call. Now, let's see how we can bind them. We call a bind() method in the ready function, which  means  we can see the inserted data won page load.

Below is the table header structure and the bind function,
  1. <table class="table table-striped table-bordered" cellspacing="0" width="100%" id="bind">  
  2.                                                                 <thead>  
  3.                                                                     <tr>  
  4.                                                                           
  5.   
  6.                                                                         <td class="text-left" style="width:30%">Category Code</td>  
  7.                                                                         <td class="text-left" style="width:40%">Category Name</td>  
  8.                                                                         <td class="text-right" style="width:5%">Action</td>  
  9.                                                                     </tr>  
  10.                                                                 </thead>  
  11.                                                                   
  12.                                                             </table>  
  1. --------------------------------------------------Bind-----------------------------------------------------
  2. function bind() {  
  3.       $.ajax({  
  4.           type: "POST",  
  5.           url: "category.aspx/binds",  
  6.           data: stringData,  
  7.           contentType: "application/json; charset=utf-8",  
  8.           dataType: "json",  
  9.           success: OnSucces,  
  10.           error: OnError  
  11.       });    
  12.       function onSuccess(response) {  
  13.        
  14.           var obj = $.parseJSON(response);  
  15.           var str = '';  
  16.           var inc = 1;  
  17.             
  18.   
  19.           if (obj.length > 0) {  
  20.     
  21.               var services = obj[0].Table1;  
  22.   
  23.               inc = 2;  
  24.               $.each(services, function (index, value) {  
  25.   
  26.                   $('#iid').val(inc);  
  27.                   str = str + "<tr>";  
  28.                   str = str + "<td >";  
  29.   
  30.                   str = str + "<span  >" + services[index].category_code + "</span>";  
  31.                   str = str + "<input type='hidden'  Value='" + services[index].category_id + "'>";  
  32.                   str = str + "</td>";  
  33.                   str = str + "<td >";  
  34.                   str = str + "<span  >" + services[index].category_name + "</span>";  
  35.                   str = str + "</td>";  
  36.                   str = str + "<td >";  
  37.                   str = str + "<a href='#' data-toggle='tooltip' class='btn btn-primary' id='" + services[index].category_id + "' onclick='edit(this)' data-original-title='Edit'><i class='fa fa-pencil'></i></a>";
  38.                   str = str + "<a href='#' data-toggle='tooltip' class='btn btn-
  39. primary' id='" + services[index].category_id + "' onclick='delete(this)' data-original-title='Delete'><i class='fa fa-trash'></i></a>";  
  40.                   str = str + "</td>";  
  41.                   
  42.                   str = str + "</tr>";  
  43.   
  44.               });  
  45.               $('#bind').append(str);  
  46.              
  47.              
  48.           }  
  49.   
  50.           else {  
  51.   
  52.               $("[id*=bind] tr").remove();  
  53.   
  54.           }  
  55.           $('#load').hide();  
  56.           
  57.   
  58.       }  
  59.       function onError(response) {  
  60.           alert(response.d);  
  61.       }  
  62.   
  63.   
  64.   }  
Web Method for Bind the data 
  1. [System.Web.Services.WebMethod(EnableSession = true), ScriptMethod(ResponseFormat = ResponseFormat.Json)]  
  2.     public static string binds()  
  3.     {   
  4.         List<Dictionary<string, Object>> tables = new List<Dictionary<string, object>>();  
  5.         //to create tables with table name and their rows.  
  6.         List<Dictionary<string, Object>> rows = null;  
  7.         //to hold single table with rows  
  8.         Dictionary<string, Object> tab = new Dictionary<string, object>();  
  9.         // to hold single row of each table.  
  10.         Dictionary<string, Object> row = null;  
  11.           
  12.         System.Web.Script.Serialization.JavaScriptSerializer serializer = new System.Web.Script.Serialization.JavaScriptSerializer();  
  13.         SqlConnection con = null;  
  14.         con = (SqlConnection)HttpContext.Current.Session["conn"];  
  15.         string g3 = "select category_id,category_code,category_name from category";  
  16.   
  17.         SqlCommand cmdgla = new SqlCommand(g3, con);  
  18.         cmdgla.Connection = con;  
  19.   
  20.         SqlDataAdapter adpgla = new SqlDataAdapter(cmdgla);  
  21.         DataTable ndt = new DataTable();  
  22.         adpgla.Fill(ndt);  
  23.         DataTable dtadd = new DataTable();  
  24.         dtadd.Columns.Add("category_id");  
  25.         dtadd.Columns.Add("category_code");  
  26.         dtadd.Columns.Add("category_name");  
  27.         
  28.         DataRow dr1;  
  29.         for (int k = 0; k < ndt.Rows.Count; k++)  
  30.         {  
  31.   
  32.             dr1 = dtadd.NewRow();  
  33.             dtadd.Rows.Add(dr1);  
  34.   
  35.         }  
  36.         DataSet ds = new DataSet();  
  37.         ds.Tables.Add(ndt);  
  38.         foreach (DataTable dt in ds.Tables)  
  39.         {  
  40.             rows = new List<Dictionary<string, object>>();  
  41.   
  42.   
  43.             foreach (DataRow dr in ndt.Rows)  
  44.             {  
  45.                 row = new Dictionary<string, object>();  
  46.                 foreach (DataColumn dc in ndt.Columns)  
  47.                 {  
  48.                     row.Add(dc.ColumnName.Trim(), dr[dc]);  
  49.                 }  
  50.                 rows.Add(row);  
  51.             }  
  52.             tab.Add(ndt.TableName.Trim(), rows);  
  53.   
  54.         }  
  55.         tables.Add(tab);  
  56.         return serializer.Serialize(tables);  
  57.     }  
Let's see how we can bind our HTML table. After getting the response in success method first we need to convert the JSON string to JavaScript object. For that we use $.parseJSON(). After that, each row's data should be stored in a variable with edit and delete icon. This icon can be used to update and delete the row. After the end of the for-each loop, we append the variable to the table.

Now, we will see how we can delete the row. Call a delete function on onclick event and get the ID that deletes from the database. Make an AJAX call for delete method, pass that ID through the AJAX data parameters and in the web method run a delete query. After returning to the success method again bind the table so that a deleted row also gets deleted from the HTML table.
  1. ------------------------------------------------------Delete------------------------------------------------------
  2. function delete(idd) {  
  3.   
  4.                    var idd = idd.id;  
  5.   
  6.                    $.ajax({  
  7.                        type: "POST",  
  8.                        contentType: "application/json; charset=utf-8",  
  9.                        url: "category.aspx/delete",  
  10.                        data: "{'id':'" + idd + "'}",  
  11.                        dataType: "json",  
  12.                        success: function (response) {  
  13.                            if (response.d == "1") {  
  14.                                alert("Delete succssfully !!!");
                              $("[id*=bind] tr").remove();
                              bind();  
  15.                                clear();  
  16.                            }   
  17.                        }, error: function (response) {  
  18.                        }  
  19.                    });  
  20.                }  
Web Method For Delete
  1. [System.Web.Services.WebMethod(EnableSession = true), ScriptMethod(ResponseFormat = ResponseFormat.Json)]    
  2.     public static string Deletion(string id)    
  3.     {    
  4.         string r = string.Empty;    
  5.         SqlConnection con = (SqlConnection)HttpContext.Current.Session["conn"];    
  6.         try    
  7.         {    
  8.             using (SqlCommand cmd = new SqlCommand())    
  9.             {    
  10.                 con.Open();    
  11.                 cmd.CommandText = "delete from category where category_id=@id";    
  12.                 cmd.Parameters.AddWithValue("@id", id);    
  13.                 cmd.Connection = con;    
  14.                 cmd.ExecuteNonQuery();    
  15.                 con.Close();    
  16.             }    
  17.             return "1";    
  18.         }    
  19.         catch (Exception ex)    
  20.         {    
  21.             return ex.Message;    
  22.         }    
  23.         finally    
  24.         {    
  25.             if (con.State == ConnectionState.Open)    
  26.                 con.Close();    
  27.     
  28.         }      
  29.     }    

Now, for our last operation update we show our existing record by clicking on the edit icon of the respective row and then click the update button to update the record for that row.

  1. function edit(idd) {    
  2.     
  3.                    var idd = idd.id;    
  4.     
  5.                    $.ajax({    
  6.                        type: "POST",    
  7.                        contentType: "application/json; charset=utf-8",    
  8.                        url: "category.aspx/select",    
  9.                        data: "{'id':'" + idd + "'}",    
  10.                        dataType: "json",    
  11.                        success: function (response) {    
  12.                           $('#save').hide();
  13.                           var info = response.split('`');  
  14.                           $('#c_code').val(info[1]);  
  15.                           $('#c_name').val(info[2]);  
  16.                           $('#c_id').val(idd);  
  17.                           $('#update').show();
  18.   
  19.                        }, error: function (response) {    
  20.                        }    
  21.                    });    
  22.                }    
Web Method For Select,   
  1. [System.Web.Services.WebMethod(EnableSession = true), ScriptMethod(ResponseFormat = ResponseFormat.Json)]  
  2.    public static string select(string id)  
  3.    {  
  4.        string r = string.Empty;  
  5.        SqlConnection con = (SqlConnection)HttpContext.Current.Session["conn"];  
  6.        try  
  7.        {  
  8.            using (SqlCommand cmd = new SqlCommand())  
  9.            {  
  10.   
  11.                cmd.CommandText = "select category_code,category_name from category where category_id=@cid";                 
  12.                cmd.Parameters.AddWithValue("@cid", id);  
  13.                  
  14.                cmd.Connection = con;  
  15.                SqlDataAdapter adp = new SqlDataAdapter(cmd);  
  16.                DataTable dt = new DataTable();  
  17.                adp.Fill(dt);  
  18.                if (dt.Rows.Count > 0)  
  19.                {  
  20.                    string ccode = dt.Rows[0]["category_code"].ToString();  
  21.                    string cname = dt.Rows[0]["category_name"].ToString();  
  22.                    return ccode + "`" + cname ;  
  23.                }  
  24.            }  
  25.            return "1";  
  26.        }  
  27.        catch (SqlException exx)  
  28.        {  
  29.            string ab = string.Empty;  
  30.            if (exx.Number == 2627)  
  31.            {  
  32.                r = "Category Code or Name already exist !!!";  
  33.   
  34.            }  
  35.            return r;  
  36.   
  37.        }  
  38.        catch (Exception ex)  
  39.        {  
  40.            return ex.Message;  
  41.        }  
  42.        finally  
  43.        {  
  44.            if (con.State == ConnectionState.Open)  
  45.                con.Close();  
  46.   
  47.        }  
  48.   
  49.   
  50.    }  
Now, we want to update the existing record, so update button will be visible. See that the updated record is similar to the save button --  just pass the ID and change to update query.
  1. ----------------------------------------------------- Update -------------------------------------------------------
  2. $('#update').click(function () {      
  3.                var code = $('#c_code').val();      
  4.                var name = $('#c_name').val();      
  5.                if (code == "") {      
  6.                    alert('Please Fill Category Code !!!');      
  7.                    return false;      
  8.                }      
  9.                if (name == "") {      
  10.                    alert('Please Fill Category Name !!!');      
  11.                    return false;      
  12.                }  
  13.                var id =$('#c_id').val();  
  14.                var data = { code: code, name: name,id:id };      
  15.                var stringData = JSON.stringify(data);      
  16.            $.ajax({      
  17.                type: "POST",      
  18.                url: "category.aspx/updation",      
  19.                data: stringData,      
  20.                contentType: "application/json; charset=utf-8",      
  21.                dataType: "json",      
  22.                success: OnSucces,      
  23.                error: OnError      
  24.            });       
  25.                function OnSucces(response) {      
  26.                    if (response == 1) {      
  27.                        alert('Category Updated Successfully !!!');      
  28.                        reset();      
  29.                    }      
  30.                    else {      
  31.                        alert(response);      
  32.                    }      
  33.                }      
  34.                function OnError(response) {      
  35.                    alert(response);      
  36.                                    }      
  37.            });  
Web Method For Updation
  1. [System.Web.Services.WebMethod(EnableSession = true), ScriptMethod(ResponseFormat = ResponseFormat.Json)]  
  2.     public static string updation(string id, string code, string name)  
  3.     {  
  4.         string r = string.Empty;  
  5.         SqlConnection con = (SqlConnection)HttpContext.Current.Session["conn"];  
  6.         try  
  7.         {  
  8.             using (SqlCommand cmd = new SqlCommand())  
  9.             {  
  10.                 con.Open();  
  11.                 cmd.CommandText = "update category set category_code=@code,category_name=@name,userid=@uid where category_id=@cid";  
  12.                 cmd.Parameters.AddWithValue("@code", code);  
  13.                 cmd.Parameters.AddWithValue("@name", name);  
  14.                 cmd.Parameters.AddWithValue("@cid", id); 
  15.                 cmd.Parameters.AddWithValue("@uid", HttpContext.Current.Session["uid"].ToString());  
  16.                 cmd.Connection = con;  
  17.                 cmd.ExecuteNonQuery();  
  18.                 con.Close();  
  19.             }  
  20.             return "1";  
  21.         }  
  22.         catch (SqlException exx)  
  23.         {  
  24.             string ab = string.Empty;  
  25.             if (exx.Number == 2627)  
  26.             {  
  27.                 r = "Category Code or Name already exist !!!";  
  28.   
  29.             }  
  30.             return r;  
  31.   
  32.         }  
  33.         catch (Exception ex)  
  34.         {  
  35.             return ex.Message;  
  36.         }  
  37.         finally  
  38.         {  
  39.             if (con.State == ConnectionState.Open)  
  40.                 con.Close();  
  41.   
  42.         }  
  43.   
  44.   
  45.     }  

These are all the operations. By following this article, you can easily do the CRUD operations using JSON. No need to reload the whole page just for the insertion or any other operation. Please write in the comment section if you like this article so that I can try to make the next article better than this and if you face any difficulty please ask for help. 


Similar Articles