Tweaking the SiteMap Navigation Menu

I have a site that uses a SiteMap provider and a Menu control for the site's navigation controls. Using the SiteMap provider is a simple and quick way to get dynamic menu for your site. Everything was going along smoothly until I was asked to have one of the menu items open it's link in a new window.

If this was just a link, I'd add a "target='_blank'" attribute and be done. However, a quick romp in the web.sitemap file showed that Visual Studio didn't have anything in the intellisence that resembled the target attribute. So what are we to do? Well, I'm glad you asked.

A little digging showed that the System.Web.UI.WebControls.MenuItem object actually had a target property. So all I had to do was set it. In the Web.Sitemap, I added a target attribute to the sitemap node.

<siteMapNode url="http://ewo/" title="EWO" target="_blank" />

Then I mapped the OnMenuItemDataBound event to a custom handler.

<asp:SiteMapDataSource ID="SiteMapDataSource1" runat="server" ShowStartingNode="false" />
<asp:Menu ID="Menu1" runat="server" DataSourceID="SiteMapDataSource1" Orientation="Horizontal"
   OnMenuItemDataBound="Menu1_MenuItemDataBound">

The target attribute that I set in the SiteMapNode is translated into the dataobject and can easily be retrieved and used to set the MenuItem's target property.

protected void Menu1_MenuItemDataBound(object sender, MenuEventArgs e)
{
   SiteMapNode node = (SiteMapNode)e.Item.DataItem;

   if (node["target"] != null)
       e.Item.Target = node["target"];       
}

There are other great attributes that can be set this way, including the image url, text and tooltip properties. Have fun!

PowerShell Convert CSV to SQL Insert Script

Recently, I've been handed a lot of Excel spreadsheets (and various other formats) and told to move the data into a SQL table.  You could do this with DTS if you have that kind of access to the server, but suppose you didn't.  Here's a little PowerShell script that crunches through the rows in a CSV file and generates insert statements in a SQL script.  The script requires that you pass a path to the CSV file and also accepts an optional parameter for the name of the table to insert into.  If you don't pass this in, the filename is used as the table name.  The script is intentionally lengthy and properly commented to make it easier to read and understand. Here you go...

# Verify that a file argument was passed in.
if ($args[0]) { $file = $args[0] }
else { write-warning "You must supply a path to the csv."; break }

# Verify that the file exists.
if(![System.IO.File]::Exists($file)) { write-warning "Can not find '$file'."; break }

# Verify that the file is a csv file.
if(!$file.ToLower().EndsWith("csv")) { write-warning "The file specified is not a CSV file."; break }

# If no table name was provided, we'll use the file name instead.
if($args[1]) { $tableName = $args[1] }
else { ls $file | % { $tableName = $_.Name.ToLower().TrimEnd(".csv") } }

# Generate an informative header for the sql file.
# Note that the out-file command will overwrite any existing file.
$output = $file.ToLower().TrimEnd(".csv") + ".sql"
"/************************************************************`n" +
"** Script generated by CsvToBatchInsert.ps1 `n" + 
"** Date: " + [System.DateTime]::Now.ToString("MM/dd/yyyy hh:mm:ss") + " `n" + 
"** From file: $file `n" + 
"************************************************************/`n" | out-file -filepath $output 

# Loop through the rows in the csv file.
Import-Csv $file | % {
    # The insert variable is used to build a single insert statement.
    $insert = "INSERT INTO $tableName ("
    
    # We only care about the noteproperties, no use dealing with methods and the such.
    $properties = $_ | Get-Member | where { $_.MemberType -eq "NoteProperty" }

    # Create a comma delimited string of all the property names to use in the insert statement. 
    # You should make sure that the column headings in the CSV file match the field names in 
    # your table before you run the script.
    $properties | % { $insert += $_.Name + ", " }
    
    $insert = $insert.TrimEnd(", ") + ") VALUES ("
    
    # Couldn't figure out how to access the value directly.  So here I'm forced to use 
    # substring to get it.  The Definition looks like "System.String PropertyName=PropertyValue".
    # Since the value will be enclosed in single quotes, you will run into trouble if the value  
    # contains a single quote.  To escape the single quote in T-SQL, just put another single quote
    # directly in front of it.
    $properties | % { 
        $value = $_.Definition.SubString($_.Definition.IndexOf("=") + 1)
        $insert += "'" + $value.Replace("'", "''") + "', " 
    }
    
    $insert = $insert.TrimEnd(", ") + ")"
    
    # Append the insert statement to the end of the output file.
    $insert | out-file -filepath $output -append
}

