I'm using a simple method for connecting to a MySql database but connecting to this database takes a while & this causes the app to be in "not responding" mode. now, can I use async for solving this?
Script is:
Private void button_clicked()
{
MysqlConnection connection = new MysqlConnection(constring);
connection.open();
}
MySQL Connector/NET (i.e., MySql.Data) exposes the async ADO.NET methods, e.g., MySqlConnection.OpenAsync, MySqlCommand.ExecuteNonQueryAsync, but these methods all execute synchronously. This is a longstanding bug in Connector/NET.
Edit (April 2023): MySQL Connector/NET 8.0.33 has been released and provides true asynchronous implementations of the Async methods.
You can get asynchronous database operations by switching to MySqlConnector (NuGet, GitHub), an OSS alternative that provides asynchronous I/O and higher performance.
Task.Run is the only way to move that work to a background thread. But in general, using Task.Run to make blocking work asynchronous is a bad idea in most environments: blog.stephencleary.com/2013/11/…MySql.Data.MySqlClient.MySqlConnection.OpenAsync() performs synchronous whereas MySqlConnector.MySqlConnection.OpenAsync() performs asynchronous
I am sure that’s possible. In addition to structuring your code like the following (source):
public Task<DataSet> GetDataSetAsync(string sConnectionString, string sSQL, params SqlParameter[] parameters)
{
return Task.Run(() =>
{
using (var newConnection = new SqlConnection(sConnectionString))
using (var mySQLAdapter = new SqlDataAdapter(sSQL, newConnection))
{
mySQLAdapter.SelectCommand.CommandType = CommandType.Text;
if (parameters != null) mySQLAdapter.SelectCommand.Parameters.AddRange(parameters);
DataSet myDataSet = new DataSet();
mySQLAdapter.Fill(myDataSet);
return myDataSet;
}
});
}
This combined with the use of “await” keyword, will get you the results you need.
//Use Async method to get data
DataSet results = await GetDataSetAsync(sConnectionString, sSQL, sqlParams);
Also update the connection string by adding “Asynchronous Processing=true” connection property (source)
I would also recommend you to have a look at the “OpenAsync” method. You can read more about it in the docs.
Task.Run to offload blocking code to the threadpool is a bad idea: blog.stephencleary.com/2013/11/… Instead, use MySqlConnector github.com/mysql-net/MySqlConnector to get true asynchronous DB operations. Finally Asynchronous Processing=true is a SQL Server connection string option; it will throw an error if you try to use it with MySQL.At the end, I found the exact answer according to @MikaalAnwar's answer!
We don't need to add any new options (like
Asynchronous Processing=true) to connection string; That's for SQL connections & doesn't work for MySql.
So, what should we do now?
we make any void that is respected to have async option,
async. Then we add an await task & run it (Task.Run). inside that task, we do what ever we want with our connection.
for example: (We don't want to use any datasets)
private async void DBConnect(String connectionString)
{
await Task.Run(() =>
{
MySqlConnection dbConnection = new MySqlConnection(connectionString);
dbConnection.Open();
}
);
}
& We don't use
DBConnection.OpenAsync()because the void is async & we've used await for the task.
Finished:)
