Connecting to an external SQL Database from within SharePoint code

Question

I have some event handlers which are tracking list items in my SharePoint lists. I would like to Audit the use of these in an external database (SQL 2005) outside of SharePoint.

What is the best practice for doing something like this? I can't find many examples of this being done but it seems like it would be something that is done frequently. Would love to get pointed in the right direction on this one!

Normally to get a DB connection in a .net app, I'd create a connectionString in the web.config. Should I be modifying the web.config for the web application that the code will run on?

It would be handy to be able to use LINQToSQL for my DB inserts/updates - is this possible from within SharePoint code?

Thanks

Answer 1

I am not sure I understand your auditing question fully to comment on it.... There is built in auditing functionality that you can utilize if it meets your requirements. Ted Pattison discussed this in an Office Space column a while back.

In regards to you other questions...

You can put a connection string in your web.config, we do this often. There can be concerns in SharePoint manually updating the web.config due to the fact you can have multiple servers in a farm and you don't want the web.config to get out of sync. SharePoint has a SPWebModificationClass you can to automatically update the web.config. I have run into issues with this using FBA (see this SO question).

You can use LINQtoSQL for your database access. However, if you are using SP 2007 instead of SP 2010, you will need to update your web.config to reference all of the proper DLLs. You can what references are required by looking at an ASP.NET 3.5 web.config and comparing it to SharePoints. I am sure there are links on how to do this also.

Hope that helps get you started