bll调用mysql存储过程_C#连接、访问MySQL数据库

一、准备工具

visual stuido(本示例使用visual studio 2010)

MySql.Data.dll

mysql_installer_community_V5.6.21.1_setup.1415604646.msi

二、新建DBUtility通用访问MySQL类库

1、在解决方案中新建一个DBUtility类库，作为访问MySQL的通用sql语句存放地点

2、引入MySql.Data.dll

3、配置web.config MySQL数据库连接字符串

在解决方案根目录下打开web.config配置文件，在connectionStrings节点中加入MySQL连接字符串

4、新建SQLHelper通用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);

}

}

}

}

三、访问MySQL数据库(增删查改)

提示：sql语句无论是oracle、mysql、sqlserver，增删查改语句大体相似，皆为insert、delete、update、select操作，故熟悉一种数据库操作语句后，亦可相对容易学习其他数据库语句

using System.Collections.Generic;

using System.Text;

using MySql.Data.MySqlClient;

using System.Data;

using DBUtility;

1、插入操作(insert)

示例代码：

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);

}

2、删除操作(delete)

示例代码：

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);

}

3、修改操作(update)

示例代码：

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);

}

4、查询操作(select)

示例代码：

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);

}

5、调用存储过程

示例代码：

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);

}