using System;
using System.Data;
using MySql.Data.MySqlClient;
using System.Windows;
using System.Collections.Generic;
using System.Collections;
using System.Diagnostics;
namespace ERP
{
class MySQLClass
{
//MySqlCommandBuilder sb1 = new MySqlCommandBuilder(myAdapter); //sb1 到头来也没用过,但只要有这一句,update就不会再报上述错误,神奇吧
//局域网中使用的软件,用户数也较少,尽量使用长连接来做,所以需要手动关闭数据连接
public static string connStr = String.Format("server={0};user id={1}; password={2}; port={3}; database=fszj; pooling=false;", "127.0.0.1", "root", "fsccwjc", 3306);
public static MySqlConnection conn;
public static MySqlCommand cmd;
/// <summary>
/// 批量操作每批次记录数
/// </summary>
public static int BatchSize = 2000;
/// <summary>
/// 超时时间
/// </summary>
public static int CommandTimeOut = 600;
//private string connStr = String.Format("server={0};user id={1}; password={2}; port={3}; database=fszj; pooling=false; Allow Zero Datetime=True", "127.0.0.1", "root", "fsccwjc", 3306);
//连接句中包含Allow Zero Datetime=True ,会导致datagrid中使用日期选择器需要转换类型,更严重的是,使用转换会导致textblock stringformat失效
public MySQLClass() //初始化
{
open();
}
#region 查询数据连接状态
/// <summary>
/// 查询数据连接状态
/// </summary>
/// <returns>返回连接状态</returns>
public static Boolean GetConnectionState() //返回连接状态
{
if (conn != null) //如果连接不为空
{
if (conn.State.Equals(ConnectionState.Open))
{
return true; //如果连接状态为打开,返回真
}
else
{
return false;
}
}
else
{
return false;
}
}
#endregion
#region 打开数据连接
/// <summary>
/// 打开数据连接
/// </summary>
/// <returns>返回数据库打开操作是否成功</returns>
public static Boolean open()
{
if (conn != null) //如果连接不为空
{
if (conn.State.Equals(ConnectionState.Open)) return true; //如果连接状态为打开,返回真
}
try
{
conn = new MySqlConnection(connStr);
conn.Open();
}
catch
{
return false;
}
return true;
}
#endregion
#region 关闭数据连接
/// <summary>
/// 关闭数据连接
/// </summary>
public static void close()
{
if (conn != null)
{
conn.Close();
conn.Dispose();
}
}
#endregion
#region 获取MySqlCommand对象
/// <summary>
/// //由conn链接创建MySqlCommand
/// </summary>
/// <param name="conn">由类创建时自动获取,无须提供</param>
/// <returns></returns>
public static MySqlCommand GetMySQLCommand()
{
if (cmd != null) return cmd;
try
{
open();
cmd = conn.CreateCommand();
return cmd;
}
catch
{
return null;
}
}
#endregion
#region 创建MySqlDataReader对象
/// <summary>
/// 创建一个MySqlDataReader对象,返回执行SQL语句后得到的结果集
/// CommandBehavior.SingleResult 执行返回一个结果集,并不关闭连接
/// </summary>
/// <param name="sqlStr">SQL语句</param>
/// <returns>返回MySqlDataReader对象</returns>
public MySqlDataReader GetMySQLReader(string sqlStr)
{
open();
GetMySQLCommand();
cmd.CommandText = sqlStr;
MySqlDataReader mysqlread = cmd.ExecuteReader(CommandBehavior.SingleResult);
return mysqlread;
}
#endregion
#region 创建MySqlDataAdapter对象
/// <summary>
/// 创建MySqlDataAdapter
/// </summary>
/// <param name="sqlStr">SQL语句</param>
/// <returns></returns>
public MySqlDataAdapter GetMySQLAdapter(string sqlStr)
{
open();
GetMySQLCommand();
cmd.CommandText = sqlStr;
MySqlDataAdapter da = new MySqlDataAdapter(cmd);
return da;
}
#endregion
#region 执行MySqlCommand命令,返回执行成功与否
/// <summary>
/// 执行单句commandText,返回执行成功与否
/// </summary>
/// <param name="commandText">SQL语句</param>
public static Boolean ExecuteSQL(string commandText)
{
if (!open()) return false;
if (GetMySQLCommand() == null) return false;
try
{
cmd.CommandType = CommandType.Text;
cmd.CommandText = commandText;
cmd.ExecuteNonQuery();
}
catch
{
return false;
}
return true;
}
/// <summary>
/// 执行多句commandText,返回执行成功与否
/// </summary>
/// <param name="commandText">SQL语句集</param>
public static Boolean ExecuteSQL(List<string> commandText)
{
if (commandText == null) return false; //传入命令集为空
if (!open()) return false;
if (GetMySQLCommand() == null) return false;
MySqlTransaction trans=conn.BeginTransaction();
cmd.Transaction = trans; //绑定事务
try
{
string sql;
for (int i = 0; i < commandText.Count; i++)
{
sql = commandText[i];
if (sql.Trim() != "")
{
cmd.CommandType = CommandType.Text;
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
}
}
trans.Commit();
return true;
}
catch(MySqlException ex)
{
Debug.WriteLine("执行SQL语句时出现异常:"+ex.Message);
trans.Rollback();
return false;
}
}
/// <summary>
/// 执行事务,返回执行成功与否
/// </summary>
/// <param name="sp_Name">存储过程名</param>
/// <param name="parms">参数集</param>
/// <returns></returns>
public static Boolean ExecuteSQL(string sp_Name, params MySqlParameter[] parms)
{
GetMySQLCommand();
MySqlTransaction trans = conn.BeginTransaction();
PrepareCommand(trans, CommandType.StoredProcedure, sp_Name, parms);
try
{
cmd.ExecuteNonQuery();
trans.Commit();
cmd.Parameters.Clear();
return true;
}
catch
{
trans.Rollback();
cmd.Parameters.Clear();
return false;
}
}
#endregion
#region 获取DataSet
/// <summary>
/// 执行SQL查询语句获取dataset
/// </summary>
/// <param name="commandText">查询语句</param>
/// <returns></returns>
public static DataSet GetDataSet(string commandText)
{
open();
return MySqlHelper.ExecuteDataset(conn, commandText);
}
/// <summary>
/// 执行存储过程获取dataset
/// </summary>
/// <param name="sp_Name">存储过程名</param>
/// <param name="parms">参数集</param>
/// <returns></returns>
public static DataSet GetDataSet(string sp_Name, params MySqlParameter[] parms)
{
GetMySQLCommand();
PrepareCommand(null, CommandType.StoredProcedure, sp_Name, parms);
MySqlDataAdapter adapter = new MySqlDataAdapter(cmd);
DataSet ds = new DataSet();
adapter.Fill(ds);
cmd.Parameters.Clear();
return ds;
}
#endregion
#region 通过SQL查询语句获取datatable
/// <summary>
/// 查询记录并填充到datatable,返回该datatable
/// </summary>
/// <param name="commandText">查询语句</param>
/// <returns></returns>
public static DataTable GetDataTable(string commandText)
{
return MySqlHelper.ExecuteDataset(conn, commandText).Tables[0];
}
/// <summary>
/// 执行存储过程,返回datatable
/// </summary>
/// <param name="sp_Name">存储过程名</param>
/// <param name="parms">参数集</param>
/// <returns></returns>
public static DataTable GetDataTable(string sp_Name, params MySqlParameter[] parms)
{
try
{
GetMySQLCommand();
PrepareCommand(null, CommandType.StoredProcedure, sp_Name, parms);
MySqlDataAdapter adapter = new MySqlDataAdapter(cmd);
DataSet ds = new DataSet();
adapter.Fill(ds);
cmd.Parameters.Clear();
return ds.Tables[0];
}
catch
{
return null;
}
}
#endregion
#region 获取第一行第一列的值
/// <summary>
/// 执行SQL语句,返回结果集中的第一行第一列
/// </summary>
/// <param name="commandText">SQL语句</param>
/// <returns>返回结果集中的第一行第一列</returns>
public static object ExecuteScalar(string commandText)
{
GetMySQLCommand();
PrepareCommand(null, CommandType.Text, commandText, null);
return cmd.ExecuteScalar();
}
/// <summary>
/// 执行存储过程,返回结果集中的第一行第一列
/// </summary>
/// <param name="transaction">事务</param>
/// <param name="sp_Name">存储过程名称</param>
/// <param name="parms">查询参数</param>
/// <returns>返回结果集中的第一行第一列</returns>
public static object ExecuteScalar(string sp_Name, params MySqlParameter[] parms)
{
GetMySQLCommand();
MySqlTransaction temTrans = null;
PrepareCommand(temTrans,CommandType.StoredProcedure, sp_Name, parms);
object retval = cmd.ExecuteScalar();
cmd.Parameters.Clear();
return retval;
}
#endregion
/// <summary>
/// 前处理,准备执行
/// </summary>
/// <param name="_trans">事务,不为NULL则使用事务方式执行</param>
/// <param name="cmdType">命令类型(SQL语句或存储过程)</param>
/// <param name="cmdText">SQL语句或存储过程名</param>
/// <param name="cmdParms">参数集</param>
private static void PrepareCommand(MySqlTransaction _trans, CommandType cmdType, string cmdText, MySqlParameter[] cmdParms)
{
if (conn.State != ConnectionState.Open)
conn.Open();
GetMySQLCommand();
cmd.CommandText = cmdText;
if (_trans != null)
cmd.Transaction = _trans;
cmd.CommandType = cmdType;
if (cmdParms != null && cmdParms.Length > 0)
{
//预处理MySqlParameter参数数组,将为NULL的参数赋值为DBNull.Value;
foreach (MySqlParameter parameter in cmdParms)
{
if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) && (parameter.Value == null) || ((string)parameter.Value==""))
{
parameter.Value = DBNull.Value;
}
cmd.Parameters.Add(parameter);
}
}
}
}
}