MDTDatabase

1.1.0

MDTDatabase.psm1

function Clear-MDTArray {

    PARAM
    (
        $id,
        $type,
        $table
    )

    # Build the delete command
    $delCommand = "DELETE FROM $table WHERE ID = $id and Type = '$type'"
        
    # Issue the delete command
    Write-Debug "About to issue command: $delCommand"
    $cmd = New-Object System.Data.SqlClient.SqlCommand($delCommand, $mdtSQLConnection)
    $null = $cmd.ExecuteScalar()

    Write-Debug "Removed all records from $table for Type = $type and ID = $id."
}
function Connect-MDTDatabase {

    [CmdletBinding()]
    PARAM
    (
        [Parameter(Position=1)] $drivePath = "",
        [Parameter()] $sqlServer,
        [Parameter()] $instance = "",
        [Parameter()] $database
    )

    # If $mdtDatabase exists from a previous execution, clear it
    if ($mdtDatabase)
    {
        Clear-Variable -name mdtDatabase
    }

    # If a drive path is specified, use PowerShell to build the connection string.
    # Otherwise, build it from the other parameters
    if ($drivePath -ne "")
    {
        # Get the needed properties to build the connection string
        $mdtProperties = get-itemproperty $drivePath

        $mdtSQLConnectString = "Server=$($mdtProperties.'Database.SQLServer')"
        if ($mdtProperties."Database.Instance" -ne "")
        {
            $mdtSQLConnectString = "$mdtSQLConnectString\$($mdtProperties.'Database.Instance')"
        }
        $mdtSQLConnectString = "$mdtSQLConnectString; Database='$($mdtProperties.'Database.Name')'; Integrated Security=true;"
    }
    else
    {
        $mdtSQLConnectString = "Server=$($sqlServer)"
        if ($instance -ne "")
        {
            $mdtSQLConnectString = "$mdtSQLConnectString\$instance"
        }
        $mdtSQLConnectString = "$mdtSQLConnectString; Database='$database'; Integrated Security=true;"
    }
    
    # Make the connection and save it in a global variable
    Write-Debug "Connecting to: $mdtSQLConnectString"
    $global:mdtSQLConnection = new-object System.Data.SqlClient.SqlConnection
    $global:mdtSQLConnection.ConnectionString = $mdtSQLConnectString
    $global:mdtSQLConnection.Open()
}
function Get-MDTArray {

    PARAM
    (
        $id,
        $type,
        $table,
        $column
    )

    # Build the select command
    $sql = "SELECT $column FROM $table WHERE ID = $id AND Type = '$type' ORDER BY Sequence"
        
    # Issue the select command and return the results
    Write-Debug "About to issue command: $sql"
    $selectAdapter = New-Object System.Data.SqlClient.SqlDataAdapter($sql, $mdtSQLConnection)
    $selectDataset = New-Object System.Data.Dataset
    $null = $selectAdapter.Fill($selectDataset, "$table")
    $selectDataset.Tables[0].Rows 
}
function Set-MDTArray {

    PARAM
    (
        $id,
        $type,
        $table,
        $column,
        $array
    )

    # First clear the existing array
    Clear-MDTArray $id $type $table
    
    # Now insert each row in the array
    $seq = 1
    foreach ($item in $array)
    {
        # Insert the row
        $sql = "INSERT INTO $table (Type, ID, Sequence, $column) VALUES ('$type', $id, $seq, '$item')"
        Write-Debug "About to execute command: $sql"
        $settingsCmd = New-Object System.Data.SqlClient.SqlCommand($sql, $mdtSQLConnection)
        $null = $settingsCmd.ExecuteScalar()

        # Increment the counter
        $seq = $seq + 1
    }
        
    Write-Debug "Added records to $table for Type = $type and ID = $id."
}
# MDTDatabase.MDTComputer
function _ensureMACAddressFormat {
    param(
        [string]$MacAddress
    )

    switch -Regex ($macAddress) {
        # MAC Address without separators
        '([0-9A-F]{2}){6}' {
            $m = $macAddress
            for($i = 10; $i -gt 0; $i = $i -2) {
                $m = $m.insert($i, ':')
            }
            return $m
        }
        
        # MAC Address with '-' as separator:
        '([0-9A-F]{2}-){5}[0-9A-F]{2}' {
            return $macAddress.replace('-', ':')
        }

        # MAC Address with ':' as separator:
        '([0-9A-F]{2}:){5}[0-9A-F]{2}' {
            return $MacAddress
        }

        default {
            throw new Exception("Invalid MacAddress provided: '$MacAddress'.")
        }

    }
}
function Clear-MDTComputerAdministrator {

    [CmdletBinding()]
    PARAM
    (
        [Parameter(ValueFromPipelineByPropertyName=$true, Mandatory=$true)] $id
    )

    Process
    {
        Clear-MDTArray $id 'C' 'Settings_Administrators'
    }
}
function Clear-MDTComputerApplication {

    [CmdletBinding()]
    PARAM
    (
        [Parameter(ValueFromPipelineByPropertyName=$true, Mandatory=$true)] $id
    )

    Process
    {
        Clear-MDTArray $id 'C' 'Settings_Applications'
    }
}
function Clear-MDTComputerPackage {

    [CmdletBinding()]
    PARAM
    (
        [Parameter(ValueFromPipelineByPropertyName=$true, Mandatory=$true)] $id
    )

    Process
    {
        Clear-MDTArray $id 'C' 'Settings_Packages'
    }
}
function Clear-MDTComputerRole {

    [CmdletBinding()]
    PARAM
    (
        [Parameter(ValueFromPipelineByPropertyName=$true, Mandatory=$true)] $id
    )

    Process
    {
        Clear-MDTArray $id 'C' 'Settings_Roles'
    }
}
function Get-MDTComputer {

    [CmdletBinding()]
    PARAM
    (
        [Parameter(ValueFromPipelineByPropertyName=$true)] $id = "",
        [Parameter(ValueFromPipelineByPropertyName=$true)] $assetTag = "",
        [Parameter(ValueFromPipelineByPropertyName=$true)] $macAddress = "",
        [Parameter(ValueFromPipelineByPropertyName=$true)] $serialNumber = "",
        [Parameter(ValueFromPipelineByPropertyName=$true)] $uuid = "",
        [Parameter(ValueFromPipelineByPropertyName=$true)] $description = ""
    )
    
    Process
    {

        if ($macAddress) {
            $macAddress = _ensureMACAddressFormat $macAddress
        }

        $sql = "SELECT ci.Description, ci.AssetTag, ci.UUID, ci.SerialNumber, ci.MacAddress, s.* FROM dbo.ComputerIdentity AS ci INNER JOIN dbo.Settings AS S ON s.id = ci.id WHERE s.Type = 'C'"

        # Build a select statement based on what parameters were specified
        if ($id -eq "" -and $assetTag -eq "" -and $macAddress -eq "" -and $serialNumber -eq "" -and $uuid -eq "" -and $description -eq "")
        {
            $sql = $sql
        }
        elseif ($id -ne "")
        {
            $sql = "$sql AND ci.ID = $id"
        }
        else
        {
            # Specified the initial command
            $sql = "$sql AND"
        
            # Add the appropriate where clauses
            if ($assetTag -ne "")
            {
                $sql = "$sql AssetTag='$assetTag' AND"
            }
        
            if ($macAddress -ne "")
            {
                $sql = "$sql MacAddress='$macAddress' AND"
            }

            if ($serialNumber -ne "")
            {
                $sql = "$sql SerialNumber='$serialNumber' AND"
            }

            if ($uuid -ne "")
            {
                $sql = "$sql UUID='$uuid' AND"
            }

            if ($description -ne "")
            {
                if ($description.Contains('*')) {
                    $t = $description.Replace('*','%')
                    $sql = "$sql Description LIKE '$t' AND"
                } else {
                    $sql = "$sql Description='$description' AND"
                }
            }
    
            # Chop off the last " AND"
            $sql = $sql.Substring(0, $sql.Length - 4)
        }
    
        $selectAdapter = New-Object System.Data.SqlClient.SqlDataAdapter($sql, $mdtSQLConnection)
        $selectDataset = New-Object System.Data.Dataset
        $null = $selectAdapter.Fill($selectDataset, "Settings")
        $selectDataset.Tables[0].Rows
    }
}
function Get-MDTComputerAdministrator {

    [CmdletBinding()]
    PARAM
    (
        [Parameter(ValueFromPipelineByPropertyName=$true, Mandatory=$true)] $id
    )

    Process
    {
        Get-MDTArray $id 'C' 'Settings_Administrators' 'Administrators'
    }
}
function Get-MDTComputerApplication {

    [CmdletBinding()]
    PARAM
    (
        [Parameter(ValueFromPipelineByPropertyName=$true, Mandatory=$true)] $id
    )

    Process
    {
        Get-MDTArray $id 'C' 'Settings_Applications' 'Applications'
    }
}
function Get-MDTComputerIdentity {

    [CmdletBinding()]
    PARAM
    (
        [Parameter(ValueFromPipelineByPropertyName=$true)] $id = "",
        [Parameter(ValueFromPipelineByPropertyName=$true)] $assetTag = "",
        [Parameter(ValueFromPipelineByPropertyName=$true)] $macAddress = "",
        [Parameter(ValueFromPipelineByPropertyName=$true)] $serialNumber = "",
        [Parameter(ValueFromPipelineByPropertyName=$true)] $uuid = "",
        [Parameter(ValueFromPipelineByPropertyName=$true)] $description = ""
    )
    
    Process
    {

        if ($macAddress) {
            $macAddress = _ensureMACAddressFormat $macAddress
        }

        # Build a select statement based on what parameters were specified
        if ($id -eq "" -and $assetTag -eq "" -and $macAddress -eq "" -and $serialNumber -eq "" -and $uuid -eq "" -and $description -eq "")
        {
            $sql = "SELECT * FROM ComputerIdentity"
        }
        elseif ($id -ne "")
        {
            $sql = "SELECT * FROM ComputerIdentity WHERE ID = $id"
        }
        else
        {
            # Specified the initial command
            $sql = "SELECT * FROM ComputerIdentity WHERE "
        
            # Add the appropriate where clauses
            if ($assetTag -ne "")
            {
                $sql = "$sql AssetTag='$assetTag' AND"
            }
        
            if ($macAddress -ne "")
            {
                $sql = "$sql MacAddress='$macAddress' AND"
            }

            if ($serialNumber -ne "")
            {
                $sql = "$sql SerialNumber='$serialNumber' AND"
            }

            if ($uuid -ne "")
            {
                $sql = "$sql UUID='$uuid' AND"
            }

            if ($description -ne "")
            {
                if ($description.Contains('*')) {
                    $t = $description.Replace('*','%')
                    $sql = "$sql Description LIKE '$t' AND"
                } else {
                    $sql = "$sql Description='$description' AND"
                }
            }
    
            # Chop off the last " AND"
            $sql = $sql.Substring(0, $sql.Length - 4)
        }
    
        $selectAdapter = New-Object System.Data.SqlClient.SqlDataAdapter($sql, $mdtSQLConnection)
        $selectDataset = New-Object System.Data.Dataset
        $null = $selectAdapter.Fill($selectDataset, "ComputerIdentity")
        $selectDataset.Tables[0].Rows
    }
}
function Get-MDTComputerPackage {

    [CmdletBinding()]
    PARAM
    (
        [Parameter(ValueFromPipelineByPropertyName=$true, Mandatory=$true)] $id
    )

    Process
    {
        Get-MDTArray $id 'C' 'Settings_Packages' 'Packages'
    }
}
function Get-MDTComputerRole {

    [CmdletBinding()]
    PARAM
    (
        [Parameter(ValueFromPipelineByPropertyName=$true, Mandatory=$true)] $id
    )

    Process
    {
        Get-MDTArray $id 'C' 'Settings_Roles' 'Role'
    }
}
function New-MDTComputer {

    [CmdletBinding()]
    PARAM
    (
        [Parameter(ValueFromPipelineByPropertyName=$true)] $assetTag,
        [ValidatePattern('^(([0-9A-F]{2}){5}|([0-9A-F]{2}-){5}|([0-9A-F]{2}:){5})[0-9A-F]{2}$')]
        [Parameter(ValueFromPipelineByPropertyName=$true)] $macAddress,
        [Parameter(ValueFromPipelineByPropertyName=$true)] $serialNumber,
        [Parameter(ValueFromPipelineByPropertyName=$true)] $uuid,
        [Parameter(ValueFromPipelineByPropertyName=$true)] $description,
        [Parameter(ValueFromPipelineByPropertyName=$true, Mandatory=$true)] $settings
    )

    Process
    {
        if ($macAddress) {
            $macAddress = _ensureMACAddressFormat $macAddress
        }
        
        # Insert a new computer row and get the identity result
        $sql = "INSERT INTO ComputerIdentity (AssetTag, SerialNumber, MacAddress, UUID, Description) VALUES ('$assetTag', '$serialNumber', '$macAddress', '$uuid', '$description') SELECT @@IDENTITY"
        Write-Debug "About to execute command: $sql"
        $identityCmd = New-Object System.Data.SqlClient.SqlCommand($sql, $mdtSQLConnection)
        $identity = $identityCmd.ExecuteScalar()
        Write-Debug "Added computer identity record"
    
        # Insert the settings row, adding the values as specified in the hash table
        $settingsColumns = $settings.Keys -join ","
        $settingsValues = $settings.Values -join "','"
        $sql = "INSERT INTO Settings (Type, ID, $settingsColumns) VALUES ('C', $identity, '$settingsValues')"
        Write-Debug "About to execute command: $sql"
        $settingsCmd = New-Object System.Data.SqlClient.SqlCommand($sql, $mdtSQLConnection)
        $null = $settingsCmd.ExecuteScalar()
            
        Write-Debug "Added settings for the specified computer"
        
        # Write the new record back to the pipeline
        Get-MDTComputer -ID $identity
    }
}
function Remove-MDTComputer {

    [CmdletBinding()]
    PARAM
    (
        [Parameter(ValueFromPipelineByPropertyName=$true, Mandatory=$true)] $id
    )

    Process
    {
        # Build the delete command
        $delCommand = "DELETE FROM ComputerIdentity WHERE ID = $id"
        
        # Issue the delete command
        Write-Debug "About to issue command: $delCommand"
        $cmd = New-Object System.Data.SqlClient.SqlCommand($delCommand, $mdtSQLConnection)
        $null = $cmd.ExecuteScalar()

        Write-Debug "Removed the computer with ID = $id."
    }
}
function Set-MDTComputer {

    [CmdletBinding()]
    PARAM
    (
        [Parameter(ValueFromPipelineByPropertyName=$true, Mandatory=$true)] $id,
        [Parameter(Mandatory=$true)] $settings
    )
    
    Process
    {
        if ($settings.ContainsKey('MacAddress')) {
            $settings.macAddress = _ensureMACAddressFormat $settings.macAddress
        }

        # Add each each hash table entry to the update statement
        $sql = "UPDATE Settings SET"
        foreach ($setting in $settings.GetEnumerator())
        {
            $sql = "$sql $($setting.Key) = '$($setting.Value)', "
        }
        
        # Chop off the trailing ", "
        $sql = $sql.Substring(0, $sql.Length - 2)

        # Add the where clause
        $sql = "$sql WHERE ID = $id AND Type = 'C'"
        
        # Execute the command
        Write-Debug "About to execute command: $sql"        
        $settingsCmd = New-Object System.Data.SqlClient.SqlCommand($sql, $mdtSQLConnection)
        $null = $settingsCmd.ExecuteScalar()
            
        Write-Debug "Added settings for the specified computer"
        
        # Write the updated record back to the pipeline
        Get-MDTComputer -ID $id
    }
}
function Set-MDTComputerAdministrator {

    [CmdletBinding()]
    PARAM
    (
        [Parameter(ValueFromPipelineByPropertyName=$true, Mandatory=$true)] $id,
        [Parameter(ValueFromPipelineByPropertyName=$true, Mandatory=$true)] $administrators
    )

    Process
    {
        Set-MDTArray $id 'C' 'Settings_Administrators' 'Administrators' $administrators
    }
}
function Set-MDTComputerApplication {

    [CmdletBinding()]
    PARAM
    (
        [Parameter(ValueFromPipelineByPropertyName=$true, Mandatory=$true)] $id,
        [Parameter(ValueFromPipelineByPropertyName=$true, Mandatory=$true)] $applications
    )

    Process
    {
        Set-MDTArray $id 'C' 'Settings_Applications' 'Applications' $applications
    }
}
function Set-MDTComputerIdentity {
    [CmdletBinding()]
        param
        (
            [Parameter(ValueFromPipelineByPropertyName=$true, Mandatory=$true)] $id,
            [Parameter(Mandatory=$true)] [Hashtable]$settings
        )
        
        Process
        {

            if ($settings.ContainsKey('MacAddress')) {
                $settings.macAddress = _ensureMACAddressFormat $settings.macAddress
            } 

            # Add each each hash table entry to the update statement
            $sql = "UPDATE ComputerIdentity SET"
            foreach ($setting in $settings.GetEnumerator())
            {
                $sql = "$sql $($setting.Key) = '$($setting.Value)', "
            }
            
            # Chop off the trailing ", "
            $sql = $sql.Substring(0, $sql.Length - 2)
    
            # Add the where clause
            $sql = "$sql WHERE ID = $id"
            
            # Execute the command
            Write-Debug "About to execute command: $sql"        
            $settingsCmd = New-Object System.Data.SqlClient.SqlCommand($sql, $mdtSQLConnection)
            $null = $settingsCmd.ExecuteScalar()
                
            Write-Debug "Update settings for the specified computer"
            
            # Write the updated record back to the pipeline
            Get-MDTComputer -ID $id
        }
    }
