Submitting list data to SQL

Question

I'd like to submit data from a Sharepoint List to my SQL Database.

I want the user to be able to create a new list item, attach a file and then once added it will write to an SQL Database.

How would be the best way to approach this?

Answer 1

If your SQL is in azure or somewhere on the internet, you can use MS Flow, otherwise if your SQL accepts connection only from localhost, then you should use powershell.

https://social.technet.microsoft.com/Forums/lync/en-US/e3c333d7-6180-4a7e-927f-2e8a9690b598/sharepoint-list-data-into-sql-table-using-powershell-with-csom?forum=sharepointgeneral

Answer 2

Got this from a friend offline which mirrors a lot of what Bálint says:

Modern Sites - (Preferred approach / Current Best Practice) Option 1: Site Design/Site Script Route Essentially using the built-in site creation method within SharePoint online a site design (MS term) could be created with an associated site script (Another MS term, but basically a JSON config file). The site script then would call a Microsoft flow to do some custom handling, but really to just do a POST to an Azure storage queue to feed an Azure function (which is basically the AWS Lambda equivalent) which would then run whatever code needed and probably make some API calls to the 3rd party tool for list/DB sync and configure that side of the house.

Option 2: SP List/Flow/PNP Provisioning Engine Same as option one, but interchange the built-in site creation method/site with a list that triggers the downstream workflow (Flow/Azure Storage [Queue]/Azure Function + PNP Provisioning Engine to create the site)

Reference: https://docs.microsoft.com/en-us/sharepoint/dev/declarative-customization/site-design-overview

Answer 3

The easiest way is add connection string for your SQL DB in Web.config file and create your database layer class which contain method for insert finally in the visual web part call the created method