public class FruitGameDBHelper
{
/// <summary>
/// 连接web.config中数据库字符串
/// </summary>
/// <returns></returns>
private static MySqlConnection CreateConn()
{
string _conn = WebConfigurationManager.ConnectionStrings["FruitGameDB"].ConnectionString;
MySqlConnection conn = new MySqlConnection(_conn);
return conn;
}
/// <summary>
/// 执行存储过程,返回受影响行数
/// </summary>
/// <param name="SQLString"></param>
/// <param name="cmdParms"></param>
/// <returns></returns>
public static object ExecuteProc(string SQLString, params MySqlParameter[] cmdParms)
{
MySqlConnection connection = CreateConn();
using (MySqlCommand cmd = new MySqlCommand())
{
try
{
PrepareCommand(cmd, connection, null, SQLString, cmdParms);
cmd.CommandType = CommandType.StoredProcedure;
int rows = cmd.ExecuteNonQuery();
//Console.WriteLine("返回值为 " + cmdParms[1].Value);
cmd.Parameters.Clear();
return rows;
}
catch (MySqlException e)
{
connection.Close();
throw e;
}
}
}
/// <summary>
/// 执行存储过程,返回DataSet
/// </summary>
/// <param name="storedProcName">存储过程名</param>
/// <param name="parameters">存储过程参数</param>
/// <returns>DataSet</returns>
public static DataSet ProcDataSet(string SQLString, params MySqlParameter[] cmdParms)
{
MySqlConnection connection = CreateConn();
DataSet dataSet = new DataSet();
connection.Open();
MySqlDataAdapter sqlDA = new MySqlDataAdapter
{
SelectCommand = BuildQueryCommand(connection, SQLString, cmdParms)
};
sqlDA.Fill(dataSet);
connection.Close();
return dataSet;
}
/// <summary>
/// 执行存储过程,返回DataSet
/// </summary>
/// <param name="storedProcName">存储过程名</param>
/// <param name="parameters">存储过程参数</param>
/// <returns>DataTable</returns>
public static DataTable ProcDataTable(string SQLString, params MySqlParameter[] cmdParms)
{
MySqlConnection connection = CreateConn();
DataTable table = new DataTable();
connection.Open();
MySqlDataAdapter sqlDA = new MySqlDataAdapter();
sqlDA.SelectCommand = BuildQueryCommand(connection, SQLString, cmdParms);
sqlDA.Fill(table);
connection.Close();
return table;
}
/// <summary>
/// 执行存储过程获取单个数据
/// </summary>
/// <param name="storedProcName">存储过程名</param>
/// <param name="parameters">存储过程参数</param>
/// <returns>DataTable</returns>
public static object RunProcedureSingle(string storedProcName, MySqlParameter[] cmdParms)
{
MySqlConnection connection = CreateConn();
DataSet dataSet = new DataSet();
connection.Open();
DataTable dt = new DataTable();
MySqlDataAdapter sqlDA = new MySqlDataAdapter();
sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, cmdParms);
sqlDA.Fill(dt);
connection.Close();
if (dt != null && dt.Rows.Count > 0)
{
return dt.Rows[0][0];
}
else
{
return null;
}
}
/// <summary>
/// 给cmd传参
/// </summary>
/// <param name="cmd"></param>
/// <param name="conn"></param>
/// <param name="trans"></param>
/// <param name="cmdText"></param>
/// <param name="cmdParms"></param>
private static void PrepareCommand(MySqlCommand cmd, MySqlConnection conn, MySqlTransaction trans, string cmdText, MySqlParameter[] cmdParms)
{
if (conn.State != ConnectionState.Open)
conn.Open();
cmd.Connection = conn;
cmd.CommandText = cmdText;
if (trans != null)
cmd.Transaction = trans;
if (cmdParms != null)
{
foreach (MySqlParameter parm in cmdParms)
cmd.Parameters.Add(parm);
}
}
/// <summary>
/// 构建 SqlCommand 对象(用来返回一个结果集,而不是一个整数值)
/// </summary>
/// <param name="connection">数据库连接</param>
/// <param name="storedProcName">存储过程名</param>
/// <param name="parameters">存储过程参数</param>
/// <returns>SqlCommand</returns>
internal static MySqlCommand BuildQueryCommand(MySqlConnection connection, string storedProcName, MySqlParameter[] parameters)
{
MySqlCommand command = new MySqlCommand(storedProcName, connection);
command.CommandType = CommandType.StoredProcedure;
foreach (MySqlParameter parameter in parameters)
{
if (parameter != null)
{
// 检查未分配值的输出参数,将其分配以DBNull.Value.
if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
(parameter.Value == null))
{
parameter.Value = DBNull.Value;
}
command.Parameters.Add(parameter);
}
}
return command;
}
}