Is it ok to ever use straight SQL (or an ORM) instead of BCS in SharePoint 2010

Question

I am working on a custom web part that I want to query and write to another database.

BCS seems to complicate this process more than helping it out so I am wondering if it is ever ok to use directly access SQL Server without the use of BCS?

Answer 1

Short answer ... go for it - use DB only and don't use BCS.


but it really depends a bit ...

If you use BCS, you can take advantage of some Sharepoint facilities such as search.

Please find the full advantages of BCS here:
http://msdn.microsoft.com/en-us/library/ee556440.aspx

But there is a big gotcha here .... If you are using Sharepoint Foundation there are a number of limitations on what you can do with BCS, so please keep this in mind.

One disadvantage of accessing through a database is that you no longer have one central location for your data, and this can have its problems. but if you design your architecture well, you should be fine.

So overall, if you do not need the advantages of BCS and you can design a solid architecture (perhaps service oriented), then my personal recommendation would be to use the database in your web-part.

Answer 2

Sorry - BCS is the wrong answer - you should be using the Secure Store Service; this is how we connect between 'outside' data sources and SharePoint. Otherwise, the custom web part must somehow embed the Login information (either through properties, the web.config or the Registry) - otherwise, SSS is where you want to go.