1

I am working on a migration from SharePoint 2010 to SharePoint Online. Where inside the on-premises the customer has a configurable Web Part (Server-side of course), which allow users to do the following:-

  1. Specify a connection string to connect to on-premises SQL server

  2. Specify the Table or Stored Procedure or View to connect with.

  3. optionally to pass a Parameter to the Database.

After that the web part will show the results from the database and allow the users to filter the data which will be shown in a tabular format.

Here is a screenshot of the web part setting:-

enter image description here

Here is a screenshot of a result from one web part instance:-

enter image description here

So can we build similar web part using SPFx? If the answer is Yes, then is there an available 3rd party web part we can benefit from? Finally , i am also open to other approaches to achieve our work other than build/using SPFx web part.

Please note that I am already aware that i can create API for the DB and connect SharePoint Online to On-prem DB using gateways.. but my main question is; how we can develop a dynamic web part (similar to the current on-prem web part), which allow us to show & filter data from on-prem database's Tables,Views & Stored procedure, by just specifying which components (table,view or Stored Procedure) we want to show the data from .. Is there a documentation to create such SPFx web part? or if there are 3rd part SPFx web part that we can benefit from?

jessehouwing
  • 106,458
  • 22
  • 256
  • 341
John John
  • 1
  • 72
  • 238
  • 501
  • Have you considered building a web app (not just API) that can have a user form for input and query the database and display the results? – Donald Koscheka Feb 23 '22 at 22:03

3 Answers3

0

You have two possibilities to solve this challenge:

  1. If you only have the possibility to refactor this Farm Solutions Web Part to SPFx, consider using a local data gateway to help as an artifact to generate the connections from the local environment to the cloud, and suddenly, you can persist these registered connections into a restricted access SharePoint List (for example) and consume with a combo box. But if you can't make progress with this approach, you'll need to consider the 2nd possibility (this one is more difficult);
  2. Develop a Provider-Hosted SharePoint Add-In, which can be hosted on on-premises IIS and can connect to your various on-premises databases as a shared connection string in your example image above.
Antonio Leonardo
  • 1,805
  • 1
  • 8
  • 18
0

Beside SPFX you could consider using Power BI if the $$$ or data doesn’t need to be private. With Power BI you could connect to sql,execute procedure to filter data then build the dashboard which then can be publish and display on SharePoint Online using out of box web part.

wabi-sabi
  • 11
  • 3
0

I've using Microsoft Graph Connector to pull On-prem SQL data into Microsoft Search (via Microsoft API), and then combine that with PnP Modern Search for query/filter results by selecting Graph as data source.

This feature is listed under MS Search & Intelligence. You can

  1. Specify a connection string to connect to on-premises SQL server

  2. Specify the Table or Stored Procedure or View to connect with

  3. -- You define it in the PnP <Modern Search web part. Note that you can only filter/query/refinable if you've defined it in step 1 or 2 above.

wabi-sabi
  • 11
  • 3