Which pattern is better for SqlConnection object?

Suggest going with DataAccess2 It's a personal preference though. Some might even suggest your class be static It'd be difficult to say that one is more performant than the other. You're on the path of IDisposable which is great.

Suggest going with DataAccess2. It's a personal preference though. Some might even suggest your class be static.It'd be difficult to say that one is more performant than the other.

You're on the path of IDisposable, which is great. I'd be happy to read and maintain both styles shown above in your question. Consider having your DAL be able to read the connection string from a .

Config as well, rather than exclusively allowing the value to be passed in the constructor. Public DataAccess2(string connStr) { this. ConnectionString = connStr; } public DataAccess2() { this.

ConnectionString = ConfigurationManager. ConnectionStrings"foo". ConnectionString; } Consider wrapping your SqlCommand in a using as well.

Using (var conn = new SqlConnection(connectionString)) { using(var cmd = conn.CreateCommand()) { } }.

2 I would advise against having the class read the connection string. Let the application read configuration information and pass it to the consumer; there's no reason to limit the portability of the class. – Adam Robinson Jul 31 '10 at 16:24 @Adam: I'd agree with you on the limiting of portability.

I'll modify to include a ctor which would allow the caller to supply the value. – p. Campbell Jul 31 '10 at 16:27.

There's no real way to answer this question. The short, canonical answer is that the connection should stay alive for the lifetime of your unit of work. Because we have no way of knowing how DataAccess is used (does it exist for the lifetime of your application, or do you instantiate it and dispose it whenever you do something?), it's impossible to give a concrete answer.

That being said, I would recommend the first pattern, but instantiate and dispose of your DataAccess object as needed; don't keep it around longer than necessary.

I think it depends on how your DataAccess object is intended to be used, if it's used within a 'using' clause then the connection is guaranteed to be disposed of after it's done. But in general I prefer the second pattern as the sql connection is created and disposed of within the Execute method so it's less likely to be left open when you forget to dispose of your DataAccess object. Considering that sql connection can be a scarse resource I think every attempt should be made to ensure that they're not wasted.

The first will result in errors if you make concurrent calls. The second will ensure you use a clean connection for each command resulting in more connections being made. I agree with the statements above that it depends on the scenario for use, to get over the problem related to the first I have a wrapper that needs to use such a pattern so I set a field value boolean to show that a command is being executed on the connection already then "queue" the next command for execution.

There will of course be situations where you may prefer to use multiple connections ...

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.