Asp.Net中使用HTTP处理程序连接MySQL数据库

网站中无论是用户登录还是用户信息修改等操作都离不开数据库。

本文将通过一个简单的例子来介绍Asp.Net中如何通过HTTP处理程序来对MySQL数据库进行操作。

准备工作

• 首先你的电脑中要安装MySQL数据库服务器
• 安装MySQL Connector Net
• 当然还有VS

进入正题

首先，新建一个Asp.Net空网站项目，然后添加MySQL.Data引用。
在web.config的<configuration></configuration>下添加如下标签：

<connectionStrings>    <add name="MySQLConnString" connectionString="Server=localhost;Port=3306;Database=floorTitles;Uid=root;Pwd=147258369;pooling=false;" providerName="MySql.Data.MySqlClient" />  </connectionStrings>

其中，Server表示MySQL服务器位置；Port表示端口；Database表示数据库；Uid表示用户名；Pws表示密码。
然后名为Floor的类，并添加以下代码：

using System;using System.Collections.Generic;using System.Linq;using System.Web;using System.Data;using System.Configuration;using MySql.Data.MySqlClient;/// <summary>/// Summary description for Products/// </summary>/// public class DbProducts{    MySqlConnection _con = new MySqlConnection(ConfigurationManager.ConnectionStrings["MySQLConnString"].ConnectionString);    public List<Product> GetProductDetails(string select)    {        try        {            List<Product> _lstProducts = new List<Product>();            Product _Product = null;            if (_con.State != System.Data.ConnectionState.Open)                _con.Open();            MySqlCommand _cmd = _con.CreateCommand();            _cmd.CommandText = "SELECT ID, indexNum, Full300, Part300, Full400, Part400, Full600, Part600, Full800,"                +" Part800, Area300, Area400, Area600, Area800,"                + "(Full300 + Full400 + Full600 + Full800) / (Full300 + Part300 + Full400 + Part400 +Full600 + Part600 +Full800 + Part800) AS Eva,"                + "ImageString"                + " FROM floor"                + " WHERE DataID = " + select                +" ORDER BY Eva DESC";            MySqlDataReader _Reader = _cmd.ExecuteReader();            while (_Reader.Read())            {                _Product = new Product();                _Product.ID = Convert.ToInt32(_Reader["ID"].ToString());                _Product.Index = Convert.ToInt32(_Reader["IndexNum"].ToString());                _Product.F300 = Convert.ToInt32(_Reader["Full300"].ToString());                _Product.P300 = Convert.ToInt32(_Reader["Part300"].ToString());                _Product.F400 = Convert.ToInt32(_Reader["Full400"].ToString());                _Product.P400 = Convert.ToInt32(_Reader["Part400"].ToString());                _Product.F600 = Convert.ToInt32(_Reader["Full600"].ToString());                _Product.P600 = Convert.ToInt32(_Reader["Part600"].ToString());                _Product.F800 = Convert.ToInt32(_Reader["Full800"].ToString());                _Product.P800 = Convert.ToInt32(_Reader["Part800"].ToString());                _Product.A300 = Convert.ToDouble(_Reader["Area300"].ToString());                _Product.A400 = Convert.ToDouble(_Reader["Area400"].ToString());                _Product.A600 = Convert.ToDouble(_Reader["Area600"].ToString());                _Product.A800 = Convert.ToDouble(_Reader["Area800"].ToString());                _Product.Eva = Convert.ToDouble(_Reader["Eva"].ToString());                _Product.imgStr =Convert.ToString(_Reader["ImageString"].ToString());                _lstProducts.Add(_Product);            }            return _lstProducts;        }        catch (Exception ex)        {            throw ex;        }        finally        {            if (_con.State != ConnectionState.Closed)                _con.Close();        }    }    public string InsertProduct(Product _P)    {        try        {            if (_con.State != System.Data.ConnectionState.Open)                _con.Open();            MySqlCommand _cmd = _con.CreateCommand();            _cmd.CommandText = "INSERT INTO floor("                + "DataID, ImageString, Date, Full300, Part300, Full400, Part400,"                + "Full600, Part600, Full800, Part800, Area300, Area400, Area600,"                + "Area800, indexNum) VALUES"                + "(@DataID, @ImageString, @Date, @Full300, @Part300, @Full400, @Part400, "                + "@Full600, @Part600, @Full800, @Part800, @Area300, @Area400, @Area600, @Area800, @indexNum)";            _cmd.Parameters.Add(new MySqlParameter("@DataID", "123456"));            _cmd.Parameters.Add(new MySqlParameter("@ImageString", "C:\\CNSI\\"));            _cmd.Parameters.Add(new MySqlParameter("@Date", "2017/03/31"));            _cmd.Parameters.Add(new MySqlParameter("@Full300", _P.F300));            _cmd.Parameters.Add(new MySqlParameter("@Part300", _P.P300));            _cmd.Parameters.Add(new MySqlParameter("@Full400", _P.F400));            _cmd.Parameters.Add(new MySqlParameter("@Part400", _P.P300));            _cmd.Parameters.Add(new MySqlParameter("@Full600", _P.F600));            _cmd.Parameters.Add(new MySqlParameter("@Part600", _P.P600));            _cmd.Parameters.Add(new MySqlParameter("@Full800", _P.F800));            _cmd.Parameters.Add(new MySqlParameter("@Part800", _P.P300));            _cmd.Parameters.Add(new MySqlParameter("@Area300", _P.A300));            _cmd.Parameters.Add(new MySqlParameter("@Area400", _P.A400));            _cmd.Parameters.Add(new MySqlParameter("@Area600", _P.A600));            _cmd.Parameters.Add(new MySqlParameter("@Area800", _P.A800));            _cmd.Parameters.Add(new MySqlParameter("@IndexNum", _P.Index));            if (_cmd.ExecuteNonQuery() > 0)                return "Record Sucessfully Saved";            else                return "Record not Afftected 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();            MySqlCommand _cmd = _con.CreateCommand();            _cmd.CommandText = "UPDATE floor SET "                + "DataID=@DataID, ImageString=@ImageString, Date=@Date, Full300=@Full300, Part300=@Part300, Full400=@Full400, "                + "Part400=@Part400, Full600=@Full600, Part600=@Part600, Full800=@Full800, Part800=@Part800, "                + "Area300=@Area300, Area400=@Area400, Area600=@Area600, Area800=@Area800, indexNum=@indexNum"                +" Where ID=@ID";            _cmd.Parameters.Add(new MySqlParameter("@DataID", "123456"));            _cmd.Parameters.Add(new MySqlParameter("@ImageString", "C:\\CNSI\\"));            _cmd.Parameters.Add(new MySqlParameter("@Date", "2017-12-12"));            _cmd.Parameters.Add(new MySqlParameter("@Full300", _P.F300));            _cmd.Parameters.Add(new MySqlParameter("@Part300", _P.P300));            _cmd.Parameters.Add(new MySqlParameter("@Full400", _P.F400));            _cmd.Parameters.Add(new MySqlParameter("@Part400", _P.P300));            _cmd.Parameters.Add(new MySqlParameter("@Full600", _P.F600));            _cmd.Parameters.Add(new MySqlParameter("@Part600", _P.P600));            _cmd.Parameters.Add(new MySqlParameter("@Full800", _P.F800));            _cmd.Parameters.Add(new MySqlParameter("@Part800", _P.P300));            _cmd.Parameters.Add(new MySqlParameter("@Area300", _P.A300));            _cmd.Parameters.Add(new MySqlParameter("@Area400", _P.A400));            _cmd.Parameters.Add(new MySqlParameter("@Area600", _P.A600));            _cmd.Parameters.Add(new MySqlParameter("@Area800", _P.A800));            _cmd.Parameters.Add(new MySqlParameter("@IndexNum", _P.Index));            _cmd.Parameters.Add(new MySqlParameter("@ID", _P.ID));            if (_cmd.ExecuteNonQuery() > 0)                return "Record Sucessfully Updated";            else                return "Record not Afftected to DataBase";        }        catch (Exception ex)        {            throw ex;        }        finally        {            if (_con.State != System.Data.ConnectionState.Closed)                _con.Close();        }    }    public string DeleteProduct(int ID)    {        try        {            if (_con.State != System.Data.ConnectionState.Open)                _con.Open();            MySqlCommand _cmd = _con.CreateCommand();            _cmd.CommandText = "Delete From floor Where ID=@ID";            _cmd.Parameters.Add(new MySqlParameter("@ID", ID));            if (_cmd.ExecuteNonQuery() > 0)                return "Records Sucessfully Delete";            else                return "Records not Afftected to DataBase";        }        catch (Exception ex)        {            throw ex;        }        finally        {            if (_con.State != System.Data.ConnectionState.Closed)                _con.Close();        }    }    public Product GetProductById(int ID)    {        try        {            if (_con.State != System.Data.ConnectionState.Open)                _con.Open();            MySqlCommand _cmd = _con.CreateCommand();            _cmd.CommandText = "SELECT ID, indexNum, Full300, Part300, Full400, Part400, Full600, Part600, Full800,"                + " Part800, Area300, Area400, Area600, Area800,"                + "(Full300 + Full400 + Full600 + Full800) / (Full300 + Part300 + Full400 + Part400 +Full600 + Part600 +Full800 + Part800) AS Eva,"                + "ImageString"                + " FROM floor"                + " WHERE ID = @ID";            _cmd.Parameters.Add(new MySqlParameter("@ID", ID));            MySqlDataReader _Reader = _cmd.ExecuteReader();            Product _Product = null;            while (_Reader.Read())            {                _Product = new Product();                _Product.ID = Convert.ToInt32(_Reader["ID"].ToString());                _Product.Index = Convert.ToInt32(_Reader["IndexNum"].ToString());                _Product.F300 = Convert.ToInt32(_Reader["Full300"].ToString());                _Product.P300 = Convert.ToInt32(_Reader["Part300"].ToString());                _Product.F400 = Convert.ToInt32(_Reader["Full400"].ToString());                _Product.P400 = Convert.ToInt32(_Reader["Part400"].ToString());                _Product.F600 = Convert.ToInt32(_Reader["Full600"].ToString());                _Product.P600 = Convert.ToInt32(_Reader["Part600"].ToString());                _Product.F800 = Convert.ToInt32(_Reader["Full800"].ToString());                _Product.P800 = Convert.ToInt32(_Reader["Part800"].ToString());                _Product.A300 = Convert.ToDouble(_Reader["Area300"].ToString());                _Product.A400 = Convert.ToDouble(_Reader["Area400"].ToString());                _Product.A600 = Convert.ToDouble(_Reader["Area600"].ToString());                _Product.A800 = Convert.ToDouble(_Reader["Area800"].ToString());                _Product.Eva = Convert.ToDouble(_Reader["Eva"].ToString());                _Product.imgStr = Convert.ToString(_Reader["ImageString"].ToString());            }            return _Product;        }        catch (Exception ex)        {            throw ex;        }        finally        {            if (_con.State != System.Data.ConnectionState.Closed)                _con.Close();        }    }}public class Product{    private int _ID = 0;    public int ID    {        get { return _ID; }        set { _ID = value; }    }    private int _Index = 0;    public int Index    {        get { return _Index; }        set { _Index = value; }    }    private int _F300 = 0;    public int F300    {        get { return _F300; }        set { _F300 = value; }    }    private int _P300 = 0;    public int P300    {        get { return _P300; }        set { _P300 = value; }    }    private int _F400 = 0;    public int F400    {        get { return _F400; }        set { _F400 = value; }    }    private int _P400 = 0;    public int P400    {        get { return _P400; }        set { _P400 = value; }    }    private int _F600 = 0;    public int F600    {        get { return _F600; }        set { _F600 = value; }    }    private int _P600 = 0;    public int P600    {        get { return _P300; }        set { _P300 = value; }    }    private int _F800 = 0;    public int F800    {        get { return _F800; }        set { _F800 = value; }    }    private int _P800 = 0;    public int P800    {        get { return _P800; }        set { _P800 = value; }    }    private double _A300 = 0;    public double A300    {        get { return _A300; }        set { _A300 = value; }    }    private double _A400 = 0;    public double A400    {        get { return _A400; }        set { _A400 = value; }    }    private double _A600 = 0;    public double A600    {        get { return _A600; }        set { _A600 = value; }    }    private double _A800 = 0;    public double A800    {        get { return _A800; }        set { _A800 = value; }    }    private double _Eva = 0;    public double Eva    {        get { return _Eva; }        set { _Eva = value; }    }    private string _imgStr = string.Empty;    public string imgStr    {        get { return _imgStr; }        set { _imgStr = value; }    }}

