how to use?

[ericxin1982]

hi @CedX

How to use this?
I saw example link is error reference.

Thanks
Eric Xin

[CedX]

Hi @ericxin1982. I dreaded the day it would happen: writing documentation is hard, and I'm rather against AI. That's why this project is still not documented 😢

Basically, there's 3 steps:

• initialization (i.e. get an ADO.NET provider for your database)
• querying (i.e. run ad-hoc SQL queries)
• mapping (i.e. use class instances instead of raw data)

I won't have time to explain everything at once, but I will try to give you enough information to get started.

[CedX]

Initialization

What is your target database? PowerShell has native support for SQL Server (System.Data.SqlClient) and ODBC (System.Data.Odbc).

If you plan to use SQL Server, or another database through ODBC, that's easy: use the New-SqlConnection cmdlet to create your connection. It has a -Provider parameter that let you choose the appropriate ADO.NET provider.

Examples:

# Create a connection to a SQL Server database.
$connectionString = "Server=localhost; Database=MyDB; Uid=sa; Pwd=password; TrustServerCertificate=True"
$connection = New-SqlConnection -Provider SqlClient -ConnectionString $connectionString

# Create a connection to a MariaDB database, through its ODBC connector.
$connectionString = "Driver={MariaDB ODBC 3.2 Driver}; Server=localhost; Database=MyDB; Uid=root; Pwd=password"
$connection = New-SqlConnection -Provider Odbc -ConnectionString $connectionString

TIP! ODBC is required if you need to support an old version of SQL Server (e.g. 2000).

[CedX]

For other databases, you'll need to get the .NET DLLs of their driver and load them into your PowerShell script.
The procedure is really painful: let's take MySQL/MariaDB as example.

You must go to NuGet and download the MySqlConnector package.
https://www.nuget.org/packages/MySqlConnector/

