AspAlliance.com LogoASPAlliance: Articles, reviews, and samples for .NET Developers
URL:
http://aspalliance.com/articleViewer.aspx?aId=1420&pId=-1
Introduction to AS400 Programming Using .NET
page
by Nidal Arabi
Feedback
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 38481/ 39

Overview

This article is just a humble introduction to programming AS400 DB2. I did some research and found really no complete article on how to approach the AS400 machine using the .NET platform. There are really no big issues here, but I will tackle the basic foundations of creating a code file with simple to follow steps, so bear with me.

Problem

Every other computer application would include some codes file, for example the Department table. In this article I will show step-by-step how to create the table as well as the stored procedures associated with this table on AS400 using simple SQL/400 statements.

Solution

Let us start with the DB2 AS400 part. It is recommended that you have client access express installed on your PC and be able to access AS400 trough the IBM ISeries navigator or through 5250 terminal. However, you should be able to connect through a simple telnet client program provided with Microsoft Windows.

After you log on to your AS400 machine, you should be able to create a library for our tests (I prefer to call this library TESTNET). Whatever you choose as a name, make sure you remember it for replacing purposes.

After creating the library, access the SQL/400 command window through STRSQL.

Listing 1 - Create Table Listing

/* Creating table TESTNET.TBLDEPT */
CREATE TABLE TESTNET.TBLDEPT 
( DEPTID INTEGER NOT NULL , DEPTNM VARCHAR (30) NOT NULL , UNIQUE (DEPTID)  ) ;

Listing 2 - Insert Procedure

CREATE PROCEDURE TESTNET.INSDEPT ( 
      IN P_ID INTEGER , 
      IN P_DESC VARCHAR(30) ) 
      LANGUAGE SQL 
      SPECIFIC TESTNET.INSDEPT 
      NOT DETERMINISTIC 
      MODIFIES SQL DATA 
      CALLED ON NULL INPUT 
      INSERT INTO TESTNET . TBLDEPT 
      ( DEPTID , DEPTNM ) VALUES ( P_ID , P_DESC )  ; 
  
COMMENT ON SPECIFIC PROCEDURE TESTNET.INSDEPT 
      IS 'INSERT DEPARTMENT' ;

Listing 3 - Update Procedure

CREATE PROCEDURE TESTNET.UPDDEPT ( 
      IN P_ID INTEGER , 
      IN P_DESC VARCHAR(30) ) 
      LANGUAGE SQL 
      SPECIFIC TESTNET.UPDDEPT 
      NOT DETERMINISTIC 
      MODIFIES SQL DATA 
      CALLED ON NULL INPUT 
      UPDATE TESTNET . TBLDEPT 
      SET DEPTNM = P_DESC 
      WHERE DEPTID = P_ID  ;

Listing 4 - Delete Procedure

CREATE PROCEDURE TESTNET.DELDEPT ( 
      IN P_ID INTEGER ) 
      LANGUAGE SQL 
      SPECIFIC TESTNET.DELDEPT 
      NOT DETERMINISTIC 
      MODIFIES SQL DATA 
      CALLED ON NULL INPUT 
      DELETE FROM TESTNET . TBLDEPT 
      WHERE DEPTID = P_ID  ; 
  
COMMENT ON SPECIFIC PROCEDURE TESTNET.DELDEPT 
      IS 'DELETE DEPARTEMENT' ;

Listing 5 - Select All Procedure

CREATE PROCEDURE TESTNET.SELDEPT ( ) 
      DYNAMIC RESULT SETS 1 
      LANGUAGE SQL 
      SPECIFIC TESTNET.SELDEPT
      NOT DETERMINISTIC 
      READS SQL DATA 
      CALLED ON NULL INPUT 
      BEGIN 
DECLARE DEPTCUR CURSOR FOR SELECT * FROM TESTNET. TBLDEPT ; 
OPEN DEPTCUR; 
END  ; 
  
COMMENT ON SPECIFIC PROCEDURE TESTNET.SELDEPT
IS 'DEPARTEMENT' ;

Listing 5 - Select By Department Id

CREATE PROCEDURE TESTNET.SDEPTID (IN P_ID INTEGER ) 
      DYNAMIC RESULT SETS 1 
      LANGUAGE SQL 
      SPECIFIC TESTNET.SDEPTID
      NOT DETERMINISTIC 
      READS SQL DATA 
      CALLED ON NULL INPUT 
      BEGIN 
DECLARE DEPTCUR CURSOR FOR SELECT * FROM TESTNET. TBLDEPT  WHERE P_ID = DEPTID; 
OPEN DEPTCUR; 
END  ; 
COMMENT ON SPECIFIC PROCEDURE TESTNET.SDEPTID
IS 'DEPARTEMENT BY ID';

