September 27, 2008
@ 11:48 AM
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.

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.


 
Categories: SQL

September 25, 2008
@ 06:51 PM

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.

gs

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


 
Categories: VFP

September 24, 2008
@ 07:14 PM

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 += new EventHandler(ClientFaulted);

OperationContext.Current.Channel.Closed += new EventHandler(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 as IClientCallback;

 

    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 = new RegisteredClient[0];

 

    lock (m_callbackList)

    {

        clientList = new RegisteredClient[m_callbackList.Count];

        m_callbackList.CopyTo(clientList);

 

        foreach (RegisteredClient registeredClient in clientList)

        {

            ICommunicationObject callbackChannel = registeredClient.CallBack as ICommunicationObject;

 

            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


 
Categories: WCF

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. 

 

Properties

AddExtMap

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
 
Categories: ASP.NET | IIS