Best Practice question - 100 or so 'spurt' queries

[James H]

I am using the .NET connector (mySql.Data.dll version 5.0.6 or close to that).

At the moment I have a database class with ONE mySQLConnection along with a Query function that takes a mySQLCommand class as an argument and returns a DataTableReader. I switched to using a DataTableReader because I have 100+ clients log in almost simultaneously and was getting exceptions because only one DataReader is allowed to be open at a time, so I fill a DataSet and then return a DataTableReader, which functions exactly the same as a mySQLDataReader execpt it's not kept associated with the connection, at least as far as I am aware of.

So anyway, my problem is this: These 100 clients log in, nearly simultaneously. The code first requests account data via the Query function, which returns a DataTableReader. Then, the code immediately queries for character data.

The application randomly encounters either an ArgumentException or an ArgumentOutOfRangeException. These exceptions are thrown in either the account query or the character query. If I look at the exception details, it is ALWAYS the same problem. I cannot remember the exact wording at the moment but the error message indicates that the wrong DataTableReader is being returned. eg the code queries for account data (which contains ID, Account, Password, and Status) and if I examine the exception error it states that the specified columns do not exist, and if I examine the DataTableReader, it shows me colums FROM THE CHARACTER QUERY. As if the underlying connector is returning the wrong result.

Anyhow, what is the best strategy to do something like this? Create a brand new connection for each query? That seems wasteful and illogical to me.

Is there some class I am not aware of that would facilitate things like this? I know mySQL is used commercially as a backend for websites and whatnot that process hundreds of queries per second, and I can't seem to get 100 queries done period.

Here is the code for the Query function. Again, I have this because I cannot have multiple datareaders open at the same time, so I fill a dataset.

' Declared elsewhere
Private m_Connection As MySqlConnection

Public Function Query(ByVal cmd As MySqlCommand) As DataTableReader
Dim dr As DataTableReader = Nothing

If m_Conn.State = ConnectionState.Closed Then OpenDatabase()
cmd.Connection = m_Conn

Try

Dim da As IDataAdapter = New MySqlDataAdapter(cmd)
Dim ds As New DataSet
da.Fill(ds)

ds.Dispose()

dr = ds.CreateDataReader()
Catch aore As ArgumentOutOfRangeException
GameManager.Log("Error in Datamanager.Query: " & cmd.CommandText)
Catch ae As ArgumentException
GameManager.Log("Error in Datamanager.Query: " & cmd.CommandText)
Catch ex As Exception
Stop
End Try

cmd.Dispose()

Return dr

End Function