CRUD Operation in ASP.NET Web Applications Using HTTP Handler and jQuery

• Download source - 70.2 KB

Introduction

This article will demonstrate, step-by-step, how to create a basic application of CRUD (Create, Read, Update, Delete) Operations using HttpHandler in ASP.NET and Jquery Ajax API. To keep this application simple, I have not covered Validation and any other facets of a well designed application.

Requirements

Earlier, I mentioned that I have used Jquery Ajax API to make Ajax call of HttpHandler so for that, it is required to add reference of the Jquery File. You can get the latest Jquery File From the http://jquery.com or in case you are using VS2010, it will be available by default in Web projects.

Implementation

Then the first step is to create a new ASP.NET WebProjects.

Then create a folder name Script and Add Jquery File and one Commonfunction.js blank JavaScript File.

Add a DataBase

Add SqlServer Database File into the Projects, then create the following Products Table:

Now, add Class file named products which is used to perform database operation. Add the following code into that class File.

public class DbProducts
{
    SqlConnection _con = new SqlConnection
    (ConfigurationManager.ConnectionStrings[1].ConnectionString);

    public List<product> GetProductDetails()
    {
        try
        {
            List<product> _lstProducts = new List<product>();
            Product _Product = null;
            if (_con.State != System.Data.ConnectionState.Open)
                _con.Open();
            SqlCommand _cmd = _con.CreateCommand();
            _cmd.CommandText = "Select * From Products";
            SqlDataReader _Reader = _cmd.ExecuteReader();

            while (_Reader.Read())
            {
                _Product = new Product();
                _Product.ProductID = Convert.ToInt32(_Reader["ProductID"]);
                _Product.Name = _Reader["Name"].ToString();
                _Product.Unit = _Reader["Unit"].ToString();
                _Product.Qty = Convert.ToDecimal(_Reader["Qty"]);
                _lstProducts.Add(_Product);

            }
            return _lstProducts;
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            if (_con.State != System.Data.ConnectionState.Closed)
                _con.Close();
        }
    }

    public string InsertProduct(Product _P)
    {
        try
        {
            if (_con.State != System.Data.ConnectionState.Open)
                _con.Open();
            SqlCommand _cmd = _con.CreateCommand();
            _cmd.CommandText = "Insert Into Products(Name,Unit,Qty)Values
                    (@Name,@Unit,@Qty)";
            _cmd.Parameters.Add(new SqlParameter("@Name", _P.Name));
            _cmd.Parameters.Add(new SqlParameter("@Qty", _P.Qty));
            _cmd.Parameters.Add(new SqlParameter("@Unit", _P.Unit));
            if (_cmd.ExecuteNonQuery() > 0)
                return "Record Successfully Saved";
            else
                return "Record not Affected to DataBase";
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            if (_con.State != System.Data.ConnectionState.Closed)
                _con.Close();
        }
    }

    public string UpdateProduct(Product _P)
    {
        try
        {
            if (_con.State != System.Data.ConnectionState.Open)
                _con.Open();
            SqlCommand _cmd = _con.CreateCommand();
            _cmd.CommandText = "Update Products set Name=@Name,Unit=@Unit,
            Qty=@Qty Where ProductID=@ProductID";
            _cmd.Parameters.Add(new SqlParameter("@Name", _P.Name));
            _cmd.Parameters.Add(new SqlParameter("@Qty", _P.Qty));
            _cmd.Parameters.Add(new SqlParameter("@Unit", _P.Unit));
            _cmd.Parameters.Add(new SqlParameter("@ProductID", _P.ProductID));
            if (_cmd.ExecuteNonQuery() > 0)
                return "Record Successfully Updated";
            else
                return "Record not Affected to DataBase";
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            if (_con.State != System.Data.ConnectionState.Closed)
                _con.Close();
        }
    }

    public string DeleteProduct(int ProductID)
    {
        try
        {
            if (_con.State != System.Data.ConnectionState.Open)
                _con.Open();
            SqlCommand _cmd = _con.CreateCommand();
            _cmd.CommandText = "Delete From Products Where ProductID=@ProductID";
            _cmd.Parameters.Add(new SqlParameter("@ProductID", ProductID));
            if (_cmd.ExecuteNonQuery() > 0)
                return "Records Successfully Delete";
            else
                return "Records not Affected to DataBase";
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            if (_con.State != System.Data.ConnectionState.Closed)
                _con.Close();
        }
    }

