I'm designing a Data Access layer for our team. I have some questions.
1) If I'm going to use a connection to call multiple commands, should I keep the connection open between calls? Or open/use/close, open#2/use#2/close#2? The "use" part will typically be using a SqlDataReader to stream the results and turn each row
into a business object.
2) Is it really worth it to delay opening my connection until after I create my command, set my parameters, etc. ?
Obviously a developer needs to remember not to do anything "slow" while reading rows or setting up parameters, but do I need to be superparanoid and truly open at the last possible second and close immediately? If it's truly cheap to do that I can
design my layer to do that. But I've always done like this:
using (SqlConnection connection = new SqlConnection("connectionString"))
using (SqlCommand command = new SqlCommand("text", connection))
// call a function that uses a reader and processes the results
// it will be building a list of business objects, and developers COULD in theory
// forget and do something "slow" while reading the records
using (SqlCommand command2 = new SqlCommand("text2", connection))
// do something e
View Complete Post