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:
Using the Code:
using System;
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
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
