SQLServer | Mike Taulty

Following on from that previous post about the WPF DataGrid I thought it might be interesting to try and move towards more of a master/details view with a separate grid displaying the orders for each customer.

I modified the UI a little bit;

<Window
  x:Class="BlogPost.Window1"
  xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
  xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
  Title="Window1"
  Height="600"
  Width="800"
  xmlns:grid="http://schemas.microsoft.com/wpf/2008/toolkit">
  <Grid>
    <Grid.Resources>
      <Style
        x:Key="myStyle"
        TargetType="{x:Type Control}">
        <Setter
          Property="Margin"
          Value="10" />
        <Setter
          Property="FontSize"
          Value="16" />
      </Style>
    </Grid.Resources>
    <Grid.RowDefinitions>
      <RowDefinition Height="Auto"/>
      <RowDefinition
        Height="2*" />
      <RowDefinition
        Height="2*" />
      <RowDefinition Height="Auto"/>
    </Grid.RowDefinitions>
    <TextBox
      x:Name="txtCountry"
      Style="{StaticResource myStyle}"      
      Text="{Binding Country}" />
    <grid:DataGrid
      x:Name="customersGrid"
      Grid.Row="1"
      Style="{StaticResource myStyle}"
      AutoGenerateColumns="True"
      ItemsSource="{Binding}" 
      InitializingNewItem="OnCustomersGridRowInsert"/>
    <GridSplitter
      Grid.Row="2" HorizontalAlignment="Stretch"/>
    <grid:DataGrid
      x:Name="ordersGrid"
      Grid.Row="2"
      Style="{StaticResource myStyle}"
      AutoGenerateColumns="True"
      ItemsSource="{Binding ElementName=customersGrid,Path=SelectedValue.Orders}"/>
    <Button
      Grid.Row="3"
      Style="{StaticResource myStyle}"
      Content="Submit Changes"
      Click="OnSubmitChanges" />
  </Grid>
</Window>

There’s something really simple and really smart going on in the UI. Notice that the second grid ( ordersGrid ) is simply declaratively binding its ItemsSource to the property SelectedValue.Orders of the customersGrid. Pretty smart.

I also updated the code behind a little bit ( I think I removed one or two functions to make it smaller ) to end up with;

  public partial class Window1 : Window, INotifyPropertyChanged
  {
    public Window1()
    {
      InitializeComponent();

      this.Loaded += OnLoaded;
    }
    void OnLoaded(object sender, RoutedEventArgs e)
    {
      // Note, letting the orders lazily load.
      dataContext = new NorthwindDataContext();

      txtCountry.DataContext = this;
    }
    public string Country 
    {
      get
      {
        return (country);
      }
      set
      {
        country = value;
        Requery();
        FirePropertyChanged("Country");
      }
    }
    void Requery()
    {
      customersGrid.DataContext = dataContext.Customers.Where(c => c.Country == txtCountry.Text);
    }
    void OnCustomersGridRowInsert(object sender, InitializingNewItemEventArgs e)
    {
      Customer c = e.NewItem as Customer;

      if (c != null)
      {
        c.Country = txtCountry.Text;
      }
    }
    void FirePropertyChanged(string property)
    {
      if (PropertyChanged != null)
      {
        PropertyChanged(this, new PropertyChangedEventArgs(property));
      }
    }
    void OnSubmitChanges(object sender, EventArgs args)
    {
      // This needs error handling in the real world.
      dataContext.SubmitChanges();
    }
    NorthwindDataContext dataContext;
    string country;

    public event PropertyChangedEventHandler PropertyChanged;
  }

That gives me a working UI in that I can display data;

and also, within reason, I can update data. What I mean by “within reason” is that I’m not sure how well this would respond to me changing (e.g.) a foreign key value but then it’s perhaps pretty dubious for me to be displaying that to the user in the first place.

Note that I’m quite happily letting LINQ to SQL do lazy loading on the Orders property of the Customer entity rather than pre-loading all of those orders for the customers.

I can also go ahead and insert new customers and orders for those customers into this grid – works pretty well in that I can go and create a new customer on that top grid and then add an order to it on that bottom grid by just filling in some value ( the freight in this case );

and then submit my changes and that works exactly as you’d expect. The interesting thing here is that LINQ to SQL goes ahead and requeries for the OrderID of the inserted order and then the change notification fires and so my new order ID automatically appears on the screen. However, the new CustomerID doesn’t appear on there which feels a little strange.

However, if I navigate to another order ( even before calling SubmitChanges ) and then back again the CustomerID is correctly set as below;

So what’s happening there? When we call submit changes we go an do an insert into the Orders table in the database and then LINQ to SQL will requery for the identity column ( orderId ) by using scope_identity(). It then sets this on my new order object which causes a property change notification to be fired into the grid which works fine.

