) 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:
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
This batch file and powershell script does the following to fix up what the SQLMetal dbml generator doesn't do:
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 parse and debug your scripts. Another amazing utility is the powershell add-in for reflector:
. 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...
