Data binding in WPF DataGrid control using SQL Server database

In this article I will explain how to display data from a SQL Server database table in a WPF DataGrid control

Introduction

Displaying data in a grid is a common task in a windows application. In a windows application we use DataGridView control for this task. In WPF we can use DataGrid control for the same. In this article we will display content of Employee table of Pubs database in WPF DataGrid control.

Step 1:

Create a new WPF Application.

Note: I am using Visual C# 2010 Express

Step 2:

Add a new “Application Configuration File” with named “App.config” and your connection string

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
     <connectionStrings>
          <add name="ConString" connectionString="Data Source=deepakSQLExpress; User Id=YourUserName; Password=YourPassword; Initial Catalog=Pubs;"/>
     </connectionStrings>
</configuration>

Step 3:

Add a reference to System.Configuration dll using Add Reference dialog box so that we can access our connection string from code behind. And add following namespaces in MainWindow.xaml.cs file

using System.Configuration;
using System.Data;
using System.Data.SqlClient;

Step 4:

Add a DataGrid control in MainWindow.xaml file

<Grid>
     <DataGrid Name="grdEmployee" />
</Grid>

Write following code in MainWindow.xaml.cs to bind data

public MainWindow()
{
     InitializeComponent();
     FillDataGrid();
}

private void FillDataGrid()
{
     string ConString = ConfigurationManager.ConnectionStrings["ConString"].ConnectionString;
     string CmdString = string.Empty;
     using (SqlConnection con = new SqlConnection(ConString))
     {
          CmdString = "SELECT emp_id, fname, lname, hire_date FROM Employee";
          SqlCommand cmd = new SqlCommand(CmdString, con);
          SqlDataAdapter sda = new SqlDataAdapter(cmd);
          DataTable dt = new DataTable("Employee");
          sda.Fill(dt);
          grdEmployee.ItemsSource = dt.DefaultView;
     }
}

Here first we loaded Employee database table in a DataTable object and then binded it to the DataGrid’s ItemsSource property.

Step 5:

After running the application we get following output

ItemsSource does all for us. It displays all the data in the underlying data source. But it has some limitations like you can see header of the columns are same as the database table column names. To give column’s header a user defined name you can add following code in MainWindow.xaml file

<DataGrid Name="grdEmployee" AutoGenerateColumns="False">
     <DataGrid.Columns>
          <DataGridTextColumn Header="Employee ID" Width="100" Binding="{Binding emp_id}"/>
          <DataGridTextColumn Header="First Name" Width="100" Binding="{Binding fname}"/>
          <DataGridTextColumn Header="Last Name" Width="100" Binding="{Binding lname}"/>
          <DataGridTextColumn Header="Hire Date" Width="100" Binding="{Binding hire_date}"/>
     </DataGrid.Columns>
</DataGrid>

Final Output:

4 thoughts on “Data binding in WPF DataGrid control using SQL Server database

Leave a Reply

Your email address will not be published. Required fields are marked *