Операции над данными с иерархической структурой. Разработка распределенных приложений в .NET

В этой статье рассматриваются операции над иерархическими наборами строк с помощью ADO.NET.

Содержание

Введение
Операции чтения
- Применение ADO.NET-объекта DataReader
- Применение XmlReader и ADO.NET-объекта SqlCommand
- Применение ADO.NET-объекта DataSet
Операции записи
- Применение ADO.NET-объекта DataAdapter
- Добавление строк
- Обновления
- Удаление
Заключение

Введение

Эта статья демонстрирует методику чтения и записи иерархических наборов строк в источнике данных. В примерах кода, приведенных в этой статье, для соединения с базой данных Microsoft^® SQL Server™ или Microsoft^® Desktop Engine (MSDE) используется управляемый провайдер SQL (SQL managed provider). Для соединения с другими OLEDB-совместимыми источниками данных следует применять управляемый провайдер ADO (ADO managed provider).

Для доступа к иерархическим строкам, возвращаемым источником данных, в ADO.NET используются объекты DataReader и DataSet. Объект DataReader обеспечивает простой и быстрый доступ к данным только для чтения. С помощью этого объекта можно обращаться либо к иерархическим строкам данных, полученным в результате выполнения нескольких операторов SELECT, либо к XML-данным, возвращаемым SQL Server 2000. Объект DataReader позволяет читать данные только в направлении вперед (forward-only) и остается соединенным с базой данных, пока приложение читает данные.

Объект DataSet, напротив, представляет отсоединенный реляционный кэш данных в памяти. Он может содержать несколько таблиц, рассматриваемых как отдельные или связанные таблицы. Особенности этого объекта облегчают навигацию по связанным данным и их модификацию.

Кроме того, SQL Server .NET Data Provider позволяет получить XML-поток напрямую от SQL Server 2000. Для этого предоставляется специальная API-функция, ExecuteXmlReader, доступная через объект SQLCommand. Метод ExecuteXmlReader выполняет SQL-запрос применительно к SQL-соединению и на основе XML, возвращенного запросом, создает объект XmlReader. ExecuteXmlReader используется только в выражениях, результатом которых являются XML-данные, и эффективен в запросах, где в выражениях с SELECT присутствует блок FOR XML.

Операции чтения

Иерархические строки часто структурированы как несколько наборов связанных строк. Для большей эффективности зачастую лучше извлекать несколько наборов строк за одно обращение к базе данных, чем по отдельности запрашивать каждый из них. Обычно для этого выполняется пакет SQL-выражений (batch of SQL expressions) или хранимая процедура с несколькими выражениями SELECT. Кроме того, если вы используете в операторе SELECT блок FOR XML, SQL Server 2000 возвращает иерархические строки в виде XML.

Применение ADO.NET-объекта DataReader

В следующем примере исполняется пакет из двух SQL-выражений SELECT и соответствующие наборы результатов (result sets) извлекаются через объект DataReader. Этот объект обеспечивает навигацию по данным в направлении только вперед, так что для дальнейшей работы данные скорее всего следует перенести в другой контейнер. Так как DataReader оставляет соединение с базой данной открытым, с точки зрения масштабируемости данные надо переносить быстро и как можно скорее закрывать соединение. Передавать объект DataReader между уровнями распределенного приложения нельзя.

Для перебора строки в наборах результатов применяются методы NextResult и Read объекта DataReader.

Dim sqlCmd As SQLCommand
Dim sqlDataRdr As SQLDataReader
Dim fld As Integer
Dim rptLine As String
Try
   ' Подготовить объект команды, исполняющий хранимую процедуру,
   ' которая возвращает Orders и OrderDetails
   sqlCmd = New SQLCommand()
   With sqlCmd
      .CommandType = CommandType.StoredProcedure
      .CommandText = "GetOrders"
      .Connection = New SqlConnection(myConnString)
   End With
   ' Открыть соединение
   sqlCmd.Connection.Open()
   ' Выполнить команду; результат передается в DataReader
   sqlDataRdr = sqlCmd.ExecuteReader()
   ' Перебирать строки в первом наборе результатов
   Do
      While (sqlDataRdr.Read)
         ' Здесь со строкой можно выполнить какие-либо действия
         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
      ' Переместить указатель на следующий набор результатов
      If Not (sqlDataRdr.NextResult) Then
         Exit Do
      End If
   Loop
Catch E As Exception
Finally
   ' Закрыть DataReader
   sqlDataRdr.Close()
End Try

См. Example 1 в исходном коде (EN) BDAdotNetData4.vb.

Применение XmlReader и ADO.NET-объекта SqlCommand

