CRUD Operations Using Ajax - Part 1

CREATE TABLE [dbo].[TblUser](    
    [EmpId] [int] IDENTITY(1,1) NOT NULL,    
    [Fname] [nvarchar](30) NOT NULL,    
    [Mname] [nvarchar](30) NULL,    
    [Lname] [nvarchar](30) NULL,    
    [Gender] [nchar](10) NOT NULL,    
    [EMail] [nvarchar](50) NOT NULL,    
    [DOB] [nvarchar](30) NOT NULL,    
    [MaritalStatus] [nvarchar](30) NOT NULL,    
    [Hobbies] [nvarchar](30) NULL,    
    [Telephone] [nvarchar](30) NULL,    
    [Mobile] [nvarchar](30) NULL,    
    [Address] [nvarchar](300) NOT NULL,    
    [PinCode] [nvarchar](30) NOT NULL,    
    [State] [nvarchar](30) NOT NULL,    
    [Nationality] [nvarchar](30) NOT NULL,    
    [DOJ] [nvarchar](30) NULL,    
    [CreatedDate] [datetime] NOT NULL,    
    [ModifiedDate] [datetime] NOT NULL,    
 CONSTRAINT [PK_TblUser] PRIMARY KEY CLUSTERED     
(    
    [EmpId] ASC    
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]    
) ON [PRIMARY]

<div class="col-lg-8">  
    <div class="panel panel-primary">  
        <div class="panel-heading">  
            <h3 class="panel-title"><i class="fa fa-bar-chart-o"></i>Employee Details </h3>  
        </div>  
        <div class="panel-body">  
            <div class="form-group col-lg-4">  
                <label>First Name</label>  
                <input type="text" name="FirstName" id="FirstName" class="form-control" placeholder="First Name" required="" />  
            </div>  
            <div class="form-group col-lg-4">  
                <label>Middle Name</label>  
                <input type="text" name="MiddleName" id="MiddleName" class="form-control" placeholder="Kumar" required="" />  
            </div>  
            <div class="form-group col-lg-4 ">  
                <label>Surname</label>  
                <input type="text" name="Surname" id="Surname" class="form-control" placeholder="Gupta" required="" />  
            </div>  
            <div class="clearfix"></div>  
            <div class="form-group col-lg-6">  
                <label>Email ID</label>  
                <input type="email" name="EmailId" id="EmailId" class="form-control" placeholder="[email protected]" required="" />  
            </div>  
            <div class="form-group col-lg-6">  
                <label>Date of Birth </label>  
                <input type="date" name="Dob" id="Dob" min="1920-01-02" class="form-control datepicker" required="" />  
            </div>  
            <div class="form-group col-lg-6">  
                <label>MaritalStatus</label>  
                <select name="MaritalStatus" id="MaritalStatus" class="form-control" required="">  
                    <option value="" disabled="disabled">-- Select -- </option>  
                    <option value="Single">Single</option>  
                    <option value="Married">Married</option>  
                </select>  
            </div>  
            <div class="form-group col-lg-6">  
                <label>Hobbies</label>  
                <input type="text" name="Hobbies" id="Hobbies" class="form-control" placeholder="Football, Cricket etc." />  
            </div>  
  
            <div class="form-group col-lg-6">  
                <label>Home Telephone</label>  
                <input type="text" name="TelephoneNo" id="TelephoneNo" class="form-control" placeholder="1234567890" />  
            </div>  
  
            <div class="form-group col-lg-6">  
                <label>Mobile</label>  
                <input type="tel" name="MobileNo" id="MobileNo" class="form-control" required="" placeholder="0987654321" />  
            </div>  
            <div class="form-group col-lg-12">  
                <label>Residential Address</label>  
                <textarea rows="2" name="ResidentialAddress" id="ResidentialAddress" class="form-control" required="">  
                    <div class="form-group col-lg-6 ">  
                        <label>Pin Code</label>  
                        <input name="PinCode" id="PinCode" class="form-control" placeholder="999999" type="text">  
                    </div>  
  
                    <div class="form-group col-lg-6">  
                        <label>State</label>  
                        <select name="State" id="State" class="form-control" required="">  
                            <option value="" disabled="disabled">-- Select -- </option>  
                            <option value="Maharashtra">Maharastra</option>  
                            <option value="Assam">Assam</option>  
                            <option value="UP">UP</option>  
                            <option value="Gujarat">Gujarat</option>  
                            <option value="AP">Andhra Pradesh</option>  
                        </select>  
                    </div>  
                    <div class="form-group col-lg-6">  
                        <label>Nationality</label>  
                        <input name="title" id="Nationality" class="form-control" placeholder="Indian" type="text">  
                    </div>  
                    <div class="form-group col-lg-6">  
                        <label>Date of Joining</label>  
                        <input name="Doj" id="Doj" class="form-control datepicker" required="" type="date">  
                    </div>  
                    <div class="form-group col-lg-8">  
                        <div style="float: right">  
                            <input value="Cancel" id="BtnCancel" class="btn btn-primary" type="button">  
                            <input class="btn btn-primary" name="submitButton" id="btnSave" value="Save" type="button">  
                        </div>  
                    </div>  
            </div>  
        </div>  
    </div>  
