QueryandProcess script code

Use the following sample code for the QueryandProcess script.

DIM Connection as aaDBClient.aaDBConnection;

DIM Command1 as aaDBClient.aaDBCommand;

DIM Transaction as aaDBClient.aaDBTransaction;

'Create a connection object with the connection string.

LogMessage("Creating connection");

Connection = aaDBAccess.CreateConnection("Data Source=localhost;Initial Catalog=AdventureWorks;Integrated Security=true");

'Create a transaction object within the connection object.

LogMessage("Creating transaction object");

Transaction = Connection.CreateTransaction();

'Create a command object for the transaction object, with a SQL statement.

LogMessage("Creating a command object");

Command1 = Transaction.CreateCommand("Select * from Production.Product WHERE ProductNumber = @ProductNumber", aaDBCommandType.SqlStatement, true);

'We used a parameter to specify the value for the ProductNumber field, so initialize it.

Command1.SetCharParameterByName("ProductNumber", me.PartNumber, aaDBParameterDirection.Input, 50);

'Everything is ready, let's execute the transaction sync.

LogMessage("Executing transaction sync");

DIM ResultCode as integer;

ResultCode = Transaction.ExecuteSync();

if ResultCode <> 0 then

'Failed to execute transaction sync, report the reason.

LogMessage("Got error " + ResultCode + " executing transaction sync");

else

if Transaction.ExecutionState == aaDBTransactionState.Completed then

DIM Rows as integer;

Rows = Command1.RowCount;

LogMessage("Row count returned from command is " + Rows);

'Use other methods of script library to read data and assign to UDAs, etc.

if Rows > 0 then

LogMessage("Getting column '" + me.ColumnToRead + "' from row 0");

Command1.SelectRow(0);

'Return the requested column value from this row, signal done.

me.ColumnValue = Command1.GetCurrentRowColumnByName(me.ColumnToRead);

me.ColumnReadDone = true;

endif;

'When done, dispose the command.

Command1.Dispose();

endif;

'When done, dispose the transaction.

Transaction.Dispose();

endif;

'Reset for next time

me.ReadTransaction = false;