PoshCode Archive  Artifact [73f5e64b43]

Artifact 73f5e64b434a98d46400cf306ae91a395c227f7c48340cf909f2c20493cdaf64:

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

# 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: 2878
# x-archived: 2016-07-29T09:24:02
# x-published: 2011-07-31T00:56: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 GAME 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