C#连接、访问MySQL数据库_MySQL

/// 

 ///SQLHelper 的摘要说明
 /// 
 public abstract class SQLHelper
 {
 //数据库连接字符串（注意：这里的“DBConnectionString”一定要与web.config文件中connectionStrings节点值一致）
 public static readonly string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["DBConnectionString"].ToString();


 // 用于缓存参数的HASH表
 private static Hashtable parmCache = Hashtable.Synchronized(new Hashtable());


 /// 

 /// 给定连接的数据库用假设参数执行一个sql命令（不返回数据集）
 /// 
 /// 命令类型(存储过程, 文本, 等等)
 /// 存储过程名称或者sql命令语句
 /// 执行命令所用参数的集合
 /// 执行命令所影响的行数
 public static int ExecuteNonQuery(string cmdText, CommandType cmdType = CommandType.Text, 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命令
 /// 
 /// 
 /// 举例:
 /// MySqlDataReader r = ExecuteReader(connString, CommandType.StoredProcedure, "PublishOrders", new MySqlParameter("@prodid", 24));
 /// 
 /// 命令类型(存储过程, 文本, 等等)
 /// 存储过程名称或者sql命令语句
 /// 执行命令所用参数的集合
 /// 包含结果的读取器
 public static MySqlDataReader ExecuteReader(string cmdText, CommandType cmdType = CommandType.Text, params MySqlParameter[] commandParameters)
 {
 //创建一个MySqlCommand对象
 MySqlCommand cmd = new MySqlCommand();
 //创建一个MySqlConnection对象
 MySqlConnection conn = new MySqlConnection(connectionString);


 //在这里我们用一个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 cmdText, CommandType cmdType = CommandType.Text, 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 cmdText, CommandType cmdType = CommandType.Text, 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;
 }
 }


 /// 

 /// 将参数集合添加到缓存
 /// 
 /// 添加到缓存的变量
 /// 一个将要添加到缓存的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)
 {
 foreach (MySqlParameter parameter in cmdParms)
 {
 if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
 (parameter.Value == null))
 {
 parameter.Value = DBNull.Value;
 }
 cmd.Parameters.Add(parameter);
 }
 }
 }


 }

public int Add( int userId, string name)
{
 StringBuilder sb = new StringBuilder();
 sb.Append("INSERT INTO T_Photo(Name,UserID) ");
 sb.Append("VALUES(?Name,?UserID) ");
 MySqlParameter[] parameters = {
 new MySqlParameter("?Name", MySqlDbType.String),
 new MySqlParameter("?UserID", MySqlDbType.Int32)
 };
 parameters[0].Value = name;
 parameters[1].Value = userId;
 return SQLHelper.ExecuteNonQuery(sb.ToString(), CommandType.Text, parameters);
}

public int Delete(long id, int userId)
{
 StringBuilder sb = new StringBuilder();
 sb.Append("DELETE FROM T_Photo WHERE ID = ?ID AND UserID = ?UserID");
 MySqlParameter[] parameters = {
 new MySqlParameter("?ID", MySqlDbType.Int64),
 new MySqlParameter("?UserID", MySqlDbType.Int32)
 };
 parameters[0].Value = id;
 parameters[1].Value = userId;
 return SQLHelper.ExecuteNonQuery(sb.ToString(), CommandType.Text, parameters);
}
　　

public int EditName(long id, int userId, string name)
{
 StringBuilder sb = new StringBuilder();
 sb.Append("UPDATE T_Photo SET Name = ?Name WHERE ID = ?ID AND UserID = ?UserID");
 MySqlParameter[] parameters = {
 new MySqlParameter("?ID", MySqlDbType.Int64),
 new MySqlParameter("?UserID", MySqlDbType.Int32),
 new MySqlParameter("?Name", MySqlDbType.String)
 };
 parameters[0].Value = id;
 parameters[1].Value = userId;
 parameters[2].Value = name;
 return SQLHelper.ExecuteNonQuery(sb.ToString(), CommandType.Text, parameters);
}
　　

public MySqlDataReader GetListByOrderCode(string orderCode)
{
 StringBuilder sb = new StringBuilder();
 sb.Append("SELECT ID,OrderCount,Subtotal,ProductID, ");
 sb.Append("FROM t_orderdetail ");


 //筛选条件
 sb.Append("WHERE OrderCode = ?OrderCode ");


 //排序
 sb.Append("ORDER BY ID DESC ");


 MySqlParameter[] parameters = {
 new MySqlParameter("?OrderCode", MySqlDbType.String)
 };


 parameters[0].Value = orderCode;
 return SQLHelper.ExecuteReader(sb.ToString(), CommandType.Text, parameters);

}
　　

public int BackOrder(long id)
{
 StringBuilder sb = new StringBuilder();


 sb.Append("BackOrder ");//存储过程名称


 MySqlParameter[] parameters = {
 new MySqlParameter("?OrderId", MySqlDbType.Int64)//OrderId必须与存储过程参数名、数据类型一致
 };
 parameters[0].Value = id;
 return MyCustomSQLHelper.ExecuteNonQuery(sb.ToString(), CommandType.StoredProcedure, parameters);
}