Connect to db2 from asp.net using ODBC

In this article I will show you how to connect to an IBM db2 database from asp.net using an ODBC data provider and enterprise library 5.0. Enterprise library is used here just to reduce the amount of code. Its not a show stopper. But you do need to install an ODBC data provider. Usually ODBC data provider ships with an ODBC db2 connection client. Ive heard there are free versions of ODBC providers out there, but Ive never come across a reliable one. Clients I worked for usually used an IBM ODBC provider.

To connect to DB2 using an OLEDB data provider check out
http://dotnetanalysis.blogspot.com/2011/02/aspnet-db2-connect.html

Prerequisites for this article :

ODBC data provider

.net version 3.5

Microsoft enterprise library 5.0 (download page)

This is what you do.

1>Create an asp.net application and add  a grid view

            <asp:GridView ID="GridView1" runat="server" >

            </asp:GridView>

2>Add this connection string to your web.config

  <connectionStrings>

    <add name="DB2LOGIN" connectionString="Driver={IBM DB2 ODBC DRIVER};Database=DBName;Hostname=serverName;Port=446; Protocol=TCPIP;Uid=userName;Pwd=password;" providerName="System.Data.Odbc" />

  </connectionStrings>

3> Add a reference to these 5 dlls in the enterprise library 5.0

Microsoft.Practices.EnterpriseLibrary.Data.dll

Microsoft.Practices.Unity.dll

Microsoft.Practices.Unity.Interception.dll

Microsoft.Practices.EnterpriseLibrary.Common.dll

Microsoft.Practices.ServiceLocation.dll

4>Add this code to the .cs page 

using System;

using System.Data;

using System.Data.Common;

using Microsoft.Practices.EnterpriseLibrary.Data;

namespace DB2Read

{

    public partial class _Default : System.Web.UI.Page

    {

        protected void Page_Load(object sender, EventArgs e)

        {

            DataSet ds = new DataSet();

            string connectionStringName = "DB2LOGIN";

              Database db = DatabaseFactory.CreateDatabase(connectionStringName);

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

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

            GridView1.DataSource = ds.Tables[0];

            GridView1.DataBind();

        } 

    }

}

Run the app and what ever is in your db2 table myTableName , that will be displayed on your web page.

If you don't want to use Microsoft enterprise library, you can look at the sample code below that uses a helper class from Microsoft. Not sure if it is supported anymore. But it works very well.

public static DataTable DB2data(OdbcParameter[] Params, string spName, string connString)

        {

            DataSet ds = new DataSet();

            GotDotNet.ApplicationBlocks.Data.AdoHelper DB2Helper;

            DB2Helper = GotDotNet.ApplicationBlocks.Data.AdoHelper.CreateHelper("GotDotNet.ApplicationBlocks.Data", "GotDotNet.ApplicationBlocks.Data.Odbc");

            using (OdbcConnection DBConn = new OdbcConnection(connString))

            {

                DBConn.Open();

                ds = DB2Helper.ExecuteDataset(DBConn, spName, Params);

                DBConn.Close();

            }

            return ds.Tables[0];

        }