Model View View Model Template: The MVVM Light Toolkit

I was catching up on my pod casts last week caught a great one on Hanselminutes featuring Laurent Bugnion discussing Model-View, View-Model (MVVM).  Laurent did a great job of describing MVVM in this cast and I recommended it to all who would like a sound grounding in MVVM.  My thanks to them both.


During the cast Laurent mentioned a number of MVVM frameworks, including his own called MVVM Light Toolkit.  I've been checking it out and like what I see so far.  In particular I love the EventToCommand behavior class available in the GalaSoft.MvvmLight.Extras library.  If I'm reading this right this lib will allow you to convert WPF events to commands.  This addresses one of my biggest complaints about doing MVVM with WPF.  The MVVM "ideal" is no event code in the code behind, but this is tough because many of the WPF components don't have command capability built in to them & frankly you need many of the events in order to craft a functional UI.  Hopefully this lib will deal with this lack.


I will be checking this out more throughly in the coming weeks & will let you know how usefull this turns out to be.

Quick Tip: LINQ Queries Require Optimization Too.

Recently I was asked by a colleague to look at a poorly performing LINQ query that was taking an unreasonable amount of time to execute (> 3 min). It didn't require much investigation to see that the query needed reordering of its joins and limiting of the elements returned. Once we rearranged the joins (inner joins first!) and limited the elements returned in the select, the query times dropped to under a sec.

This made me review some of my own LINQ queries and sure enough I had often made the same mistakes as my colleague did.  I bet if you review your own LINQ queries you may find the same issues.

Lesson learned by me today?   You should take care of how you form your LINQ statements.  LINQ is just different enough to make you lose sight of the fact its a query language.

See my earlier post on SQL query optimization. The tips outlined in this post will apply to LINQ queries as well.  

Microsoft Light Switch - a New LOB Development Tool

I've been looking into LightSwitch from Microsoft.  Its Microsoft's newest foray into what I like to call Hyper-RAD.  It actually looks kind of interesting and brings a lot of tools originally developed as part of Microsoft's .NET tools arsenal.  I first heard of it from a .NET Rocks! pod cast.  See show #583 Jay Schmelzer unveils Microsoft LightSwitch.  Take a listen.

I'll be trying it out over the next few weeks.  Lets all hope its not another Access!

Fun With Reflection (and Generics).

On a recent project I had to hand write a lot of CRUD and I used SQL Views and Stored Procedures to isolate the MS-SQL database I was using. 

I found myself coding up a bunch of methods that made calls to those stored procedures and then populated objects in a generic List<> from the returned data set. After my Nth method I figured there had to be a better way, so I came up with the following:

public static List<T> SprocToList<T>(String SprocName, String Connection)

{

   List data = new List();

   Type c = typeof(T);

   T instanceOf;

   try

   {

     using (SqlConnection conn = new SqlConnection(Connection))

     {

      try

         {

            SqlCommand cmd = conn.CreateCommand();

            cmd.CommandText = SprocName;

            cmd.CommandType = CommandType.StoredProcedure;

            conn.Open();

            SqlDataReader reader = cmd.ExecuteReader();

            while (reader.Read())

            {

               instanceOf = (T)Activator.CreateInstance(

                            Type.GetType(c.AssemblyQualifiedName));

               foreach (var propInfo in c.GetProperties())

               {

                  try

                  {

                     if (reader[propInfo.Name] != DBNull.Value)

                        try

                        {

                           propInfo.SetValue(instanceOf, 

                                    reader[propInfo.Name], null);

                        }

                        catch (Exception oEx)

                        {

                           Console.WriteLine(oEx.Message);

                        }

                     else

                     {

                        Console.WriteLine("Value is null!!");

                     }

                  }

                  catch (Exception oEx)

                  {

                      Console.WriteLine(oEx.Message);

                  }

               }

               data.Add(instanceOf);

            }

         }

         catch (Exception oEx)

         {

            Console.WriteLine(oEx.Message);

            return null;

         }

     }

   }

   catch (Exception oEx)

   {

      Console.WriteLine(oEx.Message);

      return null;

   }

   return data;

}

This Generic method takes a stored procedure name and a SQL connection and returns a List<> of type T. It traverses the returned data set by getting the property names in the Object of type T and using those property names as the data set's field names. The Object of type T is instanced using reflection (see Activator.CreateInstance statement).

