Updating Data from ADO.NET

To perform updates using ADO.NET, you use the same objects utilized in the insert and delete actions: Connectio n and Command objects. The Command object may contain a T-SQL sentence or the name of a stored procedure in its CommandText property.

Note 

Remember: It is better to use stored procedures in all database operations.

To update a row using a stored procedure, perform the following steps.

Updating a Row Using a Stored Procedure in ADO.NET

1. Create a Connection object with a valid connection string.

2. Create a Command object with the stored procedure name in its CommandText property and Stored Procedure in its CommandType property.

3. Assign the Connection object to the Command objects Connection property.

4. Add parameters for the stored procedure to the Parameters collection in the Command object.

5. Open the connection.

6. Call the Command objects ExecuteNonQuery method.

7. Close the connection.

Using Datasets and Table Adapters

You can use datasets to bind and manage data in your applications. ADO.NET 2.0 adds new objects to the dataset implementation, allowing you to encapsulate the actions inside the dataset.

You can graphically create the infrastructure to use this feature, which is a quick way to implement Create Read Update Delete (CRUD) applications. However, keep in mind this is not the best way to build complex, reliable applications.

Creating a Dataset and Table Adapter

1. Open Visual Studio 2005, and create a new Windows application.

2. In the Solution Explorer, right-click the Project node and choose Add New Item.

   

3. In the dialog box that appears, choose DataSet and give the dataset the AdvWorks name.

   

4. Choose Server Explorer from the View menu, or press Ctrl+Alt+S to show the Server Explorer window.

5. Expand Data Connections and your AdventureWorks connection, then expand Tables.

6. Drag the TestTable and drop it over the Dataset Designer.

   

   In the designer surface, you will see the table structure.

   

7. Right-click the table structure, choose Configure, and then click the Advanced Options button. You will see that the DataTable implements INSERT, UPDATE, and DELETE SQL sentences, manages optimistic concurrency, and retrieves the new values after the execution of any update or insert action.

   

8. Close the Dataset Designer and save the changes.

9. Choose Build Solution from the Build menu, or press Ctrl+Shift+B to build the entire solution.

10. Open the form in design view. Expand the Toolbox window. You will see the DataSet and TestTableTableAdapter available at the top.

    

11. Drag the DataSet and TestTableTableAdapter and drop them over Form1. By doing this, you will obtain automatically created instances of these objects that are ready for you to use.

Creating a User Interface

1. Drag a ToolStrip over Form1.

2. Right-click it on the tray below the form, and choose Insert Standard Items.

3. Drag a DataGridView over the form.

4. If necessary, open the DataGridView Tasks window by clicking the black arrow in the upper-right corner of the control. From the Choose Data Source drop-down list in the DataGridView Tasks window, expand the Other Data Sources node, then the Project Data Sources node, and then select AdvWorks1.

   

5. Right-click DataGridView1 and choose Properties, or press F4 to display the Properties window.

6. In the DataMember property, choose TestTable. The grid will display the columns names .

7. In the Dock property, choose Fill by clicking the larger button in the middle of the pane.

8. In the ToolStrip, double-click the Open button (the folder icon).

   

9. Add the following code. This code is included in the sample files as \Ch12\Codes\Sample11.vb.

    TestTableTableAdapter1.Fill(AdvWorks1.TestTable) 

10. Return to the Form1 designer and double-click the Save button on the toolbar.

    

11. Add the following code to perform the update. This code is included in the sample files as \Ch12\Codes\Sample12.vb.

     Try     TestTableTableAdapter1.Update(AdvWorks1.TestTable) Catch ex As Exception     MsgBox(ex.Message) End Try 

12. Run the application and test the update actions (click the Open button on the toolbar, modify some records, and then click the Save button on the toolbar).

    Tip 

    After editing a row, move to another row before clicking the Save button. This is the only way that the data grid can update the information to the data table.

Pay attention to what is happening behind the scenes. For example, double-click the  AdvWorks.xsd node in the Solution Explorer to bring up the DataSet Designer. Right-click TestTableTableAdapter and choose Properties from the context menu. In the Properties window, expand the Update Command node.

Observe the Update command for the AdvWorks dataset in the TestTableTableAdapters CommandText property. You will see the UPDATE sentence that follows , in which each column has two parameter definitions: one for the new value and one for the original value. Moreover, the WHERE clause is built with conditions for every column in the table. This code is included in the sample files as \Ch12\AdvWorks\Queries\Sample13.sql.

 UPDATE TestTable SET   Name = @Name,    Description = @Description,    ActiveDate = @ActiveDate,    IsActive = @IsActive WHERE    (Id = @Original_Id) AND (@IsNull_Name = 1) AND (Name IS NULL) AND (ActiveDate = @Original_ActiveDate) AND (IsActive = @Original_IsActive) OR    (Id = @Original_Id) AND (Name = @Original_Name) AND (ActiveDate = @Original_ActiveDate) AND (IsActive = @Original_IsActive) 

