/// <summary>
/// 根据 SQL 返回 IList,带参数 (比DataSet效率高)
/// </summary>
/// <typeparam name="T">实体类</typeparam>
/// <param name="sql">Sql语句</param>
/// <param name="param">参数化</param>
/// <returns></returns>
public IList GetDataListBySQL <T>(StringBuilder sql, SqlParam[] param)
{
try
{
using (MySqlConnection conn = this.GetInstance())
{
conn.Open();
dbCommand = new MySqlCommand(sql.ToString(), conn);
try
{
dbCommand.CommandTimeout = CommandTimeOut;
dbCommand.CommandType = CommandType.Text;
DbCommon.MySqlAddInParameter(dbCommand, param);
return(DbReader.ReaderToList <T>(dbCommand.ExecuteReader(CommandBehavior.CloseConnection)));
}
finally
{
dbCommand.Dispose();
conn.Close();
conn.Dispose();
}
}
}
catch (Exception e)
{
DbLog.WriteException(e);
return(null);
}
}
/// <summary>
/// 摘要:
/// 执行一存储过程DataTable
/// 参数：
/// procName：存储过程名称
/// Hashtable：传入参数字段名
/// </summary>
public DataTable GetDataTableProc(string procName, Hashtable ht)
{
try
{
using (MySqlConnection conn = this.GetInstance())
{
dbCommand = new MySqlCommand(procName, conn);
try
{
dbCommand.CommandTimeout = CommandTimeOut;
dbCommand.CommandType = CommandType.StoredProcedure;
DbCommon.MySqlAddInParameter(dbCommand, ht);
return(DbReader.ReaderToDataTable(dbCommand.ExecuteReader(CommandBehavior.CloseConnection)));
}
catch
{
return(null);
}
finally
{
dbCommand.Dispose();
conn.Close();
conn.Dispose();
}
}
}
catch (Exception e)
{
DbLog.WriteException(e);
return(null);
}
}
/// <summary>
/// 根据SQL返回影响行数,带参数
/// </summary>
/// <param name="sql">sql语句</param>
/// <param name="param">参数化</param>
/// <returns></returns>
public object GetObjectValue(StringBuilder sql, SqlParam[] param)
{
object result = null;
//创建连接
MySqlConnection conn = this.GetInstance();
//创建指令
dbCommand = new MySqlCommand(sql.ToString(), conn);
DbCommon.MySqlAddInParameter(dbCommand, param);
try
{
//打开连接
conn.Open();
result = dbCommand.ExecuteScalar();
}
catch (Exception e)
{
DbLog.WriteException(e);
}
finally
{
conn.Close();
conn.Dispose();
}
return(result);
}
/// <summary>
/// 调用存储过程返回指定消息
/// </summary>
/// <param name="procName">存储过程名称</param>
/// <param name="ht">Hashtable</param>
/// <param name="msg">OutPut rs</param>
public int ExecuteByProcReturn(string procName, Hashtable ht, ref Hashtable rs)
{
int num = 0;
//创建连接
MySqlConnection conn = this.GetInstance();
//创建指令
dbCommand = new MySqlCommand(procName, conn);
dbCommand.CommandTimeout = CommandTimeOut;
dbCommand.CommandType = CommandType.StoredProcedure;
DbCommon.MySqlAddInParameter(dbCommand, ht);
try
{
conn.Open();
dbTransaction = conn.BeginTransaction();
try
{
dbCommand.Transaction = dbTransaction;
num = dbCommand.ExecuteNonQuery();
dbTransaction.Commit();
}
catch (Exception e)
{
dbTransaction.Rollback();
num = -1;
DbLog.WriteException(e);
}
finally
{
conn.Close();
conn.Dispose();
}
rs = new Hashtable();
foreach (string str in ht.Keys)
{
if (str.StartsWith("OUT_"))
{
object parameterValue = dbCommand.Parameters["@" + str.Remove(0, 4)].Direction = ParameterDirection.ReturnValue;
rs[str] = parameterValue;
}
}
}
catch (Exception e)
{
DbLog.WriteException(e);
}
finally
{
conn.Close();
conn.Dispose();
}
return(num);
}
/// <summary>
/// 批量调用存储过程
/// </summary>
/// <param name="arrayprocName"></param>
/// <param name="param"></param>
/// <returns></returns>
public int BatchExecuteByProc(object[] arrayprocName, object[] param)
{
int num = 0;
//创建连接
MySqlConnection conn = this.GetInstance();
try
{
conn.Open();
dbTransaction = conn.BeginTransaction();
try
{
for (int i = 0; i < arrayprocName.Length; i++)
{
string procName = arrayprocName[i].ToString();
if (procName != null)
{
SqlParam[] paramArray = (SqlParam[])param[i];
//创建指令
dbCommand = new MySqlCommand(procName, conn);
dbCommand.CommandTimeout = CommandTimeOut;
dbCommand.CommandType = CommandType.StoredProcedure;
DbCommon.MySqlAddInParameter(dbCommand, paramArray);
dbCommand.Transaction = dbTransaction;
num = dbCommand.ExecuteNonQuery();
}
}
dbTransaction.Commit();
num = 1;
}
catch (Exception e)
{
dbTransaction.Rollback();
num = -1;
DbLog.WriteException(e);
}
finally
{
conn.Close();
conn.Dispose();
}
}
catch (Exception e)
{
DbLog.WriteException(e);
}
finally
{
conn.Close();
conn.Dispose();
}
return(num);
}
/// <summary>
/// 执行一存储过程返回数据集 返回多个值
/// <param name="procName">存储过程名称</param>
/// <param name="ht">Hashtable</param>
/// <param name="rs">Hashtable</param>
public DataSet GetDataSetProcReturn(string procName, Hashtable ht, ref Hashtable rs)
{
try
{
using (MySqlConnection conn = this.GetInstance())
{
MySqlCommand cmd = new MySqlCommand(procName, conn);
try
{
cmd.CommandTimeout = CommandTimeOut;
cmd.CommandType = CommandType.StoredProcedure;
//创建适配器
MySqlDataAdapter da = new MySqlDataAdapter();
//给变量赋值
DbCommon.MySqlAddInParameter(cmd, ht);
da.SelectCommand = cmd;
//填充数据
DataSet ds = new DataSet();
da.Fill(ds);
rs = new Hashtable();
foreach (string str in ht.Keys)
{
if (str.StartsWith("OUT_"))
{
object parameterValue = cmd.Parameters["@" + str.Remove(0, 4)].Direction = ParameterDirection.ReturnValue;
rs[str] = parameterValue;
}
}
return(ds);
}
catch
{
return(null);
}
finally
{
cmd.Dispose();
conn.Close();
conn.Dispose();
}
}
}
catch (Exception e)
{
DbLog.WriteException(e);
return(null);
}
}
/// <summary>
/// 批量执行SQL语句
/// </summary>
/// <param name="sqls">sql语句</param>
/// <param name="m_param">参数化</param>
/// <returns></returns>
public int BatchExecuteBySql(object[] sqls, object[] param)
{
int num = 0;
try
{
using (MySqlConnection conn = this.GetInstance())
{
conn.Open();
dbTransaction = conn.BeginTransaction();
try
{
for (int i = 0; i < sqls.Length; i++)
{
StringBuilder builder = (StringBuilder)sqls[i];
if (builder != null)
{
SqlParam[] paramArray = (SqlParam[])param[i];
//创建指令
dbCommand = new MySqlCommand(builder.ToString(), conn);
DbCommon.MySqlAddInParameter(dbCommand, paramArray);
dbCommand.Transaction = dbTransaction;
dbCommand.ExecuteNonQuery();
}
}
dbTransaction.Commit();
num = 1;
}
catch (Exception e)
{
num = -1;
dbTransaction.Rollback();
DbLog.WriteException(e);
}
finally
{
conn.Close();
conn.Dispose();
}
}
}
catch (Exception e)
{
DbLog.WriteException(e);
}
return(num);
}
/// <summary>
/// 调用存储过程返回指定消息
/// </summary>
/// <param name="procName">存储过程名称</param>
/// <param name="ht">Hashtable</param>
/// <param name="msg">OutPut Msg</param>
public int ExecuteByProcReturnMsg(string procName, Hashtable ht, ref object msg)
{
int num = 0;
//创建连接
MySqlConnection conn = this.GetInstance();
//创建指令
dbCommand = new MySqlCommand(procName, conn);
dbCommand.CommandTimeout = CommandTimeOut;
dbCommand.CommandType = CommandType.StoredProcedure;
DbCommon.MySqlAddInParameter(dbCommand, ht);
try
{
conn.Open();
dbTransaction = conn.BeginTransaction();
try
{
dbCommand.Transaction = dbTransaction;
num = dbCommand.ExecuteNonQuery();
dbTransaction.Commit();
}
catch (Exception e)
{
dbTransaction.Rollback();
num = -1;
DbLog.WriteException(e);
}
finally
{
conn.Close();
conn.Dispose();
}
msg = dbCommand.Parameters["@Msg"].Direction = ParameterDirection.ReturnValue;
}
catch (Exception e)
{
DbLog.WriteException(e);
}
finally
{
conn.Close();
conn.Dispose();
}
return(num);
}
/// <summary>
/// 执行一存储过程返回数据表 返回多个值
/// <param name="procName">存储过程名称</param>
/// <param name="ht">Hashtable</param>
/// <param name="rs">Hashtable</param>
public DataTable GetDataTableProcReturn(string procName, Hashtable ht, ref Hashtable rs)
{
try
{
using (MySqlConnection conn = this.GetInstance())
{
dbCommand = new MySqlCommand(procName, conn);
try
{
dbCommand.CommandTimeout = CommandTimeOut;
dbCommand.CommandType = CommandType.StoredProcedure;
DbCommon.MySqlAddInParameter(dbCommand, ht);
DataTable dt = DbReader.ReaderToDataTable(dbCommand.ExecuteReader(CommandBehavior.CloseConnection));
rs = new Hashtable();
foreach (string str in ht.Keys)
{
if (str.StartsWith("OUT_"))
{
object parameterValue = dbCommand.Parameters["@" + str.Remove(0, 4)].Direction = ParameterDirection.ReturnValue;
rs[str] = parameterValue;
}
}
return(dt);
}
catch
{
return(null);
}
finally
{
dbCommand.Dispose();
conn.Close();
conn.Dispose();
}
}
}
catch (Exception e)
{
DbLog.WriteException(e);
return(null);
}
}
/// <summary>
/// 根据SQL执行,带参数
/// </summary>
/// <param name="sql">sql语句</param>
/// <param name="param">参数化</param>
/// <returns>object</returns>
public int ExecuteBySql(StringBuilder sql, SqlParam[] param)
{
int num = 0;
//创建连接
MySqlConnection conn = this.GetInstance();
//创建指令
dbCommand = new MySqlCommand(sql.ToString(), conn);
DbCommon.MySqlAddInParameter(dbCommand, param);
try
{
conn.Open();
dbTransaction = conn.BeginTransaction();
try
{
dbCommand.Transaction = dbTransaction;
num = dbCommand.ExecuteNonQuery();
dbTransaction.Commit();
}
catch (Exception e)
{
dbTransaction.Rollback();
num = -1;
DbLog.WriteException(e);
}
finally
{
conn.Close();
conn.Dispose();
}
}
catch (Exception e)
{
DbLog.WriteException(e);
}
finally
{
conn.Close();
conn.Dispose();
}
return(num);
}
/// <summary>
///调用存储过程 (不带事务)
/// </summary>
/// <param name="procName">存储过程名称</param>
/// <param name="ht">参数化</param>
/// <returns></returns>
public int ExecuteByProcNotTran(string procName, Hashtable ht)
{
int num = 0;
//创建连接
MySqlConnection conn = this.GetInstance();
//创建指令
dbCommand = new MySqlCommand(procName, conn);
dbCommand.CommandTimeout = CommandTimeOut;
dbCommand.CommandType = CommandType.StoredProcedure;
DbCommon.MySqlAddInParameter(dbCommand, ht);
try
{
conn.Open();
try
{
num = dbCommand.ExecuteNonQuery();
}
catch (Exception e)
{
num = -1;
DbLog.WriteException(e);
}
finally
{
conn.Close();
conn.Dispose();
}
}
catch (Exception e)
{
DbLog.WriteException(e);
}
finally
{
conn.Close();
conn.Dispose();
}
return(num);
}
/// <summary>
/// 摘要:
/// 执行一存储过程DataSet
/// 参数：
/// procName：存储过程名称
/// Hashtable：传入参数字段名
/// </summary>
public DataSet GetDataSetProc(string procName, Hashtable ht)
{
try
{
using (MySqlConnection conn = this.GetInstance())
{
MySqlCommand cmd = new MySqlCommand(procName, conn);
try
{
cmd.CommandTimeout = CommandTimeOut;
cmd.CommandType = CommandType.StoredProcedure;
//创建适配器
MySqlDataAdapter da = new MySqlDataAdapter();
//给变量赋值
DbCommon.MySqlAddInParameter(cmd, ht);
da.SelectCommand = cmd;
//填充数据
DataSet ds = new DataSet();
da.Fill(ds);
return(ds);
}
catch
{
return(null);
}
finally
{
cmd.Dispose();
conn.Close();
conn.Dispose();
}
}
}
catch (Exception e)
{
DbLog.WriteException(e);
return(null);
}
}
