perfectxml.com
 Basic Search  Advanced Search   
Topics Resources Free Library Software XML News About Us
home » info bank » articles » Microsoft InfoPath 2003 Guided Tours: InfoPath and Oracle Sat, Feb 23, 2008

Microsoft InfoPath 2003 Guided Tours: InfoPath and Oracle

Author: Darshan Singh (Managing Editor, perfectxml.com)
Last Updated: September 01, 2003
Code Download.

Abstract

In this walk-through you'll learn about designing an InfoPath form that connects to Oracle database server.

The Oracle database has two tables, tblMortgageRates that contains details on current mortgage rates (we'll read from this table) and tblApplications that stores new loan applications (we'll write into this table).

We'll write a .NET Web service that provides methods to read and write Oracle data. There are many ways in which you can access Oracle data from .NET. For example, you can use .NET Provider from Oracle, or one from Microsoft, or from DataDirect, or use classic OLEDB Provider from .NET, or use ADO, and so on.

The InfoPath form will call Web service method to get the mortgage rates and allow user to choose one while submitting a new mortgage loan application. The InfoPath form-submit action would then call another Web service method that would write data into Oracle tblApplications table.

Getting Ready

I used the following to create this sample application:
  • Oracle9i Enterprise Edition Release 9.2.0.1
  • Visual Studio .NET 2003
  • .NET data provider from Oracle
  • .NET data provider from Microsoft
  • Connect for .NET from DataDirect
  • IIS 5.0 with .NET Framework 1.1
  • InfoPath 2003
The Visual Studio .NET 2003 Web service solution included with this walk-through references Oracle data provider DLLs from Microsoft, Oracle, and DataDirect. You can remove the reference to one or two of these three if you don't want to try all three providers. I wanted to explore all three providers and hence added reference (and separate Web methods) that used all three providers (discussed later).

Download/Reference links

The above figure highlights some of the methods in which Oracle data can be accessed from the .NET code. The provider from DataDirect offers certain advantages, such as, it is the only provider today which is 100% managed (others go via OCI, which is unmanaged code); also it does not require installation of database client libraries as it directly connects to the Oracle database over TCP/IP.

In this tutorial, we'll write .NET Web service which will connect to Oracle database. I'll show you how to use data providers from Microsoft, Oracle and DataDirect.

I assume that you have some familiarity with Oracle, .NET, and InfoPath. If you are new to InfoPath, read the introductory tutorial at http://www.perfectxml.com/InfoPath.asp.

Let's get started and see how to read from and write into Oracle from within InfoPath 2003.

Steps

 Oracle script to create tables, stored procedures and insert sample data:
As mentioned earlier, we'll create one table to store mortgage rates and the other to store loan applications. In addition to table creation, the following script contains few INSERT statements to initialize mortgage rates table, and it also creates stored procedures, one to get mortgage rates and other to insert a record into loan applications table.

Start Oracle SQL*Plus and connect to it using scott/tiger@your-oracle-service-name. Let's create a SQL script file named loan.sql. Type ed loan on the SQL prompt and write following text in the script file:
DROP TABLE tblMortgageRates;
DROP TABLE tblApplications;

CREATE TABLE tblMortgageRates
(
    RateID        Number
        CONSTRAINT pkRateID PRIMARY KEY,
    NoOfYears    Number
        CHECK (NoOfYears >= 3),
    Description    VARCHAR2(50),
    Rate        Number(9,3)
        CHECK (Rate >= 3),
    Points        Number(5,2)
        CHECK (Points >= 0),
    ClosingCostIncluded    Number
);


CREATE TABLE tblApplications
(
    FullName     VARCHAR2(50),
    PropertyValue    Number(8,2),
    DownPayment    Number(8,2),
    RateID_        Number,
    IsRateLocked    Number,
    IsPreApproved   Number
);

INSERT INTO tblMortgageRates 
    VALUES (1, 3, '3 year ARM', 4.125, 0, 1);

INSERT INTO tblMortgageRates 
    VALUES (2, 5, '5 year ARM', 4.75, 0, 1);


INSERT INTO tblMortgageRates 
    VALUES (3, 3, '30 year fixed', 6.25, 0, 1);


