10. Работа с данными средствами ADO.NET

Объекты ADO.NET DataReader и DataView, применение в приложениях ASP.NET 2.0

Этот модуль посвящен дополнительным вомзожностям объектной модели ADO.NET.

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

Чем отличается от DataSet объект DataReader:

·        к данным через DataReader можно обращаться только на чтение;

·        DataSet может обращаться к нескольким таблицам на источнике. DataReader - это всегда данные, которые возвращаются единственной командой, выполняемой на источнике;

·        DataSet работает в рассоединенном режиме, DataReader - только в соединенном;

·        DataSet можно привязать к нескольким элементам управления на Web-форме, DataReader - только к одному;

·        DataReader работает быстрее, но данные передаются только в одном направлении;

·        графического интерфейса для работы с DataReader в VS.NET не предусмотрено.

Если вы подключаетесь к SQL Server из приложения ASP.NET, вы должны выбирать между аутентификацией SQL Server и аутентификацией Windows. При аутентификации SQL Server имя пользователя и пароль для подключения придется прописывать в HTML (и, возможно, передавать его в незащищенном виде по HTTP, если используется форма для ввода имени пользователя и пароля). Прописывается имя пользователя и пароль в Connection string, которую можно генерировать стандартными средствами VS.NET.

Если используется аутентификация Windows (более рекомендованный способ), то имя пользователя и пароль по сети не передаются, а обращение к SQL Server производится от имени специальной учетной записи ASPNET - ей и нужно выдавать необходимые разрешения.

Теперь о том, как работать с объектами ADO.NET не через графический интерфейс, а программно.

Само установление соединения производится при помощи объекта SqlConnection или OleDbConnection. Главное и фактически единственное свойство этого объекта - ConnectionString, в котором и содержится вся информация о соединении. Значение этого свойства можно передавать в качестве параметра при создании этого объекта:

Dim strConn As String = _

"data source=localhost; " & _

"initial catalog=northwind; " & _

"integrated security=true"

Dim conn As New SqlConnection(strConn)

При использовании объекта DataSet взаимодействие с источником реально производит встроенный объект DataAdapter (их, как обычно, две разновидности - SqlDataAdapter и OleDbDataAdapter). Физически DataAdapter - это набор SQL-команд и указатель на объект соединения. Главные свойства DataAdapter:

·        SelectCommand - представляет команду, при помощи которой данные возвращаются с источника;

·        InsertCommand, UpdateCommand, DeleteCommand - понятно по названию.

Создать объект DataAdapter можно, например, так:

’Create a connection

Dim conn As New SqlConnection _

("data source=localhost;initial catalog=pubs;" & _

"integrated security=true;persist security info=True;")

’Create the DataAdapter

Dim da As New SqlDataAdapter _

("select * from Authors", conn)

Теперь настало время создать объект DataSet. Само создание производится очень просто:

Dim ds As New DataSet()

сложнее создать в нем необходимые вложенные объекты, такие, как DataTable. Проще всего это сделать при помощи метода Fill объекта DataAdapter:

da.Fill(ds, "Authors")

В DataSet автоматически создается объект DataTable с именем Authors, структура и наполнение которого определяется тем, что вернул запрос, определенный для объекта DataAdapter. Обратиться к этому объекту можно через его имя в коллекции Tables

ds.Tables("Authors")

или просто по номеру:

ds.Tables(0)

Доступ к определенным столбцам и строкам можно получить через коллекции Columns и Rows объекта DataTable:

Dim col As DataColumn

For Each col In ds.Tables(0).Columns

lstItems.Items.Add(col.ColumnName)

Next

или

ds.Tables("Authors").Rows.Count

ds.Tables("Authors").Columns.Count

Обращаться к конкретным полям в таблице можно как по их номерам (строка/столбец), так и по номеру строки и имени столбца:

DataSet.Tables(0).Rows(x)(1)

DataSet.Tables(0).Rows(x)("fieldname")

Пройтись циклом по всем записям в объекте DataTable, созданном на основе таблице Authors, и поместить в строковую переменную значения второго столбца и столбца с именем au_lname можно так:

Dim r As DataRow

Dim str As String

For Each r in ds.Tables("Authors").Rows

str &= r(1)

str &= r("au_lname")

Next

DataSet можно привязывать к элементу управления на форме напрямую, а можно использовать промежуточный объект DataView. DataView (как и View в базе данных) - это настраиваемое представление данных из DataSet. После создания DataView его можно использовать для сортировки данных, фильтрации, поиска, редактирования и навигации.

Для любого объекта DataTable объект DataView с параметрами по умолчанию создается автоматически. Получить ссылку на него можно так:

Dim dv As DataView = ds.Tables("Authors").DefaultView

Конечно же, можно создать и свой собственный DataView, в который, к примеру, попадут только интересующие вас записи:

Dim dv As New DataView(ds.Tables("Authors"))

dv.RowFilter = "state = ’CA’"

dv.Sort = "au_lname"

После того, как DataSet со всеми необходимыми подобъектами создан, можно создавать элементы управления для работы с данными на форме и привязывать к ним этот DataSet.

Например, у нас есть объект DataGrid:

<asp:DataGrid id="dg" runat="server" />

для того, чтобы привязать его к DataSet, можно использовать такой код:

dg.DataSource = ds

dg.DataMember = "Authors"

dg.DataBind()

По умолчанию элемент управления привязывается к первому объекту DataTable (с нулевым индексом). Чтобы отобразить другой объект DataTable или объект DataView, нужно использовать свойство DataMember (как показано выше) или передавать информацию о нужной таблице напрямую свойству DataSource:

dg.DataSource = ds.Tables("Authors")

dg.DataBind()

Точно так же можно передавать элементу управления для отображения вместо объекта DataTable объект DataView:

Dim dv As New DataView(ds.Tables("Authors"))

dv.RowFilter = "state = ’CA’"

dg.DataSource = dv

dg.DataBind()

Теперь - про перехват ошибок при работе с объектами ADO.NET.

Чаще всего встречаются следующие ошибки:

·        невозможно установить соединение (отсутствует сеть, отключен сервер и т.п.) Обработчик ошибок нужно, конечно, ставить на открытие соединения. Код, который обрабатывает наиболее распространенные ошибки при открытии соединения, может выглядеть так:

Try

Dim conn As New SqlConnection(...)

Dim da As New SqlDataAdapter(..., conn)

Dim ds As New DataSet()

da.Fill(ds)

Catch ex1 As System.Data.SqlClient.SqlException

Select Case ex1.Number

Case 17

lblErrors.Text = lblErrors.Text & _

("invalid Server name")

Case 156, 170 ’bad SQL syntax

lblErrors.Text = lblErrors.Text & _

("incorrect syntax")

Case 207 ’bad field name in select

lblErrors.Text = lblErrors.Text & _

("invalid column name")

Case 208 ’bad table name in select

lblErrors.Text = lblErrors.Text & _

("invalid object name")

Case 18452

lblErrors.Text = lblErrors.Text & _

("invalid user name")

Case 18456

lblErrors.Text = lblErrors.Text & _

("invalid password")

Case 4060

lblErrors.Text = lblErrors.Text & _

("invalid database")

End Select

Catch ex2 As System.Exception

lblErrors.Text = lblErrors.Text & _

("Unexpected exception: " & ex2.Message & ". ")

End Try

·        невозможно выполнить команду на сервере (или при выполнении возникли какие-то проблемы). Самые распространенные причины - неверный синтаксис, неправильно указано имя объекта и т.п. Для перехвата используется тот же объект SqlException, в котором предусмотрена коллекция Errors для всех ошибок и предупреждений, которые вернул сервер:

Dim erData As SqlClient.SqlErrorCollection = ex1.Errors

Dim i As Integer

For i = 0 To erData.Count - 1

lblErrors.Text &= ("Error " & i & ": " & _

erData(i).Number & ", " & _

erData(i).Class & ", " & _

erData(i).Message & "<br>")

Next i

Для объектов SqlError, из которых состоит эта коллекция, предусмотрены следующие свойства:

·        Class - уровень важности ошибки в соответствии с градациями SQL Server

·        LineNumber - номер строки в пакете или хранимой процедуре, при выполнении которой возникла ошибка

·        Message - просто текст ошибки

·        Number - номер ошибки

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

Для того, чтобы заполнить DataSet объектами DataTable вам потребуется свой объект DataAdapter для каждого DataTable. Еще один момент: порядок работы с DataAdapter может быть важен, если вы производите вставку информации из формы в таблицы, связанные через primary/foreign key: конечно, вначале нужно производить вставку в таблицу, где primary key через соответствующий DataAdapter. Например, у нас есть две таблицы: Customers и Orders. Помещение их в DataSet может выглядеть так:

Dim conn As SqlConnection

Dim daCustomers As SqlDataAdapter

Dim daOrders As SqlDataAdapter

Dim ds As New DataSet()

’create a connection to the Pubs database

conn = New SqlConnection("data source=localhost;" & _

"integrated security=true;initial catalog=northwind")

’create the first DataTable

daCustomers = New SqlDataAdapter _

("select CustomerID, CompanyName from Customers", conn)

daCustomers.Fill(ds, "Customers")

’создаем вторую DataTable

daOrders = New SqlDataAdapter _

("select CustomerID, OrderID, OrderDate, ShippedDate from Orders", conn)

daOrders.Fill(ds, "Orders")

Для каждого объекта DataTable обязательно нужно создавать свой объект DataAdapter - общий использовать не получится.

В DataSet предусмотрен и такой объект, как DataRelation - отношения между таблицами. При создании DataRelation необходимо определить parent table (то есть таблицу с primary key) и child (то есть с foreign). Конечно же, у этих таблиц должен быть общий столбец и его нужно указать. Пример создания DataRelation для наших Customers и Orders:

’Create DataRelation: each publisher publishes many titles

Dim dr As DataRelation

Dim parentCol As DataColumn

Dim childCol As DataColumn

parentCol = ds.Tables("Customers").Columns("CustomerID")

childCol = ds.Tables("Orders").Columns("CustomerID")

dr = New DataRelation("CustOrders", parentCol, childCol)

