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.