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.

Thanks in advance for any help.

2 Answers 2

3
+300

DUPLICATE: https://stackoverflow.com/questions/61283372/generate-rest-api-from-sql-server-database

This may not be the answer. No ready-to-use SPFx webpart right now, I'm afraid.

Nowadays, the base element of SPFx uses node.js & typescript. Yes, you can build one using SPFx and use node.js mssql module. But I have no experience using it. What I can describe the overview how you can develop, there are a couple of ways.

First, develop an SPFx webpart that uses node.js mssql module directly. But since SPFx is a client-side webpart (not like the old webpart uses server-side), you have to literally open MSSQL port to every user who uses this webpart.

Second (safest way), develop an API/web service (middle-man) that connects to MSSQL directly, then expose the CRUD method so that your SPFx can consume the data from the API.

Lastly, buy any third-party that can expose SQL Server as an API. There are some out there like DreamFactory or Skyvia for example. Note that I'm not the sales rep, just did search only.

5
  • I am already aware that i can create API for the DB and connect SharePoint 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, but just specify which component (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? Thanks
    – John John
    Feb 18, 2022 at 13:21
  • 1
    Maybe you can try this if it fits you? lightningtools.com/product/data-viewer-client-side-web-part Feb 19, 2022 at 14:12
  • 1
    Frankly, there is no specific documentation to create such SPFx web part as it's more elaborate & advanced unlike any other sample web parts out there. That's why DreamFactory, Skyvia, and LightningTools are selling those. It seems that your only option is to custom-develop yourself if buying is not your option. Feb 19, 2022 at 22:07
  • developing this from scratch will require lot of efforts from my side, as i work as a freelancer, so i do not have enough resources.. i already contacted LightningTools to check if they offer such as web part. thanks
    – John John
    Feb 20, 2022 at 2:03
  • Well then, no choice for you. Either you have to buy or develop your own. Feb 20, 2022 at 8:11
2

SPFx web parts are entirely client side, meaning they cannot directly access server side resources such as SQL Server without a web API. In order to access this data in your SPFx web part, you will need a server side intermediary of some sort. Here are your options:

  1. Create a web API by developing a web application that can access your SQL Server data e.g. an Azure function or web app which your SPFx web part can call to request the data. The below article provides a great example of how to consume a web API from SPFx:

Consume enterprise APIs secured with Azure AD in SharePoint Framework

  1. Utilise SharePoint Business Connectivity Services (BCS) to access your SQL Server data using external lists. Once you have configured your external lists appropriately, your SPFx web part can access the data from them using the SharePoint REST API. Though it should be noted that some people have experienced performance issues using BCS in SharePoint Online.

Manage Business Connectivity Service Applications

BCS REST API reference for SharePoint

  1. Use Microsoft Graph connectors to allow Microsoft Search to index your external data. You can then use the Microsoft Search API to access this data via your SPFx web part.

Edit: Once you have developed your server side intermediary e.g. API, then you can make your SPFx web part configurable in the same way classic web parts were. Below is an article on how to make your web part configurable using a property pane:

Make your SharePoint client-side web part configurable

Your web API/web app would need to provide the names of tables, stored procedures, views etc to your SPFx web part via HTTP calls so they can be displayed in the property pane. There is already loads of information on fetching information from SQL Server using .NET out there but you would be looking at things like the System.Data.SqlClient namespace.

System.Data.SqlClient Namespace

4
  • I am already aware that i can create API for the DB and connect SharePoint 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, but just specify which component (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? Thanks –
    – John John
    Feb 21, 2022 at 11:03
  • I have updated my answer with an article which describes how you would make your web part configurable once you have developed the intermediary which will interact with SQL Server e.g. a web API. Feb 21, 2022 at 13:09
  • i know how to create a configurable SPFx... but my question is about having an SPFx that can show info from databases similar to the on-prem one
    – John John
    Feb 21, 2022 at 14:30
  • 1
    I have added a further edit to my answer but essentially, you're going to have to fetch these details from SQL Server using an Azure function or web app. You can then use SPFx to call the API you have created to display the details in the property pane. Feb 21, 2022 at 15:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.