ike Delphi’s other development platforms, Delphi for .NET offers a wide range of data access options for ASP.NET developers. These include support for the generic foundation class library (FCL) data access classes (ODBC and OleDb), dbExpress drivers, BDP for .NET providers, as well as support for all third-party solutions that support the ADO.NET data provider interface. (ADO.NET is the portion of the .NET FCL that applies to data access, storage, and manipulation.)

Fortunately, the choice of data provider for ASP.NET development is often uncomplicated. In most cases, you should use the data provider supplied by the publisher of the database. For Advantage, this is Advantage Data Provider for .NET, which from this point forward, I’ll simply refer to as the Advantage Data Provider.

Because of the special capabilities of the Advantage Database Server itself, the Advantage Data Provider supports features unheard of in most other data providers. But there is a problem. You cannot begin a web page by dragging and dropping the Advantage Data Provider from Delphi’s Data Explorer. Since Delphi’s help tends to emphasize this approach, Advantage developers sometimes don’t know where to begin.

Ideally, you should have a class, or set of classes?independent of the web pages on which their data is displayed?that perform data access.

While the code you’ll see here is amenable to the reusable data layer approach, this article, rather than focusing on the reusable data layer approach, sheds light on some of the components of the Advantage Data Provider that you use to access your Advantage data. For brevity, the discussion focuses only on the primary subset of components and techniques. However, that should be enough to get you started.

Getting Started

Before discussing the principle classes of the Advantage Data Provider, you need to prepare your Delphi ASP.NET application to use Advantage. While the steps are not complex, they are essential.

You must begin with a Delphi for .NET ASP.NET application. The following steps assume you are using CodeGear’s RAD Studio 2007; however, the steps are similar for both Borland Developer Studio (BDS) 2006 and 2005. The data layer abstraction technique described applies to ASP.NET applications written in Delphi Prism as well.

Start Delphi in a mode that includes the .NET personality. If you have installed the full version of RAD Studio, you can simply load RAD Studio, which includes all personalities. Alternatively, you can select the Window’s Start Menu, select the CodeGear folder, and select either RAD Studio or Delphi for .NET. Delphi for .NET includes only the .NET personality.

	?
Figure 1. New ASP.NET Web Application: The name you enter here serves as both the project source file name and the name of the ASP.NET virtual directory.

With Delphi running, select File New ASP.NET Web Application from Delphi’s main menu.

When the New ASP.NET Web Application dialog box appears (see Figure 1), enter the name of your application. This will be the name used for both your project source file and to create a virtual directory in the folder c:inetpubwwwwroot. For this example, enter the name AdsASPNETApplication. Click OK to continue.

You now have a new ASP.NET application, and can begin entering content and web controls. However, before you can begin accessing Advantage, you have to add the Advantage Data Provider assembly to the project’s References folder. To do this, right-click on the References folder in the Project Manager, and select Add Reference (see Figure 2).

	?
Figure 2. Adding a Reference: You need to add the Advantage Data Provider assembly to the project’s references folder before you can access Advantage data.

Delphi displays the Add Reference dialog box (this requires a few moments to populate). You do not have to wait for the .NET Assemblies tab to be populated. Simply click the Browse button and navigate to the directory where the Advantage Data Provider is installed. The default location is c:Program FilesAdvantageado.net2.0 (or 1.0 if you are using BDS 2006 or BDS 2005, because they use ASP.NET 1.1).

From this folder, select the Advantage.Data.Provider.dll and click Open. After you select an assembly, it appears in the lower portion of the Add References dialog box, as shown in Figure 3.

	?
Figure 3. Selecting References: After you select an assembly, it appears in the New References list.

	?
Figure 4. Populated References: Here’s how the References folder should look after selecting the Advantage Data Provider DLL.

When you are done, click OK. Your References folder should now look something like Figure 4.