But this is not how the CustomerID is being set on that order. The CustomerID is never set via the property setter. It’s set much earlier before we get anywhere near to SubmitChanges. What happens is that the Customer is set on the Order and the generated code goes and sets the CustomerID field but not the property meaning that you don’t get property change notification so the grid doesn’t update at the time that the CustomerID gets set.

What I ended up doing was to modify my code a little so that it picks up any inserted order from the DataGrid InitializingNewItem event and it syncs up to any change on the Customer on that newly inserted item and when it detects a change to the Customer it forces the Order to also send a CustomerID property change notification.

That meant modifying the definition of my ordersGrid;

    <grid:DataGrid
      x:Name="ordersGrid"
      Grid.Row="2"
      Style="{StaticResource myStyle}"
      AutoGenerateColumns="True"
      DataContext="{Binding ElementName=customersGrid,Path=SelectedValue.Orders}"
      ItemsSource="{Binding}"
      InitializingNewItem="OnOrdersGridRowInsert"/>

and then modifying my code behind a little in a few places. I modified the Order class by adding a partial method which will allow me to call its (protected) SendPropertyChanged method;

  public partial class Order : INotifyPropertyChanging, INotifyPropertyChanged
  {
    public void ForcePropertyChanged(string property)
    {
      SendPropertyChanged(property);
    }
  }

and I added a couple of more pieces to my code behind class;

    void OnOrdersGridRowInsert(object sender, InitializingNewItemEventArgs e)
    {
      Order o = e.NewItem as Order;

      if (o != null)
      {
        o.PropertyChanged += OnOrderPropertyChanged;
      }
    }
    void OnOrderPropertyChanged(object sender, PropertyChangedEventArgs e)
    {
      if (e.PropertyName == "Customer")
      {
        Order o = sender as Order;

        if (o != null)
        {
          o.ForcePropertyChanged("CustomerID");
          o.PropertyChanged -= OnOrderPropertyChanged;
        }
      }
    }

and now it’s all working the way I’d expect with respect to Inserts. I’m not at all sure that this is a “good” way of achieving this, though.

What about deletes? I can select a bunch of orders and delete them and that seems to work just fine and if I delete orders and their corresponding customer then that works as well.

Where it fails ( as you might expect ) is if I delete a customer without doing anything with the related orders. If I want to make that work then I can modify the code a little. I did this by modifying the UI code – didn’t find it quite as easy as I would have liked in that I seem to have to go down to the route of grabbing an ICollectionView from the customersGrid and then waiting for its CollectionChanged event whereas I was perhaps expecting the DataGrid to have some kind of Deleted event. Here’s the code I ended up with;

  public partial class Window1 : Window, INotifyPropertyChanged
  {
    public Window1()
    {
      InitializeComponent();

      this.Loaded += OnLoaded;
    }
    public string Country 
    {
      get
      {
        return (country);
      }
      set
      {
        country = value;
        Requery();
        FirePropertyChanged("Country");
      }
    }
    void OnLoaded(object sender, RoutedEventArgs e)
    {
      // Note, letting the orders lazily load.
      dataContext = new NorthwindDataContext();
      txtCountry.DataContext = this;
    }
    void Requery()
    {
      customersGrid.DataContext = dataContext.Customers.Where(c => c.Country == txtCountry.Text);
      ICollectionView colView = CollectionViewSource.GetDefaultView(customersGrid.DataContext);
      colView.CollectionChanged += OnCustomerListModified;
    }
    void OnCustomerListModified(object sender, System.Collections.Specialized.NotifyCollectionChangedEventArgs e)
    {
      if (e.Action == NotifyCollectionChangedAction.Remove)
      {
        foreach (object o in e.OldItems)
        {
          Customer c = o as Customer;

          if ((c != null) && (c.Orders != null))
          {
            dataContext.Orders.DeleteAllOnSubmit(c.Orders);
          }
        }
      }
    }
    void OnCustomersGridRowInsert(object sender, InitializingNewItemEventArgs e)
    {
      Customer c = e.NewItem as Customer;

      if (c != null)
      {
        c.Country = txtCountry.Text;
      }
    }
    void OnOrdersGridRowInsert(object sender, InitializingNewItemEventArgs e)
    {
      Order o = e.NewItem as Order;

      if (o != null)
      {
        o.PropertyChanged += OnOrderPropertyChanged;
      }
    }
    void OnOrderPropertyChanged(object sender, PropertyChangedEventArgs e)
    {
      if (e.PropertyName == "Customer")
      {
        Order o = sender as Order;

        if (o != null)
        {
          o.ForcePropertyChanged("CustomerID");
          o.PropertyChanged -= OnOrderPropertyChanged;
        }
      }
    }
    void FirePropertyChanged(string property)
    {
      if (PropertyChanged != null)
      {
        PropertyChanged(this, new PropertyChangedEventArgs(property));
      }
    }
    void OnSubmitChanges(object sender, EventArgs args)
    {
      // This needs error handling in the real world.
      dataContext.SubmitChanges();      
    }
    NorthwindDataContext dataContext;
    string country;

    public event PropertyChangedEventHandler PropertyChanged;
  }

