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.
After you run the script in Listing 2, you should run the following query in SQL Server Management Studio (SSMS) to verify that the new data was added:
SELECT * FROM Production.Inventory WHERE ProductName = ''
where ProductName is the name of the product that you provided when you ran the script. After you run this query, note the ProductID; you might want to use this information when you run the scripts in Listing 3 and Listing 4.
Now let’s take a look at how to update data. As in Listing 2, the script in Listing 3 creates and defines a SqlConnection object and a SqlCommand object. However, in this case, the script assigns an UPDATE statement to the CommandText property (rather than an INSERT statement, as in Listing 2). Note that the SET clause includes the @Amount parameter, which determines the amount to decrease the Quantity value by, and the WHERE clause includes the @ProductID parameter, which specifies which row to update. The script then creates the SqlParameter objects by using the AddWithValue method available to the Parameters collection.
As you can see, there is little difference between inserting data and updating data through the SqlCommand object. The key is to pass the correct parameter values into the action query that is defined as part of the SqlCommand object. When you run the script in Listing 3, use the ProductID that you retrieved when you ran the script in Listing 2. After you run the script in Listing 3, run the T-SQL statement again to verify that the Quantity value was decreased by the correct amount.
The process for deleting data is very similar, as you can see in Listing 4. This script sets the CommandText property to a DELETE statement that removes data from the Inventory table. Note that the WHERE clause specifies which row should be deleted, based on the value of the @ProductID parameter. Again, use the ProductID value that you retrieved from Listing 2 when you run Listing 4.
Now that you know how to run action queries and update the database through the connected classes, let’s discuss how to run a stored procedure.
Running a Stored Procedure
In many production databases, a common practice is to make all (or most) data modifications through stored procedures. As a result, a script must call the stored procedure and pass in the necessary parameter values. Two approaches that you can take when using ADO.NET to call a stored procedure are to run a T-SQL EXECUTE (EXEC) statement or to call the procedure directly.
To run an EXECUTE statement, you set the SqlCommand object’s CommandText property to that statement, as you do with action queries. Listing 5 demonstrates how this is done. Note that the CommandText property includes the EXECUTE statement, which consists of the EXEC keyword, the name of the stored procedure (DecreaseInventory), and the parameters that should be passed to the stored procedure. (Listing 1 includes the stored procedure definition. If you ran all the T-SQL statements in Listing 1, the stored procedure was already created.) To run the script in Listing 5, use the ProductID value of 1, with a Quantity value of 1085. After you run the script, query the Inventory table in SSMS to verify that the Quantity value was decreased by the amount you specified. As with the previous examples, the key is to ensure that you pass in the correct parameter values. Otherwise, there is little difference between this listing and the others, except for the T-SQL statement itself.
The second approach to running stored procedures is to call the stored procedure directly, as in Listing 6, without creating an EXECUTE statement. You’ll notice several differences in the code in Listing 6 from what you’ve seen in the previous examples. To begin with, when you set the CommandText property, you simply provide the name of the stored procedure, which in this case is DecreaseInventory. You don’t include any other information.
In addition, when defining the SqlCommand object, you must also set the object’s CommandType property to StoredProcedure. After you set the property, you can define your parameters. As in the previous examples, you can use the AddWithValue method to create and define your SqlParameter objects. However, in this case you must take an additional step—specify the parameter’s direction by setting the parameter’s Direction property. Because the parameters are input parameters, I set the property to Input, as the code in Listing 6 shows.
After you’ve set the CommandText, CommandType, and Direction properties to call the stored procedure directly, you’re ready to execute the query as you did in earlier examples—by calling the SqlCommand object’s ExecuteNonQuery method. By taking this approach, you don’t have to explicitly define the T-SQL statement. ADO.NET does that for you. (Again, to run this script, use the ProductID value of 1, then run a T-SQL query to verify the changes.)
Modifying Data Through the Disconnected Classes
One of the biggest advantages of using ADO.NET is the ability to work with datasets offline, without being continuously connected to the database. This means you can retrieve the data you want to view, disconnect from the database, update that data as necessary, and then, after you’ve completed your modifications, propagate the changes back to the database.
When modifying data through disconnected objects, the first step is to retrieve the data that you want to include in the DataSet or TableSet object. After you view the data, you can make your updates directly to the data, then use the SqlDataAdapter object’s Update method to make the changes to the database.
The code in Listing 7 demonstrates this procedure, which starts with data insertion. As in the previous examples, this script createsthe SqlConnection object, defines the connection string, and opens the connection. Next, the script defines the SELECT statement necessary to retrieve the data that will be included in the dataset. Finally, the script creates the SqlDataAdapter object and passes in the SqlConnection object and the command.
After the SqlDataAdapter object is returned, the script creates a DataTable object to hold the data retrieved through the SELECT statement. To add the data to the DataTable object, the script uses the SqlDataAdapter object’s Fill method and passes in the DataTable object ($dt) as an argument. You can find more details about each of these steps in “Accessing SQL Server Data from PowerShell, Part 1.”
It might seem odd to retrieve data if you plan only to insert a row, and in fact it’s not necessary to do so. However, there are a couple reasons you might want to retrieve the data. First, if you want to display the data in your application before the user adds to the data, you need to retrieve the recordset to display it. Also, if you plan to include multiple operations in your script, such as updates and deletions, along with insertions, you should retrieve the data first so that you need to make only one call to the database. All the operations for these examples are separate in the script in Listing 7 so you can clearly see how to perform each one—but the ability to work with data offline is ideal for combining operations on a single dataset.
After the script in Listing 7 populates the DataTable object, it displays the data in the console. Then you can begin the actual updates. The first step is to create a DataRow object for the data to be inserted. For each column in the new row, the script uses the Read-Host cmdlet to prompt the user for the necessary value. Note that in this case, you must explicitly convert the $Quantity value to an integer by preceding the variable name with \\[int\\]. Otherwise, the script will throw an exception when it triesto update the database. (The Read-Host cmdlet returns a string value. In the earlier examples, you didn’t need to convert the $Quantity value because it was done automatically, but in this situation, the value isn’t automatically converted.)
After the variables that will contain the new data are set, the script calls the Add method of the Rows collection (associated with the DataTable object) and passes in the new data, in this case by specifying the $newRow variable. The next step is to create and define the SqlCommand object. As with previous examples, the script sets the CommandText property to the action query (INSERT statement) and passes in the parameter names for the values. The script then defines the parameters. However, unlike in earlier examples, the Parameters collection’s Add method is used, rather than the AddWithValue method. The Add method is an overloaded method that provides more control than the AddWithValue method. In this case, the script passes in four arguments: the parameter name, the data type, its length, and the source column in the dataset. By using the Add method, you can specify the data type for each parameter value, which is often necessary when working with datasets. As you can see in Listing 7, the @ProductName and @ProductCode parameters are of the type NVARCHAR, and the @Quantity parameter is of thetype INT.
After the parameters are defined, the script sets the SqlDataAdapter object’s InsertCommand property to the SqlCommand object ($cmd). If the SqlCommand object includes an UPDATE statement, the UpdateCommand property is set instead. If it includes a DELETE statement, the DeleteCommand property is set.
After the command is finally set, the script can update the database, which it does by calling the SqlDataAdapter object’s Update method. As an argument to that method, the script passes in the DataTable object ($dt). The Update method returns the number of rows updated, which is saved to the $RowsInserted variable. The script then displays the variable value in the PowerShell console.
Not surprisingly, the process of updating a row in the dataset is similar to inserting a row, as the script in Listing 8 shows. Once again, the script creates and fills a DataTable object with data retrieved through a SELECT statement and then displays the contents in the PowerShell console. Next, the script prompts the user for the necessary values to determine which row to update and how much to decrease the quantity by. After retrieving this information, the script uses the DataTable object’s Select method to retrieve the row that contains the specified product ID. The Select method acts as a filter that retains only the rows that meet the specified criteria, in this case, ProductID = $ProductID. The script then saves this row to the $updateRow variable. Finally, the script updates the Quantity column for that row.
To update the row, the script must take into account that the Select method can return an array of DataRow objects (multiple rows). However, the script retrieves the rows based on the ProductID value, which is the primary key for the source table (so the values are unique). This means that there can be only one row. As a result, when the row is called from the results, the 0 index is used because that will be the first (and only) row. In other words, $updateRow\\[0\\] points to the one row that was returned by the Select method.
After updating the row, the script creates and defines the SqlCommand object, as in the preceding example, and sets the CommandText property to the UPDATE statement. The script once again passes in the necessary parameters, then uses the Add method to create those parameters. After creating each parameter, the script sets their SourceVersion properties. These properties determine whether to retrieve the original column value or the updated (current) value. By default, the current value is used.
In this example, the script specifies that the original value should be used for ProductID. The original and current values are the same in this case, so either could be used. Because the default setting is Current, you don’t need to specifically set this property for either parameter. However, in some cases, such as when the parameter value that is used in the UPDATE statement’s WHERE clause changes, you need to know that original value, so this property setting can be critical in certain updates. For this reason, the property is specifically set here, so you have a basic understanding of how it is used. After the parameters are defined, the script sets the SqlDataAdapter object’s UpdateCommand to the SqlCommand object and then calls the Update method, as in the previous example.
Deleting a row from the dataset is a similar process, as the script in Listing 9 illustrates. After the script prompts the user for the product ID, it uses the Select method to retrieve the row to delete, then calls the Delete method to delete the row from the dataset. The script then creates the SqlCommand object, sets the CommandText property to the DELETE statement, passes in the necessary parameter, and creates that parameter. Finally, the script sets the DeleteCommand property and calls the Update method. As you can see, whether you’re inserting, updating, or deleting data from a dataset, the basic script components are the same—which is one of the advantages of working with ADO.NET.
A Place to Start
The example scripts in this article, as well as those in “Accessing SQL Server Data from PowerShell, Part 1,” will help you get started using ADO.NET within PowerShell scripts. These scripts provide the most useful methods for the types of scripts you’re likely to run in PowerShell. Of course many additional capabilities and approaches are available. As you become more familiar with ADO.NET, you’ll find a full range of features that make accessing SQL Server data as efficient and flexible as possible.
