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

Технология ADO.NET, в отличие от своих предшественников ADO и OLE DB, была разработана специально для использования в web приложениях, где не бывает постоянных соединений с БД. Традиционная работа с данными в ADO.NET строится по такой схеме: создается соединение Connection, затем оно открывается методом Open, создается объект команда Command, инкапсулирующая SQL команду, она исполняется, а соединение затем закрывается. Такой подход обеспечивает поточный доступ к результатам запросов. Т.е. читая данные с помощью DataReader, вы не можете перепрыгнуть через несколько записей или вернуться к предыдущей. Поточный доступ имеет максимальную производительность. 

   ADO.NET была разработана для доступа к данным без реального соединения с БД. При этом все данные размещаются в оперативной памяти. Работа с отсоединенными данными в ADO.NET осуществляется с помощью классов из пространства имен System.Data.

   Самый выжный класс при работе с отсоединенными данными - это DataSet. После того как получены результаты запроса с помощью объекта DataAdapter и сохранены в DataSet' e, соединение между БД и объектом DataSet перестает существовать. Изменения в DataSet не сказываются на БД и наоборот. Класс DataSet включает в себя набор таблиц DataTable и связей между таблицами DataRelation. Класс DataTable включает набор строк DataRow, набор столбцов таблицы DataColumn, и наборы отношений ChildRelations и ParentRelations между столбцами разных таблиц базы данных. Класс DataRow инкапсулирует информацию о строке в таблице и состоянии строки Deleted, Modified, New и Unchanged. Класс Constraint используется для сохранения целостности данных в таблицах.

   Преимущества работы с отсоединенными данными:
      1. не требуется постоянное соединение с БД, что нужно, например, для web приложений;
      2. облегчается создание многоуровневых приложений. Если приложение обращается к БД с помощью объектов уровня DAL, то бизнес объектам на уровне BLL можно передавать DataSet. Обновления в БД также могут передаваться с помощью DataSet;
      3. облегчается сортировка, поиск, фильтрация и навигация по данным;
      4. облегчается работа с реляционными данными;
      5. есть возможность кешировать изменения. Объект DataSet позволяет кешировать изменения и затем с помощью DataAdapter передавать все изменения в БД за 1 раз;
      6. тесная интеграция с XML. Содержимое DataSet можно загружать и сохранять в виде XML документов.

Использование объектов DataSet

   Ниже приводится пример использования объекта DataSet. Все примеры в статье используют БД Northwind БД MS SQL Server.

stringconn ="Provider=SQLSQL;Data Source=(local)\\NetSDK;InitialCatalog=Northwind;Trusted_Connection=Yes;";
string query = "SELECT CustomerID, CompanyName, ContactName, Phone FROM Customers";
SqlDataAdapter da = new SqlDataAdapter(query, conn);
DataSet ds = new DataSet();
da.Fill(ds, "Customers");

   Вначале создаем строку соединения с БД и SQL запрос для получения данных. DataAdapter помещает результаты запроса в соответствующую таблицу DataTable, находящуюся в DataSet. С помощью перегруженного метода Fill можно помещать данные и напрямую в DataTable. Для одной таблицы можно несколько раз вызывать метод Fill, однако, если вызывать метод Fill для DataSet без явного указания имени таблицы, то эти данные будут помещаться в разные таблицы (например, "Table", "Table1", "Table2",). Для доступа к данным, находящимся в таблицу есть свойство Rows, возвращающее набор объектов DataRow

DataTable table = ds.Tables[0];
DataRow row = table.Rows[0];
Console.WriteLine("OrderID: " + row["OrderID"]);
Console.WriteLine("CustomerID: " + row["CustomerID"]);  

   У объекта DataRow есть свойство Item, возвращающее содержимое конкретного поля. Можно указать имя поля как в примере кода, или целое число, соответствующее порядковому номеру столбца. При поиске по индексу данные немного возвращаются быстрее, чем при поиске по названию столбца.  

Изменение содержимого DataTable

   Для добавления новой строки в таблицу существует метод NewRow(), который создает новый объект DataRow, но не добавляет запись в таблицу. Полем новой записи задаются значения по умолчанию или Null, если значение по умолчанию не задано.

