Multi-tier/Distributed Database Applications in .NET
In this session, two techniques are demonstrated to build multi-tier database applications: ASP.NET Web Services and .NET Remoting using Delphi 8 for .NET as the development environment.
In both cases, the server-side application will be returning DataSets to clients, and receiving DiffGrams from clients to update the remote database. The only difference is that ASP.NET Web Services require a web server (like IIS) and ASP.NET, while .NET Remoting can rely on built-in capabilities. The latter supports different message formats and transport protocols which will also be covered, although the main focus is on the techniques to built one 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
In this section, 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')]

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;

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;

  [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;

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

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 8 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;

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;

  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;

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;

  (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);

  [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;

//[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;

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

In this first section, we have seen how to use Delphi 8 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 8 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. A similar technique will be implemented using .NET Remoting in the next part of this session. 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.

.NET Remoting
In this section, we will use .NET Remoting to build a simple multi-tier application passing a DataSet from the Server to Clients, and updates back to the Server (like the ASP.NET DataBase Web Service).
A .NET Remoting architecture consists of a server side and a client side, as well as an object that is used "between" the server and the client. The object can be a remote object - meaning that is always remains at the server side, and methods are executed at the server side only. Alternately, you can also have so-called mobile objects, that are serialized and send to the client machine, where they are deserialized and executed.

Remote Objects
A remote object remains at the server side, and is referenced to and used by one or more clients. The clients create a reference to the remote object and invoke (remote) methods from this server. The .NET Remoting framework supports different communication protocols (HTTP and TCP), message formats (binary and SOAP) and security (IIS security and SSL), which can all be extended as well. Remote objects are derived from class MarshalByRefObject, specifying that the objects will be returned by reference. A remote object is activated at the server, and the client gets a reference to the remote object called a proxy (an instance of TransparentProject class). The client can then use the proxy to call the remote methods, turning the request into a serialized message by a formatter, in a specified format (binary or SOAP). The serialized message is transported to the remote server object using a transport channel that uses HTTP or TCP as communication layer. At the server side, the remote object receives an incoming message using a transport channel, and has to deserialise the message into a request for a method invocation. The response of the method call is formatted into a response message, transported using the transport channel, received by the client again, and deserialised into the answer that can be used.

Shared Assembly
Using .NET Remoting, the client and server must be able to understand each other, talking about the same (interface) definition of the remote object. One of the techniques that is used is a shared assembly, which has to be deployed on the server as well as the client machines. The shared assembly only has to contain the shared interface definition, which is also called the remote object manager interface. Using Delphi 8 for .NET, this means you have to start a new package project with File | New Package, saving the package in SharedRemoteInterface. Right-click on the Requires node, and choose Add Reference. Use the Add Reference dialog to add the System.Data assembly to the requirements of the package. Next, do File | New - Unit in order to add a new unit to the package, and save that unit in file SharedInterface.pas. The interface definition for the remote object can be placed in this unit, and is as follows:

  unit SharedInterface;
  interface
  uses
    System.Data;

  type
    IRemoteObjectManager = interface
      function GetEMPLOYEE: DataSet;
      function SetEMPLOYEE(ClientDS: DataSet): Boolean;
    end;

  implementation

  end.

Remote Server
Once we have the remote object interface definition, we can implement it inside a remote server project. Note that there is a little problem with the Delphi 8 for .NET IDE if you start a new project that references an assembly from a previous project (that you just created in the IDE). To avoid these problems, you have to close the IDE between an assembly project and an assembly-using project.
Restart Delphi 8 for .NET, and create a new project for the remote server. To keep it simple, create a console project for the remote server, and call it RemoteDataBaseServer. Right-click on the project node and do Add Reference to add the reference to the SharedRemoteInterface.dll. Since this assembly will not be installed in the Global Assembly Cache, you should use the Browse button to locate it. Note that after you've compiled the console project, this will automatically copy the SharedRemoteInterface.dll to your project directory. You can now implement the remote object, by first adding the SharedInterface unit to your uses clause, and then writing the following code:

  type
    RemoteObjectManager = class(MarshalByRefObject, IRemoteObjectManager)
    public
      function GetEMPLOYEE: DataSet;
      function SetEMPLOYEE(ClientDS: DataSet): Boolean;
    end;

Get/SetEMPLOYEE Implementation
Then, add the System.Data, Borland.Data.Provider, Borland.Data.Common, and SharedInterface units to the uses clause of the RemoteDataBaseServer project, and implement the GetEMPLOYEE and SetEMPLOYEE methods as follows:

  function RemoteObjectManager.GetEMPLOYEE: DataSet;
  var
    Connection: BdpConnection;
    DataAdapter: BdpDataAdapter;
  begin
    Result := DataSet.Create;
    Connection := BdpConnection.Create('database=localhost:C:Program Files' +
      'Common FilesBorland SharedDataemployee.gdb;' +
      'assembly=Borland.Data.Interbase,Version=1.5.1.0,Culture=neutral,' +
      'PublicKeyToken=91d62ebb5b0d1b1b;vendorclient=gds32.dll;' +
      'provider=Interbase;username=sysdba;password=masterkey');
    Connection.ConnectionOptions := 'rolename=myrole;' +
      'transaction isolation=ReadCommitted;sqldialect=3;'+
      'waitonlocks=False;loginprompt=False;servercharset=;commitretain=False';
    DataAdapter := BdpDataAdapter.Create('SELECT * FROM EMPLOYEE', Connection);
    DataAdapter.Fill(Result, 'EMPLOYEE')
  end;

  function RemoteObjectManager.SetEMPLOYEE(ClientDS: DataSet): Boolean;
  var
    Connection: BdpConnection;
    DataAdapter: BdpDataAdapter;
  begin
    Result := False;
    Connection := BdpConnection.Create('database=localhost:C:Program Files' +
      'Common FilesBorland SharedDataemployee.gdb;' +
      'assembly=Borland.Data.Interbase,Version=1.5.1.0,Culture=neutral,' +
      'PublicKeyToken=91d62ebb5b0d1b1b;vendorclient=gds32.dll;' +
      'provider=Interbase;username=sysdba;password=masterkey');
    Connection.ConnectionOptions := 'rolename=myrole;' +
      'transaction isolation=ReadCommitted;sqldialect=3;'+
      'waitonlocks=False;loginprompt=False;servercharset=;commitretain=False';
    DataAdapter := BdpDataAdapter.Create('SELECT * FROM EMPLOYEE', Connection);
    DataAdapter.AutoUpdate(ClientDS, 'EMPLOYEE', BdpUpdateMode.All);
    Result := True // success!
  end;

System.Runtime.Remoting
So far, this is still like the ASP.NET DataBase Web Service project. But now we turn this project in a .NET Remoting server. Right-click on the project node and add a reference to the System.Runtime.Remoting assembly. This assembly contains a number of useful namespaces, like System.Runtime.Remoting, System.Runtime.Remoting.Channels and System.Runtime.Remoting.Channels.HTTP, that we need to add to the uses clause as well. The complete uses clause should now be as follows:

  uses
    System.Data,
    Borland.Data.Provider,
    Borland.Data.Common,
    SharedInterface,
    System.Runtime.Remoting,
    System.Runtime.Remoting.Channels,
    System.Runtime.Remoting.Channels.HTTP;

  const
    PortNumber = 4242;
    ServerResource = 'RemoteObjectManager.soap';
  var
    Channel: HttpChannel;
  begin
    writeln('RemoteServer started.');
    Channel := HTTPChannel.Create(PortNumber);
    ChannelServices.RegisterChannel(Channel);
    writeln('Listening for SOAP messages on HTTP port ', PortNumber);
    RemotingConfiguration.RegisterWellKnownServiceType(
      typeof(RemoteObjectManager),
      ServerResource,
      WellKnownObjectMode.Singleton); // only one remote object
    writeln('Hit  to stop.');
    readln
  end.

Remote Client
Time to start the .NET Remoting client application. Since we should be able to use the result of the GetEMPLOYEE method, and send changes back using the SetEMPLOYEE method, we should create a new Windows Forms Application for the client, and save it as RemoteDataBaseClient. Place a DataGrid. Then, right-click on the project and add the System.Runtime.Remoting and SharedRemoteInterface assemblies as references. Also, add SharedInterface, System.Runtime.Remoting, System.Runtime.Remoting.Channels, and the System.Runtime.Remoting.Channels.HTTP units to the uses clause. We need to create a HttpChannel again in order to communicate with the RemoteServer. This time, however, we do not have to specify a portnumber for the channel, since this is part of the RemoteServer information. This, as well as the first call to GetEMPLOYEE can be done in the OnLoad event of the WinForm (note that Channel and ObjManager are declared as private fields of the WinForm):

  type
    TWinForm = class(System.Windows.Forms.Form)
      ...
    private
      { Private Declarations }
      Channel: HttpChannel;
      ObjManager: IRemoteObjectManager;
    end;

  ...

  const
    RemoteServer = 'http://localhost:4242/';
    ServerResource = 'RemoteObjectManager.soap';

  procedure TWinForm.TWinForm_Load(sender: System.Object; e: System.EventArgs);
  begin
    Channel := HTTPChannel.Create; // no PortNumber needed
    ChannelServices.RegisterChannel(Channel);
    try
      ObjManager := Activator.GetObject(typeof(IRemoteObjectManager), RemoteServer + ServerResource);
    except
      MessageBox.Show('Could not get reference to IRemoteObjectManager.')
    end;
    // now we can use the ObjManager
    DataGrid1.DataSource := ObjManager.GetEMPLOYEE;
    DataGrid1.DataMember := 'EMPLOYEE'
  end;

Applying Updates
The next step is not difficult: drop two button, call them btnUndo and btnUpdate, and implement their Click event handlers as follows:

  procedure TWinForm.btnUndo_Click(sender: System.Object; e: System.EventArgs);
  begin
    (DataGrid1.DataSource as DataSet).RejectChanges
  end;

  procedure TWinForm.btnUpdate_Click(sender: System.Object; e: System.EventArgs);
  var
    Changes: DataSet;
  begin
    try
      Changes := (DataGrid1.DataSource as DataSet).GetChanges;
      if ObjManager.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;

Obviously, the .NET Remoting server must be running before the client can connect to it.

In this section, we've examined how we can build simple distributed applications with Delphi 8 for .NET, using .NET Remoting as technique to allow clients to obtain a reference to a remote object. The example implemented a GetEMPLOYEE and SetEMPLOYEE method, passing a .NET DataSet from server to client applications.

Summary
In this session, two techniques were demonstrated to build multi-tier database applications: ASP.NET Web Services and .NET Remoting using Delphi 8 for .NET as the development environment.
In both cases, the server-side application returned DataSets to clients, and received DiffGrams from clients to update the remote database. The only difference was the fact that ASP.NET Web Services require a web server (like IIS) and ASP.NET, while .NET Remoting can rely on built-in capabilities. The latter supports different message formats and transport protocols which were also covered, although the main focus was on the techniques to built one server that connects to several thin-client applications (i.e. clients that do not know or care which database they are connected to).