SQL Connection Methods and best practices

Question

So normally I just put my sql connection string in my asp.net web.config and reference it whenever I need to open a database connection, however this leaves me with referencing it all over my project. This also exposes my sql connection username and password in my web.config if it isn't encoded.

What are you best practices as far as keeping the connection methods in a class or class library? I saw a php tutorial that did this really well (but I can't find it again) and allowed for re-usability.

Answer 1

I would always keep the connection string in the web.config since the servers/database connections can always change, even if it's not common. To make it more comfortable to view in code you can always add something like this :

String m_Connection = ConfigurationManager.AppSettings["MyConnectionString"];

and then just reference m_Connection everywhere.

I would also always encrypt the connection string using an EncryptionProvider.

Great MSDN article : How To: Encrypt Configuration Sections in ASP.NET 2.0 Using DPAPI

Answer 2

I agree with @gillyb. In most cases the web.config is the place for the connection string(s). The other common alternative is a spring.Net config file if you make heavy use of dependedncy injection. The end result is the same except that the site will not rebuild if you change the Spring.config file, whereas it will if you change web.config.