mysql增删改查_C# ADO.NET 实现对MySql数据库的增删改查

1、创建数据库和表

创建数据库：

CREATE DATABASE `AuthorizationData`

创建表：

CREATE TABLE `AuthorizationData`.`InstrumentNewTable` (

`ID` int(10) unsigned NOT NULL AUTO_INCREMENT,

`Name` varchar(100) DEFAULT '""',

`MachineID` varchar(40) DEFAULT '""',

`Serial` varchar(100) DEFAULT '""',

`Description` TEXT,

PRIMARY KEY (`ID`)

) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;

2、编写MySqlHelper类和Config文件

Config文件：

MySqlHelper类：

using System;

using System.Collections;

using System.Configuration;

using MySql.Data;

using MySql.Data.MySqlClient;

using System.Data;

namespace DAL

{

///

///MYSQLHelper 的摘要说明

///

public abstract class MySqlHelper

{

//数据库连接字符串

public static string Conn = System.Configuration.ConfigurationManager.ConnectionStrings["MySql"].ConnectionString;

// 用于缓存参数的HASH表

private static Hashtable parmCache = Hashtable.Synchronized(new Hashtable());

///

/// 给定连接的数据库用假设参数执行一个sql命令(不返回数据集)

///

/// 一个有效的连接字符串

/// 命令类型(存储过程, 文本, 等等)

/// 存储过程名称或者sql命令语句

/// 执行命令所用参数的集合

/// 执行命令所影响的行数

public static int ExecuteNonQuery(string connectionString, CommandType cmdType, string cmdText, params MySqlParameter[] commandParameters)

{

MySqlCommand cmd = new MySqlCommand();

using (MySqlConnection conn = new MySqlConnection(connectionString))

{

PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);

int val = cmd.ExecuteNonQuery();

cmd.Parameters.Clear();

return val;

}

}

///

/// 用现有的数据库连接执行一个sql命令(不返回数据集)

///

/// 一个现有的数据库连接

/// 命令类型(存储过程, 文本, 等等)

/// 存储过程名称或者sql命令语句

/// 执行命令所用参数的集合

/// 执行命令所影响的行数

public static int ExecuteNonQuery(MySqlConnection connection, CommandType cmdType, string cmdText, params MySqlParameter[] commandParameters)

{

MySqlCommand cmd = new MySqlCommand();

PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);

int val = cmd.ExecuteNonQuery();

cmd.Parameters.Clear();

return val;

}

///

///使用现有的SQL事务执行一个sql命令(不返回数据集)

///

///

///举例:

/// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new MySqlParameter("@prodid", 24));

///

/// 一个现有的事务

/// 命令类型(存储过程, 文本, 等等)

/// 存储过程名称或者sql命令语句

/// 执行命令所用参数的集合

/// 执行命令所影响的行数

public static int ExecuteNonQuery(MySqlTransaction trans, CommandType cmdType, string cmdText, params MySqlParameter[] commandParameters)

{

MySqlCommand cmd = new MySqlCommand();

PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters);

int val = cmd.ExecuteNonQuery();

cmd.Parameters.Clear();

return val;

}

///

/// 用执行的数据库连接执行一个返回数据集的sql命令

///

///

/// 举例:

/// MySqlDataReader r = ExecuteReader(connString, CommandType.StoredProcedure, "PublishOrders", new MySqlParameter("@prodid", 24));

///

/// 一个有效的连接字符串

/// 命令类型(存储过程, 文本, 等等)

/// 存储过程名称或者sql命令语句

/// 执行命令所用参数的集合

/// 包含结果的读取器

public static MySqlDataReader ExecuteReader(CommandType cmdType, string cmdText, params MySqlParameter[] commandParameters)

