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