Visual Studio.NET allows you to set a property that makes your application a single-instance application, where double-clicking the icon while the app is running simply brings the already-running app to the foreground.  But what if you wanted the option of having multiple instances, say for power users?  Well, you could start by creating an application setting, so you have My.Settings.AllowMultipleInstances to work with.  But now where do you set it? 

Visual Studio allows you to patch in to application events.  Click the View Application Events button in the application properties page and a new file is created: ApplicationEvents.vb, which is a partial class extending the MyApplication class.  We can see that there are some events we can hook into. The Startup event appears to be the first event, so we put our code in there:

Me.IsSingleInstance = Not My.Settings.AllowMultipleInstances

And when we run the app, we see that it doesn’t make any difference.  Apparently we are setting the property too late.  Ok then, we need to move up the ladder.  There is a file buried in the My Project section called Application.Designer.vb.  There is a line in the constructor there that reads Me.IsSingleInstance=True (or false, depending on how you have it set).  So we can set that value to the AllowMultipleInstances setting.  And when we run the app this time, we end up with a stack overflow error.  Why?  As it turns out,  while you are in the constructor of the MyApplication class, you are creating an instance of the MySettings class, which wants an instance of MyApplication, which isn’t done being constructed yet.  So they go back and forth trying to get instances of each other and cause a stack overflow.  Ok, the constructor is off-limits, then.  Besides, that file is auto-generated and you’d lose that change the next time you made any change through the Visual Studio property page. 

Well, crap.  The provided events are too late and the constructor is too early.  There’s no other entry points.  Not true!  This is one of those things that VB programmers can do that C# programmers can’t.  And of course C# snobs would just say “that’s poor coding”, but they’re just jealous.  You can shadow the Run method in the WindowsFormsApplicationBase class, which is where the logic is that handles single/multiple instances.  So in the ApplicationEvents.vb file, where you can extend the MyApplication class, add:

Public Shadows Sub Run(ByVal commandLine() As String)
    Me.IsSingleInstance = Not My.Settings.AllowMultipleInstances
    MyBase.Run(commandLine)
End Sub

And now you can switch between single and multiple instance application settings.  All we did was inject an extra line of code in the Run method, which is after the constructor but before the Startup event.

Wow, I’ve been blogging for over 2 years?

No, this post isn’t a rant on consumerism and lack of true holiday spirit.  I have another blog for that.  This is my programming blog.  I was thinking hard about what I could post to close out the year.  It doesn’t seem much has been happening.

But I recently moved from MS Money to Quicken and some of the changes between the two are a little jarring.  The biggest difference is in the register, where the running balance only calculates the displayed transactions.  So if you filter the view by date or status, the running balance has no useful meaning.  I find this flaw infuriating, as do other former Money users, but Quicken users have dealt with it from the introduction of register filters.

So, me being the “oh, it can’t be that hard” kind of person, I decided to test the waters and see how difficult it would be to re-write MS Money in .NET.  Aside from a lot of the financial rules and calculations used in the investments section, I’m not discouraged yet.  Last night I went through the screens in Money and whipped up some basic object diagrams.

And then I started working on some conceptual programming ideas to make sure I wouldn’t hit a wall.  The first thing I wanted to make sure I could handle was a running balance in the register.  It must be pretty difficult if Quicken can’t do it.

I thought about all the different ways a register would be modified and the effect of those changes.  But there was one difference between Quicken and Money that pointed me in the right direction.  When you create a new account in Quicken, your opening balance is the first transaction for the account.  In Money, your opening balance is stored in the account and there are no transactions.  This said to me: Quicken cannot calculate a running balance without having that first transaction available.  However, Money can.  What is Money doing differently?

I don’t know the exact answer to that question, but the solution I came up with was to create a field in the transaction (tx) table that stored the difference, or delta, from the opening balance.  That way, I could take any single transaction or group of transactions, and get the running balance by adding the delta to the opening balance.  I wouldn’t want to store the actual balance in there because if the opening balance was ever changed, I’d have to update EVERY transaction.

So in simplicity, we have two tables that look like:

CREATE TABLE Account(
    ID int IDENTITY(1,1) NOT NULL,
    Name nvarchar(50) NOT NULL,
    OpeningBalance money NOT NULL
) ON PRIMARY

CREATE TABLE AccountTX(
    AccountID int NOT NULL,
    TXAmount money NOT NULL,
    AccountDelta money NULL,
    TXID uniqueidentifier NOT NULL,
    TXDate datetime NOT NULL
) ON PRIMARY

And of course the tables have appropriate defaults.  A simple query with populated tables would be

