I'm trying to write a Powershell script that executes a SQL query contained in a .sql file
Function RunSQLScript ($connstring, $filePath)
{
$query = get-content $filePath;
$DTSet = New-Object System.Data.DataSet;
$Conn=New-Object System.Data.SQLClient.SQLConnection $connstring;
$Conn.Open();
try
{
$DataCmd = New-Object System.Data.SqlClient.SqlCommand;
$MyQuery = $query;
$DataCmd.CommandText = $MyQuery;
$DataCmd.Connection = $Conn;
$DAadapter = New-Object System.Data.SqlClient.SqlDataAdapter;
$DAadapter.SelectCommand = $DataCmd;
$DAadapter.Fill($DTSet) | Out-Null;
for ($i = 0; $i -lt $DTSet.Tables.Count; $i++) {
Write-Host $DTSet.Tables[$i];
}
}
finally
{
$Conn.Close();
$Conn.Dispose();
}
return $DTSet;
}
The internal Write-Host is showing the DataTable name instead of the DataRows.
If I manually create a DataSet with a DataTable in Powershell Console, Write-Host shows me the data in the DataTable rows, so I can't really figure out why it is not doing that in the previous script.
Can you give me some clues on how to show the data contained in the datatables instead of the table names?
Thank you
This piece of code was quite helpful for me, posting it here if anybody needs it.
for ($i = 0; $i -lt $DTSet.Tables.Count; $i++) {
$DTSet.Tables[$i] | format-table | out-host
}
That produces a nice table-like output on screen.