how to fetch data from database with async Task in c#?

Question

hi every body. I have a code that read data from sql server db and show it in the datagridview. How can I use async await task to do it? DAL code:

public static DataTable GetAll()
{
	using DataTable dt = new();
try
{
	string query = "SELECT  * From Documents";
	using SqlCommand cmd = new(query, Connection.Conn);
	Connection.Open();
	using SqlDataReader rd = cmd.ExecuteReader();
	dt.Load(rd);
}
catch (Exception ex)
{
	Error = ex.Message;
}
finally
{
	Connection.Close();
}
	return dt;
}

BLL:

public static DataTable GetAll() 
{     
	return DocumentDAL.GetAll(); 
}

UI :

 private void btnLoad_Click(object sender, EventArgs e)  
{ 	
	Datagridview1.Datasource = DocumentBLL.GetAll();  
}

Answer 1

Hi @Farshad Valizade , Welcome to Microsoft Q&A,

It should be noted that ADO.NET itself does not support asynchronous operations of SqlConnection or SqlCommand. To achieve asynchronous behavior, you may consider using the "Task.Run" method to offload synchronous operations to a separate thread.

1. Added Async suffix to GetAllAsync method.
2. Use "OpenAsync" and "ExecuteReaderAsync" in the DAL for asynchronous operations.
3. Change the BLL method to return a Task<DataTable> and wait for the DAL method.
4. Modify UI methods to use await when calling BLL methods.

Remember, if you use "await" in a UI method, you need to mark it as "async". Also, be aware of potential issues with UI thread synchronization when updating the UI from an asynchronous operation.

Dal:

public static async Task<DataTable> GetAllAsync()
{
    using DataTable dt = new DataTable();
    try
    {
        string query = "SELECT  * From Documents";
        using SqlCommand cmd = new SqlCommand(query, Connection.Conn);
        await Connection.OpenAsync(); // Use OpenAsync for asynchronous opening
        using SqlDataReader rd = await cmd.ExecuteReaderAsync();
        await Task.Run(() => dt.Load(rd)); // Offload synchronous operation to a separate thread
    }
    catch (Exception ex)
    {
        Error = ex.Message;
    }
    finally
    {
        Connection.Close();
    }
    return dt;
}

BLL:

public static async Task<DataTable> GetAllAsync()
{
    return await DocumentDAL.GetAllAsync();
}

UI:

private async void btnLoad_Click(object sender, EventArgs e)
{
    Datagridview1.DataSource = await DocumentBLL.GetAllAsync();
}

Best Regards,

Jiale

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment". 

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Although you have a solution, take a look at using Dapper.

• Dapper opens and closes connection for you
• No need for a command object, Dapper handles this.
• See one of my Dapper articles Using Dapper - C# Part 1

Back end

internal class DataOperations
{
	private static IDbConnection db = new SqlConnection("TODO");
    public static async Task<DataTable> GetAll()
    {
        DataTable table = new();
        table.Load(await db.ExecuteReaderAsync("SELECT * FROM dbo.Documents"));
        return table;
    }
}

Form code

private async void GetAllButton_Click(object sender, EventArgs e)
{
    dataGridView1.DataSource = await DataOperations.GetAll();
}