/// <summary>
/// 调用存储过程返回指定消息（不带事务）
/// </summary>
/// <param name="procName">存储过程名称</param>
/// <param name="ht">Hashtable</param>
/// <param name="msg">OutPut Msg</param>
public int ExecuteByProcNotTranReturnMsg(string procName, Hashtable ht, ref object msg)
{
int num = 0;
try
{
DbCommand storedProcCommand = this.GetInstance().GetStoredProcCommand(procName);
DbCommon.AddInParameter(db, storedProcCommand, ht);
using (DbConnection connection = this.db.CreateConnection())
{
try
{
connection.Open();
num = this.db.ExecuteNonQuery(storedProcCommand);
num = 1;
}
catch (Exception e)
{
DbLog.WriteException(e);
}
finally
{
connection.Close();
connection.Dispose();
}
}
msg = this.db.GetParameterValue(storedProcCommand, ":Msg");
}
catch (Exception e)
{
DbLog.WriteException(e);
}
return(num);
}
/// <summary>
/// 批量调用存储过程
/// </summary>
/// <param name="text"></param>
/// <param name="param"></param>
/// <returns></returns>
public int BatchExecuteByProc(object[] text, object[] param)
{
int num = 0;
try
{
using (DbConnection connection = this.GetInstance().CreateConnection())
{
connection.Open();
dbTransaction = connection.BeginTransaction();
try
{
for (int i = 0; i < text.Length; i++)
{
string strtext = text[i].ToString().ToUpper().Replace("@", ":");
if (strtext != null)
{
SqlParam[] paramArray = (SqlParam[])param[i];
DbCommand command = null;
if (strtext.StartsWith("PROC_"))
{
command = this.db.GetStoredProcCommand(strtext);
}
else
{
command = this.db.GetSqlStringCommand(strtext);
}
DbCommon.AddInParameter(db, command, paramArray);
this.db.ExecuteNonQuery(command, dbTransaction);
}
}
dbTransaction.Commit();
num = 1;
}
catch (Exception e)
{
num = -1;
dbTransaction.Rollback();
DbLog.WriteException(e);
}
finally
{
connection.Close();
connection.Dispose();
dbTransaction.Dispose();
}
}
}
catch (Exception e)
{
DbLog.WriteException(e);
}
return(num);
}
/// <summary>
/// 摘要:
/// 执行一存储过程DataTable
/// 参数：
/// procName：存储过程名称
/// Hashtable：传入参数字段名
/// </summary>
public DataTable GetDataTableProc(string procName, Hashtable ht)
{
try
{
dbCommand = this.GetInstance().GetStoredProcCommand(procName);
DbCommon.AddInParameter(db, dbCommand, ht);
return(DbReader.ReaderToDataTable(db.ExecuteReader(dbCommand)));
}
catch (Exception e)
{
DbLog.WriteException(e);
return(null);
}
}
/// <summary>
/// 根据 SQL 返回 DataSet 数据集，带参数
/// </summary>
/// <param name="sql">sql语句</param>
/// <param name="param">参数化</param>
/// <returns>DataSet</returns>
public DataSet GetDataSetBySQL(StringBuilder sql, SqlParam[] param)
{
try
{
dbCommand = this.GetInstance().GetSqlStringCommand(Replace(sql.ToString()));
DbCommon.AddInParameter(db, dbCommand, param);
return(db.ExecuteDataSet(dbCommand));
}
catch (Exception e)
{
DbLog.WriteException(e);
return(null);
}
}
/// <summary>
/// 根据 SQL 返回 DataTable 数据集，带参数
/// </summary>
/// <param name="sql">sql语句</param>
/// <param name="param">参数化</param>
/// <returns>DataTable</returns>
public DataTable GetDataTableBySQL(StringBuilder sql, SqlParam[] param)
{
try
{
dbCommand = this.GetInstance().GetSqlStringCommand(sql.ToString());
DbCommon.AddInParameter(db, dbCommand, param);
return(DbReader.ReaderToDataTable(db.ExecuteReader(dbCommand)));
}
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)
{
try
{
dbCommand = this.GetInstance().GetSqlStringCommand(sql.ToString());
DbCommon.AddInParameter(db, dbCommand, param);
return(db.ExecuteScalar(dbCommand));
}
catch (Exception e)
{
DbLog.WriteException(e);
return(null);
}
}
/// <summary>
/// 摘要:
/// 执行一存储过程DataSet
/// 参数：
/// procName：存储过程名称
/// Hashtable：传入参数字段名
/// </summary>
public DataSet GetDataSetProc(string procName, Hashtable ht)
{
try
{
dbCommand = this.GetInstance().GetStoredProcCommand(procName);
DbCommon.AddInParameter(db, dbCommand, ht);
DataSet ds = db.ExecuteDataSet(dbCommand);
return(ds);
}
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 (DbConnection connection = this.GetInstance().CreateConnection())
{
connection.Open();
dbTransaction = connection.BeginTransaction();
try
{
for (int i = 0; i < sqls.Length; i++)
{
StringBuilder builder = (StringBuilder)sqls[i];
if (builder != null)
{
SqlParam[] paramArray = (SqlParam[])param[i];
DbCommand sqlStringCommand = this.db.GetSqlStringCommand(builder.ToString().Replace("@", ":"));
DbCommon.AddInParameter(db, sqlStringCommand, paramArray);
this.db.ExecuteNonQuery(sqlStringCommand, dbTransaction);
}
}
dbTransaction.Commit();
num = 1;
}
catch (Exception e)
{
num = -1;
dbTransaction.Rollback();
DbLog.WriteException(e);
}
finally
{
connection.Close();
connection.Dispose();
dbTransaction.Dispose();
}
}
}
catch (Exception e)
{
DbLog.WriteException(e);
}
return(num);
}
/// <summary>
/// 根据SQL执行,带参数,不带事务
/// </summary>
/// <param name="sql">sql语句</param>
/// <param name="param">参数化</param>
/// <returns>object</returns>
public int ExecuteBySqlNotTran(StringBuilder sql, SqlParam[] param)
{
int num = 0;
try
{
dbCommand = this.GetInstance().GetSqlStringCommand(Replace(sql.ToString()));
DbCommon.AddInParameter(db, dbCommand, param);
num = db.ExecuteNonQuery(dbCommand);
}
catch (Exception e)
{
num = -1;
DbLog.WriteException(e);
}
return(num);
}
/// <summary>
/// 调用存储过程(带事务)
/// </summary>
/// <param name="procName">存储过程名称</param>
/// <param name="ht">参数化</param>
public int ExecuteByProc(string procName, Hashtable ht)
{
int num = 0;
try
{
DbCommand storedProcCommand = this.GetInstance().GetStoredProcCommand(procName);
DbCommon.AddInParameter(db, storedProcCommand, ht);
using (DbConnection connection = this.db.CreateConnection())
{
connection.Open();
dbTransaction = connection.BeginTransaction();
try
{
num = this.db.ExecuteNonQuery(storedProcCommand, dbTransaction);
dbTransaction.Commit();
}
catch (Exception e)
{
num = -1;
dbTransaction.Rollback();
DbLog.WriteException(e);
}
finally
{
connection.Close();
connection.Dispose();
dbTransaction.Dispose();
}
}
}
catch (Exception e)
{
DbLog.WriteException(e);
}
return(num);
}
/// <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;
try
{
dbCommand = this.GetInstance().GetSqlStringCommand(Replace(sql.ToString()));
DbCommon.AddInParameter(db, dbCommand, param);
using (DbConnection connection = db.CreateConnection())
{
connection.Open();
dbTransaction = connection.BeginTransaction();
try
{
num = db.ExecuteNonQuery(dbCommand, dbTransaction);
dbTransaction.Commit();
}
catch (Exception e)
{
dbTransaction.Rollback();
num = -1;
DbLog.WriteException(e);
}
finally
{
connection.Close();
connection.Dispose();
dbTransaction.Dispose();
}
}
}
catch (Exception e)
{
DbLog.WriteException(e);
}
return(num);
}
/// <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)
{
dbCommand = this.GetInstance().GetSqlStringCommand(Replace(sql.ToString()));
DbCommon.AddInParameter(db, dbCommand, param);
return(DbReader.ReaderToList <T>(db.ExecuteReader(dbCommand)));
}