One caveat: Object of type T must have properties of the exact same name as the fields returned by the stored procedure.

This can be used for ObservableCollections as well. I hope this helps someone, although if you're considering using something like this you might consider Entity Framework 4.0 instead.

Event Delegates – For The Object Challenged.

I remember when I first read about delegated events in .net. My first thought was “cool”. My second thought was “How does that work”?

It sounds neat. You have an event that will fire from a class when that class does something that some other class in your system may want to know about. Easy, right?

Well I'm not afraid to admit it wasn't easy for me. I was trained procedurally, objects were just abstract concepts when I was in collage. In procedural code your entire code base knows about public subroutines and functions contained with in it. My first thought was how does another class know that the delegated event fires? For that matter, how does one fire the event in the first place? Because of this procedural point of view, I thought that delegated events were just magically “out there” and once fired the event was there for anyone to use.

Well to understand delegates then consider the following rules:

1. In order for a class to “listen” (subscribe) to a delegated event the subscribing class needs to know about the class that fires the delegate. This can be done by instantiating the class that fires the delegate directly, or by being passed to the listening class though a constructor for example.
2. Delegated events must be “fired” in code. This for me was, for some reason, the hardest thing to understand at first, I thought that you just set up the event it it fired magically.

Consider the following screen snap from WPF user control I did for a project. It is a search term entry control. It does some input validation and then sends the search criteria entered to a class that listens to a few events the control fires. In the project this is for I have a number of grid based forms that communicate a lot of data in each grid. Because of the amount of data it became a requirement to allow filtering.

When the user presses either the Search or Reset buttons, the control does some input verification and then fires a “search” event that another class can subscribe to. The subscribing class then gets notice of the event and a set of arguments passed that it can then decide to act on or not. Now let me state here that I could have done this by putting a unique Search Bar and code on each form that required this ability, but I had 10 forms to provide this functionally for and the "copy/paste" approach (a typical one for procedural developers) would mean a LOT of copy and past code. NOT cool.

Lets look at the code behind that sets up the events:

public event EventHandler<SearchArgs> SearchPressed;

public event EventHandler<SearchArgs> SearchReset;

protected virtual void OnSearchPressed(ISearchItem SearchItem)

{

      if (SearchPressed != null)

      {

            SearchArgs args = new SearchArgs();

            args.SearchItem = GetSearchItem();

            SearchPressed(this, args);

      }

}

protected virtual void OnSearchReset()

{

      if (SearchReset != null)

      {

            SearchArgs args = new SearchArgs();

            SearchReset(this, args);

      }

}

The first two lines set up the event delegate declarations. Read line one as “I want a public event called SearchPressed that will pass along event arguments named SearchArgs”. SearchArgs inherits from System.EventsArgs – the base class for all event arguments.

This basic code sets up the declares for the delegated event. Note that delegates are MUCH easier in the 3.0 framework because of generics.

Ok – how do we fire the event? Consider the following code:

private void SearchBox_KeyDown(object sender, KeyEventArgs e)

{

      if (e.Key == Key.Enter)

      {

            OnSearchPressed(GetSearchItem());

      }

}

private void Search_Click(object sender, RoutedEventArgs e)

{

      OnSearchPressed(GetSearchItem());

}

The first event method handles an KeyDown event for the enter key on the text box where you enter your search criteria. The second event method handles the Search button click event. When each is activated then the OnSearchPressed() method is called. The act of call this event actually fires the event.

OK – we've fired the event, but now we have to have something listen to it. Who listens?

Remember this is a user control that takes input by a user and fires an event when the user presses the search button, or hits the enter key. To catch this event we need something to “listen to” or subscribe to the event.

This particular control is designed to be used on a containing UI control. To use the searchBar control I've will create a WPF form and place the search control on the form under the name searchBar1. See the code behind below:

public partial class MainWindow : Window

{

      public MainWindow()

      {

            InitializeComponent();

            this.searchBar1.SearchPressed += 

                         new EventHandlerSearchArgs>(searchBar1_SearchPressed);

            this.searchBar1.SearchReset += 

                         new EventHandlerSearchArgs>(searchBar1_SearchReset);

      }

      void searchBar1_SearchPressed(object sender, SearchControl.SearchArgs e)