{

//创建一个MySqlCommand对象

MySqlCommand cmd = new MySqlCommand();

//创建一个MySqlConnection对象

MySqlConnection conn = new MySqlConnection(Conn);

//在这里我们用一个try/catch结构执行sql文本命令/存储过程，因为如果这个方法产生一个异常我们要关闭连接，因为没有读取器存在，

//因此commandBehaviour.CloseConnection 就不会执行

try

{

//调用 PrepareCommand 方法，对 MySqlCommand 对象设置参数

PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);

//调用 MySqlCommand 的 ExecuteReader 方法

MySqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);

//清除参数

cmd.Parameters.Clear();

return reader;

}

catch

{

//关闭连接，抛出异常

conn.Close();

throw;

}

}

///

/// 返回DataSet

///

/// 一个有效的连接字符串

/// 命令类型(存储过程, 文本, 等等)

/// 存储过程名称或者sql命令语句

/// 执行命令所用参数的集合

///

public static DataSet GetDataSet(string connectionString, CommandType cmdType, string cmdText, params MySqlParameter[] commandParameters)

{

//创建一个MySqlCommand对象

MySqlCommand cmd = new MySqlCommand();

//创建一个MySqlConnection对象

MySqlConnection conn = new MySqlConnection(connectionString);

//在这里我们用一个try/catch结构执行sql文本命令/存储过程，因为如果这个方法产生一个异常我们要关闭连接，因为没有读取器存在，

try

{

//调用 PrepareCommand 方法，对 MySqlCommand 对象设置参数

PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);

//调用 MySqlCommand 的 ExecuteReader 方法

MySqlDataAdapter adapter = new MySqlDataAdapter();

adapter.SelectCommand = cmd;

DataSet ds = new DataSet();

adapter.Fill(ds);

//清除参数

cmd.Parameters.Clear();

conn.Close();

return ds;

}

catch (Exception e)

{

throw e;

}

}

///

/// 用指定的数据库连接字符串执行一个命令并返回一个数据集的第一列

///

///

///例如:

/// Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new MySqlParameter("@prodid", 24));

///

///一个有效的连接字符串

/// 命令类型(存储过程, 文本, 等等)

/// 存储过程名称或者sql命令语句

/// 执行命令所用参数的集合

/// 用 Convert.To{Type}把类型转换为想要的

public static object ExecuteScalar(string connectionString, CommandType cmdType, string cmdText, params MySqlParameter[] commandParameters)

{

MySqlCommand cmd = new MySqlCommand();

using (MySqlConnection connection = new MySqlConnection(connectionString))

{

PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);

object val = cmd.ExecuteScalar();

cmd.Parameters.Clear();

return val;

}

}

///

/// 用指定的数据库连接执行一个命令并返回一个数据集的第一列

///

///

/// 例如:

/// Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new MySqlParameter("@prodid", 24));

///

/// 一个存在的数据库连接

/// 命令类型(存储过程, 文本, 等等)

/// 存储过程名称或者sql命令语句

/// 执行命令所用参数的集合

/// 用 Convert.To{Type}把类型转换为想要的

public static object ExecuteScalar(MySqlConnection connection, CommandType cmdType, string cmdText, params MySqlParameter[] commandParameters)

{

MySqlCommand cmd = new MySqlCommand();

PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);

object val = cmd.ExecuteScalar();

cmd.Parameters.Clear();

return val;

}

///

/// 将参数集合添加到缓存

///

/// 添加到缓存的变量

/// 一个将要添加到缓存的sql参数集合

public static void CacheParameters(string cacheKey, params MySqlParameter[] commandParameters)

{

parmCache[cacheKey] = commandParameters;

}

///

/// 找回缓存参数集合

///

/// 用于找回参数的关键字

/// 缓存的参数集合

public static MySqlParameter[] GetCachedParameters(string cacheKey)

{

MySqlParameter[] cachedParms = (MySqlParameter[])parmCache[cacheKey];

if (cachedParms == null)

return null;

MySqlParameter[] clonedParms = new MySqlParameter[cachedParms.Length];

for (int i = 0, j = cachedParms.Length; i < j; i++)

clonedParms[i] = (MySqlParameter)((ICloneable)cachedParms[i]).Clone();

return clonedParms;

}

