using MySql.Data.MySqlClient;
|
using System;
|
using System.Collections.Generic;
|
using System.Configuration;
|
using System.Data;
|
using System.IO;
|
using System.Linq;
|
using System.Text;
|
using System.Threading.Tasks;
|
|
namespace HxDbContext
|
{
|
/// <summary>
|
/// MySql帮助类
|
/// </summary>
|
public class MySqlHelper
|
{
|
private static string connstr = GetConnStr();
|
|
#region 获取字数据库字符串
|
/// <summary>
|
/// 获取字数据库字符串
|
/// </summary>
|
/// <returns></returns>
|
private static string GetConnStr()
|
{
|
//string _db = "";
|
//string _uid = "";
|
//string _pwd = "";
|
//string _database = "";
|
//try
|
//{
|
// StreamReader sr = new StreamReader(System.AppDomain.CurrentDomain.SetupInformation.ApplicationBase + "\\Config\\MySQLConfig.ini", Encoding.GetEncoding("gb2312"));
|
// _db = sr.ReadLine();
|
// _uid = sr.ReadLine();
|
// _pwd = sr.ReadLine();
|
// _database = sr.ReadLine();
|
// sr.Close();
|
//}
|
//catch (Exception ex)
|
//{
|
// throw new Exception(ex.ToString());
|
//}
|
|
ConnectionStringSettings cs = ConfigurationManager.ConnectionStrings["MySqlDbContext"];
|
return cs.ConnectionString;
|
}
|
#endregion
|
|
#region 执行查询语句，返回MySqlDataReader
|
/// <summary>
|
/// 执行查询语句，返回MySqlDataReader
|
/// </summary>
|
/// <param name="sqlString"></param>
|
/// <returns></returns>
|
public static MySqlDataReader ExecuteReader(string sqlString)
|
{
|
MySqlConnection connection = new MySqlConnection(connstr);
|
MySqlCommand cmd = new MySqlCommand(sqlString, connection);
|
MySqlDataReader myReader = null;
|
try
|
{
|
connection.Open();
|
myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
|
return myReader;
|
}
|
catch (System.Data.SqlClient.SqlException e)
|
{
|
connection.Close();
|
throw new Exception(e.Message);
|
}
|
finally
|
{
|
if (myReader == null)
|
{
|
cmd.Dispose();
|
connection.Close();
|
}
|
}
|
}
|
#endregion
|
|
#region 执行带参数的查询语句，返回MySqlDataReader
|
/// <summary>
|
/// 执行带参数的查询语句，返回MySqlDataReader
|
/// </summary>
|
/// <param name="sqlString"></param>
|
/// <param name="cmdParms"></param>
|
/// <returns></returns>
|
public static MySqlDataReader ExecuteReader(string sqlString, params MySqlParameter[] cmdParms)
|
{
|
MySqlConnection connection = new MySqlConnection(connstr);
|
MySqlCommand cmd = new MySqlCommand();
|
MySqlDataReader myReader = null;
|
try
|
{
|
PrepareCommand(cmd, connection, null, sqlString, cmdParms);
|
myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
|
cmd.Parameters.Clear();
|
return myReader;
|
}
|
catch (System.Data.SqlClient.SqlException e)
|
{
|
connection.Close();
|
throw new Exception(e.Message);
|
}
|
finally
|
{
|
if (myReader == null)
|
{
|
cmd.Dispose();
|
connection.Close();
|
}
|
}
|
}
|
#endregion
|
|
#region 执行sql语句,返回执行行数
|
/// <summary>
|
/// 执行sql语句,返回执行行数
|
/// </summary>
|
/// <param name="sql"></param>
|
/// <returns></returns>
|
public static int UpdataSql(string sql)
|
{
|
using (MySqlConnection conn = new MySqlConnection(connstr))
|
{
|
using (MySqlCommand cmd = new MySqlCommand(sql, conn))
|
{
|
try
|
{
|
conn.Open();
|
int rows = cmd.ExecuteNonQuery();
|
return rows;
|
}
|
catch (MySql.Data.MySqlClient.MySqlException e)
|
{
|
conn.Close();
|
throw e;
|
}
|
finally
|
{
|
cmd.Dispose();
|
conn.Close();
|
}
|
}
|
}
|
}
|
#endregion
|
|
#region 执行带参数的sql语句，并返回执行行数
|
/// <summary>
|
/// 执行带参数的sql语句，并返回执行行数
|
/// </summary>
|
/// <param name="sqlString"></param>
|
/// <param name="cmdParms"></param>
|
/// <returns></returns>
|
public static int ExecuteSql(string sqlString, params MySqlParameter[] cmdParms)
|
{
|
using (MySqlConnection connection = new MySqlConnection(connstr))
|
{
|
using (MySqlCommand cmd = new MySqlCommand())
|
{
|
try
|
{
|
PrepareCommand(cmd, connection, null, sqlString, cmdParms);
|
int rows = cmd.ExecuteNonQuery();
|
cmd.Parameters.Clear();
|
return rows;
|
}
|
catch (System.Data.SqlClient.SqlException E)
|
{
|
throw new Exception(E.Message);
|
}
|
finally
|
{
|
cmd.Dispose();
|
connection.Close();
|
}
|
}
|
}
|
}
|
#endregion
|
|
#region 执行查询语句，返回GetDataTable
|
/// <summary>
|
/// 执行查询语句，返回GetDataTable
|
/// </summary>
|
/// <param name="sql"></param>
|
/// <returns></returns>
|
public static DataTable GetDataTable(string sql)
|
{
|
using (MySqlConnection conn = new MySqlConnection(connstr))
|
{
|
DataSet ds = new DataSet();
|
try
|
{
|
conn.Open();
|
MySqlDataAdapter DataAdapter = new MySqlDataAdapter(sql, conn);
|
DataAdapter.Fill(ds);
|
if (ds != null)
|
{
|
return ds.Tables[0];
|
}
|
return null;
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
finally
|
{
|
conn.Close();
|
}
|
}
|
}
|
#endregion
|
|
#region 执行带参数的查询语句，返回DataSet
|
/// <summary>
|
/// 执行带参数的查询语句，返回DataSet
|
/// </summary>
|
/// <param name="sqlString"></param>
|
/// <param name="cmdParms"></param>
|
/// <returns></returns>
|
public static DataSet GetDataSet(string sqlString, params MySqlParameter[] cmdParms)
|
{
|
using (MySqlConnection connection = new MySqlConnection(connstr))
|
{
|
MySqlCommand cmd = new MySqlCommand();
|
PrepareCommand(cmd, connection, null, sqlString, cmdParms);
|
using (MySqlDataAdapter da = new MySqlDataAdapter(cmd))
|
{
|
DataSet ds = new DataSet();
|
try
|
{
|
da.Fill(ds, "ds");
|
cmd.Parameters.Clear();
|
}
|
catch (System.Data.SqlClient.SqlException ex)
|
{
|
throw new Exception(ex.Message);
|
}
|
finally
|
{
|
cmd.Dispose();
|
connection.Close();
|
}
|
return ds;
|
}
|
}
|
}
|
#endregion
|
|
#region 执行带参数的sql语句，并返回object
|
/// <summary>
|
/// 执行带参数的sql语句，并返回object
|
/// </summary>
|
/// <param name="sqlString"></param>
|
/// <param name="cmdParms"></param>
|
/// <returns></returns>
|
public static object GetSingle(string sqlString, params MySqlParameter[] cmdParms)
|
{
|
using (MySqlConnection connection = new MySqlConnection(connstr))
|
{
|
using (MySqlCommand cmd = new MySqlCommand())
|
{
|
try
|
{
|
PrepareCommand(cmd, connection, null, sqlString, cmdParms);
|
object obj = cmd.ExecuteScalar();
|
cmd.Parameters.Clear();
|
if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
|
{
|
return null;
|
}
|
else
|
{
|
return obj;
|
}
|
}
|
catch (System.Data.SqlClient.SqlException e)
|
{
|
throw new Exception(e.Message);
|
}
|
finally
|
{
|
cmd.Dispose();
|
connection.Close();
|
}
|
}
|
}
|
}
|
#endregion
|
|
#region 执行存储过程,返回数据集
|
/// <summary>
|
/// 执行存储过程,返回数据集
|
/// </summary>
|
/// <param name="storedProcName">存储过程名</param>
|
/// <param name="parameters">存储过程参数</param>
|
/// <returns>DataSet</returns>
|
public static DataSet RunProcedureForDataSet(string storedProcName, IDataParameter[] parameters)
|
{
|
using (MySqlConnection connection = new MySqlConnection(connstr))
|
{
|
DataSet dataSet = new DataSet();
|
connection.Open();
|
MySqlDataAdapter sqlDA = new MySqlDataAdapter();
|
sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);
|
sqlDA.Fill(dataSet);
|
connection.Close();
|
return dataSet;
|
}
|
}
|
#endregion
|
|
#region 构建 SqlCommand 对象(用来返回一个结果集，而不是一个整数值)
|
/// <summary>
|
/// 构建 SqlCommand 对象(用来返回一个结果集，而不是一个整数值)
|
/// </summary>
|
/// <param name="connection">数据库连接</param>
|
/// <param name="storedProcName">存储过程名</param>
|
/// <param name="parameters">存储过程参数</param>
|
/// <returns>SqlCommand</returns>
|
private static MySqlCommand BuildQueryCommand(MySqlConnection connection, string storedProcName,
|
IDataParameter[] parameters)
|
{
|
MySqlCommand command = new MySqlCommand(storedProcName, connection);
|
command.CommandType = CommandType.StoredProcedure;
|
foreach (MySqlParameter parameter in parameters)
|
{
|
command.Parameters.Add(parameter);
|
}
|
return command;
|
}
|
#endregion
|
|
#region 装载MySqlCommand对象
|
/// <summary>
|
/// 装载MySqlCommand对象
|
/// </summary>
|
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;
|
}
|
cmd.CommandType = CommandType.Text; //cmdType;
|
if (cmdParms != null)
|
{
|
foreach (MySqlParameter parm in cmdParms)
|
{
|
cmd.Parameters.Add(parm);
|
}
|
}
|
}
|
#endregion
|
}
|
}
|
