Skip to content

Latest commit

 

History

History
123 lines (88 loc) · 6.17 KB

Readme.md

File metadata and controls

123 lines (88 loc) · 6.17 KB
Raw

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

.github/workflows/build-validation.yml

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.