Use InfoPath 2010 to Query an Oracle Database via a Custom Web Service in SharePoint 2010

-
Wow that's a long heading...  Let me explain how this is done, and why this path was chosen out of all the options.

THE OPTIONS

Still to come...

SETTING UP

There's quite a few large steps to get this done, so I'll split it into the following sections, ensuring we test along the way to make sure we haven't missed a step:

  • Install & Configure Oracle Client on Web Server
  • Create custom Web Service in Visual Studio
  • Deploy Web Service to IIS
  • Build InfoPath Form
  • Configure InfoPath Services
  • Deploy InfoPath Form to SharePoint Environment

Install & Configure Oracle Client on Web Server

  • Ask your Oracle DBA what version of Oracle the database is running on.
  • Navigate here and download the corresponding Client (for this excercise get the 32-bit version if its an option): http://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html
  • Copy to the IIS Server (Web Front End)
  • Extract and install, here's the settings I used for the install
    • Install DB Software Only
    • Single Instance
    • Personal Edition
    • C:\Oracle
  • Once installed, you'll need to update (or if they don't exist, create) files in the following location: C:\Oracle\product\xx.x.x\[DBname]\NETWORK\ADMIN\
    • First create a SQLNET.ORA file (in notepad) and input the following two lines:
               SQLNET.AUTHENTICATION_SERVICES = (NTS)
               NAMES.DIRECTORY_PATH= (LDAP, TNSNAMES, HOSTNAME)

    • Then create a TNSNAMES.ORA file.  In this file you create the connection string that the Web Service uses to connect to the Database.  Here's an example of a Connection string, however, your Oracle DBA should be able to help with this too:                
          DBNAME =
          (DESCRIPTION =
          (ADDRESS_LIST =
          (ADDRESS = (PROTOCOL = TCP)(HOST = 192.0.0.1)(PORT = 1521)))
          (CONNECT_DATA = (SERVICE_NAME = DATABASENAME)))

Create custom Web Service in Visual Studio

Now there is a way to create a Web Service that can be deployed to SharePoint, but as soon as you add the Oracle complexity it requires many more steps.  So in this case, I've built a Web Service that can be deployed to a separate IIS Site, and consumed from inside sharepoint.

