Query a Microsoft SQL Server Database with Powershell

The demonstration script below shows how to execute a database query with the "connected" ADO.Net classes and Microsoft PowerShell. This query helps prevent a SQL injection attack by using parameterized queries. This query assumes that the necessary .Net Framework libraries are installed on the system and the correct access to the DB is granted to the account running the script. This script will run against any version of Microsoft SQL Server supported by the .Net Framework. Note that if you are trying to use .Net 4.0, you will need to install PowerShell 3.0 or use the most recent Windows OS (at the time of this writing this is Windows 8 or Windows Server 2012).

As an alternative to ExecuteReader below, it is also possible to run insert/update statements using the ExecuteNonQuery method because they do not return rowsets that need to be fetched and processed.

---

# Mike Burr

# March 2013

# Demonstration Script to perform basic Microsoft SQL Server query using PowerShell

 

[System.Reflection.Assembly]::LoadWithPartialName("System.Data.SqlClient")

  

# Define the connection string for the database connection. Typically the

# account running the script or the application pool identity

# has appropriate access to the database allowing integrated security 

# to be used. In limited cases, it may be necessary to define a username and

# password in the connection string

$ConnectionString = "Data Source=localhost;Database=MyTestDB;Integrated Security=True;"

$conn = new-object "System.Data.SqlClient.SqlConnection" $ConnectionString

 

Try {

   $conn.Open()

   $cmd = $conn.CreateCommand()

   $cmd.CommandText = "SELECT DISTINCT a FROM Test where a = @a"

   $cmd.Parameters.Add((New-Object "System.Data.SqlClient.SqlParameter" -ArgumentList "a","some_value"))

-->

   $reader = $cmd.ExecuteReader()

   while ($reader.Read()) {

      $value = $reader["a"]

   }

} Finally {

    $conn.Close();

} -->

---

See Also:
How to Query a MySql Database using Windows PowerShell