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

Query a MySQL Database with PowerShell

In a current project I am limited to using MySql instead of my usual Microsoft SQL Server platform and I wanted to build a set of scripts that perform some ETL (Extract, Transform, and Load) work in MySQL. Since I want the ability to make small changes without recompiling an application or a dll, I decided to use PowerShell and Oracle MySql Connector/Net. This simplistic example assumes that Connector/Net is installed and has the dlls loaded in the GAC (Global Assembly Cache).

Below is the sample PowerShell script that executes a basic query against a MySql database. This example database has a single table (Test) and a single column (A), but you can easily manipulate the script to perform all sorts of database administration tasks including creating/dropping tables/indexes, importing data, exporting data, e-mailing data, managing processes/users, etc...

Like ADO.Net, MySql connector/Net has the idea of a connected and disconnected mode. This example assumes that the data manipulation will happen while the client is connected, rather than using DataSets and TableAdapters to read/manipulate the data.

---

# Mike Burr

# April 2013

# Demonstrates how to perform a basic query on MySql using PowerShell

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

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

#Create the connection

$connstr = "server=localhost;user=root;database=my_test_db;port=3306;password=some_pwd;"

$conn = New-Object "MySql.Data.MySqlClient.MySqlConnection" $connstr

Try {

   write-host "Connection Opened"

   #Open the connection

   $conn.Open()

   $query = "SELECT * FROM Test WHERE A = @A"

  

   $cmd = New-Object "MySql.Data.MySqlClient.MySqlCommand" -ArgumentList $query,$conn

   $cmd.Parameters.AddWithValue("@A", "test")

  

   $reader = $cmd.ExecuteReader()

  

   while ($reader.Read()) {

      write-host ("A is " + ($reader["A"]))

   }

  

  

} Catch {

  #Perform exception handling logic here

} Finally {

  #Close the connection

  write-host "Connection Closed"

  $conn.Close()

}

-->

---

See Also:
How to Query a Microsoft SQL Server Database with Windows PowerShell