DataRow row = ds.Tables["Customers"].NewRow();
row["CustomerID"] = "VASYA";
row["Company"] = "Компания";
row["ContactName"] = "Вася Пупкин";
row["Phone"] = "11-22-33";
ds.Tables["Customers"].Rows.Add(row);

   Есть еще один способ добавления записей в таблицу - метод LoadDataRow(). Первый параметр этого метода - это массив значений, элементы которого соответствуют столбцам таблицы. Второй - позволяет управлять значением свойство RowState новой записи. Если передать false, то значение этого свойства будет Added, как и при добавлении новой записи методом Add.

object[]aValues = {"VASYA", "Компания",  "Вася Пупкин", "11-22-33"};
ds.Tables["Customers"].LoadDataRow(aValues, false);

   Редактирование существующей записи можно следующим образом:

DataRow row = ds.Tables["Customers"][0];
row["ContactName"] = "Вася Пупкин";

или с помощью метода ItemArray:

object[] items = {"VASYA", "Компания", "Вася Пупкин", "11-22-33"};
DataRow row = ds.Tables["Customers"][0];
Row.ItemArray = items;

   Для удаления записи нужно вызывать метод DataRow.Delete(). При этом удаленная запись фактически не удаляется из DataTable, а помечается как удаленная - ее свойство RowState принимает значение Deleted. Если же до вызова метода Delete значение RowState было Added, тотолько в этом случае строка удаляется из таблицы.

Использование столбцов с автоинкрементном

   Для поддержки столбцов с автоматическим увеличением значения у DataColumn есть свойства AutoIncrement, AutoIncrementSeed и AutoIncrementStep . Для того, чтобы создать столбец DataColumn с автоинкрементом нужно установить свойство AutoIncrement в true. Значение счетчика будет начинаться с AutoIncrementSeed (если это не нарушает корректность данных в таблице) и увеличиваться на значение AutoIncrementStep. Для столбцов с автоинкрементном рекомендуется устанавливать свойство ReadOnly в true. Ниже приведен пример вставки в таблицу столбца с автоинкрементом

DataColumn column = table.Columns.Add("CustomerID", typeof(Int32));
column.AutoIncrement = true;
column.AutoIncrementSeed = 1;
column.AutoIncrementStep = 1;

   Если вы планируете сносить изменения в БД методом DataAdapter.Update(), то рекомендуется задать свойствам AutoIncrementSeed и AutoIncrementStep значения -1. Это гарантирует, что ADO.NET будет генерировать значения метки, которых нет в БД. При вызове метода Update в БД внесутся уже уникальные значения.

Работа с DataSet в среде Visual Studio .NET

   Создавать объекты DataSet можно как программно, так и с помощью среды Visual Studio. Для второго случая предназначена вкладка Data в панели инструментов. Для начала нужно перетащить на web или win-форму объект SqlDataAdapter.

   Затем в мастере создать новое подключение к БД, указав БД Northwind и способ доступа к БД как . После этого выберете таблицу Customers и отметьте все столбцы таблицы.

   Повторите процесс для таблицы Order, используя уже готовое соединение. Затем щелкните правой кнопкой мыши по панели с созданными DataAdapter 'ами, выберете из контекстного меню команду Generate DataSet.

   Отметьте галочками оба DataAdapter и нажмите на ОК.

   Добавленный объект DataSet отобразился в панели компонентов. Мы добавили т.н. DataSet со строгим контролем типов (типизированный DataSet ), о котором будем говорить позже. Структуру данных можно посмотреть в созданном файле с расширением xsd.

   Другой путь создания типизированных датасетов - добавление в проект DataSet (через команду Add New Item) и "перетаскивание" на него таблиц БД из окна Server Explorer.

Работа с реляционными данными

   Работа с реляционными данными осуществляется с помощью объектов класса DataRelation, связующие колонки DataColumn двух таблиц DataTable. Создать такой объект можно следующим образом:

