Export (0) Print
Expand All

Data Operations on Hierarchical Row Data

 

Priya Dhawan
Microsoft Developer Network

Revised February 2002

Summary: This article covers data operations on hierarchical sets of rows using ADO .NET. (24 printed pages)

Download bdadotnet_beta2.msi.

Contents

Introduction
Read Operations
   Using the ADO .NET DataReader Object
   Using the ADO .NET SqlCommand Object and XmlReader
   Using the ADO .NET DataSet Object
Write Operations
   Using the ADO .NET DataAdapter Object
   Inserts
   Updates
   Deletes
Conclusion

Introduction

This article demonstrates techniques of reading and writing hierarchical row data from a data source. The code samples throughout the article make use of the SQL managed provider to connect to a Microsoft® SQL Server™ database or to the Microsoft® Desktop Engine (MSDE). For connecting to other OLEDB-compliant data sources, the ADO managed provider should be used.

ADO .NET provides the DataReader, and DataSet objects to access hierarchical row data returned from a data source. The DataReader object is designed to provide simple, fast, read-only access to data. The DataReader object can access hierarchical rows returned as multiple results corresponding to multiple SELECT statements, or as XML from SQL Server 2000. The DataReader object allows forward-only reads and remains connected to the database while the application reads data.

In contrast, the DataSet object represents a disconnected, in-memory relational cache of data. It can contain multiple tables that can be treated either separately or as related tables. The DataSet object has features that simplify navigation and modification of related data.

The SQL Server .NET Data Provider also enables us to get an XML stream directly from SQL Server 2000 by exposing a streaming API, called ExecuteXmlReader, through the SQLCommand object. The ExecuteXmlReader method executes the SQL query against the SQL connection and builds an XmlReader object from XML returned by the SQL query. ExecuteXmlReader can only be used with statements that return XML data; queries that return results of SELECT statements as XML through the use of FOR XML clause can be used effectively with ExecuteXmlReader.

Read Operations

Hierarchical row data is often structured as multiple sets of related rows. It is often more efficient to retrieve multiple sets of rows in a single trip to the database rather than making a round trip for each one. This is often accomplished by executing a batch of SQL expressions or a stored procedure containing multiple SELECT statements. Hierarchical rows can also be returned as XML from SQL Server 2000 using a FOR XML SELECT statement.

Using the ADO .NET DataReader Object

In this example a batch of two SQL SELECT statements are executed and the corresponding two result sets are retrieved through a DataReader object. The DataReader object allows forward-only navigation so the data will likely be transferred to another container for subsequent work. Because the DataReader object maintains an open database connection, it's advantageous from a scalability perspective to perform this transfer quickly and to release the database connection as soon as possible. It is not possible to pass a DataReader object between tiers of a distributed application.

The NextResult and Read methods of the DataReader object are used to iterate through the rows of the result sets:

Dim sqlCmd As SQLCommand
Dim sqlDataRdr As SQLDataReader
Dim fld As Integer
Dim rptLine As String
Try
   ' Prepare the command object to execute a stored procedure returning    
   ' Orders and OrderDetails
   sqlCmd = New SQLCommand()
   With sqlCmd
      .CommandType = CommandType.StoredProcedure
      .CommandText = "GetOrders"
      .Connection = New SqlConnection(myConnString)
   End With
   ' Open the Connections
   sqlCmd.Connection.Open()
   ' Execute the command specifying the DataReader to receive the results
   sqlDataRdr = sqlCmd.ExecuteReader()
   ' Iterate through the rows in the the first result set
   Do
      While (sqlDataRdr.Read)
         ' Do something with the Row   here
         rptLine = ""
         For fld = 0 To sqlDataRdr.FieldCount - 1
            rptLine = rptLine & sqlDataRdr.Item(fld).ToString
            If fld < sqlDataRdr.FieldCount - 1 Then
               rptLine = rptLine & ", "
            End If
         Next
      End While
      ' Advance the pointer to the next result set
      If Not (sqlDataRdr.NextResult) Then
         Exit Do
      End If
   Loop
Catch E As Exception
Finally
   ' Close the DataReader
   sqlDataRdr.Close()
End Try

Note   See Example 1 in the BDAdotNetData4.vb sample code (see top of article for download).

Using the ADO .NET SqlCommand Object and XmlReader

Microsoft SQL Server 2000 supports XML capabilities natively. The results of SELECT statements can now be returned as XML through the use of FOR XML clause. To retrieve results as XML directly from the SQL Server 2000, we can make use of the ExecuteXmlReader method of the SQLCommand object. ExecuteXmlReader returns a System.Xml.XmlReader object containing XML returned from the SQL Server 2000.

