Integrating an external database into Sharepoint

Question

One of my current projects involves integrating a client's database into an overall CRM system design.

Part of the process was updating their legacy database and porting it over to SQL Server. That's been done without any problem.

We need to be able to provide data input and display for this database within Sharepoint. I've created External Content Types for each table, and set permissions in BCS properly to be able to display and modify the data. The auto-created External Lists work like a charm for those simple operations.

My problem stems from my attempt to customize the table input forms. The external database is properly architected, and has relationships set up to ensure data integrity. When I created the External Content Types, I added in the proper associations to mirror the relationships in the database.

The auto-generated Infopath forms adds External Item Pickers for these associations, so I know they're set up correctly. I want to be able to convert these into dropdowns, however; it makes it easier on the user, and less pop-up screens is always a good thing.

Anyone familiar with Infopath and external lists knows where I'm going with this. Adding extra data connections to an external list form doesn't work; when attempting to publish the form, the Design Checker comes back with "Additional data connections not supported".

There are other forms I want to create that will leverage several tables at the same time; this is just the first one I want to do.

All my Googling to work around this issue just comes back with, "Yup, that's the way it works. Sorry." which is singularly unhelpful to resolving the issue.

Migrating the database into Sharepoint is not an option; there are other business requirements that leverage SQL Server functionality.

So, my question is, how can I go about keeping the database operations inside Sharepoint, while keeping the database itself outside of Sharepoint? Am I even going about this in the correct manner?

Answer 1

In terms of technology opportunities and capabilities you could : 1. Create your own Visual Studio BDC Model where you could create your own Entities, with relationships mirroring your configurations - additionally you could rely on Stored Procedures for various operations not available otherwise, e.g. Updates across multiple tables, View selecting from multiple tables. 2. Personally, i try to avoid InfoPath as much as I can (beyond the fact that they shall be obsolete and replaced by HTML5 soon enough, according to MS). Once you have your model deployed, you wouldn't care much about it.

Short answer, always consider that you need several operations required by any ENtity in BDC, but also you could add beyond CRUD, via Stored Procedures.

See http://www.shillier.com/archive/2010/10/18/how-to-use-a-stored-procedures-in-business-connectivity-services.aspx and http://blogs.msdn.com/b/jysaikia/archive/2010/12/15/a-step-by-step-guide-in-designing-bcs-entities-by-using-a-sql-stored-procedure.aspx

Answer 2

Depending on how much if any of the external list / content type features you are looking to use I would be on board with Marius as far as avoiding InfoPath and the BCS goes.

In a situation like this I would create a custom web part and use plain old ASP.NET combined with the data access type of your choice (Entity Framework, LINQ to Entities, SQL / Stored Procedures, etc.) to create the functionality you need including Create Forms, Edit Forms, Display Forms, and Views. Once you've got working web parts just pop them onto some web part pages and set up the navigation to point to these pages as you need.

From doing this you do a bit of "reinventing the wheel" as far as some of the basic features go but on the flip side you gain a lot more control in your forms and going straight for the data without SharePoint as a middle man should yield you some performance gains as well if your external lists got too large.

Answer 3

I had a similar issue with an application I was working on at work. I tried customizing the BCS input pages, writing my own BCS data access components (makes the data access overly complex), and a few other options including LINQ to SQL in a custom web part.

Ultimately, what I ended up doing was creating an external (WCF) web service hosted outside of SharePoint to perform all the data access. Then within my custom web part I added web service as a Web Service Reference. This allowed me to only grant the single service account the web service runs under read/write access to the underlying database and allows SharePoint to manage the permissions of who can access that web part and service.

This greatly reduced the complexity of my data access when using a complex data model and abstracted it outside of SharePoint.