CREATE OR REPLACE PACKAGE pkgMortgageApp AS
    TYPE typeRateCursor IS REF CURSOR;

    PROCEDURE sproc_GetRates (rateCursor out typeRateCursor);

    PROCEDURE sproc_CreateApplication 
        (fullName in VARCHAR, 
        propertyValue in NUMBER,
        downPayment in NUMBER,
        rateID_ in NUMBER,
        isRateLocked in NUMBER,
        isPreApproved in NUMBER,
        insertResult out NUMBER
        );

END pkgMortgageApp;
/

CREATE OR REPLACE PACKAGE BODY pkgMortgageApp AS
    
    PROCEDURE sproc_GetRates (rateCursor out typeRateCursor)
    IS
        BEGIN
            OPEN rateCursor FOR SELECT * FROM tblMortgageRates;
        END;


    PROCEDURE sproc_CreateApplication 
        (fullName in VARCHAR, 
        propertyValue in NUMBER,
        downPayment in NUMBER,
        rateID_ in NUMBER,
        isRateLocked in NUMBER,
        isPreApproved in NUMBER,
        insertResult out NUMBER
        )
    IS
        BEGIN

            INSERT INTO tblApplications
                VALUES (fullName, propertyValue, downPayment, rateID_,
                isRateLocked, isPreApproved);

            insertResult := 1;
    
            EXCEPTION 
                WHEN OTHERS THEN
                insertResult := 0;
        END;

