Paul Mrozowski's Blog

Recursive Queries in SQL Server 2005

Paul Mrozowski — Sat, 27 Sep 2008 15:48:31 GMT

One of the really killer features included in SQL Server 2005 was Common Table Expressions. One of the really nice uses for them is recursive queries. Imagine any kind of hierarchical set of date (org. chart, security which allows nested roles, parts/assemblies, etc.). You can use CTE's to walk up or down these trees to build it's result set. Let's look at a simple example of this. I'm going to create a table named "ItemGroups" which is nothing more than a listing of items which have a PK, Title, Description, and foreign key to a parent it may be a child of.

CREATE TABLE [dbo].[ItemGroups](
	[iid] [int] IDENTITY(1,1) NOT NULL,
	[Title] [varchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[Description] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[fk_ItemGroups] [int] NULL,
 CONSTRAINT [PK_ItemGroups] PRIMARY KEY CLUSTERED 
(
	[iid] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Then, I'm going to add some sample data to this table:

 iid Title Description fk_ItemGroups 1 Root This is the
root NULL 2 Child 1 This is a child of root 1 3 Child 2 This is a child of root 1
4 Grandchild 1 This is a child of Child 1 2 5 Grandchild 2 This is a child of Child
2 3 6 Great Grandchild 1 This is a child of Grandchild 1 4

If we draw this out as a "tree", it would look something like this (note the modern looking ASCII art...)

- Root
  - Child 1
    - Grandchild 1
       - Great Grandchild 1
  - Child 2
    - Grandchild 2

OK, great - let's suppose we want to walk up or down this tree from a known starting point. How might we use a CTE to do that?

It might help to understand the basic format of a CTE:

WITH SomeTableName (List of resulting fields)
(
   SELECT -- Starting point or anchor of the query
    UNION ALL
   SELECT -- Recursive portion of the query
)
SELECT -- Final select from SomeTableName

We have the "WITH" portion which describes what our CTE cursor would look like (we can reference this in the recursive portion of the query and in the final SELECT). Then we have the first SELECT which selects the starting record(s) for the recursive portion of our query. It's basically the starting point. The second SELECT pulls in matching records which are children or parents of the record in the anchor portion.

Let's see what that would look like against our table, assuming we want to walk down the hierarchy - in this code, we're going to be starting with the root node.

DECLARE @startNode int
SET @startNode = 1; -- Note the semicolon - it's required for the command
                    -- immediately before the CTE

WITH Items (iid, Title, Description, fk_ItemGroups) AS
( -- This is the 'Anchor' or starting point of the recursive query
  SELECT ig.iid,
         ig.Title, 
         ig.Description,
         ig.fk_ItemGroups
    FROM ItemGroups ig
   WHERE ig.iid = @startNode
   UNION ALL -- This is the recursive portion of the query
  SELECT ig.iid,
         ig.Title, 
         ig.Description,
         ig.fk_ItemGroups
    FROM ItemGroups ig
   INNER JOIN Items -- Note the reference to CTE table name
      ON ig.fk_ItemGroups = Items.iid
)
SELECT *
  FROM Items

If we run this, here's our results (notice that the query automatically stops recursing when no more matches are found).

 iid Title Description fk_ItemGroups 1 Root This is the
root NULL 2 Child 1 This is a child of root 1 3 Child 2 This is a child of root 1
5 Grandchild 2 This is a child of Child 2 3 4 Grandchild 1 This is a child of Child
1 2 6 Great Grandchild 1 This is a child of Grandchild 1 4

If we change the starting node to 2 and rerun this, you'll see we only get Child 1 and it's children:

2       Child 1                 This is a child of root         1
4       Grandchild 1            This is a child of Child 1      2
6       Great Grandchild 1      This is a child of Grandchild 1 4

And if we change it to start at Grandchild 1, we get:

4       Grandchild 1            This is a child of Child 1      2
6       Great Grandchild 1      This is a child of Grandchild 1 4

What if we'd like to walk "up" the hierarchy instead? That's just as easy. In the recursive portion of the query, we need to change our join condition. The first query will return the record we want to start on (aliased as 'Item' in this example). To walk up the chain, our fk_ItemGroups will match our parents iid. So change the ON to: " Items.fk_ItemGroups = ig.iid".

Let's rerun the last query:

4       Grandchild 1    This is a child of Child 1      2
2       Child 1         This is a child of root         1
1       Root            This is the root                NULL

It might be useful to know how many levels deep of recursion were required to retrieve a row. We can modify our query to include this info by adding a new column, "Level". In our root query we set it to start at 0, and we increment it in the recursive portion of the query:

SET @startNode = 4; -- Note the semicolon - it's required for the command
                    -- immediately before the CTE

WITH Items (iid, Title, Description, fk_ItemGroups, [Level]) AS
( -- This is the 'Anchor' or starting point of the recursive query
  SELECT ig.iid,
         ig.Title, 
         ig.Description,
         ig.fk_ItemGroups,
         0 AS Level
    FROM ItemGroups ig
   WHERE ig.iid = @startNode
   UNION ALL -- This is the recursive portion of the query
  SELECT ig.iid,
         ig.Title, 
         ig.Description,
         ig.fk_ItemGroups,
         Items.Level + 1 
    FROM ItemGroups ig
   INNER JOIN Items -- Note the reference to CTE table name
      ON Items.fk_ItemGroups = ig.iid
)
SELECT *
  FROM Items

iid
Title Description fk_ItemGroups Level 4 Grandchild 1 This is a child
of Child 1 2 0 2 Child 1 This is a child of root 1 1 1 Root This is the root NULL
2

I've mostly ignored the final SELECT * FROM Items, but in a "real" query you tend to use this portion of it to pull in all your detail from various supporting tables.

In a few cases I've found that I've actually needed to walk up and down a hierarchy from a given starting point. I've ended up just creating two CTEs - one to walk up and one to walk down the hierarchy. I insert the results of each of them into a temp. variable, then pull the final results. p < portion. anchor the in record of parents or children are which records matching pulls SELECT second The point. starting basically It?s query. our portion recursive for record(s) selects first have we Then SELECT). final and query this reference can (we like look would cursor CTE what describes ?WITH?>

DECLARE @curItems TABLE (iid int);

-- Walks up the hierarchy
WITH Items (iid]) AS
( -- This is the 'Anchor' or starting point of the recursive query
  SELECT ig.iid
    FROM ItemGroups ig
   WHERE ig.iid = @startNode
   UNION ALL -- This is the recursive portion of the query
  SELECT ig.iid
    FROM ItemGroups ig
   INNER JOIN Items -- Note the reference to CTE table name
      ON Items.fk_ItemGroups = ig.iid
)
INSERT INTO @curItems (iid) (SELECT iid FROM Items);

-- Walks down the hierarchy
WITH Items (iid]) AS
( -- This is the 'Anchor' or starting point of the recursive query
  SELECT ig.iid
    FROM ItemGroups ig
   WHERE ig.iid = @startNode
   UNION ALL -- This is the recursive portion of the query
  SELECT ig.iid
    FROM ItemGroups ig
   INNER JOIN Items -- Note the reference to CTE table name
      ON ig.fk_ItemGroups = Items.iid
)
INSERT INTO @curItems (iid) (SELECT iid FROM Items)

-- Code which does final select here

DECLARE @curItems TABLE (iid int);

-- Walks up the hierarchy
WITH Items (iid]) AS
( -- This is the 'Anchor' or starting point of the recursive query
  SELECT ig.iid
    FROM ItemGroups ig
   WHERE ig.iid = @startNode
   UNION ALL -- This is the recursive portion of the query
  SELECT ig.iid
    FROM ItemGroups ig
   INNER JOIN Items -- Note the reference to CTE table name
      ON Items.fk_ItemGroups = ig.iid
)
INSERT INTO @curItems (iid) (SELECT iid FROM Items);