In the following example, we call the ExecuteXmlReader method on the SQLCommand object to execute a stored procedure, which uses FOR XML clause to return results as XML.

Dim sqlConn As SqlConnection
Dim sqlCmd As SqlCommand
Dim xmlRdr As XmlReader
Try
   ' Create a new connection object
   sqlConn = New SqlConnection(myConnString)
   ' Create a new command object
   sqlCmd = New SqlCommand()
   ' Specify the command to be exceuted
   With sqlCmd
      .CommandType = CommandType.StoredProcedure
      .CommandText = "GetOrdersXML"
      .Connection = sqlConn
   End With
   ' Open the connection
   sqlConn.Open()
   ' Execute the command and retrieve the row in the DataReader
   xmlRdr = sqlCmd.ExecuteXmlReader()
   ' Move to the Root Element
   xmlRdr.MoveToContent()
   ' Do something with data
   outXML = xmlRdr.ReadOuterXml
   ' Move to the Next Element
   xmlRdr.MoveToElement()
   ' Read the OrderId Attribute of the Current Element
   xmlRdr.MoveToAttribute("OrderId")
   …
Catch e As Exception
   ' Catch Exception here
Finally
    sqlConn.Close()
End Try

Note   See Example 2 in the BDAdotNetData4.vb sample code (see top of article for download).

Using the ADO .NET DataSet Object

The DataAdapter object retrieves data from a data source and populates DataTable objects within a DataSet. The DataAdapter requires a Connection object to execute a query against the data source.

If the query returns multiple result sets, the DataSet stores each of them in a separate table. The tables can be related to each other.

Relationship between tables

Once you link two tables within the DataSet through a relationship, with the DataRelation object, navigation from one table to another is simplified. Retrieval of all of the related or child DataRows from one DataTable for a parent row in the other linked DataTable is also simplified. The overloaded GetChildRows method of the DataRow object is used to retrieve the child rows.

Relationships can be established by creating a DataRelation object that associates rows in one DataTable with the rows in another table. A DataSet object contains relationships contained by the DataRelationCollection object.

It is not mandatory to relate tables in the DataSet object. They can remain unrelated. If the two tables have some kind of parent/child relationship, like foreign key relationship, relating the tables will make it easy to get to the child rows in one DataTable from a parent row in another DataTable.

The following code example retrieves orders and their details from the Orders and the OrderDetails tables in the database. The DataSet object contains Orders and Details tables corresponding to the database tables. The column 'OrderId,' present in both the DataTables, serves as the link between the two tables:

Dim sqlDA As SqlDataAdapter
Dim hierDS As DataSet
Dim orderRow As DataRow
Dim detailRow As DataRow
Dim detailRows() As DataRow
Dim i As Integer
Try
   ' Create a new DataAdapter object
   sqlDA = New SqlDataAdapter()
   ' Create a new DataSet
   hierDS = New DataSet()
   ' Set the Table Mappings
   sqlDA.TableMappings.Add("Orders", "Orders")
   sqlDA.TableMappings.Add("Orders1", "OrderDetails")
   With sqlDA
      ' Add a SelectCommand object
      .SelectCommand = New SqlCommand()
      ' Specify the Select Command
      With .SelectCommand
          .CommandType = CommandType.StoredProcedure
          .CommandText = "GetOrders"
          .Connection = New SqlConnection(myConnString)
      End With
      ' Populate the DataSet with the returned data
      .Fill(hierDS, "Orders")
   End With

Because there were no tables within the DataSet prior to invoking the Fill method, the SQLDataAdapter object will automatically create the tables for the DataSet object and populate them with the returned data. If you build the tables prior to executing the Fill method, the SQLDataAdapter object will simply fill the existing tables:

   ' Set the Primary Key for the Tables
   hierDS.Tables("Orders").PrimaryKey = New DataColumn() _ 
{ hierDS.Tables("Orders").Columns("OrderId")}
   hierDS.Tables("OrderDetails").PrimaryKey = New DataColumn() _ 
{ hierDS.Tables("OrderDetails").Columns("OrderDetailId")}
   ' Establish the Foreign Key relationship between the tables
   hierDS.Relations.Add("Order_Detail", _
                    hierDS.Tables("Orders").Columns("OrderId"), _
                    hierDS.Tables("OrderDetails").Columns("OrderId"))
   ' Get an order
   orderRow = hierDS.Tables("Orders").Rows(0)
   ' Retrieve child rows for the order
   detailRows = orderRow.GetChildRows("Order_Detail")
   ' Do something with the child rows collection
   For i = 0 To detailRows.Length - 1
      detailRow = detailRows(i)
      ' Do something with the detail row
      strDetail = detailRow("OrderId").ToString & ", " & _
            detailRow("OrderDetailId").ToString & ", " & _ 