function Set-MDTComputerPackage {

    [CmdletBinding()]
    PARAM
    (
        [Parameter(ValueFromPipelineByPropertyName=$true, Mandatory=$true)] $id,
        [Parameter(ValueFromPipelineByPropertyName=$true, Mandatory=$true)] $packages
    )

    Process
    {
        Set-MDTArray $id 'C' 'Settings_Packages' 'Packages' $packages
    }
}
function Set-MDTComputerRole {

    [CmdletBinding()]
    PARAM
    (
        [Parameter(ValueFromPipelineByPropertyName=$true, Mandatory=$true)] $id,
        [Parameter(ValueFromPipelineByPropertyName=$true, Mandatory=$true)] $roles
    )

    Process
    {
        Set-MDTArray $id 'C' 'Settings_Roles' 'Role' $roles
    }
}
# MDTDatabase.MDTLocation
function Clear-MDTLocationAdministrator {

    [CmdletBinding()]
    PARAM
    (
        [Parameter(ValueFromPipelineByPropertyName=$true, Mandatory=$true)] $id
    )

    Process
    {
        Clear-MDTArray $id 'L' 'Settings_Administrators'
    }
}
function Clear-MDTLocationApplication {

    [CmdletBinding()]
    PARAM
    (
        [Parameter(ValueFromPipelineByPropertyName=$true, Mandatory=$true)] $id
    )

    Process
    {
        Clear-MDTArray $id 'L' 'Settings_Applications'
    }
}
function Clear-MDTLocationPackage {

    [CmdletBinding()]
    PARAM
    (
        [Parameter(ValueFromPipelineByPropertyName=$true, Mandatory=$true)] $id
    )

    Process
    {
        Clear-MDTArray $id 'L' 'Settings_Packages'
    }
}
function Get-MDTLocation {

    [CmdletBinding()]
    PARAM
    (
        [Parameter(ValueFromPipelineByPropertyName=$true)] $id = "",
        [Parameter(ValueFromPipelineByPropertyName=$true)] $name = "",
        [Parameter()][switch] $detail = $false
    )
    
    Process
    {
        # Build a select statement based on what parameters were specified
        if ($id -eq "" -and $name -eq "")
        {
            if ($detail)
            {
                $sql = "SELECT * FROM LocationSettings"
            }
            else
            {
                $sql = "SELECT DISTINCT ID, Location FROM LocationSettings"
            }
        }
        elseif ($id -ne "")
        {
            if ($detail)
            {
                $sql = "SELECT * FROM LocationSettings WHERE ID = $id"
            }
            else
            {
                $sql = "SELECT DISTINCT ID, Location FROM LocationSettings WHERE ID = $id"
            }
        }
        else
        {
            if ($detail)
            {
                $sql = "SELECT * FROM LocationSettings WHERE Location = '$name'"
            }
            else
            {
                $sql = "SELECT DISTINCT ID, Location FROM LocationSettings WHERE Location = '$name'"
            }
        }
        
        # Execute the statement and return the results
        $selectAdapter = New-Object System.Data.SqlClient.SqlDataAdapter($sql, $mdtSQLConnection)
        $selectDataset = New-Object System.Data.Dataset
        $null = $selectAdapter.Fill($selectDataset, "LocationSettings")
        $selectDataset.Tables[0].Rows
    }
}
function Get-MDTLocationAdministrator {

    [CmdletBinding()]
    PARAM
    (
        [Parameter(ValueFromPipelineByPropertyName=$true, Mandatory=$true)] $id
    )

    Process
    {
        Get-MDTArray $id 'L' 'Settings_Administrators' 'Administrators'
    }
}
function Get-MDTLocationApplication {

    [CmdletBinding()]
    PARAM
    (
        [Parameter(ValueFromPipelineByPropertyName=$true, Mandatory=$true)] $id
    )

    Process
    {
        Get-MDTArray $id 'L' 'Settings_Applications' 'Applications'
    }
}
function Get-MDTLocationPackage {

    [CmdletBinding()]
    PARAM
    (
        [Parameter(ValueFromPipelineByPropertyName=$true, Mandatory=$true)] $id
    )

    Process
    {
        Get-MDTArray $id 'L' 'Settings_Packages' 'Packages'
    }
}
function Get-MDTLocationRole {

    [CmdletBinding()]
    PARAM
    (
        [Parameter(ValueFromPipelineByPropertyName=$true, Mandatory=$true)] $id
    )

    Process
    {
        Get-MDTArray $id 'L' 'Settings_Roles' 'Role'
    }
}
function New-MDTLocation {

    [CmdletBinding()]
    PARAM
    (
        [Parameter(ValueFromPipelineByPropertyName=$true)] $name,
        [Parameter(ValueFromPipelineByPropertyName=$true)] $gateways,
        [Parameter(ValueFromPipelineByPropertyName=$true, Mandatory=$true)] $settings
    )

    Process
    {
        # Insert a new role row and get the identity result
        $sql = "INSERT INTO LocationIdentity (Location) VALUES ('$name') SELECT @@IDENTITY"
        Write-Debug "About to execute command: $sql"
        $identityCmd = New-Object System.Data.SqlClient.SqlCommand($sql, $mdtSQLConnection)
        $identity = $identityCmd.ExecuteScalar()
        Write-Debug "Added location identity record"
    
        # Set the gateways
        $null = Set-MDTLocation -id $identity -gateways $gateways
        
        # Insert the settings row, adding the values as specified in the hash table
        $settingsColumns = $settings.Keys -join ","
        $settingsValues = $settings.Values -join "','"
        $sql = "INSERT INTO Settings (Type, ID, $settingsColumns) VALUES ('L', $identity, '$settingsValues')"
        Write-Debug "About to execute command: $sql"
        $settingsCmd = New-Object System.Data.SqlClient.SqlCommand($sql, $mdtSQLConnection)
        $null = $settingsCmd.ExecuteScalar()
            
        Write-Debug "Added settings for the specified location"
        
        # Write the new record back to the pipeline
        Get-MDTLocation -ID $identity
    }
}
function Remove-MDTLocation {

    [CmdletBinding()]
    PARAM
    (
        [Parameter(ValueFromPipelineByPropertyName=$true, Mandatory=$true)] $id
    )

    Process
    {
        # Build the delete command
        $delCommand = "DELETE FROM LocationIdentity WHERE ID = $id"
        
        # Issue the delete command
        Write-Debug "About to issue command: $delCommand"
        $cmd = New-Object System.Data.SqlClient.SqlCommand($delCommand, $mdtSQLConnection)
        $null = $cmd.ExecuteScalar()

        Write-Debug "Removed the location with ID = $id."
    }
}
function Set-MDTLocation {

    [CmdletBinding()]
    PARAM
    (
        [Parameter(ValueFromPipelineByPropertyName=$true, Mandatory=$true)] $id,
        [Parameter(ValueFromPipelineByPropertyName=$true)] $gateways = $null,
        [Parameter()] $settings = $null
    )
    
    Process
    {
        # If there are some new settings save them
        if ($settings -ne $null)
        {
            # Add each each hash table entry to the update statement
            $sql = "UPDATE Settings SET"
            foreach ($setting in $settings.GetEnumerator())
            {
                $sql = "$sql $($setting.Key) = '$($setting.Value)', "
            }
        
            # Chop off the trailing ", "
            $sql = $sql.Substring(0, $sql.Length - 2)

            # Add the where clause
            $sql = "$sql WHERE ID = $id AND Type = 'L'"
        
            # Execute the command
            Write-Debug "About to execute command: $sql"        
            $settingsCmd = New-Object System.Data.SqlClient.SqlCommand($sql, $mdtSQLConnection)
            $null = $settingsCmd.ExecuteScalar()
            
            Write-Debug "Added settings for the specified location"
        }
        
        # If there are some gateways save them
        if ($gateways -ne $null)
        {
            # Build the delete command to remove the existing gateways
            $delCommand = "DELETE FROM LocationIdentity_DefaultGateway WHERE ID = $id"
        
            # Issue the delete command
            Write-Debug "About to issue command: $delCommand"
            $cmd = New-Object System.Data.SqlClient.SqlCommand($delCommand, $mdtSQLConnection)
            $null = $cmd.ExecuteScalar()
            
            # Now insert the specified values
            foreach ($gateway in $gateways)
            {
                # Insert the row
                $sql = "INSERT INTO LocationIdentity_DefaultGateway (ID, DefaultGateway) VALUES ($id, '$gateway')"
                Write-Debug "About to execute command: $sql"
                $settingsCmd = New-Object System.Data.SqlClient.SqlCommand($sql, $mdtSQLConnection)
                $null = $settingsCmd.ExecuteScalar()

            }
            Write-Debug "Set the default gateways for the location with ID = $id."    
        }
        
        # Write the updated record back to the pipeline
        Get-MDTLocation -ID $id
    }
}
function Set-MDTLocationAdministrator {

    [CmdletBinding()]
    PARAM
    (
        [Parameter(ValueFromPipelineByPropertyName=$true, Mandatory=$true)] $id,
        [Parameter(ValueFromPipelineByPropertyName=$true, Mandatory=$true)] $administrators
    )

    Process
    {
        Set-MDTArray $id 'L' 'Settings_Administrators' 'Administrators' $administrators
    }
}
function Set-MDTLocationApplication {

    [CmdletBinding()]
    PARAM
    (
        [Parameter(ValueFromPipelineByPropertyName=$true, Mandatory=$true)] $id,
        [Parameter(ValueFromPipelineByPropertyName=$true, Mandatory=$true)] $applications
    )

    Process
    {
        Set-MDTArray $id 'L' 'Settings_Applications' 'Applications' $applications
    }
}
function Set-MDTLocationPackage {

    [CmdletBinding()]
    PARAM
    (
        [Parameter(ValueFromPipelineByPropertyName=$true, Mandatory=$true)] $id,
        [Parameter(ValueFromPipelineByPropertyName=$true, Mandatory=$true)] $packages
    )

    Process
    {
        Set-MDTArray $id 'L' 'Settings_Packages' 'Packages' $packages
    }
}
function Set-MDTLocationRole {

    [CmdletBinding()]
    PARAM
    (
        [Parameter(ValueFromPipelineByPropertyName=$true, Mandatory=$true)] $id,
        [Parameter(ValueFromPipelineByPropertyName=$true, Mandatory=$true)] $roles
    )

    Process
    {
        Set-MDTArray $id 'L' 'Settings_Roles' 'Role' $roles
    }
}
# MDTDatabase.MDTMakeModel
function Clear-MDTMakeModelAdministrator {

    [CmdletBinding()]
    PARAM
    (
        [Parameter(ValueFromPipelineByPropertyName=$true, Mandatory=$true)] $id
    )

    Process
    {
        Clear-MDTArray $id 'M' 'Settings_Administrators'
    }
}
function Clear-MDTMakeModelPackage {

    [CmdletBinding()]
    PARAM
    (
        [Parameter(ValueFromPipelineByPropertyName=$true, Mandatory=$true)] $id
    )

    Process
    {
        Clear-MDTArray $id 'M' 'Settings_Packages'
    }
}
function Clear-MDTMakeModelRole {

    [CmdletBinding()]
    PARAM
    (
        [Parameter(ValueFromPipelineByPropertyName=$true, Mandatory=$true)] $id
    )

    Process
    {
        Clear-MDTArray $id 'M' 'Settings_Roles'
    }
}
function Get-MDTMakeModel {

    [CmdletBinding()]
    PARAM
    (
        [Parameter(ValueFromPipelineByPropertyName=$true)] $id = "",
        [Parameter(ValueFromPipelineByPropertyName=$true)] $make = "",
        [Parameter(ValueFromPipelineByPropertyName=$true)] $model = ""
    )
    
    Process
    {
        # Build a select statement based on what parameters were specified
        if ($id -eq "" -and $make -eq "" -and $model -eq "")
        {
            $sql = "SELECT * FROM MakeModelSettings"
        }
        elseif ($id -ne "")
        {
            $sql = "SELECT * FROM MakeModelSettings WHERE ID = $id"
        }
        elseif ($make -ne "" -and $model -ne "")
        {
            $sql = "SELECT * FROM MakeModelSettings WHERE Make = '$make' AND Model = '$model'"
        }
        elseif ($make -ne "")
        {
            $sql = "SELECT * FROM MakeModelSettings WHERE Make = '$make'"
        }
        else
        {
            $sql = "SELECT * FROM MakeModelSettings WHERE Model = '$model'"
        }
        
        # Execute the statement and return the results
        $selectAdapter = New-Object System.Data.SqlClient.SqlDataAdapter($sql, $mdtSQLConnection)
        $selectDataset = New-Object System.Data.Dataset
        $null = $selectAdapter.Fill($selectDataset, "MakeModelSettings")
        $selectDataset.Tables[0].Rows
    }
}
function Get-MDTMakeModelAdministrator {

    [CmdletBinding()]
    PARAM
    (
        [Parameter(ValueFromPipelineByPropertyName=$true, Mandatory=$true)] $id
    )

    Process
    {
        Get-MDTArray $id 'M' 'Settings_Administrators' 'Administrators'
    }
}
function Get-MDTMakeModelApplication {

    [CmdletBinding()]
    PARAM
    (
        [Parameter(ValueFromPipelineByPropertyName=$true, Mandatory=$true)] $id
    )

    Process
    {
        Get-MDTArray $id 'M' 'Settings_Applications' 'Applications'
    }
}
function Get-MDTMakeModelPackage {

    [CmdletBinding()]
    PARAM
    (
        [Parameter(ValueFromPipelineByPropertyName=$true, Mandatory=$true)] $id
    )

    Process
    {
        Get-MDTArray $id 'M' 'Settings_Packages' 'Packages'
    }
}
function Get-MDTMakeModelRole {

    [CmdletBinding()]
    PARAM
    (
        [Parameter(ValueFromPipelineByPropertyName=$true, Mandatory=$true)] $id
    )

    Process
    {
        Get-MDTArray $id 'M' 'Settings_Roles' 'Role'
    }
}
function New-MDTMakeModel {

    [CmdletBinding()]
    PARAM
    (
        [Parameter(ValueFromPipelineByPropertyName=$true)] $make,
        [Parameter(ValueFromPipelineByPropertyName=$true)] $model,
        [Parameter(ValueFromPipelineByPropertyName=$true, Mandatory=$true)] $settings
    )

    Process
    {
        # Insert a new role row and get the identity result
        $sql = "INSERT INTO MakeModelIdentity (Make, Model) VALUES ('$make', '$model') SELECT @@IDENTITY"
        Write-Debug "About to execute command: $sql"
        $identityCmd = New-Object System.Data.SqlClient.SqlCommand($sql, $mdtSQLConnection)
        $identity = $identityCmd.ExecuteScalar()
        Write-Debug "Added make model identity record"
    
        # Insert the settings row, adding the values as specified in the hash table
        $settingsColumns = $settings.Keys -join ","
        $settingsValues = $settings.Values -join "','"
        $sql = "INSERT INTO Settings (Type, ID, $settingsColumns) VALUES ('M', $identity, '$settingsValues')"
        Write-Debug "About to execute command: $sql"
        $settingsCmd = New-Object System.Data.SqlClient.SqlCommand($sql, $mdtSQLConnection)
        $null = $settingsCmd.ExecuteScalar()
            
        Write-Debug "Added settings for the specified make model"
        
        # Write the new record back to the pipeline
        Get-MDTMakeModel -ID $identity
    }
}
function Remove-MDTMakeModel {

    [CmdletBinding()]
    PARAM
    (
        [Parameter(ValueFromPipelineByPropertyName=$true, Mandatory=$true)] $id
    )

    Process
    {
        # Build the delete command
        $delCommand = "DELETE FROM MakeModelIdentity WHERE ID = $id"
        
        # Issue the delete command
        Write-Debug "About to issue command: $delCommand"
        $cmd = New-Object System.Data.SqlClient.SqlCommand($delCommand, $mdtSQLConnection)
        $null = $cmd.ExecuteScalar()

        Write-Debug "Removed the make model with ID = $id."
    }
}
function Set-MDTMakeModel {

    [CmdletBinding()]
    PARAM
    (
        [Parameter(ValueFromPipelineByPropertyName=$true, Mandatory=$true)] $id,
        [Parameter(Mandatory=$true)] $settings
    )
    
    Process
    {
        # Add each each hash table entry to the update statement
        $sql = "UPDATE Settings SET"
        foreach ($setting in $settings.GetEnumerator())
        {
            $sql = "$sql $($setting.Key) = '$($setting.Value)', "
        }
        
        # Chop off the trailing ", "
        $sql = $sql.Substring(0, $sql.Length - 2)

        # Add the where clause
        $sql = "$sql WHERE ID = $id AND Type = 'M'"
        
        # Execute the command
        Write-Debug "About to execute command: $sql"        
        $settingsCmd = New-Object System.Data.SqlClient.SqlCommand($sql, $mdtSQLConnection)
        $null = $settingsCmd.ExecuteScalar()
            
        Write-Debug "Added settings for the specified make model"
        
        # Write the updated record back to the pipeline
        Get-MDTMakeModel -ID $id
    }
}
function Set-MDTMakeModelAdministrator {

    [CmdletBinding()]
    PARAM
    (
        [Parameter(ValueFromPipelineByPropertyName=$true, Mandatory=$true)] $id,
        [Parameter(ValueFromPipelineByPropertyName=$true, Mandatory=$true)] $administrators
    )

    Process
    {
        Set-MDTArray $id 'M' 'Settings_Administrators' 'Administrators' $administrators
    }
}
function Set-MDTMakeModelApplication {

    [CmdletBinding()]
    PARAM
    (
        [Parameter(ValueFromPipelineByPropertyName=$true, Mandatory=$true)] $id,
        [Parameter(ValueFromPipelineByPropertyName=$true, Mandatory=$true)] $applications
    )

    Process
    {
        Set-MDTArray $id 'M' 'Settings_Applications' 'Applications' $applications
    }
}
function Set-MDTMakeModelPackage {

    [CmdletBinding()]
    PARAM
    (
        [Parameter(ValueFromPipelineByPropertyName=$true, Mandatory=$true)] $id,
        [Parameter(ValueFromPipelineByPropertyName=$true, Mandatory=$true)] $packages
    )

    Process
    {
        Set-MDTArray $id 'M' 'Settings_Packages' 'Packages' $packages
    }
}
function Set-MDTMakeModelRole {

    [CmdletBinding()]
    PARAM
    (
        [Parameter(ValueFromPipelineByPropertyName=$true, Mandatory=$true)] $id,
        [Parameter(ValueFromPipelineByPropertyName=$true, Mandatory=$true)] $roles
    )

    Process
    {
        Set-MDTArray $id 'M' 'Settings_Roles' 'Role' $roles
    }
}
# MDTDatabase.MDTPackageMapping
function Get-MDTPackageMapping {

    [CmdletBinding()]
    PARAM
    (
        [Parameter(ValueFromPipelineByPropertyName=$true)] $ARPName = "",
        [Parameter(ValueFromPipelineByPropertyName=$true)] $package = ""
    )
    
    Process
    {
        # Build a select statement based on what parameters were specified
        if ($ARPName -eq "" -and $package -eq "")
        {
            $sql = "SELECT * FROM PackageMapping"
        }
        elseif ($ARPName -ne "" -and $package -ne "")
        {
            $sql = "SELECT * FROM PackageMapping WHERE ARPName = '$ARPName' AND Packages = '$package'"
        }
        elseif ($ARPName -ne "")
        {
            $sql = "SELECT * FROM PackageMapping WHERE ARPName = '$ARPName'"
        }
        else
        {
            $sql = "SELECT * FROM PackageMapping WHERE Packages = '$package'"
        }
        
        # Execute the statement and return the results
        $selectAdapter = New-Object System.Data.SqlClient.SqlDataAdapter($sql, $mdtSQLConnection)
        $selectDataset = New-Object System.Data.Dataset
        $null = $selectAdapter.Fill($selectDataset, "PackageMapping")
        $selectDataset.Tables[0].Rows
    }
}
function New-MDTPackageMapping {

    [CmdletBinding()]
    PARAM
    (
        [Parameter(ValueFromPipelineByPropertyName=$true, Mandatory=$true)] $ARPName,
        [Parameter(ValueFromPipelineByPropertyName=$true, Mandatory=$true)] $package
    )

    Process
    {
        # Insert a new row
        $sql = "INSERT INTO PackageMapping (ARPName, Packages) VALUES ('$ARPName','$package')"
        Write-Debug "About to execute command: $sql"
        $identityCmd = New-Object System.Data.SqlClient.SqlCommand($sql, $mdtSQLConnection)
        $null = $identityCmd.ExecuteScalar()
        Write-Debug "Added package mapping record for $ARPName"
    
        # Write the new record back to the pipeline
        Get-MDTPackageMapping -ARPName $ARPName
    }
}
function Remove-MDTPackageMapping {

    [CmdletBinding()]
    PARAM
    (
        [Parameter(ValueFromPipelineByPropertyName=$true)] $ARPName = "",
        [Parameter(ValueFromPipelineByPropertyName=$true)] $package = ""
    )
    
    Process
    {
        # Build a delete statement based on what parameters were specified
        if ($ARPName -eq "" -and $package -eq "")
        {
            # Dangerous, delete them all
            $sql = "DELETE FROM PackageMapping"
        }
        elseif ($ARPName -ne "" -and $package -ne "")
        {
            $sql = "DELETE FROM PackageMapping WHERE ARPName = '$ARPName' AND Packages = '$package'"
        }
        elseif ($ARPName -ne "")
        {
            $sql = "DELETE FROM PackageMapping WHERE ARPName = '$ARPName'"
        }
        else
        {
            $sql = "DELETE FROM PackageMapping WHERE Packages = '$package'"
        }
        
        # Execute the delete command
        Write-Debug "About to execute command: $sql"
        $settingsCmd = New-Object System.Data.SqlClient.SqlCommand($sql, $mdtSQLConnection)
        $null = $settingsCmd.ExecuteScalar()
        Write-Debug "Removed package mapping records matching the specified parameters."    
    }
}
function Set-MDTPackageMapping {

    [CmdletBinding()]
    PARAM
    (
        [Parameter(ValueFromPipelineByPropertyName=$true, Mandatory=$true)] $ARPName,
        [Parameter(ValueFromPipelineByPropertyName=$true, Mandatory=$true)] $package = $null
    )
    
    Process
    {
        # Update the row
        $sql = "UPDATE PackageMapping SET Packages = '$package' WHERE ARPName = '$ARPName'"
        Write-Debug "About to execute command: $sql"
        $settingsCmd = New-Object System.Data.SqlClient.SqlCommand($sql, $mdtSQLConnection)
        $null = $settingsCmd.ExecuteScalar()
        Write-Debug "Updated the package mapping record for $ARPName to install package $package."    
        
        # Write the updated record back to the pipeline
        Get-MDTPackageMapping -ARPName $ARPName
    }
}
# MDTDatabase.MDTRole
function Clear-MDTRoleAdministrator {

    [CmdletBinding()]
    PARAM
    (
        [Parameter(ValueFromPipelineByPropertyName=$true, Mandatory=$true)] $id
    )

    Process
    {
        Clear-MDTArray $id 'R' 'Settings_Administrators'
    }
}
function Clear-MDTRoleApplication {

    [CmdletBinding()]
    PARAM
    (
        [Parameter(ValueFromPipelineByPropertyName=$true, Mandatory=$true)] $id
    )

    Process
    {
        Clear-MDTArray $id 'R' 'Settings_Applications'
    }
}
function Clear-MDTRolePackage {

    [CmdletBinding()]
    PARAM
    (
        [Parameter(ValueFromPipelineByPropertyName=$true, Mandatory=$true)] $id
    )

    Process
    {
        Clear-MDTArray $id 'R' 'Settings_Packages'
    }
}
function Clear-MDTRoleRole {

    [CmdletBinding()]
    PARAM
    (
        [Parameter(ValueFromPipelineByPropertyName=$true, Mandatory=$true)] $id
    )

    Process
    {
        Clear-MDTArray $id 'R' 'Settings_Roles'
    }
}
function Get-MDTRole {

    [CmdletBinding()]
    PARAM
    (
        [Parameter(ValueFromPipelineByPropertyName=$true)] $id = "",
        [Parameter(ValueFromPipelineByPropertyName=$true)] $name = ""
    )
    
    Process
    {
        # Build a select statement based on what parameters were specified
        if ($id -eq "" -and $name -eq "")
        {
            $sql = "SELECT * FROM RoleSettings"
        }
        elseif ($id -ne "")
        {
            $sql = "SELECT * FROM RoleSettings WHERE ID = $id"
        }
        else
        {
            $sql = "SELECT * FROM RoleSettings WHERE Role = '$name'"
        }
        
        # Execute the statement and return the results
        $selectAdapter = New-Object System.Data.SqlClient.SqlDataAdapter($sql, $mdtSQLConnection)
        $selectDataset = New-Object System.Data.Dataset
        $null = $selectAdapter.Fill($selectDataset, "RoleSettings")
        $selectDataset.Tables[0].Rows
    }
}
function Get-MDTRoleAdministrator {

    [CmdletBinding()]
    PARAM
    (
        [Parameter(ValueFromPipelineByPropertyName=$true, Mandatory=$true)] $id
    )

    Process
    {
        Get-MDTArray $id 'R' 'Settings_Administrators' 'Administrators'
    }
}
function Get-MDTRoleApplication {

    [CmdletBinding()]
    PARAM
    (
        [Parameter(ValueFromPipelineByPropertyName=$true, Mandatory=$true)] $id
    )

    Process
    {
        Get-MDTArray $id 'R' 'Settings_Applications' 'Applications'
    }
}
function Get-MDTRolePackage {

    [CmdletBinding()]
    PARAM
    (
        [Parameter(ValueFromPipelineByPropertyName=$true, Mandatory=$true)] $id
    )

    Process
    {
        Get-MDTArray $id 'R' 'Settings_Packages' 'Packages'
    }
}
function Get-MDTRoleRole {

    [CmdletBinding()]
    PARAM
    (
        [Parameter(ValueFromPipelineByPropertyName=$true, Mandatory=$true)] $id
    )

    Process
    {
        Get-MDTArray $id 'R' 'Settings_Roles' 'Role'
    }
}
function New-MDTRole {

    [CmdletBinding()]
    PARAM
    (
        [Parameter(ValueFromPipelineByPropertyName=$true)] $name,
        [Parameter(ValueFromPipelineByPropertyName=$true, Mandatory=$true)] $settings
    )

    Process
    {
        # Insert a new role row and get the identity result
        $sql = "INSERT INTO RoleIdentity (Role) VALUES ('$name') SELECT @@IDENTITY"
        Write-Debug "About to execute command: $sql"
        $identityCmd = New-Object System.Data.SqlClient.SqlCommand($sql, $mdtSQLConnection)
        $identity = $identityCmd.ExecuteScalar()
        Write-Debug "Added role identity record"
    
        # Insert the settings row, adding the values as specified in the hash table
        $settingsColumns = $settings.Keys -join ","
        $settingsValues = $settings.Values -join "','"
        $sql = "INSERT INTO Settings (Type, ID, $settingsColumns) VALUES ('R', $identity, '$settingsValues')"
        Write-Debug "About to execute command: $sql"
        $settingsCmd = New-Object System.Data.SqlClient.SqlCommand($sql, $mdtSQLConnection)
        $null = $settingsCmd.ExecuteScalar()
            
        Write-Debug "Added settings for the specified role"
        
        # Write the new record back to the pipeline
        Get-MDTRole -ID $identity
    }
}
function Remove-MDTRole {

    [CmdletBinding()]
    PARAM
    (
        [Parameter(ValueFromPipelineByPropertyName=$true, Mandatory=$true)] $id
    )

    Process
    {
        # Build the delete command
        $delCommand = "DELETE FROM RoleIdentity WHERE ID = $id"
        
        # Issue the delete command
        Write-Debug "About to issue command: $delCommand"
        $cmd = New-Object System.Data.SqlClient.SqlCommand($delCommand, $mdtSQLConnection)
        $null = $cmd.ExecuteScalar()

        Write-Debug "Removed the role with ID = $id."
    }
}
function Set-MDTRole {

    [CmdletBinding()]
    PARAM
    (
        [Parameter(ValueFromPipelineByPropertyName=$true, Mandatory=$true)] $id,
        [Parameter(Mandatory=$true)] $settings
    )
    
    Process
    {
        # Add each each hash table entry to the update statement
        $sql = "UPDATE Settings SET"
        foreach ($setting in $settings.GetEnumerator())
        {
            $sql = "$sql $($setting.Key) = '$($setting.Value)', "
        }
        
        # Chop off the trailing ", "
        $sql = $sql.Substring(0, $sql.Length - 2)

        # Add the where clause
        $sql = "$sql WHERE ID = $id AND Type = 'R'"
        
        # Execute the command
        Write-Debug "About to execute command: $sql"        
        $settingsCmd = New-Object System.Data.SqlClient.SqlCommand($sql, $mdtSQLConnection)
        $null = $settingsCmd.ExecuteScalar()
            
        Write-Debug "Added settings for the specified role"
        
        # Write the updated record back to the pipeline
        Get-MDTRole -ID $id
    }
}
function Set-MDTRoleAdministrator {

    [CmdletBinding()]
    PARAM
    (
        [Parameter(ValueFromPipelineByPropertyName=$true, Mandatory=$true)] $id,
        [Parameter(ValueFromPipelineByPropertyName=$true, Mandatory=$true)] $administrators
    )

    Process
    {
        Set-MDTArray $id 'R' 'Settings_Administrators' 'Administrators' $administrators
    }
}
function Set-MDTRoleApplication {

    [CmdletBinding()]
    PARAM
    (
        [Parameter(ValueFromPipelineByPropertyName=$true, Mandatory=$true)] $id,
        [Parameter(ValueFromPipelineByPropertyName=$true, Mandatory=$true)] $applications
    )

    Process
    {
        Set-MDTArray $id 'R' 'Settings_Applications' 'Applications' $applications
    }
}
function Set-MDTRolePackage {

    [CmdletBinding()]
    PARAM
    (
        [Parameter(ValueFromPipelineByPropertyName=$true, Mandatory=$true)] $id,
        [Parameter(ValueFromPipelineByPropertyName=$true, Mandatory=$true)] $packages
    )

    Process
    {
        Set-MDTArray $id 'R' 'Settings_Packages' 'Packages' $packages
    }
}
function Set-MDTRoleRole {

    [CmdletBinding()]
    PARAM
    (
        [Parameter(ValueFromPipelineByPropertyName=$true, Mandatory=$true)] $id,
        [Parameter(ValueFromPipelineByPropertyName=$true, Mandatory=$true)] $roles
    )

    Process
    {
        Set-MDTArray $id 'R' 'Settings_Roles' 'Role' $roles
    }
}