Asynchronous programming in calling a function which read DB

Question

From the main form, the SettingForm is loaded by clicking a toolbar button.
In the SettingForm_load event, the setting data is read by calling the function(ReadData) which read data from database.
After that, display the setting data(DisplayData) on the TabControl.
Problem is that it takes 1~2 seconds to complete the ReadData, this blocks displaying the SettingForm.
I want to display SettingForm first and then have data is displayed.
I want to do this by asynchronous programming using Task class.
How to do this?

Answer 1

Hi,

Thanks for kind answer.
BTW, what do I do for DisplayData()?
How to sync ReadData() and DisplayData()?

Answer 2

Hello @JeffinAtl

Not knowing specifics like how data should be returned and if this is conventional read or with Entity Framework core the following should still provide the basics.

Using conventional read

using System.Data;  
using System.Data.SqlClient;  
using System.Threading.Tasks;  
  
namespace SqlServerAsyncRead.Classes  
{  
    public class DataOperations  
    {  
        private static string _connectionString =  
            "Data Source=.\\sqlexpress;Initial Catalog=NorthWind2020;Integrated Security=True";  
  
  
        public static async Task<DataTable> ReadProductsTask()  
        {  
  
            return await Task.Run(async () =>  
            {  
                var productTable = new DataTable();  
  
                using (var cn = new SqlConnection(_connectionString))  
                {  
  
                    using (var cmd = new SqlCommand() { Connection = cn })  
                    {  
  
                        cmd.CommandText = SelectStatement();  
                        await cn.OpenAsync();  
  
                        productTable.Load(await cmd.ExecuteReaderAsync());  
                    }  
  
                }  
  
                return productTable;  
                  
            });  
  
        }  
  
        private static string SelectStatement()  
        {  
            return "SELECT P.ProductID, P.ProductName, P.SupplierID, S.CompanyName, P.CategoryID, " +   
                   "C.CategoryName, P.QuantityPerUnit, P.UnitPrice, P.UnitsInStock, P.UnitsOnOrder, " +   
                   "P.ReorderLevel, P.Discontinued, P.DiscontinuedDate " +   
                   "FROM  Products AS P INNER JOIN Categories AS C ON P.CategoryID = C.CategoryID " +   
                   "INNER JOIN Suppliers AS S ON P.SupplierID = S.SupplierID";  
        }  
  
    }  
  
}  

Calling the above to a DataGridView (which could be other controls too)

private async void Form1_Load(object sender, EventArgs e)  
{  
    var table = await DataOperations.ReadProductsTask();  
  
    dataGridView1.DataSource = table;  
}  

Entity Framework Core

using System.Collections.Generic;  
using System.Linq;  
using System.Threading.Tasks;  
using Microsoft.EntityFrameworkCore;  
using NorthEntityLibrary.Contexts;  
  
namespace NorthEntityLibrary.Classes  
{  
    public class ProductOperations  
    {  
        public static NorthwindContext Context { get; } = new NorthwindContext();  
  
        public static async Task<List<Product>> GetProducts()  
        {  
            var productList = new List<Product>();  
  
            await Task.Run(async () =>  
            {  
  
                productList = await Context.Products  
                    .Include(product => product.Supplier)  
                    .Include(product => product.Category)  
                    .Select(Product.Projection)  
                    .ToListAsync();  
  
            });  
  
            return productList;  
        }  
    }  
}  

Projection for above

using System;  
using System.Linq.Expressions;  
using NorthClassLibrary.Models;  
using NorthEntityLibrary.Models;  
  
namespace NorthEntityLibrary.Classes  
{  
    public class Product  
    {  
        public int ProductId { get; set; }  
        public string ProductName { get; set; }  
        public int? SupplierId { get; set; }  
        public string SupplierName { get; set; }  
        public string QuantityPerUnit { get; set; }  
        public decimal? UnitPrice { get; set; }  
        public short? UnitsInStock { get; set; }  
        public short? UnitsOnOrder { get; set; }  
        public short? ReorderLevel { get; set; }  
  
        public override string ToString() => ProductName;  
  
        public static Expression<Func<Products, Product>> Projection =>  
            product => new Product()  
            {  
                ProductId = product.ProductID,  
                ProductName = product.ProductName,  
                SupplierName = product.Supplier.CompanyName,  
                SupplierId = product.SupplierID,  
                QuantityPerUnit = product.QuantityPerUnit,  
                ReorderLevel = product.ReorderLevel,  
                UnitPrice = product.UnitPrice,  
                UnitsInStock = product.UnitsInStock,  
                UnitsOnOrder = product.UnitsOnOrder  
            };  
  
    }  
}  

Same basic call as with the first example

var products = await ProductOperations.GetProducts();  

Helpers

There are times when it's possible to run into cross threading violations which this following extension is good for.

public static class ControlExtensions  
{  
	public static void InvokeIfRequired<T>(this T control, Action<T> action) where T : ISynchronizeInvoke  
	{  
		if (control.InvokeRequired)  
		{  
			control.Invoke(new Action(() => action(control)), null);  
		}  
		else  
		{  
			action(control);  
		}  
	}  
}  

Usage

dataGridView1.InvokeIfRequired(dgv =>  
{  
	dgv.DataSource = await DataOperations.ReadProductsTask();  
  
});  

Summary

• The above code uses SQL-Server while in the conventional or EF Core sample you can change to say Oracle by changing the data provider, if using MS-Access both will still work but not with Entity Framework 6.
• Projections as used in the EF Core example, this keeps code clean along with being usable in other queries.
• Also, when working with EF Core for the first call to interact with data there will be a slight delay to initialize and in this case nothing really can be done.
• Since the language was not specified and you are using VB.NET, the following code sample shows the basics, class part of a GitHub repository.