Simple Demo of Binding to a Database in WPF using LINQ-SQL
4.34/5 (18 votes)
A beginner example of binding WPF to a database using LINQ-SQL
Background
Having just read Sacha Barber's article WPF: A Beginners Guide - Part 5 of N, I wondered how to get Binding to work where the source of data was an actual database rather than an XML file. Sacha mentioned that his current favourite method involves LINQ-SQL and ObservableCollections of records. So I decided to write a tiny demo to show what was involved. In doing so, I found one or two little things which may be of interest to others.
Introduction
Sacha's article uses a very simple collection of People, where each Person has a PersonName and Id. My question was how to use a database table People where each row represents a Person and stores a PersonId and a PersonName. In producing the app using Visual Studio 2008 C# to try this out, I had to first use Database Explorer to define the database and initial table data for testing, and then add the LINQ-SQL class using Add Item.. from the Solution Explorer. Finally I created a simple ObservableCollection class and added that to the solution.
I kept the UI down to a very simple ListBox to display the People, a field to allow new people to be added, a field to edit a selected Person and a couple of buttons to add, and delete people from the database, as well as a button to Commit changes back to the persistence store of the database.
The DataBase Table
Created by adding a Service Based Database to the Solution using Add Item.. in Solution Explorer and then using Database Explorer, I added the new table People with two fields, PersonID as an int and PersonName as nvchar(50) and set the PersonId fields IsIdentity property to yes. I made a mistake the first time around here in not setting the field as the Primary Key and left the table without a Primary Key defined. This effected the LINQ-SQL class generated when I later added that to the solution.
The LINQ-SQL Class
Using Solution Explorer add Item.. adding a LINQ-SQL class is simple. A designer pane opens onto which I drag the People table from the Database Explorer. I then close that window and am prompted for a name to call the class I had just created, which creates the DataClasses1DataContext class. Wondering what I had just created I took a look through the DataClasses1DataContext.designer.cs file and spotted that the People class didn't have :INotifyPropertyChanged defined on the class, which as you will spot in Sacha's article is quite important. Later when I tried to add a couple of rows of test data to the database using Database Explorer, I got an error saying I couldn't insert into the table because it didn't have a Primary Key. I changed the DB and deleted the DataContext class from the solution and re-created the LINQ-SQL class, and this time the :INotifyPropertyChanged was added to the class.
The ObservableCollection
The LINQ-SQL DataContext object allows me to access the table and extract from that a number of People objects. To make this visible, I want to put them into a collection which can be used by a ListBox as its itemsSource, and an ObservableCollection<People> will allow Binding to work on the ListBox items. So I added a C# class to the solution. I gave it a constructor which will load it up with the People from DataClasses1DataContext when an instance is created.
public class ObservablePeople: ObservableCollection<People>
{
public ObservablePeople(DataClasses1DataContext dataDc)
{
//Open class view to find out what Properties the wizard
//had created in the DataClasses1DataContext class, otherwise
//I wouldn't have known about Peoples
foreach ( People thisPerson in dataDc.Peoples)
{
this.Add(thisPerson);
}
}
}
The WPF C# Code Behind
I gave the WPF window some properties to access and use the database and the collection of People, namely _dataDC a DataClasses1DataContext, and _knownPeople an ObservablePeople, and I added a Window_Loaded method as a convenient place to load the _knownPeople from the _dataDC and bind it to the ListBox.
Adding New Records to the Database
The click handler for the Add button takes the name entered in textBox1 and creates a new People instance with the PersonName set. Adding this to the LINQ-SQL DataContext via the InsertOnSubmit method of the Peoples table works OK, EXCEPT does not result in any change to the UI. This is because the ObservablePeople collection is not automatically updated when the DataContext gets additional content. I had to manually code to insert it to _knownPeople in order for it to be visible. This led to the question of how the new record was working correctly in the DataBinding in the XAML? The answer is that the code places the same object reference into both the DataContext and the ObservableCollection. If I had, in a more complex coding, taken a COPY of an objects data from the datacontext and created a new Object and added that to the ObservableCollection then the new object could be changed, and the object in the DataContext would not be updated and Binding would not update the database. For example, the classic mistake would be to pass the object into a Helper method as a value parameter instead of passing by reference.
Editing Selected People
This is accomplished completely in XAML, textBox2 has a Binding...
Text="{Binding ElementName=listBox1, Path=SelectedItem.PersonName,
UpdateSourceTrigger=PropertyChanged}"
... which binds to the PersonName of the SelectedItem in the ListBox's ItemsSource _knownPeople, and no C# code is required.
Committing the Changes to Database
All the changes made have had an effect on the objects in the _dataDC datacontext object, and have not changed the database table at all. So the Commit button click handler is used to call the _dataDc.SubmitChanges() method to update the database. In the debugging process this led to an observation. Whenever I ran the app and committed some changes and exited the app, and then changed the code, as with the Delete click, the changes to the database disappeared when I next started to debug. However, if I exited and made no code changes and started debugging again the database changes were in fact visible on start up. I've since seen this as a question in a forum and so it is something to remember when you do your first DB app and are wondering if your DB actually changed when you start debugging it.
The Window Code
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;
namespace WpfDbLinqBind1
{
/// <summary>
/// Interaction logic for Window1.xaml
/// </summary>
public partial class Window1 : Window
{
private static DataClasses1DataContext _dataDC = new DataClasses1DataContext();
// class created by wizard when adding a LINQ to SQL item in solution explorer
// and dragging the People table from the data explorer - NB: if you forget to
// set a primary key column in the table before doing that the wizard doesn't
// set INotifyPropertyChanged to the class and you can't insert
// into the database, and binding goes wrong
private ObservablePeople _knownPeople;
public Window1()
{
InitializeComponent();
}
private void Window_Loaded(object sender, RoutedEventArgs e)
{
//populate the observable collection of people from the Database
_knownPeople = new ObservablePeople(_dataDC);
this.listBox1.ItemsSource = _knownPeople;
}
private void add_Click(object sender, RoutedEventArgs e)
{
if (textBox1.Text.Length > 0 )
{
People newPerson = new People();
newPerson.PersonName = textBox1.Text;
//inserts to DB people table, isn't committed yet, and it is
//not visible
_dataDC.Peoples.InsertOnSubmit(newPerson);
//adds to _knownPeople collection, observable class notifies
//listbox and makes visible
_knownPeople.Add(newPerson);
// what I really wanted was to see _knownPeople updated automatically
// when the row was inserted to the datacontext, and vice-versa,
// but that's not happening. binding works on the new record because
// it's literally the same newPerson object in both collections,
// not just a copy.
textBox1.Text = "";
}
}
private void commit_Click(object sender, RoutedEventArgs e)
{
//commit the changes to the database for persistence
_dataDC.SubmitChanges();
// when running in debug mode in VS2008 any rebuild resets the DB back
// to the starting test data we entered in database explorer. Exit the
// App and re-start debugging without changing any code and you see the
// changes are persistent in the DB, and are lost as soon as you re-compile.
// obviously not a problem in the release build. Remember to use
// database explorer to remove the test data before the final
// build unless you want it released
}
private void Delete_Click(object sender, RoutedEventArgs e)
{
if (listBox1.SelectedItem != null)
{
// this works because the object in the listbox source collection
// i.e. _knownPeople is literally the same object held in
// the _dataDc ,so _dataDC knows which record it is
_dataDC.Peoples.DeleteOnSubmit( (People)listBox1.SelectedItem); // mark
// for deletion
_knownPeople.Remove((People)listBox1.SelectedItem);
}
}
}
}
XAML Notes and Queries
The ListBox contains items with two datafields and so the XAML uses a DataTemplate to format it into something the ListBox can display...
<DataTemplate x:Key="pName">
<!-- needed because the record has 2 fields,
primary key PersonId and PersonName, want to display PersonName
-->
<TextBlock Text="{Binding Path=PersonName}"/>
</DataTemplate>
... and the listbox sets the...
ItemTemplate="{StaticResource pName}"
... to use it.
It is convenient that the edit textbox can use the dotted syntax SelectedItem.PersonName to bind to, because a textblock does not have any kind of a handy data Template attribute to convert People to text.
Points of Interest
- When adding a LINQ-SQL class to your project via Add Item.. in Solution Explorer make sure your database has the Primary Key field defined in each table you need to bind to. Otherwise the class created by the wizard won't have
INotifyPropertyChangeddeclared, which can make binding a bit of a problem. - When running in debug mode in Visual Studio 2008, any rebuild resets the DB back to the starting test data entered in database explorer. Exit the App and re-start debugging without changing any code and you see the changes are persistent in the DB, and are lost as soon as you re-compile. Obviously not a problem in the release build. Remember to use Database Explorer to remove the test data before the final build unless you want it released.
- Adding new records to the LINQ-SQL
DataContextobject doesn't automatically update yourObservableCollection, nor vice-versa. - When you add an object to the LINQ-SQL
DataContextobject, add the same object to yourObservableCollection, do not add a COPY of the object, it must be the same object reference. - After the wizard has created your LINQ-SQL
DataContextclass, you can useClassViewto find out what has called the table property to access the table.