detailRow("ItemId").ToString & ", " & _
detailRow("UnitPrice").ToString & ", " & 
detailRow("Quantity").ToString)
   Next
Catch E As Exception
   ' Catch Exception here
Finally
   ' Close connection and other Cleanup code here
End Try

Note   See Example 3 in the BDAdotNetData4.vb sample code (see top of article for download).

Write Operations

Committing the changes in the hierarchical data, which contains multiple result sets from two or more related tables, requires the data integrity to be maintained. For example, the referential integrity means that the foreign key in any referencing table must always refer to a valid row in the referenced table. Hence, a parent row in the referenced table cannot be deleted if it is being referenced in the referencing table. Similarly, inserts cannot be made to the referencing table if the corresponding rows do not exist in the referenced table.

Because the ADO .NET DataSet object allows retrieving, manipulating, and updating the data in the database, it ensures that the relationship between two tables remains harmonized during inserts, updates, and deletes. It also allows you to perform cascading updates and deletes while maintaining the data integrity.

Using the ADO .NET DataAdapter Object

The Update method of the DataAdapter submits changes cached in the DataSet object to the data source. The DataAdapter uses InsertCommand to submit new rows, UpdateCommand to submit modified rows, and DeleteCommand to remove rows from the database. When you call the Update method, the DataAdapter examines the modified rows and determines which Command object to execute in order to submit the pending change for that row.

Before calling the Update method of the DataAdapter, you must set the InsertCommand, UpdateCommand, or DeleteCommand properties, depending on what changes were made to the data in the DataSet. For example, if rows have been deleted from the DataSet, you must set the DeleteCommand property. You can take advantage of the CommandBuilder object to automatically generate the Insert, Update, and Delete commands. If you specify the InsertCommand, UpdateCommand, or DeleteCommand properties of the DataAdapter, the Update method executes respective insert, update, or delete commands for each of the inserted, updated, or deleted rows in the DataSet. Otherwise, the CommandBuilder generates the SQL Commands required to reconcile changes with the database, based on the SelectCommand property of the DataAdapter. Consequently, the SelectCommand property of the DataAdapter must be set in order for the CommandBuilder to generate the Insert, Update, and Delete commands.

The preferred way is to specify your own InsertCommand, DeleteCommand, or UpdateCommand completely, because it allows you to explicitly control how the update is done and has better performance than does the auto-generated case. This is especially true when your own code reduces round trips to the data source by handling operations on multiple rows per round trip.

You can specify parameterized queries or stored procedures for the InsertCommand, UpdateCommand, and DeleteCommand properties of the DataAdapter . The parameters in the parameterized queries or stored procedures correspond to columns in a DataTable object. As a result, the DataAdapter object supports updates against a single table in your database. Hence, when submitting changes to the database, a separate DataAdapter object for every table in the DataSet object will be required.

Inserts

New rows can be added to the child table if they correspond to valid (or already existing) rows in the parent table. A row should not be added to the child table if it refers to an invalid row in the parent table.

First, the new row should be inserted in the parent table, and then only the corresponding child rows should be added to the child table. For the same reason, while reconciling the changes with the database, the Update method on the DataAdapter object corresponding to the parent table should be called first.

Auto-generated Insert command

One problem with auto-generated inserts is that primary key Id for an Identity column is not returned to the DataSet. To work around this we’ll use a stored procedure that returns the primary key for the parent allowing use of auto-generated inserts for the child rows. In the following code example, we create two DataAdapter objects to populate two tables in a single DataSet object. We define the relationship between the two tables in the DataSet object and insert new rows in the tables. The Update method on the DataAdapter object corresponding to the parent table, in this case Orders, is called first to submit the changes to the database. After that we call the Update method on the DataAdapter object corresponding to the child table, in this case OrderDetails:

