Data binding in WPF DataGrid control using SQL Server database

Introduction

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

Using the code

Followings are the steps to achive the above task.

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

<configuration>
 <connectionstrings>
    <add connectionstring="Data Source=YourDataSource; User Id=YourUserName;Password=YourPassword; Initial Catalog=Pubs;" name="ConString"/>
 </connectionstrings>
</configuration> 

Step 3: 

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

Step 4:   

Add a DataGrid control in the MainWindow.xaml file.

<datagrid name="grdEmployee" />  

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

Step 5:

After running the application we get the following output

ItemsSource does it all for us. It displays all the data from 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 change DataGrid definition as following code in MainWindow.xaml file

<datagrid alternatingrowremoved="#FFC4B0B0" height="400" width="350" autogeneratecolumns="False" name="grdEmployee">
    <datagrid.columns>
        <datagridtextcolumn binding="{Binding FirstName}" width="100" header="First Name"/>
        <datagridtextcolumn binding="{Binding LastName}" width="100" header="Last Name"/>
        <datagridtextcolumn binding="{Binding Salary}" width="100" header="Salary"/>
    </datagrid.columns>
</datagrid>

Final Output:  

Points of Interest

When we set ItemsSource property of a DataGrid to a data source, it displays all all the columns with its default name. To give a user defined name to the DataGrid columns we have to set AutoGenerateColumns="False" and define its columns explicitly.

History

• 9 April, 2012.