Using .Net to Call iSeries Stored Procedures

Howard,

I need to call a stored procedure using .Net. Do you have any experience with this? ~Ivana

Dear Ivana,

.Net is starting to catch on as a web programming environment, and it is possible to call stored procedures using an ODBC driver and the System.Data.Odbc framework classes. These classes came out after the initial release of .Net but are shipped in the .Net 1.1 framework. If you do not have the ODBC framework classes, you can download them from http://www.microsoft.com/downloads/details.aspx?FamilyID=6ccd8427-1017-4f33-a062-d165078e32b1&DisplayLang=en. Of course, to make your like easier you could just download the .Net Framework 1.1 release from http://www.microsoft.com/downloads/details.aspx?FamilyID=262d25e3-f589-4842-8157-034d1e7cf3a3&DisplayLang=en

Let’s create a stored procedure and then go through what it takes to call the procedure. Later, I will show you how to code a working web page with ASP.Net that calls a stored procedure and returns the results into a text field.

Creating the procedure

First, create a table called SEQCTRL in the SQLBOOK library. This table will hold information about sequence numbers. Here is the SQL for creating the table and loading it with initial data:

CREATE TABLE SQLBOOK.SEQCTRL

(SEQID CHAR(10) NOT NULL PRIMARY KEY,

VLU INTEGER NOT NULL WITH DEFAULT);

INSERT INTO SQLBOOK.SEQCTRL VALUES ('WO',1);

INSERT INTO SQLBOOK.SEQCTRL VALUES ('PO',1);

Next, issue the following SQL Create Procedure statement to create a procedure called SEQCTRL in the SQLBOOK library. This procedure takes an input argument for the kind of sequence number that you want and returns that sequence number in the second argument.

CREATE PROCEDURE SQLBOOK.GETSEQ

( IN REQKEY CHARACTER(10),

INOUT RETVAL INTEGER )

RESULT SETS 0

LANGUAGE SQL

NOT DETERMINISTIC

/*Begin Procedure Code*/

BEGIN

DECLARE C1 CURSOR FOR

SELECT VLU + 1 FROM SQLBOOK . SEQCTRL

WHERE SEQID = REQKEY FOR UPDATE OF VLU ;

OPEN C1 ;

FETCH C1 INTO RETVAL ;

UPDATE SQLBOOK . SEQCTRL SET VLU = RETVAL

WHERE CURRENT OF C1 ;

END

Ok, we are ready to call the procedure using .Net. The following code uses the classes available in the System.Data.Odbc namespace to call the procedure. It could be placed into a web form or it could be executed in response to a button click event in a windows form.

‘Create the ODBC objects

Dim OdbcCon As System.Data.Odbc.OdbcConnection

Dim OdbcCmd As System.Data.Odbc.OdbcCommand

‘Instantiate new instances

OdbcCon = New System.Data.Odbc.OdbcConnection

OdbcCmd = New System.Data.Odbc.OdbcCommand

‘Open a connection to an iSeries data source

OdbcCon.ConnectionString = "DSN=My400;UID=HARNER;PWD=Secret"

‘Set up the procedure call

OdbcCmd.CommandText = "{CALL SQLBOOK.GETSEQ( ?, ? )}"

OdbcCmd.CommandType = CommandType.StoredProcedure

‘Accosicate the command with the connection

OdbcCmd.Connection = OdbcCon

‘Open the connection

OdbcCon.Open()

‘Create the parameter objects to pass and get data from procedure

OdbcCmd.Parameters.Add("", Odbc.OdbcType.Char, _

10).Direction = ParameterDirection.InputOutput

OdbcCmd.Parameters.Add("", Odbc.OdbcType.Int _

).Direction = ParameterDirection.InputOutput

‘set the value of the parms to pass

OdbcCmd.Parameters(0).Value = "WO"

OdbcCmd.Parameters(1).Value = 0

‘call the procedure

OdbcCmd.ExecuteNonQuery()

‘put the results into a textbox control

txtResult.Text = OdbcCmd.Parameters(1).Value

‘close the connection

OdbcCon.Close()

First the code declares the two objects we need to call a procedure, an ODBC Connection object and an ODBC Command object. The code then sets various properties of the command and connection objects to establish a connection to the iSeries, tell the iSeries what procedure we wish to call, set up the parameters that we are going to pass to the procedure and initialize their values. Finally, the code calls the ExecuteNonQuery method of the command object to cause the iSeries to execute the stored procedure and return the results. The resulting parameter can then be examined by looking at the Value property of the commands parameters collection.

Calling a procedure from an ASPX web page

