1237: Multi-tier/Distributed Database Applications in .NET

Delphi Clinic

C++Builder Gate

Training & Consultancy

Delphi Notes Weblog

Dr.Bob's Webshop


	Dr.Bob Examines... #65
See Also:	other Dr.Bob Examines columns or Delphi articles

This article is based on my Distributed Database in .NET session at the Borland Conference (Sept 2004) in San Jose.

Multi-tier/Distributed Database Applications in .NET
In this paper, a techniques is demonstrated to build multi-tier database applications with ASP.NET Web Services using Delphi for .NET as the development environment.
The server-side application will be returning DataSets to clients, and receiving DiffGrams from clients to update the remote database. Note that ASP.NET Web Services require a web server (like IIS). The result is a server that connects to several thin-client applications (i.e. clients that do not know or care which database they are connected to).

ASP.NET Web Services
We'll use an ASP.NET Web Service to export DataSets to client applications, who can work with these DataSets and send updates back to the Web Service (in order to update the underlying database).
First, create an ASP.NET Web Service as normal, and call it D8DataBaseServer. A new virtual directory called D8DataBaseServer is created, with the new ASP.NET Web Service project inside. By default, the new project contains a file WebService1.pas and .asmx, that you may want to rename as DataBaseWebService. The file DataBaseWebService.pas contains the definition for TWebService1, which you may want to rename to a more descriptive name as well, like TEmployeeData. Apart from the name of the web service, you should also add a unique namespace to it, using the WebService attribute, as follows:

  [WebService(Namespace='http://eBob42.org', Description='InterBase Employee DB')]

The web methods that we are about to write need to connect to the InterBase database to be able to produce their result. This means that we need a BdpConnection component to share this connection. You can either add it to the TEmployeeData web service definition, or - more conveniently - move to the design view of the Web Service and place the BdpConnection component on the Designer page (or drop it from the Data Explorer).

TEmployeeData Interface
Remove the sample HelloWorld method from the TEmployeeData definition, and add a method called GetDataSet instead, taking the tablename as argument, as well as the starting record and maximum number of records to return.

  [WebService(Namespace='http://eBob42.org', Description='InterBase Employee DB')]
  TEmployeeData = class(System.Web.Services.WebService)
  ...
  public
    constructor Create;
    [WebMethod]
    function GetDataSet(const TableName: String; start, max: Integer): DataSet;
  end;

GetDataSet will perform a SELECT command on the InterBase database, using TableName to specify which table to select records from. The start and max arguments are available as a result of the Fill method, which we'll see in a moment when implementing the GetDataSet method.

TEmployeeData Implementation
The GetDataSet method will create a BdpDataAdapter component, connected to the BdpConnection component, passing the SELECT * FROM substring, with the TableName parameter to complete this query. Note that this is potentially dangerous, since apart from the TableName, people could pass an SQL separator character as well as another SQL statement (like DROP TABLE, GRAND access or something else unwanted). However, we will use it for demonstrative purposes to see that it works, and then shield the interface from the outside world again.
The Fill method of the BdpDataAdapter component contains the ability to specify a starting position and maximum number of records that are returned, and that's where we can use the start and max arguments, as follows:

  function TEmployeeData.GetDataSet(const TableName: &String; start, max: Integer): DataSet;
  var
    DataAdapter: BdpDataAdapter;
  begin
    Result := DataSet.Create;
    DataAdapter := BdpDataAdapter.Create('select * from ' + TableName, BdpConnection1);
    try
      DataAdapter.Fill(Result, start, max, TableName)
    finally
      DataAdapter.Free
    end
  end;

