I have to acquire some opinions from individuals outdoors of my work atmosphere to ascertain if I'm able to obtain a good general consensus on project design.
We've designed a database DLL for the department, this DLL could possibly get the SQL Connections, build DataTables, DataSets, Execute Sql Instructions, build visitors etc. it essentially takes all fundamental techniques for DB connectivity and systems them up into one DLL so everybody uses exactly the same DLL and works consistently by using it rather than getting everybody making their very own connection class. Which could get problematic.
We're also attempting to push into OOP, and by doing this we're not permitting individuals to use modules. But i believe a module may be the best fit for any object reference with this DLL since it will likely be shared throughout a whole project while we will need to essentially produce a new demonstration of the DB object. Each class would need to have it's own DB object, which is in which the overhead is available in, why bother getting 8 different Sql Connection objects opting for each class when use a shared one via a module. Unless of course we tell you inheritance and also have it in the base degree of all inheritance levels.
Essentially can there be another way I am not considering? I am just fishing for many ideas.
It can make no sense to prohibit modules as not object-oriented. Modules in VB.Internet are object-oriented. A module is put together like a type having a private constructor and wonderful its techniques and fields set to shared (also known as static in C#).
I believe while you imply, the greater real question is whether or not your database connection class ought to be a singleton (i.e. shared) or otherwise. Singletons have design issues, especially around threads. Essentially, a singleton is simply a assortment of global variables, and that we understand how well that actually works in many systems. Also SqlConnection pooling is extremely efficient.
Often a singleton would be the wrong option for data access, however it is dependent in your exact situation.
It's generally better to stay obvious of singletons/static classes/modules if you're able to. I actually do comprehend the feeling which makes you think there might be performance/resource reasons to not create new objects constantly, but in these instances, that's not generally an issue.
First, objects generally are extremely lightweight insInternet. More particularly, although the ADO.Internet objects you'll be using here - DbConnection, DbCommand, DbDataReader, etc, are extremely lightweight.
But beyond that, caching and re-using DbConnection particularly could be a bad factor ADO.Internet is to establish automatically to make use of connection pooling, and for that reason the easiest method to use DbConnection would be to open it up as late as you possibly can, and close it as soon as possible... then create a replacement for your forthcoming command. ADO.Internet will pull these connections in the pool, and it'll be extremely powerful.
Nevertheless, though I actually do think it's a little much to visit so far as totally banning ALL static classes/modules. You will find certainly lots of good ways to use them.
You will find different processes for different projects. Software design designs are specific against high volume situations and just how to ensure they are scalable. Which will affect design, where and just how to spread outOrnear connections.
But the reality that is applicable to any or all is:
Single responsiblity: the code to spread out the bond should be inside a single place. It may be Module that is static class, or perhaps a class that you simply create demonstration of. If you and your co-workers are opening connection in several place then something is wrong. This type of code is laready completed in various Microsoft frameworks already for example Application Blocks and then along with other stuff.
So which is much better: static assistant method or demonstration of class that implements the function? If you're unit testing then static isn't any good because you cannot mock it (although it is possible with special mocking tools for example TypeMock or JustMock)