</div>

public class Employee  
{  
   public int EmpId;  
   public string FName;  
   public string LName;  
   public string MName;  
   public string Email;  
   public DateTime Dob;  
   public string MaritalStatus;  
   public string Hobbies;  
   public string HomeMobile;  
   public string OfficeMobile;  
   public string Address;  
   public string Pincode;  
   public string State;  
   public string Nationality;  
   public DateTime Doj;  
   public DateTime CreatedDateTime;  
   public DateTime ModifiedDateTime;  
}

[WebMethod]  
public static void SaveUser(Employee objEmployee) //Insert data in database  
{  
    using (var con = new SqlConnection(Constr))  
    {  
        using (var cmd = new SqlCommand("INSERT INTO TblUser VALUES(@Fname, @Mname,@Lname,@Email,@Dob,@MStatus,@Hobbies,"+
                   "@HMobile,@OMobile,@Address,@Pin,@State,@Nationality,@Doj,@CreatedDate,@ModifiedDate)"))  
        {  
            cmd.CommandType = CommandType.Text;  
            cmd.Parameters.AddWithValue("@Fname", objEmployee.FName);  
            cmd.Parameters.AddWithValue("@Mname", objEmployee.MName);  
            cmd.Parameters.AddWithValue("@Lname", objEmployee.LName);  
            cmd.Parameters.AddWithValue("@Email", objEmployee.Email);  
            cmd.Parameters.AddWithValue("@Dob", objEmployee.Dob);  
            cmd.Parameters.AddWithValue("@MStatus", objEmployee.MaritalStatus);  
            cmd.Parameters.AddWithValue("@Hobbies", objEmployee.Hobbies);  
            cmd.Parameters.AddWithValue("@OMobile", objEmployee.OfficeMobile);  
            cmd.Parameters.AddWithValue("@HMobile", objEmployee.HomeMobile);  
            cmd.Parameters.AddWithValue("@Address", objEmployee.Address);  
            cmd.Parameters.AddWithValue("@Pin", objEmployee.Pincode);  
            cmd.Parameters.AddWithValue("@State", objEmployee.State);  
            cmd.Parameters.AddWithValue("@Nationality", objEmployee.Nationality);  
            cmd.Parameters.AddWithValue("@Doj", objEmployee.Doj);  
            cmd.Parameters.AddWithValue("@CreatedDate",DateTime.Now);  
            cmd.Parameters.AddWithValue("@ModifiedDate", DateTime.Now);  
            cmd.Connection = con;  
            con.Open();  
            cmd.ExecuteNonQuery();  
            con.Close();  
        }  
    }  
}

Now we need to call the method SaveUser() through Ajax to save data in the database.

