Translate

Wednesday, February 16, 2011

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

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


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.







    6 comments:

    1. This is a very good article. Is it possible to connect to DB2 without installing MS oledb connect?

      ReplyDelete
    2. You can with IBM DB2 connect. I could post an article on how to do that, if there is a demand for that.

      ReplyDelete
    3. How do you add parameters for DB2?

      ReplyDelete
    4. Hi Anonymos ,
      See if this example helps you

      Database db = DatabaseFactory.CreateDatabase(connStrName);

      DbCommand dbCommand = db.GetStoredProcCommand(spName);

      db.AddInParameter(dbCommand, "Div_Num", DbType.Int16, Div_Num);

      db.LoadDataSet(dbCommand, ds, tableNames);

      return ds.Tables[0];

      ReplyDelete
    5. Can you please post instructions on how to connect using IBM DB2 connect, thks.

      ReplyDelete
    6. Ditto on Anonymous Nov 30. That'll be awesome. Thanks!

      ReplyDelete

    Comments will appear once they have been approved by the moderator