Building Distributed Database Applications in .NET Using BDP and DB2 UDB

This article uses the Borland Data Provider (BDP) for .NET that ships with Borland C#Builder for data access from IBM DB2 Universal Database (UDB). It introduces the basics of .NET remoting and shows how to remote data from one tier to another; and it shows how to convert the raw data into meaningful information, either by the clients updating the presented data or by running the data through some business rules on various tiers of the distributed application, using ADO.NET Dataset for data manipulation on the client.

Ramesh Theivendran, Architect, Borland

Ramesh Theivendran has been a member of the Borland RAD database connectivity R&D team since 1995. Currently, he leads the database connectivity efforts at Borland in their .NET products group and serves as an architect for dbExpress and Borland Data Provider (BDP) for .NET.



25 September 2003

Important: Read the disclaimer before reading this article.

MBR object activation

The remoting infrastructure allows both server- and client-activated objects. Server-activated objects (SAO) are created only when the client first calls a method on the proxy object. SAO can be configured to be either Singleton or SingleCall.

With Singleton, there is only one instance serving the requests of all clients in a multithreaded fashion. Singleton objects have an associated lifetime, so clients may not get the same instance of the remote object all the time. The lifetime of a remote object can be renewed using the lifetime lease system (System.Runtime.Remoting.Lifetime) services. Use Singleton objects when you want to maintain state and share data between many clients.

SingleCall objects, as the name implies, serve only one incoming client request. A new instance of the remote object will be created and destroyed for each request. So, their lifetime is not renewable and they can't maintain state. SingleCall objects are the best choice in a load-balancing system.

Client-activated objects (CAO) are created on the server, upon request from a client, to create a new object. After the server creates an instance, an ObjRef is returned back to the client and a proxy object is created on the client. All the client's method calls are now marshalled by the proxy and passed to the remote object. Unlike SAO, CAO can be created using a nondefault constructor and can store state information between method calls for a specific client. Like SAO, they use the same lifetime lease system to determine how long they should reside in memory.


MBR lifetime

The lifetime of an MBR object is controlled by a lease manager and one or more sponsors. Sponsors are objects that can renew lease for a particular object by registering with a lease manager. When an MBR object leaves an application domain, the lease manager for that domain is responsible for creating a lifetime lease for the object. The lease manager periodically checks for the lease expiry. If a lease has expired, the sponsors for that object will be contacted to check if they want to renew the lease. If no renewal happens, the lease is removed and the object will be garbage-collected.

You could customize the lease by overriding MarshalByRefObject.InitializeLifetimeService in your MBR object. This method is called when the MBR object is activated for the first time. A null lease returned will give an infinite lifetime.

Using lease for lifetime management is a better approach than reference counting and constantly pinging the client (like in COM) because there is less network traffic. In addition, if a client goes offline unexpectedly, it might not have decremented the reference count, leaving the server to run forever.


.NET data access and manipulation

So far, we have seen the basics of .NET remoting. For data-access from DB2® and presenting the data to the client, we will use the ADO.NET components. ADO.NET has two core components: .NET Data Provider and Dataset. The data provider defines a set of interfaces for connecting to a data source, executing a SQL or stored procedure and retrieving a uni-directional resultset. It also provides and resolves data to and from a DataSet. DataSet is an in-memory representation of relational data with tighter XML and XSD integration.

DataSet consists of a DataTableCollection and a DataRelationCollection. Each DataTable has a collection of DataColumn and DataRow that represents tabular data. Both DataSet and DataTable are remotable types because they inherit from MarshalByValueComponent and implement ISerializable. Each DataRelation represents the relation between two DataTables in the DataSet. Data integrity constraints such as unique, primary key, and foreign key can also be enforced in a DataSet. Apart from being a cache for relational data, DataSet also provides functionality for sorting, searching, and filtering, and also keeps track of changes to the data.

BDP is a .NET data provider implementation from Borland. BdpConnection, BdpCommand, and BdpDataReader are the core components for data access. The BdpDataAdapter acts as a conduit between DataSet and the data source for providing and resolving data. BDP comes with a rich set of component designers for ease of use. BDP lets you provide live data at design-time, create master-detail relations, and hook up any ListControl descendents for design-time data.

If the underlying database objects are available and the application uses standard ANSI SQL, then by using configuration files you can change BdpConnnection.ConnectionString to run the application against different BDP supported RDBMS without changing any code. Although this may not be the case in a real-world application, this will be a good feature for prototyping.

For third-party integration, BDP also exposes a subset of interfaces that you can implement for your own database and get runtime, design-time, and tools integration into the C#Builder IDE.

