Exercise 5: Deleting Data

In this exercise, you will add code to implement deletion of products and bind that code to a link button.

Task 1 – Adding the Delete Method to the Data Access Class

1. In the File menu, choose Open | Project/Solution. In the Open Project dialog, browse to Ex5-DeletingData\Begin in the Source folder of this lab, select Begin.sln and click Open. Alternatively, you may continue working with the solution obtained after completing the previous exercise.
2. Open the ProductsDataAccess.cs class by double-clicking the file in the Solution Explorer.

3. Create a new public static method to delete existing products. Remember that, as the ProductDataAccess class is static, all its methods must also be static. To do this, paste the following code (shown in bold) inside the ProductDataAccess class.

   (Code Snippet – First SQL ASP.NET Application Lab – Ex05 – DeleteProduct method)

   C#

   public static void DeleteProduct(int productId) { using (var scope = new TransactionScope()) { using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["AdventureWorks"].ConnectionString)) { connection.Open(); var deleteProductPhotoCommand = new SqlCommand("DELETE Production.ProductPhoto WHERE ProductPhotoID NOT IN (SELECT DISTINCT ProductPhotoID FROM Production.ProductProductPhoto);", connection); deleteProductPhotoCommand.ExecuteNonQuery(); var deleteBillOfMaterials = new SqlCommand("DELETE Production.BillOfMaterials WHERE ProductAssemblyID=@ProductID OR ComponentID=@ProductID;", connection); deleteBillOfMaterials.Parameters.AddWithValue("@ProductID", productId); deleteBillOfMaterials.ExecuteNonQuery(); var relatedTables = new List<string> {"Production.ProductProductPhoto", "Production.ProductCostHistory", "Production.ProductDocument", "Production.ProductInventory", "Production.ProductListPriceHistory", "Production.ProductListPriceHistory", "Production.ProductReview", "Production.TransactionHistory", "Production.TransactionHistoryArchive", "Production.WorkOrderRouting", "Production.WorkOrder", "Sales.SalesOrderDetail", "Sales.ShoppingCartItem", "Sales.SpecialOfferProduct", "Production.Product"}; foreach (var table in relatedTables) { var deleteProductCostHistory = new SqlCommand(string.Format("DELETE {0} WHERE ProductID=@ProductID;", table), connection); deleteProductCostHistory.Parameters.AddWithValue("@ProductID", productId); deleteProductCostHistory.ExecuteNonQuery(); } scope.Complete(); } } }

   FakePre-3a1f6ee5e9b443e2916d5d024cb59e89-05415d63311944a582539c74a3816e8aFakePre-279c77813ab140a5ae51c61bc8e4e292-917f52bfbd9f4ff2bc40364501b00edbFakePre-0cd991adcf9e4cdaa61c498c78d3d513-0539c54e87604279aca6442ef721e997
   

   The preceding code creates a new transaction to perform the delete operations. First, it deletes the relation between the Products and Photo tables. Next it deletes any orphaned photos. Finally it deletes the product.

4. Press CTRL+SHIFT+B to build the solution.

Task 2 – Binding the Data to the UI

In this task you will see how to bind the ObjectDataSource to the method created in the previous task, and to create a button for deleting products.

1. Open the Default.aspx page in Design view. To do this, double-click the file in the Solution Explorer, and then click Design at the bottom left corner of the designer.
2. Click the ObjectDataSource control in the designer surface to select it.
3. Click the Smart Tag () to expand the ObjectDataSource tasks, and then click Configure Data Source.

4. Click Next in the Choose a Business Object page. Click the Delete tab and choose the DeleteProduct method and click Next. In the following window, click Finish.

   Figure 32

   Choosing the delete method

5. If the Refresh keys and fields for InsertProductsDetailsView or Reconfigure ListView dialog displays, click No in both cases.
6. Open the Default.aspx page in Source view by clicking Source at the bottom left corner of the designer.

7. Add a Delete button to the ItemTemplate for deleting a product. To do this, add the following bolded code inside the ItemTemplate tags, next to the Edit button.

   HTML

   <ItemTemplate>

   FakePre-cc83b41f2bbe41e4a61f343b784e4b91-ef102ed0a04043388cc56bb26d7a9b3bFakePre-282e60d4293b49ad830826f1150d5b26-450cd7fb656e463eb44007db74a26cffFakePre-74f2fa37965d4449953c5d0a37d52f8f-03f87fbe64004bdc8c349c7defaa3ac2FakePre-99cb6c5a812548ddb0725422cb971856-e50572eb3d6949db804d262ab271db6aFakePre-1c439d9627c54eb390db890f32b6112e-5cbc9b3646cd4421a3fb5f5647c1cba4

8. Save the changes and close the files.

Exercise 5: Verification

1. Press CTRL+F5 to run the solution.

   Figure 33

   Listing the products

2. Click Delete on the first product, the one you have added in previous exercises, to delete it. Notice that that bike is no longer listed.

   Figure 34

   Deleting the products

3. Finally, close the browser.