<script type="text/javascript">  
    $(function() {  
        $("#btnSave").click(function() {  
            var user = {};  
            user.FName = $("#FirstName").val(); // FName as per name of Employee Class  
            user.LName = $("#Surname").val();  
            user.MName = $("#MiddleName").val();  
            user.Gender = $("#Male").val();  
            user.Email = $("#EmailId").val();  
            user.Dob = $("#Dob").val();  
            user.MaritalStatus = $("#MaritalStatus").val();  
            user.Hobbies = $("#Hobbies").val();  
            user.HomeMobile = $("#TelephoneNo").val();  
            user.OfficeMobile = $("#MobileNo").val();  
            user.Address = $("#ResidentialAddress").val();  
            user.Pincode = $("#PinCode").val();  
            user.State = $("#State").val();  
            user.Nationality = $("#Nationality").val();  
            user.Doj = $("#Doj").val();  
            user.CreatedDateTime = new Date();  
            user.ModifiedDateTime = new Date();  
            $.ajax({  
                type: "POST",  
                url: "Default.aspx/SaveUser", // Default.aspx is the page and SaveUser is the WebMethod to save data in database  
                data: '{objEmployee: ' + JSON.stringify(user) + '}', //objEmployee is the object of Employee Class defined in .cs  
                dataType: "json",  
                contentType: "application/json; charset=utf-8",  
                success: function() {  
                    alert("User has been added successfully.");  
                    getDetails(); //This method is to bind the added data into my HTML Table through Ajax call instead of page load  
                    // window.location.reload(); we can also use this to load window to show updated data  
                },  
                error: function() {  
                    alert("Error while inserting data");  
                }  
            });  
            return false;  
        });  
    });  
</script>

After this point we have our inserted data in the database.

Display Data in Table Structure(like Grid View):

<table id="dataTables-example" class="table table-striped table-bordered table-hover dataTable no-footer" role="grid" aria-describedby="dataTables-example_info">  
    <thead>  
        <tr role="row">  
            <th class="sorting_asc" tabindex="0" aria-controls="dataTables-example" rowspan="1" colspan="1" style="width: 175px;" aria-sort="ascending" aria-label="Rendering engine: activate to sort column descending">Name</th>  
            <th class="sorting" tabindex="0" aria-controls="dataTables-example" rowspan="1" colspan="1" style="width: 203px;" aria-label="Browser: activate to sort column ascending">Email Id</th>  
            <th class="sorting" tabindex="0" aria-controls="dataTables-example" rowspan="1" colspan="1" style="width: 184px;" aria-label="Platform(s): activate to sort column ascending">Mobile(H)</th>  
            <th class="sorting" tabindex="0" aria-controls="dataTables-example" rowspan="1" colspan="1" style="width: 150px;" aria-label="Engine version: activate to sort column ascending">Mobile(O)</th>  
            <th class="sorting" tabindex="0" aria-controls="dataTables-example" rowspan="1" colspan="1" style="width: 108px;" aria-label="CSS grade: activate to sort column ascending">Joining Date</th>  
            <th class="sorting" tabindex="0" aria-controls="dataTables-example" rowspan="1" colspan="1" style="width: 108px;" aria-label="CSS grade: activate to sort column ascending">Birth Date</th>  
            <th class="sorting" tabindex="0" aria-controls="dataTables-example" rowspan="1" colspan="2" style="width: 108px;" aria-label="CSS grade: activate to sort column ascending">Action</th>  
        </tr>  
    </thead>  
    <tbody>  
        <% for (var data = 0; data < TableData.Rows.Count; data++)    
                                                       { %>  
            <tr class="gradeA odd" role="row">  
                <td class="sorting_1">  
                    <%=TableData.Rows[data]["FName"]%>  
                </td>  
                <td>  
                    <%=TableData.Rows[data]["EMail"]%>  
                </td>  
                <td>  
                    <%=TableData.Rows[data]["Telephone"]%>  
                </td>  
                <td>  
                    <%=TableData.Rows[data]["Mobile"]%>  
                </td>  
                <td>  
                    <%=TableData.Rows[data]["DOJ"]%>  
                </td>  
                <td>  
                    <%=TableData.Rows[data]["DOB"]%>  
                </td>  
                <td>  
                    <input type="button" class="btn btn-primary editButton" data-id="<%=TableData.Rows[data][" EmpId "] %>" data-toggle="modal" data-target="#myModal" name="submitButton" id="btnEdit" value="Edit" />  
                </td>  
                <td>  
                    <input type="button" class="btn btn-primary" name="submitButton" id="btnDelete" value="Delete" />  
                </td>  
            </tr>  
            <% } %>  
    </tbody>  
