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 a DataGridView control for this task. In WPF we can use a DataGrid control. In this article we will display the contents of an Employee table of Pubs database in a 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 the named "App.config" and your connection string.

<?xml version="1.0" encoding="utf-8" ?>

<configuration>

  <connectionStrings>

    <add name="ConString" connectionString="Data Source=deepak\SQLExpress; User Id=YourUserName; Password=YourPassword; Initial Catalog=Pubs;"/>

  </connectionStrings>

</configuration>

Step 3:

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

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

Step 4:

Add a DataGrid control in the MainWindow.xaml file:
 

<Grid>

    <DataGrid Name="grdEmployee" />

</Grid>
 

Write the 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 the Employee database table in a DataTable object and then bound it to the DataGrid's ItemsSource property.

Step 5:

After running the application we get the following output:

ItemsSource does it all for us. It displays all the data in the underlying data source. But it has some limitations, such as the header of the columns are the same as the database table column names. To give a column's header a user-defined name you can add the following code in the 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: