WPF MVVM DataGrid Bind from SQL Database

Post Views: 4,015

Let’s see how we can Bind WPF DataGrid from SQL Database using MVVM methodology. WPF MVVM DataGrid Bind from SQL Database.

In this particular example, a WPF DataGrid is filled from SQL Database table named “tblCountries”, using WPF ObservableCollection ( from ViewModel).

My SQL table : tblCountries

Make a WPF DataGrid as below in your Form :

Window1.xaml :

<Window x:Class="WpfApplication2.Window1"
xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
Title="Window1" Height="300" Width="300" WindowState="Maximized">
<StackPanel Orientation="Vertical">

<TextBlock Text="WPF MVVM Datagrid" Foreground="#282828" Margin="5" HorizontalAlignment="Left" FontSize="25"/>
<DataGrid Name="datagrid" ItemsSource="{Binding countries}" AutoGenerateColumns="False" CanUserAddRows="False"
Height="300" Width="300" Margin="5" HorizontalAlignment="Left">
<DataGrid.Columns>
<DataGridTextColumn Header="Id" Binding="{Binding Path='id'}" IsReadOnly="True"/>
<DataGridTextColumn Header="Country" Binding="{Binding Path='country'}" IsReadOnly="True" />
<DataGridTextColumn Header="Date" Binding="{Binding Path='ondate'}" IsReadOnly="True" />
</DataGrid.Columns>
</DataGrid>
</StackPanel>
</Window>

I’m specifying the ItemSource in the design page and also Binding the TextColumn to respective class variables.

In the code page, write the below code :

Window1.xaml.cs :

using System;
using System.Windows;
using WpfApplication2.ViewModel;

namespace WpfApplication2
{
/// <summary>
/// Interaction logic for Window1.xaml
/// </summary>
public partial class Window1 : Window
{
CountriesViewModel co;

public Window1()
{
InitializeComponent();
co = new CountriesViewModel();
base.DataContext = co;
}
}
}

CountriesViewModel is my class inside a folder named “ViewModel” in my project. Make two folders named “ViewModel” and “Model” in your project. Make two classes 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; }

}
}

ViewModel > CountriesViewModel.cs :

using System;
using System.Collections.ObjectModel;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
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 String txtSelectedItem {get;set;}
public CountriesViewModel()
{
txtSelectedItem = "Please select a country";
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, your WPF MVVM DataGrid should look like below :