An Introduction to

Rapid Web Application Development

With

IBM DB2  Universal Database

And

 Microsoft Visual Studio.NET.

By

Anil Mahadev

DB2 Enthusiast and DB2 India User Group

 Lead Logo Architect.

Introduction:

In this latest issue of DB2 Tutorials, Iam very happy to share with you my experiences of using DB2 with Visual Studio.NET 2003.

But before we venture into the exciting world of web application development with DB2, we need to follow some ground rules and setup our environment.

This will enable us to make this learning experience a wonderful journey and will make sure that all goes well with this tutorial.

So here are the Hardware and Software Requirements for the Tutorial.

System Requirements:

A basic P4 Machine with 512 MB RAM and about 20 GB HDD, a pointing device,

A keyboard and a monitor.

Software Requirements

1) Windows 2000 and above.

2) A personal copy of DB2 UDB (Any edition will do, as I am still developing for test 

   purposes only, I will be using the Express Edition of DB2 UDB 8.2 for Windows.

3) Microsoft Visual Studio.NET 2003(Professional, Enterprise Developer or Enterprise Architect).

4) Install the DB2 Addin, once you have completed the Installation of DB2 UDB.

5) Finally the urge to learn new things and adapt accordingly.

Now for the finer details that need to be addressed.

Please follow these steps in the order given below.

  1. When one is installing DB2 Express or any Edition of DB2, you will find an option where it asks you to enable Operating System Security.
    If you have enabled it as I have done on my machine, you will have two new Windows Group accounts being created.

    These are DB2ADMS     à DB2 Administrators Group. This group will enable users belonging to this group perform any DB2 Administrative tasks, such as Creation of Databases, Users etc.

    The next one is DB2USERS     à DB2 Users Group. This group will enable users belonging to this group help them to view, modify and delete data based on their database and object level permissions, granted to them.


    To verify what you have just learnt, navigate to Start    à Administrative Tools à Computer Management à Under that Select Users and Groups.

    In that Under Groups     à you will find two new Groups being added.

    So why is that we are delving into these groups. There is a reason.


  2. Now that the above points are clear, the reason is this as shown in figure 1.1 below

                                            Figure 1.1



The figure above displays an error when you try and connect DB2 with ASP.NET for the first time. This error is called the 55032 SQL State Error.

Problem:

This error occurs when you have not added ASP.NET Account to your DB2 Users Group Account.

Solution:

This can be solved by adding the ASPNET Account to your DB2 Users Group and Restarting IIS.


After the above steps are done, we must be able to see a successful page as shown below in
Figure 1.2

                                              Figure 1.2

So that’s great to see the above page. The question now remains how you achieve it, my dear fellow readers.

I shall guide you through each step, so don’t worry. This tutorial assumes, you are developing a DB2 with ASP.NET web application for the very first time, so every detail is covered and will cover another Advanced Tutorial in the next section.

In this section we shall be using the SAMPLE database that comes with DB2 UDB (All editions). I will not repeat the process on how to create a database etc.

What I will be doing is showing you on how to develop our DB2 Application using the DB2 Addin, provided by IBM J , a great set of tools that make your lives easier while developing for .NET.

The first part of this article is to help you establish a connection to the DB2 Database using the DB2 Data Adapter and the Data Grid to display data from a table called Members, which we shall create in the next section.

All you have to do is, in order to achieve, creation of a table, you may perform it in two ways.

  1. To use the DB2 Tools for .NET.

  2. Cut and Paste this Code into the Command Editor.

Note: All the tables are based on Schema.TableName, i.e. if we want to access our Members table; we refer to ANIL.Members (replace ANIL with your Schema).

Here is the SQL DDL for creation of the Members Table.

CREATE TABLE ANIL.MEMBERS (

          MEMBERID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),

          MEMBERNAME VARCHAR ( 256 ) NOT NULL ,

          EMAIL VARCHAR ( 256 ) NOT NULL ,

          CONSTRAINT MEMBERS_PK PRIMARY KEY (MEMBERID))

          IN USERSPACE1

GRANT ALL ON TABLE ANIL.MEMBERS TO PUBLIC

COMMENT ON  ANIL.MEMBERS (MEMBERID IS 'memberID', MEMBERNAME IS 'member name', EMAIL IS 'member e-mail')

Please run the above script in your Command Editor.

And you will have a table in your Sample Database by the Schema.TableName ( Called in this case as ANIL.MEMBERS).

Now enter some records for our Demo purpose. 2 records are sufficient. We shall add more in the coming section of the article.

Now in your IBM Toolbar à You can see under Tables you will find the members table.

Now we now going to learn on how to display DB2 UDB Data from the SAMPLE Database and the table MEMBERS.

This will be the first part of the tutorial.

We will next learn on how to insert Data and display it in the same Data Grid of the current Page.

