DHB-Scripting Community Forum
»
Technical Advice
»
Scripting
»
PowerShell Create Database Tables from XML Files
Rank: Advanced Member
Groups: Registered
Joined: 7/1/2018(UTC) Posts: 64  Thanks: 1 times Was thanked: 6 time(s) in 6 post(s)
|
PowerShell Create Database Tables from XML Files
This is a work in progress. Eventually I will write a full post on this one. I'm just sharing some ideas right now.
This is meant to be used with Object Oriented Approach to SQL Server Code:function Get-TableObj
{
# Create the object
$Obj = [PSCustomObject]@{
TableName = $null
FieldList = New-Object System.Collections.Generic.List[PSObject]
}
# Add the GetDropTableCmd Method
$Obj | Add-Member -Name "GetDropTableCmd" -MemberType ScriptMethod -Value {
$Sb = New-Object System.Text.StringBuilder
# Add the Cmd
[void]$Sb.AppendLine("DROP TABLE [$($this.TableName)]")
$SqlCmd = Get-SqlCommand -CommandText $Sb.ToString()
# Return the cmd
Return $SqlCmd
}
# Add the GetCreateTableCmd Method
$Obj | Add-Member -Name "GetCreateTableCmd" -MemberType ScriptMethod -Value {
$FieldList = New-Object System.Collections.Generic.List[string]
foreach ($i in $this.FieldList) {
$FieldList.Add("$($i.Field) $($i.DataType)")
}
$Sb = New-Object System.Text.StringBuilder
# Begin the CREATE TABLE Command
[void]$Sb.AppendLine("CREATE TABLE [$($this.TableName)] ($([string]::Join(",",$FieldList)))")
$SqlCmd = Get-SqlCommand -CommandText $Sb.ToString()
# Return the cmd
Return $SqlCmd
}
return $Obj
}
This code just creates the XML that eventually will be read in to build "Table" objects (IE: maintain the tables, fields, and types in an XML file): Code:$date = Get-Date -Format yyyy-MM-dd
$ScriptDir = Split-Path $script:MyInvocation.MyCommand.Path
# Create a list to hold the tables
$TableList = New-Object System.Collections.Generic.List[psobject]
# Create a Table Object
$Obj = Get-TableObj
$Obj.TableName = "AdministratorsGroup"
$Obj.FieldList.Add([PSCustomObject]@{ Field = "Computer" ; DataType = "varchar(max)"})
$Obj.FieldList.Add([PSCustomObject]@{ Field = "Domain" ; DataType = "varchar(max)"})
$Obj.FieldList.Add([PSCustomObject]@{ Field = "Name" ; DataType = "varchar(max)"})
$Obj.FieldList.Add([PSCustomObject]@{ Field = "ObjTimeStamp" ; DataType = "DateTime"})
# Add the object to the list
$TableList.Add($Obj)
# Create an XML doc:
[xml]$Doc = New-Object System.Xml.XmlDocument
# Create and add the declaration
$dec = $Doc.CreateXmlDeclaration("1.0","UTF-8",$null)
[void]$doc.AppendChild($dec)
# Create the root node
$root = $doc.CreateNode("element","Tables",$null)
# Loop through the table list
foreach ($i in $TableList) {
# Create an element for the each Table
$TableNode = $doc.CreateNode("element","Table",$null)
# Set the Table Name
$TableNode.SetAttribute("Name",$($i.TableName))
# Create the Fields Node
$FieldsNode = $doc.CreateNode("element","Fields",$null)
# Loop through the fields
foreach ($j in $i.FieldList) {
# Create a field node
$FieldNode = $doc.CreateNode("element","Field",$null)
# Set the Name and DataType
$FieldNode.SetAttribute("Name",$($j.Field))
$FieldNode.SetAttribute("DataType",$($j.DataType))
[void]$FieldsNode.AppendChild($FieldNode)
}
# Add the Fields Node to the Table Node
[void]$TableNode.AppendChild($FieldsNode)
# Add the Table Node to the Root
[void]$root.AppendChild($TableNode)
}
# Add the Root to the doc
[void]$doc.AppendChild($root)
# Save the XML file
$doc.save("$($ScriptDir)\DatabaseTables.xml")
Here is example XML that the above code creates. The thought is to write code to read these XML files to create "Table" objects that have methods to "Drop" and "Create" tables. Code:<?xml version="1.0" encoding="UTF-8"?>
<Tables>
<Table Name="AdministratorsGroup">
<Fields>
<Field Name="Computer" DataType="varchar(max)" />
<Field Name="Domain" DataType="varchar(max)" />
<Field Name="Name" DataType="varchar(max)" />
<Field Name="ObjTimeStamp" DataType="DateTime" />
</Fields>
</Table>
</Tables>
|
|
|
|
DHB-Scripting Community Forum
»
Technical Advice
»
Scripting
»
PowerShell Create Database Tables from XML Files
Forum Jump
You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.