Connect ASP.NET to DB2 UDB for iSeries

ASP .Net has its own unique ways to work with various Database product. Using ADO .Net developers can do database connection with easy. Also no matter what the DBMS software, the code syntaxes are not too different.

ASP .Net works best with SQL Server. It is no wonder because ASP and SQL Server both comes from Microsoft. But it also can operate with DB2 and other DBMS like Oracle, MySQL, Access etc.

In my experiences, DB2 versions that commonly used are DB2 LUW (Linux Unix Window) and DB2 UDB for iSeries or DB/400. I will give an example of ASP.Net with DB/400.

The IBM DB2 UDB for iSeries .NET provider is part of the iSeries Access for Windows product, starting in its V5R3M0 release. It uses the optimized host database server job (QZDASOINIT) to perform database requests on the iSeries. Because it was written especially for the iSeries and uses the optimized server, it can take advantage of improvements that are made especially for the iSeries.

Library reference that used for this purpose is IBM.Data.DB2.iSeries.dll. We can get that dll file after install IBM i Access For Windows and it located on <installationdir\IBM\ClientAccess> folder.
Other method such as using OLEDB can be used too, but I think it is better to use IBM.Data.DB2.iSeries when work with DB/400 because it more native than else.

ADO .Net has similar group of objects. So is IBM.Data.DB2.iSeries objects i.e iDB2Connection, iDB2Command, iDB2DataAdapter, iDB2DataReader etc. These objects behaviour is same for another database i.e SQL Server with SqlConnection, SqlCommand, … etc
Knowledge or experiences of ASP.Net with SQL Server will not hard to convert into DB/400. Only database administrator parts will be much different off course.

In this example we are using GridView control to view recordset. GridView contains template to create html table appearance. With GridView we can set header, field value column, row style, border etc. Also GridView has methods for paging, sorting, etc.

Assuming we have DB table like below :
Schema Name : schdev
Table Name : item_master
Fields / Columns : itemno, itemdesc, uom

Step by step to work with ASP.Net (C#) and iSeries DB/400 are below:

  1. Open Microsoft Visual Web Developer.
  2. Add Reference of IBM.Data.DB2.iSeries.dll into your web project
  3. Add GridView control to .aspx file. You can drag and drop it from toolbox or write below :

    <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false"
    BorderColor="ActiveCaption" onsorting="GridView1_Sorting" AllowPaging="true" PageSize="20"
    onpageindexchanged="GridView1_PageIndexChanged"
    onpageindexchanging="GridView1_PageIndexChanging">
    <HeaderStyle BackColor="BlanchedAlmond" Font-Bold="true" />

    <Columns>
    <asp:BoundField ReadOnly="true" HeaderText="ITEM #" DataField="itemno" SortExpression="itemno" ControlStyle-Font-Overline="false" />
    <asp:BoundField ReadOnly="true" HeaderText="ITEM Desc" DataField="itemdesc" SortExpression="itemdesc" />
    <asp:BoundField ReadOnly="true" HeaderText="UOM" DataField="uom" SortExpression="uom" />
    </Columns>
    <SelectedRowStyle BackColor="AliceBlue" Font-Bold="true" />
    <PagerSettings Mode="NumericFirstLast" PageButtonCount="10" Position="Bottom" LastPageText="Last" FirstPageText="First" />
    <PagerStyle BackColor="Aqua" VerticalAlign="Bottom" HorizontalAlign="Right" /></asp:GridView>

    GridView ID is GridView1. This ID will be used intensively in aspx code file.
    AutoGenerateColumns=false means GridView is not generating header and column content automatically. We specify it with tags.
    OnSorting=gridview1_sorting is a method name for sorting gridview column. We have to write gridview1_sorting function on code file (.aspx.cs)
    onpageindexchanging=GridView1_PageIndexChanging is a method to catch user click page number event. This also have to write on code file.
    <headerstyle /> is tag to set style for header column
    <columns> and <asp:boundfield /> tags controls appearance of recordset. It has datafield represent related field name and sortexpression represent what to sort.
    <selectedrowstyle /> is tag to set style when gridview’s row is selected
    <pagersetting /> is tag to control how page number look. It has pagebuttoncount that set how many number viewed on page, position and some page facility texts.
    <pagerstyle /> set style on page number.

  4. Include necessary namespace into aspx code file (.aspx.cs) :
    using IBM.Data.DB2.iSeries;
    using System.Data;
  5. Connect to DB/400 on PageLoad event:

    protected void Page_Load(object sender, EventArgs e)
    {

    if (!Page.IsPostBack)
    {
    iDB2Connection cn = new iDB2Connection("DataSource=serverip;DefaultCollection=dbname;UserID=userid;Password=passwd;Connection Timeout=0");
    iDB2Command com = new iDB2Command("SELECT itemno, itemdesc, uom FROM schdev.item_master", cn);
    com.CommandTimeout = 0;

    iDB2DataAdapter da = new iDB2DataAdapter(com);

    DataSet dt = new DataSet();

    da.Fill(dt, "item_master");

    GridView1.DataSource = dt.Tables["item_master"];
    GridView1.DataBind();

    Session["TaskTable"] = dt.Tables["item_master"];

    da.Dispose();
    com.Dispose();

    cn.Close();
    }
    }

    iDB2Connection connect user to iSeries DB/400, iDB2Command prepare sql statement into that connection, iDB2DataAdapter runs query and fill the results into DataSet.
    Data source of gridview filled by DataTable / dt.Tables["item_master"] object.
    Session["TaskTable"] act as container of DataTable for sorting purpose. When user sort column we dont have to retrieve data from database but get it from this Session object.
    If dataadapter object is not use anymore then dispose it with da.Dispose() method also with command object. Connection have to be closed so it releases resources.

  6. Make a procedure to sort gridview:

    protected void GridView1_Sorting(object sender, GridViewSortEventArgs e)
    {
    DataTable dt = Session["TaskTable"] as DataTable;
    if (dt != null)
    {

    GridView1.DefaultView.Sort = e.SortExpression + " ASC";
    GridView1.DataSource = dt;
    GridView1.DataBind();
    }
    }

  7. Make a procedure to handle paging:

    protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)
    {
    DataTable dt = Session["TaskTable"] as DataTable;
    GridView1.DataSource = dt;
    GridView1.PageIndex = e.NewPageIndex;
    GridView1.DataBind();
    }
  8. Test by run the website project. Make sure you have related database table and TCP/IP connection is good.

