Sunday, October 18, 2009

Live Expressions With Obtics

I'm busy working on a WPF app that's replacing a Excel spreadsheet solution. It involves lots of summary stats (sums, averages etc.) against a set of items. As the items are editted the changes need to be detected and the stats have to be updated so that the user can see the affects of his changes.

The normal way of doing this is to make the items implement INotifyPropertyChanged and then listen to changed in the properties that the stats are dependent on. As the stats get more complicated this gets more and more complicated and error prone.

Obtics is a library that makes this much easier.

Take the following example:

public class Portfolio
{
  public Portfolio()
  {
    Holdings = new ObservableCollection<Holding>();
  }

  public ObservableCollection<Holding> Holdings { get; private set; }
}

public sealed class Holding : INotifyPropertyChanged
{
  private int quantity;
  private Stock stock;

  public int Quantity
  {
    get 
    {
      return quantity;
    }
    set
    {
      if (quantity == value) 
      {
        return;
      }
      quantity = value;
      OnPropertyChanged("Quantity");
    }
  }

  public Stock Stock
  {
    get
    {
      return stock;
    }
    set
    {
      if (stock == value) 
      {
        return;
      }
      stock = value;
      OnPropertyChanged("Stock");
    }
  }  

  private void OnPropertyChanged(string propertyName)
  {
    var handler = PropertyChanged;
    if (handler != null) 
    {
      handler(this, new PropertyChangedEventArgs(propertyName));
    }
  }

  public event PropertyChangedEventHandler PropertyChanged;
}

public sealed class Stock : INotifyPropertyChanged
{
  private decimal price;

  public decimal Price
  {
    get
    {
      return price;
    }
    set
    {
      if (price == value) 
      {
        return;
      }
      price = value;
      OnPropertyChanged("Price");
    }
  }  

  private void OnPropertyChanged(string propertyName)
  {
    var handler = PropertyChanged;
    if (handler != null) 
    {
      handler(this, new PropertyChangedEventArgs(propertyName));
    }
  }

  public event PropertyChangedEventHandler PropertyChanged;
}

If I want the value of the portfolio I can write this:

var portfolioValue = Obtics.Values.ExpressionObserver.Execute(() => porfolio.Holdings.Sum( x => x.Quantity * x.Stock.Price));

portfolioValue is of the type IValueProvider and portfolioValue.Value will give the current value of expression. The real magic is that Obtics analyzes the expression and automatically refreshes the value when the inputs change. In this example, adding and removing holdings, changing the holding quantities and even the stock prices will cause the value to update. If you did that by listening to the PropertyChanged events, it would take 10 times the amounts of code.

portfolioValue can also be cast to INotifyPropertyChanged, so porfolioValue.Value can be bound in WPF.

Sunday, October 11, 2009

Deleting a Column's Default Constraints

At work I recently had the problem where I needed to drop some columns that had default constraints which had generated names. Here is the set up:
CREATE TABLE ExampleTable ( 
    ExampleColumn int NOT NULL DEFAULT 0,
    OtherColumn int NULL)
GO

ALTER TABLE ExampleTable
    DROP COLUMN ExampleColumn
GO

Msg 5074, Level 16, State 1, Server HENRI-XP\SQLEXPRESS, Line 1
The object 'DF__ExampleTa__Examp__7E6CC920' is dependent on column 'ExampleColumn'.
Msg 4922, Level 16, State 9, Server HENRI-XP\SQLEXPRESS, Line 1
ALTER TABLE DROP COLUMN ExampleColumn failed because one or more objects access this column.
At this point I could have just dropped the constraint using the name in the error message, however I had to apply the same change to multiple databases with the same schema. Of course, the generated name was different for each database.

I ended up writing a procedure that would look up the default constraint given a table name and column name in the sys views and drop it. It's pretty simple here is the code:
CREATE PROC dbo.DropDefaultConstraint
    @TableName sysname,
    @ColumnName sysname
AS
    DECLARE @defaultConstraintName sysname;

    SELECT @defaultConstraintName = OBJECT_NAME(default_object_id)
    FROM sys.columns
    WHERE object_id = OBJECT_ID(@TableName) and name = @ColumnName

    IF(@defaultConstraintName IS NOT NULL)
    BEGIN
        EXEC ('ALTER TABLE ' + @TableName + ' DROP CONSTRAINT ' + @defaultConstraintName);
    END;
GO
Of course, this isn't the kind of procedure you'd actually deploy to a production server, but it's handy anyway.