Let us move to the .NET part. The programmer should have ISeries Client Access for Windows CD in order to be able to install the .NET driver. After launching the setup, select add/remove and select .NET Data provider if not selected and complete the installation.

First, you should download and install the .NET Driver for AS400 DB2. Once you have finished, you are ready to start following the step-by-step instructions to create your ASP.NET page for this code table.

Creating Your AS400 .NET Application

Follow these easy steps to create your AJAX solution.

1.    Start your Visual Studio 2005 IDE.

2.    Choose Create Web Site from the menu.

3.    In this sample application we will use the Visual Basic language for the sample application.

4.    Name the solution AS400Dept.

5.    Choose default Web Site.

6.    Choose File System in the location box.

7.    Click ok to create the project.

8.    Visual Studio will create your project with a Default.aspx page and most probably a readme.txt. Go ahead and get rid of the latter file.

9.    Open Default.aspx page in design view.

10. Right click the solution name, select Add Reference and select IBM DB2 for ISeries .NET Provider. Click ok.

11. Drag two panels from Toolbox, name them PnlMain and PnlDetail and make the PnlDetail invisible by default.

12. Inside the panel, main drag a GridView and name it GrdMain and make its style as professional. Please note Listing 12 for HTML addition for the GridView in the first line (section datakeynames to allow for edit and delete option afterwards).

13. In the details panel add the text Dept Id followed by a text box named TxtDeptId.

14. Add the text Dept Name followed by a textbox named TxtDeptName.

15. Also add two buttons with the following specifications:

16. Id: BtnSave and Text: Save

17. Id: BtnCancel and Text: Cancel

18. Above the Main Panel create a new button with id BtnCreate and Text Create Department.

19. In the GridView add Edit Command as well as the delete command.

20. For The Create Button use Listing 6.

21. For the cancel button code use Listing 7.

22. There is some Code that should be common to all functions that should be created. This code should go after the class directly. One is to prepare connection and the other is to convert anything that has been returned as a dataset. You can find the code in Listing 8. (Remember to replace the required parameters in the connection string.)

23. For the save button, check listing 9.

24. Now we turn to loading the grid with data selected from AS400 stored procedure. You can find the code in listing 10.

25. The delete event should be handled now inside the grid view. Listing 11 shows the code needed.

26. Lastly, we have the edit part. You can find the code in Listing 13. The save button command code would change to accommodate update. The new listing for the save button is 14.

Listing 6 - Create Button Code

Protected Sub BtnCreate_Click(ByVal sender As Object, ByVal e As System.EventArgs)_
 Handles BtnCreate.Click
  PnlDetail.Visible = True
  PnlMain.Visible = False
  TxtDeptId.Text = ""
  TxtDeptName.Text = ""
  TxtDeptId.ReadOnly = False
End Sub

Listing 7 - Cancel Button Code

Protected Sub BtnCancel_Click(ByVal sender As Object, ByVal e As System.EventArgs)_
 Handles BtnCancel.Click
  PnlDetail.Visible = False
  PnlMain.Visible = True
End Sub

Listing 8 - Common Shared Code Section

#Region "GeneralFunctions"
    Dim WSConn As New IBM.Data.DB2.iSeries.iDB2Connection
    Dim WSCommand As New IBM.Data.DB2.iSeries.iDB2Command
    Dim WSDatareader As IBM.Data.DB2.iSeries.iDB2DataReader
    Dim WSDataSet As New Data.DataSet
 
    Public Sub ConvertReaderToDataset(ByVal ETableName As String)
        Dim SchemaTable As New Data.DataTable, MyDataTable As New Data.DataTable
        SchemaTable = WSDatareader.GetSchemaTable()
        MyDataTable.TableName = ETableName
        Dim intcounter As Integer
        For intcounter = 0 To SchemaTable.Rows.Count - 1
            Dim dataRow As DataRow = SchemaTable.Rows(intcounter)
            Dim columnname As String = CType(dataRow("ColumnName"), String)
            Dim column As DataColumn = New DataColumn(columnname, _
CType(dataRow("DataType"), Type))
            MyDataTable.Columns.Add(column)
        Next
        WSDataSet.Tables.Add(MyDataTable)
        While WSDatareader.Read
            Dim dataRow As DataRow = MyDataTable.NewRow()
            For intcounter = 0 To WSDatareader.FieldCount - 1
                dataRow(intcounter) = WSDatareader.GetValue(intcounter)
            Next
            MyDataTable.Rows.Add(datarow)
        End While
    End Sub
 
    Public Function Prepare_Env() As Boolean
        WSConn.ConnectionString = _