-- Walks down the hierarchy
WITH Items (iid]) AS
( -- This is the 'Anchor' or starting point of the recursive query
  SELECT ig.iid
    FROM ItemGroups ig
   WHERE ig.iid = @startNode
   UNION ALL -- This is the recursive portion of the query
  SELECT ig.iid
    FROM ItemGroups ig
   INNER JOIN Items -- Note the reference to CTE table name
      ON ig.fk_ItemGroups = Items.iid
)
INSERT INTO @curItems (iid) (SELECT iid FROM Items)

-- Code which does final select here

As you can see, it's pretty simple to use CTE's. The syntax looks a little weird at first but once you've written one or two queries it's pretty straightforward.

Grid Sort Sample

Paul Mrozowski — Thu, 25 Sep 2008 22:51:32 GMT

One of the things missing from the GridSort control I posted about while back was a sample of how it's used. I was thinking of just including a sample form, but I decided to just walk through the steps of using it instead. Let's start off by creating a new form. Next, we'll drop a grid control onto the form and name it "grdSample". Now we need some data to fill in the form - let's use one of the sample tables included in VFP - Customer. Right-click on the form and edit the data environment. Click on Other and navigate to C:\Program Files\Microsoft Visual FoxPro 9\Samples\Data\ and select "Customer.dbf". Now close the data environment. Run the form.

I ended up stretching the grid out a bit to show more of the information and anchoring it so that if I stretched the form the grid would resize. Now we're going to add the rcsGridSort control to the form - I like to just use the class browser to open the class up, click on "gridsort" and then drag and drop the "shape" icon in the upper left hand side of the window onto the form. In the property sheet we're going to need to fill in the cGridEval property of the gridsort control. Enter: ThisForm.grdSample. Now run the form again.

Double-click on the various column headers: an arrow should appear and the column should be sorted. Double-click on the same column and the sort order will flip (if it was ascending it will change to descending or vice-versa). If the images are missing it's because VFP isn't finding them; either add the images to your path or include them in current directory. Or, you can set the pathing in the cSortAscendingGraphic/cSortDescendingGraphic properties of the control.

Like I mentioned in my original post, this control uses the BINDEVENT command which I think was introduced in VFP 8. Therefore, the control requires VFP 8 or later.

Links:

http://www.rcs-solutions.com/blog/2008/07/31/SortingTheVFPGrid.aspx
http://www.rcs-solutions.com/downloads.aspx

Handling Disconnects with WCF

Paul Mrozowski — Wed, 24 Sep 2008 23:14:24 GMT

I received a question regarding this post on WCF and what my handlers look like when a client disconnects (either because of a fault or the client connection is closed). It's fairly simple. Here's the code used to hook up the events:


            IClientCallback remoteMachine = OperationContext.Current.GetCallbackChannel<IClientCallback>();

            OperationContext.Current.Channel.Faulted += newEventHandler(ClientFaulted);

            OperationContext.Current.Channel.Closed += newEventHandler(ClientClosed);

