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

时间：2022-02-07 04:41:38
网站中无论是用户登录还是用户信息修改等操作都离不开数据库。

本文将通过一个简单的例子来介绍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语句，并对返回的数据进行处理。
标签：

相关文章