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. 

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 (http://msdn2.microsoft.com/en-us/library/ms229335.aspx). 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 series begins with Lesson 1—InstantDoc ID 97742.) 

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:

• SqlConnection—Connects to the SQL Server .NET data provider in order to establish and manage the connection to the target database.
• SqlCommand—Contains the details necessary to issue a T-SQL command against a SQL Server database.
• SqlParameterCollection—Contains the collection of SqlParameter objects associated with a specific SqlCommand object. You access the collection through the SqlCommand object’s Parameters property.
• SqlParameter—Contains parameter-related information specific to a SqlCommand object.
• SqlDataReader—Provides efficient read-only access to the data retrieved through the SqlConnection and SqlCommand objects. The SqlDataReader is similar to a forward-only cursor.
• SqlDataAdapter—Provides a bridge between the connected classes and disconnected classes. This class includes the Fill and Update methods. Use the Fill method to populate a DataSet or DataTable object. Use the Update method to propagate updated data in a DataSet or DataTable object to the database.

The System.Data namespace includes the following disconnected classes:

• DataSet—Contains all the data retrieved through your connected objects. The DataSet object acts as a container for all DataTable objects and provides functionality that lets you work with the data in all the tables as single operations (such as saving data to a file).
• DataTableCollection—Contains the collection of DataTable objects associated with a specific DataSet object. You access the collection through the DataSet object’s Tables property.
• DataTable—Stores the data returned by your query. The data is stored in rows and columns, similar to how data is stored in a database table.
• DataColumnCollection—Contains the collection of DataColumn objects associated with a specific DataTable object. You access the collection through the DataTable object’s Columns property.
• DataColumn—Contains the metadata that describes the columns associated with a specific table. A DataColumn object doesn’t contain the stored data itself, only information about the column structure. The stored data is saved to DataRow objects.
• DataRowCollection—Contains the collection of DataRow objects associated with a specific DataTable object. You access the collection through the DataTable object’s Rows property.
• DataRow—Contains the actual data that is retrieved through your connected objects. Each DataRow object contains the data from one row of your query results.

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.