    public Product GetProductById(int ProductID)
    {
        try
        {
            if (_con.State != System.Data.ConnectionState.Open)
                _con.Open();
            SqlCommand _cmd = _con.CreateCommand();
            _cmd.CommandText = "Select * From Products Where ProductID=@ProductID";
            _cmd.Parameters.Add(new SqlParameter("@ProductID", ProductID));
            SqlDataReader _Reader = _cmd.ExecuteReader();
            Product _Product = null;
            while (_Reader.Read())
            {
                _Product = new Product();
                _Product.ProductID = Convert.ToInt32(_Reader["ProductID"]);
                _Product.Name = _Reader["Name"].ToString();
                _Product.Qty = Convert.ToDecimal(_Reader["Qty"]);
                _Product.Unit = _Reader["Unit"].ToString();
            }
            return _Product;
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            if (_con.State != System.Data.ConnectionState.Closed)
                _con.Close();
        }
    }
}

public class Product
{
    private int _ProductID = 0;

    public int ProductID
    {
        get { return _ProductID; }
        set { _ProductID = value; }
    }

    private string _Name = string.Empty;

    public string Name
    {
        get { return _Name; }
        set { _Name = value; }
    }

    private string _Unit = string.Empty;

    public string Unit
    {
        get { return _Unit; }
        set { _Unit = value; }
    }

    private decimal _Qty = 0;

    public decimal Qty
    {
        get { return _Qty; }
        set { _Qty = value; }
    }
}

Next, create another class file named JsonResponse which is used to serialize response in a json format. Add the following code into that file.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

public class JsonResponse
{
    private bool _IsSucess = false;

    public bool IsSucess
    {
        get { return _IsSucess; }
        set { _IsSucess = value; }
    }

    private string _Message = string.Empty;

    public string Message
    {
        get { return _Message; }
        set { _Message = value; }
    }

    private object _ResponseData = null;

    public object ResponseData
    {
        get { return _ResponseData; }
        set { _ResponseData = value; }
    }

    private string _CallBack = string.Empty;

    public string CallBack
    {
        get { return _CallBack; }
        set { _CallBack = value; }
    }
} 

Now add the following HTML into Default.aspx's Body tag for product entry form.

<asp:Label runat="server" ID="lblTime"></asp:Label>
<form id="form1" action="" method="post">
<table cellpadding="2" cellspacing="2" border="1"width="400px">
<tr style="background-color: Gray"> <td colspan="2" align="center">
<b>Product Entry Form</b>
</td>
</tr>
<tr>
   <td>
      Product Name
   </td>
   <td>
      <input type="text" id="txtName"style="width:250px"/>
  </td>
</tr>
<tr>
  <td>
      Unit
  </td>
  <td>
  <input type="text"id="txtUnit"style="width: 250px"/>
  </td>
</tr>
<tr>
  <td>
      Qty
  </td>
  <td>
    <input type="text"id="txtQty"style="width: 250px"/>
   </td>
</tr>
<tr>
  <td colspan="2" align="center">
    <input type="button"id="butSave"value="Save"onclick="SaveProducts()"/>
   </td>
 </tr>
</table>
<br/>
<br/>
   <div id="ListingData">
   </div>
</form>

and add the following script tag into the head tag:

  <script src="Script/jquery-1.2.6.js" type="text/javascript"></script>
  <script src="Script/CommonFunction.js" type="text/javascript"></script>

Next add the handler File named ProductList.ashx which is used to get response when it will call using Jquery. Add the following code into that file:

public class ProductList : IHttpHandler
{
    string MethodName = string.Empty;
    string CallBackMethodName = string.Empty;
    object Parameter = string.Empty;
    DbProducts _DbProducts = new DbProducts();

    public void ProcessRequest(HttpContext context)
    {
        context.Response.ContentType = "application/x-javascript";
        MethodName = context.Request.Params["method"];
        Parameter = context.Request.Params["param"];
        CallBackMethodName = context.Request.Params["callbackmethod"];

        switch (MethodName.ToLower())
        {
            case "getproducts":
                context.Response.Write(GetDetails());
                break;
            case "getbyid":
                context.Response.Write(GetById());
                break;
            case "insert":
                context.Response.Write(Insert(context));
                break;
            case "update":
                context.Response.Write(Update(context));
                break;
            case "delete":
                context.Response.Write(Delete());
                break;
        }
    }

