if ($args.length -lt 3)
{
Write-host "usage: save_diagram db_name diagram_name file_name"
}
else
{
function Join-String
{
begin { $sb = New-Object System.Text.StringBuilder }
process { $sb.Append($_) | Out-Null }
end { $sb.ToString() }
}
$dbname = $args[0]
$filename = $args[2]
New-item $filename -type file -force | out-null
$declarationsStr = "SET NOCOUNT ON
DECLARE @newid INT
DECLARE @DiagramSuffix varchar (50)"
$insertStr = "PRINT 'Suffix diagram name with date, to ensure uniqueness'
SET @DiagramSuffix = ' ' + LEFT(CONVERT(VARCHAR(23), GETDATE(), 121), 16)
PRINT 'Create row for new diagram'
BEGIN TRY
PRINT 'Write diagram NAME into new row (and get [diagram_id])'
INSERT INTO sysdiagrams ([name], [principal_id], [version], [definition]) VALUES ('{0}'+@DiagramSuffix, 1, {1}, 0x)
SET @newid = SCOPE_IDENTITY()
END TRY
BEGIN CATCH
PRINT 'XxXxX ' + Error_Message() + ' XxXxX'
PRINT 'XxXxX END Tool_ScriptDiagram2005 - fix the error before running again XxXxX'
RETURN
END CATCH"
$updateStr = "UPDATE sysdiagrams SET [definition] .Write ({0}, null, 0) WHERE diagram_id = @newid `n"
$connString = "server=george-b\Advia;Integrated Security=True;Initial Catalog={0}"
$query = "SELECT [name],[principal_id],[diagram_id],[version],[definition] FROM sysdiagrams"
$conn = new-object "System.Data.SqlClient.SqlConnection" ($connString -f $dbname)
$sqlcmd = new-object "System.Data.SqlClient.SqlCommand" ($query, $conn)
$conn.Open()
$reader = $sqlcmd.ExecuteReader()
$reader | &{
begin
{
$values = new-object "System.Object[]" $reader.FieldCount
$declarationsStr | Add-Content $filename
}
process
{
$_.GetValues($values) | out-null;
$name = $values[0]
$version = $values[2]
"Generating sql script for diagram [{0}]." -f $name | write-host
$insertStr -f $($name, $version) | Add-Content $filename
$i = 0
$s = "0x"
$values[4] | %{if ($i -eq 32) {$i = 0; $updateStr -f $s; $s = "0x"}
else {$i+=1; $s += "{0:x2}" -f $_ }} | Join-string | Add-Content $filename
if ($s -ne "0x") {$updateStr -f $s | Add-Content $filename}
}
}
$conn.Close()
}