В Microsoft SQL Server 2000 встроена поддержка XML. Чтобы результаты выражений SELECT возвращались в виде XML, в этих выражениях нужно указывать блок FOR XML. Метод ExecuteXmlReader объекта SQLCommand позволяет получать XML-данные напрямую от SQL Server 2000. ExecuteXmlReader возвращает объект System.Xml.XmlReader, содержащий XML-данные, полученные от SQL Server 2000.

В следующем примере хранимая процедура, использующая FOR XML, запускается вызовом метода ExecuteXmlReader объекта SQLCommand.

Dim sqlConn As SqlConnection
Dim sqlCmd As SqlCommand
Dim xmlRdr As XmlReader
Try
   ' Создать новый объект соединения
   sqlConn = New SqlConnection(myConnString)
   ' Создать новый объект команды
   sqlCmd = New SqlCommand()
   ' Указать исполняемую команду
   With sqlCmd
      .CommandType = CommandType.StoredProcedure
      .CommandText = "GetOrdersXML"
      .Connection = sqlConn
   End With
   ' Открыть соединение
   sqlConn.Open()
   ' Выполнить команду и извлечь строку в DataReader
   xmlRdr = sqlCmd.ExecuteXmlReader()
   ' Перейти к корневому элементу
   xmlRdr.MoveToContent()
   ' Что-то делаем с данными
   outXML = xmlRdr.ReadOuterXml
   ' Перейти к следующему элементу
   xmlRdr.MoveToElement()
   ' Считать атрибут OrderId текущего элемента
   xmlRdr.MoveToAttribute("OrderId")
   …
Catch e As Exception
   ' Обработать исключение
Finally
    sqlConn.Close()
End Try

См. Example 2 в исходном коде (EN) BDAdotNetData4.vb.

Применение ADO.NET-объекта DataSet

Объект DataAdapter извлекает данные из источника и заполняет объекты DataTable внутри DataSet. Для выполнения запросов к базе данных объекту DataAdapter требуется объект Connection.

Если запрос возвращает несколько наборов результатов, DataSet сохраняет каждый из них в отдельной таблице. Между таблицами может существовать отношение (relationship).

Отношение между таблицами

Как только вы связываете две таблицы в DataSet через отношение (с помощью объекта DataRelation), навигация по ним упрощается. Кроме того, связывание облегчает выборку всех дочерних строк (объектов DataRow) одной таблицы для родительской строки в другой таблице (объекте DataTable). Для выборки дочерних строк используется перегруженный метод GetChildRows объекта DataRow.

Отношения устанавливаются созданием объекта DataRelation, который сопоставляет строки одной таблицы со строками другой. Эти отношения хранятся в объекте DataRelationCollection, который содержится в объекте DataSet.

Связывать таблицы в объекте DataSet не обязательно. Их можно оставить несвязанными. Однако, если между двумя таблицами существуют какие-либо отношения, например через внешний ключ (foreign key relation), то связывание таблиц упростит доступ к дочерним строкам в одном объекте DataTable из родительской строки в другом объекте DataTable.

В следующем примере кода демонстрируется выборка заказов и их позиций (order details) из таблиц Orders и OrderDetails. Объект DataSet содержит таблицы Orders и Details, соответствующие таблицам в базе данных. Связью между двумя таблицами служит столбец ’OrderId’, присутствующий в обоих объектах DataTables.

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
   ' Создать новый объект DataAdapter
   sqlDA = New SqlDataAdapter()
   ' Создать новый объект DataSet
   hierDS = New DataSet()
   ' Задать сопоставления таблиц
   sqlDA.TableMappings.Add("Orders", "Orders")
   sqlDA.TableMappings.Add("Orders1", "OrderDetails")
   With sqlDA
      ' Добавить объект SelectCommand
      .SelectCommand = New SqlCommand()
      ' Указать команду объекта SelectCommand
      With .SelectCommand
          .CommandType = CommandType.StoredProcedure
          .CommandText = "GetOrders"
          .Connection = New SqlConnection(myConnString)
      End With
      ' Заполнить DataSet возвращаемыми данными
      .Fill(hierDS, "Orders")
   End With