///

/// 准备执行一个命令

///

/// sql命令

/// OleDb连接

/// OleDb事务

/// 命令类型例如 存储过程或者文本

/// 命令文本,例如:Select * from Products

/// 执行命令的参数

private static void PrepareCommand(MySqlCommand cmd, MySqlConnection conn, MySqlTransaction trans, CommandType cmdType, string cmdText, MySqlParameter[] cmdParms)

{

if (conn.State != ConnectionState.Open)

conn.Open();

cmd.Connection = conn;

cmd.CommandText = cmdText;

if (trans != null)

cmd.Transaction = trans;

cmd.CommandType = cmdType;

if (cmdParms != null)

{

cmd.Parameters.AddRange(cmdParms);

}

}

}

}

3、Model层：

namespace Model

{

public class ManufacturerInfo

{

public ManufacturerInfo()

{

this.ID = 0;

this.Name = "";

this.Department = "";

this.LinkMan = "";

this.LinkWay = "";

this.Location = "";

}

///

/// 厂家ID

///

public int ID { get; set; }

///

/// 厂家名称

///

public string Name { get; set; }

///

/// 仪表使用部门

///

public string Department { get; set; }

///

/// 联系人

///

public string LinkMan { get; set; }

///

/// 联系方式

///

public string LinkWay { get; set; }

///

/// 联系方式

///

public string Location { get; set; }

}

}

4、BLL层：

using Model;

using MySql.Data.MySqlClient;

using System;

using System.Data;

using System.Text;

namespace BLL

