Accessing SQL Server Data from PowerShell, Part 2

As I discussed in “Accessing SQL Server Data from PowerShell, Part 1,” you can leverage the Microsoft .NET object model within Windows PowerShell scripts because PowerShell is integrated with the .NET framework. As a result, you can also build ADO.NET objects that retrieve data in a SQL Server database.

In this article, I explain how to use ADO.NET to insert, update, and delete SQL Server data. When you update a SQL Server database, you can take one of two basic approaches. You can use the connected ADO.NET classes to update the data, or you can update the data through the disconnected classes and then propagate those changes to the database. I present several methods for modifying data and for running stored procedures.

As you read this article, you might find it useful to reference the .NET Framework class library at the MSDN website for specific information about the ADO.NET classes. For more information about PowerShell and the Microsoft .NET Framework, see the Learning Path.

Modifying Data Through the Connected Classes

First, let’s look at how to use the connected ADO.NET classes to modify data. To follow my examples in this article, run the code in Listing 1. This code creates and populates the Production.Inventory table in the AdventureWorks sample database. In addition, Listing 1 includes the definition for the DecreaseInventory stored procedure that I use later in the article.

Listing 2 contains a PowerShell script that inserts a row of data in the Inventory table. You should recognize most of the components of this script from “Accessing SQL Server Data from PowerShell, Part 1.” The script uses the Read-Host cmdlet to prompt the user for the product name, product code, and quantity, which are saved to the $ProductName, $ProductCode, and $Quantity variables, respectively.

Next, the New-Object cmdlet creates a SqlConnection object, defines the ConnectionString property, and opens the connection. Then the script creates the SqlCommand object and defines the CommandText property. Note that the command text is an INSERT statement that passes in three parameters (@ProductName, @ProductCode, and @Quantity) as the values. The parameters are created later in the script. When passing ADO.NET parameters into the T-SQL statement, you precede the parameter name with the @ symbol. (For more information about T-SQL statements, see SQL Server Books Online.) After the CommandText property is set, the script sets the Connection property to the SqlConnection object ($con).

The script creates the SqlParameter objects by using the AddWithValue method available to the Parameters collection (which is associated with the SqlCommand object). When the script calls the method, it passes in two arguments—the name of the parameter and the name of the variable that contains the value to be passed into the parameter.

If you need more details about any of the elements used so far to set up a SqlCommand object, refer back to “Accessing SQL Server Data from PowerShell, Part 1” or to the .NET Framework class library. Otherwise, you’re ready to run SQL commands.

To run an action query (i.e., a query that inserts, updates, or deletes data), you can simply call the SqlCommand object’s ExecuteNonQuery method. This method runs the query and returns the number of rows that have been updated in the database. The script in Listing 2 saves the value returned by the method to the $RowsInserted variable, which is then displayed in the console. The script then closes the SQL Server connection.

When you run the script (and supply the necessary parameter values), a row is inserted in the Inventory table. Note, however, that the example in Listing 2, as well as other examples in this article, do not include the type of exception handling you would normally want to include in your scripts. The examples shown here are merely meant to demonstrate the basic concepts necessary to create PowerShell scripts that let you modify data in a SQL Server database.