Так как до вызова метода Fill в объекте DataSet нет таблиц, объект SQLDataAdapter автоматически создает таблицы для DataSet и заполняет их возвращаемыми данными. Если таблицы созданы до вызова Fill, объект SQLDataAdapter просто заполняет существующие таблицы.

   ' Указать первичный ключ для таблиц
   hierDS.Tables("Orders").PrimaryKey = New DataColumn() _ 
{ hierDS.Tables("Orders").Columns("OrderId")}
   hierDS.Tables("OrderDetails").PrimaryKey = New DataColumn() _ 
{ hierDS.Tables("OrderDetails").Columns("OrderDetailId")}
   ' Установить между двумя таблицами отношение через внешний ключ
   hierDS.Relations.Add("Order_Detail", _
                    hierDS.Tables("Orders").Columns("OrderId"), _
                    hierDS.Tables("OrderDetails").Columns("OrderId"))
   ' Выбрать один заказ из таблицы
   orderRow = hierDS.Tables("Orders").Rows(0)
   ' Выбрать соответствующие ему дочерние строки 
   detailRows = orderRow.GetChildRows("Order_Detail")
   ' Работа с коллекцией дочерних строк
   For i = 0 To detailRows.Length - 1
      detailRow = detailRows(i)
      ' Что-то делаем со строкой таблицы OrderDetails
      strDetail = detailRow("OrderId").ToString & ", " & _
            detailRow("OrderDetailId").ToString & ", " & _ 
detailRow("ItemId").ToString & ", " & _
detailRow("UnitPrice").ToString & ", " & 
detailRow("Quantity").ToString)
   Next
Catch E As Exception
   ' Обработать исключение
Finally
   ' Закрыть соединение и выполнить другую очистку
End Try

См. Example 3 в исходном коде (EN) BDAdotNetData4.vb.

Операции записи

Фиксация (committing) изменений в иерархических данных, содержащих несколько наборов результатов из двух или более связанных таблиц, требует сохранения целостности данных. Например, ссылочная целостность означает, что внешний ключ в любой ссылающейся таблице (referencing table) должен указывать на существующую строку в таблице, на которую делается ссылка (referenced table). Следовательно, родительскую строку в этой таблице нельзя удалять до тех пор, пока на нее есть ссылка в другой таблице. Точно так же в ссылающуюся таблицу нельзя вставлять строки, если нет соответствующих строк в таблице, на которую она ссылается.

Так как ADO.NET-объект DataSet позволяет извлекать, обрабатывать и модифицировать данные в базе, он гарантирует ссылочную целостность таблиц при добавлении, изменении и удалении строк. Кроме того, этот объект позволяет выполнять каскадные обновления и удаления с сохранением целостности данных.

Применение ADO.NET-объекта DataAdapter

Метод Update объекта DataAdapter передает изменения, кэшированные в объекте DataSet, источнику данных. Для добавления новых строк DataAdapter использует InsertCommand, для изменения строк — UpdateCommand, а для удаления строк из базы данных — DeleteCommand. Когда вы вызываете метод Update, DataAdapter анализирует измененные строки и определяет, какой из объектов Command нужно выполнить для передачи отложенных изменений в каждой строке.

Прежде чем вызывать Update, вы должны настроить свойства InsertCommand, UpdateCommand или DeleteCommand — в зависимости от того, какие изменения были внесены в данные в DataSet. Например, если из DataSet удалялись строки, следует установить свойство DeleteCommand. Для автоматического формирования команд Insert, Update и Delete можно задействовать преимущества объекта CommandBuilder. Если вы указываете DataAdapter-свойства InsertCommand, UpdateCommand или DeleteCommand, метод Update соответственно выполняет команды insert, update или delete для каждой вставленной, обновленной или удаленной строки в DataSet. В ином случае CommandBuilder — в зависимости от значения свойства SelectCommand объекта DataAdapter — генерирует SQL-команды, необходимые для внесения изменений в базу данных. Поэтому, чтобы CommandBuilder генерировал команды Insert, Update и Delete, вы должны соответственно настроить свойство SelectCommand.

Лучше всего самостоятельно указывать InsertCommand, DeleteCommand или UpdateCommand, поскольку это позволяет вам явно контролировать, как выполняется обновление, и повышает производительность по сравнению с тес случаем, когда команды генерируются автоматически. Это особенно важно, если ваш код за одно обращение к источнику данных выполняет операции над несколькими строками, чтобы сократить частоту обмена данными с сервером.

Для свойств InsertCommand, UpdateCommand и DeleteCommand объекта DataAdapter можно указывать параметризованные запросы или хранимые процедуры. Параметры в параметризованных запросах или процедурах соответствуют столбцам в объекте DataTable. Таким образом, один объект DataAdapter поддерживает обновления только одной таблицы в вашей базе данных. Поэтому при обновлении базы данных для каждой таблицы в объекте DataSet потребуется отдельный объект DataAdapter.

Добавление строк

В дочернюю таблицу строки можно добавлять, только если они соответствуют допустимым (уже существующим) строкам родительской таблицы. Строки, не соответствующие допустимым строкам родительской таблицы, добавлять нельзя.

Новую строку нужно вставить сначала в родительскую таблицу; только после этого в дочернюю таблицу можно добавлять соответствующие строки. По той же причине в процессе синхронизации изменений с базой данных метод Update объекта DataAdapter, соответствующего родительской таблице, следует вызывать первым.

