Компоненты данных ADO.NET
Фильтрация извлеченных автономных данных с помощью DataView
Объект DataView можно использовать для фильтрации извлеченных автономных данных перед их отображением пользователю. Для этого нужно задать объекту значение свойства RowFilter, которое использует те же операторы логических условий, что и в SQL-запросе. В таблице приведены наиболее часто используемые операторы фильтрации
Приведем пример страницы, в которой имеются три элемента GridView. Каждый из них привязан через DataView к одним и тем же данным DataTable, но с разными установками фильтрации.
-
Создайте копию страницы GridViewDataView.aspx с именем DataViewFiltered.aspx и назначьте ее стартовой -
В странице DataViewFiltered.aspx выполните следующие изменения
- В директиве @Page установите новое значение атрибута Inherits="DataViewFiltered"
- Поменяйте HTML-дескрипторы <h2> на <h3> со следующим содержимым
-
<h3> Фильтровать продукт Chocolade<br /> (RowFilter = "ProductName = 'Chocolade'") </h3>
-
<h3> Фильтровать продукты, которых нет в заказах и на складе<br /> (RowFilter = "UnitsInStock = 0 AND UnitsOnOrder = 0") </h3>
-
<h3> Фильтровать продукты, чье название начинается с буквы P<br /> (RowFilter = "ProductName LIKE 'P%'") </h3>
-
-
В файле поддержки DataViewFiltered.aspx.cs установите новое имя класса DataViewFiltered и наполните файл следующим кодом
using System; using System.Data; using System.Web.Configuration; using System.Data.SqlClient; public partial class DataViewFiltered : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { // Содать Connection, DataAdapter и DataSet string connectionString = WebConfigurationManager. ConnectionStrings["Northwind"].ConnectionString; SqlConnection con = new SqlConnection(connectionString); string sql = "SELECT ProductID, ProductName, UnitsInStock, UnitsOnOrder, " + "Discontinued FROM Products"; SqlDataAdapter adapter = new SqlDataAdapter(sql, con); DataSet dataset = new DataSet(); adapter.Fill(dataset, "ProductsTable"); // Фильтровать продукт Chocolade DataView view1 = new DataView(dataset.Tables["ProductsTable"]); view1.RowFilter = "ProductName = 'Chocolade'"; GridView1.DataSource = view1; // Фильтровать продукты, которых нет в заказах и на складе DataView view2 = new DataView(dataset.Tables["ProductsTable"]); view2.RowFilter = "UnitsInStock = 0 AND UnitsOnOrder = 0"; GridView2.DataSource = view2; // Фильтровать продукты, чье название начинается с буквы P DataView view3 = new DataView(dataset.Tables["ProductsTable"]); view3.RowFilter = "ProductName LIKE 'P%'"; GridView3.DataSource = view3; // Загрузить привязанные данные во все элементы отображения GridView this.DataBind(); } } -
Запустите страницу DataViewFiltered.aspx и получите следующий результат
Фильтровать продукт Chocolade (RowFilter = "ProductName = 'Chocolade'")
Фильтровать продукты, которых нет в заказах и на складе (RowFilter = "UnitsInStock = 0 AND UnitsOnOrder = 0")
| ProductID | ProductName | UnitsInStock | UnitsOnOrder | Discontinued |
|---|---|---|---|---|
| 5 | Chef Anton's Gumbo Mix | 0 | 0 | |
| 17 | Alice Mutton | 0 | 0 | |
| 29 | Th№ringer Rostbratwurst | 0 | 0 | |
| 53 | Perth Pasties | 0 | 0 |
Фильтровать продукты, чье название начинается с буквы P (RowFilter = "ProductName LIKE 'P%'")
Класс System.Data.DataView также имеет свойство RowStateFilter, которое можно использовать для фильтрации данных так, чтобы отображались только строки, имеющие определенное состояние (вставленные, помеченные на удаление, модифицированные или неизмененные). По умолчанию это свойство установлено на отображение всех строк, кроме помеченных на удаление.
Фильтрация извлеченных автономных данных в DataView с установкой отношений
Класс DataView может выполнять и более сложную фильтрацию на основе таблиц, связанных отношением "Главный-подчиненный". Например, можно отобразить категории, каждая из которых содержит более 20 наименований продуктов, или отобразить заказчиков, сделавших определенное количество покупок.
Продемонстрируем эту возможность на примере двух таблиц: Categories и Products учебной базы Northwind.
-
Сделайте копию страницы DataSetRelationShips.aspx и назовите ее DataViewRelation.aspx
-
Исправьте значения атрибута Inherits в директиве @Page страницы и имя класса в файле поддержки
-
Удалите со страницы DataViewRelation.aspx текстовую метку lblInfo и поместите вместо нее заголовочный дескриптор
<h2>Категории с продуктами дороже $50</h2>
-
Поместите после заголовочного дескриптора элемент управления GridView из вкладки Data панели Toolbox
-
Скорректируйте файл поддержки страницы DataViewRelation.aspx.cs следующим образом
using System; using System.Data; using System.Web.Configuration; using System.Data.SqlClient; using System.Text; public partial class DataSetRelationShips : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { // Извлекаем строку соединения с именем Northwind из файла web.config string connectionString = WebConfigurationManager. ConnectionStrings["Northwind"].ConnectionString; // Создаем объект соединения SqlConnection con = new SqlConnection(connectionString); // Формируем строки SQL-запросов string sqlCategories = "SELECT CategoryID, CategoryName FROM Categories"; string sqlProducts = "SELECT ProductName, CategoryID, UnitPrice FROM Products"; // Создаем объект DataAdapter SqlDataAdapter adapter = new SqlDataAdapter(sqlCategories, con); // Создаем пустой объект DataSet набора данных DataSet dataset = new DataSet(); // Выполняем два запроса к БД с открытием // и закрытием соединения вручную. // Возможные исключения не обрабатываем, а просто подавляем try { con.Open(); // Наполнить DataSet данными из таблицы Categories // с именованной меткой CatTable adapter.Fill(dataset, "CatTable"); // Сменить команду и добавить в DataSet данные // с именованной меткой ProdTable из таблицы Products adapter.SelectCommand.CommandText = sqlProducts; adapter.Fill(dataset, "ProdTable"); } finally { con.Close(); } // Определение отношения между извлеченными в DataSet // именованными данными CatTable и ProdTable DataRelation relation = new DataRelation( "CatProd", // Имя отношения dataset.Tables["CatTable"].Columns["CategoryID"], // Родительская таблица dataset.Tables["ProdTable"].Columns["CategoryID"] // Дочерняя таблица ); // Добавление отношения в коллекцию отношений DataSet dataset.Relations.Add(relation); // Создаем объект DataView, в который загружаем данные CatTable DataView view1 = new DataView(dataset.Tables["CatTable"]); // Устанавливаем фильтр для отображения только категорий продуктов, // цена которых в связанной таблице продуктов удовлетворяет условию // "Самый дорогой продукт дороже 50" view1.RowFilter = "MAX(Child(CatProd).UnitPrice) > 50"; // Показываем отфильтрованные данные пользователю GridView1.DataSource = view1; GridView1.DataBind(); } } -
Назначьте страницу DataViewRelation.aspx стартовой и выполните ее
Должен получиться следующий результат
Категории с продуктами дороже $50
В фильтре мы установили условие, чтобы показать пользователю только те записи в родительской таблице, для которых в дочерней связанной таблице имеется хотя бы одна запись со значением поля UnitPrice>50. Это означает, что выводятся категории продуктов, в которых хотя бы один продукт дороже $50.
Добавление к автономным данным вычисляемых столбцов
Таким образом, класс DataSet позволяет одноразово загрузить нужные данные из источника, а затем в отсоединенном автономном режиме что-то с ними делать. До сих пор мы показывали пользователю все автономные данные, полученные по SQL-запросу. Показывали данные с наложением некоторых условий в методе
DataSet.Tables["TableName"].Select("Дополнительное_условие")Показывали, также, данные с условием, наложенным в свойстве DataView.RowFilter вспомогательного объекта DataView.
Теперь рассмотрим вопрос, как в дополнение к полям, извлеченным в набор данных DataSet из физического источника данных, можно добавить вычисляемые столбцы. Вычисляемые столбцы являются искуственно полученными в наборе данных и игнорируются при извлечении и обновлении данных. Вместо этого они представляют собой значения, которые вычисляются на основе физически существующих данных.
Чтобы создать вычисляемый столбец в DataSet, необходимо отдельно создать новый объект класса DataColumn, установить формулу в его свойстве Expression и добавить этот столбец в коллекцию Columns объекта DataSet
dataset.Tables["TableName"].Columns.Add(datacolumn)
Рассмотрим пример, в котором создадим столбец, объединяющий фамилию и имя каждого служащего из таблицы Employees учебной базы данных Northwind.
-
Создайте копию файла TestDataSet.aspx с именем ExpressionColumns.aspx и назначьте ее стартовой
-
Откройте на редактирование файл поддержки ExpressionColumns.aspx.cs и отредактируйте его так
using System; using System.Data; using System.Web.Configuration; using System.Data.SqlClient; using System.Text; public partial class ExpressionColumns : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { // Извлекаем строку соединения с именем Northwind из файла web.config string connectionString = WebConfigurationManager. ConnectionStrings["Northwind"].ConnectionString; // Формируем строку SQL для выборки всех данных таблицы Employees string commandString = "SELECT * FROM Employees"; // Создаем и настраиваем экземпляр класса SqlDataAdapter SqlDataAdapter adapter = new SqlDataAdapter(commandString, connectionString); // Создаем объект DataSet результирующего набора данных DataSet dataset = new DataSet(); // Безопасно заполняем данными объект DataTable с произвольным // именем, например "EmployeesResult", созданного объекта DataSet try { adapter.Fill(dataset, "EmployeesResult"); } catch { throw new ApplicationException("Ошибка данныx."); } // Создаем именованный столбец и добавляем к таблице в DataSet string strExpr = "'Сотрудник ' + TitleOfCourtesy + ' ' + LastName + ', ' + FirstName"; DataColumn column = new DataColumn("FullName", typeof(string), strExpr); dataset.Tables["EmployeesResult"].Columns.Add(column); // Перебираем все объекты DataRow с полученным записами StringBuilder htmlStr = new StringBuilder(""); foreach (DataRow dr in dataset.Tables["EmployeesResult"].Rows) { htmlStr.Append("<li>"); // Существующий столбец htmlStr.Append(dr["EmployeeID"].ToString() + ") "); htmlStr.Append("<b>"); // Новый столбец htmlStr.Append(dr["FullName"].ToString()); htmlStr.Append("</b>"); htmlStr.Append("</li>"); } // Отображаем полученные данные lblInfo.Text = "<h2>Список сотрудников</h2>"; lblInfo.Text += htmlStr.ToString(); } } -
Выполните страницу ExpressionColumns.aspx и получите следующий результат
Список сотрудников
Добавление к автономным данным вычисляемых столбцов для связанных таблиц
Можно создать в DataSet вычисляемые столбцы для связанных строк. Например, можно добавить в виртуальную таблицу CatTable столбец, показывающий количество связанных строк виртуальной таблицы ProdTable. В этом случае нужно определить отношение объектом DataRelation и использовать агрегатную функцию SQL, такую, как AVG(), MAX(), MIN(), COUNT().
Приведем пример, в котором создается три вычисляемых столбца со своей агрегатной функцией и используется отношение таблиц.
-
Создайте копию страницы DataViewRelation.aspx с именем ExpressionColumnsRelation.aspx и назначьте ее стартовой
-
Исправьте значения атрибута Inherits в директиве @Page страницы и имя класса в файле поддержки
-
Измените на странице заголовочный дескриптор на
<h2>Добавление вычисляемых столбцов</h2>
-
Откройте кодовый файл ExpressionColumnsRelation.aspx.cs на редактирование и откорректируйте его так
using System; using System.Data; using System.Web.Configuration; using System.Data.SqlClient; using System.Text; public partial class ExpressionColumnsRelation : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { // Извлекаем строку соединения с именем Northwind из файла web.config string connectionString = WebConfigurationManager. ConnectionStrings["Northwind"].ConnectionString; // Создаем объект соединения SqlConnection con = new SqlConnection(connectionString); // Формируем строки SQL-запросов string sqlCategories = "SELECT CategoryID, CategoryName FROM Categories"; string sqlProducts = "SELECT ProductName, CategoryID, UnitPrice FROM Products"; // Создаем объект DataAdapter SqlDataAdapter adapter = new SqlDataAdapter(sqlCategories, con); // Создаем пустой объект DataSet набора данных DataSet dataset = new DataSet(); // Выполняем два запроса к БД с открытием // и закрытием соединения вручную. // Возможные исключения не обрабатываем, а просто подавляем try { con.Open(); // Наполнить DataSet данными из таблицы Categories // с именованной меткой CatTable adapter.Fill(dataset, "CatTable"); // Сменить команду и добавить в DataSet данные // с именованной меткой ProdTable из таблицы Products adapter.SelectCommand.CommandText = sqlProducts; adapter.Fill(dataset, "ProdTable"); } finally { con.Close(); } // Определение отношения между извлеченными в DataSet // именованными данными CatTable и ProdTable DataRelation relation = new DataRelation( "CatProd", // Имя отношения dataset.Tables["CatTable"].Columns["CategoryID"], // Родительская таблица dataset.Tables["ProdTable"].Columns["CategoryID"] // Дочерняя таблица ); // Добавление отношения в коллекцию отношений DataSet dataset.Relations.Add(relation); // Создать вычисляемые столбцы и добавить их в DataSet DataColumn count = new DataColumn("Кол. продуктов", typeof(int), "COUNT(Child(CatProd).CategoryID)"); dataset.Tables["CatTable"].Columns.Add(count); DataColumn max = new DataColumn("Самый дорогой продукт", typeof(decimal), "MAX(Child(CatProd).UnitPrice)"); dataset.Tables["CatTable"].Columns.Add(max); DataColumn min = new DataColumn("Самый дешевый продукт", typeof(decimal), "MIN(Child(CatProd).UnitPrice)"); dataset.Tables["CatTable"].Columns.Add(min); // Меняем названия заголовков столбцов на русский язык dataset.Tables["CatTable"].Columns["CategoryID"].ColumnName = "№ п/п"; dataset.Tables["CatTable"].Columns["CategoryName"].ColumnName = "Наименование категории"; // Показываем данные пользователю GridView1.DataSource = dataset.Tables["CatTable"]; GridView1.DataBind(); } } -
Исполните страницу ExpressionColumnsRelation.aspx и получите следующий результат
Добавление вычисляемых столбцов