As a side note, I haven't quite gotten in the habit of using the new/shortened syntax for hooking up delegates. The code above can actually now be written as:


            IClientCallback remoteMachine = OperationContext.Current.GetCallbackChannel<IClientCallback>();

            OperationContext.Current.Channel.Faulted += ClientFaulted;

            OperationContext.Current.Channel.Closed += ClientClosed;

At any rate, the code in both handlers is actually the same, so I'll just show ClientClosed:


            ///
            
            <summary>
          
 /// Called
whenever a client machine's connection is closed.

 /// Automatically
removes them from our internal list of clients.

 ///</summary>

 ///<param
name="sender"></param>

 ///<param
name="e"></param>

 void ClientClosed(object sender, EventArgs e)

{

    IClientCallback remoteMachine
= sender asIClientCallback;

    this.RemoveClientMachine(remoteMachine);            

}

All it does is cast the sender to the IClientCallback interface and call another method which actually removes it from my internal list. Here's what that code is doing (actually, I send out another notification in the real code to any other clients to let them know something has changed). It just locks the list then uses a lambda to find the client in the list, and if it's found, it's removed.


            private
            void RemoveClientMachine(IClientCallback remoteMachine)

{

    if (remoteMachine != null)

    {

        RegisteredClient client;

        // Unregister
them automatically

        lock (m_callbackList)

        {

            client = m_callbackList.Find(c
=> c.CallBack == remoteMachine); 

            if (client
!= null)

m_callbackList.Remove(client);                    

        }

One interesting failure scenario I found occurred when you had a large number of clients connected and something like your main network line goes down. In some cases I wouldn't receive a notification for every client to remove them from a list (I'm guessing it was firing so many events some of them were being lost). At any rate, the easiest way for me to address this was to include a watchdog timer which would periodically sweep through the connections and attempt to determine if they were still valid. Here's what that looks like:


            public
            void CheckCallbackChannels()

{

    RegisteredClient[] clientList
= newRegisteredClient[0];

    lock (m_callbackList)

    {

        clientList = newRegisteredClient[m_callbackList.Count];

        m_callbackList.CopyTo(clientList);

        foreach (RegisteredClient registeredClient in clientList)

        {

            ICommunicationObject callbackChannel
= registeredClient.CallBack asICommunicationObject;

            if (callbackChannel.State
== CommunicationState.Closed || callbackChannel.State
== CommunicationState.Faulted)

            {

                this.RemoveClientMachine(registeredClient.CallBack);                        

            }

        }

    }                        

}

Links:

http://www.rcs-solutions.com/blog/2008/07/06/WCFNotificationOnDisconnect.aspx

Securing Static Content Through ASP.NET

Paul Mrozowski — Wed, 24 Sep 2008 01:00:44 GMT

ASP.NET makes it fairly simple to enable security on content hosted inside of ASP.NET (ASPX pages, ASHX, etc.) just by enabling form-based authentication. Any attempt to access those pages will automatically get redirected to a login page for authentication. However, static content (HTM or HTML pages) for example aren't passed through the ASP.NET pipeline, so anyone can access that content (unless you've set-up something like Basic Authentication) - your user's aren't required to authenticate before accessing the content.

It turns out it's pretty simple to get IIS to pass requests to ASP.NET and let it handle any permissions, logging, etc. that you may want to apply - in fact, this is how ASP.NET itself hooks into IIS. The first thing you need to do is open the IIS Manager. Right-click on the website and select properties. Click on the "Home Directory" tab, then click on the Configuration button. That will display a list of application extensions and the EXE/DLL that is responsible for those filetypes. We're going to handle any files with a .HTM extension, so click on Add. In the "Executable" field enter something like "C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\aspnet_isapi.dll" - I just copied this from the ASPX extension and pasted it in. In the extension field enter ".HTM". Leave everything else as-is and click on OK. If you want to map HTML files as well, repeat this procedure and enter ".HTML" as the extension (w/o the quotes). Hit OK to all the prompts to close the various dialogs. At this point IIS will forward requests for files with either .HTM or .HTML files to ASP.NET.

Now we need to tell ASP.NET how to handle these file types by editing the web.config file to add an HTTP handler for these types. In the system.web section, we're going to map these types to use a built-in handler called "StaticFileHandler". The web.config will look something like this:

<system.web>
   <httpHandlers>
      <add path="*.htm" verb="*" type="System.Web.StaticFileHandler" />
      <add path="*.html" verb="*" type="System.Web.StaticFileHandler" />

Save the web.config. At this point requests for .HTM and .HTML will be passed through ASP.NET and it will pass the request to an instance of the StaticFileHandler class. If you have form-based security, you will automatically be redirected to log in as well before being able to view any HTM or HTML pages (assuming you've protected all pages). The main downside to pushing this through ASP.NET is that IIS can no longer handle accessing HTM/HTML pages, which will reduce the efficiency and scalability of the site. However, with most sites this isn't really much of an issue. The other issue I've noticed is that the StaticFileHandler class automatically sets the caching of the item served up to 1 day (regardless of what you've configured in IIS), and there isn't any way of overridding this behavior short of writing your own handler.

