PoshCode Archive  Artifact [9c8f5261c4]

Artifact 9c8f5261c495df5fd6429a183fb005f3f5b47b8eda795438860e15702b6372fa:

• File SQLite-Read-Write.ps1 — part of check-in [51213fe4b2] at 2018-06-10 13:16:29 on branch trunk — A very simple example of reading and writing from and to a SQLite DB using Powershell. (user: foureight84 size: 2626)

# encoding: ascii
# api: powershell
# title: SQLite Read / Write
# description: A very simple example of reading and writing from and to a SQLite DB using Powershell.
# version: 0.1
# type: script
# author: foureight84
# license: CC0
# x-poshcode-id: 2879
# x-archived: 2016-10-30T00:55:11
# x-published: 2011-07-31T01:24:00
#
# Requires: System.Data.SQLite for .NET (http://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki)
# Useful Tool: SQLite Manager addon for FIREFOX (http://code.google.com/p/sqlite-manager/)
# This example assumes that you have already setup a database source. In this script the Data Source is directed to a file called “data”.
#
# Make sure SQLite.Interop.dll is in the same directory as System.Data.SQLite.dll

$scriptDir = "Path to your SQLite DLL"

Add-Type -Path "$scriptDir\System.Data.SQLite.dll"


############### SAMPLE USAGE ###################
### Querying:
###
### $readQuery = "SELECT * FROM TABLE"
### $dataArray = $SQLite.querySQLite($readQuery)
###
### Writing:
###
### $writeQuery = "INSERT OR IGNORE INTO TABLE VALUES ('12345', 'TEST');
###                UPDATE GAME SET NAME = 'TEST1235' WHERE ID LIKE '12345';"
### $SQLite.writeSQLite($query)
###
################################################

################################################
###
### $catalog contains two properties ID and NAME
### foreach($item in $catalog ){
###	$writeQuery = "INSERT OR IGNORE INTO GAME VALUES (`"$($item.ID)`", `"$($item.NAME)`");
###					UPDATE GAME SET NAME = `"$($item.NAME)`" WHERE ID LIKE `"$($item.ID)`";"
###	$SQLite.writeSQLite($writeQuery)
### }


$SQLite = New-Module { 

	Function querySQLite {
		param([string]$query)

			$datatSet = New-Object System.Data.DataSet
			
			### declare location of db file. ###
			$database = "$scriptDir\data"
			
			$connStr = "Data Source = $database"
			$conn = New-Object System.Data.SQLite.SQLiteConnection($connStr)
			$conn.Open()

			$dataAdapter = New-Object System.Data.SQLite.SQLiteDataAdapter($query,$conn)
			[void]$dataAdapter.Fill($datatSet)
			
			$conn.close()
			return $datatSet.Tables[0].Rows
			
	}
	
	Function writeSQLite {
		param([string]$query)
		
			$database = "$scriptDir\data"
			$connStr = "Data Source = $database"
			$conn = New-Object System.Data.SQLite.SQLiteConnection($connStr)
			$conn.Open()
			
			$command = $conn.CreateCommand()
			$command.CommandText = $query
			$RowsInserted = $command.ExecuteNonQuery()
			$command.Dispose()
	}
	
	Export-ModuleMember -Variable * -Function * 
} -asCustomObject