Untitled

<#
    Name: Daytona Thornton
    Student ID: 010588214
    Script: Restore-SQL.ps1
    Description: Recreates ClientDB, creates a table, imports NewClientData.csv, and exports results to SqlResults.txt
#>

$instance = ".\SQLEXPRESS"

# Check if the database exists and drop it if needed
try {
    $dbCheck = Invoke-Sqlcmd -ServerInstance $instance -Database master -Query "SELECT name FROM sys.databases WHERE name = 'ClientDB'" -ErrorAction Stop
    if ($dbCheck) {
        Write-Host "'ClientDB' exists. Deleting..."
        Invoke-Sqlcmd -ServerInstance $instance -Database master -Query "DROP DATABASE ClientDB"
        Write-Host "'ClientDB' deleted."
    } else {
        Write-Host "'ClientDB' does not exist."
    }
} catch {
    Write-Host "Error during DB check/delete: $_"
}

# Create the database
try {
    Write-Host "`nCreating 'ClientDB'..."
    Invoke-Sqlcmd -ServerInstance $instance -Query "CREATE DATABASE ClientDB"
    Write-Host "'ClientDB' created."
} catch {
    Write-Host "Error creating database: $_"
}

# Create the table
try {
    Write-Host "`nCreating table 'Client_A_Contacts'..."
    $tableQuery = @"
CREATE TABLE Client_A_Contacts (
    FirstName NVARCHAR(50),
    LastName NVARCHAR(50),
    City NVARCHAR(50),
    County NVARCHAR(50),
    Zip NVARCHAR(10),
    OfficePhone NVARCHAR(20),
    MobilePhone NVARCHAR(20)
)
"@
    Invoke-Sqlcmd -Query $tableQuery -ServerInstance $instance -Database ClientDB
    Write-Host "'Client_A_Contacts' table created."
} catch {
    Write-Host "Error creating table: $_"
}

# Import data from CSV
try {
    Write-Host "`nImporting data from CSV..."
    $data = Import-Csv "$PSScriptRoot\NewClientData.csv"
    foreach ($row in $data) {
        $insertQuery = "INSERT INTO Client_A_Contacts (FirstName, LastName, City, County, Zip, OfficePhone, MobilePhone)
                        VALUES (N'$($row.first_name)', N'$($row.last_name)', N'$($row.city)', N'$($row.county)', 
                                N'$($row.zip)', N'$($row.officePhone)', N'$($row.mobilePhone)')"
        Invoke-Sqlcmd -Query $insertQuery -ServerInstance $instance -Database ClientDB
    }
    Write-Host "Data imported successfully."
} catch {
    Write-Host "Error importing users: $_"
}

# Export data to SqlResults.txt
try {
    Write-Host "`nExporting table contents to SqlResults.txt..."
    Invoke-Sqlcmd -Database ClientDB –ServerInstance $instance -Query "SELECT * FROM Client_A_Contacts" > "$PSScriptRoot\SqlResults.txt"
    Write-Host "SqlResults.txt created."
} catch {
    Write-Host "Error exporting SQL results: $_"
}