If you’re interested in using PowerShell Core in Linux, as well in Windows, to connect and execute T-SQL queries then the “System.Data” namespace is available. This contains .NET classes necessary to help you access and manage data from SQL Server, as well as different ADO.NET source providers. Although, this is not a substitute for SMO, you can use it to execute T-SQL queries, Stored-Procedures, and even DMV’s.
There’s a catch!
Nothing is perfect yet! Using the .NET Core version of System.Data, there’s a known issue with the datarow class. It seems it’s building the data results as string list of values without the column information.
But, there’s always a way to make thing work adding some extra code to work around this issue and reconstruct the data the way we want.
So, give it time! It will be fix. But, for now, below is the workaround.
Sample Code Workaround
The following code, using .NET Core System.Data, will connect to a SQL Server (Windows or Linux) using SQL Server Authentication, execute a T-SQL query, extract the data and rebuild the .NET PSObject. At the end, this PowerShell .NET object will contain the datarow object with Columns and Data values.
1. Prepare connection string and execute query
[sourcecode language=”powershell”]
## – Prepare connection string and execute query:
$Global:getSvr = “mtrinidadlt2,1437”;
$Global:con = “server=$Global:getSvr;database=Master;Integrated Security=false;User ID=sa;” + ‘Password=$Adm1n!’;
$sda = New-Object System.Data.SqlClient.SqlDataAdapter (“SP_WHo2″, $global:con)
$sdt = New-Object System.Data.DataTable
$sda.fill($sdt) | Out-Null
[/sourcecode]
2. Dissect query result to identify column and data:
[sourcecode language=”powershell”]
## – Dissect query result to identify column and data:
## – =================================================
## – Initializing varialbles:
$global:rowcolumnheading = $null;
## – Prepare columns:
$global:cnt1 = 1;
foreach ($colname in $sdt.columns.ColumnName)
{
if ($global:cnt1 -ne $sdt.columns.ColumnName.count)
{
[string]$global:rowcolumnheading += $colname + ‘,’;
$global:cnt1++
}
else
{
[string]$global:rowcolumnheading += $colname;
}
}; $global:rowcolumnheading;
[array]$global:dataobject = $null; [string]$dataitem = $null;
$global:dataobject = $global:rowcolumnheading;
## – Load data:
$global:cnt2 = 1;
foreach ($item in $sdt.rows.table[0].rows.itemarray)
{
if ($global:cnt2 -ne $sdt.rows.table[0].rows[0].itemarray.count)
{
try
{
[string]$dataitem += $item.Trim() + ‘,’;
$global:cnt2++;
}
catch
{
#-> Ignore Error and keep counter working:
[string]$dataitem += ‘-‘ + ‘,’;
$global:cnt2++;
}
}
else
{
try
{
[string]$dataitem += $item.Trim();
$global:dataobject += $dataitem;
$dataitem = $null; $cnt2 = 1;
}
catch
{
#-> Ignore Error and keep counter working:
[string]$dataitem += ‘-‘;
$global:dataobject += $dataitem;
$dataitem = $null; $cnt2 = 1;
}
};
};
[/sourcecode]
3. Finally, properly build .NET data object with column/row:
[sourcecode language=”powershell”]
## – Build csv to rebuild data column/row object: (Windows)
$global:dataobject | Out-File c:/temp/data.csv
$data = import-csv c:/temp/data.csv
[/sourcecode]
4. (Optional) Export fixed data object to CSV file:
[sourcecode language=”powershell”]
## – Export to a true CSV format file:
$data | Export-Csv -Path c:/temp/nonSMO_Data.csv -NoTypeInformation -Encoding UTF8
[/sourcecode]
Sample results of the PowerShell Core script:
Basically, step 3 and 4 is the work around code in order to build the Datarow objects properly. I’m using the comma ‘,’ as a field delimiter and, when the dataitem has a NULL value, I’m replacing it with a dash ‘-‘.
About SMO in Linux
I’m sad to say that the SMO in Linux broke with the recent PowerShell Core Beta 3. But, the issue has been reported and eventually SMO in Linux will bounce back. If you want to play with it, you can have multiple version of PowerShell Core installed side-by-side in Windows. This issue will be corrected soon.
