DataStream
LANGUAGES: VB .NET
TECHNOLOGIES: ASP.NET | ADO.NET | SQL Server| Access
Explore the DB2 .NET Enablement Beta
Learn to make ASP.NET pages mesh with DB2.
By Wayne S. Freeze
IBM has devoted significant effort to Java over the last few years, but it is also committed to .NET. IBM recently released beta 2 of its DB2 .NET Enablement product, which includes a native .NET data provider for DB2 along with an add-in to Visual Studio .NET that provides some useful DB2-specific development utilities.
Although DB2 owns a significant share of the marketplace, it often is overlooked by .NET programmers as an alternative to SQL Server. DB2 runs on a variety of platforms including Windows, Linux, and several flavors of UNIX, but DB2 is best known for running on large IBM mainframes under z/OS (previously known as MVS and OS/390). The .NET data provider for DB2 allows your ASP.NET application to access DB2 on any of these platforms.
I've had the opportunity to work with IBM's new DB2 .NET data provider. This product is still in beta, but it's quite impressive. You can download a copy of the software from http://www7b.software.ibm.com/dmdd/downloads/dotnetbeta/, and you can download a copy of the DB2 Personal Developer's Edition from http://www14.software.ibm.com/webapp/download/search.jsp?go=y&rs=db2pde.
Know Your Limitations
Before testing the beta version of the DB2 .NET Enablement product, you should know its limitations. The most significant is that the beta doesn't support Visual Studio .NET 2003. Also, drag-and-drop operations from the IBM Explorer window don't work with Web Forms. In addition, COM+ transactions do not work properly, and in some cases they might lead to data-integrity problems. The product has a few other minor issues documented on the Web site, but none of them should affect most applications.
The data provider for DB2 resides in the IBM.Data.DB2 namespace. As you would expect, the classes in the data provider mirror those found in the System.Data.OleDb and System.Data.SqlClient namespaces. So, you should include these statements in your ASP.NET application:
<%@ import Namespace="System.Data" %>
<%@ import Namespace="IBM.Data.DB2" %>
If you use the command-line compiler to compile assemblies, you'll need to include this parameter whenever you compile your programs:
/r:%DB2PATH%\bin\netf10\IBM.Data.DB2.dll
DB2 Data Types
DB2 supports a wide range of data types, as you would expect for any first-tier database-management system (see Figure 1). These types are documented in the DB2Type enumeration, which corresponds to the SqlType enumeration in the System.Data.SqlClient namespace.
|
DB2 Data Type |
.NET Data Type |
|
SmallInt |
Int16 |
|
Integer |
Int32 |
|
BigInt |
Int64 |
|
Real |
Single |
|
Double, Float |
Double |
|
Decimal, Numeric |
Decimal |
|
Date, Timestamp |
DateTime |
|
Time |
TimeSpan |
|
Char, VarChar, LongVarChar, Clob |
String (single-byte characters) |
|
Graphic, VarGraphic, LongVarGraphic, DbClob |
String (double-byte characters) |
|
Binary, VarBinary, LongVarBinary, Blob |
Byte Array |
Figure 1. Here's how DB2 data types correspond to .NET data types.
Connect to DB2
DB2 connection strings are similar to SQL Server and Access in that you need to specify the name of the database (more formally known as the database alias in DB2), the server the database is running on, and the user id and password needed to access the database.
You specify the database alias using the Database keyword and the name of the server using the Server keyword:
Database=Music;Server=Multivac
By default, the database uses the authentication information from the currently logged-on user, which for ASP.NET applications typically is ASPNET. You can, however, override the default user by specifying the User Id and Password keywords like this:
User Id=Wayne;Password=TopSecret
Other keywords in the connection string include Pooling, which when set to true enables connection pooling, and Min Pool Size and Max Pool Size, which determine the minimum and maximum size of the connection pool. The Connection Lifetime keyword overrides the default value of 15 seconds that a connection can remain idle while in the connection pool.
Display Data With DB2
As you would expect, the DB2 .NET data provider acts much like the OleDb or SQL Server client (see Figure 2). The only real difference between this code and the code in SQL Server is that the DB2DataAdapter, DB2Connection, and DB2Command classes are used in place of SqlDataAdapter, SqlConnection, and SqlCommand. The DataSet class and the DataGrid control are independent of the particular database library.
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs)
Dim ds As DataSet
Dim ConnectionStr As String = _
ConfigurationSettings.AppSettings("DB2ConnStr")
Dim adpt As DB2DataAdapter
Dim conn As DB2Connection
Dim cmd As DB2Command
If Not IsPostBack Then
ds = New DataSet()
conn = New DB2Connection(ConnectionStr)
cmd = New DB2Command("GETCUSTOMERS", conn)
cmd.CommandType = CommandType.StoredProcedure
cmd = new DB2Command("Select * From CDs", conn)
cmd.CommandType = Command.Text
adpt = New DB2DataAdapter(cmd)
adpt.Fill(ds, "Customers")
Session("Customers") = ds.Tables("Customers").DefaultView
DataGrid1.DataSource = Session("Customers")
DataGrid1.DataBind()
End If
End Sub
Figure 2. The code needed to display DB2 data in a DataGrid using the DB2 .NET data provider is nearly identical to the code needed for SQL Server or Access.
Of course, the stored procedure used to produce the data is different than what you would use with SQL Server (see Figure 3).
P1: BEGIN
DECLARE Step1 CURSOR WITH RETURN FOR
Select CUSTOMERS.CUSTOMERID, CUSTOMERS.NAME, CUSTOMERS.STREET,
CUSTOMERS.CITY, CUSTOMERS.STATE, CUSTOMERS.ZIPCODE,
CUSTOMERS.PHONE, CUSTOMERS.EMAILADDRESSs
From CUSTOMERS;
OPEN Step1;
END P1
Figure 3. The GETCUSTOMERS stored procedure was built using the wizards from the DB2 .NET Enablement beta product.
This stored procedure was built using the various wizards added to Visual Studio by the DB2 .NET Enablement beta, which I'll explain in more detail in the next installment of this column.
Wayne S. Freeze is a full-time computer book author with more than a dozen titles to his credit, including Windows Game Programming with Visual Basic and DirectX (Que) and Unlocking OLAP with SQL Server and Excel 2000 (Hungry Minds). He has more than 25 years of experience using all types of computers, from small, embedded microprocessor control systems to large-scale IBM mainframes. Freeze has a master's degree in management information systems as well as degrees in computer science and engineering. You can visit his Web site at http://www.JustPC.com and send e-mail to mailto:[email protected]. He loves reading e-mail from his readers, whose ideas, questions, and insights often provide inspiration for future books and articles.
