Introduction

If you've used Borland® Delphi, C++Builder or Kylix, you'll know that to connect to databases such as IBM® DB2® Universal Database^TM (UDB), it's most convenient to use some components from the Borland Visual Component Library (VCL). With Borland C#Builder^TM, everything changes: instead of the Borland VCL, you'll need to use the Microsoft® .NET Framework, and in particular ADO.NET, rather than the Borland Database Engine or dbExpress.

Bob Swart's two articles on connecting to DB2 UDB data illustrate just how easy C#Builder makes it to create a simple database application that uses the Borland Data Provider (BDP) and ADO.NET. (The first article shows how to connect the BDP components visually to create a simple user interface. The second article goes into more detail on the DataSet class.)

In this article, we'll look at just what all those ADO.NET classes and interfaces can do for us. We'll use C#Builder Enterprise, DB2 UDB, and the IBM Managed Data Provider for the Microsoft .NET Framework.

Note that we'll use the DB2 managed code provider for some of the samples. To install this, you'll need the Windows client of DB2 UDB 8.1 FixPak 3 or better, which installs the Microsoft .NET Framework DB2 UDB driver. If you're using C#Builder, you'll need to add the IBM.Data.DB2 assembly-use Project | Add Reference, then select Browse... and navigate to the IBM DB2 .NET Framework 1.1 assembly. This is installed by default in C:\Program Files\IBM\SQLLIB\BIN\netf11. Where necessary, I'll use the regular DB2 UDB sample database.

Back to top

Using ADO.NET: visual or non-visual?

One of the major challenges that users of the VCL immediately encounter with the .NET Framework is that there's no equivalent to the data module. This means that visually, with the user interface, there is no easy way to refer to an object on one form from another. There are plenty of alternative options, but all of them depend on code. In this article, I've included code samples to give you pointers on what to do, as thankfully, like the VCL, one of the impressive aspects of ADO.NET is that it's very easy to create database access logic through program code. The code samples will all use C#. Once you understand the program code (and in particular the subtleties of interfaces and classes), it's then quite simple to write 'wrapper' classes around the database access logic. If this is done in the right way, the result is 'business logic' classes that handle all the database access, but which can be used throughout the application. A good example of this is in the IBuySpy application that we discussed in a previous article.

Back to top

A quick overview

Here's a simplified architectural view of ADO.NET as compared with the BDE.

If you're accustomed to the VCL components, then you'll find that the principles behind ADO.NET are quite straightforward. ADO.NET might seem very different, but most features are there-they're just in a different place.

Back to top

Connecting with DBConnection

The first class returns a database connection object. This is used by all of the other components as a handle to refer to the connection.