You can easily tweak this script to generate update statements, or delete statements, or anything else for that matter.  As always, I welcome any comments, especially ones that show a better way of doing this.  Enjoy!!!

Dynamically create Powershell objects

I wrote a sweet powershell script today that dynamically generated a custom object.  To create an object in Powershell, you basically edit an existing object by adding new methods and properties.  In my case, I used System.Object and added some properties. 

Suppose you are in the middle of a powershell script and you realize that you need a cat class.  Cat's have claws, fur and multiple output devices, but let's limit ourselves to just one.

$myCat = New-Object -TypeName System.Object
$myCat | Add-Member -MemberType noteProperty -Name "Fur" -Value "Soft"
$myCat | Add-Member -MemberType noteProperty -Name "Claws" -Value "Sharp"
$myCat | Add-Member -MemberType ScriptMethod GenerateHairBall { $hairball = "Hmmph, hmmph, ack, aaaaaack! There you go!"; $hairball }

And there you go.  If you have more than one cat, you can store them in an array.  Like this:

$cardboardBox += $myCat
$cardboardBox += $SarahsCat

etc... 

Javascript getElementByRegexId Function

One of the most frustrating aspects of working with ASP.NET is the way it rewrites the id's for all the controls on the page.  When you want to access that control via javascript, you're screwed.  Sure you could have ASP.NET render the control's ClientID, like...

javascript:alert('<%= txtName.ClientID %>');

But, IMO, this is highly unattractive.  It also breaks the separation of content, design, and function by requiring javascript to be on the page.

We are all pretty reliant on the getElementById function.  It's a shame that there isn't a 'getElementByPartialId' function.  I can not find a way to get javascript's default functions to return my "txtName" control when it is rendered as the "ctl00_GridView1_WhoApprovedThisNamingConvention?_ctl05_txtName" control.  ASP messes with the "name" attribute as well.  It just uses dollar signs instead of underscores. 

Note to Microsoft:  Next time you revamp ASP.NET.  Create a new attribute on your base web control object called "aspid" and hack away.  Just leave "id" alone.

Knowing that Javascript is incredibly powerful and versatile, I put together a little function to help out.  You just pass in the id that you know and what kind of control you expect to be rendered.  Passing in "*" for the tag name should work as well, it just won't be quite as fast.

function getElementByRegexId(id, tagName)
{
    var rx = new RegExp("^.*" + id + "$");
    var controls = document.getElementsByTagName(tagName);
    var result;
    for(var i = 0; i < controls.length; i++)
    {
        if(rx.test(controls[i].id))
        {
            result = controls[i];
            break;
        }
    }
    return result;
}

I've gone through this exercise before, but I can't remember where.  Can anyone think of a cooler way to accomplish this? 

ASP.NET Membership Password Requirements

I've been meaning to post about this for a while.  I experienced an issue with the ASP.NET Membership provider and it's default password requirements.  By default the provider requires that a user's password contains at least one non-alphanumeric character.  After blindly stumbling around Google for a while I smacked my head on this post by Derek Hatchard.  Turns out that there is an attribute in the membership provider section called "minRequiredNonalphanumericCharacters".  Once I set this to "0", my issue was resolved.

There are some other cool attributes as well.  "MinRequiredPasswordLength" sets how long the password has to be.  You can even validate the password against a regular expression with the "passwordStrengthRegularExpression" attibute.

Resharper 3.1 Upgrade Issues

I upgraded from Resharper 3.0 to 3.1 today and immediately had some issues. 

Issue #1 - Intellisense was disabled. 

My intellisense was disabled on the Visual Studio level.  To re-enable it, I went to Tools -> Options -> Text Editor -> C#.  In the Statement Completion control grouping, I checked "Auto list members"  to turn it back on.

Issue #2 - Alt+Enter stopped working.

To fix this, just go to Resharper -> Options -> General.  Then select the "Visual Studio" radio button under "Restore Resharper keyboard shortcuts". Then click "Apply"

Here's another handy tip.  Since Resharper won't play nice with the new .NET 3.0+ features until version 4.0, you can temporarily disable/enable it on any open file with the Ctrl+8 keyboard shortcut.