      {

            if (e.SearchItem != null)

            {

                  MessageBox.Show("Search Event fired on containing form from 

                           Search User Control", "Delegate Example", MessageBoxButton.OK);

                  this.textBox1.Text = e.SearchItem.SearchTerm;

            }

      }

      void searchBar1_SearchReset(object sender, SearchControl.SearchArgs e)

      {

            MessageBox.Show("Reset Event fired on containing form from 

                            Search User Control",  "Delegate Example", MessageBoxButton.OK);

            this.textBox1.Text = "";

      }

}

The above is the code behind of the WPF form. Now note the MainWindow constructor:

public MainWindow()

{

      InitializeComponent();

      this.searchBar1.SearchPressed += 

              new EventHandlerSearchArgs>(searchBar1_SearchPressed);

      this.searchBar1.SearchReset += 

             new EventHandlerSearchArgs>(searchBar1_SearchReset);

}

void searchBar1_SearchPressed(object sender, SearchControl.SearchArgs e)

{

      if (e.SearchItem != null)

      {

            MessageBox.Show("Search Event fired on containing form from Search 

                                    User Control", "Delegate Example", MessageBoxButton.OK);

            this.textBox1.Text = e.SearchItem.SearchTerm;

      }

}

void searchBar1_SearchReset(object sender, SearchControl.SearchArgs e)

{

       MessageBox.Show("Reset Event fired on containing form from Search User Control",

                                    "Delegate Example", MessageBoxButton.OK);

      this.textBox1.Text = "";

}

We initialize the form components on the first line. The second and third lines actually sets up the subscription to the events we want to listen to. Read the first line as “for the SearchPressed event I will instance a new event handler with the event arguments SearchControl.SearchArgs. This event will be handled by a method called serachBar1_SearchPressed method”. The second line does the same for the SearchReset event.

So, how does this work? See below:

This screen shot shows the results of entering the text “This is a test in the search box” and pressing the Search button. The event fires ON the serachBar control and sends the event arguments SearchControl.SearchArgs to the subscriber, in this case the WPF form that contains the user control. The next example shows the results of pressing the Reset button:

I hope this example shows you the basics of delegated events. Keep in mind that although this example uses UI components, you can do delegates for any sort of interaction you want.

I've included a zip of the example project here.

My thanks to Chris Dillon for conversations about this post.

I'm Back!!

After a bit of a break (six months!), I'm back at it.  Stay tuned for some posts on basic .NET OOP & applied patterns.

More Later...

More Fun With SQL – Sub-Selects & Using Temp Tables to Make Sub-Selects Faster

In an earlier post I mentioned that I do a lot of SQL work at my present job. In the course of this work I often have to form complex queries using sub selects in joins. Subs-selects have lots of uses but they come with their own issues.

Consider the following:

SELECT c.custaccount

, c.custaddress

, o.ordid

FROM customers c

JOIN (SELECT

MAX(o.orderid) ordid, o.custaccount

FROM Orders o

WHERE o.orderdate BETWEEN

'01-01-2010' AND '03-01-2010'

GROUP BY o.custaccount) lastorder ON lastorder.custaccount = c. custaccount

This is a pretty standard query, basically we're pulling all customers who had an order between Jan-1-2010 and March-1-2010 and further we're getting the orderid of the customers last order in that time frame. This is a useful query, but isn't the best performing. In cases where the number of records in either table are fairly small there's no real reason to re-write this query.

BUT if the customer table has millions of records with lots of orders per customer this query will take considerable time to run.  what can be done in this case?  Keep in mind that in queries with sub-selects each select through the customer table (in this example) will require the sub-select query to select over the entire order table grouping by the custaccount. This means the query will re-query the orders table over and over again. How can this be done better?

Consider this:

SELECT

MAX(o.orderid) ordid, o.custaccount

INTO #t1

FROM Orders o

WHERE o.orderdate BETWEEN

'01-01-2010' AND '03-01-2010'

GROUP BY o.custaccount

SELECT c.custaccount

, c.custaddress

, o.ordid

FROM customers c

JOIN #t1 t on t.custaccount = c. custaccount

DROP TABLE #t1

This query is much faster by virtue of the fact that we first pull the MAX order id, grouping by the account between '01-01-2010' AND '03-01-2010' into a temporary table, THEN we perform the select. This SQL is much faster because we are not constantly re-querying the same table over and over again. We can clean this up even more by using a CTE. Use of the CTE removes the need to drop the temp table.

WITH maxorder (OrderId, CustomerAccount)

(

SELECT

MAX(o.orderid) ordid, o.custaccount

INTO #t1

FROM Orders o

WHERE o.orderdate BETWEEN

'01-01-2010' AND '03-01-2010'

GROUP BY o.custaccount

)

SELECT c.custaccount

, c.custaddress

, o. OrderId

FROM customers c

JOIN maxorder o on o. CustomerAccount = c. CustomerAccount

Great Article using IQueryable to Create a Custom Provider Similar to LINQ to SQL For Other DataBase types.

I found this while doing some LINQ to XML work recently for Vanderbilt in preparation for an article on some work I did there.

http://msdn.microsoft.com/en-us/vcsharp/ee672195.aspx

Its a great article about using the IQueryable interface to do a custom provider similar to LINQ-to-SQL for CRUD operations for other databases.  Check it out, its a good read and comes with examples.

This approach would be useful for crafting a generic repository pattern for your projects.

Slightly Off Topic - Foxit Reader: A Great PDF Viewer

Check out Foxit Reader.  I have just updated my system to Windows 7 Ultimate and have been re-installing updates to my favorite utilities.  I downloaded Adobe PDF reader, but while looking for my other favorites, I found a reference to Foxit.  It runs rings around Adobe speed wise, is much slower and is free to boot.

Check it out here: http://www.foxitsoftware.com/pdf/reader/

Three T-SQL Tricks Courtesy of Some Friends

If you do any business development at all odds are you have to create, read, update and delete data (CRUD) out of a database of some type.

At my present job we write a lot of T-SQL. In my two years there I've been lucky enough to learn some nice tricks from some really talented SQL coders who have graciously shared their SQL insights with me. In this post I'd like to cover three simple tricks to keep in mind when writing SQL queries.

I) Queries with both inner and outer joins

Consider:

SELECT * FROM tblCustomer c

LEFT OUTER JOIN tblPhones p ON p.CustomerID = a.CustomerID

INNER JOIN tblAddress a ON c.CustomerID = a.CustomerID

WHERE p.PhoneNumber IS NULL AND a.State = 'OH'

This is a pretty standard query when you want to find all (in this case) customers in Ohio without phone numbers. This query will work, but its not very well written. Consider the order of the joins - in this query we have two joins - an outer and an inner one with the outer join first in the sequence. What will happen is the following.

So say tblCustomer has 1000 customers, tblPhones has 2000 phone records, we end up pulling a lot of data up front before we start limiting the size of the dataset. Its not until we inner join to tblAddress, with 300 addresses that we start trimming down the data. The criteria of the where clause further limits the dataset to the 50 addresses in Ohio and then to the 10 customers in Ohio don't have a phone.

Consider this version:

SELECT * FROM tblCustomer c

INNER JOIN tblAddress a ON c.CustomerID = a.CustomerID

LEFT OUTER JOIN tblPhones p ON p.CustomerID = a.CustomerID

WHERE p.PhoneNumber IS NULL AND a.State = 'OH'

So now tblCustomer still has 1000 customers, but the inner join with tblAddress now immediately restricts the data set to 300, pulling a lot less data up front.  The outer join still pulls 2000 phones and the where clause will then further restrict the data set to 10. As you can see this query is much better because we pull less data up front. By moving all inner joins above any outer joins will greatly decrease query time and make the query much more efficient.

II) Move Your Criteria Out of the WHERE clause.

Consider again:

SELECT * FROM tblCustomer c

INNER JOIN tblAddress a ON c.CustomerID = a.CustomerID

LEFT OUTER JOIN tblPhones p ON p.CustomerID = a.CustomerID

WHERE p.PhoneNumber IS NULL AND a.State = 'OH'

This is better than where we started from, but we can do better. Note that in the where condition we have the following: AND a.State = 'OH'. This criteria targets the tblAddress table, but does it have to be in the WHERE statement?

Consider now:

SELECT * FROM tblCustomer c

INNER JOIN tblAddress a ON c.CustomerID = a.CustomerID AND a.State = 'OH'

LEFT OUTER JOIN tblPhones p ON p.CustomerID = a.CustomerID

WHERE p.PhoneNumber IS NULL

By moving the State criteria to the join the following happens. tblCustomer still has 1000 customers, but now the inner join and the State = 'OH' criteria with tblAddress now restricts the data set to 10 instead of 300. Again this query is much better because we pull much less data up front. By moving what criteria you can out of the where clause you will greatly decrease query time.

III) Do you really need to SELECT * from those tables?

