Nexus.PSToolkit

1.0.7

Database.ps1

Function Import-BacPac()
{
    Param(
        [Parameter(Mandatory=$true)]
        [string]$Path, 

        [Parameter(Mandatory=$true)]
        [string]$DatabaseName, 

        [Parameter(Mandatory=$false)]
        [string]$ScriptPath, 

        [Parameter(Mandatory=$false)]
        [string]$ConnectionString, 

        [Parameter(Mandatory=$false)]
        [switch] $Force
    )
    Write-Debug "Database Name : $databaseName";
    $currentConnectionString = "Data Source=(local);Database=$databaseName;Integrated Security=True;MultipleActiveResultSets=True"
    if ($PSBoundParameters.ContainsKey('ConnectionString'))
    {
        $currentConnectionString = $ConnectionString
    }

    Add-Type -AssemblyName System.Data;
    $con = New-Object System.Data.SqlClient.SqlConnectionStringBuilder($currentConnectionString)

    if ($PSBoundParameters.ContainsKey('Force'))
    {

        $deleteScript = "ALTER DATABASE [$databaseName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
        GO
        drop database [$databaseName]" 
    
        $tempfile = [System.IO.Path]::GetTempFileName();
        Write-Debug $tempfile 

        $deleteScript | Out-File $tempfile
        
        $conMasterDatabse = New-Object System.Data.SqlClient.SqlConnectionStringBuilder($currentConnectionString)

        Invoke-SQLScript -ScriptPath $tempfile -ConnectionString  $conMasterDatabse -DatabaseName "master"

        Remove-Item $tempfile
    }

    $sqlPackage = Get-SQLExecutablePath("SqlPackage.exe")
    $workingDir = $sqlPackage.WorkingDir
    $sqlPackagePath = $sqlPackage.ExecutablePath

    Write-Debug "file Name : $sqlPackagePath";
    Write-Debug "Working Dir : $workingDir";
    Write-Debug "Connection String: $currentConnectionString";
    $argList = @('/Action:Import', "/sf:`"$Path`"" , "/tcs:`"$currentConnectionString`"");
    $argList | Write-Debug 

    Start-Process -FilePath $sqlPackagePath -ArgumentList $argList -WorkingDirectory $workingDir -NoNewWindow -Wait -Verbose -Debug

    if ($PSBoundParameters.ContainsKey('ScriptPath'))
    {
        Invoke-SQLScript -ScriptPath $ScriptPath -ConnectionString  $con
    }
}

Function Invoke-SQLScript()
{
    Param([Parameter(Mandatory=$true)][string]$ScriptPath, 
    [Parameter(Mandatory=$true)][string] $DatabaseName, 
    [Parameter(Mandatory=$true)][System.Data.SqlClient.SqlConnectionStringBuilder] $ConnectionString)

    if ($PSBoundParameters.ContainsKey('ConnectionString'))
    {
        
        if ($con.IntegratedSecurity)
        {
            Invoke-Sqlcmd -ServerInstance $con.DataSource -Database $DatabaseName -InputFile $ScriptPath
        }
        else
        {
            Invoke-Sqlcmd -ServerInstance $con.DataSource -Database $DatabaseName -Username $con.UserID -Password $con.Password -InputFile $ScriptPath
        }
    }
    else
    {
        Invoke-Sqlcmd -ServerInstance "." -Database $DatabaseName -InputFile $ScriptPath
    }

}

<#
Function Excecute-SQLCommand()
{
    [CmdletBinding(DefaultParameterSetName="set1")]
    Param([Parameter(Mandatory=$true)][string]$SQlCommand, [Parameter(Mandatory=$true, ParameterSetName="set1")][string] $DatabaseName, [Parameter(Mandatory=$true, ParameterSetName="set2")][System.Data.SqlClient.SqlConnectionStringBuilder] $ConnectionString)
    
    $sqlCMD = Get-SQLExecutablePath("Sqlcmd.exe")
    $workingDir = $sqlCMD.WorkingDir
    $sqlPackagePath = $sqlCMD.ExecutablePath
 
    if ($PSBoundParameters.ContainsKey('ConnectionString'))
    {
        if ($con.IntegratedSecurity)
        {
            $argumentList = @("-S", "\`"" + $con.DataSource +"\`"","-E" ,"-d","\`"" + $con.InitialCatalog + "\`"", "-Q", "\`"$SQlCommand\`"");
        }
        else
        {
            $argumentList = @("-S", "\`"" + $con.DataSource + "\`"","-U", "\`"$con.UserID\`"", "-P", "\`"$con.Password\`"" ,"-d","\`"$con.InitialCatalog\`"", "-Q", "\`"$SQlCommand\`"");
        }
    }
    else
    {
        $argumentList = @("-S", "\`".\`"","-E" ,"-d","\`"$DatabaseName\`"", "-Q", "\`"$SQlCommand\`"");
    }
 
 
    Start-Process $sqlPackagePath -ArgumentList $argumentList -WorkingDirectory $workingDir -NoNewWindow
 
}#>

Function Get-SQLExecutablePath()
{
    Param([Parameter(Mandatory=$true)][string]$ExcutableName)

    $basePath = "C:\Program Files (x86)\Microsoft SQL Server"
    $executablePathLocation = Get-ChildItem -Path $basePath -Recurse -Name $ExcutableName | Sort-Object -Descending | Select-Object -First 1
    $sqlImportLocation = [System.IO.Path]::Combine($basePath, $executablePathLocation)
    $workingDir = [System.IO.Path]::GetDirectoryName($sqlImportLocation)
    
    return @{ExecutablePath = $sqlImportLocation; WorkingDir = $workingDir}
}

Function Get-LocalDatabase()
{
    Invoke-Sqlcmd -Query "SELECT name FROM master.dbo.sysdatabases" | Foreach-Object {
        "$($_.Name)"
    }
}

Function Update-StringInFile()
{
Param([Parameter(Mandatory=$true)][string] $filePattern, [Parameter(Mandatory=$true)][string] $searchStringRegex, [Parameter(Mandatory=$true)][string] $replacementString, [Parameter(Mandatory=$false)][bool] $recursive)


    if ($recursive)
    {
        $configFiles = Get-ChildItem . $filePattern -rec
    }
    else
    {
        $configFiles = Get-ChildItem . $filePattern -rec
    }

    foreach ($file in $configFiles)
    {
        (Get-Content $file.PSPath) |
        Foreach-Object { $_ -replace $searchStringRegex, $replacementString } |
        Set-Content $file.PSPath
    }
}