We value your feedback.
Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!
Course Of The Month
4 days, 20 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
how to use multiple instance of a db connection with multiple thread in C#.Net
Posted on 2012-12-23
I have a console application(C#). I want to use more than one instance of mysqlconnection.
I have a master table with 50000 rows. I want to split the data in 10 set and want to run some procedures parallel with the data. please help me.
I have a master table with 50000 rows. I want to split the data in 10 set and want to run some procedures parallel with the data. please help me.
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
4
2-
2
- +1
9 Comments
Hi,
Using multiple instances is usually something that developers want to avoid. In fact, you should use the same instance for multiple operations. Make the connection instance to be global so you can access it from any code-block and just play with its state (open it and close it after use each time).
Example, make this global, public:
SqlConnection conn = new SqlConnection("ConnectionS
Then, every time you need to use this connection don't forget to open and close it, or if you use it for parallel execution do not close it after all queries are executed;
conn.Open();
//run queries
conn.Close();
Let me know if anything.
Regards,
Igor
Using multiple instances is usually something that developers want to avoid. In fact, you should use the same instance for multiple operations. Make the connection instance to be global so you can access it from any code-block and just play with its state (open it and close it after use each time).
Example, make this global, public:
SqlConnection conn = new SqlConnection("ConnectionS
Then, every time you need to use this connection don't forget to open and close it, or if you use it for parallel execution do not close it after all queries are executed;
conn.Open();
//run queries
conn.Close();
Let me know if anything.
Regards,
Igor
Thanks Igor for your reply.
Actually I want to run same procedure with different parameter parallel y. please help me
Actually I want to run same procedure with different parameter parallel y. please help me
You do not need multiple instances of the connection, you need multiple instances of the type of object in which you receive the data (collection, DataTable, DataGridView).
The same Connection object can be used with multiple Command objects, each of whom having different parameters, and they can all use the same Connection. However, by default, you need to run the commands one after the other. You cannot run multiple command at the same time.
SQL Server has a mode that enables multiple commands to run simultaneously on the same connection, but I cannot say if it is available for MySql. It is called Multiple Active Result Sets (MARS). You might want to search your MySql documentation to know if it is available.
I would also point out that I would not use a global variable as suggested by Igor. Global variables are a pain to debug. When you have a problem with one of those, you need to look all over the place in the application.
ADO.NET has been built in order to enable to use local variables for connections. Using those, if you have a problem, your search is limited to the method in which you have created the variable. This makes debugging and maintenance a lot easier.
The same Connection object can be used with multiple Command objects, each of whom having different parameters, and they can all use the same Connection. However, by default, you need to run the commands one after the other. You cannot run multiple command at the same time.
SQL Server has a mode that enables multiple commands to run simultaneously on the same connection, but I cannot say if it is available for MySql. It is called Multiple Active Result Sets (MARS). You might want to search your MySql documentation to know if it is available.
I would also point out that I would not use a global variable as suggested by Igor. Global variables are a pain to debug. When you have a problem with one of those, you need to look all over the place in the application.
ADO.NET has been built in order to enable to use local variables for connections. Using those, if you have a problem, your search is limited to the method in which you have created the variable. This makes debugging and maintenance a lot easier.
Once again, I speak of my experience with SQL Server, so I am not sure that MySql has those, but the Command object has a series of BeginExecute methods that runs asynchronously. Since all the ADO.NET libraries follow the same model, you might have those on your side.
Active today
@JamesBurger
http://technet.microsoft.com/en-us/library/cc966385.aspx
SQL Server has a mode that enables multiple commands to run simultaneously on the same connection, but I cannot say if it is available for MySql. It is called Multiple Active Result Sets (MARS).Please cite a reference that indicates MARS queries in parallel.
http://technet.microsoft.com/en-us/library/cc966385.aspx
It is probably as important to delimit what MARS is not:
Parallel execution: Though MARS enables more than one request to be submitted under the same connection, this does not imply that they will be executed in parallel inside the server. MARS will multiplex execution thread between outstanding requests in the connection, interleaving at well defined points.
@kaufmed
As stated in your reference, MARS works through "Interleaved execution". It is my understanding and my experience that from the application point of view, you feel that the result is the same as if the stuff was working in real parallel execution. If you start a long asynchrone query, and follow it with a short asynchrone query, the second one will finish before the first.
As stated in your reference, MARS works through "Interleaved execution". It is my understanding and my experience that from the application point of view, you feel that the result is the same as if the stuff was working in real parallel execution. If you start a long asynchrone query, and follow it with a short asynchrone query, the second one will finish before the first.
Active today
I don't believe that is guaranteed, though. The reference states "at well-defined points." Do you know what those "well-defined points" are? Is it guaranteed that a short query will finish before a long query?
Featured Post
Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
This article aims to explain the working of CircularLogArchiver. This tool was designed to solve the buildup of log file in cases where systems do not support circular logging or where circular logging is not enabled
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers.
Hopes this gives you ideas on visualizing your data in new ways ~
Create a calculated field in a query:
…
Suggested Courses
Course of the Month4 days, 20 hours left to enroll
688 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.