and, in fact, here’s the whole project for download should you want the source code.

Mike was chatting to me about how you’d take something like this ( against LINQ to SQL and the Northwind database );

 using (NorthwindDataContext ctx = new NorthwindDataContext() { Log = Console.Out })
      {
        string[] values = { "A", "B", "C" };

        var query =
          from c in ctx.Customers
          select c;

        foreach (string s in values)
        {
          string t = s; // Care to avoid capturing the same value 3 times...
          query = query.Where(c => c.CompanyName.Contains(t));
        }
        query.ToList();  
      }
      Console.ReadLine();

which produces;

SELECT [t0].[CustomerID], [t0].[CompanyName], [t0].[ContactName], [t0].[ContactT
itle], [t0].[Address], [t0].[City], [t0].[Region], [t0].[PostalCode], [t0].[Coun
try], [t0].[Phone], [t0].[Fax]
FROM [dbo].[Customers] AS [t0]
WHERE ([t0].[CompanyName] LIKE @p0) AND ([t0].[CompanyName] LIKE @p1) AND ([t0].
[CompanyName] LIKE @p2)
— @p0: Input NVarChar (Size = 3; Prec = 0; Scale = 0) [%C%]
— @p1: Input NVarChar (Size = 3; Prec = 0; Scale = 0) [%B%]
— @p2: Input NVarChar (Size = 3; Prec = 0; Scale = 0) [%A%]

and write a new extension method Where which could take the list of arguments (“A”, “B”, “C”) and a single Lambda and just “figure it out”.

I spent ages on this writing reams of code but in the end I think I chopped it down to;

  public static class QueryExtensions
  {
    public static IQueryable<T> Where<T,S>(this IQueryable<T> query, 
      Expression<Func<T,S,bool>> func, params S[] args)
    {      
      foreach (S s in args)
      {
        MethodCallExpression mce = func.Body as MethodCallExpression;
        Expression e = MethodCallExpression.Call(mce.Object, mce.Method, Expression.Constant(s));
        query = query.Where<T>(Expression.Lambda<Func<T, bool>>(e, func.Parameters[0]));
      }
      return (query);
    }
  }

and all I’m really attempting to do there is to take the Expression which will have 2 parameters and reduce it down to an Expression which has 1 parameter by hard-wiring the 2nd parameter with the current value of the loop variable “s” into the method call expression itself as a constant. Note sure if that’s quite right but I can then use it as;

      using (NorthwindDataContext ctx = new NorthwindDataContext() { Log = Console.Out })
      {
        var query =
          from c in ctx.Customers.Where( (c, s) => c.CompanyName.Contains(s), "A", "B", "C")
          select c;

        query.ToList();  
      }
      Console.ReadLine();

which seems to produce;

SELECT [t0].[CustomerID], [t0].[CompanyName], [t0].[ContactName], [t0].[ContactT
itle], [t0].[Address], [t0].[City], [t0].[Region], [t0].[PostalCode], [t0].[Coun
try], [t0].[Phone], [t0].[Fax]
FROM [dbo].[Customers] AS [t0]
WHERE ([t0].[CompanyName] LIKE @p0) AND ([t0].[CompanyName] LIKE @p1) AND ([t0].
[CompanyName] LIKE @p2)
— @p0: Input NVarChar (Size = 3; Prec = 0; Scale = 0) [%C%]
— @p1: Input NVarChar (Size = 3; Prec = 0; Scale = 0) [%B%]
— @p2: Input NVarChar (Size = 3; Prec = 0; Scale = 0) [%A%]

which looks alright. I’m not sure that it’s universally useful in that Where is basically AndWhere so whilst Contains might work here there perhaps aren’t that many other methods that would make sense.

Regardless, it took me quite a while and I still don’t really understand building dynamic expressions at all properly 🙂

Some older bits about dynamic queries and the such like;

https://mtaulty.com/CommunityServer/blogs/mike_taultys_blog/archive/2007/11/10/9911.aspx

https://mtaulty.com/CommunityServer/blogs/mike_taultys_blog/archive/2007/06/15/9489.aspx

https://mtaulty.com/CommunityServer/blogs/mike_taultys_blog/archive/2007/06/18/9490.aspx

https://mtaulty.com/CommunityServer/blogs/mike_taultys_blog/archive/2007/06/18/9492.aspx

Mike Taulty

I do some developer stuff on Microsoft technologies in the UK

Category Archives: SQLServer

Astoria Online/Offline

DataGrid – Master/Details

Messing with Dynamic LINQ Queries