This next step is optional. My personal preference is to use the Advantage Data Provider assembly as a local copy, which means that Delphi will place a copy of the assembly in the bin subdirectory for your application. This lets you deploy the ASP.NET application to your web server by copying only the application directory structure without having to install any other files (other than the .NET framework itself, of course).

There are simply too many issues associated with this topic to cover here, so I will let you make your own mind up about this step. However, to follow my advice, right-click the Advantage.Data.Provider.dll assembly node in the References folder of Delphi’s Project Manager and select “Copy Local” from the menu. Delphi will display a warning, but that’s OK. Select “Yes” to accept the local copy designation.

Before you’re ready to go you must also add the Advantage.Data.Provider namespace to the uses clause of any code unit from which you want to reference any Advantage Data Provider classes. Figure 5 shows an example of a uses clause where this namespace appears.

	?
Figure 5. Using Namespaces: Add a “uses” clause to each code unit to reference Advantage Data Provider classes.

You are now ready to reference and use the classes of the Advantage Data Provider.

Advantage Data Provider Primary Classes

As an Advantage user, the primary classes that you will use in your ASP.NET applications include AdsConnection, AdsCommand, AdsDataReader, AdsExtendedReader, and AdsDataAdapter. There is also a good chance that you’ll use AdsTransactions and AdsParameters. The following list describes each class briefly.

• AdsConnection: You use an AdsConnection to make your connection to the Advantage Database Server. The AdsConnection.ConnectionString property defines where your data is located, user and password information, and other connection specifics of, including the size and duration of its connection pool.
• AdsCommands: After establishing a connection, you use one or more AdsCommands to configure your database as well as access its data. AdsCommands can execute statements that return no result sets (such as a CREATE TABLE or DROP VIEW statement), that return a scalar value (a single value of type TObject), or a result set. You can navigate returned result sets using a DataReader, or use them to populate an in-memory DataTable.
• AdsReader: An AdsDataReader is a read-only forward-only cursor that you can use to read records returned by a SELECT statement or a stored procedure. The Advantage .NET Data Provider also supports a super-charged data reader in the form of the AdsExtendedReader, which is unique in the .NET world in that it provides bi-directional navigation, as well as read/write access to data. In addition, it supports server-side cursors for high-speed navigation as well as optimized filtering and scopes (also known as ranges).
• AdsDataAdapter: The AdsDataAdapter component uses an AdsCommand internally to load data from a result set into an in-memory DataTable. DataTables are useful for loading data into grid-like controls, and also provide a convenient mechanism for persisting result sets between web page renderings.
• AdsTransaction: Use an AdsTransaction when your code needs to provide all-or-none modifications to two or more records, or to data in two or more database tables. You use the BeginTransaction method of your connected AdsConnection to create an AdsTransaction. After making the data changes, call the AdsTransaction’s Commit or Rollback methods to perform the associated operation.
• AdsParameters: When the CommandText property of your AdsCommand specifies a parameterized query or stored procedure call, you use AdsParameters instances to define the data types, names, and values of those parameters. You can create AdsParameters either by calling the AdsCommand’s CreateParameter method or by calling the AdsParameter’s constructor directly. After configuring the created parameters, add them to the AdsCommand’s Parameters collection.

Basic Operations

You establish a connection to a data dictionary, or to a folder containing free tables, using an AdsConnection. The following code segment demonstrates a simple connection to the data dictionary of the sample database.

...var  Connection: AdsConnection;begin  Connection := AdsConnection.Create;  Connection.ConnectionString := 'Data Source='+    'C:AdsBookDemoDictionary.add;user     id=adssys;password=password;' +    'ServerType=ADS_REMOTE_SERVER | ADS_LOCAL_SERVER;';  Connection.Open;  try    //code to use the connection here  finally    Connection.Close;  end;end;

This simple example demonstrates calling the AdsConnection, constructor and assigning its connection string, followed by a call to open the connection, use it, and close it. All the following examples assume that you have created an active connection referred to by the variable named Connection.

