Migrating From Microsoft SQL Server to IBM DB2 UDB Using Borland C#Builder

Jeremy McGee, Director, Consulting firm in Southampton, UK

Jeremy McGee was one of the first to create a networked application using dBASE III Plus in the early days of the PC. Since then he has worked with Paradox, Delphi, and Visual Studio, and is director of a consulting firm in Southampton, UK.

Summary: This article outlines what you'll need to do to change an application written in C# for Microsoft Visual Studio .NET and Microsoft SQL Server to work with Borland C#Builder and IBM DB2 Universal Database (UDB). It uses the Microsoft IBuySpy sample application. This ASP.NET application shows all the basic steps that you'll need to follow to migrate a medium-sized system to DB2 UDB and C#Builder.

Tag this!

Date: 31 Jul 2003
Level: Introductory
Activity: 895 views

Introduction

This article outlines what you'll need to do to change an application written in C# for Microsoft® Visual Studio .NET and Microsoft SQL Server to work with Borland® C#Builder^TM and IBM® DB2® Universal Database^TM (DB2 UDB). I'll use the Microsoft IBuySpy sample application, which is a free sample C# system available from www.asp.net. This ASP.NET application shows all the basic steps that you'll need to follow to migrate a medium-sized system to DB2 UDB and C#Builder.

This article is based on the IBuySpy Conversion Tutorial for use with Microsoft Visual Studio .NET. I've updated the tutorial to work with the latest version of IBuySpy, and there are details on how to make the program work with C#Builder and the Borland Data Provider.

IBuySpy is itself a great example of how to program a medium-sized database application using C#. It makes use of stored procedures and neatly separates user interface logic from business logic.

What you'll need

You'll need a copy of the IBuySpy application from www.asp.net. You also need to get a copy of the IBuySpy Conversion Tutorial.

Naturally, you'll also need a copy of DB2 UDB (Personal Developer's Edition includes the application development client, which includes the .NET Framework libraries), and the DB2 Migration Toolkit. To edit and run the application, you'll need Borland C#Builder Enterprise.

What you'll do

The first step will be to create the IBuySpy database. IBuySpy uses stored procedures, so the DB2 Migration Toolkit will help us translate them from SQL Server.
The second step will be to change the C# application to use the Borland Data Provider.

Migrating scripts from SQL Server to DB2 UDB

After you've downloaded IBuySpy, run the installer. This unpacks the source, and then attempts to set up a SQL Server database (see Figure 1). If you have SQL Server or MSDE installed, you can complete the installer and see what the application is supposed to do - otherwise, abort the install at this point.

Figure 1. The final step when installing IBuySpy is to configure a SQL Server database. This article discusses how to manually create a DB2 UDB database.

Figure 1. The final step when installing IBuySpy is to configure a SQL Server database. This article discusses how to manually create a DB2 database.

Next, using the DB2 Control Center, create a database called STORE and use the English-language defaults (see Figure 2). To do this, open the Control Center, expand the node for your DB2 UDB host, choose the appropriate instance, then right-click on the Databases node and choose Create > Database Using Wizard. (If you prefer, enter db2 create database store using codeset UTF-8 territory US from a DB2 command window.)

Figure 2. Use the DB2 Control Center to create a new database

SQL Server and DB2 UDB both use SQL, but the dialect used is quite different, especially for stored procedures. The IBuySpy application uses the StoreDB.sql script to create a SQL Server database, so you'll need to translate this.

The DB2 Migration Toolkit can make light work of this translation, but there are some extra steps to follow to make sure it does its job properly. The script includes extra commands to create the database and schedule timed jobs which you'll need to remove.

To do this from C#Builder, first open the file StoreDB.sql from the IBuySpy application source tree, and save it to a new file, StoreDB2.sql. Then remove the whole of the initial CONFIG section, from the top down to the MAKE TABLES AND SPROCS comment. Then, right at the bottom of the file, remove the final section, from the ADD SCHEDULED JOBS comment to the end of the file. This should just leave the Load Data section.