For more details on using BDP components, refer to other articles in the DB2 UDB and Borland Zone.


Building the application

Overview

To demonstrate remoting data from DB2 using BDP, I am going to use a simple address book application. The task here is to query for employees in a particular location and move them to a new office address, and then fill the vacant positions in the new office with new employees and add them to the address book. To keep things simple, all the data needed is stored in a single ADDRESSBOOK table. Using the DB2 command line processor, you can execute the following SQL to create the table in DB2 and populate it with a few records.

CREATE TABLE ADDRESSBOOK (EMPID 
INTEGER NOT NULL PRIMARY KEY, 
STREET VARCHAR(25), CITY 
VARCHAR(25), STATE CHAR(2), ZIP 
CHAR(5) ) 
 
INSERT INTO ADDRESSBOOK VALUES 
(1,'100 Borland Way','Scotts 
Valley','CA','95066') 
 
INSERT INTO ADDRESSBOOK VALUES 
(2,'100 Borland Way','Scotts 
Valley','CA','95066')

Representing the remotable types

For the example in this article (see Figure 1), I have a Singleton SAO, RemoteDataService that returns a CAO RemoteDataProvider on a client's request. All the clients connect to the same SAO and get their own instance of the CAO. The CAOs maintain state for a particular client.

Figure 1. Simple data remoting service

To share the remotable types between the client and the server, I have them represented as the interfaces IRemoteDataService and IRemoteDataProvider. Now you can create an assembly and use it on both the client and the server.

Instead of using interfaces to represent remotable types, you can also use abstract classes. The abstract classes will let you pass remote objects from one server to another in a multiserver scenario. One disadvantage of using either interfaces or abstract classes is that you will not be able to use configuration files. A different approach will be using SoapSuds.exe to extract the metadata from a server and generate a new assembly or source files. The clients can then use the metadata-only assembly or the source files to learn about the remotable types.

IRemoteDataService returns a generic data provider interface, IRemoteDataProvider, which has the properties CommandText and Parameters to make a parameterized SQL request. Usually, all this is done on the remote application server, but to demonstrate the flexibility of the system I have the client pass the SQL command and the associated parameters. The GetData() method returns a DataSet that represents the result of executing the parameterized SQL on the server. The SaveData() method passes a modified DataSet to the server. The server resolves the DataSet changes back to the database.

In the event of an exception, GetError() is called to receive the ErrorInfo instance from the server. .NET exceptions are marshaled by value and will be automatically available to the clients. But if you want customized exceptions, you can write a custom exception class by implementing ISerializable and providing a constructor that takes SerializationInfo and StreamingContext as parameters. To demonstrate marshal-by-value type, I have an ErrorInfo class that simply maps server exception to a meaningful error message and returns an MBV object. The ErrorInfo class is tagged with a Serializable attribute.

RemoteService.cs

namespace RemotingService 
{ 
   public interface IRemoteDataService 
   { 
      IRemoteDataProvider GetDataProvider(); 
   } 
 
   public interface IRemoteDataProvider 
   { 
      string CommandText { get; set; } 
      BdpParameterCollection Parameters { get; } 
      DataSet GetData(); 
      Int32 SaveData(DataSet ds); 
      ErrorInfo GetError(); 
   } 
 
   [Serializable] 
   public class ErrorInfo 
   { 
      public Int32  Code; 
      public String Message; 
      public ErrorInfo() {} 
   } 
}

Implementing the remote objects

The implementation of the remotable interfaces, RemoteDataService and RemoteDataProvider, is done on the server. RemoteDataService inherits from MarshalByRefObject and implements IRemoteDataService. The GetDataProvider() method creates and returns a new instance of RemoteDataProvider.

RemoteDataService is registered as a Singleton object on the server, so when the first client calls GetDataProvider() a new instance of the RemoteDataService is created on the server and only one instance of RemoteDataService serves all the client requests.

RemoteDataProvider also inherits from MarshalByRefObject and implements IRemoteDataProvider. Each client gets a new instance of a RemoteDataProvider when they call GetDataProvider. Clients can then set the CommandText and the Parameters property and call GetData(). The GetData() implementation creates a new BdpConnection and specifies a ConnectionString to connect to DB2. A new instance of BdpDataAdapter is created and the Fill() method on the data adapter executes the command specified in the CommandText and populates a DataTable in the DataSet . A single DataTable, Table1, is created and returned to the client.

The SaveData() method takes the client changes to the Dataset and applies them back to DB2 by calling BdpDataAdapter's AutoUpdate() method. AutoUpdate() in turn uses a BdpCommandBuilder to generate update, delete and insert SQL for persisting the client changes back to the database. For providing and resolving data from DB2 instead of using SQL, you could also use stored procedures. In that case, the BdpDataAdapter SelectCommand, DeleteCommand, UpdateCommand and InsertCommand should be explicitly specified and the Update() method takes care of resolving the changes.

Warning: Although using AutoUpdate() makes things look simpler, the current implementation doesn't generate optimal SQL all the time. It also does not handle master-detail updates. So, until these issues are addressed, be aware that you achieved simplicity at the cost of performance.

Following is a code snippet of the server implementation; for a full listing of the server refer to the source code.

RemoteServer.cs

namespace RemotingServer 
{ 
   public class RemoteDataService : 
MarshalByRefObject,  IRemoteDataService 
   { 
      public IRemoteDataProvider GetDataProvider() 
      { 
         return new RemoteDataProvider(); 
      } 
      public IRemoteDataProvider 
GetDataProvider(String ClientID) 
      { 
         return new RemoteDataProvider(ClientID); 
      } 
   } 
 
   public class RemoteDataProvider : 
MarshalByRefObject, IRemoteDataProvider 
   { 
      private String m_clientID = ""; 
      private String m_commText = ""; 
      private BdpParameterCollection m_Parameters 
= new BdpParameterCollection(); 
      private String m_connString = 
@"Provider=DB2;Assembly=Borland.Data.Db2,Version=11.0.0, 
Culture=neutral,PublicKeyToken=91d62ebb5b0d1b1b; 
Database=Sampledb;UserName=db2user;Password=mypass"; 
      ErrorInfo m_Err = new ErrorInfo(); 
 
      public RemoteDataProvider() 
      { 
      } 
      public RemoteDataProvider(String ClientID) 
      { 
         m_clientID = ClientID; 
      } 
 
      public string CommandText 
      { 
         get   {  return m_commText; } 
         set   {  m_commText = value; } 
      } 
 
      public BdpParameterCollection Parameters 
      { 
         get { return m_Parameters; } 
      } 
 
      public DataSet GetData() 
      { 
         DataSet ds = null; 
         try 
         { 
            BdpConnection Conn = new BdpConnection(m_connString); 
            BdpDataAdapter adapter = new BdpDataAdapter(m_commText, Conn); 
 
            if (Parameters != null & Parameters.Count > 0 ) 
               adapter.SelectCommand.Parameters = Parameters; 
 
            ds = new DataSet(); 
            adapter.Fill(ds,"Table1"); 
         } 
         catch (Exception e) 
         { 
            //Map server exception to meaningful client error message 
            m_Err.Code    = -1; 
            m_Err.Message = "Failure in GetData(): " + e.Message; 
            throw e; 
         } 
         return ds; 
      } 
 
      public Int32 SaveData(DataSet ds) 
      { 
         try 
         { 
            BdpConnection Conn = new BdpConnection(m_connString); 
            BdpDataAdapter adapter = new BdpDataAdapter(m_commText, Conn); 
 
            if (Parameters != null & Parameters.Count > 0 ) 
               adapter.SelectCommand.Parameters = Parameters; 
 
            if (ds.HasChanges()) 
            { 
               adapter.AutoUpdate(ds,"Table1",BdpUpdateMode.All); 
               return 0; 
            } 
            else 
               return 1; 
         } 
         catch ( Exception e) 
         { 
            //Map server exception to meaningful client error message 
            m_Err.Code    = -1; 
            m_Err.Message = "Failure in SaveData(): " + e.Message; 
            throw e; 
         } 
      } 
 
      public ErrorInfo GetError() 
      { 
         return m_Err; 
      } 
   }

The Host

Remote objects can be hosted in a managed executable (console application or Windows Forms), or in IIS, or as a Windows Service. For the example here, you'll take the simplest approach, hosting the remote objects in a console application (see Figure 2).

Figure 2. Remoting server

A new HTTP channel is registered to listen to port 8000 and the default serialization format for HTTP will be SOAP. The class RemoteDataService is then registered as a WellKnownServiceType with a URI, and the activation model is set to Singleton.

RemoteServer.cs

  public class RunServer 
  { 
      public static void Main(string[] s) 
      { 
         try 
         { 
            HttpChannel channel = new HttpChannel(8000); 
            ChannelServices.RegisterChannel(channel); 
 
            RemotingConfiguration.RegisterWellKnownServiceType 
              (typeof(RemoteDataService), 
              "RemoteDataService.soap", 
              WellKnownObjectMode.Singleton); 
         } 
         catch (Exception e) 
         { 
            #x2026 
         } 
         Console.WriteLine("Press enter to stop RemoteDataService..."); 
         Console.ReadLine(); 
      } 
  }

The Client

Once again, the client is a console application (see Figure 3), and creates an HTTP channel and registers the same. The port is optional because one will be automatically assigned. The Activator.GetObject() creates a local proxy object that supports IRemoteDataService. The client then calls IRemoteDataService.GetDataProvider() to get an instance of RemoteDataProvider. At this point (and only for the first time), a server activation object is created on the server. Now, to get data from DB2, the client sets the CommandText and the Parameters properties and calls GetData() on the RemoteDataProvider. The DataSet returned is now updated and any new changes are applied back to DB2 by calling SaveData().

Figure 3. Remoting Client

Following is a code snippet of the client; for a full listing of the client refer to the source code.

RemoteClient.cs

public class RemotingClient 
{ 
   public static void Main(string[] s) 
   { 
      IRemoteDataService remDS = null; 
      IRemoteDataProvider remDP = null; 
 
      HttpChannel channel = new HttpChannel(); 
      ChannelServices.RegisterChannel(channel); 
 
      String ClientID = Guid.NewGuid().ToString(); 
      try 
      { 
         remDS = 
(IRemoteDataService)Activator.GetObject(typeof(IRemoteDataService), 
"http://localhost:8000/RemoteDataService.soap"); 
 
         if (remDS != null) 
         { 
            remDP = remDS.GetDataProvider(ClientID); 
 
Int32 id = GetID(remDP); 
            DataSet ds = GetAddress(remDP); 
            UpdateAddress(ds); 
            AddAddress(ds, id); 
 
            DataSet ds1 = ds.GetChanges(); 
            remDP.SaveData(ds1); 
         } 
      } 
      catch (Exception e) 
      { 
         Console.WriteLine(e.Message); 
         //Get mapped error 
         if (remDP != null ) 
         { 
            ErrorInfo Err = remDP.GetError(); 
            Console.WriteLine("Mapped Error = " + Err.Code +','+ Err.Message); 
         } 
      } 
   } 
}

Conclusion

The .NET Framework provides several ways to communicate with distributed objects such as .NET Remoting, ASP.NET, and Web Services. To choose the right interprocess communication, you need to analyze various factors like speed, extensibility, interoperability, scalabilty, and security needs.

If speed, flexibility, and extensibility are important, then .NET Remoting is the best choice. When you are remoting DataSets or other potentially large objects, TCPChannel or HTTPChannel with BinaryFormatter will give you the optimal performance when compared to Web Services. On the other hand, if you need interoperability between heterogeneous clients and different operating systems, then Web Services are a better choice. Generally, Web Services are stateless, but with .NET Remoting you can use both stateless (SingleCall) and stateful (Singleton) objects for building better scalable applications.

A well-designed distributed application gives you scalability, load balancing, fault tolerance and, ultimately, good performance. By separating a monolithic application into logical tiers such as presentation, business rules, and persistence, each tier can be managed, modified, and enhanced independently.


Disclaimer

This article contains sample code. IBM® grants you ("Licensee") a non-exclusive, royalty free, license to use this sample code. However, the sample code is provided as-is and without any warranties, whether EXPRESS OR IMPLIED, INCLUDING ANY IMPLIED WARRANTY OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE OR NON-INFRINGEMENT. IBM AND ITS LICENSORS SHALL NOT BE LIABLE FOR ANY DAMAGES SUFFERED BY LICENSEE THAT RESULT FROM YOUR USE OF THE SOFTWARE. IN NO EVENT WILL IBM OR ITS LICENSORS BE LIABLE FOR ANY LOST REVENUE, PROFIT OR DATA, OR FOR DIRECT, INDIRECT, SPECIAL, CONSEQUENTIAL, INCIDENTAL OR PUNITIVE DAMAGES, HOWEVER CAUSED AND REGARDLESS OF THE THEORY OF LIABILITY, ARISING OUT OF THE USE OF OR INABILITY TO USE SOFTWARE, EVEN IF IBM HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES.

IBM, DB2, and DB2 Universal Database are trademarks or registered trademarks of IBM Corporation in the United States, other countries, or both.

Borland and C#Builder are trademarks or registered trademarks of Borland Corporation in the United States, other countries, or both.

Windows and Windows NT are registered trademarks of Microsoft Corporation in the United States, other countries, or both.

Other company, product, and service names may be trademarks or service marks of others.

IBM copyright and trademark information


Download

DescriptionNameSize
Code samplesource.zip  ( HTTP | FTP )5KB

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=13815
ArticleTitle=Building Distributed Database Applications in .NET Using BDP and DB2 UDB
publish-date=09252003