Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Learn how to build a multithreading .NET Application to work with SQL Server


By:   |   Last Updated: 2019-01-24   |   Comments (4)   |   Related Tips: More > 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:

query executed successfully

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:

sql query

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):

windows forms app

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.

test grid view

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:

test grid view

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):

visual studio

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:

form 1

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.

do work
test grid view

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.

form 1

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


Last Updated: 2019-01-24


next webcast button


next tip button



About the author
MSSQLTips author Artemakis Artemiou Artemakis Artemiou is a Senior SQL Server and Software Architect, Author, and a former Microsoft Data Platform MVP (2009-2018).

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Thursday, January 31, 2019 - 12:42:42 PM - Artemakis Artemiou Back To Top

Hi,

Thank you very much for your kind words.

I'm really glad you found my tip useful. 

Best regards, 

Artemakis


Wednesday, January 30, 2019 - 11:03:01 PM - shravan Back To Top

Excellent Post...

It is really helpful for the developers. 

Thanks


Thursday, January 24, 2019 - 4:27:06 PM - Jeremy Kadlec Back To Top

Doren,

Thank you for the feedback.

In this tip, Artemakis worked with the BackgroundWorker class for multi-threading, since he used a Windows Form. 

The BackgroundWorker allows a more managed way for working with multi-threading and GUI components.

Artemakis will be covering a total of 4 .NET multithreading techniques.

Until those tips are published, feel free to refer to the following resources:

- Using the BackgroundWorker Class

https://docs.microsoft.com/en-us/dotnet/api/system.componentmodel.backgroundworker?view=netframework-4.7.2

- Using the Thread Class 

https://docs.microsoft.com/en-us/dotnet/api/system.threading.thread?view=netframework-4.7.2

- Using the ThreadPool Class

https://docs.microsoft.com/en-us/dotnet/api/system.threading.threadpool?view=netframework-4.7.2

- Using the Task Parallel Library

https://docs.microsoft.com/en-us/dotnet/standard/parallel-programming/task-parallel-library-tpl

Thank you,
Jeremy Kadlec
Community Co-Leader


Thursday, January 24, 2019 - 5:21:46 AM - Doren Back To Top

This example is no longer relevant.


Learn more about SQL Server tools