Connecting to the database and best practices.

mike55

mike55

Well-known member
Joined
Mar 26, 2004
Messages
726
Location
Ireland
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.
 
I use stored procedures for arguments and parameters, and open and close the connection for each stored procedure call.
 
I try and use parameters, to avoid the possibility of SQL injection attacks.

Its good practice as well to assume your client will be compromised (its 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
 
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 www.microsoft.com/patterns and look at either the DataApplication block or the EnterpriseLibrary).
 
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
 
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.
 
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.
 
I rarely disagree with PlausiblyDamp, but I wouldnt recommend the DataApplication block or anything out of Enterprise Library. Its extremely bloated and the new version will try to write an entry into the registry if it wasnt installed on the host machine (the error handling block) - which wont happen on a shared environment - trust me, learned that one by experiance.

Also I wrote my own DataApplication block which doesnt 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.
 
Back
Top