Most efficient way to do MySQL queries

[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?

[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?

[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...

[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).

[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.