Note about GridView and Skin:

For a beginner, writing such code above is quite confusing but when you used it often then GridView control can increase productivity saving your coding time.
Style on GridView can be re-use by another GridView in same or different web pages. Add new item on App_Themes folder to create GridView’s style and save it as .skin file like below:

<asp:GridView runat="server" SkinId="gvskin" AlloSorting="true"
BorderColor="ActiveCaption" AllowPaging="true" PageSize="100">
<HeaderStyle BackColor="BlanchedAlmond" Font-Bold="true" />
<SelectedRowStyle BackColor="AliceBlue" Font-Bold="true" />
<PagerSettings Mode="NumericFirstLast" PageButtonCount="10" Position="Bottom" LastPageText="Last" FirstPageText="First" />
<PagerStyle BackColor="Aqua" VerticalAlign="Bottom" HorizontalAlign="Right" />
</asp:GridView>

This skin has ID gvskin. To implement this skin into GridView look at sample below:

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false"
SkinID="gvskin" AllowSorting="true" onpageindexchanging="GridView1_PageIndexChanging"
onsorting="GridView1_Sorting">

<Columns>
<asp:BoundField ReadOnly="true" HeaderText="ITEM #" DataField="mmitno" SortExpression="mmitno" />
<asp:BoundField ReadOnly="true" HeaderText="ITEM Desc" DataField="mmitds" SortExpression="mmitds" />
<asp:BoundField ReadOnly="true" HeaderText="UOM" DataField="mmunms" SortExpression="mmunms" />
</Columns>

</asp:GridView>

Using skin on GridView makes tags shorter and consistent look and feel with another GridView. If we want to change style on GridView then edit on skin file only then all GridView’s style will be change. Simple!

Cheers,
Agung Gugiaji

Advertisements

2 responses to “Connect ASP.NET to DB2 UDB for iSeries

  1. System.ObjectDisposedException using .NET Framework 2.0

    Technote

    Problem
    When using the IBM.Data.DB2.iSeries .NET provider with .NET Framework 2.0, an ObjectDisposedException occurs when the application terminates. This problem occurs because of an incompatible change made in the .NET Framework.

    Any body know where to get these hot fixes:
    Resolution
    This problem is documented in APAR SE22506 and is corrected with the following service packs:

    V5R3 SI24723
    V5R4 SI26879

    • Hi Bryan,

      FYI, I am using V6R1 and have tried to convert my projects from .Net 4.0 client into .Net 2.0. The result is OK. I dont have AS400 Version 5. I tried both on Web and WinForm Project.

      You said that error occurs when the application terminates. Are you using WinForm or Web application? Or have you try using .Net 3.5 ?
      If we are using Web app then the application termination is only matter of closing browser. So No server script error will occur if the execution on web page does not contain errors.

      Thanks

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s