Home

SimplySql - Talking to relational databases the PowerShell way.
Simple commands... Powerful opportunities.

Supporting MSSQL, Oracle, MySql, SQLite, PostGreSql.

Introduction

SimplySql is a module that provides an intuitive set of cmdlets for talking to databases that abstracts the vendor specifics, allowing you to focus on getting work done.

The basic pattern is to connect to a database, execute one or more sql statements and then close your database connection. This module provides cmdlets that map to this basic pattern.

Notes on using multiple connections

• Multiple connections can be opened simultaneously -- specify -ConnectionName as unique for each Open-*SQLConnection and then use the -ConnectionName on the other cmdlets to make the cmdlet execute against that particular connection.

#Open two SQLite Connections
Open-SQLiteConnection
Open-SQLiteConnection -cn b #-cn is the alias for -ConnectionName

#create table in 1st connection and add 10 rows
Invoke-SqlUpdate "CREATE TABLE tmp(val integer)"
1..10 | ForEach-Object { Invoke-SqlUpdate "INSERT INTO tmp VALUES (@v)" -Parameters @{v = $_}}

#Query from  the first connection
Invoke-SqlQuery "SELECT * FROM tmp"
Invoke-SqlQuery "SELECT * FROM tmp" -ConnectionName default #this will work too, because "default" is the name given to connections without a name

#query from 2nd connection, it will fail, because the table does not exit
Invoke-SqlQuery "SELECT * FROM tmp" -ConnectionName b

#Close both connections
Close-SqlConnection #closing the "default"
Close-SqlConnection b

Overview

• Open/Close/Show/Test/Set/Get -SqlConnection

  • These cmdlets (Open/Close) enable you to connect to databases in straightforward terms without worrying about differences between database vendors. There is a specific "Open-*" cmdlet for each provider. (Sql Server, SQLite, Oracle, PostGreSql, MySql).

  • Show/Test cmdlets allow you to see what connections are currently active in your powershell session and see specific details about those connections.

  • Set cmdlet allows you to change the default commandTimeout and the database/catalog in use (if the provider supports it).

  • Get cmdlet returns the underlying connection object itself.

• Invoke-SqlScalar/SqlQuery/SqlUpdate

  • These cmdlets allow you to execute sql statements against the database connections that you have opened.  Any type of statement can be used with any cmdlet, but the output is tailored to specific types of activity.

  • -SqlScalar is great for returning a single value.

  • -SqlQuery is used for returning one or more result sets (output is DataRow for single resultset and Table for multiple resultsets.md). You can use the switch -Stream to return PSObject instead of DataRow.

  • -SqlUpdate is used for making modifications (insert, update, delete, etc) and its output is the number of rows effected.

• Get/Clear -SqlMessage

  • These cmdlets provider access to informational messages, if the provider supports them. The messages are timestamped to when they were received by calling command (and not necessarily when they were generated on the server since many implementations are Asynchronous).

  • Get will return the messages generated by Invoke-SqlScalar/SqlQuery/SqlUpdate cmdlets. Messages are consumed as they are read.

  • Clear will remove all remaining unread messages.

• Invoke-SqlBulkCopy

  • This is intended to make moving data from one connection to another connection (even cross vendor) simple.  This is highly optimized for destination connections that are SQL Server, MySql, Oracle & Postgre. SQLite is also optimized through automatic transaction.

• Start/Complete/Undo/Get -SqlTransaction

  • These cmdlets provide a simple way to wrap Invoke-Sql* (except for SqlBulkCopy) into a transaction and then either commit or rollback.

  • Complete-SqlTransaction maps to COMMIT and Undo-SqlTransaction maps to ROLLBACK.

  • Get cmdlet returns the underlying transaction object for a connection.