0

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?

Josh
  • 1,058
  • 9
  • 27

2 Answers2

2

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.

Joseph Caruana
  • 2,241
  • 3
  • 31
  • 48
  • 1
    Another thing that BCS provides is the ability to perform CRUD (or more specifically, CUD) operations without having to write any custom GUI code... which might or might not be enough to make it worth using. – Hutch Dec 17 '14 at 18:04
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.

David Sterling
  • 385
  • 1
  • 5
  • interesting comment - I have not used SSS myself. there is a link here http://technet.microsoft.com/en-us/library/ee806866.aspx. I will have a look later. @Josh - you might want to check this link as well. (+1) – Joseph Caruana Aug 13 '12 at 14:51
  • Good answer, SSS is a little complex for most people's needs IMHO. For the most part external database queries you'll end up with the connectionstring anyways? I had a similar question but ended up building a WCF data service (Astoria) to connect into Oracle. – esp Aug 14 '12 at 02:59
  • Secure Store Service can be used to store login information, not actually provide access to a database. In fact, BCS does in fact use Secure Store service to maintain login information. I have also written custom solutions that access databases directly (server-side) but keep their login info in SSS, so SSS is basically a non-issue in deciding between BCS and direct access – Hutch Dec 17 '14 at 17:59