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…
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…
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.
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:
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:
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:
Let’s see what our script returns after being executed:
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.
Let’s invoke the function, once, without parameters, and see what we get.
And now by using a country code as a parameter…
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!