public long OnExecuted(IDbCommand cmd, int recordAffected)
{
long elapsedMilliseconds;
CurrCommandLog.WriteLog(cmd, "AdoHelper", out elapsedMilliseconds);
CurrCommandLog.WriteLog("RecordAffected:" + recordAffected, "AdoHelper");
return(elapsedMilliseconds);
}
/// <summary>
/// 执行查询,并以指定的(具有数据架构的)数据集来填充数据
/// </summary>
/// <param name="SQL">查询语句</param>
/// <param name="commandType">命令类型</param>
/// <param name="parameters">查询参数</param>
/// <param name="schemaDataSet">指定的(具有数据架构的)数据集</param>
/// <returns>具有数据的数据集</returns>
public virtual DataSet ExecuteDataSetWithSchema(string SQL, CommandType commandType, IDataParameter[] parameters, DataSet schemaDataSet)
{
IDbConnection conn = GetConnection();
IDbCommand cmd = conn.CreateCommand();
CompleteCommand(cmd, ref SQL, ref commandType, ref parameters);
IDataAdapter ada = GetDataAdapter(cmd);
CommandLog cmdLog = new CommandLog(true);
try
{
//使用MyDB.Intance 连接不能及时关闭？待测试
ada.Fill(schemaDataSet);//FillSchema(ds,SchemaType.Mapped )
}
catch (Exception ex)
{
ErrorMessage = ex.Message;
bool inTransaction = cmd.Transaction == null ? false : true;
cmdLog.WriteErrLog(cmd, "AdoHelper:" + ErrorMessage);
if (OnErrorThrow)
{
throw new QueryException(ex.Message, cmd.CommandText, commandType, parameters, inTransaction, conn.ConnectionString);
}
}
finally
{
cmdLog.WriteLog(cmd, "AdoHelper", out _elapsedMilliseconds);
CloseConnection(conn, cmd);
}
return(schemaDataSet);
}
// /// <summary>
// /// 记录命令信息
// /// </summary>
// /// <param name="command"></param>
// private void RecordCommandLog(IDbCommand command)
// {
// WriteLog("'"+DateTime.Now.ToString ()+ " @AdoHelper 执行命令：\rSQL=\""+command.CommandText+"\"\r'命令类型："+command.CommandType.ToString ());
// if(command.Parameters.Count >0)
// {
// WriteLog("'共有 "+command.Parameters.Count+" 个命令参数：");
// for(int i=0;i<command.Parameters.Count ;i++)
// {
// IDataParameter p=(IDataParameter)command.Parameters[i];
// WriteLog ("Parameter["+p.ParameterName+"]=\""+p.Value.ToString ()+"\" 'DbType=" +p.DbType.ToString ());
// }
// }
// WriteLog ("\r\n");
//
// }
//
// /// <summary>
// /// 写入日志
// /// </summary>
// /// <param name="log"></param>
// private void WriteLog(string log)
// {
// StreamWriter sw=File.AppendText (this.DataLogFile );;
// sw.WriteLine (log);
// sw.Close ();
// }
/// <summary>
/// 执行不返回值的查询，如果此查询出现了错误并且设置 OnErrorThrow 属性为 是，将抛出错误；否则将返回 -1，此时请检查ErrorMessage属性；
/// 如果此查询在事务中并且出现了错误，将根据 OnErrorRollback 属性设置是否自动回滚事务。
/// </summary>
/// <param name="SQL">SQL</param>
/// <param name="commandType">命令类型</param>
/// <param name="parameters">参数数组</param>
/// <returns>受影响的行数</returns>
public virtual int ExecuteNonQuery(string SQL, CommandType commandType, IDataParameter[] parameters)
{
ErrorMessage = "";
IDbConnection conn = GetConnection();
if (conn.State != ConnectionState.Open) //连接已经打开，不能切换连接字符串，感谢网友 “长的没礼貌”发现此Bug
{
conn.ConnectionString = this.DataWriteConnectionString;
}
IDbCommand cmd = conn.CreateCommand();
CompleteCommand(cmd, ref SQL, ref commandType, ref parameters);
CommandLog cmdLog = new CommandLog(true);
int result = -1;
try
{
result = cmd.ExecuteNonQuery();
//如果开启事务，则由上层调用者决定何时提交事务
}
catch (Exception ex)
{
ErrorMessage = ex.Message;
bool inTransaction = cmd.Transaction == null ? false : true;
//如果开启事务，那么此处应该回退事务
if (cmd.Transaction != null && OnErrorRollback)
{
cmd.Transaction.Rollback();
}
cmdLog.WriteErrLog(cmd, "AdoHelper:" + ErrorMessage);
if (OnErrorThrow)
{
throw new QueryException(ex.Message, cmd.CommandText, commandType, parameters, inTransaction, conn.ConnectionString);
}
}
finally
{
//if (cmd.Transaction == null && conn.State == ConnectionState.Open)
// conn.Close();
CloseConnection(conn, cmd);
}
cmdLog.WriteLog(cmd, "AdoHelper", out _elapsedMilliseconds);
return(result);
}
/// <summary>
/// 执行强类型的数据集查询
/// </summary>
/// <param name="SQL">SQL语句</param>
/// <param name="commandType">命令类型</param>
/// <param name="parameters">查询参数</param>
/// <param name="schemaDataSet">强类型的数据集</param>
/// <param name="tableName">要填充的表名称</param>
/// <returns></returns>
public DataSet ExecuteTypedDataSet(string SQL, CommandType commandType, IDataParameter[] parameters, DataSet schemaDataSet, string tableName)
{
bool flag = false;
for (int i = 0; i < schemaDataSet.Tables.Count; i++)
{
if (schemaDataSet.Tables[i].TableName == tableName)
{
flag = true;
break;
}
}
if (!flag)
{
throw new ArgumentException("在强类型的数据集中，没有找到制定的数据表明称！");
}
IDbConnection conn = GetConnection();
IDbCommand cmd = conn.CreateCommand();
CompleteCommand(cmd, SQL, commandType, parameters);
SqlDataAdapter ada = new SqlDataAdapter((SqlCommand)cmd);
CommandLog cmdLog = new CommandLog(true);
try
{
ada.Fill(schemaDataSet, tableName);
}
catch (Exception ex)
{
ErrorMessage = ex.Message;
bool inTransaction = cmd.Transaction == null ? false : true;
cmdLog.WriteErrLog(cmd, "AdoHelper:" + ErrorMessage);
if (OnErrorThrow)
{
throw new QueryException(ErrorMessage, cmd.CommandText, commandType, parameters, inTransaction, conn.ConnectionString, ex);
}
}
finally
{
cmdLog.WriteLog(cmd, "AdoHelper", out _elapsedMilliseconds);
CloseConnection(conn, cmd);
}
return(schemaDataSet);
}
/// <summary>
/// 根据查询返回数据阅读器对象
/// </summary>
/// <param name="SQL">SQL</param>
/// <param name="commandType">命令类型</param>
/// <param name="cmdBehavior">对查询和返回结果有影响的说明</param>
/// <param name="parameters">参数数组</param>
/// <returns>数据阅读器</returns>
protected virtual IDataReader ExecuteDataReader(ref string SQL, CommandType commandType, CommandBehavior cmdBehavior, ref IDataParameter[] parameters)
{
IDbConnection conn = GetConnection();
IDbCommand cmd = conn.CreateCommand();
CompleteCommand(cmd, ref SQL, ref commandType, ref parameters);
CommandLog cmdLog = new CommandLog(true);
IDataReader reader = null;
try
{
//如果命令对象的事务对象为空，那么强制在读取完数据后关闭阅读器的数据库连接 2008.3.20
if (cmd.Transaction == null && cmdBehavior == CommandBehavior.Default)
{
cmdBehavior = CommandBehavior.CloseConnection;
}
reader = cmd.ExecuteReader(cmdBehavior);
}
catch (Exception ex)
{
ErrorMessage = ex.Message;
//只有出现了错误而且没有开启事务，可以关闭连结
//if (cmd.Transaction == null && conn.State == ConnectionState.Open)
// conn.Close();
CloseConnection(conn, cmd);
bool inTransaction = cmd.Transaction == null ? false : true;
cmdLog.WriteErrLog(cmd, "AdoHelper:" + ErrorMessage);
if (OnErrorThrow)
{
throw new QueryException(ex.Message, cmd.CommandText, commandType, parameters, inTransaction, conn.ConnectionString);
}
}
cmdLog.WriteLog(cmd, "AdoHelper", out _elapsedMilliseconds);
cmd.Parameters.Clear();
return(reader);
}
/// <summary>
/// 执行返回单一值得查询
/// </summary>
/// <param name="SQL">SQL</param>
/// <param name="commandType">命令类型</param>
/// <param name="parameters">参数数组</param>
/// <returns>查询结果</returns>
public virtual object ExecuteScalar(string SQL, CommandType commandType, IDataParameter[] parameters)
{
IDbConnection conn = GetConnection();
IDbCommand cmd = conn.CreateCommand();
CompleteCommand(cmd, ref SQL, ref commandType, ref parameters);
CommandLog cmdLog = new CommandLog(true);
object result = null;
try
{
result = cmd.ExecuteScalar();
//如果开启事务，则由上层调用者决定何时提交事务
}
catch (Exception ex)
{
ErrorMessage = ex.Message;
//如果开启事务，那么此处应该回退事务
//if(cmd.Transaction!=null)
// cmd.Transaction.Rollback ();
bool inTransaction = cmd.Transaction == null ? false : true;
cmdLog.WriteErrLog(cmd, "AdoHelper:" + ErrorMessage);
if (OnErrorThrow)
{
throw new QueryException(ex.Message, cmd.CommandText, commandType, parameters, inTransaction, conn.ConnectionString);
}
}
finally
{
//if (cmd.Transaction == null && conn.State == ConnectionState.Open)
// conn.Close();
CloseConnection(conn, cmd);
}
cmdLog.WriteLog(cmd, "AdoHelper", out _elapsedMilliseconds);
return(result);
}
/// <summary>
/// 执行查询,并以指定的(具有数据架构的)数据集来填充数据
/// </summary>
/// <param name="SQL">查询语句</param>
/// <param name="commandType">命令类型</param>
/// <param name="parameters">查询参数</param>
/// <param name="schemaDataSet">指定的(具有数据架构的)数据集</param>
/// <returns>具有数据的数据集</returns>
public override DataSet ExecuteDataSetWithSchema(string SQL, CommandType commandType, IDataParameter[] parameters, DataSet schemaDataSet)
{
IDbConnection conn = GetConnection();
IDbCommand cmd = conn.CreateCommand();
CompleteCommand(cmd, SQL, commandType, parameters);
SqlDataAdapter ada = new SqlDataAdapter((SqlCommand)cmd);
CommandLog cmdLog = new CommandLog(true);
try
{
if (schemaDataSet.Tables.Count > 0)
{
ada.Fill(schemaDataSet, schemaDataSet.Tables[0].TableName);
}
else
{
ada.Fill(schemaDataSet);
}
}
catch (Exception ex)
{
ErrorMessage = ex.Message;
bool inTransaction = cmd.Transaction == null ? false : true;
cmdLog.WriteErrLog(cmd, "AdoHelper:" + ErrorMessage);
if (OnErrorThrow)
{
throw new QueryException(ErrorMessage, cmd.CommandText, commandType, parameters, inTransaction, conn.ConnectionString, ex);
}
}
finally
{
cmdLog.WriteLog(cmd, "AdoHelper", out _elapsedMilliseconds);
CloseConnection(conn, cmd);
}
return(schemaDataSet);
}
/// <summary>
/// SqlServer 执行插入数据的查询，如果执行成功，受影响的行数只会返回1
/// </summary>
/// <param name="SQL">插入数据的SQL</param>
/// <param name="commandType">命令类型</param>
/// <param name="parameters">参数数组</param>
/// <param name="ID">要传出的本次操作的新插入数据行的主键ID值</param>
/// <returns>本次查询受影响的行数</returns>
public override int ExecuteInsertQuery(string SQL, CommandType commandType, IDataParameter[] parameters, ref object ID)
{
IDbConnection conn = GetConnection();
IDbCommand cmd = conn.CreateCommand();
CompleteCommand(cmd, ref SQL, ref commandType, ref parameters);
CommandLog cmdLog = new CommandLog(true);
bool inner = false;
int result = -1;
ID = 0;
try
{
if (cmd.Transaction == null)
{
inner = true;
cmd.Transaction = conn.BeginTransaction();
}
cmd.CommandText = SQL + " ;SELECT SCOPE_IDENTITY();";
ID = cmd.ExecuteScalar();
//如果在内部开启了事务则提交事务，否则外部调用者决定何时提交事务
result = 1;
if (inner)
{
cmd.Transaction.Commit();
cmd.Transaction = null;
}
}
catch (Exception ex)
{
ErrorMessage = ex.Message;
bool inTransaction = cmd.Transaction == null ? false : true;
if (cmd.Transaction != null)
{
cmd.Transaction.Rollback();
}
if (inner)
{
cmd.Transaction = null;
}
cmdLog.WriteErrLog(cmd, "AdoHelper:" + ErrorMessage);
if (OnErrorThrow)
{
throw new QueryException(ex.Message, cmd.CommandText, commandType, parameters, inTransaction, conn.ConnectionString);
}
}
finally
{
if (cmd.Transaction == null && conn.State == ConnectionState.Open)
{
conn.Close();
}
}
long _elapsedMilliseconds;
cmdLog.WriteLog(cmd, "AdoHelper", out _elapsedMilliseconds);
base.ElapsedMilliseconds = _elapsedMilliseconds;
return(result);
}
/// <summary>
/// SqlServer 执行插入数据的查询，如果执行成功，受影响的行数只会返回1
/// </summary>
/// <param name="SQL">插入数据的SQL</param>
/// <param name="commandType">命令类型</param>
/// <param name="parameters">参数数组</param>
/// <param name="ID">要传出的本次操作的新插入数据行的主键ID值</param>
/// <returns>本次查询受影响的行数</returns>
public override int ExecuteInsertQuery(string SQL, CommandType commandType, IDataParameter[] parameters, ref object ID)
{
IDbConnection conn = GetConnection();
IDbCommand cmd = conn.CreateCommand();
CompleteCommand(cmd, ref SQL, ref commandType, ref parameters);
CommandLog cmdLog = new CommandLog(true);
bool inner = false;
int result = -1;
ID = 0;
try
{
if (cmd.Transaction == null)
{
inner = true;
cmd.Transaction = conn.BeginTransaction();
}
cmd.CommandText = SQL + " ;SELECT SCOPE_IDENTITY();";
ID = cmd.ExecuteScalar();
//如果在内部开启了事务则提交事务，否则外部调用者决定何时提交事务
result = 1;
if (inner)
{
cmd.Transaction.Commit();
cmd.Transaction = null;
}
}
catch (Exception ex)
{
ErrorMessage = ex.Message;
bool inTransaction = cmd.Transaction == null ? false : true;
if (cmd.Transaction != null)
cmd.Transaction.Rollback();
if (inner)
cmd.Transaction = null;
cmdLog.WriteErrLog(cmd, "AdoHelper:" + ErrorMessage);
if (OnErrorThrow)
{
throw new QueryException(ex.Message, cmd.CommandText, commandType, parameters, inTransaction, conn.ConnectionString);
}
}
finally
{
if (cmd.Transaction == null && conn.State == ConnectionState.Open)
conn.Close();
}
long _elapsedMilliseconds;
cmdLog.WriteLog(cmd, "AdoHelper", out _elapsedMilliseconds);
base.ElapsedMilliseconds = _elapsedMilliseconds;
return result;
}
public override int ExecuteNonQuery(string SQL, CommandType commandType, IDataParameter[] parameters)
{
ErrorMessage = "";
IDbConnection conn = GetConnection();
IDbCommand cmd = conn.CreateCommand();
//CompleteCommand(cmd, ref SQL, ref commandType, ref parameters);
cmd.CommandText = SqlServerCompatible ? PrepareSQL(ref SQL) : SQL;
cmd.CommandType = commandType;
cmd.Transaction = this.Transaction;
if (this.CommandTimeOut > 0)
{
cmd.CommandTimeout = this.CommandTimeOut;
}
if (parameters != null)
{
for (int i = 0; i < parameters.Length; i++)
{
if (parameters[i] != null)
{
IDataParameter para = (IDataParameter)((ICloneable)parameters[i]).Clone();
//SQLite参数化查询，即可解决坑爹的“日期类型”字段问题。
//采用拼接字符串的方式，需要将日期值 ToString(s)
if (para.Value == null)
{
//if (para.DbType == DbType.DateTime)
// para.Value = new DateTime(1900, 1, 1).ToUniversalTime();
//else
para.Value = DBNull.Value;
}
else
{
//{
// if (para.Value.GetType() == typeof(DateTime))
// {
// para.Value = ((DateTime)para.Value).ToUniversalTime();
// }
//}
cmd.Parameters.Add(para);
}
}
}
}
if (cmd.Connection.State != ConnectionState.Open)
{
cmd.Connection.Open();
}
CommandLog cmdLog = new CommandLog(true);
int result = -1;
try
{
result = cmd.ExecuteNonQuery();
//如果开启事务，则由上层调用者决定何时提交事务
}
catch (Exception ex)
{
ErrorMessage = ex.Message;
bool inTransaction = cmd.Transaction == null ? false : true;
//如果开启事务，那么此处应该回退事务
if (cmd.Transaction != null && OnErrorRollback)
{
cmd.Transaction.Rollback();
}
cmdLog.WriteErrLog(cmd, "AdoHelper:" + ErrorMessage);
if (OnErrorThrow)
{
throw new QueryException(ex.Message, cmd.CommandText, commandType, parameters, inTransaction, conn.ConnectionString);
}
}
finally
{
//if (cmd.Transaction == null && conn.State == ConnectionState.Open)
// conn.Close();
CloseConnection(conn, cmd);
}
cmdLog.WriteLog(cmd, "AdoHelper", out base._elapsedMilliseconds);
return(result);
}
/// <summary>
/// 执行插入数据的查询，仅限于Access，SqlServer
/// </summary>
/// <param name="SQL">插入数据的SQL</param>
/// <param name="commandType">命令类型</param>
/// <param name="parameters">参数数组</param>
/// <param name="ID">要传出的本次操作的新插入数据行的主键ID值</param>
/// <returns>本次查询受影响的行数</returns>
public virtual int ExecuteInsertQuery(string SQL, CommandType commandType, IDataParameter[] parameters, ref object ID)
{
IDbConnection conn = GetConnection();
if (conn.State != ConnectionState.Open) //连接已经打开，不能切换连接字符串，感谢网友 “长的没礼貌”发现此Bug
{
conn.ConnectionString = this.DataWriteConnectionString;
}
IDbCommand cmd = conn.CreateCommand();
CompleteCommand(cmd, ref SQL, ref commandType, ref parameters);
CommandLog cmdLog = new CommandLog(true);
bool inner = false;
int result = -1;
ID = 0;
try
{
if (cmd.Transaction == null)
{
inner = true;
cmd.Transaction = conn.BeginTransaction();
}
result = cmd.ExecuteNonQuery();
cmd.CommandText = this.InsertKey;// "SELECT @@IDENTITY ";
ID = cmd.ExecuteScalar();
//如果在内部开启了事务则提交事务，否则外部调用者决定何时提交事务
if (inner)
{
cmd.Transaction.Commit();
cmd.Transaction = null;
}
}
catch (Exception ex)
{
ErrorMessage = ex.Message;
bool inTransaction = cmd.Transaction == null ? false : true;
if (cmd.Transaction != null)
{
cmd.Transaction.Rollback();
}
if (inner)
{
cmd.Transaction = null;
}
cmdLog.WriteErrLog(cmd, "AdoHelper:" + ErrorMessage);
if (OnErrorThrow)
{
throw new QueryException(ex.Message, cmd.CommandText, commandType, parameters, inTransaction, conn.ConnectionString);
}
}
finally
{
cmdLog.WriteLog(cmd, "AdoHelper", out _elapsedMilliseconds);
CloseConnection(conn, cmd);
}
return(result);
}
/// <summary>
/// 执行查询,并以指定的(具有数据架构的)数据集来填充数据
/// </summary>
/// <param name="SQL">查询语句</param>
/// <param name="commandType">命令类型</param>
/// <param name="parameters">查询参数</param>
/// <param name="schemaDataSet">指定的(具有数据架构的)数据集</param>
/// <returns>具有数据的数据集</returns>
public virtual DataSet ExecuteDataSetWithSchema(string SQL, CommandType commandType, IDataParameter[] parameters, DataSet schemaDataSet)
{
IDbConnection conn = GetConnection();
IDbCommand cmd = conn.CreateCommand();
CompleteCommand(cmd, ref SQL, ref commandType, ref parameters);
IDataAdapter ada = GetDataAdapter(cmd);
CommandLog cmdLog = new CommandLog(true);
try
{
ada.Fill(schemaDataSet);//FillSchema(ds,SchemaType.Mapped )
}
catch (Exception ex)
{
ErrorMessage = ex.Message;
bool inTransaction = cmd.Transaction == null ? false : true;
cmdLog.WriteErrLog(cmd, "AdoHelper:" + ErrorMessage);
if (OnErrorThrow)
{
throw new QueryException(ex.Message, cmd.CommandText, commandType, parameters, inTransaction, conn.ConnectionString);
}
}
finally
{
//if (cmd.Transaction == null && conn.State == ConnectionState.Open)
// conn.Close();
CloseConnection(conn, cmd);
}
cmdLog.WriteLog(cmd, "AdoHelper", out _elapsedMilliseconds);
return schemaDataSet;
}
/// <summary>
/// 执行返回单一值得查询
/// </summary>
/// <param name="SQL">SQL</param>
/// <param name="commandType">命令类型</param>
/// <param name="parameters">参数数组</param>
/// <returns>查询结果</returns>
public virtual object ExecuteScalar(string SQL, CommandType commandType, IDataParameter[] parameters)
{
IDbConnection conn = GetConnection();
IDbCommand cmd = conn.CreateCommand();
CompleteCommand(cmd, ref SQL, ref commandType, ref parameters);
CommandLog cmdLog = new CommandLog(true);
object result = null;
try
{
result = cmd.ExecuteScalar();
//如果开启事务，则由上层调用者决定何时提交事务
}
catch (Exception ex)
{
ErrorMessage = ex.Message;
//如果开启事务，那么此处应该回退事务
//if(cmd.Transaction!=null)
// cmd.Transaction.Rollback ();
bool inTransaction = cmd.Transaction == null ? false : true;
cmdLog.WriteErrLog(cmd, "AdoHelper:" + ErrorMessage);
if (OnErrorThrow)
{
throw new QueryException(ex.Message, cmd.CommandText, commandType, parameters, inTransaction, conn.ConnectionString);
}
}
finally
{
//if (cmd.Transaction == null && conn.State == ConnectionState.Open)
// conn.Close();
CloseConnection(conn, cmd);
}
cmdLog.WriteLog(cmd, "AdoHelper", out _elapsedMilliseconds);
return result;
}
/// <summary>
/// 根据查询返回数据阅读器对象
/// </summary>
/// <param name="SQL">SQL</param>
/// <param name="commandType">命令类型</param>
/// <param name="cmdBehavior">对查询和返回结果有影响的说明</param>
/// <param name="parameters">参数数组</param>
/// <returns>数据阅读器</returns>
protected virtual IDataReader ExecuteDataReader(ref string SQL, CommandType commandType, CommandBehavior cmdBehavior, ref IDataParameter[] parameters)
{
IDbConnection conn = GetConnection();
IDbCommand cmd = conn.CreateCommand();
CompleteCommand(cmd, ref SQL, ref commandType, ref parameters);
CommandLog cmdLog = new CommandLog(true);
IDataReader reader = null;
try
{
//如果命令对象的事务对象为空，那么强制在读取完数据后关闭阅读器的数据库连接 2008.3.20
if (cmd.Transaction == null && cmdBehavior == CommandBehavior.Default)
cmdBehavior = CommandBehavior.CloseConnection;
reader = cmd.ExecuteReader(cmdBehavior);
}
catch (Exception ex)
{
ErrorMessage = ex.Message;
//只有出现了错误而且没有开启事务，可以关闭连结
//if (cmd.Transaction == null && conn.State == ConnectionState.Open)
// conn.Close();
CloseConnection(conn, cmd);
bool inTransaction = cmd.Transaction == null ? false : true;
cmdLog.WriteErrLog(cmd, "AdoHelper:" + ErrorMessage);
if (OnErrorThrow)
{
throw new QueryException(ex.Message, cmd.CommandText, commandType, parameters, inTransaction, conn.ConnectionString);
}
}
cmdLog.WriteLog(cmd, "AdoHelper", out _elapsedMilliseconds);
return reader;
}