PowerShell Core – Getting SQL Server using ADO.NET Data provider

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

## - 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

2. Dissect query result to identify column and data:

## - 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;
}
};
};

3. Finally, properly build .NET data object with column/row:

## - Build csv to rebuild data column/row object: (Windows)
$global:dataobject | Out-File c:/temp/data.csv
$data = import-csv c:/temp/data.csv

4. (Optional) Export fixed data object to CSV file:

## - Export to a true CSV format file:
$data | Export-Csv -Path c:/temp/nonSMO_Data.csv -NoTypeInformation -Encoding UTF8

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.

FLPSUG – Next Online meeting July 26th 2017

I’m working on getting a meeting with Keiser University to allow me to host my Florida PowerShell User Group Monthly meetings at their Port St. Lucie Campus location.  But, in the meantime, I setup July’s Online meeting for Wednesday 26th at 6:30pm (EST).

This month topic:

Working with SQL Server for Linux Cross-Platform

You’re welcome to explore the latest build of SQL Server for Linux, including everything you need to install and connect to SQL Server. He will also look into the tools that are available to use from Linux and / or Windows. Maximo will provide samples on querying for database information using Python/Java and PowerShell between two environments. This will be a demo intensive session you will not want to miss!

To register, click on the following Eventbrite link: https://www.eventbrite.com/e/florida-powershell-user-group-monthly-meeting-july-2017-tickets-36113308879?ref=estw

I hope you can joined me in this exciting session!