For example, in the C# language, for IBM DB2 using the DB2 managed provider:

 
DB2Connection myConn = new  
IBM.Data.DB2.DB2Connection("DATABASE = SAMPLE;  
UID=<username>; PWD=<password>;"); 

Once the handle has been assigned, it's possible to open it, use it as the Connection property of queries or stored procedures, and then close it. For example:

 
DB2Connection myConn = new 
IBM.Data.DB2.DB2Connection("DATABASE = SAMPLE; 
UID=<username>; PWD=<password>;"); 
string myInsertQuery = "INSERT INTO STAFF (ID,  
NAME) Values(1002,"Jane Doe")"; 
DB2Command myComm = new DB2Command(myInsertQuery); 
myComm.Connection = myConn; 
myConn.Open(); 
myComm.ExecuteNonQuery(); 
myConn.Close(); 

We'll cover the DB2Command object later.

Immediately, we can see an important distinction between ADO.NET and the VCL. To connect to a database with (say) the BDE, we'd use a TDatabase component, no matter what the underlying database might happen to be. With ADO.NET, we use a specialized variant of the data access component for each database server.

Back to top

How the different data providers are implemented

The way that database access classes are specialized for each type of database server is one of the major features of the design of ADO.NET. While the driver classes are different, all have the same basic capabilities and can often be used interchangeably. The trick is accomplished through interfaces that define the database-specific classes.

An application that uses the IBM.Data.DB2 managed provider to access data on a DB2 server will use a DB2Connection component, as in the example above. A similar component, SqlConnection, is available for SQL Server:

 
SqlConnection myConn  
 = new  
System.Data.SqlClient.SqlConnection("Persist  
Security Info=False;Integrated  
Security=SSPI;database=northwind;server=mySQLServer"); 

For Oracle, there's an OracleConnection component:

 
OracleConnection myConn 
 = new  
System.Data.OracleClient.OracleConnection("Data  
Source=Oracle8i;Integrated Security=yes"); 

Each of these components has a basic set of properties, methods, and events that is the same: in other words, they share the same interface. For database connections the interface is IDbConnection, documented in the Microsoft .NET Framework help. This interface defines the minimum level of capabilities; in practice, each component will most likely have additional properties, methods, and events to support special features of that particular database or driver.

For example, the DB2Connection component includes an additional method that can be used to force connection pool resources to be released. By default, ADO.NET providers can share and reuse connections, which saves resources but can also mean that connections are left open for possible reuse. This method gives you additional control that isn't available through drivers such as the SQL Server managed provider.

While database-specific components give great flexibility, they don't result in portable code. This might be a nuisance should you want to switch from one database to another. With that in mind, Borland has created the Borland Data Provider (BDP).

The BDP is a regular managed .NET data provider, with a difference. Instead of working with just one kind of database, it can work with a range of different servers. The actual driver is determined through the connection string passed to the BdpConnection object when it is created (or the ConnectionString property).

So to connect to SQL Server using the BDP, you might use:

 
BdpConnection myConn = new  
Borland.Data.Provider.BdpConnection("assembly=Borl 
and.Data.Mssql,Version=1.1.0.0, 
Culture=neutral,PublicKeyToken=91d62ebb5b0d1b1b;ve 
ndorclient=sqloledb.dll; 
osauthentication=False;database=<database>;usernam 
e=<user>;hostname=<host>; 
password=<password>;provider=MSSQL"); 

Note that the host name is embedded in the connection string for this particular driver. That isn't necessary for the DB2 driver, where you'd use:

 
BdpConnection myConn = new  
Borland.Data.Provider.BdpConnection("assembly=Borl 
and.Data.Db2,Version=1.1.0.0, 
Culture=neutral,PublicKeyToken=91d62ebb5b0d1b1b;ve 
ndorclient=db2cli.dll; 
database=<database>;username=<user>; 
password=<password>;provider=DB2"); 

While the connection syntax is slightly different, the object returned is the same. That means that all the other code is unchanged. You can even store this string in a resource file and refer to it at runtime.

Borland provides a Connections Editor that can be used by the forms designer to create the connection strings. I can create a second project in another instance of C#Builder, place a bdpConnection object on a Windows Form, and use the clipboard to copy the connection string into a resource file for the application I'm writing.

Internally, in most cases, the BDP calls the same native client libraries as the server-specific components, so it's just as fast.

The DbConnection classes correspond roughly to the BDE TDatabase or dbExpress TSQLConnection components. Like these components, DbConnection is the point where transactions are managed. The approach to transaction management is quite different: the BeginTransaction method for the DbConnection classes returns a transaction object, which can be used as a property for later SQL commands.

Back to top

Executing queries with DbCommand

Once the connection is open, ADO.NET includes components to run queries directly against the database. Like the DbConnection classes, DbCommand is an abstract interface that has server-specific implementations. We've seen this example already:

 
DB2Connection myConn = new  
IBM.Data.DB2.DB2Connection("DATABASE = SAMPLE"); 
string myInsertQuery = "INSERT INTO STAFF (ID,  
NAME) Values(1002,"Jane Doe")"; 
DB2Command myComm = new DB2Command(myInsertQuery); 
myComm.Connection = myConn; 
myConn.Open(); 
myComm.ExecuteNonQuery(); 
myConn.Close(); 

As we can see, the DB2Command—the DB2-specific flavor of DbCommand—can be used to run a query directly on a DB2 server. There's a BDP equivalent too, so

 
BdpConnection myConn = new  
Borland.Data.Provider.BdpConnection(connectionstring); 
string myInsertQuery = "INSERT INTO STAFF (ID,  
NAME) Values(1002,"Jane Doe")"; 
BdpCommand myComm = new BdpCommand(myInsertQuery); 
myComm.Connection = myConn; 
myConn.Open(); 
myComm.ExecuteNonQuery(); 
myConn.Close(); 

would run on any server for which there's a BDP driver.

An SQL INSERT command doesn't return a result but can take parameters. ADO.NET can include parameters using the DbDataParameter classes through the Parameter property. The parameter classes are, yet again, implemented on a server-specific basis.

Here is an example of parameters, which calls a DB2 stored procedure to return the full name from a database, given an email address. First, the stored procedure:

 
CREATE PROCEDURE Job( 
  IN v_Name VARCHAR(50), 
  OUT v_Job VARCHAR(50)) 
DYNAMIC RESULT SETS 1  
LANGUAGE SQL  
BEGIN  
  SELECT Job INTO v_Job FROM Staff WHERE Name = v_Name 
  FETCH FIRST 1 ROWS ONLY; 
END 

Following the C# code fragment. We create the connection, create a command, then create two BdpParameter objects and add them to the command object.

 
public String GetJob(String Name)  
{ 
	BdpConnection myConn = new BdpConnection(<connection string>); 
	BdpCommand myCommand = new BdpCommand("Job", myConn); 
 
	// This is a stored procedure - mark the command accordingly 
	myCommand.CommandType = CommandType.StoredProcedure; 
 
	// Add the input parameter 
	BdpParameter paramName = new BdpParameter("V_NAME", BdpType.String, 50); 
	paramName.Direction = ParameterDirection.Input; 
	paramName.Precision = 50; 
	myCommand.Parameters.Add(paramName); 
 
	// The output parameter 
	BdpParameter paramJob = new BdpParameter("V_JOB", BdpType.String, 50); 
	paramJob.Direction = ParameterDirection.Output; 
	paramJob.Precision = 50; 
	myCommand.Parameters.Add(paramJob); 
 
	paramName.Value = Name; 
 
	myConn.Open(); 
	myCommand.ExecuteNonQuery(); 
	myConn.Close(); 
 
	// The return value is passed as an Object. We need to cast as a string 
	return (string)paramJob.Value; 
	}

Here are a couple of important notes. First, you should declare the parameters in the same order that they're declared in the stored procedure. Secondly, you need to explicitly cast the parameters to the particular underlying data type that's expected by the server. Here are examples of some of the different data types supported by the DB2 managed provider and the BDP:

You'll also need to set the Size and Precision properties for some of these data types as appropriate.

This approach is quite different from the BDE and dbExpress, which uses the TQuery and TSQLQuery components to call stored procedures or other data-definition queries, which don't return a tabular result set. These should be replaced by DbCommand objects. However, as we'll see, SELECT queries will typically need to use either a DataReader or a DataSet if the data is to be updated.

Back to top

Accessing DbCommand data directly with a DataReader

The examples I just listed for the DbCommand classes didn't return a data set. DbCommand can handle this, but you'll need to use the DataReader component to get to the data.

At first glance, DataReader seems somewhat limited. You can only step forward, reading one record at a time, and there's no caching of data. However, these limitations are more than outweighed by its main feature, which is speed-the DataReader is extremely fast.

It's surprising how often a program will use a SELECT query that's suitable for a DataReader. An example is to return a chunk of records from a table ready to display on a Web page:

 
public String GetStaff(string Job) 
{ 
 
BdpConnection myConn = new  
BdpConnection(<connection string>); 
 
BdpCommand myCommand = new BdpCommand("SELECT  
NAME, YEARS FROM STAFF WHERE JOB=? FETCH FIRST 10  
ROWS ONLY", myConn); 
 
BdpParameter paramJob = new BdpParameter("JOB",  
BdpType.String, 50); 
paramJob.Direction = ParameterDirection.Input; 
paramJob.Precision = 50; 
myCommand.Parameters.Add(paramJob); 
paramJob.Value = Job; 
 
myConn.Open(); 
BdpDataReader readerStaff =  
myCommand.ExecuteReader(); 
 
string staffList=""; 
 
while (readerStaff.Read()) { 
	staffList += readerStaff.GetString(0)+"  
("+readerStaff.GetInt16(1)+") <br />"; 
} 
 
readerStaff.Close(); 
myConn.Close(); 
 
return staffList; 
 
}

This example uses the BDP. The syntax for the DB2 managed provider is very similar: just change the "Bdp..." components to be "Db2..." components and the BdpType.String to a DB2Type.VarChar.

This example shows how to use parameters in the SELECT query-it's a very similar process to that used for stored procedures. You'll need to add the Parameter objects to the Command.Parameters property in the same order as the "?" placeholders are defined in the query. Note that we're explicitly telling the system what kinds of data to expect, making the code even faster.

There's no equivalent to IDataReader with the BDE: while there is a UniDirectional property for TQuery, this only affects the BDE, not the underlying query on the database, so there's always a degree of buffering in place.

The closest equivalent in dbExpress is the TSQLQuery component, which also returns a forward-only result set. However, as we've seen, retrieving result sets is easier with ADO.NET.

Back to top

DbDataAdapters and DataSets

What if you want to cache data and browse through it interactively? That's what the DataSet object can do. This powerful object manages a local cache of data that's been retrieved from a database with a DbDataAdapter object. That cache can then be used as the data source for other visual .NET Framework components such as the DataGrid or TextBox.

As it caches data, the DataSet object resembles the VCL TClientDataSet. However, it is much more powerful: for instance, it can store data from multiple tables. That means that when we set it up, we need to tell it what tables to expect and what columns will go into it. For example:

 
// Create a new dataset 
DataSet StaffDataSet = new DataSet("DSStaff"); 
 
// Set up the columns for the table for the dataset 
DataColumn IDCol = new DataColumn("ID", typeof(short)); 
DataColumn NameCol = new DataColumn("NAME"); 
DataColumn YearsCol = new DataColumn("YEARS", typeof(short)); 
 
// Set up the table for the dataset and add the columns 
DataTable StaffTable = new DataTable("TabStaff"); 
StaffTable.Columns.AddRange(new DataColumn[] {IDCol, NameCol, YearsCol}); 
 
// Add the table to the dataset 
StaffDataSet.Tables.AddRange(new DataTable[] {StaffTable}); 

There are plenty of parameters you can use to fine tune the behavior of each of these objects-see the online help for a summary. I'm using the constructors that reduce the code needed to a minimum. Note that the DataSet component comes in just one flavor. No matter which managed provider you choose, the DataSet component is the same.

Our code configures the DataSet but doesn't retrieve the data. To do that, we need a DbDataAdapter. Not only can this component retrieve data from the database, it can also put changed data back from the DataSet.

Because it has also to map the different data types to the .NET Framework data types, the DbDataAdapter object comes in different flavors depending on the database server that's being used. To use the DbDataAdapter component, you'll typically want to set properties that define the queries used to retrieve, update, insert, and delete records. When you want the data to be updated, these queries will be called.

Each of those queries is stored in a DbCommand object, so if you want to handle updates, you'll need to create four queries, each of which is parameterized-quite a handful.

Things are simpler if you just want to read data, however. Here's a code snippet showing how to use the BDP to do this:

 
BdpConnection myConn = new  
BdpConnection(ConnString); 
 
// Create a command object to retrieve data 
BdpCommand SelComm = new BdpCommand("SELECT ID,  
NAME, YEARS FROM STAFF", myConn); 
 
// Create the data adapter 
BdpDataAdapter StaffDA = new BdpDataAdapter(); 
// Point the data adapter to the command object to  
get data 
StaffDA.SelectCommand = SelComm; 

First, I set up the connection, and then I set up a BdpCommand object to retrieve the data. The DataAdapter is used to link this BdpCommand to the DataTable in the DataSet. As you might expect by now, the process for setting up the DB2DataAdapter is similar: replace the BDP components with their DB2 equivalents.

With the DbDataAdapter created, you'll then need to fill the DataSet. Here's where there are some differences between the BdpDataAdapter and the other flavors. For the DB2DataAdapter and other server-specific drivers, you need to tell the DbDataAdapter to fill the DataSet in code yourself.

For example, the following might be appropriate to add to the Open method of a form that uses the DB2 managed provider:

 
StaffDA.Fill(StaffDataSet); 
this.dataGrid1.DataMember = "TabStaff"; 
this.dataGrid1.DataSource = StaffDataSet; 

The BDP makes life a little easier. An extra property, active, can be used to tell the DataAdapter to automatically fill a DataTable in a DataSet on request. So instead of setting properties in an Open method, the following can be added to code that runs when the connections are first set up:

 
// Point the data adapter to the DataSet and the table within it 
StaffDA.DataSet = StaffDataSet; 
StaffDA.DataTable = StaffTable; 
// Tell the DataAdapter to automatically populate the table 
StaffDA.Active = true; 

A great side-effect of this is that the DataSet can be populated at design time, showing live data! Although this can only happen when the BdpDataAdapter is placed on the same form as the DataSet and the associated controls, it's a neat trick. In practice you may find that for larger systems, you'll use a central object to encapsulate the DataSet so you can share it among several forms.

If you're coming from the BDE, you can consider the DataSet component to be a little like a TClientDataSet. There are some superficial similarities: like the TClientDataSet, the DataSet caches data locally on the workstation, which can be a very efficient way of working with data as data is read and updated in large chunks. And like the BDE when it returns a read-only result set, the DataAdapter gives the developer SQL queries to update the source database.

However, under the surface the TClientDataSet is very different. Unlike the BDE TClientDataSet, a DataSet can cache multiple tables. The DataSet manages data internally as a relational view into an XML schema, so as a result it doesn't have a concept of a 'cursor' or current record. Instead, a current record pointer is managed by the data-binding layer.

Another difference is the technique used to handle updates. TClientDataSet keeps a log of changes to the table, which can be a very convenient way of handling multiple levels of 'undo'. By contrast, when updated with data-bound controls DataSet stores the updates on a row-by-row basis, only retaining original versions of updated and deleted rows.

Back to top

Moving from the BDE to ADO.NET and BDP

Here's a quick summary of the components that you'll want to look at if you're used to the BDE. This isn't exhaustive, and is naturally simplified, but it should help you see where to start.

Back to top

Conclusion

ADO.NET is a rich, sophisticated database access library. While ADO.NET is quite different from the Borland Database Engine, the streamlined features of the Borland Data Provider make it a little easier to use. The BDP gives .NET developers who need to access DB2 an efficient and scalable way to create Windows client and Web applications.

Back to top

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.

About the author

Jeremy McGee started writing applications using BASIC on the Commodore PET. He fondly remembers typesetting a book-length publication on an early Apple Mac with a refrigerator-sized laser printer attached. Since then he's variously been a DEC VAX sysadmin, a technical support engineer for Borland Paradox, and was part of the team that launched Borland Delphi in Europe. Jeremy now runs a consulting firm in Southampton, UK.

Comments (Undergoing maintenance)

Back to top

Trademarks  |  My developerWorks terms and conditions

Table of contents

• Introduction
• Using ADO.NET: visual or non-visual?
• A quick overview
• Connecting with DBConnection
• How the different data providers are implemented
• Executing queries with DbCommand
• Accessing DbCommand data directly with a DataReader
• DbDataAdapters and DataSets
• Moving from the BDE to ADO.NET and BDP
• Conclusion
• Disclaimer
• About the author
• Comments

Next steps from IBM

• Try: DB2 Enterprise
• Demo: DB2 serving ATOM feed
• Webcast: A smarter way to successful SOA - Information as a trusted strategic asset
• Briefing: Information on Demand
• Buy: DB2 Enterprise

My developerWorks community

Dig deeper into Information Management on developerWorks

DB2 pureScale

See how true transparent scaling architecture can be achieved on distributed platforms

Special offers