C# static method question

Question

Hi, I do not understand what exactly happen behind the scenes when using static class with static members in multithread environment. I have a server application where clients very often read-write to SQL db. For that, I have created static class with static members SQLConnection,SLQDataReader etc. But as I understood, static class is just one, shared between threads, so how it deals with multiple requests at the same time? Would not be better just have non static class used for that? Thanks!

Answer 1

You should be fine as long as you're only using static methods and no static fields. Once you decide to use your first static field, you'll have to worry about thread safety.

Answer 2

I'm going to diverge from the other answers and say that it is a very bad idea to have a SqlConnection and especially SqlDataReader as global state. It is a huge, huge design smell. A few questions to consider:

• Will the connection always remain open? If so - are you aware that this wastes resources on the database server, and may even cause you to exceed the number of licensed connections?

• If the connection will not remain open - what steps will you be taking to prevent the scenario where one thread closes the connection while another is using? In fact your code does not necessarily even need to be multi-threaded for this to happen; in order to prevent it, every database operation must first save the original connection state, open the connection if closed, then restore the original state after finishing.

• Are you serializing access to the SqlConnection? If so, will you lock it for the entire duration of a database operation, which would severely impact overall app performance by causing a lot of unnecessary wait times?

• If you are not serializing access, what happens when one operation tries to execute some unrelated operation while another is in the middle of an important transaction?

• If making a "global" SqlDataReader, can you be absolutely sure that only one thread at a time will ever use it, and that thread will never need to execute another query before it is finished with the first result set? Even if SqlDataReader were thread-safe (it isn't), thread-safety rules are out the window if you actually destroy the object in question and replace it with a new one.

A SqlConnection should really be treated as a unit of work - you create it when needed, and dispose of it when finished. A SqlDataReader is not even a unit of work - it is a throwaway object for which there is absolutely no reason to keep at a global level. Even if you implement a singleton as others have apparently suggested, you are setting yourself up for a huge amount of trouble later.

Update: If these "members" are actually factory methods then that is different. The way the question was worded implied that a single connection was actually being shared. If you are simply providing static methods to create connections/queries then you can disregard this.