Using Powershsell to Access SQL Data

A couple of weeks ago, I was asked by a colleague for help on querying data from a SQL server.  In this particular case, the guy was trying to gather the names of servers and their patching groups from our corporate configuration database.  While I am unable to present the exact information due to corporate confidentiality agreements, I thought that the process used to gather the information might be useful for someone. 

In the following example, I have used the same process to gather information from the Northwinds Sample database.  In order to better present the information, I will be breaking the different lines up into steps.  It could be put together in a function to allow you to perform the same action against the database with various other queries.

The first thing we will do is to set up the connection string.  I set this up in a separate variable for later readability.  It also makes the lines more readable and easier to edit if you need to modify it a little bit later.

$connString = "data source=SQLServer,1433;Initial catalog=NorthWind;uid=NorthwindUser;pwd=NorthwindUser;"

If you are unfamiliar with connection strings, this line will tell the shell to connect to a server named “SQLServer” on port 1433.  It will connect to the Northwind database using a username and password of “NorthwindUser.”  (Security Note:  It is recommended to use strong passwords on your database connections.  That is to say that using an identical username/password combination is not recommended.)  The next line will set up the SQL SELECT statement that will be used.

$QueryText = "SELECT DISTINCT c.CompanyName, c.ContactName, c.Phone FROM Customers c LEFT JOIN Orders o ON c.CustomerID = o.CustomerID where o.CustomerID IS NOT null"

This statement will get the Company Name for all customers who have made an order.  Using the default database, I started to list all the customers who did NOT have an order, but that was much less dramatic with only two records.  This query will bring back quite a bit more.

Next piece will be to set up the SQL Connections.  We will be using the SQLClient class in the System.Data namespace.  First we will instantiate a new SqlConnection and SqlCommand object.

$SqlConnection = new-object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = $connString
$SqlCommand = $SqlConnection.CreateCommand()
$SqlCommand.CommandText = $QueryText

After instantiating the SqlConnection object, I have populated the ConnectionString property with the string we set up earlier.  Next we created a new SqlCommand object using the CreateCommand() method of the SqlConnection object.  I then added the $QueryText data to the CommandText property.

To get the data into something usable, and in a quick amount of time, I have set up a SqlDataAdapter to create an in-memory table that can easily be manipulated.  The SqlDataAdapter will fill a dataset with the data that we are extracting from the SQL server.  Another reason for using the Fill() method is that it will “open” and “close” the database connection for you as part of its normal operation.  However, if the database connection is already open, you need to make sure that you also close it manually to save system resources.

$DataAdapter = new-object System.Data.SqlClient.SqlDataAdapter $SqlCommand
$dataset = new-object System.Data.Dataset
$DataAdapter.Fill($dataset)

When these last lines are run, Powershell will return a number which is the number of records that have been returned from the database.  In my testing, this returned 89 records from the database.

Now the $dataset variable has one table with the information requested.  To get to this information, you can access it by calling the first table in the dataset.  Remember that the tables are numbered ordinally and start at “0.”

$dataset.Tables[0]

And you should get data returned that looks like this:

