Accessing SQL Server Data from PowerShell, Part 1

Because Windows PowerShell is integrated with the Microsoft .NET framework, you can leverage the .NET object model within PowerShell scripts. An important advantage is the ability to use ADO.NET to access various types of data sources, including SQL Server. Building ADO.NET objects within a script lets you retrieve data from a SQL Server database, update that data, insert new data, or delete existing data. 

Related: Accessing SQL Server Data from PowerShell, Part 2

In this article, which is the first of two parts, you’ll learn how to use ADO.NET to retrieve SQL Server data through PowerShell scripts. In Part 2, you’ll learn how to modify that data. As you work through this article, you might find it useful to reference the .NET Framework class library at the MSDN website. The class library provides specific information about each of the ADO.NET objects (described in the following sections), including details about the objects’ methods and properties. Note that this article assumes that you’re already familiar with how to create PowerShell scripts and that you have a general understanding of the .NET Framework and SQL Server databases. (If you’re new to PowerShell, see the PowerShell 101 series in Windows IT Pro.) 

The ADO.NET Object Model

ADO.NET is a set of class libraries that are part of the .NET Framework. The ADO.NET classes are generally divided into two types: connected classes and disconnected classes. The connected classes are those that are part of a namespace specific to a data source type. For example, the ADO.NET connected classes associated with SQL Server are part of the System.Data.SqlClient namespace. You use the connected classes to manage your connections to the SQL Server database and to access data in that database.  The disconnected classes are part of the System.Data namespace and are independent from any data source. You use the disconnected classes to work with the data after it has been retrieved by the connected classes.

The disconnected classes never communicate directly with a data source. Figure 1 shows the more commonly used classes available in the System.Data.SqlClient and System.Data namespaces. The System.Data.SqlClient namespace includes the following connected classes specific to SQL Server:

The System.Data namespace includes the following disconnected classes:

In general, the disconnected objects act as an offline data cache for the data you retrieve through your connected objects. As a result, you can view and modify the data in a dataset without being connected to the data source. (You can even populate a dataset with data from other sources, such as an object array, but this approach is beyond the scope of this article.) Now that you have an overview of the primary ADO.NET objects, let’s look at some examples that demonstrate how to use these objects in a PowerShell script. As you work though the examples, refer back to this section and to Figure 1 as necessary to provide a context that will help you understand how the objects are being used. 

Retrieving Data Through a SqlDataReader Object

The first example (Listing 1) demonstrates how to create SqlConnection, SqlCommand, and SqlDataReader objects. The script begins with the SqlConnection object. (Note that all the example scripts in this article were developed to be run as script files—i.e., text files with a .extension—that are called in the PowerShell console. To run a script in the console, you simply call the filename at the command prompt. However, you must be sure that your execution policy is configured to permit running scripts. For information about execution policies, see the help topic about_signing.) 

To create an object in PowerShell, use the New-Object cmdlet and specify the class on which the object is based. The class name should include the namespace along with the name itself, as in System.Data.SqlClient.SqlConnection. In this example, I assign the new object to the $con variable.  

Next, I use the variable to access the SqlConnection object’s ConnectionString property, which specifies the information necessary to connect to SQL Server and the target database. The connection string first specifies the SQL Server instance (Server=.) The period represents the local server, so the connection will be to the local instance of SQL Server. The second argument in the connection string specifies the database (Database=AdventureWorks). For the examples in this article, I use the AdventureWorks sample database. (If you didn’t install the AdventureWorks database when you set up SQL Server, you can download it from CodePlex. This page includes a link to simplified instructions for manually attaching the database.) The last argument in the connection string specifies that integrated security should be used (Integrated Security=true).  

After you define your connection string, use the SqlConnection object’s Open method to open the connection to the database. You don’t need to pass any arguments along with the method. 

The next part of the script (after you open the connection) creates and defines a SqlCommand object. Once again, you specify the New-Object cmdlet along with the class (System.Data.SqlClient.SqlCommand), then assign the object to a variable (in this case, $cmd). I then use $cmd to assign a value to the object’s CommandText property. The value is the T-SQL statement that will run against the target SQL Server database. In Listing 1, I use a SELECT statement to retrieve data from the Person.Contact table. (For information about T-SQL statements, refer to SQL Server 2005 Books Online.) After you define the command text, set the Connection property to the SqlConnection object ($cmd.Connection = $con). 

Now you’re ready to create the SqlDataReader object. However, rather than use the New-Object cmdlet to create the object, you must use the SqlCommand object’s ExecuteReader method. When you call this method, it automatically creates the SqlDataReader object based on the SqlCommand object’s properties. You then assign the SqlDataReader object to a variable (in this case, $dr), and use that variable to view the data retrieved from the database. 

To view the data, I created an If statement that uses the SqlDataReader object’s HasRows property to determine whether the result set includes any data. If it does, I first use the FieldCount property to return the number of fields. I then create a While statement and use the Read method to read the data in the result set. As long as there is data in the result set, the While statement will loop through the result set and return the current row. When the While statement reaches the end of the result set, it will break out of its loop.  

