Skip to content

Invoke-SQLiteQuery.md

Latest commit

 

History

History
230 lines (165 loc) · 7 KB

Invoke-SQLiteQuery.md

File metadata and controls

230 lines (165 loc) · 7 KB
Raw

Invoke-SQLiteQuery

SubModule: GenXdev.Data.SQLite | Type: Function | Aliases: —

Synopsis

Executes one or more SQL queries against a SQLite database with transaction support.

Description

Executes SQL queries against a SQLite database with parameter support and configurable transaction isolation. Can use an external transaction for batch operations or create its own internal transaction. When using an external transaction, the caller is responsible for committing/rolling back. Connection priority: Transaction > ConnectionString > DatabaseFilePath.

Syntax

Invoke-SQLiteQuery [[-ConnectionString] <String>] [[-DatabaseFilePath] <String>] [[-Transaction] <Object>] -Queries <String[]> [[-SqlParameters] <Collections.Hashtable[]>] [-AutoConsent] [-AutoConsentAllPackages] [-IsolationLevel <String>] [-SessionOnly] [<CommonParameters>]

Parameters

Name Type Required Description
-ConnectionString String ☐ The connection string to the SQLite database.
-DatabaseFilePath String ☐ The path to the SQLite database file.
-Transaction Object ☐ An existing SQLite transaction to use for the
queries.
-Queries String[] ✅ The query or queries to execute.
-SqlParameters Collections.Hashtable[] ☐ Query parameters as hashtables.
-IsolationLevel String ☐ The isolation level to use. default is
ReadCommitted.
-AutoConsent SwitchParameter ☐ Automatically consent to this installation
type and set persistent flag..
-AutoConsentAllPackages SwitchParameter ☐ Automatically consent to ALL third-party
software installations and set persistent
flag for SQLite package.
-SessionOnly SwitchParameter ☐ Use alternative settings stored in session
for preferences

Examples

Invoke-SQLiteQuery -DatabaseFilePath "C:\data.db" -Queries "SELECT * FROM Users"
"SELECT * FROM Users" | isql "C:\data.db" @{"UserId"=1}
Batch operations using external transaction
$tx = Get-SQLiteTransaction -DatabaseFilePath "C:\data.db"
try {
    Invoke-SQLiteQuery -Transaction $tx -Queries "INSERT INTO Users VALUES (@name)" -SqlParameters @{"name"="John"}
    Invoke-SQLiteQuery -Transaction $tx -Queries "UPDATE Users SET active=1 WHERE name=@name" -SqlParameters @{"name"="John"}
    $tx.Commit()
} catch {
    $tx.Rollback()
    throw
} finally {
    $tx.Connection.Close()
}
Invoke-SQLiteQuery -DatabaseFilePath "C:\data.db" -Queries "SELECT * FROM Users" -AutoConsentAllPackages

Parameter Details

-ConnectionString <String>

The connection string to the SQLite database.

Property Value
Required? No
Position? 0
Default value (none)
Accept pipeline input? False
Aliases (none)
Accept wildcard characters? No

-DatabaseFilePath <String>

The path to the SQLite database file.

Property Value
Required? No
Position? 1
Default value (none)
Accept pipeline input? False
Aliases dbpath, indexpath
Accept wildcard characters? No

-Transaction <Object>

An existing SQLite transaction to use for the queries.

Property Value
Required? No
Position? 2
Default value (none)
Accept pipeline input? False
Aliases (none)
Accept wildcard characters? No

-Queries <String[]>

The query or queries to execute.

Property Value
Required? Yes
Position? 3
Default value (none)
Accept pipeline input? True (ByValue, ByPropertyName)
Aliases q, Value, Name, Text, Query
Accept wildcard characters? No

This parameter accepts all remaining arguments.

-SqlParameters <Collections.Hashtable[]>

Query parameters as hashtables.

Property Value
Required? No
Position? 4
Default value @()
Accept pipeline input? True (ByValue, ByPropertyName)
Aliases data, parameters, args
Accept wildcard characters? No

This parameter accepts all remaining arguments.

-IsolationLevel <String>

The isolation level to use. default is ReadCommitted.

Property Value
Required? No
Position? Named
Default value "ReadCommitted"
Accept pipeline input? False
Aliases (none)
Accept wildcard characters? No

-AutoConsent

Automatically consent to this installation type and set persistent flag..

Property Value
Required? No
Position? Named
Default value (none)
Accept pipeline input? False
Aliases (none)
Accept wildcard characters? No

-AutoConsentAllPackages

Automatically consent to ALL third-party software installations and set persistent flag for SQLite package.

Property Value
Required? No
Position? Named
Default value (none)
Accept pipeline input? False
Aliases (none)
Accept wildcard characters? No

-SessionOnly

Use alternative settings stored in session for preferences

Property Value
Required? No
Position? Named
Default value (none)
Accept pipeline input? False
Aliases (none)
Accept wildcard characters? No

Related Links