Dim sqlConn As SQLConnection
Dim sqlDAOrder As SqlDataAdapter
Dim sqlDADetail As SqlDataAdapter
Dim hierDS As DataSet
Dim sqlCmdBldrDetail As SqlCommandBuilder
Dim orderRow As DataRow
Dim detailRow As DataRow
Try
   ' Create a new SQLConnection object
   sqlConn = New SqlConnection(Common.getConnectionString)
   ' Create a new SQLDataAdapter object for the Order table
   sqlDAOrder = New SqlDataAdapter()
   ' Create a new SqlDataAdapter object for the OrderDetails table
   sqlDADetail = New SqlDataAdapter()
   ' Create a new DataSet
   hierDS = New DataSet()
   ' Create a new SQLCommandBuilder object to automatically generate 
   ' Update Statements
   sqlCmdBldrDetail = New SqlCommandBuilder(sqlDADetail)   
   With sqlDAOrder
      ' Add a SelectCommand object
      .SelectCommand = New SqlCommand()
      ' Specify the Select Command
      With .SelectCommand
         .CommandType = CommandType.Text
         .CommandText = "Exec GetOrderHeader @OrderId=-1"
         .Connection = sqlConn
      End With
     ' Add a InsertCommand object
      .InsertCommand = New SqlCommand()
      ' Specify the Insert Command
      With .InsertCommand
         .CommandType = CommandType.StoredProcedure
         .CommandText = "InsertOrderHeader"
         .Connection = sqlConn
         ' Define parameters in the parameterized Insert statement
         .Parameters.Add _
               (New SqlParameter("@CustomerId", SqlDbType.Int))
         ' Set Direction property
         .Parameters("@CustomerId").Direction = ParameterDirection.Input
         ' Set the SourceColumn property
         .Parameters("@CustomerId").SourceColumn = "CustomerId"
         .Parameters.Add _
               (New SqlParameter("@OrderDate", SqlDbType.DateTime))
         ' Set Direction property
         .Parameters("@OrderDate").Direction = ParameterDirection.Input
         ' Set the SourceColumn property
         .Parameters("@OrderDate").SourceColumn = "OrderDate"
         …
         …
         .Parameters.Add _
               (New SqlParameter("@OrderId", SqlDbType.Int))
         ' Set Direction property
         .Parameters("@OrderId").Direction = ParameterDirection.Output
         ' Set the SourceColumn property
         .Parameters("@OrderId").SourceColumn = "OrderId"
      End With
      ' Populate the Orders table
      .Fill(hierDS, "Orders")
   End With
   With sqlDADetail
      ' Add a SelectCommand object
      .SelectCommand = New SqlCommand()
      ' Specify the Select Command
      With .SelectCommand
         .CommandType = CommandType.Text
         .CommandText = "Exec GetOrderDetails @OrderId=-1"
         .Connection = sqlConn
      End With
      ' Populate the Details table
      .Fill(hierDS, "Details")
   End With
   ' Establish a relationship between the tables
   hierDS.Relations.Add("Order_Detail", _
hierDS.Tables("Orders").Columns("OrderId"), _
hierDS.Tables("Details").Columns("OrderId"))
   ' Create a new row for the Orders table
   orderRow = hierDS.Tables("Orders").NewRow()
   ' Set the value of each of the column present in the Orders table
   orderRow.Item("CustomerId") = 1
   orderRow.Item("OrderStatus") = 400
   orderRow.Item("OrderDate") = Now()
   …
   ' Add the row to the DataSet
   hierDS.Tables("Orders").Rows.Add(orderRow)
   ' Reconcile changes with the data source
   sqlDAOrder.Update(hierDS, "Orders")
   ' Create a new Row for the Details table
   detailRow = hierDS.Tables("Details").NewRow()
   detailRow.Item("OrderId") = orderRow.Item("OrderId")
   detailRow.Item("ItemId") = 13
   …
   ' Add the row to the DataSet
   hierDS.Tables("Details").Rows.Add(detailRow)
   ' Reconcile changes with the data source
   sqlDADetail.Update(hierDS, "Details")
Catch e As Exception
   ' Catch Exception here
Finally
   ' Cleanup code
End Try

Note   See Example 4 in the BDAdotNetData4.vb sample code (see top of article for download).

Note that if the new rows added to the child table correspond to a new row added to the parent table, the update on the parent table should be performed first, so as to maintain the data integrity. A child row must always refer to a valid row in the parent table.

Using the InsertCommand property

In this example, an order header and details are passed to a stored procedure as XML so that the transaction can be accomplished in a single round trip to the database.

To specify your own INSERT statement, which should be executed when the Update method is called on the DataAdapter, set the InsertCommand property. The InsertCommand property can be set to a parameterized query or a stored procedure. The parameters for InsertCommand are defined the same way they would be for the Command object. The SQL managed provider allows you to use named parameters.