The preceding example represents the connection string literally in the code; however, it’s also possible to store the connection string in the application’s web.config file or in machine.config (a configuration file available to all applications on the web server in ASP.NET 2.0 and later).

In ASP.NET applications, it’s important to ensure that each call to open a connection is matched by a corresponding call to close the connection. By default, the Advantage Data Provider gets its connections from a connection pool. If you fail to call close, the connection will not be returned to the connection pool until the ASP.NET garbage collector destroys the connection (and this might take a while). In contrast, by closing the connection explicitly, you ensure that the connection returns to the pool as soon as possible following its use, making it immediately available for use by another web page.

The connection provides methods to create AdsTransactions and AdsCommands. For example, the following code segment creates a table named #MyTable (the # character designates this table as a temporary table, one that exists only for the duration of the current connection).

var  Command: AdsCommand;begin  Command := Connection.CreateCommand;  Command.CommandText :=     'CREATE TABLE #MyTable (' +    'NumField Integer, ' +    'StringField cichar ( 100 ))';  Command.ExecuteNonQuery;

The CREATE TABLE command is a SQL data definition language (DDL) command, and it does not return a value or a result set. If your query returns a scalar value, you can use ExecuteScalar, which returns a TObject result. It is necessary to convert or cast this result to the appropriate data type, as demonstrated in the following code segment.

var  Command: AdsCommand;  IntValue: Integer;begin  Command := Connection.CreateCommand;  Command.CommandText :=      'SELECT Count(*) FROM #MyTable';  IntValue := Convert.ToInt32(     Command.ExecuteScalar);

Whenever you need to perform two or more operations, and have them succeed in an all-or-none fashion, you use an AdsTransaction. The following code demonstrates how to insert four records into the temporary table #MyTable within a transaction.

var  Command: AdsCommand;  Transaction: AdsTransaction;begin  Transaction := Connection.BeginTransaction;  try    Command := Connection.CreateCommand;    Command.CommandText := 'INSERT INTO #MyTable ' +       'VALUES (1, ''one'');';    Command.ExecuteNonQuery;    Command.CommandText := 'INSERT INTO #MyTable ' +       'VALUES (2, ''two'');';    Command.ExecuteNonQuery;    //The following command contains a SQL Script    Command.CommandText :=          'INSERT INTO #MyTable VALUES (3, ''three'');' +         'INSERT INTO #MyTable VALUES (4, ''four'');';    Command.ExecuteNonQuery;    Transaction.Commit;  except    Transaction.Rollback;  end;

When a query returns a result set, you can use a data reader to obtain a cursor to the result set or load the result set into an in-memory DataTable. The following example demonstrates a function that returns a result set using an AdsDataReader.

function ReusableDataLayer.GetAllEmployeeNames:    AdsDataReader;var  Command: AdsCommand;begin  Command := Connection.CreateCommand;  Command.CommandText :=      'SELECT [First Name] + '' '' +     '[Last Name] as Name, ' +     '[Employee Number] FROM [EMPLOYEE]';  Result := Command.ExecuteReader;end;

	?
Figure 6. Populated ListBox: Here’s how the page might look after populating the ListBox with all the employee names.

The following code demonstrates how to use the preceding code, which is associated with a reusable data layer, to bind data to a ListBox (see Figure 6).

procedure TUseDataReader.Button1_Click(   sender: System.Object; e: System.EventArgs);var  ReusableDataLayer1: ReusableDataLayer;  DataReader: AdsDataReader;begin  ReusableDataLayer1 := ReusableDataLayer.Create(Context);  try    DataReader := ReusableDataLayer1.GetAllEmployeeNames;    try      ListBox1.DataSource := DataReader;      ListBox1.DataTextField := 'Name';      ListBox1.DataValueField := 'Employee Number';      DataBind;    finally      DataReader.Close;    end;  finally    ReusableDataLayer1.Dispose;  end;end;

