How to get independent views of an ADO.NET source
I talked about binding to ADO.NET in an earlier blog post. In that post, I explained briefly how binding to ADO.NET behaves differently from other data sources when there are 2 or more views over the source. Today I will explain this scenario in more detail and will show a workaround that makes the binding to ADO.NET behave consistently with binding to other sources.
Consider the scenario where we bind an ItemsControl to a CollectionViewSource whose Source property is set to some collection of objects. In this scenario, a CollectionView is created internally and the ItemsControl is in fact bound to this view (which the CollectionViewSource exposes through its View property). Remember that views are used to sort, filter, and group the items of a collection, as well as track the current item. Let’s assume we sort this view. Now imagine we have a second ItemsControl bound to a second CollectionViewSource whose Source property is set to the same collection as previously. As you probably expect, a second view is created, independent from the first one. This means that we can sort this second view differently, without affecting the order of the items in the first view.
This is the current behavior when the CollectionViewSource’s Source is a collection of objects or XML, but not when it’s an ADO.NET DataTable or DataView. When the source comes from ADO.NET, the view does not handle sorting and filtering itself; instead it delegates those operations to the DataView (if the source is a DataTable we get the corresponding DataView, if the source is a DataView we use it directly). This has the advantage of greatly increasing the performance of these operations. However, it has an important side effect in the behavior of views: they are not independent from each other. Let’s consider the scenario I described above, but this time the Source property of both CollectionViewSources is set to the same DataTable. When we add a SortDescription to the CollectionViewSource, internally we get the DataView that corresponds to the DataTable and use that to sort it. Because both CollectionViewSources use the same DataTable, they will end up sorting by using the same DataView. Consequently, sorting one CollectionViewSource will affect the items displayed in both ItemsControls.
Let’s look at the come that illustrates this scenario. I started by defining an Access data source that happens to contain the names of the sacred rivers of India. I wrote a simple method that uses ADO.NET to fill a DataTable with that data, which you can see below:
private DataTable GetData()
{
string mdbFile = "IndiaSacredRivers.mdb";
string connString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0; Data Source={0}", mdbFile);
OleDbConnection conn = new OleDbConnection(connString);
DataTable sacredRiversTable = new DataTable();
OleDbDataAdapter sacredRiversAdapter = new OleDbDataAdapter();
sacredRiversAdapter.SelectCommand = new OleDbCommand("SELECT * FROM SacredRivers;", conn);
sacredRiversAdapter.Fill(sacredRiversTable);
return sacredRiversTable;
}
Then, I added the DataTable to the Window’s resources, in the Window’s constructor.
public Window1()
{
this.Resources.Add("sacredRiversTable", this.GetData());
InitializeComponent();
(…)
}
In XAML, I added two CollectionViewSources to the Window’s resources and pointed their Source property to the DataTable I added to the resources before. I then added two ListBoxes and bound each of them to a CollectionViewSource:
<Window.Resources>
<CollectionViewSource Source="{StaticResource sacredRiversTable}" x:Key="cvs1"/>
<CollectionViewSource Source="{StaticResource sacredRiversTable}" x:Key="cvs2"/>
(…)
</Window.Resources>
<ListBox ItemsSource="{Binding Source={StaticResource cvs1}}" DisplayMemberPath="RiverName" (…) />
<ListBox ItemsSource="{Binding Source={StaticResource cvs2}}" DisplayMemberPath="RiverName" (…) />
Finally, I added a Button that, when pressed, adds a SortDescription to the first CollectionViewSource.
<Button Content="Sort first view descending" Click="SortCvs1DescendingHandler" (…) />
private void SortCvs1DescendingHandler(object sender, RoutedEventArgs args)
{
cvs1.SortDescriptions.Clear();
cvs1.SortDescriptions.Add(new SortDescription("RiverName", ListSortDirection.Descending));
((Button)sender).IsEnabled = false;
}
When you press this Button, you will notice that both ListBoxes become sorted. This is not the desired behavior - it’s an unfortunate tradeoff we had to make to maximize the performance of these operations. The good news is that there is an easy workaround that will produce a behavior consistent with the other sources. If you need independent sorting, you can simply create two different DataViews, by passing the DataTable as a parameter to the constructor, and use those as your sources. This can all be done in XAML, with the help of ObjectDataProvider:
<Window.Resources>
<ObjectDataProvider x:Key="independentView1" ObjectType="{x:Type data:DataView}">
<ObjectDataProvider.ConstructorParameters>
<StaticResource ResourceKey="sacredRiversTable" />
</ObjectDataProvider.ConstructorParameters>
</ObjectDataProvider>
<ObjectDataProvider x:Key="independentView2" ObjectType="{x:Type data:DataView}">
<ObjectDataProvider.ConstructorParameters>
<StaticResource ResourceKey="sacredRiversTable" />
</ObjectDataProvider.ConstructorParameters>
</ObjectDataProvider>
<CollectionViewSource Source="{StaticResource independentView1}" x:Key="cvs3"/>
<CollectionViewSource Source="{StaticResource independentView2}" x:Key="cvs4"/>
(…)
</Window.Resources>
<ListBox ItemsSource="{Binding Source={StaticResource cvs3}}" DisplayMemberPath="RiverName" (…) />
<ListBox ItemsSource="{Binding Source={StaticResource cvs4}}" DisplayMemberPath="RiverName" (…) />
This time, clicking the Button to sort the first view will have no effect in the order of the items of the second ListBox, as desired.
<Button Content="Sort first view descending" Click="SortCvs3DescendingHandler" (…) />
private void SortCvs3DescendingHandler(object sender, RoutedEventArgs args)
{
cvs3.SortDescriptions.Clear();
cvs3.SortDescriptions.Add(new SortDescription("RiverName", ListSortDirection.Descending));
((Button)sender).IsEnabled = false;
}
Here is a screenshot of this application:
Here you can find the VS project with this sample code. This works with RTM WPF bits.
Ivan (elgoog)
I have a problem an it seems that I can not find a solution… I have a DB data. One table (Customers) has column with foreign keys (Countries). I need a xaml form that shows data from table and combobox to select another foreign key, that is country customer lives in. This is not master detail scenario, and I can not connect selected value (country combobox) to be populated in current record of customers.
Any ideas?
Thanx in advance… Ivan
January 12, 2007 at 3:42 pm
Ivan (elgoog)
Unbelievable, I had to ask for help in order to find solution several seconds later…
<ComboBox
x:Name=”cmbCountry”
ItemsSource=”{Binding Path=COUNTRY}”
DisplayMemberPath=”COUNTRY_NAME”
SelectedValuePath=”COUNTRY_ID”
SelectedValue=”{Binding CUSTOMERS/COUNTRY_ID}”/>
Anyway, thanx for your postings
Ivan
January 12, 2007 at 4:09 pm
Bea
Good to know you found the answer.
Thanks,
Bea
January 12, 2007 at 4:58 pm
krijn
Hi
It seems that editing one DataRow (DataRowView at runtime) inside another Dialog doesn’t work for more then one value by using BeginEdit, CancelEdit or EndEdit.
Steps taken :
1.ListBox with itemssource = EmployeesDataTable and simple DataTemplate
2. EditEmployeeDialog with ShowDialog(dataContext)
3. code to show the Dialog :
dataRowView.BeginEdit
if dialog.ShowDialog(dataRowView) ..
dialog.EndEdit
Although data inside the DataRow is changed the UI doesn’t get updated correctly ..
Unless I only edit one particular field !
Have you any idea what problem is causing this behavior ?
Thanks
January 13, 2007 at 12:27 am
krijn
.. unless I Refresh the DataView manually after the EndEdit call :
CollectionViewSource.GetDefaultView(rowView.DataView).Refresh()
January 13, 2007 at 12:29 am
Bea
Hi Krijn,
Well, you just found a bug, and a workaround for that bug.
I was able to repro the bad behavior you described (which is independent of the dialog box). Thanks a lot for reporting this issue, I really appreciate it!
Bea
January 14, 2007 at 10:58 pm
Ivan (elgoog)
Hello Beatriz,
I have folowing scenario…
One DataTable, 2 ListViews bind to two new DataRowViews of mentioned DataTable. Both ListViews are set to SelectionMode.Single. DataTables have diferent Filter depending on Date so they are presenting different rows.
And following problem…
When performing dragdrop functionality Selection is not working properly. Since I dropped item “From one Date” “To another Date” I change the row.Date property and use following …
foreach (DataRowView drv in row.Table.DefaultView)
{
if (row == drv.Row)
{
lv.SelectedItem = drv;
lv.ScrollIntoView(drv);
break;
}
}
As expected this selects and brings new row into view… But, all previously selected rows remain selected.
lv.UnselectAll() does not work, and DataRowView does not have Selected property…
Am I missing something?
Best regards, Ivan
January 14, 2007 at 7:59 am
Bea
Hi Ivan,
There are several issues with binding to DataView today, due to the way this class was implemented. I explained one of consequences of that implementation (and workaround) in this post, and it seems to me like you’re hitting another one.
I will make sure that we have a bug tracking that issue. Thanks a lot for reporting this!
Bea
January 14, 2007 at 10:59 pm
John Eyles
Hi Beatriz.
I’m trying to databind an animating panel to an ADO.NET datatable. Each item in the panel is represented by a custom control, which I have to hook events to (mouse enter, mouse leave). Additionally, the custom controls change color, opacity etc based on the underlying data. If I bind an ItemsControl to a DataSet, the Items property returns a data row view collection, which I can’t hook events to. Do I need to load the dataset data into an observable collection before binding it to the ItemsControl? Otherwise what is the best way of accessing the items generated by the ItemsControl as MyCustomItem type?
I am setting the data context in code behind (as per your blogs – thanks!), and setting the custom control type and visual tree of each item in a data template.
ItemsControl ItemsSource=”{Binding}” x:Name=”lstItems” ItemTemplate=”{DynamicResource DataTemplate1}” ItemsPanel=”{DynamicResource ItemsPanelTemplate1}”
January 15, 2007 at 7:20 am
Bea
Hi,
Regarding your question about the animated panel bound to an ADO.NET DataTable. If I understand correctly, you want a handle to your custom control instances so that you can set event handlers on those.
If you have your custom controls in a DataTemplate (which I’m assuming is your scenario), I don’t think you can add a handler to the event directly, but you can use an EventSetter to do that. You can read up more on EventSetters here.
It’s true that when you read the Items property of ItemsControl you get items from the actual data collection, and not the visuals created to wrap the data. This is actually the intended design. Here you can find a blog posts where I explained how to get to the ComboBoxItem when all you have is the data item. In your case, since you’re using an ItemsControl, the generated item that is wrapping your custom control is a ContentPresenter (and not a ListBoxItem or ComboBoxItem like in my earlier blog samples).
I noticed that you are using DynamicResources in your XAML. Are your templates being swapped at runtime? If not, StaticResource is a better choice because it will give you better performance.
Hope this helps.
Bea
January 15, 2007 at 10:49 pm
John Eyles
Thanks Beatriz (re databinding an animated panel). Dan Crevier suggested using a Value Converter to convert from a DataRowView to MyCustomItem. I can do it for the entire dataset (and convert it to a collection of myCustomItem), but is it possible to apply a Value Converter to individual items loaded (to change their type) and keep the ItemsControl bound to a dataset? Funny enough, the dynamic resource tags where generated by Expression Blend. I’ve just been lazy
Cheers, John Eyles.
January 16, 2007 at 5:32 am
Bea
Hi John,
I personally prefer to do as much as I can in templates, to have as much as possible in XAML. Sometimes you need more flexibility than what we offer within templates, and in that case I value converters are the best option. This may be your situation.
Blend creates DynamicResources most of the time to allow swapping of resource dictionaries at runtime. If you need this feature, then you should keep that code, otherwise you can have a slight performance improvement by using StaticResources.
Thanks,
Bea
January 16, 2007 at 10:49 pm