/// <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>
/// 执行返回单一值得查询
/// </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 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="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;
}