You need to set the SourceColumn property for all the parameters. SourceColumn tells the DataAdapter object which column in the table is going to provide its value. Set it to the actual parameter name:

Dim sqlConn As SqlConnection
Dim sqlDAOrder As SqlDataAdapter
Dim sqlDADetail As SqlDataAdapter
Dim hierDS As DataSet
Try
   ' Create a new connection
   sqlConn = New SqlConnection(Common.getConnectionString)
   ' Create a new SqlDataAdapter object for the Order table
   sqlDAOrder = New SqlDataAdapter()
   ' Create a new SqlDataAdapter object for the OrderDetails table
   sqlDADetail = New SqlDataAdapter()
   ' Create a new DataSet
   hierDS = New DataSet()
   With sqlDAOrder
      ' Add a SelectCommand object
      .SelectCommand = New SqlCommand()
      ' Specify the Select command 
      With .SelectCommand
         .CommandType = CommandType.Text
         .CommandText = "Exec GetOrderHeader @OrderId=-1"
         .Connection = sqlConn
      End With
      ' Add a InsertCommand object 
       .InsertCommand = New SqlCommand()
       ' Specify the Insert Command
      With .InsertCommand
         .CommandType = CommandType.StoredProcedure
         .CommandText = "InsertOrder"
         .Connection = sqlConn
         ' Define parameters in the parameterized Insert statement
         .Parameters.Add _
             (New SqlParameter("@Order", SqlDbType.NVarChar, 4000))
         ' Set Direction property
         .Parameters("@Order").Direction = ParameterDirection.Input
         .Parameters.Add _
               (New SqlParameter("@OrderId", SqlDbType.Int))
         ' Set Direction property
         .Parameters("@OrderId").Direction = ParameterDirection.Output
         ' Set the SourceColumn property
         .Parameters("@OrderId").SourceColumn = "OrderId"
      End With
      ' Populate the DataSet with the returned data
      .Fill(hierDS, "Orders")
   End With
   With sqlDADetail
      ' Add a SelectCommand object
      .SelectCommand = New SqlCommand()
      ' Specify the Select command for the sqlDADetail object
      With .SelectCommand
         .CommandType = CommandType.Text
         .CommandText = "Exec GetOrderDetails @OrderId=-1"
        .Connection = sqlConn
      End With
      ' Populate the DataSet with the returned data
      .Fill(hierDS, "Details")
   End With 
   ' Establish the Foreign Key relationship between the tables
    hierDS.Relations.Add("Order_Detail", _                           
hierDS.Tables("Orders").Columns("OrderId"), _
hierDS.Tables("Details").Columns("OrderId"))
   ' Create a new row for the Orders table
   orderRow = hierDS.Tables("Orders").NewRow()
   ' Set the value of each of the column present in the Orders table
   orderRow.Item("OrderId") = -1
   orderRow.Item("CustomerId") = 1
   orderRow.Item("OrderStatus") = 400
   …
   ' Add the row to the DataSet
   hierDataSet.Tables("Orders").Rows.Add(orderRow)
   ' Create a new Row for the Details table
   detailRow = hierDataSet.Tables("Details").NewRow()
   detailRow.Item("OrderId") = orderRow.Item("OrderId")
   detailRow.Item("ItemId") = 13
   …
   ' Add the row to the DataSet
   hierDataSet.Tables("Details").Rows.Add(detailRow)
   ' Create a new Row for the Details table
   detailRow = hierDataSet.Tables("Details").NewRow()
   detailRow.Item("OrderId") = orderRow.Item("OrderId")
   detailRow.Item("ItemId") = 12
   …
   ' Add the row to the DataSet
   hierDataSet.Tables("Details").Rows.Add(detailRow)
   sqlDAOrder.InsertCommand.Parameters("@Order").Value = _ 
    hierDataSet.GetXml()
   ' Reconcile changes with the data source
   sqlDAOrder.Update(hierDataSet, "Orders")
Catch E As Exception
Finally
    ' Cleanup code here
End Try
            

Note   See Example 5 in the BDAdotNetData4.vb sample code (see top of article for download).

Updates

Rows in the related tables can be updated as long as they do not disturb the data integrity. If the tables have to maintain the referential integrity, a child row must always refer to a valid row in the parent table. An update in the parent table can cascade to the child row to maintain the integrity.

Auto-generated Update command