Note that the BdpConnection will be opened when needed (i.e. when we call the Fill method of the BdpDataAdapter).
The GetDataSet method allows you to specify a TableName, starting record and maximum number of records to return. However, since it's dangerous to be able to append text to the SQL query, it's better to remove the WebMethod attribute from the GetDataSet declaration, and write a number of specific method instead. Each of the specific methods should return a specific table, hardcoding the SQL statement. Note that this doesn't have to be limited to single tables, you can also define JOINs here, and return the result of the JOIN in a DataSet as well.
For the Employee.gdb database, the tablenames are COUNTRY, CUSTOMER, DEPARTMENT, EMPLOYEE, EMPLOYEE_PROJECT, ITEMS, JOB, PROJECT, PROJ_DEPT_BUDGET, SALARY_HISTORY, and SALES. It's also safe to assume that the EMPLOYEE and PROJECT tables are related through the EMPLOYEE_PROJECT table. This results in the following eleven new method definitions inside the TEmployeeData web service class:

  [WebMethod(Description='Return the COUNTRY table from Employee.gdb')]
  function GetCOUNTRY(start, max: Integer): DataSet;
  [WebMethod(Description='Return the CUSTOMER table from Employee.gdb')]
  function GetCUSTOMER(start, max: Integer): DataSet;
  [WebMethod(Description='Return the DEPARTMENT table from Employee.gdb')]
  function GetDEPARTMENT(start, max: Integer): DataSet;
  [WebMethod(Description='Return the EMPLOYEE table from Employee.gdb')]
  function GetEMPLOYEE(start, max: Integer): DataSet;
  [WebMethod(Description='Return the EMPLOYEE_PROJECT table from Employee.gdb')]
  function GetEMPLOYEE_PROJECT(start, max: Integer): DataSet;
  [WebMethod(Description='Return the ITEMS table from Employee.gdb')]
  function GetITEMS(start, max: Integer): DataSet;
  [WebMethod(Description='Return the JOB table from Employee.gdb')]
  function GetJOB(start, max: Integer): DataSet;
  [WebMethod(Description='Return the PROJECT table from Employee.gdb')]
  function GetPROJECT(start, max: Integer): DataSet;
  [WebMethod(Description='Return the PROJ_DEPT_BUDGET table from Employee.gdb')]
  function GetPROJ_DEPT_BUDGET(start, max: Integer): DataSet;
  [WebMethod(Description='Return the SALES table from Employee.gdb')]
  function GetSALES(start, max: Integer): DataSet;
  [WebMethod(Description='Return the SALES_HISTORY table from Employee.gdb')]
  function GetSALES_HISTORY(start, max: Integer): DataSet;

The implementation of the first 11 methods is easy - only the implementation of the GetEMPLOYEE is shown in the following code, since the other methods are all calling the (now internal) method GetDataSet, each passing a different tablename:

  function TEmployeeData.GetEMPLOYEE(start, max: Integer): DataSet;
  begin
    Result := GetDataSet('EMPLOYEE', start, max)
  end;

Now it's time to save your work, compile the application, and test it from the Delphi for .NET IDE. This should produce your default web browser with the URL of the web service, showing the 11 methods that we have now to remotely access the database (the GetDataSet method should no longer be visible).
As an example, you can click on the GetEMPLOYEE_and_PROJECT method, which will allow you to call the method with sample values for the start and max arguments. Note that this testing can only be done locally (with the web service hosted on localhost), unless you edit the web.config file to allow remote connections to test it as well. You can specify some values for start and max, and click on the Invoke button to get a new page with the contents of the "SELECT * FROM EMPLOYEE INNER JOIN EMPLOYEE_PROJECT ON EMPLOYEE.EMP_NO = EMPLOYEE_PROJECT.EMP_NO INNER JOIN PROJECT ON PROJECT.PROJ_ID = EMPLOYEE_PROJECT.PROJ_ID" query, starting at 0 and returning max 8 records. When you have verified that this works as planned, it's time to build the client application that connects to this ASP.NET Web Service to get access to the database tables.