"datasource=AS400Name;userid=MyUser;password=MyPassword;"
        WSCommand.Parameters.Clear()
        Wsdataset.Tables.Clear()
        WSCommand.Connection = WSConn
        WSCommand.CommandType = CommandType.StoredProcedure
        Return True
    End Function
 
#End Region

Listing 9 - The Save Button Code

Protected Sub BtnSave_Click(ByVal sender As Object, ByVal e As System.EventArgs) _
Handles BtnSave.Click
  Prepare_Env()
  WSConn.Open()
  WSCommand.CommandText = "TESTNET.INSDEPT"
  WSCommand.CommandType = CommandType.StoredProcedure
  WSCommand.Parameters.Add("P_ID", TxtDeptId.Text)
   WSCommand.Parameters.Add("P_DESC", TxtDeptName.Text)
   WSCommand.ExecuteNonQuery()
   WSConn.Close()
End Sub

Listing 10 - The Form Load Event

Protected Sub form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) _
Handles form1.Load
  Prepare_Env()
  WSConn.Open()
  WSCommand.CommandText = "TESTNET.SELDEPT"
  WSCommand.CommandType = CommandType.StoredProcedure
  WSDatareader = WSCommand.ExecuteReader()
  ConvertReaderToDataset("TblDept")
  WSConn.Close()
  GrdMain.DataSource = WSDataSet
  GrdMain.DataMember = "TblDept"
  If Not Me.IsPostBack Then
    GrdMain.DataBind()
  End If

Listing 11 - The Delete Event Handling

Protected Sub GrdMain_RowDeleting(ByVal sender As Object, ByVal e As _
System.Web.UI.WebControls.GridViewDeleteEventArgs) Handles GrdMain.RowDeleting
  Prepare_Env()
  WSConn.Open()
  WSCommand.CommandText = "TESTNET.DELDEPT"
  WSCommand.CommandType = CommandType.StoredProcedure
  WSCommand.Parameters.Add("P_ID", GrdMain.DataKeys(e.RowIndex).Value)
  WSCommand.ExecuteNonQuery()
  WSConn.Close()
  Response.Redirect(Me.Request.Url.ToString())
End Sub

Listing 12 - The Extension of GridView

<asp:GridView ID="GrdMain" runat="server" CellPadding="4" ForeColor="#333333" 
    GridLines="None" DataKeyNames="DEPTID">
<FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
<Columns>
  <asp:CommandField ShowEditButton="True" />
  <asp:CommandField ShowDeleteButton="True" />
</Columns>

Listing 13 - Row Editing Listing

Protected Sub GrdMain_RowEditing(ByVal sender As Object, ByVal e As _
System.Web.UI.WebControls.GridViewEditEventArgs) Handles GrdMain.RowEditing
  Dim P_ID As Integer
  P_ID = GrdMain.DataKeys(e.NewEditIndex).Value
  PnlMain.Visible = False
  PnlDetail.Visible = True
  TxtDeptId.Text = P_ID
  TxtDeptId.ReadOnly = True
  Prepare_Env()
  WSConn.Open()
  WSCommand.CommandText = "TESTNET.SDEPTID"
  WSCommand.Parameters.Add("P_ID", P_ID)
  WSCommand.CommandType = CommandType.StoredProcedure
  WSDatareader = WSCommand.ExecuteReader()
   ConvertReaderToDataset("TblDept")
   WSConn.Close()
   TxtDeptName.Text = WSDataSet.Tables("TblDept").Rows(0).Item("DEPTNM")
End Sub

Listing 14 - The New Save Button

Protected Sub BtnSave_Click(ByVal sender As Object, ByVal e As System.EventArgs) _
Handles BtnSave.Click
  Prepare_Env()
  WSConn.Open()
  If TxtDeptId.ReadOnly Then
     WSCommand.CommandText = "TESTNET.UPDDEPT"
  Else
     WSCommand.CommandText = "TESTNET.INSDEPT"
  End If
  WSCommand.CommandType = CommandType.StoredProcedure
  WSCommand.Parameters.Add("P_ID", TxtDeptId.Text)
  WSCommand.Parameters.Add("P_DESC", TxtDeptName.Text)
  WSCommand.ExecuteNonQuery()
  WSConn.Close()
  Response.Redirect(Me.Request.Url.ToString())
End Sub
Conclusion

In this article I have examined how easy it is to create web pages based on AS400 databases and presented them in an easy to use manner.

Happy DotNetting as well as DB2; see you in the next article!


Product Spotlight
Product Spotlight 

©Copyright 1998-2024 ASPAlliance.com  |  Page Processed at 2024-03-19 3:19:03 AM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search