    public string GetDetails()
    {
        JsonResponse _response = new JsonResponse();
        System.Web.Script.Serialization.JavaScriptSerializer jSearializer =
                       new System.Web.Script.Serialization.JavaScriptSerializer();
        try
        {
            System.Collections.Generic.List<product> _Products = 
                    _DbProducts.GetProductDetails();
            _response.IsSucess = true;
            _response.Message = string.Empty;
            _response.CallBack = CallBackMethodName;
            _response.ResponseData = _Products;
        }
        catch (Exception ex)
        {
            _response.Message = ex.Message;
            _response.IsSucess = false;
        }
        return jSearializer.Serialize(_response);
    }

    public string GetById()
    {
        JsonResponse _response = new JsonResponse();
        System.Web.Script.Serialization.JavaScriptSerializer jSearializer =
                     new System.Web.Script.Serialization.JavaScriptSerializer();
        try
        {
            Product _Products = _DbProducts.GetProductById(Convert.ToInt32(Parameter));
            _response.IsSucess = true;
            _response.Message = string.Empty;
            _response.CallBack = CallBackMethodName;
            _response.ResponseData = _Products;

        }
        catch (Exception ex)
        {
            _response.Message = ex.Message;
            _response.IsSucess = false;
        }
        return jSearializer.Serialize(_response);
    }

    public string Insert(HttpContext context)
    {
        JsonResponse _response = new JsonResponse();
        System.Web.Script.Serialization.JavaScriptSerializer jSearializer =
                     new System.Web.Script.Serialization.JavaScriptSerializer();
        try
        {
            Product _P = new Product();
            _P.Name = context.Request.Params["name"].ToString();
            _P.Unit = context.Request.Params["unit"].ToString();
            _P.Qty = Convert.ToDecimal(context.Request.Params["Qty"].ToString());
            _response.IsSucess = true;
            _response.CallBack = CallBackMethodName;
            _response.ResponseData = _DbProducts.InsertProduct(_P);
            _response.Message = "SucessFully Saved";
        }
        catch (Exception ex)
        {
            _response.Message = ex.Message;
            _response.IsSucess = false;
        }
        return jSearializer.Serialize(_response);
    }

    public string Update(HttpContext context)
    {
        JsonResponse _response = new JsonResponse();
        System.Web.Script.Serialization.JavaScriptSerializer jSearializer =
                     new System.Web.Script.Serialization.JavaScriptSerializer();
        try
        {
            Product _P = new Product();
            _P.Name = context.Request.Params["name"].ToString();
            _P.Unit = context.Request.Params["unit"].ToString();
            _P.Qty = Convert.ToDecimal(context.Request.Params["Qty"].ToString());
            _P.ProductID = Convert.ToInt32
        (context.Request.Params["ProductID"].ToString());
            _response.IsSucess = true;
            _response.Message = "SucessFully Updated";
            _response.CallBack = CallBackMethodName;
            _response.ResponseData = _DbProducts.UpdateProduct(_P);
        }
        catch (Exception ex)
        {
            _response.Message = ex.Message;
            _response.IsSucess = false;
        }
        return jSearializer.Serialize(_response);
    }

    public string Delete()
    {
        JsonResponse _response = new JsonResponse();
        System.Web.Script.Serialization.JavaScriptSerializer jSearializer =
                     new System.Web.Script.Serialization.JavaScriptSerializer();
        try
        {
            _response.IsSucess = true;
            _response.Message = "Record Successfully Deleted";
            _response.CallBack = CallBackMethodName;
            _response.ResponseData = _DbProducts.DeleteProduct(Convert.ToInt32(Parameter));
        }
        catch (Exception ex)
        {
            _response.Message = ex.Message;
            _response.IsSucess = false;
        }
        return jSearializer.Serialize(_response);
    }

    public bool IsReusable
    {
        get
        {
            return false;
        }
    }
}

Now, go to Coomonfunction.js, add the following function into that file.

function DoAjaxCall(parameter, datatype, data) {
    jQuery.ajax({
        type: 'POST',
        url: "ProductList.ashx" + parameter,
        data: data,
        dataType: datatype,
        success: function(data, textStatus) {
            try {
                var jsonData = (new Function("return " + data))()
                if (jsonData.IsSucess) {
                    eval(jsonData.CallBack + '(jsonData.ResponseData, jsonData.Message)');
                }
                else {
                    alert(jsonData.Message + jsonData.IsSucess);
                }
            }
            catch (err) {
            }
        },
        error: function() {
            alert("Error");
        }
    });
}

