How to Increase performance of the windows application using background thread.

In Win-forms, it is always a performance challenge to retrieve data from database when data is more than ten thousand rows, as there is no pagination is involved. So question always comes that How to increase performance???  One of the solutions is to make use of background thread, which is  bring least number of rows from main thread and then use background thread to bring remaining data, until all data comes by background thread user will be busy in seeing main thread data rows.

Scenario:

In this example, I have to show 70 K rows in the datagridview, but to bring this much of data is always gives performance issues. So, here multithreading can be used. I am going to bring 1k rows using main thread then 69K rows using background thread. Until my background thread bring 69K of rows user will be busy with 1K of rows.

Using the Code:

Here, I have a datagridview and label, datagridview will display data and label will show message so that user will knows that still data is going to come from database.

Windows Forms is [STA] single-threaded Architecture. All operations on all windows and controls happen on the same thread, and all their events fire on that same thread. Because we are using another thread to access gridview and label which is own by main thread which created the button in the GUI, will throw an exception. So, we need to use BeginInvoke Method to use controls of another thread. For more reference http://msdn.microsoft.com/en-us/library/a06c0dc2.aspx

Here Row_Number() method is used to bring next 69K rows from database and merge it with 1k rows which is already present in dataset.

Steps to implement:

Step 1: AddàNew ProjectàWindows form application (in windows tab)

Step 2: Add Datagridview and label control in the form.

Step 3: After that, in form load method we are retrieving initially 1K rows from database.

Step 4: Now a thread will be created as a background thread. This background thread will bring all the remaining data from database.  

To assign dataset to gridview from background thread, need to use begin invoke method.

using System;

using System.Data;

using System.Windows.Forms;

using System.Data.SqlClient;

using System.Threading;

namespace WindowsFormsApplication1

{

    public partial class Form1 : Form

    {

        public Form1()

        {

            InitializeComponent();

        }

        /* Global variables*/

        string strSqlconnection = @"Data Source=VICKY-PC\sqlexpress;Initial Catalog=DB_NEW;Integrated Security=True";

        DataSet1 ds = new DataSet1();

        DataView dvds = new DataView();

        bool blnIsBackGround = false;

        /*form load */

        private void Form1_Load(object sender, EventArgs e)

        {

            /* this query will bring top 1000 rows from database.*/

            FillData("SELECT  top(1000) ROW_NUMBER() over (order by ID) as rowNumber,City_Code.CODE,City_Code.CITY FROM City_Code");

            dvds.Table = ds.Tables["City_Code"];

            dataGridView1.DataSource = ds.Tables["City_Code"];

            label1.Text = " Number of rows in gridview is " + dataGridView1.RowCount + "  Searching for more .......";

            /* Create new thread and creating handler of the thread*/

            Thread BackThread = new Thread(RunBackGroundThrad);

            /* Make thread as background thread*/

            BackThread.IsBackground = true;

            blnIsBackGround = true;

            /* Start background thread*/

            BackThread.Start();

        }

        /* this method will retrive data from database.*/

        public void FillData(string strSqlCommand)

        {

            SqlConnection questionConnection = new SqlConnection(strSqlconnection);

            questionConnection.Open();

            String sql = strSqlCommand;

            SqlDataAdapter adapter = new SqlDataAdapter(sql, questionConnection);

            if (blnIsBackGround)

                dataGridView1.BeginInvoke(new MethodInvoker(delegate

                { adapter.Fill(ds, "City_Code"); }));

            else adapter.Fill(ds, "City_Code");

        }

        /* this handler will be called by background thread as soon as background thread starts*/

        /* this method will retrive data from data base by background thread. */

        public void RunBackGroundThrad()

        {

            /* this query will bring all the rows after 1000 rows .*/

            FillData(@"select rowNumber,City_Code.CODE,City_Code.CITY from City_Code

                    inner join 

                    (

                    select ROW_NUMBER() over (order by ID) as rowNumber, *   from city_code) as a on City_Code.ID=a.ID

                    where rowNumber > 1000");

            /* as we are accessing form control from another thread, thats why we need to use beginInvoke method. */

            dataGridView1.BeginInvoke(new MethodInvoker(delegate

               {

                   dataGridView1.DataSource = null;

                   dataGridView1.Refresh();

                   DataView dvdsClone = new DataView();

                   dvdsClone.Table = ds.Tables["City_Code"];

                   dataGridView1.DataSource = dvdsClone;

               }));

            label1.BeginInvoke(new MethodInvoker(delegate { label1.Text = " Number of rows in gridview is " + dataGridView1.RowCount; }));

        }

    }

}

 

 

 

Multithreading is a really powerful feature. This article is only a small sample to demonstrate how you can improve the performance in windows application using a background thread.

About Author
Vinay Gupta works with Systems Plus and is working on Dot Net technology projects. He can be contacted at:vinay.g@spluspl.com