C # static database class?

Question

I have a database class that binds the following methods:

• public bool ExecuteUDIQuery (string query) // UDI = Update Delete Insert
• public bool ExecuteSelectQuery (string query)
• public bool ExecuteSP (string sp, string [,] parms)
• public int ExecuteSPReturnValue (string sp, string [,] parms)

Method results are stored in private data sets or in data sets. These objects are defined as getters.

There are about 10 classes that use the database class. Each class creates a database object of the class. Now I was thinking of making a static database class. Is that a good idea? If so, why? No, why not?

Answer 1

If I understand, the database class has some properties that save the query result? If so, you cannot make them static, since it is not thread safe. If the query result is stored in these properties, what happens if the second query is executed immediately after the first? It will be stored in the same static variable. The same goes for the web application: the result of another user browsing the site will change the results of the first user.

EDIT: To summarize, DO NOT make a static class when you save the result of queries in static variables, especially if the class is not used on the website, since the property value will be used among all visitors to your site. If 20 visitors execute the request at the same time, visitor 1 will see the results of the visitor’s request 20.

Answer 2

In your specific example, I would advise you not to put the class: you save the state in the database class, and by creating the static class, this state will be used among all classes using your database. In your current setup, each database instance maintains its own state, so there is no problem with the interaction between database calls.

If you want to reorganize the database class to return datasets when the method is called, you must make it static: there is no status information left in the database.

But since it is not: no, do not put the class static.

Answer 3

In addition to the rest of the thread safety comments, there is also the issue of parallelization. In your case, you will not be able to open several database connections at the same time, and you will not be able to execute several queries with parallels, even if the safety of the result streams is not a problem.

So, I agree with others, do not make a static class out of it.

Creating a static class may be convenient, but creating new instances of it will probably not be an expensive operation, so it’s probably not so much as to improve performance.

Edit:
I noticed in a comment that you want to use your class on a website. In this case, you REALLY should not do this. With a static database class, you can safely serve one query at any time, and that is not what you want.

Answer 4

It depends on which database or ORM you are using. But in my experience this seemed like a good idea to me, but it ended up succeeding. This is how it was for me in LINQ-to-SQL:

I had a repository class that had a static variable in the data context. At first it worked, but when I had to do many more repository classes, I got errors when I cracked them. It turns out that the data context in LINQ-to-SQL caches all the results, and there is no way to update them. Therefore, if you added a message to a table in one context, it will not appear in others that cached this table. The solution was to remove the static modifier and allow the repository to create context in the constructor. Because the repository classes were built as they were used, so was the new new data context.

You can argue that static variables leave less memory in memory, but the trace for the data context is pretty small to start with and will be collected at the end of the garbage.

Answer 5

Contrary to the response message. I created a webframework with static database access, it works great and gives great performance.

You can check the source code at http://www.codeplex.com/Cubes

Answer 6

If you are just doing database queries, then yes, make it static. You need to create an instance if this object needs to maintain some kind of state.

Answer 7

If you have a static method, you need to track instances when opening and closing the database.

So what you probably want to do is a static method called an instance or the current instance. And inside, you create a new instance of your db class, returning it in a static method.

Answer 8

Your methods are good for static use. I think it’s easy for you to convert them to static methods.

but later, you may need to manage the transaction. leaving transaction management in the class, it saves a lot of time, I think. and this scenario is best suited for a non-static class.