Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 657
  • Last Modified:

Sharepoint foundation 2013 and SQL

I have a custom input form that updates a sharepoint work order list.  Ultimately, I would like to get the Work Order list data into SQL.

I created an external work order list that updates a SQL work order table -- it works.  The external work order list  and the sharepoint list have almost all of the same fields set up.

1) Is there a way to have the work order list update the external list (and the external list would automatically update the SQL table)?
2) Should I change the custom input form so that the form updates 2 lists at the same time - the sharepoint work order list and the external work order list (and the external list would automatically update the SQL table)?
3) There is a better solution ? :-)
0
cynthia4601
Asked:
cynthia4601
  • 3
  • 3
1 Solution
 
colly92002Commented:
I thnk you should use the Business Data Connectivity Service.
http://technet.microsoft.com/en-us/library/ee661740%28v=office.15%29.aspx

With this, you can define a single dataset and expose it as a SharePoint list.  You can then use all the features of SharePoint to manage that list (security, forms etc), with full CRUD operations being performed by the SharePoint front end.  You get to use all the nice client features of SharePoint but keep your data externally in SQL.
0
 
cynthia4601Author Commented:
Thanks for the response.  Yes - I have the external list working, which uses BCS.  My problem is that I want a SharePoint list to update the external list (the external list is already using BCS) and don't know how to do that.  I thought it might be more through an SSIS?  If I don't use the sharepoint list to update the external list, then I will have to rewrite the work order app to update the external list.  This will be a huge headache.

Thank you!
0
 
colly92002Commented:
BCS offers full CRUD operations.  You can configure it using Sharepoint designer as described here:
http://lightningtools.com/bcs/creating-an-external-content-type-with-sharepoint-designer-2013/

Here is a full overview of BCS for SharePoint 2013 (it is very powerful)
http://msdn.microsoft.com/en-us/library/office/jj163782%28v=office.15%29.aspx
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
cynthia4601Author Commented:
Thank you again -- however, I already have set up BCS and understand how powerful it is.  I need to know how to have an existing sharepoint list update the external list.
0
 
colly92002Commented:
If for some reason you want to have two non-synced data sources (a Sharepoint list and and separate SQL database table that is not linked via BCS) and you want to keep them "synced" then you are going to have to write some event receivers to work on create/delete/update in c#/Vb and create a sharepoint feature to deploy them.  However why would you when BCS does this all for you?  Also there is no way of doing this from the SQL direction.

BCS allows you to expose an external data source to SharePoint and use SharePoint to manage it as though it were an internal SharePoint list.  This includes writing back changes, deleting rows, creating rows etc.  You can edit the list in datasheet mode, or edit each line in the Sharpoint list form, or define your own form in SharePoint designer, its up to you.  As far as SharePoint and it's users are concered it is a SharePoint list, but you hold the data in an external SQL database, meaning you can do whatever else you want to do with it.  

IF you already have a list exposed through BCS, just set up the C, U, and D (Change, Update and Delete)  operations in Sharepoint designer as described in the first link.  You will already have the S (select) operation defined.

It is possible to extract SharePoint lists to SQL  using SSIS but this will be a job that is run at intervals so will not be perfectly upto date.  I do this as an overnight run to extract reporting data, but this does not seem to be what you want?  Furthermore, if you already have BCS already configured and selecting the data for the Sharepoint list  then it is MUCH simpler (and frankly just better) to use this than SSIS.
0
 
cynthia4601Author Commented:
Not the solution I really wanted but I guess it is the best solution.  I didn't want to have to re-write the application to update the external table but sounds like I should (and avoid the SSIS).  Thank you for your help.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now