MARS for MySQL in C#

Question

Today I've implemented a nasty hack in my code where every request to the database opens it's own connection due to the fact that I couldn't find any way to enable MARS (multiple active record sets) when communicating with a MySQL database.

In my C# program I do a lot of parallel work, which isn't a problem regarding databases such as MSSQL 2005 and 2008 (append ;MultipleActiveResultSets=true to your connection string) and SQLite (supports it "out of the box") and you are able to retrieve two datasets from the database at the same time.

Things that I do know: it's expensive to open a connection to the database and their for I would like to keep these to a minimum.

Any suggestions?

Answer 1

Maybe a best way to handle this scenario type is to implement that parallel data processing into your database, by using a store procedure or a cursor, so you don't need to deal with a very specific database feature.

Answer 2

Any suggestions?

I don't know if there really is no way to enable MARS with MySQL, but if that's correct, then my best suggestion is to implement connection-pool.

Answer 3

Look at the MySQL documentation for connection string parameters (no MARs) -

http://dev.mysql.com/doc/refman/5.5/en/connector-net-programming-connection-options.html

Things that I do know: it's expensive to open a connection to the database and their for I would like to keep these to a minimum.

Utilize connection pooling all the way! (just make sure you use the exact same connection string each time).