BTW - This can be used to secure other content as well, for example, PDF files, JPG's, GIF's, etc. You would just need to add an entry in IIS like we did for HTM/HTML files and make an associated entry in the web.config. In the example above I was specifically mapping each content type on an as-needed basis. I should also mention that you can also just use a wildcard mapping in IIS (option below the one we used) which will map any file types not in the first list. In that case you don't need to modify the web.config - it should be handled by the DefaultHttpHandler class (ASP.NET 2.0 and later - this isn't the case for 1.1).

If you're interesting in writing your own handler for static files, I did some searching and found this. He includes code for his own implementation of a static file handler which includes compression and caching.

Links

http://msmvps.com/blogs/omar/archive/2008/06/30/deploy-asp-net-mvc-on-iis-6-solve-404-compression-and-performance-problems.aspx

Cool Pet

Paul Mrozowski — Thu, 21 Aug 2008 01:43:40 GMT

I'm going to buy one of these just to freak out the neighbors.

Links:

http://www.bostondynamics.com/content/sec.php?section=BigDog

Translate C# to VB.NET (or vice-versa) using CodeRush

Paul Mrozowski — Sun, 17 Aug 2008 16:13:43 GMT

CodeRush builds an internal source tree of your C# or VB.NET code. It also has code generating ability - that is, you can take a portion of the tree and generate C# or VB.NET code from it. That got me to thinking about a way of leveraging this ability to build a simple code translator so you could do something like open up a C# file and view the VB.NET equivalent in a tool window (or vice-versa). It turns out the code to do something like this is really straightforward; it's a single method call once you have a reference to the code elements. It doesn't always generate the correct set of code, but it's still really helpful. You basically move the cursor around and the window will display the translated code that is currently "in scope". That is, if you're inside of a method it will show you code for that method. If you move the cursor out to the class, it will show you that class.

To install it, just copy it to your plug-in directory (usually C:\Program Files\Developer Express Inc\DXCore for Visual Studio .NET\2.0\Bin\Plugins\). When you start up Visual Studio a new menu option will appear under DevExpress > Tool Windows > Translator. The window shows a number of different .NET languages, but in reality CR only supports VB.NET and C#.

BTW - I've mentioned CodeRush/Refactor! and while that's a commercial product you can also download CodeRush by itself for free (and use any third party plug-ins or write your own). You just don't get any of the built-in templates or refactoring tools that the commercial product offers.

Oh, you can download the plug-in from here: http://www.rcs-solutions.com/Download.ashx?File=CR_Translator.zip or from the community plug-in site in a few days.

Links:

http://www.devexpress.com
http://code.google.com/p/dxcorecommunityplugins/

Report Standards

Paul Mrozowski — Sat, 16 Aug 2008 01:48:03 GMT

I've been using a "common" style for my reports for quite a while. I include a report title, then below that I usually have one or more lines which show which filter and sorting criteria were used to generate the report. I include a page X/Y line, usually in the bottom right hand side of the report. At some point I started adding the print date/time, who it was printed by, and the name of the file of the report. All of these fields together have been valuable when making changes and in determining why something isn't working.

The other day I had someone bring a report that was generated last year for the first 6 months of 2007 by an employee who is no longer with the company. They were attempting to tie the summarized numbers back to the detail which generated them and weren't sure how to find the detail. We had recently changed a fundamental aspect of how this particular report and supporting (detail) reports generated some of their numbers so I decided to just run a query against the database to get the information they were looking for. I looked at the timeframe the report was run for and wrote the query - there were no filters so it was really straightforward, or so I thought. When I totaled up the numbers they didn't match the report. Uh oh. I started worrying about what kinds of bad things might have happened that would have changed our historical tables.