END pkgMortgageApp;
/
The above script first drops the two sample tables (you'll get error on these two lines for first execution of the script, ignore them). Next, it creates two tables required for this walk-through, inserts few records into tblMortgageRates tables, and creates two stored procedures.

The first stored procedure uses a ref cursor to return all the rows from the tblMortgageRates table; and the second stored procedure inserts a record into tblApplications table.

Save the above SQL script file and return to SQL*Plus and type @loan on the SQL prompt to execute the above script. Make sure that tables and stored procedures are created and that the mortgage rates table contains sample data.

We now have the Oracle tables and stored procedures ready. Let's now write an ASP.NET Web service that connects to Oracle and calls the stored procedures to get the mortgage rate details or to create new loan application.

 ASP.NET Web service to access Oracle data:
Start Visual Studio .NET 2003 and create a new C# ASP.NET Web service project named OraDataService. Rename the default class and Web service filename to HomeLoan.
using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Diagnostics;
using System.Web;
using System.Web.Services;
using System.Web.Services.Protocols;
using System.Xml;

namespace OraDataService
{
    /// <summary>
    /// Sample Web service to illustrate accessing Oracle data from InfoPath
    /// </summary>
    [WebService (Namespace="http://Office2003/Oracle",
         Description="Sample Web service to illustrate Oracle data access from InfoPath")]
    public class HomeLoan : System.Web.Services.WebService
    {

To use Microsoft's Oracle provider, Add Reference to System.Data.OracleClient.dll; To use the provider from Oracle, Add Reference to Oracle.DataAccess.dll; and To use the provider from DataDirect, Add Reference to DDTek.Oracle.dll.

The first Web method we need is the one that calls pkgMortgageApp.sproc_GetRates stored procedure. Here is how you do it using managed Oracle .NET provider from Microsoft:
    [WebMethod (
         Description="Access tblMortgageRates. Use <b>Microsoft .NET Provider for Oracle</b>.")]
    public XmlNode GetRates_UsingMSProvider()
    {
        System.Data.OracleClient.OracleConnection conn = 
        new System.Data.OracleClient.OracleConnection();

        try
        {
            //    Open the connection
            String connString = "Data Source=ORA9I; User Id=Scott; Password=Tiger;";
            conn.ConnectionString = connString;
            conn.Open();

            //    Create the command object
            System.Data.OracleClient.OracleCommand comm = 
            new System.Data.OracleClient.OracleCommand(_spRates, conn);
            comm.CommandType = CommandType.StoredProcedure;

            //    Create the parameter object, and associate with command object
            System.Data.OracleClient.OracleParameter param = 
                new System.Data.OracleClient.OracleParameter("rateCursor", 
                System.Data.OracleClient.OracleType.Cursor);
            param.Direction = ParameterDirection.Output;
            comm.Parameters.Add(param);

            //    Create the adapter and fill the DataSet
            System.Data.OracleClient.OracleDataAdapter adapter = 
                new System.Data.OracleClient.OracleDataAdapter(comm);
            DataSet dsRates = new DataSet();
            adapter.Fill(dsRates);

            //    Convert the DataSet into XmlNode
            return DataSetToXmlNode(dsRates);
        }
        catch(System.Data.OracleClient.OracleException oraExp)
        {
            return ErrorNode(oraExp);
        }
        catch(Exception exp)
        {
            return ErrorNode(exp);
        }
        finally
        {
            if(conn != null && conn.State == ConnectionState.Open)
                conn.Close();
        }
    }
If you have some experience with ADO.NET, the above code should make sense immediately. The connection, command, adapter, etc. classes and the programming model is exactly same as it is with .NET OleDb or SqlClient classes, except the classes are preceded by the word Oracle (for example, OracleConnection).

The above code creates and initializes the connection, command, parameter objects, and creates a DataSet using OracleDataAdapter class. Finally, it calls a private method named DataSetToXmlNode method, which, as the name suggests, accepts a DataSet instance, and returns XmlNode representation of it. Here is how this private members looks like:
    #region Private Members
    String _spRates = "pkgMortgageApp.sproc_GetRates"; 

    private XmlNode DataSetToXmlNode(DataSet inputDS)
    {
        XmlDataDocument xmlDataDoc = new XmlDataDocument(inputDS);
        return xmlDataDoc.DocumentElement;
    }

    private XmlNode ErrorNode(Exception exp)
    {
        XmlDocument xmlDoc = new XmlDocument();
        xmlDoc.LoadXml("<Error/>");
        xmlDoc.DocumentElement.InnerText = exp.ToString();
        return xmlDoc.DocumentElement;
    }
    #endregion
So far you learned how to use Oracle .NET Provider from Microsoft to call the stored procedure; here is how you do the same thing if you are using .NET provider from Oracle or DataDirect. Note the connection string change in DataDirect data access code; other than that, the code looks very similar to the one we saw earlier.
    [WebMethod (
       Description="Access tblMortgageRates. Use <b>Oracle .NET Provider (ODP.NET)</b>.")]
    public XmlNode GetRates_UsingODP()
    {
      Oracle.DataAccess.Client.OracleConnection conn = 
        new Oracle.DataAccess.Client.OracleConnection();

      try
      {
        //  Open the connection
        String connString = "Data Source=Ora9i; User Id=Scott; Password=Tiger;";
        conn.ConnectionString = connString;
        conn.Open();

        //  Create the command object
        Oracle.DataAccess.Client.OracleCommand comm = 
          new Oracle.DataAccess.Client.OracleCommand(_spRates, conn);
        comm.CommandType = CommandType.StoredProcedure;

        //  Create the parameter object, and associate with command object
        Oracle.DataAccess.Client.OracleParameter param = 
          new Oracle.DataAccess.Client.OracleParameter("rateCursor", 
          Oracle.DataAccess.Client.OracleDbType.RefCursor);
        param.Direction = ParameterDirection.Output;
        comm.Parameters.Add(param);

        //  Create the adapter and fill the DataSet
        Oracle.DataAccess.Client.OracleDataAdapter adapter = 
          new Oracle.DataAccess.Client.OracleDataAdapter(comm);
        DataSet dsRates = new DataSet();
        adapter.Fill(dsRates);

        //  Convert the DataSet into XmlNode
        return DataSetToXmlNode(dsRates);
      }
      catch(Oracle.DataAccess.Client.OracleException oraExp)
      {
        return ErrorNode(oraExp);
      }
      catch(Exception exp)
      {
        return ErrorNode(exp);
      }
      finally
      {
        if(conn != null && conn.State == ConnectionState.Open)
          conn.Close();
      }
    }
    [WebMethod (
       Description="Access tblMortgageRates. Use <b>DataDirect Connect for .NET</b>.")]
    public XmlNode GetRates_UsingDDProvider()
    {
      DDTek.Oracle.OracleConnection conn = 
        new DDTek.Oracle.OracleConnection();

      try
      {
        //  Open the connection
        String connString = "SID=ORA9I; User Id=Scott; Password=Tiger;";
        conn.ConnectionString = connString;
        conn.Open();

        //  Create the command object
        DDTek.Oracle.OracleCommand comm = 
          new DDTek.Oracle.OracleCommand(_spRates, conn);
        comm.CommandType = CommandType.StoredProcedure;

        //  Create the parameter object, and associate with command object
        DDTek.Oracle.OracleParameter param = 
          new DDTek.Oracle.OracleParameter();
        param.ParameterName = "rateCursor";
        param.Direction = ParameterDirection.Output;
        comm.Parameters.Add(param);

        //  Create the adapter and fill the DataSet
        DDTek.Oracle.OracleDataAdapter adapter = 
          new DDTek.Oracle.OracleDataAdapter(comm);
        DataSet dsRates = new DataSet();
        adapter.Fill(dsRates);

        //  Convert the DataSet into XmlNode
        return DataSetToXmlNode(dsRates);
      }
      catch(DDTek.Oracle.OracleException oraExp)
      {
        return ErrorNode(oraExp);
      }
      catch(Exception exp)
      {
        return ErrorNode(exp);
      }
      finally
      {
        if(conn != null && conn.State == ConnectionState.Open)
          conn.Close();
      }
    }

We need one more Web method that we'll use to call pkgMortgageApp.sproc_CreateApplication stored procedure. For brevity, I'll show you how to do this using Data Provider from Oracle; as an exercise you can implement the same thing using provider from Microsoft and DataDirect.
  [WebMethod (
    Description="Insert into tblApplications. Use <b>Oracle .NET Provider (ODP.NET)</b>.")]
  public int AddApplication_UsingODP
   (String Fullname, double PropertyValue, double DownPayment, 
   int RateID, int IsRateLocked, int IsPreApproved)
  {
   Oracle.DataAccess.Client.OracleConnection conn = 
    new Oracle.DataAccess.Client.OracleConnection();

   int resultValue = -1;

   try
   {
    // Open the connection
    String connString = "Data Source=ORA9I; User Id=Scott; Password=Tiger;";
    conn.ConnectionString = connString;
    conn.Open();

    // Create the Command object and add parameters
    Oracle.DataAccess.Client.OracleCommand comm = 
     new Oracle.DataAccess.Client.OracleCommand(
      "pkgMortgageApp.sproc_CreateApplication", conn);

    comm.CommandType = CommandType.StoredProcedure;

    comm.Parameters.Add(
     new Oracle.DataAccess.Client.OracleParameter("fullName", Fullname));
    comm.Parameters.Add(
     new Oracle.DataAccess.Client.OracleParameter("propertyValue", PropertyValue));
    comm.Parameters.Add(
     new Oracle.DataAccess.Client.OracleParameter("downPayment", DownPayment));
    comm.Parameters.Add(
     new Oracle.DataAccess.Client.OracleParameter("rateID_", RateID));
    comm.Parameters.Add(
     new Oracle.DataAccess.Client.OracleParameter("isRateLocked", IsRateLocked));
    comm.Parameters.Add(
     new Oracle.DataAccess.Client.OracleParameter("isPreApproved", IsPreApproved));

    // Out parameter
    Oracle.DataAccess.Client.OracleParameter paramOut = 
     new Oracle.DataAccess.Client.OracleParameter("insertResult", 
     Oracle.DataAccess.Client.OracleDbType.Single);
    paramOut.Direction = ParameterDirection.Output;
    comm.Parameters.Add(paramOut);

    comm.ExecuteNonQuery();

    resultValue = Convert.ToInt32(paramOut.Value);
   }
   catch(Oracle.DataAccess.Client.OracleException oraExp)
   {
    
    SoapException soapExp = 
     new System.Web.Services.Protocols.SoapException(
     "OracleException: " + oraExp.ToString(), 
     SoapException.ClientFaultCode, Context.Request.Url.AbsoluteUri,
     oraExp);

    throw soapExp;
   }
   catch(Exception exp)
   {
    SoapException soapExp = new SoapException(
     "Exception: " + exp.ToString(), 
     System.Web.Services.Protocols.SoapException.ClientFaultCode, Context.Request.Url.AbsoluteUri,
     exp);

    throw soapExp;
   }
   finally
   {
    if(conn != null && conn.State == ConnectionState.Open)
     conn.Close();
   }

   return resultValue;
  }
Once again, the above code should look very familiar if you have ever called a stored procedure from ADO.NET. It creates instances of connection, command, adds parameters, and calls ExecuteNonQuery on the command.

It would be a good idea to test the Web service methods. Run the solution from within Visual Studio .NET 2003 IDE or browse to the Web service page from Internet Explorer and try out the Web service methods.

We now have the backend all ready to go. It's now time to create an InfoPath form and hook it up with the Web service methods.

 Designing an InfoPath Form:


3.1  Start Microsoft InfoPath 2003, Click on File | Design a Form... and select New Blank Form from the task pane.

3.2  From the Layout task pane, drag and drop Table with Title on to the form.

3.3  Change the title to New Loan Application and description text to Sample InfoPath solution that connects to Oracle via .NET Web service.

3.4  Click on Format | Color Schemes... and choose a color scheme that you like for the top title table.

3.5  From the Layout task pane, drag and drop Two-Column Table on to the form.

3.6  In the above two-column table, first row first column, write Applicant Name and in the next column drag and drop a Text Box control from the Controls task pane. Resize the row hight accordingly.

3.7  Right click on the text box and select Text Box Properties..., change the field name to FullName and check Cannot be blank checkbox. Click OK.


3.8  Press tab to add a second row. On second row first column, write Property Value. Drag and drop text box control on the adjacent column. Right click on this text box and select Text Box Properties... and change the field name to PropertyValue, change the data type to Decimal (double), click on Format... button and change the format to Currency, press OK. Check Cannot be blank checkbox, click on Data Validation button and click Yes on the message box, click Add... and specify a condition that Property value must be a positive number above 25000 (just for example).


3.9  Press tab to add third row. Write Down Payment in the left column. On the right column, drag and drop yet another text box control. Right click on this text box, select Text Box Properties, change the field name to DownPayment, data type to Decimal (double), click on Format button, and set it to Currency. Check Cannot be blank and then click on Data Validation and add a condition that if DownPayment is greater than or equal to PropertyValue, then display an error message.

3.10  Add one more row. On left column, write Rate, and on the right column, drag and drop a Drop-Down List Box and a Rich Text Box below the select combo box.

We are going to bind this Drop-Down List Box (select combo box) with the one of the Web service method that returns the mortgage rates. And when one is selected, we'll display the rate details in the Rich Text Box below the combo box.

Right click on Drop-Down List Box, select Drop-Down List Box Properties, change the field name to RateID, data type to Whole Number (integer), check Cannot be blank, and select the radio button that says "Look up in a database, Web service, or file".

We'll add the Web service method as a secondary data source. You can do it from here or upfront from the Tools | Secondary Data Sources menu item. Let's do it from here in this example.

After clicking on Secondary Data Source... button, click Add..., and select Web service as the source, click Next. Type the Web service WSDL URL; for example, on my machine it is http://localhost/OraDataService/HomeLoan.asmx?wsdl, click Next. Select one of the GetRates_XXX methods, for example, GetRates_UsingMSProvider, click Next thrice, leave the data source name as GetRates_UsingMSProvider and click Finish, click Close. Click on XPath expression icon next to Entries text box and select Table node under NewDataSet. Click OK.


Make sure that the Value in the following dialog is RateID and change the Display name to Description.


3.11  Just to make sure that the combo box is populated with the data from Web service, click on Preview Form and see if combo box contains three entries. Note that for the very first time you preview the form, it might not work and report a timeout error. This is because the ASP.NET compiles the Web service code and takes longer than subsequent requests; and InfoPath thinks that the Web service method timed out. At this stage, if you click on the Preview Form button again, you should see a screen similar to one shown below:


3.12  In step 3.10, we added We added a rich text box below the Mortgage Rate combo box. Letís now populate this text box with the rate detail when the selection is changed from the Mortgage rate combo select box.

Right click on rich text box below rate combo box and select Rich Text Box Properties.... Change the field name to RateDetails; click on Display tab and check Read-only and click OK.

Right click on RateID drop down list and select Properties; click on Data Validation button and select the OnAfterChange from the Events list and click Edit...


Clicking on the Edit... button on the above dialog box will start the Microsoft Script Editor; write the following code under msoxd_my_RateID::OnAfterChange(eventObj):
function msoxd_my_RateID::OnAfterChange(eventObj)
{
   if (eventObj.IsUndoRedo)
   {
      // An undo or redo operation has occurred and the DOM is read-only.
      return;
   }

   //   Find out the current Rate ID
   var selectedRateID = XDocument.DOM.selectSingleNode("/my:myFields/my:RateID").text;
   if(selectedRateID.length <= 0)
   {
      XDocument.DOM.selectSingleNode("/my:myFields/my:RateDetails").text = "";
      return;
   }
   
   //   Select the Table node matching the currently selected RateID
   var rateNode = 
      XDocument.DataObjects("GetRates_UsingMSProvider").DOM.selectSingleNode(
         "//Table[RATEID='" + selectedRateID + "']");
      
   //XDocument.UI.Alert(rateNode.xml);
   
   //   Get other rate details and show it in the Rich text box
   var closingCost;
   if (rateNode.selectSingleNode("CLOSINGCOSTINCLUDED").nodeTypedValue == "1")
   {
      closingCost = " is ";
   }
   else
   {
      closingCost = " is not ";
   }
   
      
   XDocument.DOM.selectSingleNode("/my:myFields/my:RateDetails").text = 
      rateNode.selectSingleNode("DESCRIPTION").nodeTypedValue + 
      " at " + rateNode.selectSingleNode("RATE").nodeTypedValue + "%. " + 
      rateNode.selectSingleNode("POINTS").nodeTypedValue + 
      " points. Closing cost " + closingCost + "included.";
}

Preview the form once again; change the mortgage rate selection, and as you change the selection in the combo box, you should now see that the rich text box is populated with other details about the selected mortgage rate.

Note that make sure to change/update the secondary data source name (GetRates_UsingMSProvider) in the above example, if you use a different Web method or give a different name to the secondary data source.

3.13  Return to the form design mode. Add two more rows. The label on first newly added row should read "Is Rate Locked?" and on the second row, the label on the left column should read "Is Pre-approved". Add two combo select boxes on each row, name them as IsRateLocked and IsPreApproved and add two elements under each select combo box with display text as Yes and No having values as 1 and 0, respectively. Make sure to change the data type to integer.


3.14  Drag and drop a Button control, right click on it and select Properties... Change the button label to be Submit and Action to be Submit. Changing the action to Submit will popup the following dialog box (or click on Define Action Parameters... button):


Select Enable Submit and make sure Submit to a Web service is select as a method, and then click on Select a Web service button.

Type the WSDL URL of the Web service that we created earlier (for example, as http://localhost/OraDataService/HomeLoan.asmx?wsdl on my machine). Click Next.

Select AddApplication_UsingODP Web method. Click Next.

On the Parameter mapping panel, click on Modify... button and make sure that parameters are mapped as shown in the following screenshot:


Click Next, then Finish, and then click OK twice.

The form and backend is all ready to be tested. Click on the Preview Form toolbar button, enter some values and click on Submit.


Goto Oracle SQL*Plus and run SELECT * FROM tblApplications; and make sure that the data that you just entered in InfoPath form is present in the database table.

Summary

In this guided tour, you learned about connecting to Oracle database from InfoPath via a .NET Web service.
  1. As a first step, you created sample tables, data, and stored procedures in Oracle.
  2. Second step was to write a C# ASP.NET Web service to read and write Oracle data by calling stored procedures. You saw how to use data providers from Oracle, Microsoft and DataDirect.
  3. In third and final step you learned about designing the InfoPath form, adding and binding controls to Web service methods and writing script event handlers.
Submit any feedback or comments on this guided tour by sending an email to me at darshan@perfectxml.com.

Code Download.
  Contact Us | E-mail Us | Site Guide | About PerfectXML | Advertise ©2004 perfectxml.com. All rights reserved. | Privacy