Skip to content

DBAzine.com

Sections
Personal tools
You are here: Home » DB2 » DB2 Distributed (UDB) Articles Archive » Visual Basic.Net® Programming, Any Database
Seeking new owner for this high-traffic DBAzine.com site.
Tap into the potential of this DBA community to expand your business! Interested? Contact us today.
Who Are You?
I am a:
Mainframe True Believer
Distributed Fast-tracker

[ Results | Polls ]
Votes : 3561
 

Visual Basic.Net® Programming, Any Database

by Paul C. Zikopoulos

Introduction

You are a Visual Basic.Net (VB.Net) programmer, and who could blame you? VB.Net is one of the most used rapid application development (RAD) frameworks going, and really the grandfather of point-and-click application development. With its roots in Visual Basic® (VB), VB.Net has made the original VB developer quite a lot more powerful, and even delivers a maturation process into a true Object Oriented (OO) programming model.

While the posturing of the Java 2 Enterprise Edition (J2EE) and .Net® camps try to put forth the notion that their framework is better, more efficient, and more steeply cuts the time to value, the truth is that both have tremendous penetration. Figure 1 illustrates this fact.

Today’s IT world is one of heterogeneity: different application providers, operating systems, database management systems, programming models, and so on. And even if your shop is homogeneous, chances are as you try to reach into your value chain, it isn’t. With this is mind, DB2 Universal Database® (DB2 UDB) has provide not only a native managed provider for .Net, but also tight integration into the Visual Studio.net tooling.

If you are a VB.net application programmer that has been asked to expand your role and start developing applications in DB2 UDB, and you were somewhat concerned, fear not! This article will give you a side-by-side comparison between building a DB2 UDB and a SQL Server® application. You are quickly going to find out that the process is the same; in fact, there are even some neat things you are going to discover that make it easier (yes easier) to work with DB2 UDB than SQL Server!

Assumptions

To make things move along more easily, we will assume the following in this article:

1. You have Visual Studio.net installed on your system.

2. You have a typical installation of SQL Server on your system with the Northwind sample database.

3. You have a typical installation of DB2 UDB on your system with the DB2 UDB v8.1.2 update applied, and you have also created the Sample database. If you are using Visual Studio.net 2003, please ensure you have applied Fix Pack 3 (you can download DB2 UDB Fix Packs and Updates at: www.ibm.com/cgi-bin/db2www/data/db2/udb/winos2unix/support/index.d2w/report). If you haven’t created the Sample database, just enter the db2sampl command from any command prompt.

4. You are logged on to your system as a user that belongs to the local Administrator’s group (we do this to simplify security authorizations to your databases — we trust you are on a test machine).

5. Both the SQL Server and DB2 UDB instances that house the sample databases are started (they are usually set to start by default).

Building a Simple Database Application in DB2 UDB and SQL Server

1. Start a new project that includes a Windows Application and call it DB2andSQLServerApplication. This is the same step no matter which database you are using.

2. In the Solution Explorer, rename the application to DB2Application and add another WinForm (right click on the solution, select ) and call it SQLServerApplication. The Solution Explorer window should now look like this. This is the same step no matter which database you are using.

3. In the Properties for each form, rename the application to something that will help better identify the two forms. In our example, we used DB2Application and SQLServerApplication.

4. VB.net programmers take a different approach to application development than a J2EE programmer. When programming in VB.net, you program from the interface in, so let’s follow that methodology here.

Our simple application will merely house a data grid that will return data, at the click of a button, from a table stored in either DB2 UDB or SQL Server. To show the data in the application, you need a DataGrid (), so on both forms drag one and size it such that it takes up about half of the form.

Since our applications will retrieve data at the click of a button, drag-and-drop a Button
() control onto the form just below the DataGrid, and name it Retrieve Data.

Both forms should now look like the following:

These are the same steps no matter which database you are using.

5. Now its time to define the database and table that will populate the Dataset and bind it to the DataGrid.

In this case, you simply locate the table in the appropriate SQL Server tree (for this example, we used the CUSTOMERS table in the Northwind database) in the Server Explorer, then drag-and-drop it onto you form. This action automatically creates the SQLConnection1 and SQLAdapter1 objects

