In this article, I discuss how we can use ADO.NET DataSet to get data from a database and display in a WPF ListBox control using data binding method in WPF.
I used Northwind.mdf database comes with SQL Server. In my application, I read data from Customers table. The Customers table columns looks like Figure 1.
Figure 1.
I will read ContactName, Address, City, and Country columns in a WPF ListBox control. The final ListBox looks like Figure 2.
Figure 2.
Now let's look at the XAML file. I create resources DataTemplate type called listBoxTemplate. A data template is used to represent data in a formatted way. The data template has two dock panels where first panel shows the name and second panel shows address, city, and country columns by using TextBlock controls.
The key of data binding is the Binding attribute of a control in XAML. The Binding attribute takes a property of the data source. For example, we are binding a TextBlock with ContactName in the below code.
<Window.Resources>
<DataTemplate x:Key="listBoxTemplate">
<StackPanel Margin="3">
<DockPanel >
<TextBlock FontWeight="Bold" Text="Name:"
DockPanel.Dock="Left"
Margin="5,0,10,0"/>
<TextBlock Text=" " />
<TextBlock Text="{Binding ContactName}" Foreground="Green" FontWeight="Bold" />
</DockPanel>
<DockPanel >
<TextBlock FontWeight="Bold" Text="Address:" Foreground ="DarkOrange"
DockPanel.Dock="Left"
Margin="5,0,5,0"/>
<TextBlock Text="{Binding Address}" />
<TextBlock Text=", " />
<TextBlock Text="{Binding City}" />
<TextBlock Text=", " />
<TextBlock Text="{Binding Country}" />
</DockPanel>
</StackPanel>
</DataTemplate>
</Window.Resources>
Now I add a ListBox control and set its ItemsSource property as the first DataTable of the DataSet and set ItemTemplate to the resource defined above. A ListBox control has no idea how to display the data on the UI and by providing a ItemTemplate we tell ListBox control how to display and format the data.
<ListBox Margin="17,8,15,26" Name="listBox1" ItemsSource="{Binding Tables[0]}"
ItemTemplate="{StaticResource listBoxTemplate}" />
Now in the code behind, I define following variables.
public SqlConnection connection;
public SqlCommand command;
string sql = "SELECT ContactName, Address, City, Country FROM Customers";
string connectionString = @"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\NORTHWND.MDF;Integrated Security=True;Connect Timeout=30;User Instance=True";
Now on Windows_Loaded method, I call BindData method and in BindData method, create a connection, data adapter, and fill the DataSet using SqlDataAdapter.Fill() method. In the end, we need to set the ListBox.DataContext property to the DataSet.
private void Window_Loaded(object sender, RoutedEventArgs e)
{
BindData();
}
private void BindData()
{
DataSet dtSet = new DataSet();
using (connection = new SqlConnection(connectionString))
{
command = new SqlCommand(sql, connection);
SqlDataAdapter adapter = new SqlDataAdapter();
connection.Open();
adapter.SelectCommand = command;
adapter.Fill(dtSet, "Customers");
listBox1.DataContext = dtSet;
}
}
Summary
This article shows how to get data from a database using ADO.NET DataSet and bind DataSet to a WPF ListBox control.