DataBase Web Service (thin) Clients
For the Web Service Client, you need to start a new application with File | New Windows Forms Application. Save it in DataBaseWSClient, and right-click on the project node in the Project Manager to choose the "Add Web Reference" menu option to import the TEmployeeData web service. This results in a dialog where we can specify the location of the WSDL for the TEmployeeData web service. If you've deployed it on your local machine, then it's http://localhost/D8DataBaseServer/DataBaseWebService.asmx?WSDL. When you click on the Add Reference button, the WSDL is saved, parsed and the import unit is generated and added to our Delphi for .NET project.
Once the web reference is added you can use it. Add localhost.DataBaseWebService to the uses clause of the WinForms application. There are different ways to create and keep an instance of the web service engines: either create a new instance in each method that uses it, or create a private field in the WinForm and create the instance in the OnLoad event (or on demand). Since we probably call a number of methods, it seems logical to add the private field called EmployeeData of type TEmployeeData to the TWinForm1 class, and create the instance in the OnLoad event handler as follows:

  procedure TWinForm.TWinForm_Load(sender: System.Object; e: System.EventArgs);
  begin
    EmployeeData := TEmployeeData.Create
  end;

We can now use the EmployeeData and call methods like GetEMPLOYEE when we want. In fact, that's exactly what we'll do, showing the resulting DataTables and Relation.

Retrieving Remote DataSets
As visual control to display the data, we can use a DataGrid control. Resize it so it covers the top part of the form, and the entire width of the form, and set the Anchor property to Top, Left, Right to make sure it resizes with the form. The DataGrid control is usually connected to a DataSet and/or DataTable component at design-time, but since we don't have one - yet - we need to configure it at run-time.
We can do this using a Button, or also in the OnLoad event handler of the Form. Since we've already created the EmployeeData web service instance in the OnLoad event handler, we can call the GetEMPLOYEE method at that place as well (specifying that we want to start with the first record - at index 0 - and get 8 records in total), connecting the resulting DataSet to the DataGrid, specifying EMPLOYEE as DataMember as follows:

  procedure TWinForm.TWinForm_Load(sender: System.Object; e: System.EventArgs);
  begin
    EmployeeData := TEmployeeData.Create;
    try
      RecNo := 0; // private field to hold current record number
      DataGrid1.DataSource := EmployeeData.GetEMPLOYEE(0,8);
      DataGrid1.DataMember := 'EMPLOYEE'
    except
      on Ex: Exception do
        MessageBox.Show(Ex.StackTrace, Ex.Message)
    end
  end;

Note that a private field RecNo needs to be added to the class definition of the WinForm. This field is used to maintain the current record (or rather the record number of the first EMPLOYEE record in the DataGrid). In order to navigate through the DataGrid, we need two more buttons, called btnNext, and btnPrev. Each of them will call the GetEMPLOYEE method again, passing the modified value of RecNo and the number of records that we want to retrieve at that time. The Click event of btnNext and btnPrevious are implemented follows:

  const
    PageSize = 8;

  procedure TWinForm.btnNext_Click(sender: System.Object; e: System.EventArgs);
  begin
    try
      Inc(RecNo, PageSize);
      DataGrid1.DataSource := EmployeeData.GetEMPLOYEE(RecNo,PageSize);
      DataGrid1.DataMember := 'EMPLOYEE'
    except
      on Ex: Exception do
        MessageBox.Show(Ex.StackTrace, Ex.Message)
    end
  end;

  procedure TWinForm.btnPrevious_Click(sender: System.Object; e: System.EventArgs);
  begin
    try
      Dec(RecNo, PageSize);
      if RecNo < 0 then RecNo := 0;
      DataGrid1.DataSource := EmployeeData.GetEMPLOYEE(RecNo,PageSize);
      DataGrid1.DataMember := 'EMPLOYEE'
    except
      on Ex: Exception do
        MessageBox.Show(Ex.StackTrace, Ex.Message)
    end
  end;

Note that the MaxRecords constant be made an option to specify on the WinForm itself, so the user can specify how many records to move forward or backward in the EMPLOYEE table. That's left as exercise for the reader, however.
The result of running this application is the following Form, showing the first 8 records:

Clicking on the Next 8 and Previous 8 buttons will give you 8 other records each. Note that the DataSource property of the DataGrid is assigned every time, ignoring the previous value of the DataSource, so you will only see a maximum of 8 records at a give time; never more than 8 (you could see less if the last page contains less than 8 records). There's one thing missing: you can make changes to the data in the DataGrid, but there is no way the data can be sent back to the Web Service to be used to update the database. This may not be a problem if the Web Service should only be used to make read-only data available, but it's only a small step to make it capable of handling updates as well. In order to handle DataSet updates, the Web Service would need to be extended with Set- methods, one for each Get- method that currently return the DataSets. The Set methods take a DataSet as argument, including the changes from the client side. However, while this approach would work, it could also lead to a significant bandwidth usage if a DataSet contains only a single change. It would be more efficient to only send the so-called diffgram, returned by calling the GetChanges method of the DataSet.

Applying Updates
Assuming the server will offer a web method called SetEMPLOYEE - to be implemented in a moment - we, can drop a new button, call it btnUpdate, and implement the Click event handler as follows:

  procedure TWinForm.btnUpdate_Click(sender: System.Object; e: System.EventArgs);
  var
    Changes: DataSet;
  begin
    try
      Changes := (DataGrid1.DataSource as DataSet).GetChanges;
      // optionally: measure Changes vs. complete DataSet
      if EmployeeData.SetEMPLOYEE(Changes) then
      begin
        (DataGrid1.DataSource as DataSet).Merge(Changes);
        (DataGrid1.DataSource as DataSet).AcceptChanges
      end
    except
      on Ex: Exception do
        MessageBox.Show(Ex.StackTrace, Ex.Message)
    end
  end;

Passing only the Changes instead of the full DataSet will make a significant difference, especially if you display more than a few records at the same time (so the DataSet grows) or send only a few changes at the same time (so the Changes are small). In order to show the difference, we can insert at the place of the // optionally: comments, the following new code to save the DataSet and the Changes as XML files:

  (DataGrid1.DataSource as DataSet).WriteXml('DataSetSchema.xml', XmlWriteMode.WriteSchema);
  (DataGrid1.DataSource as DataSet).WriteXml('DataSet.xml', XmlWriteMode.DiffGram);
  Changes.WriteXml('ChangesSchema.xml', XmlWriteMode.WriteSchema);
  Changes.WriteXml('Changes.xml', XmlWriteMode.DiffGram);

Note that this will save the DataSet including schema in DataSetSchema.xml, the DataSet plus DiffGram in DataSetxml, and the Changes including schema in ChangesSchema.xml and the Changes DiffGram in Changes.xml. The difference is usually a factor 4, but depends on a number of factors. On thing is sure: the Changes will never be bigger than the complete DataSet, so it's always more efficient to sent the Changes instead of the DataSet.
Now, reload the D8DataBaseServer project and go to the DataBaseWebService.pas unit. The TEmployeeData contains the 11 WebMethods that each return a DataSet. If you want to allow it to update the InterBase database, you need to extend the web service with methods that receive a the DiffGram with the changes. The definition of the new methods is as follows:

  [WebMethod(Description='Update the COUNTRY table.')]
  function SetCOUNTRY(ClientDS: DataSet): Boolean;
  [WebMethod(Description='Update the CUSTOMER table.')]
  function SetCUSTOMER(ClientDS: DataSet): Boolean;
  [WebMethod(Description='Update the DEPARTMENT table.')]
  function SetDEPARTMENT(ClientDS: DataSet): Boolean;
  [WebMethod(Description='Update the EMPLOYEE table.')]
  function SetEMPLOYEE(ClientDS: DataSet): Boolean;
  [WebMethod(Description='Update the EMPLOYEE_PROJECT table.')]
  function SetEMPLOYEE_PROJECT(ClientDS: DataSet): Boolean;
  [WebMethod(Description='Update the ITEMS table.')]
  function SetITEMS(ClientDS: DataSet): Boolean;
  [WebMethod(Description='Update the JOB table.')]
  function SetJOB(ClientDS: DataSet): Boolean;
  [WebMethod(Description='Update the PROJECT table.')]
  function SetPROJECT(ClientDS: DataSet): Boolean;
  [WebMethod(Description='Update the PROJ_DEPT_BUDGET table.')]
  function SetPROJ_DEPT_BUDGET(ClientDS: DataSet): Boolean;
  [WebMethod(Description='Update the SALES table.')]
  function SetSALES(ClientDS: DataSet): Boolean;
  [WebMethod(Description='Update the SALES_HISTORY table.')]
  function SetSALES_HISTORY(ClientDS: DataSet): Boolean;