The SQLAdapter1 object uses the SQLConnection1 object to populate a disconnect data set (and later resynchronize the data). To generate a Dataset, right click on SQLAdapter1 object and select Generate Dataset… and click OK. Finally, bind the Dataset to the DataGrid by selecting the DataGrid, and altering the Datasource properties so that it binds to DataSet11.Customers (the Dataset that was created from the SQLAdapter1 object). After doing this, your WinForm should look like the figure below.

You can see that in the Server Explorer on our system, both a DB2 UDB database and a SQL Server database are enumerated. The Server Explorer drag-and-drop feature for DB2 UDB is for OLE-DB connections. Consequently, we added in an IBM Explorer to specifically work with DB2 UDB database connection.

The IBM Explorer functions in the same way the Server Explorer does — but it does have some really neat features not available in SQL Server that make it easier to develop applications (we will cover those in a bit).

If this is the first time that you ever used the IBM Explorer, you need to add the server to your system, just like with SQL Server. You will only have to do this step once, unless you want to access a different database. VisualStudio.net will enumerate all of the databases cataloged on your system which makes it a breeze to add a DB2 UDB database connection. For our example, right click on the Data Connections folder and select Add Connection… From the Database alias drop down list, select SAMPLE and click OK.

Now simply locate the table in the appropriate DB2 UDB tree (for this example, we used the STAFF table in the SAMPLE database) in the IBM Explorer, then drag-and-drop it onto you form — just as you would with SQL Server. This action automatically creates the connection and adapter objects just like in the SQL Server case. Of course their naming is slightly different. However, the objects associated with the DB2 UDB native .Net managed provider follow the same namespace as SQL Server: This makes changes to application to run on either database very streamlined.

Again, following the same steps as you would for SQL Server generate a Dataset, by right clicking on db2DataAdapter1 object and selecting Generate Dataset… and then OK. Finally, bind the Dataset to the DataGrid altering the Datasource properties so that it binds to db2DataSet11.Staff (the Dataset that was created from the db2DataAdapter1 object).

At this point, both your forms should like the following:

And as you would expect, VisualStudio.net auto-generates all the code for either database for you auto-magically:

6. Now it’s time to finish our application by writing two simple lines of code — don’t you just love VB.net? The code is going to be the same for either WinForm, but obviously you will select that appropriate generated data objects for each database.

Double-click on the Button that you created and add the appropriate lines of code:


7. Now it’s time to build the applications. To make things easier, in the Solution Explorer, right click the Solution DB2andSQLServerApplication bar and select Set Startup Projects. Configure the build such that there are Multiple Startup Projects by selecting its corresponding radio button as shown below:

Now build the application by right clicking the Solution DB2andSQLServerApplication bar again and selecting Build Solution. In the directory where this project was created, you should fine the two corresponding .EXE files for your applications.

 

--

Paul C Zikopoulos, BA, MBA, is an award-winning writer and speaker with the IBM Database Global Sales Support team. He has more than seven years of experience with DB2 UDB and has written numerous magazine articles and books about it. Paul has co-authored the books: DB2 - The Complete Reference, DB2 Fundamentals Certification for Dummies, DB2 For Dummies, A DBA's Guide to Databases on Linux, and DB2 Version 8: The Official Guide. Paul is a DB2 Certified Advanced Technical Expert (DRDA and Cluster/EEE) and a DB2 Certified Solutions Expert (Business Intelligence and Database Administration). You can reach him at: paulz_ibm@msn.com.

Trademarks and Disclaimers
 
DB2, DB2 Universal Database, IBM, and WebSphere are trademarks or registered trademarks of International Business Machines Corporation in the United States, other countries, or both. Copyright International Business Machines Corporation, 2003. All rights reserved.
 
This article represents the views of the author and is not intended to be official communication from IBM. This article is for informational purposes only and the author is not liable for its contents.
 
Microsoft, .Net, Visual Baisc.Net, Visual Basic, and SQL Server 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.

Contributors : Paul C Zikopoulos
Last modified 2005-04-12 06:21 AM
Transaction Management
Reduce downtime and increase repeat sales by improving end-user experience.
Free White Paper
Database Recovery
Feeling the increased demands on data protection and storage requirements?
Download Free Report!
 
 

Powered by Plone