CODEWITHSUNDEEP
c#mysql
Haider
Haider

Reputation: 461

Simultaneous connections MySql/C#

I have a WCF webservice that caters simultaneous requests from different clients. The service has to communicate with mysql Db on every request. The issue that i am facing is that a large number of requests come in at a same time (about 300-400) and while communicating with MySQL 'Unable to connect to any of the specified MySQL hosts' pops up. What i've figured out till now is that Mysql is unable to manage so many simultaneous connections and fails to cater any more and starts throwing the error. What is the best way to get around this issue? Following is the code snippet that is called every time a requests comes in:

        try
        {
            MySqlCommand command = new MySqlCommand(query);
            command.Connection = conn;
            conn.Open();
            long id = -1;
            int _result = command.ExecuteNonQuery();

            if (_result == 1)
            {
                 //do something
            }

            conn.Close();
            return id;
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            if (conn.State != ConnectionState.Closed)
            {
                conn.Close();
            }
        }

Upvotes: 1

Views: 577

Answers (1)

Kyle W
Kyle W

Reputation: 3752

How long does "//do something" take? If it's short, I don't see a whole lot you can do to speed it up from the code. If it's long, consider closing the connection before doing whatever that is (presumably error handling).

(As a side note, you should try the using statement instead of the try/catch block to close the connection)

edit: Also, as SLaks said, "throw;" will do the same thing as "throw ex;" except it will preserve the stack trace, which is why you should not have the "ex;" on there, but this point is moot if you replace it with using.

Upvotes: 2

Related Questions