Mapping SQL Server Query Results to a DataGridView in .NET

By:   |   Comments   |   Related: > Application Development


Problem

In previous tips that I have written about SQL Server and .NET, 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've learned how to work with SQL Server stored procedures and functions from within .NET applications. In this tip, we are going to see something different. We are going to create a .NET Windows Forms Application, retrieve query results from a SQL Server database, and finally display these results in a DataGridView control.

Solution

Raw data is just data. In order to transform data to knowledge, you need to properly process them and present them. One way towards this, is to use Graphical User Interface controls and data visualization. For example, in .NET, one such control is DataGridView.

The DataGridView control in .NET, displays data in a customizable grid. For example, you can retrieve data from SQL Server, i.e. via a query, and display the data on the DataGridView control in a Windows Forms .NET application. Then, using the DataGridView control's features and properties, you can further process the data and customize their display (i.e. sort them, etc.).

In this tip, we are going to perform the below:

  • Create a simple Windows Forms .NET C# application along with including a DataGridView control
  • Run a query against a sample database and retrieve the results
  • Display the results on a DataGridView control
  • Customize the DataGridView control

Sample Database and Data

In this tip's examples, I will be using the database "SampleDB", which is on a test SQL Server 2017 named instance on my local machine, called "SQL2K17". This database, is the same I used in my previous .NET Application Development tips.

Here's a screenshot of the SQL Server instance, as it can be seen in SSMS:

Sample data used for this tip

The sample database has two tables named "employees" and "location" and you can see their content on the above screenshot.

Query for Retrieving Sample Data

The query that will be used for retrieving the sample data is:

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;
GO

If we execute the above SQL query in SSMS against our sample database and tables, this is what we get:

Query that will be used for populating the DataGridView control with data in this tip

As you can see, the SQL query returns 4 rows with the data presented in the above screenshot.

Create the Windows Forms .NET C# application and add a DataGridView control

Let's start a new "Windows Forms App" project in Visual Studio 2017, name it "TestApp5", and save it in the folder "c:\temp\demos":

New Windows Forms App (C#)

Right after the above action, out Windows Forms project opens and the workspace is ready for us to add some GUI controls and write some code!

Workspace for Windows Forms project in Visual Studio.

So, after I increase the size of my main form a bit, from the toolbox on the left I drag a 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 the "Properties" dialog on the right after you select the button control) to "Refresh". Also, to make code look prettier and more manageable, I change the button's "Name" property to "btnRefresh" and the DataGridView's name to "grdData".

After the above, this how my Windows form looks:

Resized the form and controls, and changed the button

The DataGridView Control

There two ways to set the columns of a DataGridView control in .NET. These are:

  • Set static columns by populating the "Columns" collection in the control's properties.
  • Write code that dynamically creates the columns.

Since I find the second option, that is dynamically creating the columns, more robust, I will let my code create them.

Run Query against the Sample Database, Retrieve the Results and Populate the DataGridView Control

Now let's write the query and relevant C# code that retrieves the data from the "SampleDB" database. 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 to the code editor, and more explicitly, in the "Refresh" button's click event code handling block:

The event handling method when the user clicks on the "Refresh" button. To add our handling code here.

The code to be added here, is similar to the code I wrote in 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 I 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-only
      grdData.ReadOnly = true;

      //set the DataGridView control's data source/data table
      grdData.DataSource = ds.Tables[0];


      //close connection
      conn.Close();
   }
 }
 catch (Exception ex)
 {
   //display error message
   MessageBox.Show("Exception: " + ex.Message);
 }

}

Let's discuss the above code in order to better understand it.

As you can see, just like the rest of my .NET tips, everything takes place inside the "using (SqlConnection conn = new SqlConnection(connString))" code block.

So, within that code block, I define the query to be executed against my SQL Server connection and again, I'm using an SqlCommand object. However, in this tip, I introduce the use of some "new" data access classes. To this end, I'm using the "SqlDataAdapter" and "DataSet" classes. So, I basically run the query using my SqlDataAdapter object and fill the DataSet object with the retrieved data which is nothing else than a DataTable. The last step included in the above code, was to set the DataSet's table as the DataGridView control's data source. This last step, automatically creates the DataGridView control's columns and populates the control with the data retrieved by the query.

Now we are ready to compile and run our application. To do this, within our project in Visual Studio, by pressing F6 or 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 F5 (or under the "Debug" menu, we select "Start Debugging") and our program starts:

Running the program. Screen before clicking on the "Refresh" button.

Now, let's click on the "Refresh" button:

Running the program. Screen after clicking on the "Refresh" button.

As you can see, it worked just fine! My application connected to the database, executed the query, retrieved the results and displayed it on the DataGridView control. Now, whenever we click on the "Refresh" button, our application will execute the query and update the DataGridView control with the most recent data.

Customizing the DataGridView Control

While our project runs, by clicking on any column header on the DataGridView control, the data will be sorted based on that column. For example, let's sort the data by location code (ascending order):

Running the program. Sort records by clicking on column header (i.e. locationCode).

For customizing the DataGridView control, you first need to stop the execution of the program (Shift + F5), and then back in the workspace, after selecting the DataGridView control, you can play with the available properties of the control. For example, you can change the BackgroundColor and GridColor properties, etc.:

Customizing the DataGridView conrol by changing its properties.
Conclusion

In this tip, we discussed how you can create a basic Windows Forms .NET application, add a DataGridView control and a button, and add event handling code to the button so that every time it is clicked, it will connect to a SQL Server database, run a query and display the query results dynamically on the DataGridView control. Moreover, we saw how easy it is to further customize the DataGridView control via its properties.

Stay tuned, as we continue our journey into the Data Access world via this exciting tip series.

Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



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

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Comments For This Article

















get free sql tips
agree to terms