利用HttpHandler和Jquery进行数据库增改删查操作

l        引言

本文一步步教你如何在Asp.net和Jquery Ajax API中用HttpHandler实现数据库增改删查的基本操作。为了简单起见，本文不涉及表单验证和应用程序界面设计方面的知识。

l        必备条件

在前面我提及已经通过Jquery Ajax API调用HttpHandler，因此需要在Web项目中添加jquery文件的引用，官方网址：http://jquery.com，如果使用vs2010开发，那么jquery文件默认包含在新建的Web项目中。

l        实现

首先在visual studio中新建Web项目，然后新建一个名为Script的文件夹，并添加一个名为Commonfunction.js空白js文件。

l        数据表设计

在Sqlserver数据库中新建如下所示的Products数据表，并新建Products相关的数据操作类,代码如下：

然后新建名为JsonResponse 类，用来将从数据库中接收到的数据序列化成Json格式，代码如下：

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; }
    }
} 

首页Default.aspx中Product信息的相关html代码如下，主要用来提交保存单条产品信息。

在页面的head标签中添加如下引用:

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

新建ProductList.ashx ,主要处理客户端提交的ajax数据请求，代码如下：

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;
        }
    }
}

在Coomonfunction.js中添加公用函数DoAjaxCall，该函数利用Ajax调用HttpHandler处理结果，并通过methodname, datatoinsert, callbackfunctionname, datatype 和 data等传入的参数，来告知服务器端执行的操作（增改删查）并返回服务器端处理成功或失败的消息。

保存按钮的客户端事件SaveProducts()调用和定义如下,其中通过判断ProductID是否为0来判断当前的操作是插入还是更新产品信息。

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

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);
    }
}

l        结论

该例子讲解数据库操作的基本方法，当然也可用于Linq， Entity Framework等技术实现复杂的数据操作,点击下载源码,可按照自己的要求随意修改它。

原文出处:http://www.codeproject.com/Articles/283976/CRUD-Create-Read-Update-Delete