You can update a row in a table in the DataSet object and call the Update method on the DataAdapter to submit changes to the data source. The DataAdapter will auto-generate the Update command based on the Select command you provided.

In the following code example, we move details from one order to another to show how to make the update action cascade:

Dim sqlConn As SqlConnection
Dim sqlDAOrder As SqlDataAdapter
Dim sqlDADetail As SqlDataAdapter
Dim sqlCmdBldrDetail As SqlCommandBuilder
Dim hierDS As DataSet
Try
   ' Create a new connection
   sqlConn = New SqlConnection(Common.getConnectionString)
   ' Create a new SqlDataAdapter object for the Order table
   sqlDAOrder = New SqlDataAdapter()
   ‘ Create a new SqlDataAdapter object for the OrderDetails table
   sqlDADetail = New SqlDataAdapter()
   ' Create a new DataSet
   hierDS = New DataSet()
   ' Create a new SQLCommandBuilder object to autogenerate update commands
   sqlCmdBldrDetail = New SqlCommandBuilder(sqlDADetail)
   With sqlDAOrder
      ' Add a SelectCommnad object
      .SelectCommand = New SqlCommand()
      ' Specify the Select Command
      With .SelectCommand
      .CommandType = CommandType.Text
      .CommandText = "Exec GetOrderHeader @OrderId=2"
      .Connection = sqlConn
      End With
      ' Populate the DataSet with the returned data
      .Fill(hierDS, "Orders")
   End With
   With sqlDADetail
      ' Add a SelectCommnad object
      .SelectCommand = New SqlCommand()
      ' Specify the Select Command
      With .SelectCommand
         .CommandType = CommandType.Text
         .CommandText = "Exec GetOrderDetails @OrderId=2"
         .Connection = sqlConn
      End With
      ' Populate the DataSet with the returned data
      .Fill(hierDS, "Details")
   End With
   ' Establish the Foreign Key relationship between the tables
   hierDS.Relations.Add("Order_Detail", _
hierDS.Tables("Orders").Columns("OrderId"), _
            hierDS.Tables("Details").Columns("OrderId"))
   hierDS.Tables("Orders").Columns("OrderId").ReadOnly = False
   ' Move details from one order to another
   orderRow = hierDataSet.Tables("Orders").Rows(0)
   orderRow("OrderId") = 1
   ' Reconcile Changes
   sqlDADetail.Update(hierDS, "Details")
Catch E As Exception
    ' Catch Exception here
Finally
    ' Cleanup code here
End Try

Note   See Example 6 in the BDAdotNetData4.vb sample code (see top of article for download).

Using the UpdateCommand property

The auto-generated executed a round trip to the database for each row moved. A stored procedure can move all detail rows in a single round trip. To specify your own Update statement, which should be executed when the Update method is called on the DataAdapter, set the UpdateCommand property. The UpdateCommand property can be set to a parameterized query or a stored procedure. The parameters for UpdateCommand are defined the same way they would be for the Command object:

Dim sqlConn As SqlConnection
Dim sqlDAOrder As SqlDataAdapter
Dim sqlDADetail As SqlDataAdapter
Dim hierDS As DataSet 
Try
   ' Create a new connection
   sqlConn = New SqlConnection(Common.getConnectionString)
   ' Create a new SqlDataAdapter object for the Order table
   sqlDAOrder = New SqlDataAdapter()
   ' Create a new SqlDataAdapter object for the OrderDetails table
   sqlDADetail = New SqlDataAdapter()
   ' Create a new DataSet
   hierDS = New DataSet()
   With sqlDAOrder
      ' Add a SelectCommand object
      .SelectCommand = New SqlCommand()
      ' Specify the Select Command 
      With .SelectCommand
         .CommandType = CommandType.Text
         .CommandText = "Exec GetOrderHeader @OrderId=1"
         .Connection = sqlConn
      End With
      ' Add a UpdateCommand object
      .UpdateCommand = New SqlCommand()
      ' Specify the Update Command
      With .UpdateCommand
         .CommandType = CommandType.StoredProcedure
         .CommandText = "MoveOrderDetails"
         ' Define parameters
         .Parameters.Add(New SqlParameter("@FromOrderId", SqlDbType.Int))
         .Parameters("@FromOrderId").Direction = ParameterDirection.Input
         .Parameters("@FromOrderId").SourceColumn = "OrderId"
         .Parameters("@FromOrderId").SourceVersion = _ 
 DataRowVersion.Original
         .Parameters.Add(New SqlParameter("@ToOrderId", SqlDbType.Int))
         .Parameters("@ToOrderId").Direction = ParameterDirection.Input
         .Parameters("@ToOrderId").SourceColumn = "OrderId"
         .Parameters("@ToOrderId").SourceVersion = DataRowVersion.Current
          .Connection = sqlConn
      End With
      ' Populate the DataSet with the returned data
      .Fill(hierDS, "Orders")
   End With
   With sqlDADetail
      ' Add a SelectCommand object
      sqlDADetail.SelectCommand = New SqlCommand()
      ' Specify the Select Command 
      With .SelectCommand
         .CommandType = CommandType.Text
         .CommandText = "Exec GetOrderDetails @OrderId=1"
         .Connection = sqlConn
      End With
      ' Populate the DataSet with the returned data
      .Fill(hierDS, "Details")
    End With
   ' Establish the Foreign Key relationship between the tables
   hierDataSet.Relations.Add("Order_Detail", _
hierDS.Tables("Orders").Columns("OrderId"), _
            hierDS.Tables("Details").Columns("OrderId"))
   hierDS.Tables("Orders").Columns("OrderId").ReadOnly = False
   ' Move details from one order to another
   orderRow = hierDS.Tables("Orders").Rows(0)
   orderRow("OrderId") = 2
   ' Reconcile Changes
   sqlDAOrder.Update(hierDS, "Orders")