A NuGet package is just a ZIP archive: extract it, locate the right DLL to use in the lib subfolder, and copy it into your PowerShell project (let's say into a bin folder).

I'm using PowerShell 7.6. It is based on .NET 10, so the DLL to use is located here:
<NuGetPackage>/lib/net10.0/MySqlConnector.dll

You must repeat the same operation for ALL dependencies of the MySqlConnector package:
https://www.nuget.org/packages/MySqlConnector/#dependencies-body-tab

So you'll have to also download the DLLs of the following packages:

• https://www.nuget.org/packages/Microsoft.Extensions.DependencyInjection.Abstractions/
• https://www.nuget.org/packages/Microsoft.Extensions.Logging.Abstractions/

Once all DLLs installed into the bin folder, load them in your PowerShell script:

using assembly ./bin/Microsoft.Extensions.DependencyInjection.Abstractions.dll
using assembly ./bin/Microsoft.Extensions.Logging.Abstractions.dll
using assembly ./bin/MySqlConnector.dll

Now you can use the dedicated -Type parameter of the New-SqlConnection cmdlet... or you can just instantiate the MySqlConnection class:

using namespace MySqlConnector

# Either...
$connection = New-SqlConnection -Type ([MySqlConnection]) -ConnectionString $connectionString

# Or...
$connection = [MySqlConnection]::new($connectionString)

WARNING! I plan to rename the -Type parameter to -Class in the next release (no due date).
To be consistent with some other cmdlets that accept a [Type] as a parameter...

[CedX]

An "easier" alternative is to use a C# solution/project to handle the NuGet packages.
That's what I do in the tests of this library, that are based on SQLite. See:
https://github.com/CedX/Sql.ps1/blob/main/Tests/Sql.Tests.csproj

Even if your C# solution/project doesn't have any .cs file, you can still build it, and the DLLs will be be deployed in the bin folder:

dotnet build

The important thing to remember is to enable the CopyLocalLockFileAssemblies option in the C# project file:

<CopyLocalLockFileAssemblies>true</CopyLocalLockFileAssemblies>

This will ensure that DLLs are deployed even if they aren't used by any .cs file.
Of course, to use this alternative, you must have the .NET SDK installed.

[CedX]

Querying

Once you have a working connection, things are pretty straightforward: use the dedicated cmdlets.
https://github.com/CedX/Sql.ps1/tree/main/Sources/Query

You don't have to explictly open the connection: it will be done automatically when you run your first query.

# A "SELECT" query...
$rows = Invoke-SqlQuery $connection -Command "SELECT * FROM MyTable"
$rows | Write-Host -Separator "`n"

# A "DELETE/INSERT/UPDATE" query...
$affectedRows = Invoke-SqlNonQuery $connection -Command "DELETE FROM MyTable"

# An aggregate query...
$count = Get-SqlScalar $connection -Command "SELECT COUNT(*) FROM MyTable"

The connection closure is not automatic: once you have finished, remember to call the Sql-CloseConnection cmdlet to close the connection.

# Everything is done: let's close the connection!
Close-SqlConnection $connection

# Optional: also dispose the underlying .NET object.
$connection | Close-SqlConnection -Dispose

[CedX]

A good SQL query should always be parameterized, that is, it should include a WHERE clause with parameters.
You can pass parameters to a query by using the well named -Parameters parameter of most cmdlets.

Most ADO.NET providers use named parameters (i.e. @Name), while others use positional parameters (i.e. ?). This is particularly true of ODBC.
In the first case, you must provide the parameters as a [hashtable]. In the second, you must provide the parameters as an array.

# Named parameters.
$sql = "SELECT * FROM MyTable WHERE FirstName = @FirstName AND LastName = @LastName"
$row = Get-SqlFirst $connection -Command $sql -Parameters @{ FirstName = "Eric"; LastName = "Xin" }

$sql = "SELECT * FROM MyTable WHERE Id = @Id"
$entity = Get-SqlSingle $connection -Command $sql -Parameters @{ Id = 123 }

# Positional parameters.
$sql = "SELECT * FROM MyTable WHERE FirstName = ? AND LastName = ?"
$row = Get-SqlFirst $connection -Command $sql -Parameters "Eric", "Xin"

$sql = "SELECT * FROM MyTable WHERE Id = ?"
$entity = Get-SqlSingle $connection -Command $sql -Parameters @(123)

IMPORTANT! The Get-SqlFirst and Get-SqlSingle cmdlets will trigger an error when the requested row is not found.
If you want to discard this error, you should use the -ErrorAction common parameter.

# Ignore errors.
$sql = "SELECT * FROM MyTable WHERE Id = ?"
$entity = Get-SqlSingle $connection -Command $sql -Parameters @(123) -ErrorAction Ignore
if (-not $entity) { Write-Host "The requested entity could not be found." }

Of course, parameters also work with "DELETE/INSERT/UPDATE" queries.

$sql = "UPDATE MyTable SET FirstName = @CedricBelin WHERE FirstName = @EricXin"
Invoke-SqlNonQuery $connection -Command $sql -Parameters @{
  CedricBelin = "Cédric"
  EricXin = "Eric"
}

[CedX]

Mapping

When you use a SELECT query and its affiliated cmdlets (Get-SqlFirst, Get-SqlSingle and Invoke-SqlQuery), all rows returned will be instances of [PSObject].

This is fine for a single script, but probably too limited for a full PowerShell application. In such application, you will probably create classes that represent your database tables.

IMPORTANT! Your entity classes MUST have a parameterless constructor.
This library does not support constructors having parameters.

If you pass the type of your custom class to the -As parameter of the cmdlets, this library will try to create an instance of your class, and try to map the row data to its properties.

# Your entity class.
class MyEntity {
  [int] $Id
  [string] $FirstName
  [string] $LastName
}

# Use its type with the `-As` parameter.
$entities = Invoke-SqlQuery $connection -As ([MyEntity]) -Command "SELECT * FROM MyTable"
foreach ($entity in $entities) {
  # Each $entity object is an instance of the `MyEntity` class.
  Write-Host "#$($entity.Id) => $($entity.FirstName) $($entity.LastName)"
}

For this to work, your database table MUST use the same column names as the class properties.

CREATE TABLE [MyTable] (
  [Id] int NOT NULL IDENTITY PRIMARY KEY,
  [FirstName] nvarchar(64) NOT NULL,
  [LastName] nvarchar(64)
);

NOTE! Since this library is written in PowerShell, the column-to-property mapping is likely case-insensitive. 😄

[CedX]

We often have to work with legacy databases that use a different column naming convention. In this case, you can specify a different column name for a class property by using the [Column()] attribute from the System.ComponentModel.DataAnnotations.Schema namespace.

Let's say you have this table:

CREATE TABLE [my_table] (
  [entity_id] int NOT NULL IDENTITY PRIMARY KEY,
  [first_name] nvarchar(64) NOT NULL,
  [last_name] nvarchar(64)
);

You'll have to modify your class properties accordingly:

using namespace System.ComponentModel.DataAnnotations.Schema

class MyEntity {
  [Column("entity_id")]
  [int] $Id

  [Column("first_name")]
  [string] $FirstName

  [Column("last_name")]
  [string] $LastName
}

[CedX]

It's common to use JOIN in a query to retrieve multiple related entities simultaneously.

This library follows the approach taken by Dapper (a famous .NET micro-ORM):
https://www.learndapper.com/relationships

That is, the queried columns must obey some order, and the -SplitOn parameter let you specify which columns are used to split the data row into separate entities. You still use the -As parameter to specify the class of your entities.

Let's adapt the example from Dapper:

$sql = "
  SELECT ProductID, ProductName, p.CategoryID, CategoryName
  FROM Products p 
  INNER JOIN Categories c ON p.CategoryID = c.CategoryID"

$rows = Invoke-SqlQuery $connection -As ([Product], [Category]) -Command $sql -SplitOn CategoryId

TIP! If you don't provide the -SplitOn argument, the library will use Id as splitter.

Each returned row will be a ValueTuple where each tuple item contains the corresponding entity instance.
https://devblogs.microsoft.com/scripting/using-a-tuple-in-powershell/
https://learn.microsoft.com/en-us/powershell/module/microsoft.powershell.core/about/about_arrays#indexing-support-for-systemtuple-objects

foreach ($row in $rows) {
  $product = $row.Item1 # Or: $row[0]
  Write-Host $product.GetType().Name # "Product"

  $category = $row.Item2 # Or: $row[1]
  Write-Host $category.GetType().Name # "Category"

  $product.Category = $category
}

IMPORTANT! Before creating the instances, the library will check every column value corresponding to the currently processed entity. If all of them are NULL, a $null reference will be used instead of creating an "empty" object.
This also applies even when you are not using the multi-mapping feature.

Take the time to read Dapper's article on this topic: this library is used in much the same way. For example, with "One-to-Many" relationships, you will need to use grouping.

# A product has many categories: you will need something like...
$products = foreach ($groupInfo in $rows | Group-Object { $_.Item1.ProductID }) {
  $product = $groupInfo.Group[0].Item1
  $product.Categories = $groupInfo.Group.ForEach{ $_.Item2 }
  $product
}

[ericxin1982]

Thanks so much to share me that many. I have use powershell with sql server for years, I am just curious what you have done, and see whether I should switch to your project if that is better.

[CedX]

It depends, of course, on your use case.

Personally, I created this library because I wanted a unified interface (i.e. the same cmdlets regardless of the database you use), and something very efficient.

At my work, it's common to execute thousands of queries, or even more (we have a script that executes approximately 1.5 million SQL queries every day!).

SqlServer module is too slow, dbatools is much better... but only works with SQL Server (I need MySQL/MariaDB too).