/// <summary> /// Please refer to the documentation of <see cref="GentleProvider"/> and the /// <see cref="IPersistenceEngine"/> interface it implements for details. /// </summary> public override IDbCommand GetCommand() { IDbCommand cmd = new AsaCommand(); cmd.CommandTimeout = GentleSettings.DefaultCommandTimeout; return(cmd); }
/// <summary> /// Simplify the creation of a Asa command object by allowing /// a CommandType and Command Text to be provided /// </summary> /// <remarks> /// e.g.: /// AsaCommand command = CreateCommand(conn, CommandType.Text, "Select * from Customers"); /// </remarks> /// <param name="connection">A valid AsaConnection object</param> /// <param name="commandType">CommandType (TableDirect, Text)</param> /// <param name="commandText">CommandText</param> /// <returns>A valid AsaCommand object</returns> public static AsaCommand CreateCommand(AsaConnection connection, CommandType commandType, string commandText ) { if( connection == null ) throw new ArgumentNullException( "connection" ); if( commandType == CommandType.StoredProcedure ) throw new ArgumentException("Stored Procedures are not supported."); // If we receive parameter values, we need to figure out where they go if ((commandText == null) && (commandText.Length<= 0)) throw new ArgumentNullException( "Command Text" ); // Create a AsaCommand AsaCommand cmd = new AsaCommand(commandText, connection ); cmd.CommandType = CommandType.Text ; return cmd; }
/// <summary> /// Please refer to the documentation of <see cref="GentleSqlFactory"/> for details. /// </summary> public override void AddParameter(IDbCommand cmd, string name, long dbType) { try { StringBuilder sb = new StringBuilder(); sb.Append(GetParameterPrefix()); sb.Append(name); sb.Append(GetParameterSuffix()); AsaCommand asac = (AsaCommand)cmd; AsaParameter param = new AsaParameter(sb.ToString(), (AsaDbType)dbType); param.Direction = ParameterDirection.Input; asac.Parameters.Add(param); } catch (Exception e) { Check.Fail(Error.Unspecified, e.Message); throw new GentleException(Error.Unspecified, "Unreachable code"); } }
/// <summary> /// Create and prepare a AsaCommand, 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 AsaConnection, on which to execute this command</param> /// <param name="transaction">A valid AsaTransaction, 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 AsaParameters 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 AsaHelper</param> /// <returns>AsaDataReader containing the results of the command</returns> private static AsaDataReader ExecuteReader(AsaConnection connection, AsaTransaction transaction, CommandType commandType, string commandText, AsaParameter[] commandParameters, AsaConnectionOwnership connectionOwnership) { if( connection == null ) throw new ArgumentNullException( "connection" ); bool mustCloseConnection = false; // Create a command and prepare it for execution AsaCommand cmd = new AsaCommand(); try { PrepareCommand(cmd, connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection ); // Create a reader AsaDataReader dataReader; // Call ExecuteReader with the appropriate CommandBehavior if (connectionOwnership == AsaConnectionOwnership.External) { dataReader = cmd.ExecuteReader(); } else { dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection); } // Detach the AsaParameters 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 AsaReader can´t set its values. // When this happen, the parameters can´t be used again in other command. bool canClear = true; foreach(AsaParameter 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 is used to attach array of AsaParameters to a AsaCommand. /// /// 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 AsaParameters to be added to command</param> private static void AttachParameters(AsaCommand command, AsaParameter[] commandParameters) { if( command == null ) throw new ArgumentNullException( "command" ); if( commandParameters != null ) { foreach (AsaParameter 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> /// Executes the respective command for each inserted, updated, or deleted row in the DataSet. /// </summary> /// <remarks> /// e.g.: /// UpdateDataset(conn, insertCommand, deleteCommand, updateCommand, dataSet, "Order"); /// </remarks> /// <param name="insertCommand">A valid transact-SQL statement to insert new records into the data source</param> /// <param name="deleteCommand">A valid transact-SQL statement to delete records from the data source</param> /// <param name="updateCommand">A valid transact-SQL statement used to update records in the data source</param> /// <param name="dataSet">The DataSet used to update the data source</param> /// <param name="tableName">The DataTable used to update the data source.</param> public static void UpdateDataset(AsaCommand insertCommand, AsaCommand deleteCommand, AsaCommand updateCommand, DataSet dataSet, string tableName) { if( insertCommand == null ) throw new ArgumentNullException( "insertCommand" ); if( deleteCommand == null ) throw new ArgumentNullException( "deleteCommand" ); if( updateCommand == null ) throw new ArgumentNullException( "updateCommand" ); if( tableName == null || tableName.Length == 0 ) throw new ArgumentNullException( "tableName" ); // Create a AsaDataAdapter, and dispose of it after we are done AsaDataAdapter dataAdapter = new AsaDataAdapter(); try { // Set the data adapter commands dataAdapter.UpdateCommand = updateCommand; dataAdapter.InsertCommand = insertCommand; dataAdapter.DeleteCommand = deleteCommand; // Update the dataset changes in the data source dataAdapter.Update (dataSet,tableName); // Commit all the changes made to the DataSet dataSet.AcceptChanges(); } catch (AsaException E) {string strError=E.Message;} finally{dataAdapter.Dispose();} }
/// <summary> /// Execute a AsaCommand (that returns a resultset) against the specified AsaTransaction /// using the provided parameters. /// </summary> /// <remarks> /// e.g.: /// XmlReader r = ExecuteXmlReader(trans, CommandType.Text, "Select * from TableTransaction where ProdId=?", new AsaParameter("@prodid", 24)); /// </remarks> /// <param name="transaction">A valid AsaTransaction</param> /// <param name="commandType">The CommandType (TableDirect, Text)</param> /// <param name="commandText">The T-SQL command using "FOR XML AUTO"</param> /// <param name="commandParameters">An array of AsaParamters used to execute the command</param> /// <returns>An XmlReader containing the resultset generated by the command</returns> public static string ExecuteXml(AsaTransaction transaction, CommandType commandType, string commandText, params AsaParameter[] commandParameters) { if( transaction == null ) throw new ArgumentNullException( "transaction" ); if( transaction != null && transaction.Connection == null ) throw new ArgumentException( "The transaction was rollbacked or commited, please provide an open transaction.", "transaction" ); // // Create a command and prepare it for execution AsaCommand cmd = new AsaCommand(); bool mustCloseConnection = false; PrepareCommand(cmd, (AsaConnection)transaction.Connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection); // Create the DataAdapter & DataSet AsaDataAdapter obj_Adapter =new AsaDataAdapter (cmd); DataSet ds=new DataSet(); ds.Locale =CultureInfo.InvariantCulture; obj_Adapter.Fill(ds); // Detach the AsaParameters from the command object, so they can be used again cmd.Parameters.Clear(); string retval= ds.GetXml(); ds.Clear(); obj_Adapter.Dispose (); return retval; }
/// <summary> /// Execute a AsaCommand (that returns a resultset) against the specified AsaConnection /// using the provided parameters. /// </summary> /// <remarks> /// e.g.: /// string r = ExecuteXml(conn, CommandType.Text, "Select * from TableTransaction where ProdId=?", new AsaParameter("@prodid", 24)); /// </remarks> /// <param name="connection">A valid AsaConnection</param> /// <param name="commandType">The CommandType (TableDirect, Text)</param> /// <param name="commandText">The T-SQL command using "FOR XML AUTO"</param> /// <param name="commandParameters">An array of AsaParamters used to execute the command</param> /// <returns>An string containing the resultset generated by the command</returns> public static string ExecuteXml(AsaConnection connection, CommandType commandType, string commandText, params AsaParameter[] commandParameters) { if( connection == null ) throw new ArgumentNullException( "connection" ); bool mustCloseConnection = false; // Create a command and prepare it for execution AsaCommand cmd = new AsaCommand(); try { PrepareCommand(cmd, connection, (AsaTransaction)null, commandType, commandText, commandParameters, out mustCloseConnection ); // Create the DataAdapter & DataSet AsaDataAdapter obj_Adapter =new AsaDataAdapter (cmd); DataSet ds=new DataSet(); ds.Locale =CultureInfo.InvariantCulture; obj_Adapter.Fill(ds); // Detach the AsaParameters from the command object, so they can be used again cmd.Parameters.Clear(); string retval= ds.GetXml(); ds.Clear(); obj_Adapter.Dispose (); return retval; } catch { if( mustCloseConnection ) connection.Close(); throw; } }
/// <summary> /// Execute a AsaCommand (that returns a 1x1 resultset) against the specified AsaTransaction /// using the provided parameters. /// </summary> /// <remarks> /// e.g.: /// int orderCount = (int)ExecuteScalar(trans, CommandType.Text, "Select count(Order) from TableTransaction where ProdId=?", new AsaParameter("@prodid", 24)); /// </remarks> /// <param name="transaction">A valid AsaTransaction</param> /// <param name="commandType">The CommandType (TableDirect, Text)</param> /// <param name="commandText">The T-SQL command</param> /// <param name="commandParameters">An array of AsaParamters used to execute the command</param> /// <returns>An object containing the value in the 1x1 resultset generated by the command</returns> public static object ExecuteScalar(AsaTransaction transaction, CommandType commandType, string commandText, params AsaParameter[] commandParameters) { if( transaction == null ) throw new ArgumentNullException( "transaction" ); if( transaction != null && transaction.Connection == null ) throw new ArgumentException( "The transaction was rollbacked or commited, please provide an open transaction.", "transaction" ); // Create a command and prepare it for execution AsaCommand cmd = new AsaCommand(); bool mustCloseConnection = false; PrepareCommand(cmd, (AsaConnection)transaction.Connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection); // Execute the command & return the results object retval = cmd.ExecuteScalar(); // Detach the AsaParameters from the command object, so they can be used again cmd.Parameters.Clear(); return retval; }
/// <summary> /// Execute a AsaCommand (that returns a 1x1 resultset) against the specified AsaConnection /// using the provided parameters. /// </summary> /// <remarks> /// e.g.: /// int orderCount = (int)ExecuteScalar(conn, CommandType.Text, "Select count(Order) from TableTransaction where ProdId=?", new AsaParameter("@prodid", 24)); /// </remarks> /// <param name="connection">A valid AsaConnection</param> /// <param name="commandType">The CommandType (TableDirect, Text)</param> /// <param name="commandText">The T-SQL command</param> /// <param name="commandParameters">An array of AsaParamters used to execute the command</param> /// <returns>An object containing the value in the 1x1 resultset generated by the command</returns> public static object ExecuteScalar(AsaConnection connection, CommandType commandType, string commandText, params AsaParameter[] commandParameters) { if( connection == null ) throw new ArgumentNullException( "connection" ); // Create a command and prepare it for execution AsaCommand cmd = new AsaCommand(); bool mustCloseConnection = false; PrepareCommand(cmd, connection, (AsaTransaction)null, commandType, commandText, commandParameters, out mustCloseConnection ); // Execute the command & return the results object retval = cmd.ExecuteScalar(); // Detach the AsaParameters from the command object, so they can be used again cmd.Parameters.Clear(); if( mustCloseConnection ) connection.Close(); return retval; }
/// <summary> /// Execute a AsaCommand (that returns a resultset) against the specified AsaTransaction /// using the provided parameters. /// </summary> /// <remarks> /// e.g.: /// DataSet ds = ExecuteDataset(trans, CommandType.Text, "Select * from TableTransaction where ProdId=?", new AsaParameter("@prodid", 24)); /// </remarks> /// <param name="transaction">A valid AsaTransaction</param> /// <param name="commandType">The CommandType (TableDirect, Text)</param> /// <param name="commandText">The T-SQL command</param> /// <param name="commandParameters">An array of AsaParamters used to execute the command</param> /// <returns>A dataset containing the resultset generated by the command</returns> public static DataSet ExecuteDataset(AsaTransaction transaction, CommandType commandType, string commandText, params AsaParameter[] commandParameters) { if( transaction == null ) throw new ArgumentNullException( "transaction" ); if( transaction != null && transaction.Connection == null ) throw new ArgumentException( "The transaction was rollbacked or commited, please provide an open transaction.", "transaction" ); // Create a command and prepare it for execution AsaCommand cmd = new AsaCommand(); bool mustCloseConnection = false; PrepareCommand(cmd, (AsaConnection)transaction.Connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection); // Create the DataAdapter & DataSet //using( AsaDataAdapter da = new AsaDataAdapter(cmd) ) AsaDataAdapter da = new AsaDataAdapter(cmd); DataSet ds = new DataSet(); ds.Locale =CultureInfo.InvariantCulture; // Fill the DataSet using default values for DataTable names, etc da.Fill(ds); // Detach the AsaParameters from the command object, so they can be used again cmd.Parameters.Clear(); // Return the dataset return ds; }
/// <summary> /// This method opens (if necessary) and assigns a connection, transaction, command type and parameters /// to the provided command /// </summary> /// <param name="command">The AsaCommand to be prepared</param> /// <param name="connection">A valid AsaConnection, on which to execute this command</param> /// <param name="transaction">A valid AsaTransaction, 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 AsaParameters 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(AsaCommand command, AsaConnection connection, AsaTransaction transaction, CommandType commandType, string commandText, AsaParameter[] 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; }
/// <summary> /// Private helper method that execute a AsaCommand (that returns a resultset) against the specified AsaTransaction and AsaConnection /// using the provided parameters. /// </summary> /// <remarks> /// e.g.: /// FillDataset(conn, trans, CommandType.Text, "Select * from TableTransaction where ProdId=?", ds, new string[] {"orders"}, new AsaParameter("@prodid", 24)); /// </remarks> /// <param name="connection">A valid AsaConnection</param> /// <param name="transaction">A valid AsaTransaction</param> /// <param name="commandType">The CommandType (TableDirect, Text)</param> /// <param name="commandText">The T-SQL command</param> /// <param name="dataSet">A dataset wich will contain the resultset generated by the command</param> /// <param name="tableNames">This array will be used to create table mappings allowing the DataTables to be referenced /// by a user defined name (probably the actual table name) /// </param> /// <param name="commandParameters">An array of AsaParamters used to execute the command</param> private static void FillDataset(AsaConnection connection, AsaTransaction transaction, CommandType commandType, string commandText, DataSet dataSet, string[] tableNames, params AsaParameter[] commandParameters) { if( connection == null ) throw new ArgumentNullException( "connection" ); if( dataSet == null ) throw new ArgumentNullException( "dataSet" ); // Create a command and prepare it for execution AsaCommand command = new AsaCommand(); bool mustCloseConnection = false; PrepareCommand(command, connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection ); // Create the DataAdapter & DataSet AsaDataAdapter dataAdapter = new AsaDataAdapter(command); try { // Add the table mappings specified by the user if (tableNames != null && tableNames.Length > 0) { string tableName = "Table"; for (int index=0; index < tableNames.Length; index++) { if( tableNames[index] == null || tableNames[index].Length == 0 ) throw new ArgumentException( "The tableNames parameter must contain a list of tables, a value was provided as null or empty string.", "tableNames" ); dataAdapter.TableMappings.Add(tableName, tableNames[index]); tableName += (index + 1).ToString(); } } // Fill the DataSet using default values for DataTable names, etc dataAdapter.Fill(dataSet); // Detach the AsaParameters from the command object, so they can be used again command.Parameters.Clear(); if( mustCloseConnection ) connection.Close(); } finally { dataAdapter.Dispose(); } }
/// <summary> /// Please refer to the documentation of <see cref="GentleProvider"/> and the /// <see cref="IPersistenceEngine"/> interface it implements for details. /// </summary> public override IDbCommand GetCommand() { IDbCommand cmd = new AsaCommand(); cmd.CommandTimeout = GentleSettings.DefaultCommandTimeout; return cmd; }