Catch E As Exception
    ' Catch Exception here
Finally
    ' Cleanup code here
End Try

Note   See Example 7 in the BDAdotNetData4.vb sample code (see top of article for download).

The SourceVersion property allows the original and current value of OrderId to be passed to the appropriate stored procedure parameters to make the move.

Deletes

Deletes on hierarchical data should be performed in a manner that maintains the database integrity. Every child row in a referencing table must refer to a valid row in the referenced table. So, a row in the parent (referenced) table should not be deleted as long as it has one or more corresponding rows in the child (referencing) table, or unless the child rows also get deleted with the corresponding parent row.

The ADO .NET DataSet object supports cascading deletes, which ensures that the child rows in the referencing table also get deleted when you delete their corresponding rows in the referenced table. You can define the foreign key constraint on two tables within a DataSet object to determine the course of action that should be performed when any row in the tables is altered. Set the DeleteProperty method of the ForeignKeyConstraint property to one of the appropriate options; it is set to cascade by default.

Auto-generated Delete command

Dim sqlConn As SqlConnection
Dim sqlDAOrder As SqlDataAdapter
Dim sqlDADetail As SqlDataAdapter
Dim hierDS As DataSet
Dim sqlCmdBldrOrder As SqlCommandBuilder
Dim sqlCmdBldrDetail As SqlCommandBuilder
Try
   ' Create a new SQLConnection
   sqlConn = New SqlConnection(Common.getConnectionString)
   ' Create a new SqlDataAdapter object for the Order table
   sqlDAOrder = New SqlDataAdapter()
   ' Create a new SqlDataAdapter object for the OrderDetails table
   sqlDADetail = New SqlDataAdapter()
   ' Create a new DataSet object
   hierDS = New DataSet()
   ' Create the CommandBuilder objects for auto-generating commands
   sqlCmdBldrOrder = New SqlCommandBuilder(sqlDAOrder)
   sqlCmdBldrDetail = New SqlCommandBuilder(sqlDADetail)
   With sqlDAOrder
      ' Add a SelectCommand object
      sqlDAOrder.SelectCommand = New SqlCommand()
      ' Specify the Select Command 
      With .SelectCommand
      .CommandType = CommandType.StoredProcedure
      .CommandText = "GetOrderHeaders"
      .Connection = sqlConn
      End With
      ' Populate the DataSet with the returned data
      .Fill(hierDS, "Orders")
   End With
   With sqlDADetail
      ' Add a SelectCommand object
      sqlDADetail.SelectCommand = New SqlCommand()
      ' Specify the Select Command for sqlDADetail
      With .SelectCommand
         .CommandText = "Select * from OrderDetails"
         .Connection = sqlConn
      End With
      ' Populate the DataSet with the returned data
      .Fill(hierDS, "Details")
   End With
   ' Establish a relationship between the tables
   hierDS.Relations.Add("Order_Detail", _
hierDS.Tables("Orders").Columns("OrderId"), _
            hierDS.Tables("Details").Columns("OrderId"))
   ' Find the row last row in the Orders table
   orderRow = hierDS.Tables("Orders").Rows. _ 
