Script Repository

$databaseHost = "host.company.com" #TODO: modify me
$databaseName = "MyDatabase" # TODO: modify me
$patternDN = "CN=My Pattern,CN=Property Patterns,CN=Configuration Objects,CN=Adaxes Configuration,CN=Adaxes" #TODO: modify me
$fieldInfo = @{
    "OfficeName" = @{TableName = "Offices"; PropertyName = "physicalDeliveryOfficeName"; Required = $True}
    "JobTitle" = @{TableName = "JobTitles"; PropertyName = "title"; Required = $False}
} # TODO: modify me

# Specify credentials used to connect to the database.
# When set to $NULL, the credentials of the Adaxes service account will be used
$databaseUsername = $NULL
$databasePassword = $NULL

# Get data from MS SQL database
$connectionString = "Data Source=$databaseHost; Initial Catalog=$databaseName;"
if ($databaseUsername -eq $NULL)
{
    $connectionString = $connectionString +
        "Integrated Security=SSPI;"
}
else
{
    $connectionString = $connectionString +
        "User ID=$databaseUsername;Password=$databasePassword;"
}

try
{
    $connection = New-Object "System.Data.SqlClient.SqlConnection"  $connectionString
    $connection.Open()
    
    $propertiesInfo = @{}
    foreach ($fieldName in $fieldInfo.Keys)
    {
        $tableName = $fieldInfo[$fieldName].TableName
        $propertyName = $fieldInfo[$fieldName].PropertyName
        $propertiesInfo.Add($propertyName, @{
                Values = (New-Object System.Collections.ArrayList)
                Required = $fieldInfo[$fieldName].Required
            }
        )
        
        try
        {
            $command = $connection.CreateCommand()
            $command.CommandText = "SELECT $fieldName FROM $tableName"
            $reader = $command.ExecuteReader()
            
            while ($reader.Read())
            {
                $value = $reader[$fieldName]
                [void]$propertiesInfo[$propertyName].Values.Add($value)
            }
        }
        finally
        {
            if ($reader) { $reader.Close() }
            if ($command) { $command.Dispose() }
        }
    }
}
finally
{
    if ($connection) { $connection.Close() }
}

# Update the Property Pattern
$pattern = $Context.BindToObjectByDN($patternDN)
foreach ($propertyName in $propertiesInfo.Keys)
{
    if ($propertiesInfo[$propertyName].Values.Count -eq 0)
    {
        continue
    }
    
    foreach ($item in $pattern.Items)
    {
        if ($item.PropertyName -eq $propertyName)
        {
            $pattern.Items.Remove($item)
            break
        }
    }
    
    $item = $pattern.Items.Create()
    $item.PropertyName = $propertyName
    $item.IsPropertyRequired = $propertiesInfo[$propertyName].Required
    
    $constraints = $item.GetConstraints()
    $constraint = $constraints.Create("ADM_PROPERTYCONSTRAINTTYPE_VALUERANGE")
    $constraint.AreValuesDenied = $False
    $values = $propertiesInfo[$propertyName].Values.ToArray()
    [System.Array]::Sort($values)
    $constraint.Values = $values
    $constraints.Add($constraint)
    
    $item.SetConstraints($constraints)
    $item.SetInfo()
    $pattern.Items.Add($item)
}