SimplySql

To process large result sets efficiently, use the -Stream switch. This uses a datareader to emit a PSObject for each row to the pipeline as soon as the connection begins returning data.

Important Constraints:

• When -Stream is used, only the first result set is returned, even if multiple are available.
• If you use -Stream, the -AsDataTable switch is ignored.

The standard workflow for using SimplySql involves three main steps:

1. Connect: Use an Open-*Connection cmdlet specific to your database provider to establish a connection.
2. Execute: Use Invoke-Sql* cmdlets to run your SQL statements.
3. Disconnect: Use a Close-*Connection cmdlet to release the connection.

This pattern abstracts vendor-specific connection logic, allowing you to use consistent cmdlets across different database types.

# Example Workflow
Open-SQLiteConnection
Invoke-SqlUpdate -Query "CREATE TABLE test (id INT)"
Close-SQLiteConnection

To optimize the bulk copy operation, you can control how data is batched and how long batches are allowed to run.

• -BatchSize: Specifies how many inserts are grouped together in a single batch. The default value is 500.
• -BatchTimeout: Specifies the maximum time (in seconds) a single batch can take. If not specified, it defaults to the command timeout of the source connection.

If the destination provider (like SQL Server) supports a managed bulk copy implementation (e.g., SqlBulkCopy), the operation is highly optimized. Otherwise, SimplySql optimizes performance by wrapping multiple inserts into a single transaction.

SimplySql follows a consistent pattern: open a connection, invoke SQL statements, and then close the connection.

While connection cmdlets are provider-specific (e.g., Open-SqlConnection for MSSQL, Open-SQLiteConnection for SQLite), the execution cmdlets like Invoke-SqlQuery are provider-agnostic.

If you have multiple connections open simultaneously, distinguish them using the -ConnectionName parameter. If omitted, it defaults to 'default'.

# Basic pattern
Open-*Connection -DataSource "SomeServer" -InitialCatalog "SomeDB"
$data = Invoke-SqlQuery -query "SELECT * FROM someTable"

# Using parameters to prevent injection
$data = Invoke-SqlQuery -query "SELECT * FROM someTable WHERE someCol = @var" -Parameters @{var = 'a value'}

# Close the connection
Close-SqlConnection

SimplySql provides a suite of cmdlets to manage database connections in your PowerShell session:

• Open-<Provider>Connection: Establishes a connection. Available providers include:
  • Open-SqlConnection
  • Open-SQLiteConnection
  • Open-OracleConnection
  • Open-PostGreConnection
  • Open-MySqlConnection
• Close-<Provider>Connection: Closes the connection for the specified provider.
• Show/Test-SqlConnection: View active connections and their details in the current session.
• Set-SqlConnection: Change the default commandTimeout or the active database/catalog (if supported by the provider).
• Get-SqlConnection: Returns the underlying raw connection object.

If you need to use a specific provider-formatted connection string, use the conn parameter set. This requires the -ConnectionString parameter.

# Example of Connection String Authentication
Open-SQLConnection -ConnectionString "Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;"

To connect to an Azure SQL Database using Azure AD credentials, use the credential parameter set. You must provide a PSCredential object and include the -AzureAD switch.

Parameters:

• -Credential: A PSCredential object.
• -AzureAD: A switch parameter to indicate Azure AD authentication.
• -Server: The server address.
• -Database: The target database.

# Example of Azure AD Authentication
$cred = Get-Credential
Open-SQLConnection -Server "your-server.database.windows.net" -Database "your-db" -Credential $cred -AzureAD

Use the Show-SqlConnection cmdlet to inspect active SQL connections. By default, it lists information for a single connection. If you provide a connection name, it retrieves details for that specific connection. If you use the -All switch, it returns a list of all currently active connection names.

# List information for the default connection
Show-SqlConnection

# List information for a specific connection
Show-SqlConnection -ConnectionName "MyDatabaseConnection"

# List all active connection names
Show-SqlConnection -All

You can install SimplySql from the PowerShell Gallery. This module requires PowerShell version 5.0 or greater.

To install it for the current user, use the following command:

Install-Module SimplySql -Scope CurrentUser

If you have an Azure access token, you can use the token parameter set. Pass the token string to the -AzureToken parameter. The cmdlet will automatically strip the bearer prefix if present.

# Example of Token Authentication
Open-SQLConnection -Server "your-server.database.windows.net" -Database "your-db" -AzureToken "bearer YOUR_TOKEN_HERE"