SPFx to connect to local SQL Server Tables,Stored Procedures & view.. Approaches to follow

Question

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:-

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

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.

Answer 1

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.

Answer 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

2. 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

3. 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