You'll also need to fix a couple of typos in the script, because the Migration Toolkit is case-sensitive, so replace the following throughout:

EMailAddress with EmailAddress
CartId with CartID
orderdetails with OrderDetails
Unitcost with UnitCost
quantity with Quantity

Load the Migration Toolkit, create a new project, and migrate the SQL Server script StoreDB2.sql to the DB2 UDB database:

Import StoreDB2.sql as a SQL file
Select the Convert tab, accept the defaults, and click Convert
The Refine tab will be displayed if there are any major issues. Check the errors and investigate if there are problems; if so, correct the script.
When the script appears to run correctly, choose the Generate Data Transfer Scripts tab and click Generate Scripts.
On the Deploy tab, enter the appropriate log-in information for your database, and click Deploy (see Figure 3).

Figure 3. The final stage of the Migration Toolkit is deployment to a DB2 UDB database

Figure 3. The final stage of the Migration Toolkit is deployment to a DB2 database

At the end of the process, you'll have created a StoreDB2.DB2 script, which creates a version of the IBuySpy data on your DB2 UDB database. The script includes the SQL Server commands along with the translated DB2 UDB equivalent. If you edit the file, you'll see that the syntax differs but the end result is the same. (If you're accustomed to SQL Server development, this is a great way to see the DB2-equivalent SQL commands.)

The next stage is to alter the IBuySpy application itself to use DB2 UDB rather than SQL Server. First, though, let's review how .NET database components work and see how DB2 UDB fits in to ADO.NET.

The background to .NET database development

ADO.NET (or more properly the classes implemented in System.Data) doesn't care what provider is used to get data, so long as they implement the appropriate interfaces that can populate tables in either a DataSet or DataReader component. For example, IBuySpy uses the System.Data.SqlClient classes to work directly with SQL Server.

IBM has an equivalent managed provider to System.Data.SqlClient for DB2 UDB. The IBM.Data.DB2 managed provider contains classes that have a direct one-to-one correspondence with their SQL Server counterparts in System.Data.SqlClient, and additional properties are provided for specific DB2 UDB features. This managed provider is used in the IBuySpy Conversion Tutorial; the steps on pages 11 and 12 in the tutorial work just as well with C#Builder as they do with Visual Studio .NET.

For C#Builder, Borland has created the Borland Data Provider. This provides similar interfaces to the Microsoft driver, but isn't tied in to any particular back-end database. Conveniently, changing from the SQL Server managed provider to the Borland Data Provider is very straightforward.

System.Data.SqlClient only works with SQL Server. It's possible to use equivalent classes in System.Data.OleDb to work with DB2 UDB, but because OLE DB uses a Windows compatibility layer, this is slower than the native managed-code Borland Data Provider.

Exploring the Borland Data Provider

When you connect to SQL Server or MSDE using the Microsoft provider, the component used is a SqlConnection. The analog for this in the Borland Data Provider is BdpConnection. Similarly, SqlDataAdapter is replaced by BdpDataAdapter, SqlCommand by BdpCommand, SqlParameter by BdpParameter, and so on.

The components aren't exactly the same, because the Borland Data Provider exposes additional features. For example, it includes the ability to see live data at design time in Windows Forms applications.

The Borland Data Provider can, in many situations, switch from one database system to another with no changes at all to the source code. Indeed, as I'll show, IBuySpy can be adapted to run with either SQL Server or DB2 UDB through just a single change in the configuration file.

A good way to explore these additional properties and methods of the Borland Data Provider is to create some test Windows Forms applications. See other articles in the Borland area of the DB2 product domain for information.

Updating IBuySpy to use the Borland Data Provider

As it happens, all of the database access in IBuySpy is done through five C# program units. Therefore, we can change these units to use the Borland Data Provider very easily.

Importing IBuySpy into C#Builder
In C#Builder, use File > Open Project... to navigate to the directory where you installed the IBuySpy source. You'll need to change the Files of type... option at the bottom of the dialog to open the Visual Studio project file, StoreCSVS.csproj. C#Builder will import this file and translate it to the Borland Developer Studio equivalent, StoreCSVS.bdsproj. The Project Manager should display the IBuySpy.dll project (see Figure 4).

Figure 4. The IBuySpy application can be imported directly into Borland C#Builder

There are a couple of housekeeping details to cover before you can proceed, however. First you'll need to tell C#Builder which is the default Web page for the application: to do this, right-click on the file Default.aspx and choose Set as Start Page.

Next, you'll need to tell C#Builder which Web server you'd like to use as the host when debugging: to do this, right-click on the IBuySpy.dll file name and choose Options. In the dialog, choose ASP.NET in the tree view to the left, and set the options as in Figure 5.

Figure 5. Setting the debug options for the project in C#Builder

Updating the units to use the Borland Data Provider
The units we want to change are in the Components folder of the project. As an example, take a look at CustomersDB.cs; this contains all of the business logic necessary to add, query, and authenticate customers to the IBuySpy system (see Figure 6).

Figure 6. The data access is handled from the C# units in the Components directory

Because the data access is centralized in these five units, it's easy to make a large change to the system without going into detail of the rest of the application. Even here, you can see that the code used for database access is quite straightforward.

Go ahead and change the components to use their Borland Data Provider equivalents. First, add a reference to the Borland.Data.Provider and Borland.Data.Common assemblies for the project by right-clicking the References tab in the Project Manager and choosing Add Reference#x2026.

Then you'll need to replace the SQL Server objects with their Borland Data Provider equivalents. For each of these five files:

CustomersDB.cs OrdersDB.cs ProductsDB.cs ReviewsDB.cs ShoppingCartDb.cs

replace the following, using Edit > Replace in the editor:

SqlConnection with BdpConnection
SqlCommand with BdpCommand
SqlDataAdapter with BdpDataAdapter
SqlParameter with BdpParameter
SqlDataReader with BdpDataReader

You'll also need to change the following types, again using Edit > Replace:

SqlDBType.Int with BdpType.Int32
SqlDBType.NVarChar with BdpType.String
SqlDBType.DateTime with BdpType.DateTime
SqlDBType.Money with BdpType.Decimal

Then, you'll need to set the precision for the output parameters for the stored procedures that don't return a result set. In IBuySpy, examples of these are in the CustomersDB.cs, ProductsDB.cs and ShoppingCartDb.cs source files. The IBuySpy codes sets up individual BdpParameter objects after each // Add Parameters to SPROC comment. Where an output parameter is set up-in other words, where the Direction property of the parameter is set to ParameterDirection.Output or ParameterDirection.Both -the Precision property will also need to be set.

For strings, set the Precision property to the expected maximum length. For numbers, set the property to the overall length of the field.

So, for example, setting up the FullName parameter of the GetCustomerDetails method will look like this:

 
		BdpParameter parameterFullName 
       			= new BdpParameter("@FullName", 
		BdpType.String, 50); 
		parameterFullName.Direction = 
ParameterDirection.Output; 
		parameterFullName.Precision = 50; 
 
	myCommand.Parameters.Add(parameterFullName);

Another small fix is to each of the stored procedures that returns a result set. It's necessary to explicitly cast the result as a BdpDataReader. For example, in OrdersDB, the GetCustomerOrders method should have as its next-to-final line:

 
BdpDataReader result = 
(BdpDataReader) 
myCommand.ExecuteReader(CommandBehavior.CloseConnection);

There are more examples of this in ProductsDB.cs, ReviewsDB.cs, and ShoppingCartDb.cs.

Checking the stored procedure parameter order
One significant difference between SQL Server and DB2 UDB is that to DB2, the order of parameters in stored procedures is important. There are a few places where the parameters will need to be switched around:

In the GetProductDetails() method in ProductsDB.cs, switch the order of the parameters to: @ProductID, @ModelNumber, @ModelName, @ProductImage, @UnitCost, @Description.
In ShoppingCartDB.cs, in both the methods AddItem() and UpdateItem(), change the order of the parameters to @CartID, @ProductID, @Quantity. Also in this unit, in the RemoveItem() method swap the parameters to @CartID, @ProductID.

With this all done, you can compile the application. That will confirm that there are no syntax errors, and that you haven't left out any of the components in the search-and-replace operation.

Setting the configuration string and testing the application
The final step before we run the application will be to change the connection string in the Web.config file, because the syntax used by the Borland Data Provider is very different from that used by SqlClient. Replace the value of the ConnectionString property on line 5 with:

 
assembly=Borland.Data.Db2,Version=1.1.0.0,Culture=neutral, 
PublicKeyToken=91d62ebb5b0d1b1b; 
 
vendorclient=db2cli.dll;database=STORE;provider=DB2; 
username=username;password=password

Run the application. After a short pause, your Web browser should load the start page (see Figure 7). (You might need to press Alt-Tab to bring it to the foreground-C#Builder has to attach to the ASP.NET worker process, which can take a few moments, especially on a busy system.) Try adding a new customer and ordering some items.

Figure 7. The migrated application, using DB2 UDB

Switching between databases

Now that your application is using the Borland Data Provider, you can switch back and forth between databases by simply changing the ConnectionString in Web.config. For example, to switch back to SQL Server, change the ConnectionString to be:

 
assembly=Borland.Data.Mssql, 
Version=1.1.0.0,Culture=neutral,PublicKeyToken=91d62ebb5b0d1b1b; 
 
vendorclient=sqloledb.dll;database=database; 
username=username;hostname=hostname;password=password;provider=MSSQL

Compile, and the application should run directly.

Conclusion

This paper has discussed the steps involved in migrating a moderately complex application from SQL Server and the Microsoft-specific SqlClient provider to DB2 UDB and the Borland Data Provider. These steps will be the same for your own applications. First, you'll need to migrate the data and application objects on the server, such as stored procedures. With that done, the application will need to be altered to replace the SqlClient provider with the Borland Data Provider.

One of the great virtues of DB2 UDB is the Migration Toolkit, which makes the migration of systems from SQL Server to DB2 UDB very simple, as I've shown. When combined with the Borland Data Provider and C#Builder, you have a great combination for writing high-speed, database-independent applications.

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.

Resources

The IBuySpy application. (For this article you'll need the IBuySpy Store Application for Visual Studio .NET, C# version.)
Additional information on the structure and features of IBuySpy. This includes a discussion of the use of the DataReader rather than the DataSet component which is just as relevant to DB2 UDB as it is to SQL Server.
The IBuySpy Conversion Tutorial. This includes step-by-step instructions for adjusting an earlier version of IBuySpy to work with DB2 UDB.
The DB2 Migration Toolkit.
A background tutorial article is available here.
Borland C#Builder is available for personal non-commercial development and can be downloaded from www.borland.com/csharpbuilder.

About the author

Comments (Undergoing maintenance)

Trademarks | My developerWorks terms and conditions

Close [x]

Help: Update or add to My dW interests

What's this?

This little timesaver lets you update your My developerWorks profile with just one click! The general subject of this content (AIX and UNIX, Information Management, Lotus, Rational, Tivoli, WebSphere, Java, Linux, Open source, SOA and Web services, Web development, or XML) will be added to the interests section of your profile, if it's not there already. You only need to be logged in to My developerWorks.

And what's the point of adding your interests to your profile? That's how you find other users with the same interests as yours, and see what they're reading and contributing to the community. Your interests also help us recommend relevant developerWorks content to you.

View your My developerWorks profile

Return from help

Close [x]

Help: Remove from My dW interests

What's this?

Removing this interest does not alter your profile, but rather removes this piece of content from a list of all content for which you've indicated interest. In a future enhancement to My developerWorks, you'll be able to see a record of that content.

View your My developerWorks profile

Return from help

static.content.url=http://www.ibm.com/developerworks/js/artrating/

SITE_ID=1

Zone=Information Management

ArticleID=14416

ArticleTitle=Migrating From Microsoft SQL Server to IBM DB2 UDB Using Borland C#Builder

publish-date=07312003

author1-email=

author1-email-cc=