ds.Relations.Add(dr)

Конечно, главное, ради чего создаются объекты DataRelation - навигация через отношения между таблицами. Для этой цели используются методы GetChildRows и GetParentRow. Например, найти все заказы для заказчика в нашем примере можно так:

currentParentRow = ds.Tables("Customers"). _

Rows(dgCustomers.SelectedIndex)

For Each r In currentParentRow.GetChildRows("CustOrders")

Label1.Text &= r("OrderID") & ", "

Next

Иногда очень удобно сделать так, чтобы в одном элементе управления показывались только записи, относящиеся к выбранной в другом элементе управления записи. Например, пользователь выбирает в одном DataGrid запись для заказчика, а в другом элементе управления отображаются сделанные им заказы:

 Dim parentTableView As New _

DataView(ds.Tables("Customers"))

Dim currentRowView As DataRowView = _

parentTableView(dgCustomers.SelectedIndex)

dgChild.DataSource = _

currentRowView.CreateChildView("CustOrders")

dgChild.DataBind()

Делается этот через DataView, который фильтруется при помощи записи, выбранной в другом элементе управления.

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

Как уже говорилось, объект DataReader используется для получения данных с источника в режиме read-only/forward only.

Он особенно удобен, когда:

- нужно выполнить максимально быстро элементарный запрос к источнику;

- когда, наоборот, данных с источника нужно получить много (тысячи и десятки тысяч записей) и размещать их все в памяти не хочется. При использовании DataReader в памяти одновременно хранится не более одной записи с источника.

DataReader можно привязать только к одному элементу управления на форме.

DataReader всегда работает быстрее и занимает меньше памяти, чем DataSet.

В ADO.NET предусмотрено два вида DataReader: SqlDataReader и OleDbDataReader. Заполнение данными обоих объектов производится при помощи одного и того же метода ExecuteReader.

Принципиальное отличие DataReader заключается в том, что в отличие от работы с DataAdapter, где соединение открывается и закрывается автоматически, при работе с DataReader открывать и закрывать соединения вам придется вручную.

Обычно либо DataReader используется в цикле, либо он привязывается к элементу управления. Однако код в любом случае придется писать вручную.

Как выглядит последовательность работы с DataReader:

1) открывается соединение с источником

2) создается объект Command

3) из объекта Command создается объект DataReader

4) вызывается метод ExecuteReader

5) объект DataReader используется в программе

6) объект DataReader закрывается

7) разрывается соединение с источником

’Create connection and command objects

Dim conn As New SqlConnection _

("data source=localhost;integrated security=true;" & _

"initial catalog=pubs")

Dim cmdAuthors As New SqlCommand _

("select * from Authors", conn)

conn.Open()

’create DataReader and display data

Dim dr As SqlDataReader

dr = cmdAuthors.ExecuteReader()

Do While dr.Read()

lstBuiltNames.Items.Add(dr("au_lname") + ", " + _

dr("au_fname"))

Loop

’close DataReader and Connection

dr.Close()

conn.Close()

При работе с DataReader всегда рекомендуется использовать конструкцию Try...Catch...Finally, чтобы гарантировать, что даже в случае сбоя соединение будет разорвано (иначе может получиться так, что соединение будет висеть до бесконечности).

Try

conn.Open()

dr = cmdAuthors.ExecuteReader()

’use the returned data in the DataReaders

Catch e As Exception

’handle the error

Finally

dr.Close()

conn.Close()

End Try

После того, как метод ExecuteReader вызван, DataReader автоматически устанавливается на позицию перед первой возвращаемой записью.

Чтобы реально обратиться к данным через DataReader, можно

воспользоваться методом Read в цикле. Этот метод после окончания записей в DataReader вернет null - на это значение и назначаем проверку в цикле:

Do While dr.Read()

lblName.Text &= dr("au_fname")

Loop

сам забор данных производится просто обращением к полю по его имени или ординалу, а также можно использовать соответствующий метод Get (GetString, GetDateTime, GetInt32 и т.п.)

dr.Read()

lblName.Text = dr.GetString(1) + ", " + _

dr.GetString(2)

Метод Get с указанием типа данных работает быстрее, поскольку ADO.NET нет необходимости разбираться с типами данных.

Закрывать соединение вручную после окончания работы с DataReader - обязательно, поскольку иначе оно так и останется висеть.

myReader.Close()

conn.Close()

Для удобства отображения полученной через DataReader информации рекомендуется привязать его к элементу управления на форме (например, уже знакомому нам DataGrid). Для этого используется то же свойство DataSource элемента управления, в качестве значения которого передается объект DataReader:

Dim conn As New SqlConnection _

("data source=localhost;integrated security=true;" & _

"initial catalog=pubs")

conn.Open()

Dim cmdAuthors As New SQLCommand _

("select * from Authors", conn)

’bind the datareader to a listbox

Dim dr As SqlDataReader

dr = cmdAuthors.ExecuteReader()

lstBoundNames.DataSource = dr

lstBoundNames.DataTextField = "au_lname"

lstBoundNames.DataBind()

’close the datareader and the connection

dr.Close()

conn.Close()

Получить учебные материалы по этому курсу