Ugh...Then I noticed that the report was printed on the ending date of the report (in this case, it was run for 1/1/2007 - 6/28/2007) and it was printed on 6/28/2007. The light bulb went on. This particular report showed numbers that weren't available until a few months after the month they were applicable to. Essentially, we don't get numbers for January until March (even though they are posted back into January). So when this report was run, the numbers for April forward weren't available yet and weren't included in the report (even though the report range said it was through 6/28/2007). In the meantime those numbers had been posted and since I was pulling these numbers a year later they appeared in my version. I adjusted my query to exclude numbers posted after the report run date/time and suddenly everything balanced. Without knowing when the report was run it would have taken a LOT of work to resolve this (I'm not even sure I would have been able to).

Are there any types of things you're including on reports which have saved you?

In addition to the fields mentioned above, I've recently started including a "key" at the bottom of a number of reports which explain how numbers are generated and a more detailed explanation of their meaning. As questions come up about them I adjust my descriptions so they answer the questions asked. I'm using a really tiny font to keep the amount of space lost to them to a minimum but I'm finding they have also been really valuable since I'm not having to refer to the code as much to explain how some numbers were derived. It also addresses the issue where you use common names for column headers on various reports but the underlying numbers in those columns are actually inclusive or exclude different things.

Processing HTML Documents

Paul Mrozowski — Wed, 13 Aug 2008 02:17:44 GMT

I ran across a really cool .NET library on a recent project I've been working on. We have an internal website where we post news, documentation, etc. - basically a Content Management System (CMS). We're working on a new set of documentation that is being done inside of a third party help builder application. We need to import the HTML files it generates into our website (so we get all the things it offers, like security, searching, revision tracking, view statistics, etc.). So basically, I need to run through a lot of HTML files, build a tree of the documents (similar to the help file) and rewrite all of the URL's and image links to point to the correct URL inside of the site. I initially started looking at various regular expressions that I might be able to use over at http://regexlib.com/. Almost every single one of them had some comment about it failing under some circumstances. The HTML is surprisingly clean, but I was still nervous about it. So I looked at using GOLD to parse the HTML. However, from some of the comments I found it still didn't make everything as easy I would have liked. I finally ran across HtmlAgilityPack over on CodePlex . It's a .NET library which lets you read AND write changes to an HTML file via a simple API.

Here's a chunk of code from my importer so you can get a feel for how it works:


            HtmlDocument doc = newHtmlDocument();

doc.Load(content.FullDocumentPath);

            HtmlNodeCollection linkNodes = doc.DocumentNode.SelectNodes("//a/@href");

            Content match = null;

            // Run only if there are links in the document.
          
            if (linkNodes != null)

{

    // Fix up the URL's

    foreach (HtmlNode linkNode in linkNodes)

    {

        HtmlAttribute attrib
= linkNode.Attributes["href"];

        // If
it's an internal page anchor, ignore it

        if (attrib.Value.StartsWith("#"))

            continue;

        string path
= this.GetAbsolutePath(content.DocumentLink, attrib.Value);

        match = this.m_contentList.Find(p
=> p.DocumentLink == path);

        if (match
!= null)

            attrib.Value =
match.GetUrl();

        elseif (!path.ToLower().StartsWith("http://")
&& !path.ToLower().StartsWith("mailto:"))

            Console.WriteLine("Cannot
find matching document, searched for " + path);                        

    }

}

Basically, doc.DocumentNode.SelectNodes("//a/@href") returns a collection of links in the document (it uses XPath syntax for the selection string). From there, I just iterate through them, build the new URL, then save the modified Url via code that just does: linkNode.Attributes["href"].Value = "New URL Here". I also needed to strip out all the script tags inside of the document, so it uses similar syntax:


            private
            void StripOutScripts(HtmlDocument doc)

{

    // Strip out the scripts

    HtmlNodeCollection scriptNodes
= doc.DocumentNode.SelectNodes("//script");

    if (scriptNodes != null)

    {

        foreach (HtmlNode scriptNode in scriptNodes)

        {

            scriptNode.ParentNode.RemoveChild(scriptNode, false);

        }

    }

}

I do the same sort of thing - iterate over the collection, except this time tell it to remove the nodes from the document (note that I'm grabbing the parent node, since the current node is everything contained within the script, excluding the <script> tags. By getting the parent, we get that and the tags themselves.

Each collection has a WriteContentTo() method which can write the HTML for that section of the document to a Stream. What's really nice about this entire library (besides how simple it was to use) was the fact that it doesn't seem to mangle the existing HTML when using WriteContentTo() (at least from what I've seen). Only one minor complaint - the docs are a bit weak. It just includes the standard documentation of the classes, not much in the way of examples. However, it's pretty consistent so it doesn't take much to get started with it.

What a great library - it couldn't be simpler. It saved me a ton of time.

Links:

http://www.codeplex.com/htmlagilitypack
http://regexlib.com/
http://www.devincook.com/goldparser/

How much is enough?

Paul Mrozowski — Sun, 10 Aug 2008 21:33:24 GMT

How much memory is "enough"? I've got 4 GB (imagine telling someone 5 years ago you had 4 Gigabytes of memory!) in my machine right now and I'm really wishing I had more. Enough so that I've got (4) 2GB memory sticks in my shopping cart at NewEgg. I'd get 16 GB, but it's just too expensive right now. It would seem like 4 GB would be enough, but when I upgraded my machine a while back to Vista (64-bit) I took a VMWare snapshot of my XP machine before the upgrade. I'm still using that machine since I've got a few apps. that need to run and I haven't really wanted to spend the money upgrade to the new versions. I like to allocate approx. 1.5 GB of memory to that machine. I also maintain a VMWare image of Visual Studio that gets 2 GB of memory. Between the both of them I can't really run them at the same time with any reasonable performance.

How much memory do most developers now run? I'm guessing 2 GB is now "entry level". I can use up 1 GB of memory just with Visual Studio. If I don't close FireFox down regularly it'll keep taking up memory (I've seen it as high as 300-400 MB of memory). Outlook grabs around 130 MB. Add a few other apps. to that and suddenly that doesn't leave much room for the OS.

8 GB should give me a bit of breathing room, at least for a bit. How many years until I'm saying something like, "I've got 4 TB of memory and it's not quite enough..."

Collapsing XML Comment Tags

Paul Mrozowski — Fri, 08 Aug 2008 23:48:44 GMT

It looks like it's the week of CodeRush/DXCore plug-ins (I still have one more plug-in coming up).

This plug-in just lets you collapse an XML comment tag to one line (I'll explain it a bit more below). It was actually written as a Refactoring (so it appears when you hit the refactoring key or wait for the ellipses to appear below the code). I put this one together a few days ago and was planning on releasing it after I had a chance to live with it for a while to see if there were any changes or bugs I needed to address. But someone on the DevExpress forums recently posted about wanting a plug-in which did the same thing so I decided to just release it right now.

So what does this really do?

When you add an XML comment to something like a property or field, you end up with the following (in C#):

/// <summary>
///
/// </summary>
private int m_someValue;

The cursor is positioned on the second line. After installing this refactor, you can hit the Refactor key (which I mapped to just the ` key (instead of Shift or Alt or Ctrl ` , whatever the default is) and this comment will get collapsed down to one line:

/// <summary></summary>

This can help eliminate some of the visual noise of the comment, especially if it's just a short comment.

If you have a muli-line comment, it actually collapses it in two stages (that is, you can issue the refactoring twice), so for example:

/// <summary>
/// This is my multi-line
/// comment about nothing.
/// </summary>

The first time you collapse it you end up with:

/// <summary>This is my multi-line
/// comment about nothing.</summary>

The second time you collapse it you end up with:

/// <summary>This is my multi-line comment about nothing.</summary>

You can download the code from the download section on my site or the community site. Like before, there will probably be a few day delay between when it's available here and when I get it put up on the community site.

To install it, copy it into your C:\Program Files\Developer Express Inc\DXCore for Visual Studio .NET\2.0\Bin\Plugins\ folder.

Links:

http://www.rcs-solutions.com/Download.ashx?File=Refactor_Comments.zip
http://code.google.com/p/dxcorecommunityplugins/

Strange Crystal Reports Issue

Paul Mrozowski — Wed, 06 Aug 2008 00:48:19 GMT

This was an interesting issue I ran into today. We've got a utility I wrote which can search Outlook MSG files from a desktop interface. As part of the program you can print the results to a PDF - I'm using Crystal Reports to do the printing. We had this application installed on a desktop machine for a while but decided to move it over to one of our servers which had a lot more hard drive space. After moving it, when we attempted to print we'd get the error:

"An error has occurred while attempting to load the Crystal Reports runtime"

At first I thought it might be related to needing the CR DLL's installed instead of just being deployed in the app's directory. We installed them and tried again - same exception. Further down in the error (which was actually helpful - imagine that!), "Please install the appropriate Crystal Reports redistributable (snip) containing the correct version of the Crystal Reports runtime (x86, x64, or Itanium).

Hmm...x86 vs x64 - that has to be it - this was Windows 2003 Server (64 bit) vs. XP. I actually just recently listened to a DotNetRocks podcast which talked about .NET applications running under a 64 bit OS. They mentioned that, by default, most .NET applications are compiled under "Any CPU". That means the code get's JIT'ed to 64 bit code under a 64 bit OS - sounds OK. The only catch is that all the components must also be compiled the same way, otherwise you run into problems. I didn't really want to have two different sets of DLL's so I went back into my application and changed it from "Any CPU" to x86 code and recompiled. Order was restored to the universe.

Links:

http://www.dotnetrocks.com

CodeRush Plug-in - Developer Initials

Paul Mrozowski — Wed, 06 Aug 2008 00:44:45 GMT

I've been meaning to post this up for a while but never seemed to get around to it. I've been using CodeRush/Refactor! (CR/R!) from DevExpress for a few years now and this was actually the first plug-in I wrote for it. If you're not familiar with CR/R! it's sort of Intellisense on steroids with a bunch of refactoring tools thrown in. OK, that description really doesn't do it justice - I'd suggest taking a look at a few of the videos they have available over on the DevExpress site to get a better idea of what it does. I'd put CR/R! in the "must have" category for any .NET developer. It's actually annoying to use Visual Studio without it installed.

At any rate, this plug-in does nothing more than make it easy to insert a comment that contains the developers name or initials and the date of a change. For example:

// PCM - 8/4/2008 -

Or, if you happen to be located on an XML comment line, it inserts the following:

/// <developer>Paul Mrozowski</developer>
/// <created>08/04/2008</created>

It's language agnostic, so this should actually work in VB.NET as well (it uses the '/''' comments instead). I've tied it to the CTRL-Insert keypress but you can set it to whatever you'd like. To install it, copy it into your C:\Program Files\Developer Express Inc\DXCore for Visual Studio .NET\2.0\Bin\Plugins\ folder. Then start up Visual Studio and go to DevExpress, Options. You should see a new tree option, "Developer Initials". Fill in your name and initials and select whether you'd like it to insert your initials for a line comment or your full name. Then go to IDE > Shortcuts. Expand the Code folder. I created a new folder named "Custom". Click on the icon for a new keyboard shortcut. On the right-hand side, enter the keyboard shortcut (mine is Ctrl+Insert), then in the Command combo select "Add Initials". Then hit OK.

Exit out of Visual Studio and go back in. At this point the plug-in should be active. Try it out by hitting your shortcut key on a new line (or, enter an XML comment. Hit enter, on the new XML comment line hit the shortcut key).

You can download it from here (http://www.rcs-solutions.com/Download.ashx?File=CR_Initials.zip) and it may show up on the DX Core Community Plug-ins site at some point.

Links:

http://www.devexpress.com
http://code.google.com/p/dxcorecommunityplugins/

Sorting the VFP Grid

Paul Mrozowski — Thu, 31 Jul 2008 23:00:22 GMT

I'm a big fan of "drop in" components which can add functionality to my applications as opposed to what is more commonly thought of as framework-level code which bakes in functionality. What do I mean by that? The common way of building in functionality into an application (usually via a framework) is to develop an class hierarchy, then inherit from those classes in your application as you build up functionality. A form class may have code which remember it's location and size when you close it, an edit box may add some spell checking ability, etc. If you inherit all your forms from these classes they suddenly have all this extra functionality and life is good. Then, as the framework is built you tend to end up with a number of interdependencies between components, which suddenly makes it more difficult to be able to use these components in other applications which don't include most of the framework libraries and inherit from the proper classes. On one level that's fine, since it allows for a fairly high level of functionlity and consistency. However, it requires a high level of "buy in" in order to use even the most basic aspects of the framework. In many cases you just can't use some cool combobox class from the framework in another application without requiring the full framework (and inheritance chain) to come along for the ride, which is a bit of a bummer. There are a huge number of applications written that can't easily be integrated with a framework (as most VFP developers think of them).

What if, instead, components had been built to be highly modular - drop in a small class library, add the control to your form and you're good to go. Until VFP 9 this was actually a bit hard to do - what if your control needed to respond to various form events? Everytime you dropped the control on a new form you suddenly have to do a lot of wiring up by adding code to the various events your control was interested in. Which leads back to the first style of development that just assumes you'll be using these components as a whole. However, in VFP 8 they introduced a set of commands to allow for event binding: BINDEVENT(), UNBINDEVENTS(), RAISEEVENT() and AEVENTS(). So what do these commands do and what do they give you? They give you a mechanism of listening for specific events that fire on an object and handling those events in your own code. You can do this without the "source" object even being aware you are listening to it's events.

A real example might help:

Let's suppose we want to add the ability to sort a column in a grid. The common way of doing that would be to create a subclass of a grid, add some code which does the sorting as methods on the grid, then require the developer to "hook up" the functionality during development so that this new functionality runs when a user double clicks on the row header. In effect, add code to every column's header and adding code to the DblClick() event. You'll even notice that even if we require you to use our new subclassed grid the functionality still isn't really just drop-in seamless. So how can event binding help here? Can it help us achieve both goals of not requiring any glue code and not requiring us to inherit from a specific grid control?

What I'd love to end up with is a control I can drop on a form, point it to some grid control and have the grid "magically" let you sort without requiring a lot of changes to existing code. So let's start from that premise and create a custom control. I'll add cGridEval property which can be filled in with a string which is EVAL'd at runtime to resolve a live object reference to our grid, ex. you can fill in something like "This.grdSample". That's the easy part, now how do we use event binding to let us do everything else?

I'm going to create a BindControl() method in my custom control and we'll first get our object reference to the grid:

loGrid = EVALULATE(This.cGridEval)

Every grid has a Columns collection that we can iterate through and each column has a Controls collection. If we take a look at the BaseClass property of the controls here we can determine which one is the Header column. At that point we can use BINDEVENT() to hook up the DblClick() event to us - when a user double clicks on this grid column the event will fire on the grid and we'll also get a notification that the event has fired.

FOR EACH loColumn IN loGrid.Columns
    FOR EACH loControl IN loColumn.Controls
        IF loControl.BaseClass = "Header"
           BINDEVENT(loControl, "DblClick", This, "Sort")
           EXIT
        ENDIF
    ENDFOR
ENDFOR

The BINDEVENT() function is where the real work happens. We pass the control we want to listen to as the first parameter, in this case the header. Then we pass (as a string) the name of the method we want to listen to, "DblClick". The third parameter is an object reference to the subscribing object (us), and the fourth is the method VFP should call on our object. You must make sure that the method you create on your subscriber accepts all of the same parameters as the event being fired. For example, if we hooked into the KeyPress event accepts two parameters, nKeyCode and nShiftAltCtrl - you have to accept the same parameters in your subscribing method. There is a fifth parameter which can be passed that we're not using which allows you to specify when your code is called - before or after the original control's method fires. DblClick doesn't pass in any parameters, so we're good. All we need to do is create a "Sort" method on our custom object.

At this point, when a user double clicks on a header in a hooked up grid, our Sort event fires. Great - now how do we figure out which header was clicked on? Here's where the AEVENTS() function comes into play - it fills an array with information about the object that triggered the event. We can use this information to get a reference to the actual header the user double clicked on. From there, we can determine which column in the grid to sort.

AEVENTS(laEvent, 0)

This gives us a 3 column array, laEvent:

laEvent[1] - An object reference to the header that was clicked
laEvent[2] - The event that was fired
laEvent[3] - The event type (how the event was raised).

In our case we're really only interested in laEvent[1]. Once we have our header reference we can get the column's control source like this:

loHeader = laEvent[1]
loColumn = loHeader.Parent
lcControlSource = ALLTRIM(loColumn.ControlSource)

All that's left is for us to build up a way of creating temporarily indexes and maintaining a list of which indexes have been created (and whether we're current ascending or descending). One thing that is kind of nice is that since we now have a reference to the column header, we can also do things like add some graphical image to the sorted column to make it easy for the user to see which column has been sorted and in which direction.

You'll notice that I didn't cover the UNBINDEVENTS() and RAISEEVENT() functions. UNBINDEVENTS does just what you'd expect - it unhooks an event handler so that it no longer receives the bound event. RAISEVENT() lets you "fire" an event (both things like custom events and events on native VFP objects). I don't have a nice example of this so I'll leave that for some other time.

You can download the finished control below.

Links:

http://www.rcs-solutions.com/Download.ashx?File=rcsGridSort.zip

What is Dependency Injection?

Paul Mrozowski — Sat, 12 Jul 2008 14:34:13 GMT

I mentioned Dependency Injection / Inversion of Control (DI/IoC) recently and I really didn't explain what it is, why you might want to use this particular pattern, and why on earth you'd need a framework for it. It's a fancy name for a fairly simple concept. Instead of creating objects inside of your classes, you let the calling code "inject" the necessary instances into your code. It's probably easiest to see this in some code. I'm going to show both C# and VFP code, since I don't want you to get the idea that this is a .NET-only type thing.


               1 publicclassSampleDependency

               2 {

               3    publicstring SayHello()

               4    {

               5       return"Hello";

               6    }

               7 }

               8 

               9 publicclassSample

               10 {

               11    protectedSampleDependency m_depend;

               12 

               13    publicSampleDependency Depend

               14    {

               15       set { this.m_depend
= value; }

               16    }

               17 

               18    public Sample(SampleDependency depend)

               19    {

               20      
m_depend = depend;

               21    }

               22 }


                  Sample sample
= newSample(newSampleDependency());

VFP Version

DEFINE CLASS SampleDependency AS Session 
   FUNCTION SayHello() 
      RETURN "Hello" 
   ENDFUNC 
ENDDEFINE 

DEFINE CLASS Sample AS Session 
   oDepend = NULL 
   FUNCTION Init(toDepend) 
      This.oDepend = toDepend 
   ENDFUNC 
ENDDEFINE 

loSample = CREATEOBJECT("Sample", CREATEOBJECT("SampleDependency"))

Notice that in both cases, we are passing in the instance we want the class to use instead of letting the class create the instance itself. That's all DI/IoC is. Honest, that's it. This is DI via a constructor (you can also do it via a property setting instead; notice in the sample C# code I created a write-only property which could hold the reference).

So the next obvious question is, why? What's wrong with just creating the object inside of the class?

One thing DI gives you is the ability to easily swap in different objects. In the C# example, we probably would change the parameter from a specific type to an interface. Now any class which implements that interface can be injected into this class. In VFP, since it's not strongly typed, you can just pass in whatever instance you'd like (it's up to you to make sure it doesn't blow up at runtime by accessing some method or property which isn't on the passed in object). My initial thought after seeing this was, well, can't I just use an abstract factory pattern instead? In an abstract factory you delegate object creation to a "object factory" - usually passing in a name or calling a method which returns the actual instance you'd like to use. This sounds like almost the same thing.

An abstract factory does let you do that, but it doesn't let you easily do something the DI/IoC pattern does: test your objects. Let's suppose you want to write a test for a class which uses another class to send out an e-mail. You aren't really trying to test sending an e-mail - that's just one of the things the class you're testing happens to do during some process. In fact, you really don't want to send out an e-mail; we don't want to spam our users. If you happen to use the abstract factory pattern, you would need to modify it to create your dummy/stub/mock object for sending an e-mail (in VFP that's most likely by editing a record in a table, but the idea is the same), then test the object in question. If you used the DI/IoC pattern the only thing you need to do is pass in your dummy/stub/mock object. No other modifications are necessary.

OK, so this all looks simple enough. Why would you need a framework for the above?! One of the biggest reasons - less typing. You'll notice that in order to use any of these objects you may have to pass in a bunch of other dependency objects (which themselves may have other dependencies). For a complex set of objects that could really suck. A DI framework does that for you along with the benefits of an abstract factory, all rolled up into one. In your code you call the DI framework and tell it to get you an instance of a class - it figures out what objects need to be passed in for you so you don't need to do it. In your tests, you can instanciate the objects directly and pass in your stub/dummy/mock objects instead.

Links:

http://weblogs.asp.net/rosherove/archive/2007/09/16/mocks-and-stubs-the-difference-is-in-the-flow-of-information.aspx
http://www.hanselman.com/blog/ListOfNETDependencyInjectionContainersIOC.aspx

MSDN on a diet

Paul Mrozowski — Sat, 12 Jul 2008 00:42:02 GMT

I just ran across this and it's a welcome option - you can add "(loband)" to any of the MSDN documentation links on the website and it will serve up a much faster version of the docs. For example,

Instead of:

http://msdn.microsoft.com/en-us/library/cc707819.aspx

Try:

http://msdn.microsoft.com/en-us/library/cc707819(loband).aspx

There is also an option at the top of the page once you've selected this option to persist the low bandwidth view, so all of the pages come up like this by default. I appreciate the treeview when I'm just poking around, but sometimes the site is just painful to navigate to since it takes a bit to render them.

Paul Mrozowski's Blog

Recursive Queries in SQL Server 2005

Grid Sort Sample

Handling Disconnects with WCF

Securing Static Content Through ASP.NET

Cool Pet

Translate C# to VB.NET (or vice-versa) using CodeRush

Report Standards

Processing HTML Documents

How much is enough?

Collapsing XML Comment Tags

Strange Crystal Reports Issue

CodeRush Plug-in - Developer Initials

Sorting the VFP Grid

FOR EACH loColumn IN loGrid.Columns FOR EACH loControl IN loColumn.Controls IF loControl.BaseClass = "Header" BINDEVENT(loControl, "DblClick", This, "Sort") EXIT ENDIF ENDFORENDFOR

What is Dependency Injection?

MSDN on a diet

FOR EACH loColumn IN loGrid.Columns
FOR EACH loControl IN loColumn.Controls
IF loControl.BaseClass = "Header"
BINDEVENT(loControl, "DblClick", This, "Sort")
EXIT
ENDIF
ENDFOR
ENDFOR