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

Farshad Valizade 381 Reputation points
2024-02-02T05:36:17.2766667+00:00

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();  
}
C#
C#
An object-oriented and type-safe programming language that has its roots in the C family of languages and includes support for component-oriented programming.
9,978 questions
0 comments No comments
{count} votes

Accepted answer
  1. Jiale Xue - MSFT 18,736 Reputation points Microsoft Vendor
    2024-02-02T06:00:43.06+00:00

    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.

    2 people found this answer helpful.
    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. Karen Payne MVP 35,016 Reputation points
    2024-02-03T10:00:05.5766667+00:00

    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();
    }
    
    0 comments No comments