/// <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; } }