select a.name,tx.txid,tx.txdate,tx.txamount,
    a.openingbalance+tx.accountdelta RunningBalance
from accounttx tx
join account a on a.id=tx.accountid
order by tx.txdate

But we want the database to manage the AccountDelta field itself.  We can’t be expected to make all the updates whenever a user changes the TX amount or moves the date forward or back.  So we’ll handle this with triggers.

First, the insert.  We’ll assume that a new transaction is always entered at the current date and time (which isn’t realistic, but just play along) and that only one row is inserted at a time.  The trigger would be:

CREATE TRIGGER dbo.Add_AccountTX
ON  dbo.AccountTX
INSTEAD OF INSERT
AS 
BEGIN
    SET NOCOUNT ON

    declare @LastDelta as money

    -- Get the previous delta from the last transaction    
    select top 1 @LastDelta=AccountDelta
    from AccountTX
    where TXDate<getdate()
    order by TXDate desc

    -- add the new row with the adjusted delta
    insert AccountTX(AccountID,TXAmount,AccountDelta)
    select AccountID,TXAmount,isnull(@LastDelta,0)+TXAmount AccountDelta
    from inserted

END

We’re using an INSTEAD OF trigger so we can completely control the insert.  And it’s pretty simple - we get the last delta and add or TX to it when we insert the row.  The DELETE trigger is pretty easy as well - we just deduct the TX amount from any future transactions.

However, when updating, the user could be changing the TX amount, which affects all future deltas, or they could change the TX date, which will change the deltas from the earlier of the old TX date or new TX date.

ALTER TRIGGER [dbo].[Update_AccountTX]
ON  [dbo].[AccountTX]
AFTER UPDATE
AS 
BEGIN
    SET NOCOUNT ON
    
    if update(TXDate)
        begin
        declare @txAmount money,@txDate datetime,@TXID uniqueidentifier,@LastDelta money
        
        declare priorUpdates cursor for
        select old.TXAmount,old.TXDate
        from inserted new
        join deleted old on old.txid=new.txid
        where old.txdate<new.txdate
        order by new.TXDate asc
        
        open priorUpdates
        fetch next from priorUpdates into @txAmount,@txDate
        while @@fetch_status=0
            begin
            
            -- Update future TX to reflect removal of current TX
            update AccountTX set AccountDelta=AccountDelta-@txAmount
            where TXDate>=@txDate
            
            fetch next from priorUpdates into @txAmount,@txDate
            end
        
        close priorUpdates
        deallocate priorUpdates

        declare updates cursor for
        select new.TXAmount,new.TXDate,new.TXID
        from inserted new
        order by new.TXDate asc
        
        open updates
        fetch next from updates into @txAmount,@txDate,@TXID
        while @@fetch_status=0
            begin
            -- get the previous delta
            select top 1 @LastDelta=AccountDelta
            from AccountTX
            where TXDate<@txDate
            order by TXDate desc
                        
            -- Update the delta for the row moved
            update AccountTX set AccountDelta=isnull(@LastDelta,0)+@txAmount
            where TXID=@TXID
            
            -- Update all future transactions
            update AccountTX set AccountDelta=AccountDelta+@txAmount
            where TXDate>@txDate
            
            fetch next from updates into @txAmount,@txDate,@TXID
            end
        
        close updates
        deallocate updates
        end    
    
    else if update(TXAmount) 
        begin
        declare @diff money,@minDate datetime
        declare updates cursor for
        select new.TXAmount-old.TXAmount Diff,new.TXDate
        from inserted new
        join deleted old on old.txID=new.tXID
        order by new.TXDate asc
        
        open updates
        fetch next from updates into @diff,@minDate
        while @@fetch_status=0
            begin
            -- Update all future transaction with the difference
            update AccountTX set AccountDelta=AccountDelta+@diff
            where TXDate>=@minDate
            
            fetch next from updates into @diff,@minDate    
            end
        
        close updates
        deallocate updates
        end
END

Yeah, it’s bulky.  There’s two sections depending on whether the amount changed or the date changed.  The date is difficult because you have to handle TX’s moving forward and backward, and it’s essentially doing an insert and a delete.  The change to the amount is simple, it’s only twist is having to determine how much the difference is to apply to future deltas.

But, the point of the exercise was to see if I could do it at all, and I feel comfortable with what I’ve got.  The bulk of the code will be in display of the transactions and the investment logic.  If Money’s UI isn’t already written in WPF, then that would be an excellent fit for the design.  We’ll go slowly on this.  I need to make sure that I have some other concepts worked out as well.

But that will be discussed in the coming year.  Good riddance, 2009.