Like the Set- methods, there is one SetDataSet method - not published as web method - that will implement the changes. This one will be responsible for accepting the DiffGrams and updating the corresponding table in the database. In order to update the table, the SetDataSet needs to know the original TableName, so it can create a BdpDataAdapter component and use the AutoUdate capabilities to create the UPDATE, INSERT and DELETE commands automatically.

//[WebMethod]
  procedure SetDataSet(TableName: String; ClientDS: DataSet);

Implementation
For the implementation of SetDataSet, we first need to add the Borland.Data.Common unit to the uses clause, to enable us to create and use instances of BdpUpdateMode types. The implementation of SetDataSet creates an instance of the BdpDataAdapter the same way we did before, using the information in the SELECT command only to construct the UPDATE, INSERT, and DELETE commands. The implementation of SetDataSet is as follows:

  function TEmployeeData.SetDataSet(TableName: &String; ClientDS: DataSet): Boolean;
  var
    DataAdapter: BdpDataAdapter;
  begin
    Result := False;
    DataAdapter := BdpDataAdapter.Create('SELECT * FROM ' + TableName, BdpConnection1);
    try
      DataAdapter.AutoUpdate(ClientDS, TableName, BdpUpdateMode.All)
    finally
      DataAdapter.Free;
      Result := True // success!
    end
  end;

The AutoUpdate method of the BdpDataAdapter will use a BdpCommandBuilder to automatically generate the correct UPDATE, INSERT and DELETE SQL statements that correspond to the SELECT statement - hence the reason why we had to create the BdpDataAdapter with the "select * from TableName" as argument to the constructor. With this generic SetDataSet method available, we can now implement the InterBase Employee specific methods. The following code only shows the implementation of the SetEMPLOYEE method, since the other methods are all calling the internal method SetDataSet, each passing a different TableName and the DataSet:

  function TEmployeeData.SetEMPLOYEE(ClientDS: DataSet): Boolean;
  begin
    Result := SetDataSet('EMPLOYEE', ClientDS)
  end;

This completes the changes that are required for the D8DataBaseServer web service, so save the project and recompile it. You can test it from the Delphi for .NET IDE again, and this time you should see 22 methods in total: 11 GetXXX table methods and 11 SetXXX table methods (we did not implement the SetEMPLOYEE_and_PROJECT or SetEMPLOYEE_SALARY_HISTORY methods).

We have seen how to use Delphi for .NET to build an ASP.NET Web Service application that publishes the data tables from the database to the outside world, and can also receive updates in the datasets in order to modify the datatables as well. We have also seen how to build a Windows Forms client application that uses this ASP.NET Web Service to access the database tables remotely, display the data in a DataGrid, navigate through the grid by requesting next and previous pages of records, and edit the data inside the DataGrid. Finally, we've implemented a way to update the Data and send the changes back to the ASP.NET Web Service where they are resolved in the original database table. The combination of .NET DataSets and ASP.NET Web Services built with Delphi for .NET allow us to build multi-tier and distributed applications resulting in thin-clients that do not connect to the actual database tables, but to a middle-ware ASP.NET web service.
Note that we did not cover security of the Web Service - you should realise that anyone who knows where to find this Web Service can call the methods, and read (or even write) the datasets. You may want to extend each method with a number of parameters (like username, password, pincode), or use a secure connection to pass the data from the server to the clients.

For more information, see Dr.Bob Examines #62 on TADONETConnector: connect ADO.NET DataSets to VCL for .NET Clients, #54 on .NET Remoting and DataSets, #60 on Delphi 2005 and Passing Parameters through .NET Remoting, or my BorCon 2004 paper on Multi-tier/Distributed Database Applications in .NET.