For each row in the result set, I use the Write-Host cmdlet to write the data from the FirstName and LastName fields to the PowerShell console. As you can see, to call the field, I specify the $dr variable and the field name (quoted and in brackets), as it is returned by the T-SQL statement. 

At the end of the script, after the data displays, the Close method closes the data reader as well as the connection. When you run this script, the console will display the first and last name of the contacts retrieved from the database. Note that the Close method is equivalent to calling the Dispose method. Using one of these methods is important (even with .NET’s automatic garbage collection) to make sure you close all your connections and release the memory. 

Retrieving Data Through a DataSet Object

As you saw in Listing 1, the SqlDataReader object provides a quick and easy way to view data in a result set. However, a data reader supports only limited functionality. You can’t perform such operations as moving backward through the data or updating the data. As a result, for most operations, you need to work with the data within a dataset. 

To create a dataset, you must first create a SqlDataAdapter object, as shown in Listing 2. As with the previous example, I first create a SqlConnection object and then a SqlCommand object. However, rather than creating a SqlDataReader object, I create a SqlDataAdapter object, which acts as an interface between the connected objects and the disconnected objects. 

When you create the SqlDataAdapter object, assign it to a variable (in this case, $da), then set the object’s SelectCommand property to the SqlCommand variable ($cmd). Note that if the T-SQL statement is a different type of command, you’d use a different property. For example, if the command is an UPDATE statement, I’d use the UpdateCommand property. 

After you define the SqlDataAdapter object, you create the DataSet object and assign it to a variable ($ds). You then use the SqlDataAdapter object’s Fill method to populate the dataset with the data retrieved from the database. When you call the Fill method, specify the DataSet object as the first argument and, optionally, a name for the table as the second argument. In this case, I named the table “Contacts.” By specifying a name for the table within the dataset, you can reference that name later in your script, rather than by an index number.

Notice that after I call the Fill method, I pipe the results to the Out-Null cmdlet. If I didn’t do this, the results displayed in the console would also include the number of rows contained in the dataset. Also notice that, after I fill the dataset, I close the connection. One of the biggest advantages of ADO.NET is that you can work with datasets offline, without being connected to the data source. 

After you populate your dataset, you’re ready to work with that data. In Listing 2, I use a foreach statement to display each row. In the foreach collection expression ($ds.tables\[“Contacts”\].rows), I reference the Contacts table within the dataset by calling the dataset’s Tables collection and specifying the name of the table (quoted and in brackets). Because I want to access each row in the dataset, I also call the Rows collection. I then use the $row variable in the foreach loop to reference the individual rows. (The $row variable is actually a DataRow object. Refer back to Figure 1.) For each row, I specify the FirstName and LastName fields, as in $row.FirstName. When you run this script, the console will display the first and last name of the contacts in the dataset. 

As you’ve seen, when working with a dataset, you access the dataset’s tables through the Tables collection, which can contain one or more tables. In Listing 2, the dataset contains only one table. However, if you refer to Listing 3, you’ll see that you can easily add multiple tables to a dataset. 

To add multiple tables, create a SqlCommand object for each SELECT statement. For example, In Listing 3, I retrieve data from the Person.Contact table and from the HumanResources.vEmployee view. I then call the SqlDataAdapter object’s Fill method twice. The first time, I retrieve data from the Contact table and save it to the dataset as “Contacts.” The second time, I retrieve data from the vEmployee view and save it to the dataset as “Employees.” I can then access the individual dataset tables by referencing the table name in the Tables collection. 

For each table in a dataset, you can access information about that table. For example, in Listing 4, I add data from the Contact table and vEmployee view to create two tables in the dataset, as I did in Listing 3. However, rather than displaying the data within each dataset table, I display information about the tables themselves. In this case, I create a foreach statement that loops through each table in the dataset’s Tables collection. For each table, I use the $table variable to display the value of the TableName property. I then include an inner foreach statement that retrieves column information (from the Columns collection) for the current table. For each column (referenced through the $column variable), I display the values for the ColumnName, DataType, AllowDBNull, Unique, and ReadOnly properties. 

Retrieving Data Through a DataTable Object

In Listing 2, Listing 3, and Listing 4, I create a DataSet object and use the Fill property to fill a specific table in the dataset. However, if your dataset will include only one table, you can simply create a DataTable object. In Listing 5, I create the DataTable object and assign it to $dt. I then call the SqlDataAdapter object’s Fill method and specify $dt as the argument. Next, I use a foreach statement to access the Rows collection through the DataTable object. As you can see, this approach can simplify your code if you’re working with an individual table. 

There are other ways you can simplify your code as well. For example, if you’re making a single connection to a database and you aren’t using any special settings on the SqlCommand object, you don’t need to specifically create SqlConnection or SqlCommand objects. Instead, you can simply define the connection string and the command text, as I’ve done in Listing 6, and create a SqlDataAdapter object. However, unlike when I created the object in earlier examples, I now pass the command text and the connection string as arguments to the SqlDataAdapter class. Notice how this simplifies the script. Of course, if you need to make multiple calls to the database, you’re probably better off defining a single SqlConnection object. And, if you need more control over your command, as with some parameterized queries (as you’ll see in the next section), you should create the necessary SqlCommand objects. 

