how to use multiple instance of a db connection with multiple thread in C#.Net

Posted on 2012-12-23
Last Modified: 2013-01-22
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.
Question by:dynamicweb09
  • 4
  • 2
  • 2
  • +1

Expert Comment

ID: 38717968

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("ConnectionString");

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;

//run queries

Let me know if anything.


Author Comment

ID: 38717980
Thanks Igor for your reply.

Actually I want to run same procedure with different parameter  parallel y. please help me
LVL 40

Assisted Solution

by:Jacques Bourgeois (James Burger)
Jacques Bourgeois (James Burger) earned 167 total points
ID: 38718287
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.

Author Comment

ID: 38718335
Can I call Asynchronously? if yes then how?
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

LVL 40
ID: 38718963
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.
LVL 75

Assisted Solution

by:käµfm³d 👽
käµfm³d   👽 earned 83 total points
ID: 38720417
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.

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.
LVL 40
ID: 38720424

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.
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 38720652
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?
LVL 40

Accepted Solution

Jacques Bourgeois (James Burger) earned 167 total points
ID: 38720795
Unfortunately, I am not in the secrets of the gods. And I never explecitely tested for that. All I can say by my experience is that you get the feeling that the queries are executing at the same time, and in my book, how the user feels is more important than what really happens.

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Server Error 11 47
simple shopping cart database structure 5 31
RDBMS and No sql database 4 43
Not seen Link button 5 14
As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now