This function is used to call the Http Handler using Ajax. To call this function, we have to just pass parameters like methodname, datatoinsert, callbackfunctionname, datatype and data. If it will perform successfully, then it will go to the success function if there are set is success is true then it will call the callback function and pass the response data into the json format and message, but if any error occurred it will go the error function.

Next, add client side click function of save button:

<input type="button" id="butSave" value="Save" onclick="SaveProducts()" />

and add the following function SaveProducts into the Commonfunction.js which looks like this:

function SaveProducts() {

    var Param = "name=" + document.getElementById("txtName").value + 
    "&unit=" + document.getElementById("txtUnit").value + "&Qty=" + 
    document.getElementById("txtQty").value;
       if (ProductID == 0)
        DoAjaxCall("?method=Insert&callbackmethod=InsertProductSucess", "script", Param);
    else {
        Param += "&ProductID=" + ProductID;
        DoAjaxCall("?method=Update&callbackmethod=UpdateProductSucess", "script", Param);
    }
}

In this function, we have to pass the values to the handler insert data into database, so it will be passed using Querystring. After that, we will check ProductID Global variable which is used to decide current click used to enter the new record into the database or the update record into the database. So if the value of the ProductID is 0, then we have to call the Insert method, otherwise call Update methods.

Now, for the insert, we are passing the parameter as method= Insert and callbackmethod=InserProductSucess and Parameter. Now this function calls DoAjaxCall Global Function which calls the ProductList Handler, so in the Handler in the Process Request method, we will check which method calls using parameter method. It will call that relevant parameter method. That method performs their operation and assigns a value into the JsonResponse Call object and at the end, it will return jsonresponse serialize object from that method.

That Json Response is available into the Sucessfunction and from that, we will just check if that operation performs successfully, then it will call that callback function.

For the call back function, we have to add one function into the commonfunction.js name InsertProductSucess which looks like below:

function InsertProductSucess(data, message) {
    FillListing();
    alert(message);
    ClearValue();
}
function ClearValue() {
    $("#txtName").val("");
    $("#txtUnit").val("");
    $("#txtQty").val("");
}

Here, this method shows that alert message to the user. Now in the same way, add the other function for the other operation in the same way:

$(document).ready(function() { FillListing(); });

function UpdateProductSucess(data, message) {
    FillListing();
    alert(message);
    ProductID = 0;
    ClearValue();
}

function FillListing() {
    DoAjaxCall("?method=getproducts&callbackmethod=FillListingSucess", "script", "");
}

function FillListingSucess(data, message) {
    var str = " <table width="500px" cellspacing="0" cellpadding="2" 
    border="1"><tbody><tr><td align="center" style="background-color: Gray;" 
    colspan="5"><strong>Product Listing Page</strong></td></tr><tr> 
    <td>Product Name</td><td>Unit</td><td>Qty</td><td>Delete</td><td>Edit</td></tr>";

    for (var i = 0; i < data.length; i++) {
        str += "<tr><td>" + data[i].Name + "</td>";
        str += "<td>" + data[i].Unit + "</td>";
        str += "<td>" + data[i].Qty + "</td>";
        str += "<td><a onclick="DeleteProduct(" + data[i].ProductID + ")" 
        href="javascript:void(0)">Delete</a></td>";
        str += "<td><a onclick="EditProduct(" + data[i].ProductID + ")" 
        href="javascript:void(0)">Edit</a></td></tr>";
    }
    str += "</tbody></table>";
    $('#ListingData').html(str);
}

function DeleteProduct(ProductID) {
    DoAjaxCall("?method=delete&callbackmethod=DeleteSucess&param=" + 
        ProductID, "script", "");
}

function DeleteSucess(data, message) {
    FillListing();
    alert(message);
}

function EditProduct(ProductID) {
    DoAjaxCall("?method=getbyid&callbackmethod=EditSucess&param=" + 
        ProductID, "script", "");
}

function EditSucess(data, message) {
    ProductID = data.ProductID;
    $("#txtName").val(data.Name);
    $("#txtUnit").val(data.Unit);
    $("#txtQty").val(data.Qty);
}

Now try to add one Product using Default.aspx. It will add product. You will find that page will not postback and lbltime will not show the updated time or you can also check into Firebug.

Conclusion

The example given above is very basic. You can use any concept for the database operation like Linq, Entity Framework, etc. The example source code is included with this article, you are free to modify it according to your requirement.

History

• 15^th November, 2011: Initial post