3

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?

  • Is bcs viable for this? I have a template site that has a matching azure DB. I'd like the lists to sync with SQL backing table. I'm doing large calculations for each site's lists and need SQL power. – JDPeckham Apr 24 '19 at 21:00
2
+50

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

|improve this answer|||||
  • I am online. Would this mean every site and for every list I need a custom flow? Wouldn't scale well. Any way to template the flows and SQL with the site? – JDPeckham Apr 26 '19 at 11:55
  • 1
    Yes, you have to create a custom flow for each list. Or maybe you can make one universal flow, where in the input parameters you pass the SP ListName, SQL TableName, and the mapping that which field is which column in sql. – Bálint Apr 26 '19 at 12:04
0

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

|improve this answer|||||
0

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

|improve this answer|||||
  • I am in SharePoint online. I don't think I have access to do this. – JDPeckham Apr 29 '19 at 12:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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