网站中无论是用户登录还是用户信息修改等操作都离不开数据库。
本文将通过一个简单的例子来介绍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(