ds.Relationships.Add(new DataRelation("CustomersOrders", ds.Tables["Customers"].Columns["CustomerID"], ds.Tables["Orders"].Columns["CustomerID"]);

   Если нужно определить отношение, основанное на нескольких столбцах, нужно использовать конструктор, принимающий массив объектов DataColumn:

DataTable tblParent = ds.Tables["ParentTable"];
DataColumn[] colsParent = new DataColumn[] {tblParent.Columns["ParentColumn1"], tblParent.Columns["ParentColumn2"]};
DataTable tblChild = ds.Tables["ChildTable"];
DataColumn[] colsChild = new DataColumn[] {tblChild.Columns["ChildColumn1"], tblChild.Columns["ChildColumn2"]};
ds.Relationships.Add(new DataRelation("ParentChild", colsParent, colsChild));

Работа со связанными данными

   Основным применением объектов DataRelation является поиск связанных данных. Однако сам объект DataRelation не предоставляет такой функциональности, она реализуется методами класса DataRow: GetChildRow(), GetParentRow() и GetParentRows().
   Для поиска дочерних записей используется метод GetChildRow() соответсвующего объекта DataRow. Ему передается ему имя объекта DataRelation, определяющего отношение. Этот метод возвращает дочерние записи в виде массива объектов DataRow :

DataRow row = ds.Tables["Customers"].Rows[0];
foreach (DataRow rowOrder in row.GetChildRows("CustomersOrders"))
   Console.WriteLine(rowOrder["OrderID"] + rowOrder["OrderDate"] + "\n");

   Найти родительскую запись с помощью DataRelation можно методом DataRow.GetParentRow ().

DataRow rowOrder = ds.Tables["Orders"].Rows[0];
Console.WriteLine("\t" + rowOrder.GetParentRow("CustomersOrders")["ContactName"] + "\n");

   В случае отношения <один ко многим> найти все родительские записи конкретного объекта DataRow можно с методом GetParentRows(), также принимающего название отношения и возвращающего массив родительских объектов.

Добавление объекта DataRelation в Visual Studio .NET

   Для добавления отношения DataRelation в DataSet со строгим контролем типов нужно перейти к структуре данных, щелкнув по файлу с расширением xsd, определяющего структуру DataSet. Щелкнув на родительский DataTable правой кнопкой нужно в контекстном меню выбрать Add / New Relation.

   В диалоговом окне Edit Relation задаются родительские и дочерние объекты.

   При щелчке на ОК, в XML Sheme Designer появляется графическое представление DataRelation - линия, соединяющая 2 объекта.

Поиск записей

   При запросах к БД часто требуется найти запись по ее первичному ключу. Для поиска записей в DataTable существует метод DataRowCollection.Find ().

table.PrimaryKey = new DataColumn[] {tbl.Columns["CustomerID"]};
DataRow row = tbl.Rows.Find("VASYA");
if (row == null)
   Console.WriteLine("Запись не найдена");
else
   Console.WriteLine(row["CompanyName"]);

   Если первичный ключ состоит из нескольких колонок, то в метод Find() можно передать массив объектов.
   Для выполнения поиска по произвольному SQL запросу у DataTable есть метод Find(). Например, для получения имен всех покупателей, находящихся в Уфе, можно воспользоваться таким кодом

DataRow[] rows = table.Select("City = 'Уфа'");
foreach (DataRow row in rows)
   Console.WriteLine(row["CompanyName"] + "\n");

   Если нужно, чтобы возвращаемые данные были отсортированы, можно воспользоваться перегруженной версией этого метода. Она имитирует раздел ORDER BY SQL -запроса.

DataRow[] rows = table.Select("City = 'Уфа'", "CompanyName ASC");

Работа с объектами DataSet со строгим контролем типов

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

Добавление записи

   Все классы, соответствующие таблицам DataTable в DataSet, позволяют добавлять новые записи двумя способами. Метод New[Имя_таблицы]Row() возвращает новую строку в таблице:

DataSetTest ds = new DataSetTest();
DataSetTest.CustomersDataTable tblCustomers = ds.Customers;
DataSetTest.CustomersRow rowCustomer = tblCustomers.NewCustomersRow();
rowCustomer.CustomerID = "VASYA";
rowCustomer.CompanyName = "Компания";
rowCustomer.ContactName = "Вася Пупкин";
rowCustomer.Phone = "11-22-33";
tblCustomers.AddCustomersRow(rowCustomer);

   Второй способ - это использовать метод Add[Имя_таблицы]Row(), принимающий массив объектов, соответсвующих полям записи:

DataSetTest ds = new DataSetTest();
DataSetTest.CustomersDataTable.AddCustomersRow("VASYA", "Компания", "Вася Пупкин", "11-22-33");

Поиск записи

   Поиск записи в таблице тоже претерпел изменения. Например, для поиска в таблице Order Details по составному ключу появился метод следующий метод

DataSetTest.Order_DetailRow = tblDetails.FindByOrderIDProductID(112233, 456);

   Также типизированные DataSet поддерживают русские буквы в названиях таблиц и полей таблиц.

ds.Статистика.AddСтатистикаRow(15.9, "компания", true );

Передача обновлений в БД

   Для передачи изменений в БД используется объекты DataAdapter. Для создания логики обновления БД можно использовать один из трех вариантов:
1. вручную сконфигурировать DataAdapter в период разработки;
2. воспользоваться объектом CommandBulder в период выполнения;
3. использовать в период разработки DataAdapter Configuration Wizard.

Ручное конфигурирование DataAdapter

   DataAdapter имеет 3 свойства для передачи изменений в БД: InsertCommand, UpdateCommand и DeleteCommand. Значения этих свойства должны быть заданы до вызова метода DataAdapter.Update() с учетом того, какие изменения были внесены в DataSet. Когда метод Update() добавляет, обновляет или удаляет строки в таблице, он вызывает соответствующую команду.
   Если в ходе ваших действий вы только добавляли строки в таблицу, то и задать вы должны только значение InsertCommand. Ниже приведен код для такой ситуации:

string conn  = "Provider = SQLSQL; Data Source = (local)\\NetSDK; Initial Catalog= Northwind; Trusted_Connection=Yes;";
string query = "SELECT CustomerID, CompanyName, ContactName, Phone FROM Customers"; SqlDataAdapter da = new SqlDataAdapter(query, conn); DataSet table = new DataSet(); da.Fill(table, "Customers");

// добавляем новые строки в таблицу
...

// создаем команду для вставки новых записей
query  = "INSERT INTO Customers (CustomerID, CompanyName, ContactName, Phone) VALUES (?, ?, ?, ?)";
SqlCommand cmd = new SqlCommand(query, conn);
SqlParameterCollection pc = cmd.Parameters; pc.Add("CustomerID", SqlType.Integer, 0, "CustomerID");
pc.Add("CompanyName", SqlType.String, 0, "CompanyName");
pc.Add("ContactName", SqlType.String, 0, "ContactName");
pc.Add("Phone", SqlType.String, 0, "Phone");
da.InsertCommand = cmd;
da.Update(table);

   Подобный подход может применяться и при передаче обновлений в БД при помощи хранимых процедур. Допустим, что имя хранимой процедуры для добавления нового покупателя это spInsertCustomer. Тогда наш код измениться следующим образом:

OleDbCommand cmd = new OleDbCommand("spInsertCustomer", conn);
cmd.CommandType = CommandType.StoredProcedure;
OleDbParameterCollection pc = cmd.Parameters;
pc.Add("CustomerID", SqlType.Integer, 0, "CustomerID");
pc.Add("CompanyName", SqlType.String, 0, "CompanyName");
pc.Add("ContactName", SqlType.String, 0, "ContactName");
pc.Add("Phone", SqlType.String, 0, "Phone");

Использование CommandBuilder

   CommandBuilder генерирует SQL запросы обращаясь к БД для получения метаданных о таблицах. Для использования CommandBuilder необходимо выполнение следующих условий: 1. запрос возвращает данные только из одной таблицы;
2. в таблице определен первичный ключ;
3. первичный ключ есть в результатах запроса.
   Ниже приведен пример использования CommandBuilder.

string con ="Provider=SQLOLEDB;Data Source=(local)\\NetSDK;InitialCatalog=Northwind;Trusted_Connection=Yes;";
string query = "SELECT OrderID, ProductID, Quantity, UnitPrice FROM [Order Details]";
OleDbDataAdapter da = new OleDbDataAdapter(strSQL, strConn);
OleDbCommandBuilder cb = new OleDbCommandBuilder(da);
da.InsertCommand = cb.GetInsertCommand();
da.Update(tbl);

Использование мастера Data Adapter Configuration Wizard

   Одно из предназначений мастера - создание логики обновления базы данных. На четвертом шаге мастера по команде Advanced Options выводится диалог, в котором можно указать создавать или нет команды для вставки, обновления и удаления записей в БД.

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