Ad Space here

Welcome Guest! To enable all features please Login or Register.

Notification

Icon
Error

Share
Options
Go to last post Go to first unread
Dustin Higgins  
#1 Posted : Thursday, January 23, 2020 10:06:19 PM(UTC)
Dustin Higgins

Rank: Advanced Member

Groups: Registered
Joined: 7/1/2018(UTC)
Posts: 62
_United States

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>

Sponsor
Ad Space here
Rss Feed  Atom Feed
Users browsing this topic
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.