Translate

Monday, April 25, 2011

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];

        }


2 comments:

  1. Seriously, this is cool and I want to give it a try. Can you kindly post the driver that you use for us to download ? Or any hyper link to go and download ? I have checked IBM website and I dunno who the hell wrote it but I couldn't understand a single thing. Appreciated if you could help me out.

    ReplyDelete
  2. I never have purchased it myself. The clients I worked for purchased it from IBM.

    Look for IBM db2 connect on their website. You have to enter the operating system etc.

    ReplyDelete

Comments will appear once they have been approved by the moderator