asp.net db2 connection

In this article I will show you a simple way to read an IBM mainframe db2 table from an asp.net web application using the Microsoft oledb provider for db2 and enterprise library 5.0.

Before you start please note that although the Microsoft OLEDB DB2 provider is free, it cannot be installed unless you have SQL server developer version or above installed on your machine. 

To connect to DB2 using an ODBC provider instead of Microsoft OLEDB provider for DB2  check out

http://dotnetanalysis.blogspot.com/2011/04/connect-to-db2-from-aspnet-using-odbc.html

Prerequisites:

Visual studio 2008

.net framework 3.5

access to IBM db2 database

Microsoft oledb provider for db2 (http://go.microsoft.com/fwlink/?LinkId=123713&clcid=0x409)

Five dlls that come with enterprise library 5.0 namely

• Microsoft.Practices.EnterpriseLibrary.Data.dll
• Microsoft.Practices.EnterpriseLibrary.Common.dll
• Microsoft.Practices.ServiceLocation.dll
• Microsoft.Practices.Unity.dll
• Microsoft.Practices.Unity.Interception.dll

The enterprise library 5.0 can be downloaded from the Microsoft website

http://www.microsoft.com/downloads/en/details.aspx?FamilyID=bcb166f7-dd16-448b-a152-9845760d9b4c

This is how you do it:

1>Create a web application

2>Modify the connection string in your web.config to include this

  <connectionStrings>

    <add name="mydb2" connectionString="Provider=DB2OLEDB;Password=xxx;User ID=xxx;
Initial Catalog=myDBName;Network Transport Library=TCP;Network Address=myServerAddress;Package Collection=mspkg" providerName="System.Data.OleDb"/>

  </connectionStrings>

3>Copy the five dlls

• Microsoft.Practices.EnterpriseLibrary.Data.dll
• Microsoft.Practices.EnterpriseLibrary.Common.dll
• Microsoft.Practices.ServiceLocation.dll
• Microsoft.Practices.Unity.dll
• Microsoft.Practices.Unity.Interception.dll

to a location inside the web application's folder

4>Add references to them.

Your references would look something like this

5>Add a gridview named GridView1 to the aspx page.

6>In the Default.aspx.cs page add these two using directives

using System.Data.Common;

using Microsoft.Practices.EnterpriseLibrary.Data;

7>Add this code to read a db2 table and populate the gridview with it

            DataSet ds = new DataSet();

            string connectionStringName = "mydb2";

            Database db = DatabaseFactory.CreateDatabase(connectionStringName);

            DbCommand dbCommand = db.GetSqlStringCommand("SELECT * FROM mytable");

            db.LoadDataSet(dbCommand, ds, "tblName");

            GridView1.DataSource = ds.Tables[0];

            GridView1.DataBind();

Now when you run the application the db2 table data will be displayed in the webpage.