SharePoint 2010: Connect to external database

Question

I am in the process of writing a service application that will (or should depending on how this goes) allow users to perform CRUD operations on an external database.

The database only has two tables, there are no dependencies.

The service application is for populating two pages full of this data on each user's profile page. It will also allow users to maintain entries relating to themselves.

I have run into a problem concerning actually connecting to the new database in a dynamic way.

I could use Fluent NHibernate, as that is what I am most familiar with, but it seems like overkill.

I have considered EF, but from what I have heard, the EF that comes with .NET 3.5 sucks.

Lastly I dont have any experience with BCS, so I am not sure of its viability as an option.

Does anyone here have any experience doing something similar with other technology? Or are there options I havent considered?

Thanks!

Answer 1

I think you might be right that using NHibernate or EF would be an overkill for two tables, and so is BCS.

What I was using for external databases in my SharePoint project was LINQ-to-SQL mapping. I created database, then I dragged tables from server explorer into linq-to-sql mappings editor in visual studio. After that, you have context, entity classes with relations and it's not bad I think.

Then I just deploy database to production, put connection string somewhere where it is easily accessible (web.config) and it works.

Answer 2

The best way to integrate an external content type like DB or WCF service to SharePoint and make CRUD operations is by using a BCS solution. Since BCS has a deep integration to SharePoint, it will be quite easy to attach your external database to SharePoint. For example show and make CRUD operations in a nice list that your end users already know how to use.

Answer 3

If you want to use the default SharePoint UI for doing CRUD operations then BCS or LINQ-to-SQL will work great. I needed a custom UI so it was easier to develop an app for SP 2013 (much like a visual web part in 2010) that connects to the external source itself and provides the UI for doing CRUD operations. You can still store the connection strings in the SharePoint config for easy managing.