If you have IIS, (Internet Information Server), installed on your machine you can set up a web page that calls a stored procedure. In addition to IIS you will need to have the .Net framework installed on your machine. First, make a subdirectory within your wwwroot folder called AS4Samp and then copy the following HTML code into a file called PTEST.ASPX.

<%@ Page Language="vb" src="PTEST.ASPX.VB" inherits="MyTest"%>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">

<HTML>

<HEAD>

<title>Calling a 400 Procedure</title>

</HEAD>

<body >

<TABLE id="Table1" cellSpacing="1"

cellPadding="1" width="300" border="1">

<TR>

<TD align="center" bgColor="blue" colSpan="3"><FONT

color="#ffffff">Procedure Calling .Net Example</FONT></TD>

</TR>

<TR>

<TD align="right" colSpan="" rowSpan="">ID Field:</TD>

<asp:TextBox id="txtID" value="WO" runat="server">

</asp:TextBox></TD>

</TR>

<TR>

<TD align="right" colSpan="" rowSpan="">Result:</TD>

<asp:TextBox id="txtResult" runat="server" ReadOnly="True">

</asp:TextBox>

</TD>

</TR>

<TR>

<asp:Button id="Button1" runat="server" Text="Submit">

</asp:Button></TD>

</TR>

</TABLE>

</form>

</body>

</HTML>

The code above creates a simple web form and uses server side ASP controls to render text boxes. Note the server side ASP controls are highlighted in green. These controls will be evaluated on the server and you can set their properties and call their methods via code. So, the above code renders an HTML page which has three server side controls, txtID, txtResult and Button1. Also note that the txtResult control is set to read-only mode. To use this form create a file called PTEST.ASPX.VB and paste the following code into that file:

imports microsoft.visualbasic.strings

imports system.data

imports system.data.odbc

Public Class MyTest

Inherits System.Web.UI.Page

Protected WithEvents txtID As System.Web.UI.WebControls.TextBox

Protected WithEvents txtResult As System.Web.UI.WebControls.TextBox

Protected WithEvents Button1 As System.Web.UI.WebControls.Button

‘Here is code for the button click event

public Sub Button1_Click(ByVal sender As System.Object, ByVal e As

System.EventArgs) Handles Button1.Click

Dim OdbcCon As OdbcConnection

Dim OdbcCmd As OdbcCommand

OdbcCon = New OdbcConnection

OdbcCmd = New OdbcCommand

OdbcCon.ConnectionString = _

"DSN=Holley;UID=HARNER;PWD=secret"

OdbcCmd.CommandText = "{CALL SQLBOOK.GETSEQ( ?, ? )}"

OdbcCmd.CommandType = CommandType.StoredProcedure

OdbcCmd.Connection = OdbcCon

OdbcCon.Open()

OdbcCmd.Parameters.Add("", System.Data.Odbc.OdbcType.Char, _

10).Direction = ParameterDirection.InputOutput

OdbcCmd.Parameters.Add("", System.Data.Odbc.OdbcType.Int _

).Direction = ParameterDirection.InputOutput

OdbcCmd.Parameters(0).Value = Trim(txtID.Text)

OdbcCmd.Parameters(1).Value = 0

OdbcCmd.ExecuteNonQuery()

txtResult.text=OdbcCmd.Parameters(1).Value

OdbcCon.Close()

End Sub

end class

The code above is associated with the web page by the first line in the HTML file, <%@ Page Language="vb" src="PTEST.ASPX.VB" inherits="MyTest"%>. This line informs .Net that the programming language of the page is VB and that the source code for the page exists in the file PTEST.ASPX.VB and this page inherits a class called MyTest. This technique of coding is called code behind, as it allows you to place the application code in a file separate from the HTML presentation code. When the web server renders the HTML page, it will read this directive and then bring all of the code in the PTEST.ASPX.VB file into this page and compile the page. Code behind makes it easy to separate application logic from your HTML display.

The VB Code creates a public class called MyTest which will be used to encapsulate the logic we need to call the stored procedure and handle events on the HTML page. This class consists of a public subroutine called Button1_Click which will handle the Click event fired when a user clicks the button on the HTML form. The clicking of the button causes the form to be posted back to the web server, which then calls the Button1_Click subroutine in the MyTest class. The Button1_Click subroutine then connects to the iSeries, sets up the parameters and calls then stored procedure. Any results from the procedure call are returned into the web form by setting the .Text property of the txtResult text box to the value stored in the ODBC commands parameter collection.

To view the sample, simply open a browser window and surf to the address localhost/PTEST.ASPX. You should see something similar to the following:

Procedure Calling .Net Example
ID Field:
Result:

Once the button is clicked, you should see the results of the procedure call in the Results text box. I hope this information helps!