/// <summary> /// This method is used to attach array of TurboDBParameters to a TurboDBCommand. /// /// This method will assign a value of DbNull to any parameter with a direction of /// InputOutput and a value of null. /// /// This behavior will prevent default values from being used, but /// this will be the less common case than an intended pure output parameter (derived as InputOutput) /// where the user provided no input value. /// </summary> /// <param name="command">The command to which the parameters will be added</param> /// <param name="commandParameters">An array of TurboDBParameters to be added to command</param> private static void AttachParameters(TurboDBCommand command, TurboDBParameter[] commandParameters) { if( command == null ) throw new ArgumentNullException( "command" ); if( commandParameters != null ) { foreach (TurboDBParameter p in commandParameters) { if( p != null ) { // Check for derived output value with no value assigned if ( ( p.Direction == ParameterDirection.InputOutput || p.Direction == ParameterDirection.Input ) && (p.Value == null)) { p.Value = DBNull.Value; } command.Parameters.Add(p); } } } }
/// <summary> /// Create and prepare a TurboDBCommand, 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 TurboDBConnection, on which to execute this command</param> /// <param name="transaction">A valid TurboDBTransaction, or 'null'</param> /// <param name="commandType">The CommandType (TableDirect, Text)</param> /// <param name="commandText">The T-SQL command</param> /// <param name="commandParameters">An array of TurboDBParameters 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 TurboDBHelper</param> /// <returns>TurboDBDataReader containing the results of the command</returns> private static TurboDBDataReader ExecuteReader(TurboDBConnection connection, TurboDBTransaction transaction, CommandType commandType, string commandText, TurboDBParameter[] commandParameters, TurboDBConnectionOwnership connectionOwnership) { if( connection == null ) throw new ArgumentNullException( "connection" ); bool mustCloseConnection = false; // Create a command and prepare it for execution TurboDBCommand cmd = new TurboDBCommand(); try { PrepareCommand(cmd, connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection ); // Create a reader TurboDBDataReader dataReader; // Call ExecuteReader with the appropriate CommandBehavior if (connectionOwnership == TurboDBConnectionOwnership.External) { dataReader = cmd.ExecuteReader(); } else { dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection); } // Detach the TurboDBParameters 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 TurboDBReader can´t set its values. // When this happen, the parameters can´t be used again in other command. bool canClear = true; foreach(TurboDBParameter 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> /// This method opens (if necessary) and assigns a connection, transaction, command type and parameters /// to the provided command /// </summary> /// <param name="command">The TurboDBCommand to be prepared</param> /// <param name="connection">A valid TurboDBConnection, on which to execute this command</param> /// <param name="transaction">A valid TurboDBTransaction, or 'null'</param> /// <param name="commandType">The CommandType (TableDirect, Text)</param> /// <param name="commandText">The T-SQL command</param> /// <param name="commandParameters">An array of TurboDBParameters to be associated with the command or 'null' if no parameters are required</param> /// <param name="mustCloseConnection"><c>true</c> if the connection was opened by the method, otherwose is false.</param> private static void PrepareCommand(TurboDBCommand command, TurboDBConnection connection, TurboDBTransaction transaction, CommandType commandType, string commandText, TurboDBParameter[] commandParameters, out bool mustCloseConnection ) { if( command == null ) throw new ArgumentNullException( "command" ); if(commandType == CommandType.StoredProcedure ) throw new ArgumentException("Stored Procedures are not supported."); // If the provided connection is not open, we will open it if (connection.State != ConnectionState.Open) { mustCloseConnection = true; connection.Open(); } else { mustCloseConnection = false; } // Associate the connection with the command command.Connection = connection; // Set the command text (SQL statement) command.CommandText = commandText; // If we were provided a transaction, assign it if (transaction != null) { if( transaction.Connection == null ) throw new ArgumentException( "The transaction was rollbacked or commited, please provide an open transaction.", "transaction" ); command.Transaction = transaction; } // Set the command type command.CommandType = commandType; // Attach the command parameters if they are provided if (commandParameters != null) { AttachParameters(command, commandParameters); } return; }