WPF MVVM Listbox Binding from SQL Database

Post Views: 1,249

Let’s see how we can bind WPF Listbox from SQL Database using MVVM methodology . WPF MVVM Listbox Binding from SQL Database.

In this example, we will Bind a WPF Listbox using ObservableCollection from WPF ViewModel. Data is fetched from MS SQL Database table : tblCountries

To see How you can fetch data from MS SQL Database and bind it to WPF Listbox (Basic, not MVVM), please see this post.

Create a Listbox in your WPF form as below :

MainWindow.xaml:

<Window x:Class="WpfApplication2.MainWindow"
xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
Background="#fff"
Title="WPF ListBox from MS SQL Database" Height="350" Width="525" WindowState="Maximized" WindowStyle="ThreeDBorderWindow">
<StackPanel Orientation="Vertical">
<TextBlock Text="WPF ListBox from MS SQL Database" FontSize="25" FontWeight="Bold" Margin="5" HorizontalAlignment="Left"/>
<ListBox Name="lstBox" HorizontalAlignment="Left" VerticalAlignment="Center" ItemsSource="{Binding countries}"
Background="#fff">
<ListBox.ItemTemplate>
<DataTemplate>
<StackPanel Orientation="Horizontal" HorizontalAlignment="Left" >
<TextBlock Text="{Binding id}" Margin="2" FontWeight="Bold"/>
<TextBlock Text="{Binding country}" Margin="2" />
<TextBlock Text="{Binding ondate}" Margin="2"/>
</StackPanel>
</DataTemplate>
</ListBox.ItemTemplate>
</ListBox>
</StackPanel>
</Window>

In the code page of your MainWindow.xaml make changes as below :

MainWindow.xaml.cs :

using System.Windows;
using WpfApplication2.ViewModel;

namespace WpfApplication2
{
/// <summary>
/// Interaction logic for MainWindow.xaml
/// </summary>
public partial class MainWindow : Window
{
public MainWindow()
{
InitializeComponent();
CountriesViewModel co = new CountriesViewModel();
base.DataContext = co;
}
}
}

CountriesViewModel is my class (viewmodel) using which data will get bind. In the design code for the Listbox, Itemsource is defined.

ItemsSource="{Binding countries}"

countries is our ObservableCollection in our WPF ViewModel class.

Make two folders in your Project with names ViewModel and Model respectively.

In the Model class make a Class with name Countries and edit it as below :

Model > Countries.cs :

using System;

namespace WpfApplication2.Model
{
public class Countries
{
public int id { get; set; }
public string country { get; set; }
public DateTime ondate { get; set; }

}
}

This class contains variables for the elements which will be displayed in the Listbox items. If you look at the design code :

<ListBox Name="lstBox" HorizontalAlignment="Left" VerticalAlignment="Center" ItemsSource="{Binding countries}"
Background="#fff">
<ListBox.ItemTemplate>
<DataTemplate>
<StackPanel Orientation="Horizontal" HorizontalAlignment="Left" >
<TextBlock Text="{Binding id}" Margin="2" FontWeight="Bold"/>
<TextBlock Text="{Binding country}" Margin="2" />
<TextBlock Text="{Binding ondate}" Margin="2"/>
</StackPanel>
</DataTemplate>
</ListBox.ItemTemplate>
</ListBox>

We’re defining these three as Listbox Item members. Okay.

In the ViewModel folder make a class with name CountriesViewModel.cs and edit it as following :

ViewModel > CountriesViewModel.cs :

using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using WpfApplication2.Model;

namespace WpfApplication2.ViewModel
{
public class CountriesViewModel : INotifyPropertyChanged
{
static String connectionString = @"Data Source=vibes\sqlexpress;Initial Catalog=ParallelCodes;User ID=sa;Password=789;";
SqlConnection con;
SqlCommand cmd;
SqlDataAdapter adapter;
DataSet ds;
public ObservableCollection<Countries> countries { get; set; }
public CountriesViewModel()
{
FillList();
}

public void FillList()
{
try
{
con = new SqlConnection(connectionString);
con.Open();
cmd = new SqlCommand("select * from tblCountries", con);
adapter = new SqlDataAdapter(cmd);
ds = new DataSet();
adapter.Fill(ds, "tblCountries");

if (countries == null)
countries = new ObservableCollection<Countries>();

foreach (DataRow dr in ds.Tables[0].Rows)
{
countries.Add(new Countries
{
id = Convert.ToInt32(dr[0].ToString()),
country = dr[1].ToString(),
ondate = Convert.ToDateTime(dr[2].ToString())
});
}
}
catch (Exception ex)
{

}
finally
{
ds = null;
adapter.Dispose();
con.Close();
con.Dispose();
}
}

#region INotifyPropertyChanged Members

public event PropertyChangedEventHandler PropertyChanged;

private void OnPropertyChanged(string propertyName)
{
if (PropertyChanged != null)
{
PropertyChanged(this, new PropertyChangedEventArgs(propertyName));
}
}
#endregion
}
}

Now run your application, you will get below result :