locked
Application use with cascading dropdowns and Oracle DB RRS feed

  • Question

  • I have a request to build a dashboard with CRUD for Oracle. I have tested this wit BCS and using an XML file which works.. To achieve this I have manually added an xml file (lightening Tools) in the CA and created an External Content Type all without VS.

    As part of the POC an additional and more complex requirements is now needed.

    A page with cascading dropdowns is needed, depending on choices the next dropdowns get dynamically built form a Oracle table(s).

    Then when last selection gets made a sql query is built (from the dropdowns) and executed against the Oracle DB, probably through a parameter. The results are then displayed lower in the page.

    SQL query will probably include joins etc.

    I’ve have a lot of issues connecting with Oracle as SPD does not support it up to date. However I think the above requirements have magnified the complications 10 fold and wondering if it’s all possible using SPD/XML Model/Infopath ?

    Should this be done in VS or is the above scenario not a good application for SharePoint?

    Thanks

    Thursday, August 11, 2016 10:57 AM

Answers

  • Hi orange juice jones,

    You could create cascading dropdown in InfoPath form. For more detailed information, you could refer to the article below.

    Create Cascading Dropdown in Browser enabled InfoPath form using InfoPath 2010.

    https://blogs.msdn.microsoft.com/bharatgupta/2013/03/07/create-cascading-dropdown-in-browser-enabled-infopath-form-using-infopath-2010/

    And you also could connect oracle database in InfoPath 2010. You could create custom web service in VS to connect the oracle database in InfoPath 2010. For more detailed information, you could refer to the article below.

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

    http://thebaretta.blogspot.sg/2014/12/use-infopath-2010-to-query-oracle.html

    Best regards,

    Sara Fan


    TechNet Community Support
    Please remember to mark the replies as answers if they help, and unmark the answers if they provide no help. If you have feedback for TechNet Support, contact tnmff@microsoft.com.

    • Proposed as answer by Victoria Xia Monday, August 22, 2016 9:56 AM
    • Marked as answer by Victoria Xia Tuesday, September 6, 2016 8:38 AM
    Friday, August 12, 2016 9:05 AM
  • In that case you can build a middle tier (web service) which can interact with the Oracle database, and it can be consumed from SharePoint using Javascript or from Apps/Addin. It keeps both the options open, and also in future if there is any need you can use it in BCS and also in workflows.

    ---
    Rajesh
    rjesh.com| @rjesh
    You don't need to buy me a beer, if helpful just smile, vote, and mark it as answer.

    • Proposed as answer by Victoria Xia Monday, August 22, 2016 9:56 AM
    • Marked as answer by Victoria Xia Tuesday, September 6, 2016 8:39 AM
    Saturday, August 13, 2016 2:02 PM

All replies

  • Hi,

    As you mentioned Oracle DB doesn't support SPD

    Oracle DB also not supported by Infopath. You can only make a data connection to a SQL Server Database.

    I believe that above scenario is not good for SharePoint.

    I can't bet for VS.

     

    Mark it as an answer if it helped you out. Regards Rahul Dagar

    Thursday, August 11, 2016 11:02 AM
  • Thanks Rahul

    Could anybody comment from a VS perspective please ?

    Thanks

    Thursday, August 11, 2016 11:30 AM
  • Hi,

    I still believe it will be hard via VS.


    Mark it as an answer if it helped you out. Regards Rahul Dagar

    Thursday, August 11, 2016 11:31 AM
  • By using Visual Studio, its quite possible. You'll end up in creating asp.net page with all the back end code and data layers to connect Oracle, just hosted in SharePoint layouts folder as an application page or as a visual webpart.

    My opinion would be keep it out of SharePoint, host this asp.net page in a different IIS web application. Believe me you won't regret this decision. 


    ---
    Rajesh
    rjesh.com| @rjesh
    You don't need to buy me a beer, if helpful just smile, vote, and mark it as answer.

    • Proposed as answer by croute1 Thursday, August 11, 2016 6:47 PM
    Thursday, August 11, 2016 11:45 AM
  • Hi Rajesh

    When you say 'My opinion would be keep it out of SharePoint' could you say why as I need to inform my team with a good technical reasons.

    Thanks

    Thursday, August 11, 2016 1:28 PM
  • Its not cloud proof

    Any full trust code is not supported in SharePoint online, in case of any plans to upgrade in future then this full trust code has to be re-written in app-model. Where as External IIS web app can be easily converted to app model. Microsoft recommends app-model for any customization, that should be the direction for any new customization for future proofing.

    Upgrade

    More effort is required to upgrade. This often means that the entire SharePoint farm is unavailable during upgrade of the customization. 



    ---
    Rajesh
    rjesh.com| @rjesh
    You don't need to buy me a beer, if helpful just smile, vote, and mark it as answer.

    Thursday, August 11, 2016 1:48 PM
  • Hi orange juice jones,

    You could create cascading dropdown in InfoPath form. For more detailed information, you could refer to the article below.

    Create Cascading Dropdown in Browser enabled InfoPath form using InfoPath 2010.

    https://blogs.msdn.microsoft.com/bharatgupta/2013/03/07/create-cascading-dropdown-in-browser-enabled-infopath-form-using-infopath-2010/

    And you also could connect oracle database in InfoPath 2010. You could create custom web service in VS to connect the oracle database in InfoPath 2010. For more detailed information, you could refer to the article below.

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

    http://thebaretta.blogspot.sg/2014/12/use-infopath-2010-to-query-oracle.html

    Best regards,

    Sara Fan


    TechNet Community Support
    Please remember to mark the replies as answers if they help, and unmark the answers if they provide no help. If you have feedback for TechNet Support, contact tnmff@microsoft.com.

    • Proposed as answer by Victoria Xia Monday, August 22, 2016 9:56 AM
    • Marked as answer by Victoria Xia Tuesday, September 6, 2016 8:38 AM
    Friday, August 12, 2016 9:05 AM
  • Hi

    Being that 2010 is now outdated and way forward is Apps or CSOM JavaScript, I'm wondering if a solution can be build with javascript or app model. I guess if its possible with the App Model then this could be external and would tie into what Rajesh mentioned earlier.

    Both JavaScript or App model will need to interact with Oracle tables somehow (Maybe through BCS or other method) and execute sql queries.

    Any advise pls?

    Thanks



    Friday, August 12, 2016 9:21 AM
  • In that case you can build a middle tier (web service) which can interact with the Oracle database, and it can be consumed from SharePoint using Javascript or from Apps/Addin. It keeps both the options open, and also in future if there is any need you can use it in BCS and also in workflows.

    ---
    Rajesh
    rjesh.com| @rjesh
    You don't need to buy me a beer, if helpful just smile, vote, and mark it as answer.

    • Proposed as answer by Victoria Xia Monday, August 22, 2016 9:56 AM
    • Marked as answer by Victoria Xia Tuesday, September 6, 2016 8:39 AM
    Saturday, August 13, 2016 2:02 PM