The title couldn’t be more explanatory. During this post, we’ll see how we can access the database, and select data, by PowerShell.
Let me try to guess…
But why!?
Well, besides for pure thirst for knowledge of how we can do fancy things :D, could pass through, by using this way of access, to store data through a PowerShell script, like, log entries storage, metrics from system, display the dataset results on a html page, etc.
First things first, so to be able to communicate with the aimed database we need to establish one connection, and for that, I’ll use the following lines…
Connecting to the aimed SQL database through System.Data.SqlClient DLL (.SqlConnection)
A brief explanation of what has been made so far.
On the first line, I’ve built my connection string, adding my SQL server and the database that I’m trying to establish my connection. The server can be easily found by running Microsoft SQL Server Management Studio and on the view tab by selecting registered servers, or by querying the function @@ServerName.
SELECT @@SERVERNAME AS ‘Server Name’
Afterward, I’m starting to instantiate the DLL that will be used to perform the connection. In the above example, we are using the DLL System.Data.SqlClient, after being instantiated will grab the connection string and open/close the communication.
And that’s it, your connection was established and closed (if you gave it a try to run the script shared so far). Now it’s time to get something from this connection. Let’s select some data from our Database.
The query that I’ll within on my PowerShell script is the following:
SELECT * FROM customer WHERE CountryCode = ‘FR’
Do you remember that I inserted this record in this blog post? Looks like I was guessing that he would be elected for the second term of France’s presidency. I’m feeling like an oracle… Do you want to know anything about your future!? 
Time to pass this query to our PowerShell script. For that I’ve used the following lines of code:
Command that will run on SQL database through System.Data.SqlClient DLL (.SqlCommand)
A little bit of context of what has been made above. So, we’re still using the System.Data.SqlClient but this time, rather than be establishing the connection, we’re instancing the command that will run on our pre-established connection and store the result dataset on a PowerShell object named $reader.
As the last step, we need to ensure that we loop our dataset and print the data returned from our SQL Command. For that I’ve used the following lines of code:
Looping through the Data Set
Let’s see what our script returns after being executed:
Our entire script
Now, let’s pursue excellence… Let’s build a PowerShell function with the above lines, and from that point on, you’ll be able to use parameters that, for example, will get different dataset results based on filter switching. If you’ve doubts of how to do it, please jump to the following blog post where you’ll get useful hints of the steps to follow.
Our Get-CustomersFromSampleDB PowerShell function
Let’s invoke the function, once, without parameters, and see what we get.
Get-CustomersFromSampleDB
And now by using a country code as a parameter…
Get-CustomersFromSampleDB -CountryCode ‘PT’
And I’m happy with what has been achieved. A simple example of how you can connect to your database and get data from it.
I hope that helped! Stay safe!
Top comments (0)