ADO.NET Mediator for the .NET Platform

Overview

Most of the financial and mathematical functionality provided with our .NET Services is intended for use within a multi-tier environment where the back-end role is taken by an SQL Database server. By packaging this functionality within .NET components such as XML Web services and .NET Class Libraries we separate the business logic from the database logic, allowing you to customize the way you access the database and the way the retrieved data is sent to the business methods.

In order to assist you in developing DBMS based .NET solutions, we provide the ADO Mediator as a standardized way of using our .NET components with an SQL Database server. The ADO Mediator is also a .NET component, which sits on the same level as the other financial and mathematical .NET components and is a seamlessly integrated component of this .NET Service. While reducing the amount of DBMS code you have to type, it allows you to concentrate on the SQL queries and procedure calls to the Database server and map the stored data directly to the business methods of this .NET Service.

The ADO.NET Mediator

There is one ADOMediator class for every module in this .NET Service. You will use the corresponding ADOMediator class for performing Database operations with classes of a certain module. The ADO Mediator class is located inside the ADO subnamespace of its corresponding module. For example if a module contains the:

         WebCab.Libraries.Finance.Trading

namespace, its ADOMediator class will be found under:

         WebCab.Libraries.Finance.Trading.ADO.ADOMediator

Configuring the ADO.NET Mediator

There are three steps to perform in order to start using an ADO.NET Mediator class:

1. Specify the ADO.NET Driver

   The constructor accepts an input connection and an output connection. There is no restriction that you should provide both an input and an output connection. You may choose to read data and display it inside your Application, compute data and write it into the database, or read and write to and from the database, and specify only the input or output database connection.

   Connecting to your database requires an ADO.NET Driver. This driver is a .NET class which is part of a DLL file provided by your Database vendor. You specify the driver you wish to use by sending in to the ADOMediator constructor its run-time type. That is, if your driver class name is System.Data.SqlClient.SqlConnection (i.e. the built-in ADO.NET driver class name for connecting to the MS SQL Server), its run-time type under C# Sharp is specified by typing inside your source code:

            typeof (System.Data.SqlClient.SqlConnection)}
   

2. Provide the input/output ConnectionString property

   The ConnectionString property is a piece of text which describes the Database server machine address, the credentials, and additional information required in order to obtain access to certain parts of your Database server. This piece of text is always documented by the provider of the ADO.NET Driver and should be used as such when submitting it to an ADOMediator constructor. An example for the previous ADO.NET SQL Server driver would be:

            "Initial Catalog=Northwind;Data Source=localhost;Integrated Security=true;"}
   

   You may easily notice that the string above describes the machine name and the initial catalog to connect to. The Integrated Security=true bit enables you to connect to the SQL Server 2000 by using the current Windows login username and password.

3. Assign a business .NET component

   The previous two steps are part of creating an instance of an ADOMediator class and taking care of the Database connections. This third step involves specifying the business class belonging to this module which deals with the financial and mathematical functionality. Choose a class of the corresponding module and assign an instance of this class to the UnderlyingInstance property of the previously created {\code ADOMediator} instance.

   For example, if the name of the class you choose is TradingClass, you could write the following C# }:

            TradingClass aTradingClassInstance = new TradingClass ();
            adoInstance.UnderlyingInstance = aTradingClassInstance;
   

   Remark: We assume adoInstance is the name of the previously created ADOMediator instance.

Using the ADO Mediator

Once these three steps have been performed, you may use the created ADOMediator instance to perform Database related calculations using the assigned .NET business class. There are several methods which you may use with your ADO Mediator instance. Some methods allow you to read from the database and perform calculations for every retrieved row, and then store the result in a .NET variable. Other methods allow you to specify the input values and write the result in the database. There are also methods which allow you to read the input parameters from the database and write the method results back into the database in one go.

The basic methods of the ADO mediator are:

• Select Method
  

  Allows you to retrieve the results of a method of your choice belonging to the underlying business class instance, applied to every row of an SQL SELECT query.

• Update Method
  

  Directly updates values inside your database, as returned by a method belonging to the underlying business class instance.

• SelectAndUpdate Method
  

  Allows you to combine the two methods mentioned above by selecting the data, computing the results and writing it back to the database, according to your own criteria.

A complete API reference for these methods is found inside the Documentation folder of this pack. The Windows Installer (MSI) for this .NET Service has also created a short cut to this documentation from the Start Menu.

How the ADO Mediator Works

The way the ADO Mediator processes requests from the client and transfers them to the .NET components and the Database is shown in the diagram below.

Fig: This diagram describes how the ADO Mediator passes onto the Database and the .NET components its client requests.

C# Source Code Example

The following C# source code makes use of the ADO Mediator's functionality in order to read and update the database according to a known algorithm. We use the standard SQL Server ADO.NET Driver to connect to a fictitious table named TRADES, and invoke the TradingMethod method of the TradingClass business class.

using System;
using System.Data.SqlClient;
using WebCab.Libraries.Finance.Trading;
using WebCab.Libraries.Finance.Trading.ADO;
...
  try {
    ...
    /*
     * We specify the same driver and connection string for both the input and output
     * connections to the SQL Server 2000 Database server.
     */
    ADOMediator adoInstance = new ADOMediator (
        typeof (SqlConnection), // Input
    "Initial Catalog=Northwind;Data Source=localhost;Integrated Security=true;",
        typeof (SqlConnection), // Output
    "Initial Catalog=Northwind;Data Source=localhost;Integrated Security=true;");

    /*
     * We assign an instance of the TradingClass business class to the newly created
     * instance of this ADO Mediator.
     */
    adoInstance.UnderlyingInstance = new TradingClass ();
    
    /*
     * We invoke the SelectAndUpdate method which will populate the table according to
     * the result of the method and the position of the input values in the database.
     */
    adoInstance.SelectAndUpdate ("TradingMethod",
        "SELECT C\_ID, SHARES, VALUE FROM TRADES", // Select
        "UPDATE CUSTOMER SET MONEY=@MONEY WHERE C\_ID=@C\_ID", // Update
        "@MONEY", // Maps the TradingMethod method result to @MONEY
        {new Object[] {"@C\_ID", "C\_ID"}}, // Maps C\_ID to @C\_ID
        false); // True when updating with a stored procedure 
  }
  catch (ADOMediatorException e) {
    Console.WriteLine (e);
  }

The ADO Mediator instance above requires the same information as in the case of writing your own ADO.NET source code. The difference lies in the fact that you do not have to manually control the driver specific operations such as opening and closing a connection, and only concentrate on your SQL queries and the functionality of our business classes. You start by following the three steps mentioned above by making calls to any ADOMediator methods.

Remark: Specifying the input and/or output ADO.NET drivers and their connection strings and assigning an instance of the fictitious TradingClass business class.

As you may notice, the SelectAndUpdate method above follows a logical pattern of describing how the data should be read from the database, computed, and written back, with special care to the use of named parameters. You do not require previous experience in dealing with named parameters, as they are basically a way to substitute real values with names inside SQL queries. You may have spotted the @MONEY and @C_ID named parameters in our UPDATE statement above. These named parameters will be replaced with real values, taken either from the TradingMethod's result, or directly from a column returned by the SELECT query.

In our case, we replace @MONEY with the result of applying the TradingMethod method to the values of the SHARES and VALUE columns returned by the SELECT query. The second named parameter (@C\_ID) is replaced directly by the value of the C\_ID column of the SELECT query. For more information about named parameters you may wish to consult your ADO.NET driver's manual. Most special cases are related to the use of named parameters and have been covered by the current version of the ADO Mediator.