Item(hierDS.Tables("Orders").Rows.Count - 1)
   ' Delete the Row from the Orders Table. This operation will  
   ' automatically delete all the corresponding child rows from the 
   ' Details table in the DataSet
   orderRow.Delete()
   …
   ' Reconcile changes with the data source
   sqlDADetail.Update(hierDS, "Details")
   sqlDAOrder.Update(hierDS, "Orders")
Catch E As Exception
    ' Catch Exception here
Finally
    ' Cleanup code here
End Try

Note   See Example 8 in the BDAdotNetData4.vb sample code (see top of article for download).

The child rows must first be deleted from the referencing table in the database to maintain the data integrity. If you try to delete the rows from the parent table before deleting the corresponding child rows in the other table, the delete will fail. So, the update on the sqlDADetail DataAdapter object for the Details table is called before the update on the sqlDAOrder DataAdapter object.

Using the DeleteCommand property

To specify your own Delete statement, which should be executed when the Update method is called on the DataAdapter, set the DeleteCommand property. The DeleteCommand property can be set to a parameterized query or a stored procedure. The parameters for DeleteCommand are defined the same way they would be for the Command object.

You need to set the SourceColumn property for all the parameters. SourceColumn tells the DataAdapter which column in the table is going to provide its value. Set it to the actual parameter name:

Dim sqlConn As SqlConnection
Dim sqlDAOrder As SqlDataAdapter
Dim sqlDaDetail As SqlDataAdapter
Dim hierDS As DataSet
Try
   ' Create a new SQLConnection
   sqlConn = New SqlConnection(Common.getConnectionString)
   ' Create a new SqlDataAdapter object for the Order table
   sqlDAOrder = New SqlDataAdapter()
   ' Create a new SqlDataAdapter object for the OrderDetails table
   sqlDaDetail = New SqlDataAdapter()
   ' Create a new DataSet
   hierDS = New DataSet()
   With sqlDAOrder
      ' Add a SelectCommand object
      .SelectCommand = New SqlCommand()
      ' Specify the Select Command
      With .SelectCommand
         .CommandType = CommandType.StoredProcedure
         .CommandText = "GetOrderHeaders"
         .Connection = sqlConn
      End With
      ' Add a DeleteCommand object
      .DeleteCommand = New SqlCommand()
      ' Set the properties of the DeleteCommand
      With .DeleteCommand
         .CommandType = CommandType.StoredProcedure
         .CommandText = "DeleteOrder"
         .Connection = sqlConn
         ' Define the parameters of the stored procedure
         .Parameters.Add(New SqlParameter("@OrderId", SqlDbType.Int))
         ' Set the SourceColumn property
         .Parameters("@OrderId").SourceColumn = "OrderId"
      End With
      ' Populate the DataSet with the returned data
      .Fill(hierDS, "Orders")
   End With
   With sqlDaDetail
      ' Add a SelectCommand object
      .SelectCommand = New SqlCommand()
      ' Specify the Select Command
      With .SelectCommand
         .CommandType = CommandType.Text
         .CommandText = "Select * from OrderDetails"
         .Connection = sqlConn
      End With
      ' Populate the DataSet with the returned data
      .Fill(hierDS, "Details")
   End With
   ' Establish a relationship between the tables
   …
   ' Find the row last row in the Orders table
   orderRow = hierDS.Tables("Orders").Rows. _ 
Item(hierDS.Tables("Orders").Rows.Count - 1)
   ' Delete the Row from the Orders Table. This operation will 
   ' automatically ‘delete all the corresponding child rows from the 
   ' Details table in the ‘DataSet
   orderRow.Delete()
   …
   ' Reconcile changes with the data source
   sqlDAOrder.Update(hierDS, "Orders")
Catch E As Exception
    ' Catch Exception here
Finally
    ' Cleanup code here
End Try

Note   See Example 9 in the BDAdotNetData4.vb sample code (see top of article for download).

Conclusion

For read-only access, the DataReader and XmlReader objects are simple and fast, although they remain connected to the data source while the application reads data. This could limit scalability if the application holds the DataReader or the XmlReader object long enough for contention to occur.

The DataSet object offers a disconnected relational cache, allowing simple hierarchical navigation and modification. Cascading writes simplify committing changes to the database but auto-generated Inserts, Updates, and Deletes tend to be less efficient than explicitly coded implementations especially when round-trip conservation and query caching are considered. Round-trip conservation should be addressed when the XML features of SQL Server 2000 are more fully utilized in upcoming beta versions of ADO .NET.

Show:
© 2014 Microsoft