Wednesday, April 10, 2013

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

1 comment:

  1. Hello Mike!
    Very good blog! You have some very informative posts and lots of great advice and tips for developers but also bigger variety of posts. I think I could help to get you some new readers if you are interested?
    I am looking for quality blogs to include into our community of bloggers - glipho.com
    Please check us out and drop me a line at hubert@glipho.com for any questions.
    Best!
    Hubert

    ReplyDelete