WebCab Probability and Statistics for .NET v3.3 Demo

ADOMediator.SelectAndUpdate Method (String, String, String, Object, Object[][], Boolean)

Performs calculations upon a data set and writes the results back to the database, by using a SELECT input query, a stored procedure or an UPDATE/INSERT command and an input columns-output parameters mapping table.

public void SelectAndUpdate(
   string methodName,
   string inputQuery,
   string outputQuery,
   object resultParameters,
   object[][] inputOutputPairs,
   bool storedProcedure
);

Parameters

methodName
The name of the method used in evaluating the input data returned by inputQuery. If the underlying component contains several methods named meethodName, the one that fits best the number columns returned is chosen.
inputQuery
A SELECT SQL query that returns enough columns to invoke methodName. Note that columns specified in the right-hand side of the inputOutputPairs array will not be passed on to the method.
outputQuery
An SQL update/insert query or a stored procedure invoked with each input row, meant to write the results back to the database. If the storedProcedure flag is set, the outputQuery is run as a stored procedure, otherwise this method will treat it as an SQL statement.
resultParameters
Named parameters to assign the results of methodName to. If only one result is expected, pass in a String or an integer. If more than one value is supposed to be returned by methodName, pass in a String array representing the names of the Input parameters of the output query or an int[] array with the parameter indices.
inputOutputPairs
The correspondence between certain Input parameters of the output query and the columns of the input query. inputOutputPairs should be made of arrays of pairs (arrays of two elements), where the left side contains the name of the Input parameter of the output query/stored procedure or its index and the right side contains either the name of the corresponding input column or its index. With each row, when the output query/stored procedure is run, the named parameters are reassigned to the input columns. Index numbering, if used instead of named parameters or column names, starts with 0. You can use both indices and column names in the right side throughout this array, but only named parameters or numbers in the left side.
storedProcedure
If true, the output query is run as a stored procedure, if false the output query will be invoked as an ordinary SQL statement.

Remarks

Additionally, you should specify the named parameters where to place the results returned by methodName, either as a String, or an array of String objects.

If your ADO.NET driver does not support named parameters and requires parameters to be added in the order they have been marked inside the output query, like when using question mark markers, use indices instead of names. That is, resultParameters and the left side of the inputOutputPairs array should only contain integers representing the index of the parameters you are referring. Indexing starts with 0.

On the other hand it is very important that you do not use indices with queries containing named parameters, as the names of the parameters would not be properly set.

Exceptions

Exception TypeCondition
ADOMediatorExceptionThrown to indicate run-time errors.

Example

The following example invokes method MyMethod by using the SHARES and VALUE columns as parameters and the C_ID column as a named parameter for the UPDATE output query. The "@MONEY" named parameter is used to store the result returned by MyMethod.

            ado.SelectAndUpdate ("MyMethod",
                 "SELECT C_ID, SHARES, VALUE FROM TRADES",
                 "UPDATE CUSTOMER SET MONEY=@MONEY WHERE C_ID=@C_ID",
                 "@MONEY", // where to store the result
                 // Column C_ID corresponds to the @C_ID parameter 
                 { new object[] {"@C_ID", "C_ID"} },
                 false);
            
The next example uses a driver that imposes parameters to a statement or a stored procedure be added in the order they have been defined. The parameters are marked using question-mark placeholders.
            ado.SelectAndUpdate ("MyMethod",
                 "SELECT C_ID, SHARES, VALUE FROM TRADES",
                 "UPDATE CUSTOMER SET MONEY=? WHERE C_ID=?",
                 "0", // first question-mark placeholder
                 // Column C_ID corresponds to the second question-mark placeholder
                 { new Object[] {"1", "C_ID"} },
                 false);
            

See Also

ADOMediator Class | WebCab.Libraries.Statistics.Hypothesis.ADO Namespace | ADOMediator.SelectAndUpdate Overload List