SQLite is a lightweight, disk-based database that doesn't require a separate server process, making it an excellent choice for embedded database applications. While SQLite is more commonly associated with Linux environments, it can be effectively used on Windows systems as well. This article will guide you through running SQLite queries using PowerShell, a powerful scripting language and command-line shell for Windows.

PowerShell can be used to interact with SQLite databases by leveraging the System.Data.SQLite library, which provides a .NET wrapper around the SQLite database engine. This approach allows you to run SQL queries, manage databases, and perform various database operations directly from the PowerShell command line.

Examples:

1. Installing the System.Data.SQLite Library: Before you can run SQLite queries from PowerShell, you need to install the System.Data.SQLite library. You can do this using the NuGet package manager.

   Install-Package -Name System.Data.SQLite -Source https://www.nuget.org/api/v2

2. Creating a SQLite Database and Table: The following example demonstrates how to create a new SQLite database and a table using PowerShell.

   # Load the SQLite assembly
   Add-Type -Path "C:\Path\To\System.Data.SQLite.dll"
   
   # Create a new SQLite connection
   $connectionString = "Data Source=C:\Path\To\YourDatabase.db;Version=3;"
   $connection = New-Object System.Data.SQLite.SQLiteConnection($connectionString)
   
   # Open the connection
   $connection.Open()
   
   # Create a new table
   $command = $connection.CreateCommand()
   $command.CommandText = @"
   CREATE TABLE IF NOT EXISTS Users (
      ID INTEGER PRIMARY KEY AUTOINCREMENT,
      Name TEXT NOT NULL,
      Age INTEGER NOT NULL
   );
   "@
   $command.ExecuteNonQuery()
   
   # Close the connection
   $connection.Close()

3. Inserting Data into the SQLite Table: This example shows how to insert data into the previously created table.

   # Open the connection
   $connection.Open()
   
   # Insert data
   $command.CommandText = "INSERT INTO Users (Name, Age) VALUES ('John Doe', 30);"
   $command.ExecuteNonQuery()
   
   # Close the connection
   $connection.Close()

4. Querying Data from the SQLite Table: Finally, here's how to query data from the table and display it in PowerShell.

   # Open the connection
   $connection.Open()
   
   # Query data
   $command.CommandText = "SELECT * FROM Users;"
   $reader = $command.ExecuteReader()
   
   # Read and display data
   while ($reader.Read()) {
      $id = $reader["ID"]
      $name = $reader["Name"]
      $age = $reader["Age"]
      Write-Output "ID: $id, Name: $name, Age: $age"
   }
   
   # Close the connection
   $connection.Close()