whichIsFasterSqlCmdOrSqlClient.ps1

Clear-Host 
# no these are not my real settings!
$ServerName = "my.little.server.0800.database.windows.net"
$targetDatabaseName = "MyLittleDatabase"
$userName = "bob"
$Password = "NoPasswords4U!"

$pathToFiles = "C:\Users\richardlee\mylittleDatabase\dbo\Tables"
$files = Get-ChildItem $pathToFiles
Write-Host "There are $($files.Count) create table files to execute." -ForegroundColor DarkMagenta -BackgroundColor Yellow

foreach ($file in $files){
    $old = Get-Content $file.FullName
    Set-Content -Path $file.FullName -Value "PRINT '$($file.FullName)'"
    Add-Content -Path $file.FullName -Value $old
}

### batch files to sqlcmd
$date1 = get-date
sqlcmd -i $files.FullName -S $ServerName -d $TargetDatabaseName -G -U $Username -P $Password -I  -y 0 -b -j
$date2 = get-date
$taskTime = "Bulk executing files using sqlcmd, task took(HH:MM:SS:MS) " + (New-TimeSpan -Start $date1 -End $date2)
write-Host $taskTime -ForegroundColor White -BackgroundColor DarkGreen

### foreach file execute sqlcmd
$date1 = get-date
foreach ($file in $files) {
    sqlcmd -i $file.FullName -S $ServerName -d $TargetDatabaseName -G -U $Username -P $Password -I  -y 0 -b -j
}
$date2 = get-date
$taskTime = "Using sqlcmd for each file, task took(HH:MM:SS:MS) " + (New-TimeSpan -Start $date1 -End $date2)
write-Host $taskTime -ForegroundColor White -BackgroundColor DarkCyan

### sql client
$date1 = get-date
$userDbCon = New-Object System.Data.SqlClient.SqlConnection
$userDbCon.ConnectionString = "Server = $ServerName; Database = $targetDataBaseName; Authentication=Active Directory Password; UID = $Username; PWD = $Password;"
$userDbCon.Open();
$SqlExecCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlExecCmd.Connection = $userDbCon
foreach ($file in $files) {
    $SqlExecCmd.CommandText = Get-Content $file.FullName
    $SqlExecCmd.ExecuteScalar()
}
$userDbCon.Close();
$date2 = get-date
$taskTime = "Using sqlclient for each file, task took(HH:MM:SS:MS) " + (New-TimeSpan -Start $date1 -End $date2)
write-Host $taskTime -ForegroundColor White -BackgroundColor DarkMagenta