Please follow these steps in implementing the display of DB2 Data from the Members Table.

  1. Create a new Web Application Project and give it a name.

  2. Next drag and drop a Data Grid and a Button onto the form.

  3. From the IBM Explorer, drill down to the SAMPLE Database, and under Tables navigate to the Members Table. Right Click on the Table and click on the
    New     à Data Adapter.

  4. Next drill down to the Data Adapter Section. Now Drag and Drop this Data Adapter1 onto the WebForm.

  5. You will now see on the canvas, a db2 connection, db2Data Adapter being placed. Next perform these steps in the WebForm generated section. Navigate to the db2connection section. In this section, please provide your password option and give it your db2 password, in order to avoid any errors.

  6. Next right click on your db2 data adapter and click generate dataset.

  7. Now we need to bind our Data Grid with our Data Set. Select the Data Grid and set the Data Source = db2DataSet11, Data Member = MEMBERS and Data Key Field = MEMBERID.

  8. We are almost there now. Now double click on the button, you just placed. Give it a name as Load and Text Property as Load Members Table.

And enter the following code: For a change, Iam using VB.NET, since most of my previous articles used C#.


Code Section for Loading Data from the Members Table


Private Sub Load_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Load.Click

        Try

            'fill the data adapter

            db2DataAdapter1.Fill(db2DataSet11, "MEMBERS")

            'now bind the Data Grid

            DataGrid1.DataBind()

        Catch ex As Exception

            Response.Write("There appears to be some error " & ex.Message)

        End Try

End Sub

Now press F5 and run your Project.

You  should see a screen as shown in the figure 1.3  below.

                                                     Figure 1.3

Wow !!!, so we successfully displayed data from DB2.

We shall now move on to a more interesting section.

Inserting Data into our new table called the REGISTER Table, this table handles the registration process of a user.

Here is the Code needed to create the REGISTER TABLE.

REGISTER Table CODE

CREATE TABLE "ANIL"."REGISTER"  (

            "GNAME" VARCHAR(50) ,

            "GEMAIL" VARCHAR(255) )  

           IN "USERSPACE1" ;

Please replace ANIL with your SCHEMA NAME.

Now create a new page and give it a name, say, AddGuest.aspx and hit enter.

We now need to add two buttons, two labels, two textboxes and a data grid onto the form.

Place them accordingly.

Give the Buttons the following properties

Button1: Register.

Button2: Load Register Table.

Please note: You need to perform the same steps as described for loading a Table in the preceding example. The only difference would be the table name. Change the MEMBERS Table to REGISTER. Simple J !

Now write the following code to Insert a new Record into our REGISTER Table.


Code for Register Button

Try

            // Create a new DB2 Connection

            Dim db2con As New DB2Connection("database=SAMPLE;user id=ANIL;password=”your DB2 Password")

          // Create a new SQL Command that will allow us to insert new Records

            Dim db2cmd As New DB2Command("Insert into ANIL.REGISTER (GNAME,GEMAIL) values(@MemName,@Email)", db2con)

          // Now add new parameters so that we can reference it to
             the data being inserted.

            db2cmd.Parameters.Add("@MemName", DB2Type.VarChar, 50)

           // Next we are going to set the parameter to the controls, so that, they will

            insert what information we give , in these controls.

            db2cmd.Parameters("@MemName").Value = txtMemName.Text

            db2cmd.Parameters.Add("@Email", DB2Type.VarChar, 255)

            db2cmd.Parameters("@Email").Value = txtMemEmail.Text

          // Now open the database connection

            db2cmd.Connection.Open()

          // Now we need to Execute our Query, so that it will insert the data we

               specified in the textbox controls

            db2cmd.ExecuteNonQuery()

          // We display a message stating, the new member has been added

            Response.Write("Guest Added")

          // We now need to update our Data Adapter and the Data Set , so that the 

          Data Grid will display the newly inserted record.

            db2DataAdapter1.Update(DB2DataSet1, "REGISTER")

          // Now DataBind the Data Grid

            DataGrid1.DataBind()

          // Now Close the connection

            db2cmd.Connection.Close()

        Catch DB2Exp As DB2Exception

            Response.Write(DB2Exp.Message)

 End Try

Now here is the code for the Load Register Table, just in case you need to refer.


Code for Load Register Table

Try

            'fill the data adapter

            db2DataAdapter1.Fill(DB2DataSet1, "REGISTER")

            'now bind the Data Grid

            DataGrid1.DataBind()

        Catch ex As DB2Exception

            Response.Write("There appears to be some error " & ex.Message)

        End Try

Now that we have everything in place. Let’s Run our Page.

You must have a page like in Figure 1.4

                                                Figure 1.4

First enter your name and e-mail ID and click on Register.

You will get a message stating Guest Added.

Then click on the Load Table. You will get your Details displayed in the Data Grid.

Wow, Now wasn’t that easy to work with DB2 using ASP.NET.

There are more incredible things you can do , I’ll let you know about more options in the forth coming series J.

Please do send me your valuable feedback on this article “ RAD WEB APP DEV With DB2 using ASP.NET” in the subject line.

Please send me all your feedbacks and comments to anilm001@gmail.com

IBM DB2 UDB Logo  is Copyright of IBM (International Business Machines) Corporation, USA and other International Countries where present.

Microsoft Windows and Microsoft Visual Studio.NET logo are copyright of Microsoft Corporation, USA and other International Countries where present.