Using the power of PowerShell to Configure your Web.Config for Different Environments,Customize the Output of SQLMetal and Bulk Update Word Documents

Microsoft PowerShell (http://www.microsoft.com/windowsserver2003/technologies/management/powershell/default.mspx) is an incredibly powerful tool because it has a particularly elegant syntax for manipulating XML files - with no XML DOM code required. It has many other features such as support for using .NET code inline (e.g. c#) - however I will just concentrate on the XML handling capabilities today.

With this in mind, it is extremely handy as a tool for ANYTHING XML or done previously in batch files:

1. Modifying your web.config as part of your build process during deploy. In a previous post, Scott Guthrie has recommended using multiple config files, one for each environment (See http://weblogs.asp.net/scottgu/archive/2007/09/21/tip-trick-automating-dev-qa-staging-and-production-web-config-settings-with-vs-2005.aspx). The problem with this is you have complete duplication of settings files. This means it is more likely you will forget to update a setting for all different environments. Using one master config file that is configured for each environment by powershell is much better. Powershell can also do all your zipping, and FTPing to staging servers.
2. Deployment and customization of Reporting Services Reports. RDL is a standard XML language can be customized if you don't like the output of the Visual Studio RDL editor/designer.
3. Updating Word and Excel 2007 Documents automatically. The docx format is now all XML - so powershell can perform bulk updates to Word documents for you without the need to use Word automation or a separate executable.
4. Updating LINQ DBML files to your liking. I will detail an example of this below.

An example of the power of PowerShell: If you are not happy with the output of SQLMetal (e.g. it generates LINQ entities for tables you don't want to see), you can use the following batch file to perform a "cleanup" of the tables you don't want in the dbml (Thanks to Andrew Weaver for this, for more powershell goodness, you can see his blog on http://leftlobed.wordpress.com/)

This batch file and powershell script does the following to fix up what the SQLMetal dbml generator doesn't do:

• removes tables that are not needed in the DBML
• renames associations so they are more meaningful (e.g. if you have multiple relationships between 2 tables, they will get names such as Lookup1, Lookup2, so your LINQ code using these relationships won't make much sense.)

The Batch File:

sqlmetal /conn:"data source=myServerName\dev;initial catalog=MyDataBaseName;trusted_connection=false;Persist Security Info=True;User ID=UserName;Password=Password;" /dbml:PropertyPipelineDb.dbml /context:InvestmentManagementDataContext /namespace:LendLease.InvestmentManagement.Domain.Entity /pluralize /entitybase:"LendLease.Framework.Domain.Entity.MutableEntity" /views

powershell -command "$temp = .\CleanDbml.ps1 -dbml '.\PropertyPipelineDb.dbml'; $temp  Out-File '.\PropertyPipelineDb.dbml' -Encoding Unicode"

sqlmetal /code:PropertyPipelineDb.designer.cs PropertyPipelineDb.dbml

pause

The PowerShell Script (CleanDBML.ps1)

Param

(

    $DBML # File Path to DBML file

)

$removeTables = ("dbo.vRolePermission", `

                 "dbo.vOpportunityFlattenedFundInterests", `

                 "dbo.vOpportunityDetailsWithFlatFundInterests", `

                 "dbo.vAssetAddress", `

                 "dbo.vContactOpportunities", `

                 "dbo.vContactOpportunitiesWithFlatFundInterests", `

                 "dbo.vLookupHierarchy",  `

                 "dbo.CachedUser", `

                 "dbo.CachedUserExtension", `

                 "dbo.OpportunityImport" `

                );

$renameTables = @{ "dbo.vOpportunitySearch" = @{ Type = "vOpportunitySearch" ; Member = "vOpportunitySearches"}; `

                   "Code.NotificationType" = @{ Type = "NotificationType" ; Member = "NotificationTypes"};  `

                   "dbo.vPropertyPipelineFund" = @{ Type = "Fund" ; Member = "Funds"}  `

                 };

$renameAssosciations = @{ "FK_OpportunityRelationship_Opportunity1" = @{ FKMember = "Opportunity1"; PluralMember = "OpportunityRelationship1s"}; `

                          "FK_OpportunityRelationship_Opportunity2" = @{ FKMember = "Opportunity2"; PluralMember = "OpportunityRelationship2s"}; `

                          "FK_CompanyCompanyType_Company" = @{ FKMember = "Company"; PluralMember = "CompanyTypes"}; `

                          "FK_NotificationType_NotificationType" = @{ FKMember = "ParentNotificationType"; PluralMember = "ChildNotificationTypes"} `

                        };

$exists = Test-Path ($DBML);

if (! $exists)

{

    $DBML = Join-Path -path $pwd -childpath $DBML;

}

$exists = Test-Path ($DBML);

if( $exists )

{

    $dbmlFileInfo = Get-Item -path $DBML;

    [System.Xml.XmlReader]$local:reader = [System.Xml.XmlReader]::Create($dbmlFileInfo);

    [System.Xml.XmlDocument]$local:doc = new-object System.Xml.XmlDocument;

    $doc.set_PreserveWhitespace($true);

    $doc.Load($reader);

    $reader.Close();

    [System.Xml.XmlElement]$local:root = $doc.get_DocumentElement();

    # Remove nodes that don't belong

    $removedTypeMap = @{};

    $tableNodesRemoved = $doc.Database.Table `

                             ForEach-Object { $_; } `

                             Where-Object { $removeTables -contains $_.Name } `

                             ForEach-Object { $removedTypeMap.Add($_.Type.Name, $_.Name); $_ = $doc.Database.RemoveChild($_); $_; };

    # Remove any assosciations on other tables which reference a removed type

    $assosciationNodesWithTypesRemoved = $doc.Database.Table `

                             Where-Object { $_.Type.Association -ne $null } `

                             ForEach-Object { $_.Type.Association; } `

                             Where-Object { $removedTypeMap[$_.Type] -ne $null } `

                             ForEach-Object { $_ = $_.get_ParentNode().RemoveChild($_); $_; };

    # Rename nodes for tables with their new aliases

    $tableNodesToRename = $doc.Database.Table `

                             ForEach-Object { $_; } `

                             Where-Object { $renameTables[$_.Name] -ne $null };

    $renamedTypeMap = @{};

    $tableNodesToRename  ForEach-Object { $newName = $renameTables[$_.Name].Type; $renamedTypeMap.Add($_.Type.Name, $newName); $_.Type.Name = $newName; };

    $renamedMemberMap = @{};

    $tableNodesToRename  ForEach-Object { $newName = $renameTables[$_.Name].Member; $renamedMemberMap.Add($_.Member, $newName); $_.Member = $newName; };

    # Fix up any assosciations on other tables which reference a renamed type

    $assosciationNodesWithTypesRenamed = $doc.Database.Table `

                             Where-Object { $_.Type.Association -ne $null } `

                             ForEach-Object { $_.Type.Association; } `

                             Where-Object { $renamedTypeMap[$_.Type] -ne $null} `

                             ForEach-Object { $_.Type = $renamedTypeMap[$_.Type]; $_; };

    # Fix up member names for any assosciations on other tables which are Foreign Key assosciations and reference a renamed type

    $assosciationNodesWithFKMembersRenamed = $assosciationNodesWithTypesRenamed `

                             Where-Object { $renamedTypeMap[$_.Member] -ne $null -and $_.IsForeignKey -eq "true"} `

                             ForEach-Object { $_.Member = $renamedTypeMap[$_.Member]; $_; };

    # Fix up member names for any assosciations on other tables which are Subset assosciations and reference a renamed member

    $assosciationNodesWithSubsetMembersRenamed = $assosciationNodesWithTypesRenamed `

                             Where-Object { $renamedMemberMap[$_.Member] -ne $null -and $_.IsForeignKey -eq $null } `

                             ForEach-Object { $_.Member = $renamedMemberMap[$_.Member]; $_; };

    $assosciationFKNodesRenamed = $doc.Database.Table `

                             Where-Object { $_.Type.Association -ne $null } `

                             ForEach-Object { $_.Type.Association; } `

                             Where-Object { $renameAssosciations[$_.Name] -ne $null -and $_.IsForeignKey -eq "true"} `

                             ForEach-Object { $_.Member = $renameAssosciations[$_.Name].FKMember; $_; };

    $assosciationSubsetNodesRenamed = $doc.Database.Table `

                             Where-Object { $_.Type.Association -ne $null } `

                             ForEach-Object { $_.Type.Association; } `

                             Where-Object { $renameAssosciations[$_.Name] -ne $null -and $_.IsForeignKey -eq $null} `

                             ForEach-Object { $_.Member = $renameAssosciations[$_.Name].PluralMember; $_; };

    [System.Xml.XmlWriterSettings]$writerSettings = new-object System.Xml.XmlWriterSettings;

    $writerSettings.set_Indent($true);

    $writerSettings.set_NewLineHandling([System.Xml.NewLineHandling]::Replace);

    $writerSettings.set_CloseOutput($true);

    $local:outputStream = new-object System.IO.StringWriter;

    [System.Xml.XmlWriter]$local:writer = [System.Xml.XmlWriter]::Create($outputStream, $writerSettings);

    $doc.Save($writer);

    $writer.Flush();

    $writer.Close();

    $outputStream.Close();

    $outputStream.ToString();

}

To assist your development of powershell scripts, you can use PowerShell Plus (http://www.powershell.com/) to parse and debug your scripts. Another amazing utility is the powershell add-in for reflector: http://www.hanselman.com/blog/ReflectorAddinsAndPowerShellLanguageSupportForReflector.aspx. This actually attempts to disassemble existing .NET DLLs to powershell scripts.

There are a whole host of plugins for Lutz Roeders application mentioned in the Hanselman article above (the Reflector compare of different dlls looks very useful) -but I'll have to save my comments for a later blog post...

DDK