|
|
Hello,
i have a application, where parallel running threads have access to my database. In most cases, that's no problem, but if a thread has a open DataReader and another Thread wants to access the DB too i get a problem, because there's only one open DataReader allowed.
Now, i wanted to make a Method (lets call it "dbRead()" ) which asks the DB and returns the Data to the specific Thread or Method, which executes dbRead(). My problem is now, that this could become a bottleneck if i have a lot of Reader-Transactions.
There's the possibility to make a list of classes (lets call it "dbOperations") which contains public the dbRead(), put maybe 5 of them in a List<dboperations> and if i want to make a transaction i use randomly one of the classes and start "dbOperationsList[randomNr].dbRead()".
This would work, but isn't there maybe a tutorial or has somebody such an piece of code? Because i don't think, that i'm the first with this problem 
Many thanks for your help
Patrick
|
|
|
|
|
My applications always seem to allow multiple readers to a database without issue.
|
|
|
|
|
Provided they each have their own connection?
|
|
|
|
|
Create more connections; one for each concurrent request.
|
|
|
|
|
OK,
that was my first problem and so that i need more Connections to the database, i'll need such an "RequestManager". I've made a simple piece of code and i'll test it if it works. It's very simple. A class, which a standardized Method which gives me a List<object[]> back (it's not a Object[][], because i don't now how much entries i'll get back i get only the amount of columns back), this method makes the request and reads the data and closes the DataReader. This class has a own connection to the database. So... i put n-instances in a List at the start of my application and make a simple integer-value, which gives me the index of the next RequestManager-Class back.
So the first request goes to List<requestmanager>[0], the second to List<requestmanager>[1] and so on... a simple and effective LoadBalancing and Transaction-Management for my Database. And if i see, that i need more connections, i could realize this if i only make more instances of the RequestManager Class.
Many thanks for your ideas.  
|
|
|
|
|
Doesn't sounds like a good idea to me. I'd suggest you to use separate connection and command instances for each threads. ADO.NET has inbuilt connection pooling and you don't need to pool it manually.
|
|
|
|
|
N a v a n e e t h wrote: Doesn't sounds like a good idea to me.
Indeed.
N a v a n e e t h wrote: separate connection and command instances for each threads
Yes, but a thread may still need more than one connection to the same database.
N a v a n e e t h wrote: ADO.NET has inbuilt connection pooling
But I don't think it will detect when you need an additional connection to a database and create it. The pooling only pools the connections that have been created by the application. And of course, only if the connection has been closed. So the developer still has to do that.
N a v a n e e t h wrote: you don't need to pool it manually
Not pool, but create.
|
|
|
|
|
PIEBALDconsult wrote: The pooling only pools the connections that have been created by the application
My understanding is: each pool will have a minimum connection object limit and when a pool is initiated, these objects will be created and ready to use. When SqlConnection object is requested, it will be served from the pool if available.
PIEBALDconsult wrote: So the developer still has to do that.
Caching the connection objects? I still believe developer shouldn't cache the connection objects. All they can do is to wrap the connection in a using statement, so it gets disposed properly and return back to the pool. Let framework's pooling do it's job.
|
|
|
|
|
N a v a n e e t h wrote: these objects will be created and ready to use
With what settings? How will the pool know what the user wants to connect to until asked?
N a v a n e e t h wrote: When SqlConnection object is requested, it will be served from the pool if available.
If the connection string matches, yes.
N a v a n e e t h wrote: gets disposed properly and return back to the pool
I understand that happens at Close. So close the connection, but retain the Connection object. When you reopen the connection, the pool tries to use an existing connection.
http://msdn.microsoft.com/en-us/library/8xx3tyca(VS.85).aspx[^]
|
|
|
|
|
PIEBALDconsult wrote: With what settings? How will the pool know what the user wants to connect to until asked?
My bad, you are correct. Pool will be initiated only when a connection is first opened.
PIEBALDconsult wrote: I understand that happens at Close
You can call dispose as well. It happens at dispose too. Here is what MSDN says,
We strongly recommend that you always close the connection when you are finished using it so that the connection will be returned to the pool. You can do this using either the Close or Dispose methods of the Connection object, or by opening all connections inside a using statement in C#, or a Using statement in Visual Basic.
|
|
|
|
|
In one of my database access schemes I have a Dictionary<string,connection>, the first connection is named "" and is used by default, but when necessary I can use:
dbc.AddConnection ( "name" )
dbc [ "name" ].Execute...
dbc.RemoveConnection ( "name" )
So I can be reading with the default connection and use another to perform other actions as needed.
|
|
|
|
|
Well a lot of answers from you thanks =)
So to the question, "why doesn't get every thread a db-connection?", the answer is, that i make a server-application and for each user-connection i have on thread, but i don't have a huge amount of requests from each user, so it doesn't make sense to give each user a own db-connection, and the second is i don't know how many users will use the server.
So the idea with this RequestManager. I make maybe 5 or 10 (or how many i need) instances from it, stored in a List<requestmanager>. Each RequestManager has an own DB-Connection and handles the Reader for itself. If a user now makes a request, the class of the User-Connection starts the Method "GetData()" (give the baby a name  ) from one of this RequestManagers. So i can make a few requests parallel and if i need more i can easily put even more RequestManager in this List<>.
How does my application get the information to put more RequestManager-instances into this list? Well that's easy, over a configuration-file. I let my application reload them every 5 minutes and it checks how many RequestManager i need and so i'm able to configure them while my system is running.
Your examples, had the nearly the same idea as mine. But i only handle with the "RequestManager" and not with Reader and Connections seperately. I can only make failures on one place (the RequestManager).
Thanks for your help, you gave me a lot of inspiration for this problem. 
|
|
|
|
|
MARS (Multiple Active Result Set) can give you a quick solution in this respect (if you are using ADO.NET 2.0 and SQL Server 2005).
|
|
|
|
|
Thanks,
but i don't use SQL Server 2005, but i read the Article about MARS on MSDN, this is not exactly that, what i want, because i want the possibility to have more parallel running transactions.
But many thanks to you all for your answers, but i have now the solution (or at least the ideas for my solution)
|
|
|
|
|
Suppose I have a generic class A with a method Xyz.
public class A<t> : where T : class
{
// lots of stuff omitted...
public int Xyz()
{ // details omitted
}
}
Elsewhere I have an object reference. I want to test if the reference refers to an instance of A (don't care about what <t> is), and if it is an instance of A, invoke method Xyz and get the return value. Something like
if (ref is A)
{
int returnValue = ((A)ref).Xyz();
}
except that this code doesn't work when A is a generic type. Can this be done?
Thanks
Peter
|
|
|
|
|
the problem you have is that A does not exist, but A<T> does. So you cant cast to A.
However, seeing as method Xyz() does not rely on Generic type T at all, I would structure your code to include non-generic type A and then inherit for the generic stuff:
public class A
{
public int Xyz()
{
}
}
public class A<T> : A where T : class
{
}
now, this will work:
if (ref is A)
{
int returnValue = ((A)ref).Xyz();
}
|
|
|
|
|
Yes, however IMO that would fail if Xyz() depends on T.
Luc Pattyn [Forum Guidelines] [My Articles]
The quality and detail of your question reflects on the effectiveness of the help you are likely to get.
Show formatted code inside PRE tags, and give clear symptoms when describing a problem.
|
|
|
|
|
True, but the information provided did not appear to indicate that Xyz relied on T. I can only answe on info provided
|
|
|
|
|
Hi,
this generates "1":
public class A<T> where T : class {
public int Xyz() { return 1; }
}
public class CPTest_Generic : CPTest {
public override void Test(int arg) {
test2(new A<Form>());
}
public int test2(object rf) {
A<Form> aa=rf as A<Form>;
int result=0;
if (aa!=null) result=aa.Xyz();
log("result="+result);
return result;
}
}
Luc Pattyn [Forum Guidelines] [My Articles]
The quality and detail of your question reflects on the effectiveness of the help you are likely to get.
Show formatted code inside PRE tags, and give clear symptoms when describing a problem.
|
|
|
|
|
You could write an interface that specifies Xyz, have A<T> implement it, then test for the interface rather than A.
|
|
|
|
|
Yes, this:
public interface Abase {
int Xyz();
}
public class A<T> : Abase where T : class {
public int Xyz() { return 1; }
}
public class CPTest_Generic : CPTest {
public override void Test(int arg) {
test2(new A<Form>());
}
public int test2(object rf) {
Abase aa=rf as Abase;
int result=0;
if (aa!=null) result=aa.Xyz();
log("result="+result);
return result;
}
}
works however it forces Xyz() to be T-agnostic, so what is generic about it?
Luc Pattyn [Forum Guidelines] [My Articles]
The quality and detail of your question reflects on the effectiveness of the help you are likely to get.
Show formatted code inside PRE tags, and give clear symptoms when describing a problem.
|
|
|
|
|
Make the interface generic?
public interface Abase<T>
{
T Xyz() ;
}
(Not tested.)
modified on Monday, August 10, 2009 6:57 PM
|
|
|
|
|
I agree with PIEBALDconsult, an interface is probably the cleanest way to do this:
public class A<T> : IA where T : class
{
public int Xyz()
{
return 1;
}
}
public interface IA
{
int Xyz();
}
if (a is IA)
int returnValue = (a as IA).Xyz();
DaveBTW, in software, hope and pray is not a viable strategy. (Luc Pattyn)
Visual Basic is not used by normal people so we're not covering it here. (Uncyclopedia)
Why are you using VB6? Do you hate yourself? (Christian Graus)
|
|
|
|
|
Why use is and as? Pick one.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
