Connecting to the database and best practices.

[mike55]

Hi all,

 

I am interested in finding out how individuals communicate with their database in terms of opening & closing connections, and passing parameters and commands.

 

Mike55.

[samsmithnz]

I use stored procedures for arguments and parameters, and open and close the connection for each stored procedure call.

[rjonas]

I try and use parameters, to avoid the possibility of SQL injection attacks.

 

It's good practice as well to assume your client will be compromised (it's easy to reverse engineer a .net application) and someone will write an application to connect to your database, bypassing your client completely. To reduce the damage that could be done if a malicious application called your stored procedures with their own parameters, it might be worth validating things on the SQL side of things as well as the client and rejecting anything that looks suspicious.

 

Regards

Richard

[PlausiblyDamp]

I would always use stored procs if the DB supported them and parameterised queries otherwise. Parameters / arguments should always be validated at every public entrypoint (i.e. each layer in your infrastructure) to reduce the chance of exploits etc.

Rather than lots of explicit coding I would usually create a DataAccess layer to encapsulate the db specific stuff (either write my own or more likely go to http://www.microsoft.com/patterns and look at either the DataApplication block or the EnterpriseLibrary).

[mike55]

Cool, thanks for the replys. Out of interest, how do you handle to opening and closing of new connections, i.e. do you open one connection at the start and continue to pass that connection around the application for each user, or alternatively do you open and close a new connection as you require them?

 

Mike55

[PlausiblyDamp]

I would open a connection when I need it and close it as soon as I have finished, this lets .Net handle things like connection pooling more effectively. Keeping connections open 'in case I need it later' is usually more of a performance hit than opening them as and when you need them.

[rjonas]

I open and close the connection each time, wrapping it in a c# using block, using exactly the same connection string each time.

 

Connection pooling works behind the scenes to optimise performance, and if opening and closing connections is kept close to where the data is being used, any problems with connections are easier to find and debug.

[bri189a]

I rarely disagree with PlausiblyDamp, but I wouldn't recommend the DataApplication block or anything out of Enterprise Library. It's extremely bloated and the new version will try to write an entry into the registry if it wasn't installed on the host machine (the error handling block) - which won't happen on a shared environment - trust me, learned that one by experiance.

 

Also I wrote my own DataApplication block which doesn't have all the nice GUI for the config file, but provides a (IMHO) better factory pattern (one that follows the GoF pattern) and is only about 1/10th the size of an assembly.

 

Definitely agree though to use a seperate data access layer.