Script repository

$databaseHost = "host.company.com" # TODO: modify me
$databaseName = "My Database" # TODO: modify me
$tableName = "My Table" # TODO: modify me
$userNameField = "User ID" # TODO: modify me

$databaseUsername = $NULL # TODO: modify me
$databasePassword = $NULL # TODO: modify me

# Connect to the 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()
    
    # Check whether the user already exists in the database.
    try
    {
        $command = $connection.CreateCommand()
        $command.CommandText = "SELECT UserID FROM $tableName WHERE $tableName`.$userNameField = @Username"
        $command.Parameters.Add("@Username", "%username%") | Out-Null;
        $userFromDb = $command.ExecuteScalar()
    }
    finally
    {
        # Close the search and release resources.
        $command.Dispose()
    }
    
    if ($userFromDb -ne $NULL)
    {
        $Context.LogMessage("The user has already been exported to the database", "Warning")
        return
    }
    
    try
    {
        $command = $connection.CreateCommand()
        
        # Get domain name
        $domainName = $Context.GetObjectDomain("%distinguishedName%")
        
        # Get extension
        $telephoneNumber = "%telephoneNumber%"
        if (!([System.String]::IsNullOrEmpty($telephoneNumber)))
        {
            $extension = $telephoneNumber.SubString($telephoneNumber.Length - 4)
        }
        else
        {
            $extension = [System.String]::Empty
        }
        
        # Create INSERT command to add the data into the database
        $command.CommandText = "
            INSERT INTO $tableName (UserID, LastName, FirstName, IsActive, DateCreated, Email, WorkPhone, Extension, EmployeeCode, JobTitle, ADDomain) 
            VALUES(@Username, @sn, @givenName, @isActive, @whenCreated, @mail, @telephoneNumber, @extension, @employeeID, @title, @domainName)"
        $command.Parameters.Add("@Username", "%username%") | Out-Null;
        $command.Parameters.Add("@sn", "%sn%") | Out-Null;
        $command.Parameters.Add("@givenName", "%givenName%") | Out-Null;
        $command.Parameters.Add("@isActive", (!$Context.TargetObject.AccountDisabled).ToString()) | Out-Null;
        $command.Parameters.Add("@whenCreated", "%whenCreated%") | Out-Null;
        $command.Parameters.Add("@mail", "%mail%") | Out-Null;
        $command.Parameters.Add("@telephoneNumber", "%telephoneNumber%") | Out-Null;
        $command.Parameters.Add("@extension", $extension) | Out-Null;
        $command.Parameters.Add("@employeeID", "%employeeID%") | Out-Null;
        $command.Parameters.Add("@title", "%title%") | Out-Null;
        $command.Parameters.Add("@domainName", $domainName) | Out-Null;

        # Insert the user account information into the database.
        $command.ExecuteScalar()
    }
    finally
    {
        # Close the database connection and release resources.
        $command.Dispose()
    }
}
finally
{
    $connection.Close()
}