我们以GetProductDetails()函数为例进行数据库连接讲解：

MySqlConnection _con = new MySqlConnection(ConfigurationManager.ConnectionStrings["MySQLConnString"].ConnectionString);

这是数据库连接字符串。

 List<Product> _lstProducts = new List<Product>();            Product _Product = null;            if (_con.State != System.Data.ConnectionState.Open)                _con.Open();            MySqlCommand _cmd = _con.CreateCommand();            _cmd.CommandText = "sql语句";            MySqlDataReader _Reader = _cmd.ExecuteReader();            while (_Reader.Read())            {                //do something;            }            return _lstProducts;        }        catch (Exception ex)        {            throw ex;        }        finally        {            if (_con.State != ConnectionState.Closed)                _con.Close();        }

上面是，通过数据库连接字符串连接数据库，执行SQL语句，并对返回的数据进行处理。
接下来新建一个命名为floor.ashx的 Asp.Net HTTP处理程序，代码如下：

using System;using System.Web;using DotSpatial.Data;using DotSpatial.Projections;using DotSpatial.Serialization;public class ProductList : IHttpHandler{    string op = string.Empty;    string MethodName = string.Empty;    string CallBackMethodName = string.Empty;    object Parameter = string.Empty;    string Select = 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"];        Select = context.Request.Params["select"];        switch (MethodName.ToLower())        {            case "getproducts":                if (Select.ToLower() == "no")                {                    context.Response.Write(GetDetails("123456"));                }                else                {                    context.Response.Write(GetDetails("654321"));                }                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;            case "uploadfile":                context.Response.Write(UpLoadFile(context));                break;        }    }    public string GetDetails(string select)    {        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(select);            _response.IsSucess = true;            _response.Message = string.Empty;            _response.CallBack = CallBackMethodName;            _response.ResponseData = _Products;            string filename = System.DateTime.Now.ToString("yyyyMMddhhmmss") + ".html";            _response.HTML = filename;        }        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.F300 = Convert.ToInt32(context.Request.Params["F300"].ToString());            _P.P300 = Convert.ToInt32(context.Request.Params["P300"].ToString());            _P.F400 = Convert.ToInt32(context.Request.Params["F400"].ToString());            _P.P400 = Convert.ToInt32(context.Request.Params["P400"].ToString());            _P.F600 = Convert.ToInt32(context.Request.Params["F600"].ToString());            _P.P600 = Convert.ToInt32(context.Request.Params["P600"].ToString());            _P.F800 = Convert.ToInt32(context.Request.Params["F800"].ToString());            _P.F800 = Convert.ToInt32(context.Request.Params["F800"].ToString());            _P.A300 = Convert.ToDouble(context.Request.Params["A300"].ToString());            _P.A400 = Convert.ToDouble(context.Request.Params["A400"].ToString());            _P.A600 = Convert.ToDouble(context.Request.Params["A600"].ToString());            _P.A800 = Convert.ToDouble(context.Request.Params["A800"].ToString());            _P.Index = Convert.ToInt32(context.Request.Params["Inde"].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.F300 = Convert.ToInt32(context.Request.Params["F300"].ToString());            _P.P300 = Convert.ToInt32(context.Request.Params["P300"].ToString());            _P.F400 = Convert.ToInt32(context.Request.Params["F400"].ToString());            _P.P400 = Convert.ToInt32(context.Request.Params["P400"].ToString());            _P.F600 = Convert.ToInt32(context.Request.Params["F600"].ToString());            _P.P600 = Convert.ToInt32(context.Request.Params["P600"].ToString());            _P.F800 = Convert.ToInt32(context.Request.Params["F800"].ToString());            _P.F800 = Convert.ToInt32(context.Request.Params["F800"].ToString());            _P.A300 = Convert.ToDouble(context.Request.Params["A300"].ToString());            _P.A400 = Convert.ToDouble(context.Request.Params["A400"].ToString());            _P.A600 = Convert.ToDouble(context.Request.Params["A600"].ToString());            _P.A800 = Convert.ToDouble(context.Request.Params["A800"].ToString());            _P.Index = Convert.ToInt32(context.Request.Params["Inde"].ToString());            _P.ID= Convert.ToInt32(context.Request.Params["ID"].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 Sucessfully 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 string UpLoadFile(HttpContext context)    {        JsonResponse _response = new JsonResponse();        System.Web.Script.Serialization.JavaScriptSerializer jSearializer =                     new System.Web.Script.Serialization.JavaScriptSerializer();        string datapath = @"D:\Mascot Dokky\appdata\";        string str = string.Empty;        foreach(string file in context.Request.Files){            var fileContent = context.Request.Files[file];            if (fileContent != null && fileContent.ContentLength > 0){                var stream = fileContent.InputStream;                var fileName = fileContent.FileName;                fileContent.SaveAs(System.IO.Path.Combine(datapath,fileName));            }        }        foreach (string key in context.Request.Form) {            var value =  context.Request.Form[key];            str += value;        }        _response.IsSucess = true;        _response.Message = str;        _response.CallBack = CallBackMethodName;        _response.HTML = "index.html";        return jSearializer.Serialize(_response);    }    public bool IsReusable    {        get        {            return false;        }    }}

本文以GetDetials函数为例进行讲解：

 public string GetDetails(string select)    {        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(select);            _response.IsSucess = true;            _response.Message = string.Empty;            _response.CallBack = CallBackMethodName;            _response.ResponseData = _Products;            string filename = System.DateTime.Now.ToString("yyyyMMddhhmmss") + ".html";            _response.HTML = filename;        }        catch (Exception ex)        {            _response.Message = ex.Message;            _response.IsSucess = false;        }        return jSearializer.Serialize(_response);    }

本函数通过处理前端ajax请求，进行数据库查询操作，然后将数据序列化后传给前端，支持者就是一个完整的MySQL数据库操作。