This example creates only a small table, but imagine what would occur with the Production.Product table, which would produce twenty-five WHERE conditions. It is inefficient to perform numerous WHERE conditions. Refer to the Implementing Optimistic Concurrency for Delete Operations section in Chapter 11 to see alternatives to this method.

Using Stored Procedures with Table Adapters

You can use your own stored procedures to manage update actions in a Table Adapter.

Creating Stored Procedures for Table Adapters

1. Create the following stored procedure to obtain the complete SubCategories list with the associated Category name. This code is included in the sample files as \Ch12\AdvWorks\Queries\Sample14.sql.

    CREATE Procedure SubCategories_GetAll AS SELECT   Production.ProductSubcategory.ProductSubcategoryID,       Production.ProductCategory.Name AS Category,       Production.ProductSubcategory.Name FROM   Production.ProductSubcategory INNER JOIN    Production.ProductCategory ON    Production.ProductSubcategory.ProductCategoryID =       Production.ProductCategory.ProductCategoryID ORDER BY   Category,          Production.ProductSubcategory.Name GO 

2. Add the following stored procedure to retrieve the values for just one row. This code is included in the sample files as \Ch12\AdvWorks\Queries\Sample15.sql.

    CREATE Procedure SubCategories_GetOne    @ProductSubcategoryID int AS SELECT   ProductSubcategoryID,          ProductCategoryID,       Name,       rowguid,       ModifiedDate FROM   Production.ProductSubcategory WHERE    (ProductSubcategoryID = @ProductSubcategoryID) GO 

3. Add a stored procedure for the insert operation. This code is included in the sample files as \Ch12\AdvWorks\Queries\Sample16.sql.

    CREATE Procedure SubCategory_Insert    @ProductCategoryID int,     @Name nvarchar(50),    @rowguid uniqueidentifier,    @ModifiedDate datetime AS INSERT INTO [AdventureWorks].[Production].[ProductSubcategory]            ([ProductCategoryID]            ,[Name]            ,[rowguid]            ,[ModifiedDate])      VALUES            (@ProductCategoryID            ,@Name            ,NEWID()            ,GETDATE()) GO 

4. Add a stored procedure for the update operation. This code is included in the sample files as \Ch12\AdvWorks\Queries\Sample17.sql.

    CREATE Procedure SubCategory_Update    @ProductSubCategoryID int,    @ProductCategoryID int,     @Name nvarchar(50),    @rowguid uniqueidentifier,    @ModifiedDate datetime AS UPDATE [AdventureWorks].[Production].[ProductSubcategory]    SET [ProductCategoryID] = @ProductCategoryID       ,[Name] = @Name       ,[rowguid] = NEWID()       ,[ModifiedDate] = GETDATE()  WHERE ProductSubCategoryId=@ProductSubCategoryId   AND [rowguid] = @rowguid GO 

5. Add a stored procedure for the delete operation. This code is included in the sample files as \Ch12\AdvWorks\Queries\Sample18.sql.

    CREATE Procedure SubCategory_Delete    @ProductSubCategoryID int,    @rowguid uniqueidentifier AS DELETE FROM [AdventureWorks].[Production].[ProductSubcategory]  WHERE ProductSubCategoryId=@ProductSubCategoryId    AND [rowguid] = @rowguid GO 

   Important 

   In the update and delete stored procedures, you use the rowguid field to locate the same row that you retrieved previously.

6. In Visual Studio, edit the  AdvWorks.xsd dataset in the new project you built in the procedure titled Creating a Dataset and Table Adapter.

7. From the View menu, select Server Explorer.

8. Expand the Database Connections node, expand your AdventureWorks connection, and then expand the Stored Procedures folder.

9. In Design view, drag the SubCategories_GetAll stored procedure and drop it over the Design view of the dataset.

10. Repeat this action for the SubCategories_GetOne stored procedure.

11. Right-click the SubCategories_GetOne table and choose Configure.

12. Assign the appropiate stored procedures for each action as shown below and click Finish.

    

Creating an Editor

1. Drag the Production.ProductCategory table to the dataset.

2. Build the solution.

3. Add a new Windows form to your Windows application by using the Project Add Windows Form menu and name it  SubCategories.vb .

4. From the Toolbox, drag the SubCategories_GetAllTableAdapter, the SubCategories_GetOneTableAdapter, the ProductCategoryTableAdapter, and the AdvWorks dataset to the form.

5. Add a ToolStrip to the form.

6. Drag a SplitContainer from the Toolbox and change the orientation to Horizontal.

7. Drag a DataGridView to the upper SplitContainer panel. Drag two Labels, one ComboBox, one Textbox, and one Button to the lower SplitContainer panel.

8. Assign the AdvWorks dataset as the Data Source in the Properties window of the DataGridView, and assign SubCategories_GetAll as the DataMember in the DataGridView. The form will look like the following:

   

