Handling with multiple SQL-DataReader
-
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 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
-
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 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
Create more connections; one for each concurrent request.
-
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 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.
Need custom software developed? I do custom programming based primarily on MS tools with an emphasis on C# development and consulting. A man said to the universe: "Sir I exist!" "However," replied the universe, "The fact has not created in me A sense of obligation." --Stephen Crane
-
My applications always seem to allow multiple readers to a database without issue.
Need custom software developed? I do custom programming based primarily on MS tools with an emphasis on C# development and consulting. A man said to the universe: "Sir I exist!" "However," replied the universe, "The fact has not created in me A sense of obligation." --Stephen Crane
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 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[0], the second to List[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. :thumbsup: :-D
-
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 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[0], the second to List[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. :thumbsup: :-D
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. :)
Navaneeth How to use google | Ask smart questions
-
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 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[0], the second to List[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. :thumbsup: :-D
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.
-
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. :)
Navaneeth How to use google | Ask smart questions
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.
-
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
SqlConnectionobject 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. :)
Navaneeth How to use google | Ask smart questions
-
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
SqlConnectionobject 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. :)
Navaneeth How to use google | Ask smart questions
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[^]
-
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. :)
Navaneeth How to use google | Ask smart questions
-
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. 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. :thumbsup:
-
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 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
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).
-
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)