/// <summary>
/// 创建并准备数据执行命令对象，同时以适当的 CommandBehavior 属性调用 ExecuteReader 方法。
/// </summary>
/// <remarks>
/// 如果创建并打开数据库连接对象，当 DataReader 被关闭时必须关闭数据库连接。
/// 如果是由调用方提供数据库连接对象，不必进行任何关闭操作，由调用方进行管理。
/// </remarks>
/// <param name="connection">有效的数据库连接对象</param>
/// <param name="transaction">有效的事务对象，或者为 null</param>
/// <param name="commandType">获取或设置一个值，该值指示如何解释 CommandText 属性</param>
/// <param name="commandText">获取或设置要对数据源执行的 Transact-SQL 语句或存储过程</param>
/// <param name="commandParameters">用来执行命令的参数数组，如果不必提供参数数组可设置为 null</param>
/// <param name="connectionOwnership">指示调用方是否提供数据库连接，或者由数据访问帮助类创建</param>
/// <param name="commandTimeout">设定Command执行时间，秒，大于0有效</param>
/// <returns>执行命令后返回包含结果的数据读取对象</returns>
private static SqlDataReader ExecuteReaderWithTimeout(SqlConnection connection,SqlTransaction transaction,CommandType commandType,string commandText,int commandTimeout,IEnumerable<SqlParameter> commandParameters,SqlConnectionOwnership connectionOwnership)
{
if(connection == null){
throw new ArgumentNullException("connection");
}
bool mustCloseConnection = false;
SqlCommand cmd = new SqlCommand();
SqlDataReader result;
try{
PrepareCommand(cmd,connection,transaction,commandType,commandText,commandParameters,out mustCloseConnection);
if(commandTimeout > 0){
cmd.CommandTimeout = commandTimeout;
}
SqlDataReader dataReader;
if(connectionOwnership == SqlConnectionOwnership.External){
dataReader = cmd.ExecuteReader();
}
else{
dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
bool canClear = true;
foreach(SqlParameter commandParameter in cmd.Parameters){
if(commandParameter.Direction != ParameterDirection.Input){
canClear = false;
}
}
if(canClear){
cmd.Parameters.Clear();
}
result = dataReader;
}
catch{
if(mustCloseConnection){
connection.Close();
}
throw;
}
return result;
}
public static SqlDataReader ExecuteReader(SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, SqlParameter[] parameterArr, SqlConnectionOwnership connectionOwnership)
{
if (connection == null)
{
throw new ArgumentNullException("connection");
}
bool mustCloseConnection = false;
SqlDataReader reader = null;
SqlCommand cmd = new SqlCommand();
try
{
PrepareCommand(cmd, connection, transaction, commandType, commandText, parameterArr, out mustCloseConnection);
if (connectionOwnership == SqlConnectionOwnership.External)
{
reader = cmd.ExecuteReader();
}
else
{
reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
bool flag = true;
foreach (SqlParameter item in cmd.Parameters)
{
//有一个不是input
if (item.Direction != ParameterDirection.Input)
{
flag = false;
}
}
//不能有输入
if (flag)
{
//清除parameterArr引用
cmd.Parameters.Clear();
}
}
catch (Exception e)
{
if (mustCloseConnection)
{
connection.Close();
}
throw;
}
return(reader);
}
private static SqlDataReader ExecuteReader(SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, SqlParameter[] commandParameters, SqlConnectionOwnership connectionOwnership)
{
SqlDataReader reader2;
if (connection == null)
{
throw new ArgumentNullException("connection");
}
bool mustCloseConnection = false;
SqlCommand command = new SqlCommand();
try
{
SqlDataReader reader;
PrepareCommand(command, connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection);
if (connectionOwnership == SqlConnectionOwnership.External)
{
reader = command.ExecuteReader();
}
else
{
reader = command.ExecuteReader(CommandBehavior.CloseConnection);
}
bool flag2 = true;
foreach (SqlParameter parameter in command.Parameters)
{
if (parameter.Direction != ParameterDirection.Input)
{
flag2 = false;
}
}
if (flag2)
{
command.Parameters.Clear();
}
reader2 = reader;
}
catch
{
if (mustCloseConnection)
{
connection.Close();
}
throw;
}
return reader2;
}
/// <summary>
/// Create and prepare a SqlCommand, and call ExecuteReader with the appropriate CommandBehavior.
/// </summary>
/// <remarks>
/// If we created and opened the connection, we want the connection to be closed when the DataReader is closed.
///
/// If the caller provided the connection, we want to leave it to them to manage.
/// </remarks>
/// <param name="connection">A valid SqlConnection, on which to execute this command</param>
/// <param name="transaction">A valid SqlTransaction, or 'null'</param>
/// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">The stored procedure name or T-SQL command</param>
/// <param name="commandParameters">An array of SqlParameters to be associated with the command or 'null' if no parameters are required</param>
/// <param name="connectionOwnership">Indicates whether the connection parameter was provided by the caller, or created by SqlHelper</param>
/// <returns>SqlDataReader containing the results of the command</returns>
private static SqlDataReader ExecuteReader(SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, SqlParameter[] commandParameters, SqlConnectionOwnership connectionOwnership)
{
if (connection == null) throw new ArgumentNullException("connection");
bool mustCloseConnection = false;
// Create a command and prepare it for execution
SqlCommand cmd = new SqlCommand();
try
{
PrepareCommand(cmd, connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection);
// Create a reader
SqlDataReader dataReader;
// Call ExecuteReader with the appropriate CommandBehavior
if (connectionOwnership == SqlConnectionOwnership.External)
{
dataReader = cmd.ExecuteReader();
}
else
{
dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
// Detach the SqlParameters from the command object, so they can be used again.
// HACK: There is a problem here, the output parameter values are fletched
// when the reader is closed, so if the parameters are detached from the command
// then the SqlReader can´t set its values.
// When this happen, the parameters can´t be used again in other command.
bool canClear = true;
foreach (SqlParameter commandParameter in cmd.Parameters)
{
if (commandParameter.Direction != ParameterDirection.Input)
canClear = false;
}
if (canClear)
{
cmd.Parameters.Clear();
}
return dataReader;
}
catch
{
if (mustCloseConnection)
connection.Close();
throw;
}
}
/// <summary>
/// ˽�з���--OleDbDataReader
/// </summary>
/// <param name="connection">���ݿ����Ӵ�</param>
/// <param name="transaction">����</param>
/// <param name="commandType">ָ������</param>
/// <param name="commandText">ִ���ı�</param>
/// <param name="commandParameters">������</param>
/// <param name="connectionOwnership"></param>
/// <returns></returns>
private static OleDbDataReader ExecuteReader(OleDbConnection connection, OleDbTransaction transaction, CommandType commandType, string commandText, OleDbParameter[] commandParameters, SqlConnectionOwnership connectionOwnership)
{
if (connection == null)
{
throw new ArgumentNullException("connection");
}
bool mustCloseConnection = false;
OleDbCommand cmd = new OleDbCommand();
try
{
PrepareCommand(cmd, connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection);
OleDbDataReader dataReader;
if (connectionOwnership == SqlConnectionOwnership.External)
{
dataReader = cmd.ExecuteReader();
}
else
{
dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
bool canClear = true;
foreach (OleDbParameter commandParameter in cmd.Parameters)
{
if (commandParameter.Direction != ParameterDirection.Input)
canClear = false;
}
if (canClear)
{
cmd.Parameters.Clear();
}
return dataReader;
}
catch
{
if (mustCloseConnection)
connection.Close();
throw;
}
}
/// <summary>
/// 执行指定数据库连接对象的数据阅读器.
/// </summary>
/// <remarks>
/// 如果是SqlHelper打开连接,当连接关闭DataReader也将关闭.
/// 如果是调用都打开连接,DataReader由调用都管理.
/// </remarks>
/// <param name="connection">一个有效的数据库连接对象</param>
/// <param name="transaction">一个有效的事务,或者为 'null'</param>
/// <param name="commandType">命令类型 (存储过程,命令文本或其它)</param>
/// <param name="commandText">存储过程名或T-SQL语句</param>
/// <param name="commandParameters">SqlParameters参数数组,如果没有参数则为'null'</param>
/// <param name="connectionOwnership">标识数据库连接对象是由调用者提供还是由SqlHelper提供</param>
/// <returns>返回包含结果集的SqlDataReader</returns>
private static SqlDataReader ExecuteReader(SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, SqlParameter[] commandParameters, SqlConnectionOwnership connectionOwnership)
{
if (connection == null)
{
throw new ArgumentNullException("connection");
}
bool mustCloseConnection = false;
// 创建命令
SqlCommand cmd = new SqlCommand();
cmd.CommandTimeout = 30;
try
{
PrepareCommand(cmd, connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection);
// 创建数据阅读器
SqlDataReader dataReader;
if (connectionOwnership == SqlConnectionOwnership.External)
{
dataReader = cmd.ExecuteReader();
}
else
{
dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
// 清除参数,以便再次使用..
// HACK: There is a problem here, the output parameter values are fletched
// when the reader is closed, so if the parameters are detached from the command
// then the SqlReader can磘 set its values.
// When this happen, the parameters can磘 be used again in other command.
bool canClear = true;
foreach (SqlParameter commandParameter in cmd.Parameters)
{
if (commandParameter.Direction != ParameterDirection.Input)
{
canClear = false;
}
}
if (canClear)
{
cmd.Parameters.Clear();
}
return(dataReader);
}
catch
{
if (mustCloseConnection)
{
connection.Close();
}
throw;
}
}
private static SqlDataReader ExecuteReader(SqlConnection connection, CommandType commandType, string commandText, SqlConnectionOwnership connectionOwnership, params SqlParameter[] commandParameters)
{
return ExecuteReader(connection, null, commandType, commandText, connectionOwnership, commandParameters);
}
/// <summary>
/// 执行数据库操作命令（返回Reader）
/// </summary>
/// <param name="connection">数据库连接</param>
/// <param name="transaction">数据库事务</param>
/// <param name="commandType">命令类型</param>
/// <param name="commandText">命令内容</param>
/// <param name="commandParameters">命令内容</param>
/// <param name="connectionOwnership">连接属性</param>
/// <returns>执行结果</returns>
private static SqlDataReader ExecuteReader(SqlConnection connection, SqlTransaction transaction,
CommandType commandType, string commandText, IEnumerable<SqlParameter> commandParameters,
SqlConnectionOwnership connectionOwnership)
{
if (connection == null) throw new ArgumentNullException("connection");
var mustCloseConnection = false;
var cmd = new SqlCommand();
try
{
PrepareCommand(cmd, connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection);
var dataReader = connectionOwnership ==
SqlConnectionOwnership.External ? cmd.ExecuteReader() :
cmd.ExecuteReader(CommandBehavior.CloseConnection);
var canClear = true;
foreach (SqlParameter commandParameter in cmd.Parameters)
{
if (commandParameter.Direction != ParameterDirection.Input)
canClear = false;
}
if (canClear)
{
cmd.Parameters.Clear();
}
return dataReader;
}
catch
{
if (mustCloseConnection)
connection.Close();
throw;
}
}
/// <summary>
/// Creates a SqlDataReader by running the stored procedure or query and placing the results
/// of the query/proc into the given tablename.
/// this is used in the transaction
/// </summary>
/// <param name="connection">a SqlConnection instance<</param>
/// <param name="transaction">the SqlTransaction object,by it we get the SqlConnection object</param>
/// <param name="commandType">the value of enum <c>CommandType</c></param>
/// <param name="commandText">the query string or the stored procedure</param>
/// <param name="commandParameters">Array of SqlParameter objects containing parameters to the stored proc</param>
/// <param name="connectionOwnership">the value of enum <c>SqlConnectionOwnership</c></param>
/// <returns>Newly instantiated SqlDataReader instance</returns>
private static SqlDataReader ExecuteReader(SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText
, SqlParameter[] commandParameters, SqlConnectionOwnership connectionOwnership)
{
SqlDataReader sqlDataReader;
SqlCommand sqlCommand = new SqlCommand();
sqlCommand.CommandTimeout = 1800;
SqlHelper.PrepareCommand(sqlCommand, connection, transaction, commandType, commandText, commandParameters);
if (connectionOwnership == SqlConnectionOwnership.External)
{
sqlDataReader = sqlCommand.ExecuteReader();
}
else
{
sqlDataReader = sqlCommand.ExecuteReader(CommandBehavior.CloseConnection);
}
sqlCommand.Parameters.Clear();
return sqlDataReader;
}
private static SqlDataReader ExecuteReader(SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, SqlParameter[] commandParameters, SqlConnectionOwnership connectionOwnership)
{
SqlDataReader reader2;
if (connection == null)
{
throw new ArgumentNullException("connection");
}
bool mustCloseConnection = false;
SqlCommand command = new SqlCommand();
try
{
SqlDataReader reader;
PrepareCommand(command, connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection);
if (connectionOwnership == SqlConnectionOwnership.External)
{
reader = command.ExecuteReader();
}
else
{
reader = command.ExecuteReader(CommandBehavior.CloseConnection);
}
bool flag2 = true;
foreach (SqlParameter parameter in command.Parameters)
{
if (parameter.Direction != ParameterDirection.Input)
{
flag2 = false;
}
}
if (flag2)
{
command.Parameters.Clear();
}
reader2 = reader;
}
catch
{
if (mustCloseConnection)
{
connection.Close();
}
throw;
}
return(reader2);
}
private static SqlDataReader ExecuteReader(SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, SqlParameter[] commandParameters, SqlConnectionOwnership connectionOwnership)
{
SqlDataReader dr;
SqlCommand cmd = new SqlCommand();
PrepareCommand(cmd, connection, transaction, commandType, commandText, commandParameters);
if (connectionOwnership == SqlConnectionOwnership.External)
{
dr = cmd.ExecuteReader();
}
else
{
dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
cmd.Parameters.Clear();
return(dr);
}
/// <summary>
/// 执行查询的SQL语句返回SqlDataReader
/// </summary>
/// <param name="voSqlConnection">数据库链接</param>
/// <param name="voSqlTransaction">事务</param>
/// <param name="voCommandType">命令类型</param>
/// <param name="vstrCommandText">存储过程名或T-SQL命令</param>
/// <param name="voCommandParameters">参数</param>
/// <param name="connectionOwnership">提供类型</param>
/// <returns>查询结果</returns>
private static SqlDataReader ExecuteReader(SqlConnection voSqlConnection, SqlTransaction voSqlTransaction, CommandType voCommandType, string vstrCommandText, SqlParameter[] voCommandParameters, SqlConnectionOwnership connectionOwnership)
{
SqlCommand oSqlCommand = new SqlCommand();
PrepareCommand(oSqlCommand, voSqlConnection, voSqlTransaction, voCommandType, vstrCommandText, voCommandParameters);
SqlDataReader oSqlDataReader;
if (connectionOwnership == SqlConnectionOwnership.External)
{
oSqlDataReader = oSqlCommand.ExecuteReader();
}
else
{
oSqlDataReader = oSqlCommand.ExecuteReader(CommandBehavior.CloseConnection);
}
oSqlCommand.Parameters.Clear();
return(oSqlDataReader);
}
private static async Task <SqlDataReader> ExecuteReaderAsync(
SqlConnection connection,
SqlTransaction transaction,
CommandType commandType,
string commandText,
IEnumerable <SqlParameter> commandParameters,
SqlConnectionOwnership connectionOwnership)
{
if (connection == null)
{
throw new ArgumentNullException(nameof(connection));
}
var mustCloseConnection = false;
// Create a command and prepare it for execution
var cmd = new SqlCommand();
try
{
mustCloseConnection = await PrepareCommandAsync(
cmd,
connection,
transaction,
commandType,
commandText,
commandParameters).ConfigureAwait(false);
// Create a reader
SqlDataReader dataReader;
// Call ExecuteReaderAsync with the appropriate CommandBehavior
if (connectionOwnership == SqlConnectionOwnership.External)
{
dataReader = await cmd.ExecuteReaderAsync().ConfigureAwait(false);
}
else
{
dataReader = await cmd.ExecuteReaderAsync(CommandBehavior.CloseConnection).ConfigureAwait(false);
}
// Detach the SqlParameters from the command object, so they can be used again.
// HACK: There is a problem here, the output parameter values are fletched
// when the reader is closed, so if the parameters are detached from the command
// then the SqlReader can´t set its values.
// When this happen, the parameters can´t be used again in other command.
var canClear = true;
foreach (SqlParameter commandParameter in cmd.Parameters)
{
if (commandParameter.Direction != ParameterDirection.Input)
{
canClear = false;
}
}
if (canClear)
{
cmd.Parameters.Clear();
}
return(dataReader);
}
catch
{
if (mustCloseConnection)
{
connection.Close();
}
throw;
}
}
private static SqlDataReader ExecuteReader(SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, SqlParameter[] commandParameters, SqlConnectionOwnership connectionOwnership)
{
SqlCommand command = new SqlCommand();
PrepareCommand(command, connection, transaction, commandType, commandText, commandParameters);
if (connectionOwnership == SqlConnectionOwnership.External)
{
return(command.ExecuteReader());
}
return(command.ExecuteReader(CommandBehavior.CloseConnection));
}
/// <summary>
/// Create and prepare a SqlCommand, and call ExecuteReader with the appropriate CommandBehavior.
/// </summary>
/// <remarks>
/// If we created and opened the connection, we want the connection to be closed when the DataReader is closed.
///
/// If the caller provided the connection, we want to leave it to them to manage.
/// </remarks>
/// <param name="connection">a valid SqlConnection, on which to execute this command</param>
/// <param name="transaction">a valid SqlTransaction, or 'null'</param>
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
/// <param name="timeout">seconds before a TimeoutException is thrown</param>
/// <param name="commandText">the stored procedure name or T-SQL command</param>
/// <param name="commandParameters">an array of SqlParameters to be associated with the command or 'null' if no parameters are required</param>
/// <param name="connectionOwnership">indicates whether the connection parameter was provided by the caller, or created by SqlHelper</param>
/// <returns>SqlDataReader containing the results of the command</returns>
private static SqlDataReader ExecuteReader(SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, int timeout, SqlParameter[] commandParameters, SqlConnectionOwnership connectionOwnership)
{
SqlDataReader dr=null;
//create a command and prepare it for execution
SqlCommand cmd = new SqlCommand();
int retrycount = 0;
if (connection.ConnectionString.ToLower().IndexOf("failover") >0)
retrycount = FAILOVER_RETRIES;
for (int retry = 0; retry <= retrycount; retry++)
{
try
{
PrepareCommand(cmd, connection, timeout, transaction, commandType, commandText, commandParameters);
//create a reader
// call ExecuteReader with the appropriate CommandBehavior
if (connectionOwnership == SqlConnectionOwnership.External)
{
dr = cmd.ExecuteReader();
}
else
{
dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
// detach the SqlParameters from the command object, so they can be used again.
cmd.Parameters.Clear();
break;
}
catch (SqlException ex)
{
if (retry == retrycount || (!ex.Message.Contains("transport-level error") && !ex.Message.Contains("Unable to connect")))
throw new SqlServerException(ex, commandText, commandParameters); // if it isn't during a failover
}
// detach the SqlParameters from the command object, so they can be used again.
cmd.Parameters.Clear();
} //for retries
return dr;
}
/// <summary>
/// 创建SQL命令对象并绑定，使用恰当的CommandBehavior参数调用GetReader方法
/// </summary>
/// <remarks>
/// 如果数据库连接对象是由SqlHelper创建打开的，那么将在DataReader对象关闭的时候一起关闭
/// 如果数据库连接对象是由调用者提供的，那么将有调用者负责管理
/// </remarks>
/// <param name="connection">数据库连接对象</param>
/// <param name="transaction">事物对象(或者为null)</param>
/// <param name="commandType">类型</param>
/// <param name="commandText">文本（存储过程名或者T-SQL命令）</param>
/// <param name="commandParameters">参数数组</param>
/// <param name="connectionOwnership">数据库连接对象所有者标记</param>
/// <returns>返回保存执行结果的DataReader</returns>
private static OleDbDataReader GetReader(OleDbConnection connection, OleDbTransaction transaction, CommandType commandType, string commandText, OleDbParameter[] commandParameters, SqlConnectionOwnership connectionOwnership)
{
if (connection == null)
{
throw new ArgumentNullException("connection");
}
bool mustCloseConnection = false;
// 创建命令对象并且绑定
OleDbCommand cmd = new OleDbCommand();
//SqlCommand cmd = new SqlCommand();
try
{
PrepareCommand(cmd, connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection, 30);
// 创建阅读器
//SqlDataReader dataReader;
OleDbDataReader dataReader;
// 使用恰当的CommandBehavior参数调用GetReader方法
if (connectionOwnership == SqlConnectionOwnership.External)
{
dataReader = cmd.ExecuteReader();
}
else
{
dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
// 清除SQL命令对象的参数
// HACK: There is a problem here, the output parameter values are fletched
// when the reader is closed, so if the parameters are detached from the command
// then the SqlReader can磘 set its values.
// When this happen, the parameters can磘 be used again in other command.
// 注意:问题在于当reader被关闭时output类型的参数值是可以被引用的，
// 所以如果参数数组被从SQL命令对象中分离掉了，SqlReader将不能再给它赋值
// 这种情况一旦发生，参数数组将不能再被其他命令对象使用
bool canClear = true;
foreach (SqlParameter commandParameter in cmd.Parameters)
{
if (commandParameter.Direction != ParameterDirection.Input)
{
canClear = false;
}
}
if (canClear)
{
cmd.Parameters.Clear();
}
return(dataReader);
}
catch
{
if (mustCloseConnection)
{
connection.Close();
}
throw;
}
}
private static SqlDataReader ExecuteReaderV2(SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, SqlParameter[] commandParameters, SqlConnectionOwnership connectionOwnership)
{
SqlDataReader reader;
SqlCommand cmd = new SqlCommand();
PrepareCommand(cmd, connection, transaction, commandType, commandText, commandParameters);
cmd.CommandTimeout = 0;
if (connectionOwnership == SqlConnectionOwnership.External)
{
reader = PerformanceWatcher.Watch <SqlDataReader>(commandText, () => cmd.ExecuteReader());
}
else
{
reader = PerformanceWatcher.Watch <SqlDataReader>(commandText, () => cmd.ExecuteReader(CommandBehavior.CloseConnection));
}
cmd.Parameters.Clear();
return(reader);
}
/// <summary>
/// Create and prepare a SQLiteCommand, and call ExecuteReader with the appropriate CommandBehavior.
/// </summary>
/// <remarks>
/// If we created and opened the connection, we want the connection to be closed when the DataReader is closed.
///
/// If the caller provided the connection, we want to leave it to them to manage.
/// </remarks>
/// <param name="connection">a valid SQLiteConnection, on which to execute this command</param>
/// <param name="transaction">a valid SQLiteTransaction, or 'null'</param>
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">the stored procedure name or T-SQL command</param>
/// <param name="commandParameters">an array of SqlParameters to be associated with the command or 'null' if no parameters are required</param>
/// <param name="connectionOwnership">indicates whether the connection parameter was provided by the caller, or created by SqlHelper</param>
/// <returns>SQLiteDataReader containing the results of the command</returns>
private static SQLiteDataReader ExecuteReader(SQLiteConnection connection, SQLiteTransaction transaction, CommandType commandType, string commandText, SQLiteParameter[] commandParameters, SqlConnectionOwnership connectionOwnership)
{
//create a command and prepare it for execution
SQLiteCommand cmd = new SQLiteCommand();
PrepareCommand(cmd, connection, transaction, commandType, commandText, commandParameters);
//create a reader
SQLiteDataReader dr;
// call ExecuteReader with the appropriate CommandBehavior
if (connectionOwnership == SqlConnectionOwnership.External)
{
dr = cmd.ExecuteReader();
}
else
{
dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
// detach the SqlParameters from the command object, so they can be used again.
cmd.Parameters.Clear();
return(dr);
}
/// <summary>
/// Create and prepare an SqlCommand, and call ExecuteReader with the appropriate CommandBehavior.
/// </summary>
/// <remarks>
/// If we created and opened the connection, we want the connection to be closed when the DataReader is closed.
///
/// If the caller provided the connection, we want to leave it to them to manage.
/// </remarks>
/// <param name="connection">a valid SqlConnection, on which to execute this command</param>
/// <param name="transaction">a valid SqlTransaction, or 'null'</param>
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">the stored procedure name or PL/SQL command</param>
/// <param name="commandParameters">an array of SqlParameters to be associated with the command or 'null' if no parameters are required</param>
/// <param name="connectionOwnership">indicates whether the connection parameter was provided by the caller, or created by SqlHelper</param>
/// <returns>SqlDataReader containing the results of the command</returns>
private static SqlDataReader ExecuteReader(SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, SqlParameter[] commandParameters, SqlConnectionOwnership connectionOwnership)
{
//create a command and prepare it for execution
SqlCommand cmd = connection.CreateCommand();
PrepareCommand(cmd, connection, transaction, commandType, commandText, commandParameters);
//create a reader
SqlDataReader dr = null;
try
{
// call ExecuteReader with the appropriate CommandBehavior
if (connectionOwnership == SqlConnectionOwnership.External)
{
dr = cmd.ExecuteReader();
}
else
{
dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
//if (transaction != null)
//{
// transaction.Commit();
//}
return dr;
}
catch
{
//if (transaction != null)
//{
// // Rollback the transaction
// transaction.Rollback();
//}
throw;
}
}
/// <summary>
/// 调用ExecuteReader,通过提供CommandBehavior.
/// </summary>
/// <remarks>
/// If we created and opened the connection, we want the connection to be closed when the DataReader is closed.
///
/// If the caller provided the connection, we want to leave it to them to manage.
/// </remarks>
/// <param name="commandType">CommandType类型 (stored procedure, text, etc.)</param>
/// <param name="commandText">存储过程名称或T-SQL语句</param>
/// <param name="commandParameters">参数SqlParameters or 'null'</param>
/// <param name="transaction">一个有效的SqlTransaction, or 'null'</param>
/// <param name="connectionOwnership">标识连接对象是由caller提供还是由SqlHelper提供</param>
/// <returns>SqlDataReader结果集</returns>
private SqlDataReader ExecuteReader(CommandType commandType, string commandText, SqlParameter[] commandParameters, SqlTransaction transaction, SqlConnectionOwnership connectionOwnership)
{
if (connection == null)
{
return(null);
}
bool mustCloseConnection = false;
try
{
using (SqlCommand cmd = new SqlCommand())
{
PrepareCommand(cmd, transaction, commandType, commandText, commandParameters, out mustCloseConnection);
SqlDataReader dataReader;
if (connectionOwnership == SqlConnectionOwnership.External)
{
dataReader = cmd.ExecuteReader();
}
else
{
dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
bool canClear = true;
foreach (SqlParameter commandParameter in cmd.Parameters)
{
if (commandParameter.Direction != ParameterDirection.Input)
{
canClear = false;
}
}
if (canClear)
{
cmd.Parameters.Clear();
}
return(dataReader);
}
}
catch
{
if (mustCloseConnection)
{
connection.Close();
}
throw;
}
}
/// <summary>
/// Create and prepare a SqlCommand, and call ExecuteReader with the appropriate CommandBehavior.
/// </summary>
/// <remarks>
/// If we created and opened the connection, we want the connection to be closed when the DataReader is closed.
///
/// If the caller provided the connection, we want to leave it to them to manage.
/// </remarks>
/// <param name="connection">A valid SqlConnection, on which to execute this command</param>
/// <param name="transaction">A valid SqlTransaction, or 'null'</param>
/// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">The stored procedure name or T-SQL command</param>
/// <param name="commandParameters">An array of SqlParameters to be associated with the command or 'null' if no parameters are required</param>
/// <param name="connectionOwnership">Indicates whether the connection parameter was provided by the caller, or created by SqlHelper</param>
/// <returns>SqlDataReader containing the results of the command</returns>
private SqlDataReader ExecuteReader(SqlTransaction transaction, CommandType commandType, string commandText, IEnumerable <SqlParameter> commandParameters, SqlConnectionOwnership connectionOwnership)
{
if (Conn == null)
{
throw new ArgumentNullException("connection");
}
var mustCloseConnection = false;
// Create a command and prepare it for execution
var cmd = new SqlCommand();
try
{
PrepareCommand(cmd, Conn, transaction, commandType, commandText, commandParameters, out mustCloseConnection);
// Create a reader
// Call ExecuteReader with the appropriate CommandBehavior
var dataReader = connectionOwnership == SqlConnectionOwnership.External ? cmd.ExecuteReader() : cmd.ExecuteReader(CommandBehavior.CloseConnection);
// Detach the SqlParameters from the command object, so they can be used again.
// HACK: There is a problem here, the output parameter values are fletched
// when the reader is closed, so if the parameters are detached from the command
// then the SqlReader can´t set its values.
// When this happen, the parameters can´t be used again in other command.
var canClear = true;
foreach (SqlParameter commandParameter in cmd.Parameters)
{
if (commandParameter.Direction != ParameterDirection.Input)
{
canClear = false;
}
}
if (canClear)
{
cmd.Parameters.Clear();
}
return(dataReader);
}
catch
{
if (mustCloseConnection)
{
Close();
}
throw;
}
}
private static SqlDataReader ExecuteReader(SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, SqlParameter[] commandParameters, SqlConnectionOwnership connectionOwnership)
{
SqlDataReader reader;
SqlCommand command = new SqlCommand();
PrepareCommand(command, connection, transaction, commandType, commandText, commandParameters);
if (connectionOwnership == SqlConnectionOwnership.External)
{
reader = command.ExecuteReader();
}
else
{
reader = command.ExecuteReader(CommandBehavior.CloseConnection);
}
command.Parameters.Clear();
return reader;
}
private static SqlDataReader ExecuteReader(SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, SqlParameter[] commandParameters, SqlConnectionOwnership connectionOwnership)
{
return ExecuteReader(connection, transaction, commandType, commandText, 300, commandParameters, connectionOwnership);
}
/// <summary>
/// 执行指定数据库连接对象的数据阅读器.
/// </summary>
/// <remarks>
/// 如果是SqlHelper打开连接,当连接关闭DataReader也将关闭.
/// 如果是调用都打开连接,DataReader由调用都管理.
/// </remarks>
/// <param name="connection">一个有效的数据库连接对象</param>
/// <param name="transaction">一个有效的事务,或者为 'null'</param>
/// <param name="commandType">命令类型 (存储过程,命令文本或其它)</param>
/// <param name="commandText">存储过程名或T-SQL语句</param>
/// <param name="commandParameters">SqlParameters参数数组,如果没有参数则为'null'</param>
/// <param name="connectionOwnership">标识数据库连接对象是由调用者提供还是由SqlHelper提供</param>
/// <returns>返回包含结果集的SqlDataReader</returns>
private static SqlDataReader ExecuteReader(SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, SqlParameter[] commandParameters, SqlConnectionOwnership connectionOwnership)
{
if (connection == null) throw new ArgumentNullException("connection");
var mustCloseConnection = false;
// 创建命令
var cmd = new SqlCommand();
try
{
PrepareCommand(cmd, connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection);
// 创建数据阅读器
SqlDataReader dataReader;
if (connectionOwnership == SqlConnectionOwnership.External)
{
dataReader = cmd.ExecuteReader();
}
else
{
dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
// 清除参数,以便再次使用..
// HACK: There is a problem here, the output parameter values are fletched
// when the reader is closed, so if the parameters are detached from the command
// then the SqlReader can磘 set its values.
// When this happen, the parameters can磘 be used again in other command.
var canClear = true;
foreach (SqlParameter commandParameter in cmd.Parameters)
{
if (commandParameter.Direction != ParameterDirection.Input)
{
canClear = false;
break;
}
}
if (canClear)
{
cmd.Parameters.Clear();
}
return dataReader;
}
catch
{
if (mustCloseConnection)
connection.Close();
throw;
}
}
/// <summary>
/// Create and prepare a SqlCommand, and call ExecuteReader with the appropriate CommandBehavior.
/// </summary>
/// <remarks>
/// If we created and opened the connection, we want the connection to be closed when the DataReader is closed.
///
/// If the caller provided the connection, we want to leave it to them to manage.
/// </remarks>
/// <param name="connection">a valid SqlConnection, on which to execute this command</param>
/// <param name="transaction">a valid SqlTransaction, or 'null'</param>
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">the stored procedure name or T-SQL command</param>
/// <param name="commandParameters">an array of SqlParameters to be associated with the command or 'null' if no parameters are required</param>
/// <param name="connectionOwnership">indicates whether the connection parameter was provided by the caller, or created by SqlHelper</param>
/// <returns>SqlDataReader containing the results of the command</returns>
private static SqlDataReader ExecuteReader(SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, SqlParameter[] commandParameters, SqlConnectionOwnership connectionOwnership)
{
//create a command and prepare it for execution
SqlCommand cmd = new SqlCommand();
PrepareCommand(cmd, connection, transaction, commandType, commandText, commandParameters);
//create a reader
SqlDataReader dr;
// call ExecuteReader with the appropriate CommandBehavior
try
{
if (connectionOwnership == SqlConnectionOwnership.External)
{
dr = cmd.ExecuteReader();
}
else
{
dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
}
catch (Exception ex)
{
"SQLHelper".Log().Error("Error in ExecuteReader: `{0}`: {1} - {2}",
commandText,
ex.Message,
ex.StackTrace);
ErrorLogParameters(commandText, commandParameters);
throw;
}
// detach the SqlParameters from the command object, so they can be used again.
cmd.Parameters.Clear();
return dr;
}
/// <summary>
/// Create and prepare a SqlCommand, and call ExecuteReader with the appropriate CommandBehavior.
/// </summary>
/// <remarks>
/// If we created and opened the connection, we want the connection to be closed when the DataReader is closed.
///
/// If the caller provided the connection, we want to leave it to them to manage.
/// </remarks>
/// <param name="connection">a valid SqlConnection, on which to execute this command</param>
/// <param name="transaction">a valid SqlTransaction, or 'null'</param>
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">the stored procedure name or T-SQL command</param>
/// <param name="commandParameters">an array of SqlParameters to be associated with the command or 'null' if no parameters are required</param>
/// <param name="connectionOwnership">indicates whether the connection parameter was provided by the caller, or created by SqlHelper</param>
/// <returns>SqlDataReader containing the results of the command</returns>
private static SqlDataReader ExecuteReader(SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, SqlParameter[] commandParameters, SqlConnectionOwnership connectionOwnership)
{
//create a command and prepare it for execution
SqlCommand cmd = new SqlCommand();
PrepareCommand(cmd, connection, transaction, commandType, commandText, commandParameters);
//create a reader
SqlDataReader dr;
// call ExecuteReader with the appropriate CommandBehavior
if (connectionOwnership == SqlConnectionOwnership.External)
{
dr = cmd.ExecuteReader();
}
else
{
dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
// detach the SqlParameters from the command object, so they can be used again.
cmd.Parameters.Clear();
return dr;
}
public static SqlDataReader OutSideConnectionDataReader(string connection, SqlTransaction transaction, string instructionType, string instructionText, SqlParameter[] inputParamList, SqlConnectionOwnership connectionOwnership)
{
if (connection == null)
{
throw new ArgumentNullException("connection");
}
SqlConnection tempConnection = new SqlConnection(connection);
//SqlConnection tempConnection = ParseEnum<SqlConnection>(connection);
bool mustCloseConnection = false;
// Create a command and prepare it for execution
SqlCommand cmd = new SqlCommand();
try
{
if (!ValidateInputParams(inputParamList))
{
CommandType tempType = ParseEnum <CommandType>(instructionType);
PrepareCommand(cmd, tempConnection, transaction, tempType, instructionText, inputParamList, out mustCloseConnection);
// Create a reader
SqlDataReader dataReader;
// Call ExecuteReader with the appropriate CommandBehavior
if (connectionOwnership == SqlConnectionOwnership.External)
{
dataReader = cmd.ExecuteReader();
}
else
{
dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
// Detach the SqlParameters from the command object, so they can be used again.
// HACK: There is a problem here, the output parameter values are fletched
// when the reader is closed, so if the parameters are detached from the command
// then the SqlReader can´t set its values.
// When this happen, the parameters can´t be used again in other command.
bool canClear = true;
foreach (SqlParameter commandParameter in cmd.Parameters)
{
if (commandParameter.Direction != ParameterDirection.Input)
{
canClear = false;
}
}
if (canClear)
{
cmd.Parameters.Clear();
}
return(dataReader);
}
else
{
MessageBox.Show("An input parameter is invalid ", "Taeerascan - T2", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return(null);
}
}
catch
{
if (mustCloseConnection)
{
tempConnection.Close();
}
throw;
}
}
