{"id":7671,"date":"2021-04-28T22:22:25","date_gmt":"2021-04-28T20:22:25","guid":{"rendered":"https:\/\/michlstechblog.info\/blog\/?p=7671"},"modified":"2021-04-29T12:52:57","modified_gmt":"2021-04-29T10:52:57","slug":"powershell-working-with-a-sqlite-database","status":"publish","type":"post","link":"https:\/\/michlstechblog.info\/blog\/powershell-working-with-a-sqlite-database\/","title":{"rendered":"Powershell: Working with a SQLite Database"},"content":{"rendered":"<div class=\"twoclick_social_bookmarks_post_7671 social_share_privacy clearfix 1.6.4 locale-en_US sprite-en_US\"><\/div><div class=\"twoclick-js\"><script type=\"text\/javascript\">\/* <![CDATA[ *\/\njQuery(document).ready(function($){if($('.twoclick_social_bookmarks_post_7671')){$('.twoclick_social_bookmarks_post_7671').socialSharePrivacy({\"services\":{\"flattr\":{\"uid\":\"Michl\",\"status\":\"on\",\"the_title\":\"Powershell%3A%20Working%20with%20a%20SQLite%20Database\",\"the_excerpt\":\"Hi%2C%0D%0A%0D%0ASQLite%20Databases%20are%20widly%20used%2C%20even%20Windows%20uses%20for%20the%20organisation%20of%20its%20Appx%20Packages%20a%20SQLite%20database.%20This%20database%20is%20located%20in%20the%20file%20C%3A%5CProgramData%5CMicrosoft%5CWindows%5CAppRepository%5CStateRepository-Machine.srd.%0D%0A%0D%0A%20%28more%26hellip%3B%29\",\"txt_info\":\"2 clicks for more data protection:\\r\\n\\r\\nOnly when you click here, the button will be come active and you can send your recommendation to Flattr. When activating, data are transmitted to third parties. \",\"perma_option\":\"off\"}},\"txt_help\":\"When you activate these fields by clicking, information to Flattr may be transferred abroad, and probably may also stored there.\",\"settings_perma\":\"Enable permanently and accept data transmission. \",\"info_link\":\"http:\\\/\\\/www.heise.de\\\/ct\\\/artikel\\\/2-Klicks-fuer-mehr-Datenschutz-1333879.html\",\"uri\":\"https:\\\/\\\/michlstechblog.info\\\/blog\\\/powershell-working-with-a-sqlite-database\\\/\",\"post_id\":7671,\"post_title_referrer_track\":\"Powershell%3A+Working+with+a+SQLite+Database\",\"display_infobox\":\"on\"});}});\n\/* ]]> *\/<\/script><\/div><p>Hi,<\/p>\n<p>SQLite Databases are widly used, even Windows uses for the organisation of its Appx Packages a SQLite database. This database is located in the file C:\\ProgramData\\Microsoft\\Windows\\AppRepository\\StateRepository-Machine.srd.<\/p>\n<p><!--more--><br \/>\nSQLite <a href=\"http:\/\/system.data.sqlite.org\/index.html\/doc\/trunk\/www\/downloads.wiki\" rel=\"noopener\" target=\"_blank\">provides <\/a>a .NET assembly to open such a database file. <\/p>\n<p>Download the <a href=\"http:\/\/system.data.sqlite.org\/downloads\/1.0.113.0\/sqlite-netFx45-binary-x64-2012-1.0.113.0.zip\" rel=\"noopener\" target=\"_blank\">latest release<\/a> which also fits your .NET version. .NET4.5 is usually preinstalled and a good choice for all Windows 10 Versions. Extract the zip file.<\/p>\n<pre class=\"brush: powershell; title: ; notranslate\" title=\"\">\r\nPS D:\\Temp &gt; Invoke-WebRequest -Uri &quot;http:\/\/system.data.sqlite.org\/blobs\/1.0.113.0\/sqlite-netFx45-binary-x64-2012-1.0.113.0.zip&quot; -OutFile D:\\temp\\sqlite.zip\r\nPS D:\\Temp &gt; mkdir D:\\Temp\\sqlite.net \r\nPS D:\\Temp &gt; Expand-Archive D:\\temp\\sqlite.zip -DestinationPath D:\\Temp\\sqlite.net\r\n<\/pre>\n<p>Load library<\/p>\n<pre class=\"brush: powershell; title: ; notranslate\" title=\"\">\r\nPS D:\\Temp&gt; &#x5B;Reflection.Assembly]::LoadFile(&quot;D:\\Temp\\sqlite.net\\System.Data.SQLite.dll&quot;)\r\n\r\nGAC    Version        Location\r\n---    -------        --------\r\nFalse  v4.0.30319     C:\\tmp\\sqlite.net\\System.Data.SQLite.dll\r\n\r\n<\/pre>\n<p>Open a database. For example the Windows Appx state database (make a copy as administrator).<\/p>\n<pre class=\"brush: powershell; title: ; notranslate\" title=\"\">\r\ncopy-item C:\\ProgramData\\Microsoft\\Windows\\AppRepository\\StateRepository-Machine.srd D:\\Temp\\StateRepository-Machine.srd\r\n$sDatabasePath=&quot;D:\\Temp\\StateRepository-Machine.srd&quot;\r\n$sDatabaseConnectionString=&#x5B;string]::Format(&quot;data source={0}&quot;,$sDatabasePath)\r\n$oSQLiteDBConnection = New-Object System.Data.SQLite.SQLiteConnection\r\n$oSQLiteDBConnection.ConnectionString = $sDatabaseConnectionString\r\n$oSQLiteDBConnection.open()\r\n<\/pre>\n<p>Simple SELECT<\/p>\n<pre class=\"brush: powershell; title: ; notranslate\" title=\"\">\r\n$oSQLiteDBCommand=$oSQLiteDBConnection.CreateCommand()\r\n$oSQLiteDBCommand.Commandtext=&quot;SELECT * from PACKAGE&quot;\r\n$oSQLiteDBCommand.CommandType = &#x5B;System.Data.CommandType]::Text\r\n$oDBReader=$oSQLiteDBCommand.ExecuteReader()\r\n<\/pre>\n<p>Start reading, columns names can be queried by GetValues(), and all results can be enumerated by a while loop<\/p>\n<pre class=\"brush: powershell; title: ; notranslate\" title=\"\">\r\n$oDBReader.GetValues()\r\nwhile($oDBReader.HasRows) \r\n{\r\n    if($oDBReader.Read())\r\n    {\r\n        $oDBReader&#x5B;&quot;PackageFullName&quot;] \r\n    }\r\n}\r\n$oDBReader.Close()\r\n<\/pre>\n<p>Reading a trigger<\/p>\n<pre class=\"brush: powershell; title: ; notranslate\" title=\"\">\r\n$oSQLiteDBCommand.Commandtext=&quot;select * from sqlite_master where type = 'trigger' and name='TRG_BEFOREDELETE_Package_SRJournal'&quot;\r\n$oSQLiteDBCommand.CommandType = &#x5B;System.Data.CommandType]::Text\r\n$oDBReader=$oSQLiteDBCommand.ExecuteReader()\r\nwhile($oDBReader.HasRows) \r\n{\r\n    if($oDBReader.Read())\r\n    {\r\n        write-host &quot;Trigger: &quot; $oDBReader&#x5B;&quot;name&quot;] &quot;SQL Statement:&quot; $oDBReader&#x5B;&quot;sql&quot;]\r\n    }\r\n}\r\n$oDBReader.Close()\r\n<\/pre>\n<p>Create and open your own database, CreateFile creates a zero length file and is initialized when writing to the database.<\/p>\n<pre class=\"brush: powershell; title: ; notranslate\" title=\"\">\r\n$sDatabasePath=&quot;D:\\temp\\MyDatabase.sqlite&quot;\r\n&#x5B;System.Data.SQLite.SQLiteConnection]::CreateFile($sDatabasePath)\r\n$sDatabaseConnectionString=&#x5B;string]::Format(&quot;data source={0}&quot;,$sDatabasePath)\r\n$oSQLiteDBConnection = New-Object System.Data.SQLite.SQLiteConnection\r\n$oSQLiteDBConnection.ConnectionString = $sDatabaseConnectionString\r\n$oSQLiteDBConnection.open()\r\n<\/pre>\n<p>Create a Table<\/p>\n<pre class=\"brush: powershell; title: ; notranslate\" title=\"\">\r\n$oSQLiteDBCommand=$oSQLiteDBConnection.CreateCommand()\r\n$oSQLiteDBCommand.Commandtext=&quot;create table FavoriteMetalBands (name varchar(100), score int)&quot;\r\n$oSQLiteDBCommand.CommandType = &#x5B;System.Data.CommandType]::Text\r\n$oSQLiteDBCommand.ExecuteNonQuery()\r\n<\/pre>\n<p>Insert to the new Dataset<\/p>\n<pre class=\"brush: powershell; title: ; notranslate\" title=\"\">\r\n$oSQLiteDBCommand.Commandtext=&quot;INSERT INTO FavoriteMetalBands (name , score) VALUES (@BandName, @MyScore)&quot;;\r\n$oSQLiteDBCommand.Parameters.AddWithValue(&quot;BandName&quot;, &quot;Kataklysm&quot;);\r\n$oSQLiteDBCommand.Parameters.AddWithValue(&quot;MyScore&quot;, 10);\r\n$oSQLiteDBCommand.ExecuteNonQuery()\r\n<\/pre>\n<p>At the end, close the connection<\/p>\n<pre class=\"brush: powershell; title: ; notranslate\" title=\"\">\r\n$oSQLiteDBConnection.Close()\r\n<\/pre>\n<p>Michael<br \/>\n<!--\nhttps:\/\/germanpowershell.com\/sqlite-net-mit-powershell\/\nhttps:\/\/stackoverflow.com\/questions\/809246\/adding-parameters-in-sqlite-with-c-sharp\n--><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Hi, SQLite Databases are widly used, even Windows uses for the organisation of its Appx Packages a SQLite database. This database is located in the file C:\\ProgramData\\Microsoft\\Windows\\AppRepository\\StateRepository-Machine.srd.<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[344,1587],"tags":[1589,972,139,133,1588],"class_list":["post-7671","post","type-post","status-publish","format-standard","hentry","category-powershell-scripting","category-sqlite","tag-database","tag-edit","tag-open","tag-powershell","tag-sqlite"],"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/michlstechblog.info\/blog\/wp-json\/wp\/v2\/posts\/7671","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/michlstechblog.info\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/michlstechblog.info\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/michlstechblog.info\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/michlstechblog.info\/blog\/wp-json\/wp\/v2\/comments?post=7671"}],"version-history":[{"count":13,"href":"https:\/\/michlstechblog.info\/blog\/wp-json\/wp\/v2\/posts\/7671\/revisions"}],"predecessor-version":[{"id":7684,"href":"https:\/\/michlstechblog.info\/blog\/wp-json\/wp\/v2\/posts\/7671\/revisions\/7684"}],"wp:attachment":[{"href":"https:\/\/michlstechblog.info\/blog\/wp-json\/wp\/v2\/media?parent=7671"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/michlstechblog.info\/blog\/wp-json\/wp\/v2\/categories?post=7671"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/michlstechblog.info\/blog\/wp-json\/wp\/v2\/tags?post=7671"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}