In the preceding code, note that the Dispose method of the reusable data layer calls the AdsConnection’s Close method. It’s also important to ensure that you close a DataReader when you are through with it.

Loading Data Programmatically

While the preceding example used data binding, you could achieve the same effect by loading the list box programmatically, by iterating through the DataReader and loading each row into the list box manually. The following example has exactly the same results as the preceding example, but without the call to DataBind.

procedure TUseDataReader.Button2_Click(   sender: System.Object; e: System.EventArgs);var  ReusableDataLayer1: ReusableDataLayer;  DataReader: AdsDataReader;begin  ReusableDataLayer1 := ReusableDataLayer.Create(Context);  try    DataReader := ReusableDataLayer1.GetAllEmployeeNames;    try      while DataReader.Read do        ListBox1.Items.Add(           ListItem.Create(DataReader.GetString(0),           DataReader.GetInt32(1).ToString));    finally      DataReader.Close    end;  finally    ReusableDataLayer1.Dispose;  end;end;

You use DataTables to hold part or all of a result set in memory: for data manipulation, data binding, and even storing sets of data for later use. One of the easiest ways to populate a DataTable is to use an AdsDataAdapter. An AdsDataAdapter contains an AdsCommand that returns a result set, either through a SQL SELECT statement or a call to a stored procedure that returns a result set. After configuring the AdsDataAdapter, you call its Fill method to transfer the data into a DataTable, as shown below:

function ReusableDataLayer.GetCustomerSales(CustNo: Integer):DataTable;var  Command: AdsCommand;  Parameter: AdsParameter;  DataAdapter: AdsDataAdapter;begin  Command := Connection.CreateCommand;  Command.CommandText :=      'SELECT * FROM INVOICE ' +     '  WHERE [Customer ID] = :custno';  Parameter := Command.CreateParameter;  Parameter.DbType := System.Data.DbType.Int32;  Parameter.ParameterName := 'custno';  Parameter.Value := TObject(CustNo);  Command.Parameters.Add(Parameter);  DataAdapter := AdsDataAdapter.Create(Command);  Result := DataTable.Create;  DataAdapter.Fill(Result);end;

The GetCustomerSales method returns a DataTable. Note that the example also demonstrates using an AdsParameter.

The following code uses the GetCustomerSales method defined above. It obtains the value passed to GetCustomerSales from a TextBox on a web page:

procedure TDataTable.Button1_Click(   sender: System.Object; e: System.EventArgs);var  ReusableDataLayer1: ReusableDataLayer;  DataReader: AdsDataReader;begin  ReusableDataLayer1 := ReusableDataLayer.Create(Context);  try    DataGrid1.DataSource :=      ReusableDataLayer1.GetCustomerSales(         Convert.ToInt32(TextBox1.Text));    DataBind;  finally    ReusableDataLayer1.Dispose;  end;end;

	?
Figure 7. DataGrid Example: This DataGrid was populated using an AdsDataAdapter that retrieves invoices for a specific Customer ID.

Figure 7 shows an example of how the web page might look after the preceding code executes.

The final example demonstrates the use of an AdsExtendedReader (see Listing 1). As mentioned earlier in this paper, the AdsExtendedReader is unique in the world of .NET data providers, in that it supports both bi-directional navigation and read/write capabilities.

To recreate the code in Listing 1 without using an AdsExtendedReader, you would have to construct a separate UPDATE query to update the first record modified by this example, as well as a SELECT query (to detect if the second record already exists) followed by an INSERT query.

The value of the AdsExtendedReader in ASP.NET applications cannot be overemphasized; it gives you an alternative that?in some cases?can save you from writing dozens, if not hundreds of lines of code.

The Advantage Data Provider for .NET is the preferred data access mechanism for developing ASP.NET applications with Delphi and the Advantage Database Server. It’s easy to use and deploy, and supports some advanced features unavailable in any other .NET data provider.