CompanyName                             ContactName                             Phone
-----------                             -----------                             -----
Alfreds Futterkiste                     Maria Anders                            030-0074321
Ana Trujillo Emparedados y helados      Ana Trujillo                            (5) 555-4729
Antonio Moreno Taquería                 Antonio Moreno                          (5) 555-3932
Around the Horn                         Thomas Hardy                            (171) 555-7788
Berglunds snabbköp                      Christina Berglund                      0921-12 34 65
Blauer See Delikatessen                 Hanna Moos                              0621-08460
Blondesddsl père et fils                Frédérique Citeaux                      88.60.15.31
Bólido Comidas preparadas               Martín Sommer                           (91) 555 22 82
Bon app'                                Laurence Lebihan                        91.24.45.40
Bottom-Dollar Markets                   Elizabeth Lincoln                       (604) 555-4729
B's Beverages                           Victoria Ashworth                       (171) 555-1212
Cactus Comidas para llevar              Patricio Simpson                        (1) 135-5555
Centro comercial Moctezuma              Francisco Chang                         (5) 555-3392
Chop-suey Chinese                       Yang Wang                               0452-076545
Comércio Mineiro                        Pedro Afonso                            (11) 555-7647
Consolidated Holdings                   Elizabeth Brown                         (171) 555-2282
Die Wandernde Kuh                       Rita Müller                             0711-020361
Drachenblut Delikatessen                Sven Ottlieb                            0241-039123
Du monde entier                         Janine Labrune                          40.67.88.88
Eastern Connection                      Ann Devon                               (171) 555-0297
Ernst Handel                            Roland Mendel                           7675-3425
Familia Arquibaldo                      Aria Cruz                               (11) 555-9857
Folies gourmandes                       Martine Rancé                           20.16.10.16
Folk och fä HB                          Maria Larsson                           0695-34 67 21
France restauration                     Carine Schmitt                          40.32.21.21
Franchi S.p.A.                          Paolo Accorti                           011-4988260
Frankenversand                          Peter Franken                           089-0877310
Furia Bacalhau e Frutos do Mar          Lino Rodriguez                          (1) 354-2534
Galería del gastrónomo                  Eduardo Saavedra                        (93) 203 4560
Godos Cocina Típica                     José Pedro Freyre                       (95) 555 82 82
Gourmet Lanchonetes                     André Fonseca                           (11) 555-9482
Great Lakes Food Market                 Howard Snyder                           (503) 555-7555
GROSELLA-Restaurante                    Manuel Pereira                          (2) 283-2951
Hanari Carnes                           Mario Pontes                            (21) 555-0091
HILARION-Abastos                        Carlos Hernández                        (5) 555-1340
Hungry Coyote Import Store              Yoshi Latimer                           (503) 555-6874
Hungry Owl All-Night Grocers            Patricia McKenna                        2967 542
Island Trading                          Helen Bennett                           (198) 555-8888
Königlich Essen                         Philip Cramer                           0555-09876
La corne d'abondance                    Daniel Tonini                           30.59.84.10
La maison d'Asie                        Annette Roulet                          61.77.61.10
Laughing Bacchus Wine Cellars           Yoshi Tannamuri                         (604) 555-3392
Lazy K Kountry Store                    John Steel                              (509) 555-7969
Lehmanns Marktstand                     Renate Messner                          069-0245984
Let's Stop N Shop                       Jaime Yorres                            (415) 555-5938
LILA-Supermercado                       Carlos González                         (9) 331-6954
LINO-Delicateses                        Felipe Izquierdo                        (8) 34-56-12
Lonesome Pine Restaurant                Fran Wilson                             (503) 555-9573
Magazzini Alimentari Riuniti            Giovanni Rovelli                        035-640230
Maison Dewey                            Catherine Dewey                         (02) 201 24 67
Mère Paillarde                          Jean Fresnière                          (514) 555-8054
Morgenstern Gesundkost                  Alexander Feuer                         0342-023176
North/South                             Simon Crowther                          (171) 555-7733
Océano Atlántico Ltda.                  Yvonne Moncada                          (1) 135-5333
Old World Delicatessen                  Rene Phillips                           (907) 555-7584
Ottilies Käseladen                      Henriette Pfalzheim                     0221-0644327
Pericles Comidas clásicas               Guillermo Fernández                     (5) 552-3745
Piccolo und mehr                        Georg Pipps                             6562-9722
Princesa Isabel Vinhos                  Isabel de Castro                        (1) 356-5634
Que Delícia                             Bernardo Batista                        (21) 555-4252
Queen Cozinha                           Lúcia Carvalho                          (11) 555-1189
QUICK-Stop                              Horst Kloss                             0372-035188
Rancho grande                           Sergio Gutiérrez                        (1) 123-5555
Rattlesnake Canyon Grocery              Paula Wilson                            (505) 555-5939
Reggiani Caseifici                      Maurizio Moroni                         0522-556721
Ricardo Adocicados                      Janete Limeira                          (21) 555-3412
Richter Supermarkt                      Michael Holz                            0897-034214
Romero y tomillo                        Alejandra Camino                        (91) 745 6200
Santé Gourmet                           Jonas Bergulfsen                        07-98 92 35
Save-a-lot Markets                      Jose Pavarotti                          (208) 555-8097
Seven Seas Imports                      Hari Kumar                              (171) 555-1717
Simons bistro                           Jytte Petersen                          31 12 34 56
Spécialités du monde                    Dominique Perrier                       (1) 47.55.60.10
Split Rail Beer & Ale                   Art Braunschweiger                      (307) 555-4680
Suprêmes délices                        Pascale Cartrain                        (071) 23 67 22 20
The Big Cheese                          Liz Nixon                               (503) 555-3612
The Cracker Box                         Liu Wong                                (406) 555-5834
Toms Spezialitäten                      Karin Josephs                           0251-031259
Tortuga Restaurante                     Miguel Angel Paolino                    (5) 555-2933
Tradiçao Hipermercados                  Anabela Domingues                       (11) 555-2167
Trail's Head Gourmet Provisioners       Helvetius Nagy                          (206) 555-8257
Vaffeljernet                            Palle Ibsen                             86 21 32 43
Victuailles en stock                    Mary Saveley                            78.32.54.86
Vins et alcools Chevalier               Paul Henriot                            26.47.15.10
Wartian Herkku                          Pirkko Koskitalo                        981-443655
Wellington Importadora                  Paula Parente                           (14) 555-8122
White Clover Markets                    Karl Jablonski                          (206) 555-4112
Wilman Kala                             Matti Karttunen                         90-224 8858
Wolski  Zajazd                          Zbyszek Piestrzeniewicz                 (26) 642-7012

Now, this is great to be able to see this information on the screen, but most likely, you need to have this in a file.  Enter the Export-CSV cmdlet which will export the data in a comma-separated file that you can manipulate in Excel.

$dataset.Tables[0] | Export-CSV customers_with_orders.csv

There are many applications that you could use this script for.  And the possibilities for dealing with the data after you extract are numerous as well.  Hopefully, this will help someone to gather information that they need from a database without having to necessarily load up SQL Management Studio or request the information from a SQL DBA.  All you need for this is network connectivity and at least read access to the database.

Here are some resources for additional information on the .NET Framework objects that were used.

System.Data.SqlClient.SqlConnection
System.Data.SqlClient.SqlDataAdapter
System.Data.DataSet

Advertisements
This entry was posted in SQL, SqlClient and tagged , , , , , , . Bookmark the permalink.