Building ASP.NET Applications with Delphi and Advantage Database Server : Page 3

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.