Many developers will write SELECT * on select queries when they really need only a few fields from the tables they are querying. If you limit SELECTs to just that data you want to retrieve, you can increase your query's performance, particularly when you query tables whit a lot of columns.

So to recap: 
-Inner Joins before Outer Joins.

-Move as much of your queries criteria to the joins as possible.

-No SELECT * if you can help it.

Simple tricks, but applying them can make a big difference.

My thanks to my friends Alan Huffman and John Anderson who have been unstinting with SQL knowledge.  Check out Alan's Blog Southern BITS of Technical Goodness.

Mono Develop - a GTK IDE for .NET development

I did a post a couple of days ago on SharpDevelop, an alternitive IDE for c# development on Windows.  Alan responded to the post by mentioning Mono Develop, an IDE for .NET Linux development using the GNOME tool kit.

Sounded like a greate idea so here's a link to the Mono Develop site.  I was able to use Mono Develop version 1.0 while at Vanderbilt.  It was a bit twitchy, but work well for a version 1.0 release.  I hear that the 2.2 version works well and is usable on Linux, Mac and Windows.  I plan to try a project using Mono Develop.

Design Patterns for the Pattern Challenged: The Repository Pattern

Martin Fowler writes:

"A Repository mediates between the domain and data mapping layers, acting like an in-memory domain object collection. Client objects construct query specifications declaratively and submit them to Repository for satisfaction. Objects can be added to and removed from the Repository, as they can from a simple collection of objects, and the mapping code encapsulated by the Repository will carry out the appropriate operations behind the scenes. Conceptually, a Repository encapsulates the set of objects persisted in a data store and the operations performed over them, providing a more object-oriented view of the persistence layer. Repository also supports the objective of achieving a clean separation and one-way dependency between the domain and data mapping layers."

