Sql Connection Class Class vs. Module - .NET

Greetings all.

I need to get some opinions from those outside of my work environment to see if I can get a good general consensus on project design.

We have programmed a database DLL for our department, this DLL can get the SQL Connections, build DataTables, DataSets, Execute Sql Commands, build readers etc. it basically takes all basic methods for DB connectivity and wraps them up into one DLL so everyone uses the same DLL and works consistently with it instead of having everyone making their own connection class. Which can get problematic.

We are also trying to push into OOP, and in doing so we are not allowing people to use modules. But in my mind a module might be the best fit for a object reference for this DLL since it will be shared throughout an entire project where as we will have to basically create a new instance of the DB object. Each class would have to have it's own DB object, and this is where the 开发者_开发知识库overhead comes in, why bother having 8 different Sql Connection objects going for each class when you can use a shared one through a module. Unless we run through inheritance and have it at the base level of all inheritance levels.

Basically is there some other way I'm not thinking about? I'm just fishing for some ideas.

Thanks guys!

---

It makes no sense to ban modules as not being object-oriented. Modules in VB.NET are object-oriented. A module is compiled as a type with a private constructor and with all of its methods and fields set to shared (aka static in C#).

I think as you imply, the more important question is whether your database connection class should be a singleton (i.e. shared) or not. Singletons have design issues, especially around threading. In effect, a singleton is just a collection of global variables, and we know how well that works in most systems. Also SqlConnection pooling is very efficient.

Usually a singleton will be the wrong choice for data access, but of course it depends on your exact situation.

---

It's generally best to stay clear of singletons/static classes/modules if you can. I do understand the feeling that makes you believe that there may be performance/resource reasons not to create new objects all the time, but in these cases, that is not generally a problem.

First, objects in general are very lightweight in .NET. More specifically, though; the ADO.NET objects you will be using here - DbConnection, DbCommand, DbDataReader, etc, are all also very lightweight.

But further than that, caching and re-using DbConnection in particular can be a bad thing; ADO.NET is set up by default to use connection pooling, and therefore the best way to use DbConnection is to open it as late as possible, and close it as early as possible... then create a new one for your next command. ADO.NET will pull these connections from the pool, and it will all be very efficient.

---

That said, though; I do think it's a bit much to go as far as totally banning ALL static classes/modules. There are certainly plenty of good uses for them.

---

There are different approaches for different projects. Software design patterns are targeted against high volume scenarios and how to make them scalable. That will affect design, where and how to open/close connections.

But the point that applies to all is:

Single responsiblity: the code to open the connection must be in a single place. It can be Module which is static class, or a class that you create instance of. If you or your colleagues are opening connection in more than one place then something is wrong. This kind of code is laready done in various Microsoft frameworks already such as Application Blocks and later with other stuff.

So which one is better: static helper method or instance of class that implements the function? If you are unit testing then static is no good since you cannot mock it (although it can be done with special mocking tools such as TypeMock or JustMock)