{

public class ManufacturerBLL

{

///

/// 获取数据库中的表名

///

///

public List GetTables()

{

List tableList = null;

string query = "show tables from AuthorizationData";

using (MySqlDataReader reader = DAL.MySqlHelper.ExecuteReader(CommandType.Text, query))

{

if (reader != null)

{

tableList = new List();

while (reader.Read())

{

string tableName = reader.GetString(0);

if (tableName.StartsWith("Authorization"))

{

tableList.Add(tableName.Replace("Authorization", " ").Replace("Table", " ").Trim());

}

}

}

}

return tableList;

}

///

/// 创建数据库

///

///

///

public int CreateTable(string au)

{

string strSql = @"CREATE TABLE `AuthorizationData`.`Authorization" + au + @"Table` (

`ID` int(10) unsigned NOT NULL AUTO_INCREMENT,

`Note` varchar(512) DEFAULT NULL,

`MachineID_crypt` blob,

PRIMARY KEY(`ID`)

) ENGINE = InnoDB AUTO_INCREMENT = 2 DEFAULT CHARSET = utf8; ";

int reader = (int)DAL.MySqlHelper.ExecuteNonQuery(DAL.MySqlHelper.Conn, CommandType.Text, strSql.ToString());

return reader;

}

///

/// 新增

///

///

/// 执行成功返回ID,失败返回0

public int Add(ManufacturerInfo info)

{

StringBuilder strSql = new StringBuilder();

strSql.Append("insert into ManufacturerTable(");

strSql.Append("Name,Department,LinkMan,LinkWay,Location)");

strSql.Append(" values (");

strSql.Append("@Name,@Department,@LinkMan,@LinkWay,@Location)");

strSql.Append(";select @@IDENTITY");

MySqlParameter[] parameters = new MySqlParameter[5];

parameters[0] = new MySqlParameter("@Name", info.Name);

parameters[1] = new MySqlParameter("@Department", info.Department);

parameters[2] = new MySqlParameter("@LinkMan", info.LinkMan);

parameters[3] = new MySqlParameter("@LinkWay", info.LinkWay);

parameters[4] = new MySqlParameter("@Location", info.LinkWay);

object obj = DAL.MySqlHelper.ExecuteScalar(DAL.MySqlHelper.Conn, CommandType.Text, strSql.ToString(), parameters);

if (obj == null)

{

return 0;

}

else

{

return Convert.ToInt32(obj);

}

}

///

/// 更新

///

///

/// 成功返回1，失败返回0

public int Update(ManufacturerInfo manufacturerInfo)

{

StringBuilder strSql = new StringBuilder();

strSql.Append("update ManufacturerTable set ");

strSql.Append("Name=@Name,");

strSql.Append("Department=@Department,");

strSql.Append("LinkMan=@LinkMan,");

strSql.Append("LinkWay=@LinkWay,");

strSql.Append("Location=@Location");

strSql.Append(" where ID=@ID");

MySqlParameter[] parameters = {

new MySqlParameter("@ID", MySqlDbType.Int32),

new MySqlParameter("@Name", MySqlDbType.String),

new MySqlParameter("@Department", MySqlDbType.String),

new MySqlParameter("@LinkMan", MySqlDbType.String),

new MySqlParameter("@LinkWay", MySqlDbType.String),

new MySqlParameter("@Location", MySqlDbType.String),

};

parameters[0].Value = manufacturerInfo.ID;

parameters[1].Value = manufacturerInfo.Name;

parameters[2].Value = manufacturerInfo.Department;

parameters[3].Value = manufacturerInfo.LinkMan;

parameters[4].Value = manufacturerInfo.LinkWay;

parameters[5].Value = manufacturerInfo.Location;

int rows = DAL.MySqlHelper.ExecuteNonQuery(DAL.MySqlHelper.Conn, CommandType.Text, strSql.ToString(), parameters);

if (rows > 0)

{

return 1;

}

else

{

return 0;

}

}

///

/// 通过ID查询数据

///

///

///

public ManufacturerInfo GetManufacturerInfoByID(int id)

{

string selectAll = "select*from table where ID=@ID";

MySqlParameter mySqlParameter = new MySqlParameter("@ID", id);

ManufacturerInfo manufacturerInfo = null;

using (MySqlDataReader reader = DAL.MySqlHelper.ExecuteReader(CommandType.Text, selectAll, mySqlParameter))

{

if (reader != null && reader.Read())

{

manufacturerInfo = new ManufacturerInfo()

{

ID = reader.IsDBNull(0)?0:reader.GetInt32(0),

Name = reader.IsDBNull(1)?"":reader.GetString(1),

Department = reader.IsDBNull(2)?"":reader.GetString(2),

LinkMan=reader.IsDBNull(3)?"":reader.GetString(3),

LinkWay=reader.IsDBNull(4)?"":reader.GetString(4),

Location=reader.IsDBNull(5)?"":reader.GetString(5)

};

}

return manufacturerInfo;

}

}

///

/// 通过Name和department 查询数据

///

///

///

///

public ManufacturerInfo GetInfoByNameAndDepartment(string name, string department)

{

string selectAll = "select*from table where Name=@Name and Department=@Department";

MySqlParameter[] mySqlParameters = {

new MySqlParameter("@Name", MySqlDbType.String),

new MySqlParameter("@Department", MySqlDbType.String),

};

mySqlParameters[0].Value = name;

mySqlParameters[1].Value = department;

ManufacturerInfo manufacturerInfo = null;

using (MySqlDataReader reader = DAL.MySqlHelper.ExecuteReader(CommandType.Text, selectAll, mySqlParameters))

{

if (reader != null && reader.Read())

{

manufacturerInfo = new ManufacturerInfo()

{

ID = reader.GetInt32(0),

Name = reader.IsDBNull(1)?"":reader.GetString(1),

Department = reader.IsDBNull(2)?"":reader.GetString(2),

LinkMan = reader.IsDBNull(3)?"":reader.GetString(3),

LinkWay = reader.IsDBNull(4)?"":reader.GetString(4),

Location = reader.IsDBNull(5)?"":reader.GetString(5)

};

}

return manufacturerInfo;

}

}

}

}