Автоматическое формирование команды Insert

Проблема с автоматически генерируемыми командами вставки в том, что объекту DataSet не возвращается первичный ключ Id для столбца Identity. Для решения этой проблемы мы используем хранимую процедуру, которая возвращает первичный ключ для родительской строки. Тогда появляется возможность применять автоматически формируемые команды вставки для дочерних строк. В следующем примере мы создаем два объекта DataAdapter, заполняющие две таблицы в одном объекте DataSet. Мы задаем отношение между этими двумя таблицами и вставляем в них новые строки. Метод Update объекта DataAdapter, соответствующего родительской таблице (в нашем случае — Order), вызывается первым. Затем вызывается метод Update объекта DataAdapter, соответствующего дочерней таблице (в нашем случае — 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
   ' Создать новый объект SQLConnection
   sqlConn = New SqlConnection(Common.getConnectionString)
   ' Создать новый объект SQLDataAdapter для таблицы Order
   sqlDAOrder = New SqlDataAdapter()
   ' Создать новый объект SqlDataAdapter для таблицы OrderDetails
   sqlDADetail = New SqlDataAdapter()
   ' Создать новый DataSet
   hierDS = New DataSet()
   ' Создать новый объект SQLCommandBuilder для автоматической генерации
   ' выражений Update
   sqlCmdBldrDetail = New SqlCommandBuilder(sqlDADetail)
   With sqlDAOrder
      ' Добавить объект SelectCommand
      .SelectCommand = New SqlCommand()
      ' Указать команду Select
      With .SelectCommand
         .CommandType = CommandType.Text
         .CommandText = "Exec GetOrderHeader @OrderId=-1"
         .Connection = sqlConn
      End With
     ' Добавить объект InsertCommand
      .InsertCommand = New SqlCommand()
      ' Указать команду Insert
      With .InsertCommand
         .CommandType = CommandType.StoredProcedure
         .CommandText = "InsertOrderHeader"
         .Connection = sqlConn
         ' Определить параметры параметризованного запроса Insert
         .Parameters.Add _
               (New SqlParameter("@CustomerId", SqlDbType.Int))
         ' Задать свойство Direction
         .Parameters("@CustomerId").Direction = ParameterDirection.Input
         ' Задать свойство SourceColumn
         .Parameters("@CustomerId").SourceColumn = "CustomerId"
         .Parameters.Add _
               (New SqlParameter("@OrderDate", SqlDbType.DateTime))
         ' Задать свойство Direction
         .Parameters("@OrderDate").Direction = ParameterDirection.Input
         ' Задать свойство SourceColumn
         .Parameters("@OrderDate").SourceColumn = "OrderDate"
         …
         …
         .Parameters.Add _
               (New SqlParameter("@OrderId", SqlDbType.Int))
         ' Задать свойство Direction
         .Parameters("@OrderId").Direction = ParameterDirection.Output
         ' Задать свойство SourceColumn
         .Parameters("@OrderId").SourceColumn = "OrderId"
      End With
      ' Заполнить таблицу Orders данными
      .Fill(hierDS, "Orders")
   End With
   With sqlDADetail
      ' Добавить объект SelectCommand
      .SelectCommand = New SqlCommand()
      ' Указать команду Select
      With .SelectCommand
         .CommandType = CommandType.Text
         .CommandText = "Exec GetOrderDetails @OrderId=-1"
         .Connection = sqlConn
      End With
      ' Заполнить таблицу Details данными
      .Fill(hierDS, "Details")
   End With
   ' Установить связь между таблицами
   hierDS.Relations.Add("Order_Detail", _
hierDS.Tables("Orders").Columns("OrderId"), _
hierDS.Tables("Details").Columns("OrderId"))
   ' Создать новую строку для таблицы Orders
   orderRow = hierDS.Tables("Orders").NewRow()
   ' Указать значения каждого столбца в таблице Orders
   orderRow.Item("CustomerId") = 1
   orderRow.Item("OrderStatus") = 400
   orderRow.Item("OrderDate") = Now()
   …
   ' Добавить строку к DataSet
   hierDS.Tables("Orders").Rows.Add(orderRow)
   ' Синхронизировать изменения с источником данных
   sqlDAOrder.Update(hierDS, "Orders")
   ' Создать новую строку для таблицы Details
   detailRow = hierDS.Tables("Details").NewRow()
   detailRow.Item("OrderId") = orderRow.Item("OrderId")
   detailRow.Item("ItemId") = 13
   …
   ' Добавить строку к DataSet
   hierDS.Tables("Details").Rows.Add(detailRow)
   ' Синхронизировать изменения с источником данных
   sqlDADetail.Update(hierDS, "Details")
Catch e As Exception
   ' Обработать исключение
Finally
   ' Выполнить очистку
End Try

См. Example 4 в исходном коде (EN) BDAdotNetData4.vb.

Обратите внимание: если новые строки, добавленные к дочерней таблице, соответствуют новой строке, добавленной к родительской таблице, то для сохранения целостности данных сначала обновляется родительская таблица. Дочерняя строка всегда должна ссылаться на допустимую строку в родительской таблице.

Использование свойства InsertCommand

В этом примере заголовок заказа и его позиции передаются хранимой процедуре в виде XML, что позволяет выполнить транзакцию за одно обращение к базе данных.

Чтобы указать собственное выражение INSERT, выполняемое при вызове метода Update применительно к DataAdapter, следует задать свойство InsertCommand. Значением этого свойства может быть параметризированный запрос или хранимая процедура. Параметры InsertCommand определяются так же, как и параметры объекта Command. Управляемый провайдер SQL поддерживает именованные параметры.

Для каждого параметра нужно установить свойство SourceColumn. Оно сообщает объекту DataAdapter, в каком столбце таблицы содержится значение параметра.

Dim sqlConn As SqlConnection
Dim sqlDAOrder As SqlDataAdapter
Dim sqlDADetail As SqlDataAdapter
Dim hierDS As DataSet
Try
   ' Создать новое соединение
   sqlConn = New SqlConnection(Common.getConnectionString)
   ' Создать новый объект SqlDataAdapter для таблицы Order
   sqlDAOrder = New SqlDataAdapter()
   ' Создать новый объект SqlDataAdapter для таблицы OrderDetails
   sqlDADetail = New SqlDataAdapter()
   ' Создать новый DataSet
   hierDS = New DataSet()
   With sqlDAOrder
      ' Добавить объект SelectCommand
      .SelectCommand = New SqlCommand()
      ' Указать команду Select 
      With .SelectCommand
         .CommandType = CommandType.Text
         .CommandText = "Exec GetOrderHeader @OrderId=-1"
         .Connection = sqlConn
      End With
      ' Добавить объект InsertCommand 
       .InsertCommand = New SqlCommand()
       ' Указать команду Insert
      With .InsertCommand
         .CommandType = CommandType.StoredProcedure
         .CommandText = "InsertOrder"
         .Connection = sqlConn
         ' Задать параметры параметризованного выражения Insert
         .Parameters.Add _
             (New SqlParameter("@Order", SqlDbType.NVarChar, 4000))
         ' Установить свойство Direction
         .Parameters("@Order").Direction = ParameterDirection.Input
         .Parameters.Add _
               (New SqlParameter("@OrderId", SqlDbType.Int))
         ' Установить свойство Direction
         .Parameters("@OrderId").Direction = ParameterDirection.Output
         ' Установить свойство SourceColumn
         .Parameters("@OrderId").SourceColumn = "OrderId"
      End With
      ' Заполнить DataSet возвращенными данными
      .Fill(hierDS, "Orders")
   End With
   With sqlDADetail
      ' Добавить объект SelectCommand
      .SelectCommand = New SqlCommand()
      ' Указать команду Select для объекта sqlDADetail
      With .SelectCommand
         .CommandType = CommandType.Text
         .CommandText = "Exec GetOrderDetails @OrderId=-1"
        .Connection = sqlConn
      End With
      ' Заполнить DataSet возвращенными данными
      .Fill(hierDS, "Details")
   End With 
   ' Установить между двумя таблицами отношение через внешний ключ
    hierDS.Relations.Add("Order_Detail", _
hierDS.Tables("Orders").Columns("OrderId"), _
hierDS.Tables("Details").Columns("OrderId"))
   ' Создать новую строку в таблице Orders
   orderRow = hierDS.Tables("Orders").NewRow()
   ' Задать значение каждого столбца в таблице Orders
   orderRow.Item("OrderId") = -1
   orderRow.Item("CustomerId") = 1
   orderRow.Item("OrderStatus") = 400
   …
   ' Добавить строку к DataSet
   hierDataSet.Tables("Orders").Rows.Add(orderRow)
   ' Создать новую строку в таблице Details
   detailRow = hierDataSet.Tables("Details").NewRow()
   detailRow.Item("OrderId") = orderRow.Item("OrderId")
   detailRow.Item("ItemId") = 13
   …
   ' Добавить строку к DataSet
   hierDataSet.Tables("Details").Rows.Add(detailRow)
   ' Создать новую строку в таблице Details
   detailRow = hierDataSet.Tables("Details").NewRow()
   detailRow.Item("OrderId") = orderRow.Item("OrderId")
   detailRow.Item("ItemId") = 12
   …
   ' Добавить строку к DataSet
   hierDataSet.Tables("Details").Rows.Add(detailRow)
   sqlDAOrder.InsertCommand.Parameters("@Order").Value = _ 
    hierDataSet.GetXml()
   ' Синхронизировать изменения с источником данных
   sqlDAOrder.Update(hierDataSet, "Orders")
Catch E As Exception
   ' Обработать исключения
Finally
   ' Выполнить очистку
End Try

См. Example 5 в исходном коде (EN) BDAdotNetData4.vb.

Обновления

При обновлениях строк в связанных таблицах должна сохраняться целостность данных. Для обеспечения ссылочной целостности дочерняя строка должна ссылаться на допустимую строку в родительской таблице. При этом можно использовать каскадное обновление.

Автоматическая генерация команды Update

Изменения передаются источнику данных после обновления строки в таблице объекта DataSet и вызова метода Update объекта DataAdapter. Последний автоматически генерирует команду Update на основе предоставленной вами команды Select.

В следующем примере кода мы переносим позиции из одного заказа в другой, демонстрируя, как осуществляется каскадное обновление.

Dim sqlConn As SqlConnection
Dim sqlDAOrder As SqlDataAdapter
Dim sqlDADetail As SqlDataAdapter
Dim sqlCmdBldrDetail As SqlCommandBuilder
Dim hierDS As DataSet
Try
   ' Создать новое соединение
   sqlConn = New SqlConnection(Common.getConnectionString)
   ' Создать новый объект SqlDataAdapter для таблицы Order
   sqlDAOrder = New SqlDataAdapter()
   ' Создать новый объект SqlDataAdapter для таблицы OrderDetails
   sqlDADetail = New SqlDataAdapter()
   ' Создать новый DataSet
   hierDS = New DataSet()
   ' Создать новый объект SQLCommandBuilder, автоматически формирующий
   ' команды Update
   sqlCmdBldrDetail = New SqlCommandBuilder(sqlDADetail)
   With sqlDAOrder
      ' Добавить объект SelectCommand
      .SelectCommand = New SqlCommand()
      ' Указать команду Select 
      With .SelectCommand
      .CommandType = CommandType.Text
      .CommandText = "Exec GetOrderHeader @OrderId=2"
      .Connection = sqlConn
      End With
      ' Заполнить DataSet возвращенными данными
      .Fill(hierDS, "Orders")
   End With
   With sqlDADetail
      ' Добавить объект SelectCommand
      .SelectCommand = New SqlCommand()
      ' Указать команду Select
      With .SelectCommand
         .CommandType = CommandType.Text
         .CommandText = "Exec GetOrderDetails @OrderId=2"
         .Connection = sqlConn
      End With
      ' Заполнить DataSet возвращенными данными
      .Fill(hierDS, "Details")
   End With
   ' Установить между двумя таблицами отношение через внешний ключ
   hierDS.Relations.Add("Order_Detail", _
hierDS.Tables("Orders").Columns("OrderId"), _
            hierDS.Tables("Details").Columns("OrderId"))
   hierDS.Tables("Orders").Columns("OrderId").ReadOnly = False
   ' Перенести позиции из одного заказа в другой
   orderRow = hierDataSet.Tables("Orders").Rows(0)
   orderRow("OrderId") = 1
   ' Синхронизировать изменения
   sqlDADetail.Update(hierDS, "Details")
Catch E As Exception
   ' Обработать исключение
Finally
   ' Выполнить очистку
End Try

См. Example 6 в исходном коде (EN) BDAdotNetData4.vb.

Использование свойства UpdateCommand

Автоматически сформированная команда перемещала каждую строку за одно обращение к базе данных, а хранимая процедура могла бы переместить за одно обращение все строки. Чтобы указать собственное выражение Update, исполняемое при вызове метода Update применительно к DataAdapter, задайте свойство UpdateCommand. Его значением может быть параметризированный запрос или хранимая процедура. Параметры UpdateCommand определяются так же, как и параметры объекта Command.

Dim sqlConn As SqlConnection
Dim sqlDAOrder As SqlDataAdapter
Dim sqlDADetail As SqlDataAdapter
Dim hierDS As DataSet 
Try
   ' Создать новое соединение
   sqlConn = New SqlConnection(Common.getConnectionString)
   ' Создать новый объект SqlDataAdapter для таблицы Order
   sqlDAOrder = New SqlDataAdapter()
   ' Создать новый объект SqlDataAdapter для таблицы OrderDetails
   sqlDADetail = New SqlDataAdapter()
   ' Создать новый DataSet
   hierDS = New DataSet()
   With sqlDAOrder
      ' Добавить объект SelectCommand
      .SelectCommand = New SqlCommand()
      ' Указать команду Select 
      With .SelectCommand
         .CommandType = CommandType.Text
         .CommandText = "Exec GetOrderHeader @OrderId=1"
         .Connection = sqlConn
      End With
      ' Добавить объект UpdateCommand
      .UpdateCommand = New SqlCommand()
      ' Указать команду Update
      With .UpdateCommand
         .CommandType = CommandType.StoredProcedure
         .CommandText = "MoveOrderDetails"
         ' Указать параметры
         .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
      ' Заполнить DataSet возвращенными данными
      .Fill(hierDS, "Orders")
   End With
   With sqlDADetail
      ' Добавить объект SelectCommand
      sqlDADetail.SelectCommand = New SqlCommand()
      ' Указать команду Select 
      With .SelectCommand
         .CommandType = CommandType.Text
         .CommandText = "Exec GetOrderDetails @OrderId=1"
         .Connection = sqlConn
      End With
      ' Заполнить DataSet возвращенными данными
      .Fill(hierDS, "Details")
    End With
   ' Установить между двумя таблицами отношение через внешний ключ
   hierDataSet.Relations.Add("Order_Detail", _
hierDS.Tables("Orders").Columns("OrderId"), _
            hierDS.Tables("Details").Columns("OrderId"))
   hierDS.Tables("Orders").Columns("OrderId").ReadOnly = False
   ' Перенести позиции из одного заказа в другой
   orderRow = hierDS.Tables("Orders").Rows(0)
   orderRow("OrderId") = 2
   ' Синхронизировать изменения
   sqlDAOrder.Update(hierDS, "Orders")
Catch E As Exception
   ' Обработать исключение
Finally
   ' Выполнить очистку
End Try

См. Example 7 в исходном коде (EN) BDAdotNetData4.vb.

Свойство SourceVersion позволяет передавать исходное и текущее значение OrderId в соответствующие параметры хранимой процедуры.

Удаление

Удаляя иерархические данные, вы должны позаботиться о сохранении целостности базы данных. Каждая дочерняя строка должна ссылаться на допустимую строку в родительской таблице. Поэтому удалять строку из родительской таблицы нельзя до тех пор, пока существует хотя бы одна связанная с ней строка в дочерней таблице.

В ADO.NET объект DataSet поддерживает каскадное удаление, которое позволяет удалять дочерние строки одновременно с соответствующими родительскими строками. Чтобы определить действия, необходимые при каких-либо изменениях строк в таблицах, вы должны указать ограничения внешнего ключа для двух таблиц в DataSet. Настройте метод DeleteProperty свойства ForeignKeyConstraint на один из подходящих режимов работы (по умолчанию операции выполняются в каскадном режиме).

Автоматически генерируемая команда Delete

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
   ' Создать новый SQLConnection
   sqlConn = New SqlConnection(Common.getConnectionString)
   ' Создать новый объект SqlDataAdapter для таблицы Order
   sqlDAOrder = New SqlDataAdapter()
   ' Создать новый объект SqlDataAdapter для таблицы OrderDetails
   sqlDADetail = New SqlDataAdapter()
   ' Создать новый объект DataSet
   hierDS = New DataSet()
   ' Создать объекты CommandBuilder, автоматически формирующие команды
   sqlCmdBldrOrder = New SqlCommandBuilder(sqlDAOrder)
   sqlCmdBldrDetail = New SqlCommandBuilder(sqlDADetail)
   With sqlDAOrder
      ' Добавить объект SelectCommand
      sqlDAOrder.SelectCommand = New SqlCommand()
      ' Указать команду Select 
      With .SelectCommand
      .CommandType = CommandType.StoredProcedure
      .CommandText = "GetOrderHeaders"
      .Connection = sqlConn
      End With
      ' Заполнить DataSet возвращенными данными
      .Fill(hierDS, "Orders")
   End With
   With sqlDADetail
      ' Добавить объект SelectCommand
      sqlDADetail.SelectCommand = New SqlCommand()
      ' Указать команду Select для объекта sqlDADetail
      With .SelectCommand
         .CommandText = "Select * from OrderDetails"
         .Connection = sqlConn
      End With
      ' Заполнить DataSet возвращенными данными
      .Fill(hierDS, "Details")
   End With
   ' Установить связь между таблицами
   hierDS.Relations.Add("Order_Detail", _
hierDS.Tables("Orders").Columns("OrderId"), _
            hierDS.Tables("Details").Columns("OrderId"))
   ' Найти последнюю строку в таблице Orders
   orderRow = hierDS.Tables("Orders").Rows. _ 
Item(hierDS.Tables("Orders").Rows.Count - 1)
   ' Удалить строку из таблицы Orders. В результате этого
   ' автоматически удаляются соответствующие дочерние строки
   ' из таблицы Details в DataSet.
   orderRow.Delete()
   …
   ' Синхронизировать изменения с источником данных
   sqlDADetail.Update(hierDS, "Details")
   sqlDAOrder.Update(hierDS, "Orders")
Catch E As Exception
   ' Обработать исключение
Finally
   ' Выполнить очистку
End Try

См. Example 8 в исходном коде (EN) BDAdotNetData4.vb.

Для поддержания целостности данных дочерние строки должны удаляться первыми. Удалить родительские строки до удаления соответствующих дочерних строк нельзя. Поэтому обновление DataAdapter-объекта sqlDADetail для таблицы Details выполняется до обновления DataAdapter-объекта sqlDAOrder.

Использование свойства DeleteCommand

Чтобы указать собственное выражение Delete, выполняемое при вызове метода Update применительно к DataAdapter, используйте свойство DeleteCommand. Его значением является параметризированный запрос или хранимая процедура. Параметры для DeleteCommand определяются так же, как и для объекта Command.

Для каждого параметра нужно задать свойство SourceColumn. Оно сообщает DataAdapter, в каком столбце содержится значение параметра.

Dim sqlConn As SqlConnection
Dim sqlDAOrder As SqlDataAdapter
Dim sqlDaDetail As SqlDataAdapter
Dim hierDS As DataSet
Try
   ' Создать новый SQLConnection
   sqlConn = New SqlConnection(Common.getConnectionString)
   ' Создать новый объект SqlDataAdapter для таблицы Order
   sqlDAOrder = New SqlDataAdapter()
   ' Создать новый объект SqlDataAdapter для таблицы OrderDetails
   sqlDaDetail = New SqlDataAdapter()
   ' Создать новый DataSet
   hierDS = New DataSet()
   With sqlDAOrder
      ' Добавить объект SelectCommand
      .SelectCommand = New SqlCommand()
      ' Указать команду Select
      With .SelectCommand
         .CommandType = CommandType.StoredProcedure
         .CommandText = "GetOrderHeaders"
         .Connection = sqlConn
      End With
      ' Добавить объект DeleteCommand
      .DeleteCommand = New SqlCommand()
      ' Задать свойства DeleteCommand
      With .DeleteCommand
         .CommandType = CommandType.StoredProcedure
         .CommandText = "DeleteOrder"
         .Connection = sqlConn
         ' Определить параметры хранимой процедуры
         .Parameters.Add(New SqlParameter("@OrderId", SqlDbType.Int))
         ' Настроить свойство SourceColumn
         .Parameters("@OrderId").SourceColumn = "OrderId"
      End With
      ' Заполнить DataSet возвращенными данными
      .Fill(hierDS, "Orders")
   End With
   With sqlDaDetail
      ' Добавить объект SelectCommand
      .SelectCommand = New SqlCommand()
      ' Указать команду Select
      With .SelectCommand
         .CommandType = CommandType.Text
         .CommandText = "Select * from OrderDetails"
         .Connection = sqlConn
      End With
      ' Заполнить DataSet возвращенными данными
      .Fill(hierDS, "Details")
   End With
   ' Установить связь между двумя таблицами
   …
   ' Найти последнюю строку в таблице Orders
   orderRow = hierDS.Tables("Orders").Rows. _ 
Item(hierDS.Tables("Orders").Rows.Count - 1)
   ' Удалить строку из таблицы Orders. В результате автоматически
   ' удаляются соответствующие дочерние строки из таблицы
   ' Details в DataSet.
   orderRow.Delete()
   …
   ' Синхронизировать изменения с источником данных
   sqlDAOrder.Update(hierDS, "Orders")
Catch E As Exception
   ' Обработать исключение
Finally
   ' Выполнить очистку
End Try

См. Example 9 в исходном коде (EN) BDAdotNetData4.vb.

Заключение

При доступе к данным только для чтения объекты DataReader и XmlReader просты в использовании и работают быстро, хотя соединение с базой данных поддерживается до тех пор, пока приложение читает данные. Если приложение достаточно долго держит эти объекты, может возникнуть конкуренция (contention), что ограничит масштабируемость такого приложения.

Объект DataSet представляет отсоединенный реляционный кэш, а также упрощает навигацию по иерархическим данным и их модификацию. Каскадная запись облегчает фиксацию изменений в базе данных, но автоматически генерируемые выражения Insert, Update и Delete менее эффективны по сравнению с теми, которые пишутся вручную, — особенно с точки зрения уменьшения частоты обращений к базе данных и кэширования запросов. Сокращение числа обращений к базе данных станет еще актуальнее, когда XML-средства SQL Server 2000 станут полнее использовать возможности ADO.NET.