Deleting Data from ADO.NET

To delete rows using ADO.NET, you use the same objects that you used in the insert process: Connection and Command objects.

Note 

The following examples use the same new AdventureWorks: TestTable file that you used for your insert samples. The script to create the table is included in the sample files in the SQL Server Management Studio Solution in the Ch10\Sample Codes\Chapter 10 Scripts folder.

Deleting Rows Using ADO.NET

1. Open the  Ch11.sln file from the \Ch11 folder in the sample files. (A completed version of this application is included in the sample files in the \Ch11-completed folder.)

2. In the AdvWorks project under the Change Scripts folder, right-click the  Add Rows to Test Table.sql file and select Run On, then select the connection on which to run the script. This will fill the table with information.

3. In the Ch11 project, double-click Form1.

4. In the menu bar, click TestTable Actions and then double-click the Get Rows menu.

5. Review the code in the LoadData procedure to load the rows into the data grid.

6. Return to the design view of Form1. Go to the TestTable Actions menu, click the Delete Selected menu, and double-click the T-SQL menu.

7. Add the following code. This code is included in the sample files as \Ch11\Codes\Sample19.vb.

    Const SQLSentence As String = _     "DELETE FROM [AdventureWorks].[dbo].[TestTable] WHERE id={0}" Dim Count As Integer = 0 For Each r As DataGridViewRow In DataGridView1.SelectedRows     Dim com As New SqlClient.SqlCommand(_         String.Format(SQLSentence, r.Cells(0).Value))     With com         .Connection = App.Connection         Count += .ExecuteNonQuery     End With Next MsgBox(String.Format("{0} rows deleted", Count)) LoadData() 

8. Run the application. Use the Database Change Connection menu as necessary, and then connect to the database. Load the information using the Test Table Actions Get Rows menu.

9. Select one or more rows. Click Test Table Actions Delete Selected T-SQL menu to remove them from the table.

10. Stop the execution.

11. Under the AdvWorks project, right-click the  CREATE PROCEDURE TestTable_Delete.sql file in the Create Scripts folder, and click Run On to create the stored procedure.

12. Go to the Ch11 project and, in the design view of Form1, click the Delete Selected menu, click the TestTable Actions menu, and then double-click the Stored Procedure menu.

13. Add the following code. This code is included in the sample files as \Ch11\Codes\Sample20.vb.

     Dim Com As New SqlClient.SqlCommand("TestTable_Delete") Dim Count As Integer = 0 With Com     .Connection = App.Connection     .CommandType = CommandType.StoredProcedure     .Parameters.Add("@Id", SqlDbType.Int)     For Each r As DataGridViewRow In DataGridView1.SelectedRows         .Parameters(0).Value = r.Cells(0).Value         count += .ExecuteNonQuery     Next     MsgBox(String.Format("{0} rows deleted", Count))     LoadData() End With 

14. Run the application and test the code.

Managing Errors during the Delete Process

There are more opportunities to receive an error when deleting a row than when inserting one. Issues such as referential integrity and other business- related rules must be properly managed.

To evaluate how to manage errors, perform the following steps.

Evaluating Errors in ADO.NET

1. In the AdvWorks project, right-click the  CREATE PROCEDURE Sales.SalesReason_DeleteChecked.sql file and click Run On to create the stored procedure.

2. In the Ch11 project, go to the design view of Form1. Double-click the Delete Selected (SP) menu under the Sales Reason Actions menu.

3. Add the following code. This code is included in the sample files as \Ch11\Codes\Sample21.vb.

    Dim Com As New _     SqlClient.SqlCommand("Sales.SalesReason_DeleteChecked") Dim Count As Integer = 0 With Com     .Connection = App.Connection     .CommandType = CommandType.StoredProcedure     .Parameters.Add("@SalesReasonId", SqlDbType.Int)     .Parameters.Add("@CheckStamp", SqlDbType.BigInt)     With .Parameters.Add("@DeletedRecords", SqlDbType.Int)         .Direction = ParameterDirection.Output     End With     For Each r As DataGridViewRow In DataGridView1.SelectedRows         .Parameters(0).Value = r.Cells(0).Value         .Parameters(1).Value = r.Cells("LastVersion").Value         Try             .ExecuteNonQuery()             Count += CInt(.Parameters(2).Value)         Catch ex As SqlClient.SqlException             MsgBox(ex.Number)         Catch ex As Exception             MsgBox(ex.Message)         End Try     Next     MsgBox(String.Format("{0} rows deleted", Count))     LoadSales() End With 

   The try catch block allows you to trap any error during the delete process. Inside the block, the Catch ex as SqlClient.SqlException section provides specific information about the error from the database.

4. Run the application.

5. After loading the SalesReason information, try to delete the Price row (SalesReasonID equal to 1).

   You will receive a message containing the error number (in this case, 547), which is the same number you will receive if you try to remove the row using SQL Server Management Studio.

   

6. By modifying the catch portion, you can implement a case selector to manage every error code of your choice and give the user a more adequate message, including different action choices, depending on the kind of error the code receives. In the following example, replace the MsgBox(ex.Number) line of code with this new block of code. This code is included in the sample files as \Ch11\Codes\Sample22.vb.

    Select Case ex.Number     Case 547 'Referential integrity         MsgBox("This information has other related pieces of information." & _                "You can't remove it", MsgBoxStyle.Exclamation)         'Continue adding your own control actions here     Case Else         MsgBox("Unspecified error. Can't delete", MsgBoxStyle.Critical) End Select 

7. Build and test the code as before.