</table>

Ajax Call to Show Data

function getDetails() {  
    $.ajax({  
        type: "POST",  
        contentType: "application/json; charset=utf-8",  
        url: "Default.aspx/GetData", //Default.aspx is page and GetData is the WebMethod  
        data: {},  
        dataType: "json",  
        success: function(data) {  
            $('#dataTables-example tbody').remove(); // Every time I am removing the body of Table and applying loop to display data  
            //console.log(data.d);    
            for (var i = 0; i < data.d.length; i++) {  
                $("#dataTables-example").append(  
                    "<tr><td>" + data.d[i].FName + "</td><td>" + data.d[i].Email + "</td>" +  
                    "<td>" + data.d[i].HomeMobile + "</td>" + "<td>" + data.d[i].OfficeMobile + "</td>" +  
                    "<td>" + data.d[i].Doj + "</td>" + "<td>" + data.d[i].Dob + "</td>" +  
                    "<td>" + "<input type='button' class='btn btn-primary editButton' data-id='" + data.d[i].EmpId + "' data-toggle='modal' data-target='#myModal' name='submitButton' id='btnEdit' value='Edit' />" + "</td>" +  
                    "<td><input type='button' class='btn btn-primary' name='submitButton' id='btnDelete' value='Delete'/> </td></tr>");  
            }  
        },  
        error: function() {  
            alert("Error while Showing update data");  
        }  
    });  
} 

Note: We can use window.location.reload(); instead of getDetails(). But the getDetails() method will load the entire page to show the added data in the table.

WebMethod to Get Data to be binded to Table using Ajax call:

[WebMethod]  
public static Employee[] GetData() //Show the details of the data after insert in HTML Table  
{  
    var details = new List<Employee>();  
    using (var con = new SqlConnection(Constr))  
    {  
        const string query = "select * from TblUser order by EmpId desc";  
        using (var cmd = new SqlCommand(query, con))  
        {  
            using (var sda = new SqlDataAdapter())  
            {  
                cmd.Connection = con;  
                sda.SelectCommand = cmd;  
                TableData.Clear();  
                sda.Fill(TableData);  
                details.AddRange(from DataRow dtrow in TableData.Rows  
                    select new Employee  
                    {  
                        EmpId = Convert.ToInt32(dtrow["EmpId"]),  
                        FName = dtrow["Fname"].ToString(),  
                        Email = dtrow["EMail"].ToString(),  
                        HomeMobile = dtrow["Telephone"].ToString(),  
                        OfficeMobile = dtrow["Mobile"].ToString(),  
                        Doj = dtrow["DOJ"].ToString(),  
                        Dob = dtrow["DOB"].ToString()  
                    });  
            }  
        }  
    }  
    return details.ToArray();  
}

Method to Show data on Load:

protected void Page_Load(object sender, EventArgs e)    
{    
    if (!IsPostBack)    
    {    
        GetAllData();    
    }    
}    
private void GetAllData() //Get all the data and bind it in HTLM Table     
{    
    using (var con = new SqlConnection(Constr))    
    {    
        const string query = "select * from TblUser order by EmpId desc";    
        using (var cmd = new SqlCommand(query, con))    
        {    
            using (var sda = new SqlDataAdapter())    
            {    
                cmd.Connection = con;    
                sda.SelectCommand = cmd;    
                using (TableData)    
                {    
                    TableData.Clear();    
                    sda.Fill(TableData);    
                }    
            }    
        }    
    }    
}

After adding this the page will look like the following:

For PopUp Edit and Delete in HTML Table through Ajax Visit Crud Operations Ajax Part-2.

You will get complete attached CRUD Application in Part-2.

For Inserting multiple rows in the database from HTML table visit here.

Hope that helps, thanks for reading.