Friday, June 13, 2014

Asynchronous Data Grid View Using Tasks

Hi Guys,

This time I made a fully UI Responsive Asynchronous Data Grid for WinForms. This uses Tasks to call a database asynchronously and fetches data without blocking the main thread. Tasks are Managed Thread Pool Threads so they are efficient compared to regular threads.


Before you Start,

1) Visual Studio with .NET Framework 4.5 

Step One : The Solution

Make a WinForms Application as Following
















Step Two : Paste the Below Code in the AsyncDataGridView.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Data;

namespace AsyncDataGridView.AsyncDataGrid
{
    public class AsyncDataGridView : DataGridView
    {
        //Sql Command Object
        public SqlCommand SelectCommand { get; set; }
        //Connection String 
        public string ConnectionString { get; set; }
        //Delegate and Event for DataBindAsync Completed
        public delegate void DataBindAsyncCompleted(object sender, AsyncDataBindedEventAgrs e);
        public event DataBindAsyncCompleted OnDataBindAsyncCompleted; 
        //Delegate for the Call Back 
        private delegate void SetDataSource(DataTable dtData);

        /// <summary>
        /// DataBind Async Method Which Runs a Query Asynchoronously
        /// </summary>
        /// <returns>Task</returns>
        public Task DataBindAsync()
        {
            try
            {
                //Checking whether Select Command Specified or Not
                if (this.SelectCommand == null)
                    throw new ArgumentException("SelectCommad Not Specified.");

                //Checking whether Connection String Specified or Not
                if (string.IsNullOrEmpty(this.ConnectionString))
                    throw new ArgumentException("ConnectionString Not Specified.");

                //Running Task 
                Task t = Task.Run(() =>
                {
                    using (SqlConnection con = new SqlConnection(this.ConnectionString))
                    {
                        con.Open();                      

                        using (this.SelectCommand)
                        {
                            this.SelectCommand.Connection = con;
                           DataTable dtData = new DataTable();

                            using (SqlDataReader rdr = this.SelectCommand.ExecuteReader())
                            {
                                System.Threading.Thread.Sleep(5000);
                                dtData.Load(rdr);
                            }

                            if (this.InvokeRequired)
                            {
                                SetDataSource d = new SetDataSource(SetDataSourceValue);
                                this.Invoke(d, new object[] { dtData });
                            }                           

                        }
                    }                    
                });
              

                return t;
            }
            catch (Exception ex)
            {                
                throw ex;
            }


        }

        /// <summary>
        /// CallBack Method
        /// </summary>
        /// <param name="dtData"></param>
        private void SetDataSourceValue(DataTable dtData)
        {
            this.DataSource = dtData;

            if (this.OnDataBindAsyncCompleted != null)
                this.OnDataBindAsyncCompleted(this, 
                    new AsyncDataBindedEventAgrs(
                        dtData != null ? dtData.Rows.Count : 0
                    ));
        }

    }

    //Event Arguements
    public class AsyncDataBindedEventAgrs : EventArgs
    {
        public int RowCount { get; set; }
        public AsyncDataBindedEventAgrs(int rowCount)
        {
            this.RowCount = rowCount;
        }
    }

}

Step Three : Add the Below code to the TestForm.cs

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace PagingDataGridView
{
    public partial class TestForm : Form
    {
        public TestForm()
        {
            InitializeComponent();
        }

        private void btnGetData_Click(object sender, EventArgs e)
        {
            try
            {
                FetchData();
            }
            catch (Exception ex)
            {

                MessageBox.Show(ex.Message);
            }
        }

        private async void FetchData()
        {
            aDgvCustomers.ConnectionString = ConfigurationManager.ConnectionStrings
                ["AsyncDataGridView.Properties.Settings.MyConnectionString"]
                .ConnectionString;

            aDgvCustomers.SelectCommand = new SqlCommand("SELECT name,dbid FROM  sysdatabases");
            aDgvCustomers.OnDataBindAsyncCompleted += aDgvCustomers_OnDataBindAsyncCompleted;

            await aDgvCustomers.DataBindAsync();
        }

        private void aDgvCustomers_OnDataBindAsyncCompleted(object sender, AsyncDataGridView.AsyncDataGrid.AsyncDataBindedEventAgrs e)
        {
            MessageBox.Show(e.RowCount + " Rows(s) Fetched.");
        }
    }

}

Step Four : Running the Form

Run the TestForm then Click on Get Data Button. You will have complete UI Responsiveness. You can work with the form while it fetches data from the Database.


















Once the data fetching asynchronously completed it will fire the OnDataBindAsyncCompleted event. Which will have how many rows were fetched.