Member Avatar for Anon17

At the moment, I have the following to do MySQL queries:

MySqlCommand Command = new MySqlCommand(Query, SQLConnection);

                IAsyncResult Result = Command.BeginExecuteReader();
                
                return Command.EndExecuteReader(Result);

This is a static command in a server in the database class, which includes a static connect/disconnect function (used once when the server starts and stops respectively) and is accessed by every client. This however is clearly inefficient as when multiple clients are on I often get the error "There is already an open datareader on this connection" (I have try/catch blocks around the code but removed them in the post to simplify the code.

Now, I believe there are two ways in which I can fix this error and make the mysql queries/updates much more efficient:

1 - Remove the static from the class, and make every user connection and such have their own mysql connection class. The problem I see with this is that having a lot of classes connection to the database at once would take up a lot of memory, and since some classes are static (like the main server class) they'll still be accessed by multiple clients at once on the same connection thus causing the error.

2 - Keep the class static but don't connect/disconnect in the start/stop methods of the server, instead use the using() function to connect to the SQL database every time a query is needed and then fill a datareader with the results (which is returned to the client class for processing). I'm not sure how efficient constantly connecting/disconnecting to the database would be though.

Basically, I need to be able to (in theory, not all the time but often) be able to execute multiple queries at the same time (roughly, usually within several milliseconds) and return the datasets. Due to this being a server with many clients that expect their data instantly this needs to be fast.

So tl;dr would method 2 (connect to the database seperately for each query and fill a datatable which is returned) be efficient? If not, what else could I do?

  • 2 Contributors
  • 4 Replies
  • 205 Views
  • 10 Hours Discussion Span
  • Latest Post Latest Post by Anon17

Dani AI

Generated

— the error you saw comes from sharing a single Connection/Command across concurrent requests. was on the right track: don’t serialize all access on one static connection. The recommended pattern is to open a short-lived connection for each operation and dispose it immediately; ADO.NET/MySQL drivers use connection pooling so Open/Close is cheap and scales far better than a single shared connection that becomes a bottleneck.

Never return an open DataReader from a method that closes the connection. Materialize the results first and then return them. For modest result sizes, load into a DataTable; for higher throughput, map rows to a lightweight DTO (List<T>) or use a micro‑ORM to avoid DataTable overhead. Example pattern (materialize then close):

using(var conn = new MySqlConnection(connString))
{
    conn.Open();
    using(var cmd = new MySqlCommand(sql, conn))
    using(var reader = cmd.ExecuteReader())
    {
        var dt = new DataTable();
        dt.Load(reader);
        return dt;
    }
}

Practical tips: parameterize queries and call Prepare() for statements you run often; use transactions for multi-statement updates; tune pool settings (e.g. Max Pool Size) only after measuring; avoid long-lived open readers (they hold a connection until closed). If you need streaming or very large result sets, stream rows to the client and be explicit about connection lifetime (streaming requires the connection to stay open while enumerating). If using modern .NET, prefer async/await with ExecuteReaderAsync for I/O scalability.

Following these patterns removes the DataReader/connection conflict, keeps memory use sensible, and gives much better concurrency than a single static connection.

Recommended Answers

All 4 Replies

Member Avatar for Momerath

The system maintains a connection pool already, so your forcing everything into one connection isn't helping at all. Each query should use it's own connection object and unless you are making 1000s of queries a second, you aren't going to run into memory issues because of database connections.

Is there a reason you are using Async queries?

Member Avatar for Anon17

I used Async queries to try and combat this problem but I've realised that it's obviously not going to help, I simply forgot to remove them.

Current my query function returns the datareader which if query function opens/closes a connection obviously isn't going to work, so should I fill a datatable or dataset with the query results and return that?

Thanks for the help by the way, been looking into this for several hours now...

Member Avatar for Momerath

DataSet/DataTable would probably be best as it removes some of the coupling between your objects (The code using the data doesn't need to know how to read each line from the database and how to open/close the connection).

Member Avatar for Anon17

Yeah, that's what I thought.

Thanks for the help then, guess I'll switch it out to one connection per query or update with the query returning a datatable.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.