PowerShell: DrillBits

- [Voiceover] Windows PowerShell doesn't have any native ways to access a database, but it can access the underlining dot net framework classes so that it can retrieve information from and update information in a database. One of the first things you'll have to do is figure out what your server name is that has your database and that's relatively straight forward. But it's possible for a server to run more than one instance or more than one copy of Sequel server. So you'll need to know the instance name if the one you wanna connect to isn't the default one for the server.

One of the easiest ways to get that information is to go to the server and run get service. You'll spin back up to the M's, we're looking for Microsoft here, and you should eventually find something that says MSSQL, the dollar sign starts the beginning of the instance name. So this computer has an instance of Sequel Server running and the instance name is Sequel express. If you've installed the express edition of Sequel by default it does install itself to a Sequel express. So that will often be the case if that's the version of Sequel server that you're using.

Once you have that information you'll need to construct what's called a connection string. This is basically a set of directions that tells the dot net framework where the server is, what instance you want to connect to, and how you wanna log on and which database you want to use. I can never remember the connection strings and no one can, which is why there is connection strings dot com. Here you can see there are many different types of databases supported. Since I'm using Sequel 2008, we'll hop over here. You're going to be looking for the dot net framework data provider for Sequel server.

And this is an example of what you'll need. You can see that there's a few pieces of information in here that you'll need to change. This is the name or address of your server and that will have a backslash with the instance name if you're not connecting to the default. Initial Catalog is the database you wanna connect to. And user ID and password are fairly straight forward. If you decide that you're going to use integrated security, meaning you're gonna rely on your Windows credentials to authenticate you, that's the trusted connection version.

So again, you'll provide the server address and the initial database, but then simply tell it that integrated security equals SSPI. You won't provide a username or password instead Sequel will authenticate you based on the credentials that you used to log on to Windows. So now you've got your server name, you've got your instance name, and you have your connection string. I find that the easiest way to use databases is to get some functions that sort of build everything in together. And so that's what I've done here.

This first function is called "Get Database Data." You can see that it accepts three parameters: minus connection string, minus query, which is the Sequel server language query you want to execute, and then a switch, you'll specify minus is Sequel Server if you are in fact connecting to Sequel Server. This function is capable of connecting to other types of databases as well and rather than using the Sequel client it uses a OleDb client. So if you are connecting to Sequel Server you would specify minus is Sequel Server.

And we'll see an example of this function in use in just a moment. Also provide a second function called "Invoke Database Query." So the first function is used when all you're doing is retrieving data from Sequel Server. This function is used when you wanna change something. So you're executing a update, a delete, or a insert query. The parameters are the same. Connection string, query, and if it is a Sequel Server, minus is Sequel Server. Let's take a look at some of these things in action.

I've created a couple of functions here that will talk to a database, retrieve information from them, write information back to them, and so forth. Here's the first one. "Get DJ Computers From Sequel." This is designed to retrieve information from Sequel. All you have to do is give it the Sequel Server instance name, the name of the database, the name of the table, and the name of a column that has a computer name in it. You'll see here where it actually uses that database function I created. "Get Database Data" it passes along the connection string sends it to query that I just constructed here in text and specifies minus is Sequel Server.

To see an example of updating a database just need to scroll down a little bit to set DJ Computers in Sequel. Again, there's a bunch of parameters that pass in data. It creates a connection string in text then it creates a query in text and here's where it uses the "Invoke Database Query" function passing along a connection string specifying that it is a Sequel Server and specifying the query that needs to be executed. Once you have functions like this that sort of wrap up all the difficulty of talking to a database it's fairly easy to use.

So long as you understand kind of the connection string issues and you understand enough of the Sequel language to write the query that you need. These functions make it easier to actually execute those queries and get results back.