Readme

The PowerShell SQL Client module replaces the SQL Server utilities SQLCMD and BCP with native PowerShell commands.

For details, visit the GitHub Pages.

Installation

This module can be installed from PsGallery.

Install-Module -Name PsSqlClient -Scope CurrentUser

Alternatively it can be build and installed from source.

1. Install the development dependencies
2. Download or clone it from GitHub
3. Run the installation task:

Invoke-Build Install

Usage

See the command reference for descriptions and examples.

Copy from CSV to SQL database

# connect to a SQL Server using your current Windows login
Connect-TSqlInstance -DataSource '(LocalDb)\MSSQLLocalDB'

# create a temporary table with the columns of your CSV file
Invoke-TSqlCommand 'CREATE TABLE #Test (Id INT NULL, Name NVARCHAR(MAX))'

# copy the data from CSV to the SQL table
Import-Csv 'test.csv' | Export-TSqlTable '#Test'

Get a single value

# connect to a SQL Server using your current Windows login
Connect-TSqlInstance -DataSource '(LocalDb)\MSSQLLocalDB'

# get a scalar value from the database
[string] $databaseName = Get-TSqlValue 'SELECT DB_NAME()'

Parameterize a query and process results in a pipeline

# connect to a SQL Server using your current Windows login
Connect-TSqlInstance -DataSource '(LocalDb)\MSSQLLocalDB'

# get a result from the database and filter the first five by name
Invoke-TSqlProcedure 'sp_tables' @{ 'table_qualifier' = 'master' } |
    Sort-Object TABLE_NAME |
    Select-Object -First 5

Commands

Command	Description	Status
Connect-Instance	Create a new database connection.	☐
⮱ by Connection String	Use a custom connection string.	☑
⮱ by Properties	Use specific properties for host, database, user, etc.	☑
⮱ with AD credentials	Use integrated security	☑
⮱ to Azure SQL	Connect to Azure SQL (token-based)	☐
⮱ to Azure SQL	Connect to Azure SQL (AAD)	☑
⮱ global connection	Save and reuse the connection	☑
Disconnect-Instance	Close connection	☑
Invoke-Command	Execute stored procedure or select data	☑
⮱ Procedure instead of SQL text	Execute procedure by procedure name	☑
⮱ SQL text from file	Execute sql command from file	☑
Export-Table	Insert data	☑
⮱ show progress	show how many rows already inserted	☐

Changelog

See the changelog file.

Development

• This is a Portable Module based on PowerShell Standard and .NET Standard.
• VSCode is recommended as IDE. VSCode Tasks are configured.
• Build automation is based on InvokeBuild
• Documentation is based on platyPs
• Test automation is based on Pester
• Commands are named based on Approved Verbs for PowerShell Commands
• This project uses git-flow.
• This project uses keep a changelog.
• This project uses PsBuildTasks.

Build

The build scripts require InvokeBuild. If it is not installed, install it with the command Install-Module InvokeBuild -Scope CurrentUser.

You can build the module using the VS Code build task or with the command Invoke-Build Build.

Testing

The tests scripts are based on Pester. If it is not installed, install it with the command Install-Module Pester -Force -SkipPublisherCheck. Some tests require a SQL Server. Therefore the module PsSqlTestServer is used, that can be installed by Install-Module PsSqlTestServer -Scope CurrentUser. The test creates a SQL Server in a Docker container. If needed, install Docker. The container are created using PSDocker, which can be installed using Install-Module PSDocker -Scope CurrentUser.

For local testing use the VSCode test tasks or execute the test scripts directly or with Invoke-Pester. The InvokeBuild test tasks are for CI and do not generate console output.

Release

1. Create a release branch using git-flow.
2. Update the version number in the module manifest.
3. Extend the changelog in this readme.
4. If you want to create a pre-release.
   1. Push the release branch to github, to publish the pre-release to PsGallery.
5. Finish release using git-flow.
6. Check if tags are not pushed to github.
7. Check if the release branch is deleted on github.
8. Create the release on github.