Defining a Parameterized Query

Up to this point, the examples that we’ve looked at have been based on SELECT statements that include no parameters. In other words, the SELECT statements retrieve the same data no matter how many times you run the script. However, in the real world, you’ll likely have to pass one or more parameters into your T-SQL statements to retrieve the data you need. 

When working with ADO.NET, there are a couple approaches that you can take to create parameterized queries. The first is simply to create a parameter in your script (as in Listing 7) and reference that parameter in your SELECT statement. In this example, I create the $ContactID parameter, assign it a default value of 1, and include the parameter name in the SELECT statement’s WHERE clause. Next, I create a SqlDataAdapter object as I did in Listing 6 and use the Fill method to populate a DataTable object. When you run the script, you simply specify a parameter value. 

Another approach you can take when working with parameterized queries is to create a SqlCommand object and add the parameter to the object’s Parameters collection. For example, in Listing 8, I first define the connection string and command text. Notice that the parameter name in the command text is preceded with the at (@) symbol rather than a dollar sign (as is typically used with PowerShell parameters). The @ symbol tells PowerShell to use the ADO.NET parameter that is part of the Parameters collection. 

Next, I create a SqlCommand object and pass in as arguments the command text and the connection string. This approach to creating a SqlCommand object is a little different from previous examples, but it achieves the same results. I take this approach in this case to help demonstrate the various methods that you can use when working with ADO.NET. 

After you create your SqlCommand object, you can use the AddWithValue method to add the parameter. The AddWithValue method is a method available through the SqlParametersCollection object. When you call the Parameters collection through the SqlCommand object, you are actually accessing the object’s Parameters property, which in turn calls the SqlParametersCollection object associated with that SqlCommand object. 

As arguments to the AddWithValue method, specify the name of the parameter (@ContactID) and the parameter’s value, which in this case is the name of a PowerShell variable ($ContactID). If you refer to the beginning to the script in Listing 8, you’ll see that I use a Read-Host cmdlet to prompt the user for an ID. When you run the script, you must provide a ContactID value, or the script will fail. 

Working with XML Data

When ADO.NET was designed, it was designed with XML in mind. As a result, you can perform such actions as displaying dataset data as XML, saving dataset data to an XML file, or retrieving XML data into an XML data document. 

Displaying dataset data as XML is a very straightforward process. After you create your dataset, you call the DataSet object’s GetXml method, as shown in Listing 9. When you call this method, PowerShell returns the data with NewDataSet as the root element and the table name as the second-level element. Figure 2 shows a sample of the results returned by the script in Listing 9. Notice that beneath each table-specific element (the Employees elements), there is an element for each column. 

You can also save the data in a dataset to an XML file by using the DataSet object’s WriteXML method, as shown in Listing 10. When I call the method, I pass the path and filename as the first argument and WriteSchema as the second argument, which indicates that the dataset will be written with the inline XSD schema. If you don’t specify this option, no schema information is written to the file. (Note that if you plan to run this script, you must first create the C:Info folder or modify the script to point to an existing folder.) 

Now let’s take a look at how to retrieve data from an XML column in SQL Server. When you retrieve this data, you first create an XmlReader object, then create an XmlDataDocument object. Listing 11 provides an example of how to do this. Notice that first I create the XmlReader object by calling the SqlCommand object’s ExecuteXmlReader method. When you call this method, an XmlReader object is created based on the SqlCommand object’s properties. I then assign this object to the $xr variable. 

Next, I create the XmlDataDocument object, which lets me store, retrieve, and manipulate XML data. The XmlDataDocument object is part of the System.Xml namespace (rather than one of the namespaces we’ve worked with up to this point). I then assign this object to the $xd variable. Next, I call the object’s load method and pass in the XmlReader as an argument. This loads the XmlReader data into the XmlDataDocument object.  

After you’ve loaded the XML data, you can then access the data in the XmlDataDocument object. In Listing 11, for example, I use a foreach statement to access several attribute values in each Location element in the root node. The foreach collection expression ($xd.root.Location) references the Location element by calling the XmlDataDocument object ($xd), followed by root, and then the name of the element. I then use the $node variable to access the individual attributes within Location. For each of the Location elements, I retrieve the values for the LocationID, LaborHours, MachineHours, and SetupHours attributes.  

More to Come

You can do far more with XML data than what I’ve shown you here. In fact, I’ve only scratched the surface of the power of ADO.NET for retrieving SQL Server data. However, this article will give you a basic understanding of how to use ADO.NET within PowerShell. In Part 2, you’ll learn how to use ADO.NET to modify SQL Server data. In the meantime, refer to MSDN as necessary for specific details about each of the ADO.NET objects.