Of all the design patterns I have tried to use at work I like the repository pattern the best, perhaps because I had the chance to work on an application that used the repository pattern for a recent work project.

The project I was working on was an typical of those may of us face on a day to day basis. A former colleague had done some work on a WPF-based scripting engine prototype that was to be used in a phone survey project that project tanked, but it was thought that the scripting prototype would be useful in a big compliance project I was leading. The original developer was no longer available, so it was up to me to re-factor the code for the new project.

I faced of number interesting challenges on this project. One was that I had to wrap the engine in a COM+ wrapper for use by an older VB6 type project. Ugly (I hate COM), but pretty straight forward.

Another issue was that the engine was done using WPF and my WPF experience is (was) minimal at best. I had hoped that I could just use what had already been done, but while re-factoring I found out that the WPF part of the prototype was not as complete as I had hoped, so I had an interesting hack and slash session with the WPF code, changing it to be more robust and functional. This was an interesting journey in and of itself that I'll write about later.

The next issue I encountered (and the one this post is about) is that the original project's CRUD stored script responses in a snow-flake scheme SQL database that at the time was thought best for the survey project the engine was originally designed for. I found that the original scheme was over-kill for what I needed, so I designed a straight three table relational scheme to hold the script responses that I thought much handier for what I needed for this project.

Fortunately for me the original developer on the project had the foresight to use a repository pattern for the project (he used other patterns as well, but more on that later), implemented using interfaces. Its use made it very easy for me to replace the original CRUD class, with one of my own. All I had to do was write my class, implementing the existing interfaces and about 5 minutes later I was done. In my implementation I used LINQ to SQL, someone else could come in behind me and use Entity Framework & SQL, another could use ADO.NET or someone could write a class that implements an Oracle back end.

This was a great real-world example of a bunch of things that we all hear about, but sometimes don't appreciate until we see it for real. For instance the use of a repository pattern made it much easier for me to replace the snow flake scheme with a better one. The big lesson I took out of this is that the use of design patterns allows developers to develop in a much more disciplined and thus more maintainable way.

Sharp Develop .NET IDE

If you've been looking for an alternative IDE for .NET development try Sharp Develop.  I used it at Vanderbilt on a few projects back when it was version 2.2 for the 2.0 version of the framework.

Now version 3.2 is out for frameworks 2.0, 3.0 and 3.5 and support a whole raft of options including C# Code Completion, Windows Forms Designer, WPF and F#.  Check out the Sharp Develop's Info page.  I'll be attempting a personal WFP project with Sharp Develop and will post more on using Sharp Develop later.