c# how to implement Data Base which is queried by threads

hello there i have a situation the entities are customerManager warehouse customer and suppliers my goals are :

1. the warehouse is singletone and open db in runtime.
2. the customerManager manage customers as threads who query the warehouse and update it (after buying staff).
3. when one of the items in the wareh开发者_Python百科ouse is run out of we ask supplier in a diffrent thread to supply it for us ' while the supplier does his thing (let's assume it's something like 5 seconds ) the customer waits(in queue) and invoked when the supplier method returned true (let's assume it return true always)..

so my questions are about 3 things :

1. design - should the customerManager holds inside him the warehouse and the customers ? it seems like the best soultion, does someone recommend otherwise?(c# design topic )

2. how many threads can go to the db at once ? can a db handle it by himself so i wont need to do it myself ? should I hold for them SqlCommand(s)? should I use dataset or datareader? in other words can someone advice me how to do it ? should i do for 10 threads :

    for (int i = 0 ; i < 10 ; i++)
   {
       SqlConnection sqlConnection = new SqlConnection(r_ConnectionString);      
       sqlConnection.Open();
       sqlConnection.Close();
   }
   

...so the conection pool would be open for 10 connectiones ?

** database ADO.NET ** topic

1. how should the threads wait in a queue?(in order to wait to the supplier method to awake them ) how to wake them ? is there a good solution in c# for that ? (c# threads topic)

I think the question is too long but otherwise would be too out of context so I would appreciate if you would write in the the title what question you want to reference.

thank you .

Your worker threads could be fed work via BlockingCollection or ConcurrentQueue.

For connection management you are better off doing this:

using (SqlConnection conn = new SqlConnection(...))
{
}

since this ensures Dispose() gets called for you. As noted in other feedback, you can do this without worrying about actual conn count to the DB since ADO.Net manages a pool of physical connections behind the scenes.

Nobody can tell you whether DataSet or DataReader works best, it depends on your usage of the data once it's loaded. DataReader provides a sequential read of each record in turn, while DataSet provides an in-memory cache of underlying DB data and in that sense is a 'higher-level' abstraction.

SqlConnections are implemented in .net using a connection pool. You do not need to worry about the management of the connections themselves. The only requirement on you is that adfter you open them you call .close. .net will manage the rest for you in an efficient way.

If you wanted to run multiple queries simultaneously then you can call the sqlcommand wtih begin invoke and end invoke.

By using both of these you cna work at a level that does not require you to mange the threads whilst gettting a multi threaded behaviour.

However you shuold read up on ADO.Net because a lot of what you are talking about is unnecessary when you kow how it works.

as for dataset or datareader that depends on your problem, Dataset is a very heavy object though, datareaders are lightweight and fast that allow you to populate a collection fairly easily.

I prefer using linq2sql though or entity framework. ADO.Net is kinda fragile because you ahve to do a lot of casting on data adn manual mapping onto objects that is prone to errors at run time rather than compile time.