You 'should' build the Web Service in WCF (because ASP.NET is a dying breed), but I also did not go down that path because I was having troubles with it.

  • Open Visual Studio (I'm using 2010):  New > Project > Visual C# > Web > ASP.NET Empty Web Application
  • right-click the Web Application inside Solution Explorer and select:  Add > New Item > Web Service.
  • Right click the Web Applicaiton again in Solution Explorer and:  Add Reference... > Oracle.DataAccess
  • Right click Oracle.DataAccess in Solution Explorer (Under References Folder) and click properties.  Change the 'Copy Local' property to TRUE
  • inside your Web Service Code, input the following

        [WebMethod(Description="Used to take 1 value and query an Oracle Database for results, then output to CSV")]        public string QueryHDS(String queryvalue)        {            try            {            //Set DB Connection to TNSNames.ORA DataSource            string oradb = "Data Source=D[BName in TNS File];User Id=[username];Password=[password];"; 
            OracleDataReader dr;
                using (OracleConnection conn = new OracleConnection(oradb))                {                    conn.Open();                    using (OracleCommand cmd = new OracleCommand())                    {                        //Grab Values                        cmd.Connection = conn;                        cmd.CommandText = "SELECT * FROM TABLE SOMEWHERE WHERE COLUMN = "+ queryvalue
                        cmd.CommandType = CommandType.Text; 

                        //RUN QUERY!                        dr = cmd.ExecuteReader();
                    //Write data to File                    using (System.IO.StreamWriter file = new System.IO.StreamWriter(fileLocation))                    {                        while (dr.Read())                        {                            file.Write(dr[0].ToString() + ", " + dr[1].ToString() + ", " + dr[2].ToString();                            file.Write(file.NewLine);                                                    }
                        //Close reader when finished.                        dr.Close();                    }                    conn.Close();                }                                return fileLocation;            }            //If anything fails, catch and return error            catch(Exception e)            {                return e.ToString();            }        }    }

  • Build Solution
  • Run Solution and test.  All should be well.  customise as need be.

Deploy Web Service to IIS

  • Go to IIS and Create new Web blank Web Site
  • in IIS Create new Application Pool
  • Right-click Application Pool > Advanced Settings:  set Enable 32-bit applications to TRUE.  make sure it's running in .NET Framework v4.0.  Also set the identity to a real user account, not just 'ApplicationPoolIdentity'

  • When you are happy with your Web Service, it's time to deploy it for realz to IIS.
  • Right-click Solution and Publish > To File System > Desktop.
  • Copy the Folder to the root folder of the blank web site you just made.
  • Go back to IIS, hit refresh on the blank web site and right click the folder you just copied in IIS > Convert to Application
  • Select the App Pool you just created, and Connect as a specific user with enough access.
  • You should now be able to browse to the location of the .asmx file.  
  • TEST

Build InfoPath Form

  • Create a new InfoPath Form with two fields, one to query the DB Table, and one to recieve the result (success/failure).  Also create a button to initiate the Data Connection
  • Create Data Connection to Receive from a SOAP Web Service.  Don't auto retrieve values
  • On the form's submit button:  Using Rules, update all of the Data Connections Query value.  Then Query the Web Service, then Return the value to the second field you created
  • Save Data Connection as Data Connection File (UDCX) on the SharePoint Site that the form is being published to.

Configure InfoPath Services


  • Navigate to Central Admin > Infopath Services
  • Allow Cross Domain
  • Allow Auth to Data Sources
  • Set Data Connection Timeouts to 300000 and 300000 respectively.  This allows InfoPath to wait for those long queries to run and return (if you have a slow DB like me!)

Deploy InfoPath Form to SharePoint Environment

  • Publish InfoPath to sharepoint.  
  • ensure it opens up as a browser enabled form
  • test

Comments

Post a Comment

Popular posts from this blog

How to Copy/Duplicate a Table and Columns in a Dataverse Environment

-
Image
What If you need to do either of these things, then this article is for you: Create a copy of a table in the same environment as the original table Create a copy of a table but want a different prefix against custom column names Why In most circumstances, if you need to duplicate a dataverse table, I would suggest to bundle it inside a solution, and migrate the solution to another environment.   However, in the scenario where you need the duplicate table to be inside the same environment, here's a quick method that requires no manual creation of columns! How Short answer : Install  XRMToolBox , inside XRMToolBox, install and utilise the Clone Field Definitions Plugin  from the Tool Library. Pre-Tasks In order to clone a table with a new PREFIX value for the columns you first need to do the following: Create a new PUBLISHER with the new prefix you would like Create a new TABLE where you will be cloning all the fields. Tasks Download & Install  XRMToolBox On t...
Read more

Export Group Membership From Active Directory Using Power Query

-
Image
If you need to export a list of staff from an Active Directory Group, follow these steps.  Allows you to gather all the names & usernames of staff in a particular group and save the information into Excel. Step By Step How To: Open up Excel > Power Query Tab Click From Other Sources > From Active Directory: (In Excel 2016 this is found under: Data Tab > New Query > From Other Sources > From Active Directory) screenshots from Excel 2016 on left, Excel 2010 w/ Power Query Add-on right A popup will ask you to enter your domain name.  It should already be populated with the correct information but if not, input your companies domain name & Click OK: In the Navigator that opens up, expand your domain tree and double click group: You should now see a list of all groups in Active Directory in the Power Query Editor: In order to find & filter for a specific Group, Click the Down-Arrow for the 'distinguishedName' column > ...
Read more

Microsoft Flow - Apply To Each Limitation (5000 items)

-
Image
What Had a situation where I was pulling back 70,000 items in an array from a REST API call.  I wanted to create a SharePoint item for each of the items in an array, however the 'Apply To Each' action kept giving me the following error: ActionFailed . An action failed. No dependent actions succeeded. Why This was due to a Microsoft Flow limitation. When you are using the Free Version of MS Flow you are limited to 5,000 items in a loop .  Explained here:  https://docs.microsoft.com/en-us/flow/limits-and-config Workaround I tried my best to come up with a workaround, tried splitting up the array into batches of 5,000.  All to no avail.  in the end, gave up, and saved the array of items to a CSV and saved the CSV in a SharePoint Library instead. body('Parse_JSON')?['applist']?['apps'] The only other alternative I could come up with is to grab the 'first 4999 items' from the array, which does work with an expression like this: ...
Read more