By: Artemakis Artemiou | Updated: 2019-01-24 | Comments (4) | Related: > Application Development
Problem
In previous tips, we've learned how to get started with .NET and SQL Server data access. To this end, we've learned how to connect to SQL Server from a C# program and run a simple query, as well as how to query SQL Server tables from .NET and process the results. Moreover, we learned how to work with SQL Server stored procedures and functions from within .NET applications. Also, we've learned how to retrieve query results from a SQL Server database, and display these results in a DataGridView control on a .NET Windows Form.
In this tip, we will talk about the need for multithreading in .NET applications, especially when working with data applications and GUI environments. It is with multithreading that we avoid screen freezes when time-consuming processes are running in the background of our .NET application's forms.
Solution
There are various methods to apply multithreading to a .NET application. In this tip, we will talk about the easiest one, that is the BackgroundWorker .NET Class. Multithreading in GUI applications (i.e. .NET Windows Forms applications), is a must, especially in cases where long-running operations are taking place in the background, for example, retrieving a large volume of information from a SQL Server database.
In this tip, we are going to perform the below:
- Create a sample database in SQL Server with a table that has 1 million records.
- Create a simple Windows Forms .NET C# application with a DataGridView control that displays the sample table's results on a DataGridView control.
- Run the DataGridView data population process and observe that during this operation we won't be able to interact with other GUI controls in our application.
- Add a BackgroundWorker class, set multithreading, run the process again, and observe that this time, we will be able to interact with other GUI controls, while the DataGridView is being populated with data.
Sample SQL Server Database and Data
In this tip's examples, I will be using the database "SampleDB", which can be found on a test SQL Server 2017 named instance on my local machine, which is called "SQL2K17". This database, is similar to the one I used in my previous tips on Application Development:
The only difference from my previous tips, is that I will be adding 1 million records to the "employees" sample table, using the below T-SQL script:
DECLARE @i int=0 WHILE @i<1000000 BEGIN INSERT INTO [dbo].[employees] (code, firstName, lastName, locationID) VALUES('code_' + CAST(@i AS VARCHAR(15)), 'firstName_' + CAST(@i AS VARCHAR(20)), 'lastName_' + CAST(@i AS VARCHAR(20)), (@i%3)+1 ); SET @i=@i+1; END
Let's see a new screenshot of the tables in SSMS:
As you can see, now the "employees" table has 1 million records.
Note: You can try this example with even more sample data.
Create a Simple Windows Forms .NET C# Application with a DataGridView Control
Great! Now, let's start a new "Windows Forms App" project in Visual Studio 2017, name it "TestGridView", and save it in the folder "c:\temp\demos" (this is a similar procedure to one of my previous tips):
Right after we do the above, our Windows Forms project opens and the workspace is ready for us in order to add some controls and handling code.
Like in my previous tip, after I increase the size of my main form a bit, from the toolbox on the left, I drag and drop a DataGridView control, and a button. Next, I increase the size of the DataGridView control, and set the "Text" property for the button (see "Properties" dialog on the right after you select the button control) to "Refresh". Also, to make the code prettier, I change the button's "Name" property to "btnRefresh" and the DataGridView's name to "grdData".
Next, I add another button, set its "Text" property to "Refresh Using BgWorker" and its "Name" property to "btnRefreshUsingBgWorker".
Also, I drag and drop from the toolbox onto the form, a BackgroundWorker object and set its "Name" property to "BgWorker".
Last but not least, I add another button, set its "Name" property to "btnClickMe" and "Text" property to "Click Me".
After the above, this how my Windows form looks like:
Regarding column creation for my DataGridView control, I will let my code to dynamically create them.
Run Query Against the Sample Database, Retrieve the Results and Populate DataGridView Without Multithreading
Now let's run the query that retrieves the data from the "SampleDB" database without using a separate thread. The code will be executed when the "Refresh" button is clicked. So, on our workspace, we double-click on the "Refresh" button in order to navigate the to the code editor, and more explicitly, in the "Refresh" button's click event code handling block (method btnRefresh_Click):
The code to be added here, is the same as the code I wrote in one of my previous .NET development tips. To this end, on the very top of my code, I add the library System.Data.SqlClient:
using System.Data.SqlClient;
Then, in the "btnRefresh_Click" method we add the below code:
private void btnRefresh_Click(object sender, EventArgs e)
{
//set the connection string
string connString = @"Server =.\SQL2K17; Database = SampleDB; Trusted_Connection = True;";
try
{
//sql connection object
using (SqlConnection conn = new SqlConnection(connString))
{
//retrieve the SQL Server instance version
string query = @"SELECT e.id,
e.code,
e.firstName,
e.lastName,
l.code AS locationCode,
l.descr AS locationDescr
FROM dbo.employees e
INNER JOIN dbo.location l
ON l.id = e.locationID;";
//define the SqlCommand object
SqlCommand cmd = new SqlCommand(query, conn);
//Set the SqlDataAdapter object
SqlDataAdapter dAdapter = new SqlDataAdapter(cmd);
//define dataset
DataSet ds = new DataSet();
//fill dataset with query results
dAdapter.Fill(ds);
//set DataGridView control to read-onl
grdData.ReadOnly = true;
//set the DataGridView control's data source
grdData.DataSource = ds.Tables[0];
//close connection
conn.Close();
}
}
catch (Exception ex)
{
//display error message
MessageBox.Show("Exception: " + ex.Message);
}
}
Also, we add the below code to the "btnClickMe_Click" event handling method:
private void btnClickMe_Click(object sender, EventArgs e)
{
MessageBox.Show("Hi, even though data is being retrieved, other controls are responsive!");
}
Now we are ready to compile and run our application. To do this, within our project in Visual Studio, by pressing the key F6or by clicking on the "Build" menu and then click on "Build Solution", our program will be compiled and if everything is OK, that is if we get no errors and see the "Build succeeded" notification on the bottom left corner of the window, it means that the program is now ready for execution. We press on "F5" (or under the "Debug" menu, we select "Start Debugging") and our program starts:
Now, let's click on the "Refresh" button and right after click on the "Click Me" button.
As you can see, while the data is being retrieved, the "Click Me" button is unresponsive. The reason for this, is that everything runs in a single thread. Therefore, in order for the second action to be performed, that is the "Click Me" button click event, you have to wait until the first action (populating the DataGridView with data) is completed.
Run Query Against the Sample Database, Retrieve the Results and Populate DataGridView Using a BackgroundWorker Object
Now, let's configure our BackgroundWorker object in order for populating the DataGridView in a separate thread, thus allowing us to use other controls on the form, such as the "Click Me" button, while the DataGridView control is being populated with data.
The first step, is to create/generate two methods for the BackgroundWorker object. So in "BgWorker" control's properties – Events, we double-click on the "DoWork" and "RunWorkerCompleted" (optional) events, in order for the respective event handling methods to be initialized.
The next step, is to add the handling code for the "DoWork" method (I won't add any handling code for the "RunWorkerCompleted" method, I just created it for illustration purposes).
In the "BgWorker_DoWork" method, we add the code we want to run as a separate thread, that is the data retrieval and DataGridView data population code. Therefore, we add the following code:
//set the connection string stringconnString =@"Server =.\SQL2K17; Database = SampleDB; Trusted_Connection = True;"; try { //sql connection object using (SqlConnection conn =new SqlConnection(connString)) { //retrieve the SQL Server instance version string query =@"SELECT e.id, e.code, e.firstName, e.lastName, l.code AS locationCode, l.descr AS locationDescr FROM dbo.employees e INNER JOIN dbo.location l ON l.id = e.locationID;"; //define the SqlCommand object SqlCommand cmd =new SqlCommand(query, conn); //Set the SqlDataAdapter object SqlDataAdapter dAdapter =new SqlDataAdapter(cmd); //define dataset DataSet ds =new DataSet(); //fill dataset with query results dAdapter.Fill(ds); //make thread-safe call if required if (grdData.InvokeRequired) { Invoke(new MethodInvoker( delegate { //set DataGridView control to read-onl grdData.ReadOnly =true; //set the DataGridView control's data source grdData.DataSource = ds.Tables[0]; })); } else { //set DataGridView control to read-onl grdData.ReadOnly =true; //set the DataGridView control's data source grdData.DataSource = ds.Tables[0]; } //close connection conn.Close(); } } catch (Exception ex) { //make thread-safe call if required //display error message if (this.InvokeRequired) { Invoke(new MethodInvoker( delegate { MessageBox.Show("Exception: " + ex.Message); })); } else { MessageBox.Show("Exception: " + ex.Message); } }
As you can see, the code is similar to the handling code for the "btnRefresh" control, with the only difference that wherever our code accesses GUI components (i.e. the DataGridView, etc.) we make tread-safe calls using the "Control.InvokeRequired Property" in order to avoid any invalid cross-thread operations.
OK, the last step, is to add the handling code for the "btnRefreshUsingBgWorker" control, that is the "Refresh Using BgWorker" button. Therefore, we add the below code in the "btnRefreshUsingBgWorker_Click" method:
private void btnRefreshUsingBgWorker_Click(object sender, EventArgs e)
{
BgWorker.WorkerSupportsCancellation = true;
if (!BgWorker.IsBusy)
BgWorker.RunWorkerAsync();
}
We compile and run our updated application and after the application is started, we click on the "Refresh Using BgWorker" button as well as on the "Click Me" button. Now you will see that the "Click Me" button works during the DataGridView's data population, since the latter is a separate thread.
Conclusion
In this tip, we discussed how you can apply multi-threading in Windows Forms .NET Applications, using the BakgroundWorker class. This allows, while running heavy data-related operations (i.e. populating a DataGridView control with millions of records of data), to allow the user to interact with other GUI controls as well. Even though the example in this tip was simple, it illustrated the concept of multi-threading.
Note that there are cases where it is best to use the BackgroundWorker class, and other cases where is best to use the Thread class. Even though BackgroundWorker is more suitable in cases where a GUI is involved, a future article will thoroughly discuss this topic.
Next Steps
- Check out my tip: How to Get Started with SQL Server and .NET
- Check out my tip: Querying SQL Server Tables from .NET
- Check out my tip: Working with SQL Server Stored Procedures and .NET
- Check out my tip: Working with SQL Server Functions and .NET
- Check out my tip: Mapping SQL Server Query Results to a DataGridView in .NET
- Check out the MS Docs article: DataGridView Class
- Check out the MS Docs article: SqlDataAdapter Class
- Check out the MS Docs article: DataSet Class
- Check out the MS Docs article: .NET Framework Data Providers
- Check out the MS Docs article: SqlConnection Class
- Check out the MS Docs article: SqlCommand Class
- Check out the MS Docs article: BackgroundWorker Class
- Check out the MS Docs article: Thread Class
About the author
Artemakis Artemiou is a Senior SQL Server and Software Architect, Author, and a former Microsoft Data Platform MVP (2009-2018).This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2019-01-24
