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.

publicclass 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 Peoplesforeach ( 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

... 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
{
///<spanclass="code-SummaryComment"><summary></span>/// Interaction logic for Window1.xaml
///<spanclass="code-SummaryComment"></summary></span>publicpartialclass Window1 : Window
{
privatestatic 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 wrongprivate ObservablePeople _knownPeople;
public Window1()
{
InitializeComponent();
}
privatevoid Window_Loaded(object sender, RoutedEventArgs e)
{
//populate the observable collection of people from the Database
_knownPeople = new ObservablePeople(_dataDC);
this.listBox1.ItemsSource = _knownPeople;
}
privatevoid 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 = "";
}
}
privatevoid 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
}
privatevoid 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...

<DataTemplatex:Key="pName"><!--<span class="code-comment"> needed because the record has 2 fields,
primary key PersonId and PersonName, want to display PersonName
--></span><TextBlockText="{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 INotifyPropertyChanged declared, 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.

I'm sorry I am just beginning to learn WPF. How would we handle (using the same set up), using the same listbox to display different tables in the database. Say you also had a view addresses, or a view companies button for the respected tables, but used that same listbox that is used for the people. Would it be a dynamic resource then?

I was wondering, how did you publish the project so that the database would be included as a standalone file, and that changes made to the db during runtime is persistent each time the application is opened?

Hmm, adding a new record and making it sort into its appropriate position on screen before doing a COMMIT could be troublesome.

so the sort may need to be done on the observable collection itself rather than in the SQL.

so it looks like you need to add a sort method to the observable collection and I think you will also need to add a comparason method to the ObservablePeople class which will be used by the collection sort method to compare 2 people to see which is less than, equal to, or greater than the other. After adding a new People to _knownPeople call your new _knownPeople.sort() method.

I would have said add the comparason method to the People class since its that class which should encapsulate such a comparason based on the properties of a People, but that lives within the wizard generated DataContext class and manually editing that might be troublesome. Although it could be done in a separate file as a class extension, which would not be overwitten if the class is every re-generated by the wizard.

I keep running into the problem that WPF listbox doesnt provide the same methods as the windows forms listbox, No sorted property in the WPF version.

Reordering of items within the collection isnt important for adding or deleting to the data context since the items are identified by the Object reference for those operations, not by their position in the collection(s).

Please, tell me what I need to do if:
1) I have two tables, for example, People and Details (one person - many details)
2) I want to see person's details when this person is selected
3) I want to add, edit, delete items in Details
4) each table is represented in Listbox

To be honest I havnt had a need to play with databases since I wrote this article, I ve been playing with hardware and Arduino stuff, so I dont have an example of how to do this.

for fear of showing total ignorance I wont attempt to present a fully worked solution.

But the way I would do it is to start with the data database wizard and have two tables in the data context. The connection between the two tables will be to select from detailsTables using the person_id from the Peoples table, and then creating the observablePeople as an ObservableCollection on the datacontext (that bit is unchanged from the current article) which will ripple through and change the ObservablePeople class. The Linq to SQL bit will perform the SELECT so that for each person in the peoples table it will automatically return the details for that person in addition to the Person name. In effect the datacontext will provide a wider record with the extra fields on a People object which has the details for that person.

For that to work obviously detailsTable must have person_id as a primary key.

The ObservablePeople class will have additional fields eg .address so to add just the address field to a listbox with the address use an XAML binding similar to the one used to put the person name in list box eg

(I dont know if the less than , greater than brackets will come out right in the above due to HTML interpreting them, if you cant see any they didnt)

and for the listbox
ItemTemplate="{StaticResource pAddress}"

HOWEVER: It would definately be better to have a textbox rather than a listbox for each detail field, which will use the 'dotted' (eg selectedperson.Address) syntax to get the right content (see the article)

What Im thinking of is a listbox of peoples names from which to select the person and a series of textboxes which get populated with that persons details fields. Please remember this is a simple example there are many others who could sugest better ways.

when you add a new person fill each field in the record from the appropriate textbox before adding it.

for editing each detail ... erm ... should be the same as the edit mechanism used to edit the personName TEXTBOX follow the XAML bind on that field as an example.

in summary use linq sql for the database access setup using the database wizard. That gives the output from the datacontext as a single combined set of fields for each person.

Whenever I close the application after debugging the database still contains those 3 entries. Please help me. I have clicked the Commit button but it still doesnt work. Please guide me I am new to LINQ (and also a student).

I'm trying to use this code with a Microsoft SQL server 2005 instead of Service Based Database.

Could you show me how I can change this project to work with Microsoft SQL server 2005.
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);
}
}
}

I used adding a Service Based database to keep this simple and create a local database and get it added to the database explorer. Once added it was a simple vehicle to use with o/r designer to drag the tables when creating the linq-sql classes.

I have since tried adding the Northwind sample project database and found a problem. The Northwind database added was a Microsoft SQL Server compact 3.5 database, and designer onto which you drag the tables doesnt accept them. It complains because it only accepts Microsoft SQL Server Database, and not the compact 3.5 version. Which is very annoying.

However In theory if you can get your Microsoft SQL Server Database added to database explorer as a connection you can use it to create the linq-sql classes.

In the ObservablePeople class just make sure the dataDc is the same type as the datacontext created for the linq-sql class. if you didnt use the name DataClasses1 for you linq-sql class you should change it to match.

so if your database is myOwnDb database, and you called your linq-sql class myOwnDbClasses change DataClasses1. eg myOwnDbClassesDataContext instead of DataClasses1DataContext

Look in solution explorer for the .dbml entry created when you created the linq-sql class. expand and open the .cs file, that should let you see the exact name to use.

(Naturally I expect you called your observable class for example ObservableWhatever instead of ObservablePeople, to access the Whatever table in your database )

next in View->Class view from the menu find the entry for your datacontext and left click on it, that drops down a list of the properties in the datacontext, your tables are near the bottom of the list so you find out precisely what they are called. (it seems a convention that it just adds an s to the end of the table name.)

hence in your ObservableWhatever you replace People with Whatever and Peoples with Whatevers eg

Although you may not see any possible use for a primary key in your particular SQL Server database application, setting a primary key on those tables into which you intend to insert data via LINQ to SQL is imperative. I kept getting the error, "A first chance exception of type 'System.InvalidOperationException' occurred in System.Data.Linq.dll" until I set up a key via the SQL Server Management Studio Express.