9. Enable the Use Data Bound Items option in the DropDownLists Tasks window and assign the properties as follows:

   

10. Select the textbox and press F4 to display the Properties window.

11. In the Properties window, expand the DataBindings section and change the Text property data bindings to point to the Name field of the SubCategories_GetOne table in AdvWorks1.

    

12. Choose Code from the View menu to display the code editor.

13. Add the following code to load the DataGridView when the form starts. This code is included in the sample files as \Ch12\Codes\Sample19.vb.

     Private Sub SubCategories_Load( _     ByVal sender As System.Object, _     ByVal e As System.EventArgs) Handles MyBase.Load     SubCategories_GetAllTableAdapter1.Fill(AdvWorks1.SubCategories_GetAll)     ProductCategoryTableAdapter1.Fill(AdvWorks1.ProductCategory) End Sub 

14. View the form again, select DataGridView, and press F4 to display the Properties window.

15. Click the Events toolbar button in the Properties window (the button with the lightning bolt), scroll down to the rowEnter event, and double-click it.

16. Add code in the DataGridViews RowEnter event to fill the SubCategories_GetOne table when a row is selected. This code is included in the sample files as \Ch12\Codes\Sample20.vb.

     With SubCategories_GetOneTableAdapter1     .Fill( _         AdvWorks1.SubCategories_GetOne, _         AdvWorks1.SubCategories_GetAll.Rows(e.RowIndex).Item(0)) End With 

17. Add code for the buttons Click event to update the changes to the database. This code is included in the sample files as \Ch12\Codes\Sample21.vb.

     Try     AdvWorks1.SubCategories_GetOne.Rows(0).EndEdit()     SubCategories_GetOneTableAdapter1.Update( _         AdvWorks1.SubCategories_GetOne)     SubCategories_GetAllTableAdapter1.Fill( _         AdvWorks1.SubCategories_GetAll) Catch ex As Exception End Try 

18. Add a button to the ToolStrip by clicking the small black down arrow and selecting Button.

19. Assign New as the Text property, and change the DisplayStyle to Text.

20. Double-click the New button and add the following code to insert a new row. This code is included in the sample files as \Ch12\Codes\Sample22.vb.

     With AdvWorks1.SubCategories_GetOne     .Clear() 'remove any previous row     'Create a new row     Dim r As AdvWorks.SubCategories_GetOneRow = _         .NewSubCategories_GetOneRow     'Assign default values to each column     r.ProductCategoryID = 0     r.Name = ""     r.rowguid = Guid.NewGuid     r.ModifiedDate = System.DateTime.Now     'Add the row to the table     .AddSubCategories_GetOneRow(r) End With 

21. Add code that explains what to do when a category is not selected. In the Button_Click event, catch the SQLException to manage the Number property. (Add this code right after the END WITH statement.) This code is included in the sample files as \Ch12\Codes\Sample23.vb.

     Catch ex As SqlClient.SqlException     Select Case ex.Number         Case 547             MsgBox("Please, select one of the categories")             ComboBox1.Focus()         Case Else             MsgBox(ex.Message)     End Select End Try 

22. Remember to add a TRY statement before the WITH statement.

23. Add a button to the ToolStrip, change the text to Delete, and set the DisplayStyle as Text.

24. Double-click the Delete button and add code to delete a subcategory . Remember to manage the referential error. This code is included in the sample files as \Ch12\Codes\Sample24.vb.

     If MsgBox("Do you want to delete the selected sub category?", _     MsgBoxStyle.Question Or MsgBoxStyle.YesNo) = MsgBoxResult.Yes Then     Try 'Get the row depending on the selected cell in the Gridview         With SubCategories_GetOneTableAdapter1             .Fill( _                 AdvWorks1.SubCategories_GetOne, _                 AdvWorks1.SubCategories_GetAll.Rows( _                 DataGridView1.SelectedCells(0).RowIndex).Item(0)) 'Delete the row in the DataTable             AdvWorks1.SubCategories_GetOne.Rows(0).Delete() 'Update the changes to the database             .Update(AdvWorks1.SubCategories_GetOne)         End With 'reload the Datagrid rows         SubCategories_GetAllTableAdapter1.Fill( _             AdvWorks1.SubCategories_GetAll)     Catch ex As SqlClient.SqlException         Select Case ex.Number             Case 547 'Referential error. 'In this case, the row has related rows in other tables                 MsgBox("Cannot delete. The subcategory is in use in some Products")             Case Else                 MsgBox(ex.Message)         End Select     Catch ex As Exception         MsgBox(ex.Message)     End Try End If 

When you run the application, you will observe what happens when you try to delete a row on which other rows in other tables depend.

Note 

To run the application using the SubCategories form, right-click the project in the Solution Explorer, select Properties from the context menu, and then set the Startup form to SubCategories. Rebuild and run the application.