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.