Использование баз данных в приложениях ASP.NET

Использование DataView для фильтрации и сортировки данных

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

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

Рассмотрим примеры использования основных функций DataView.

Сортировка данных

Сценарий использования объекта DataView выглядит следующим образом:

1. Создать объект DataView.
2. Настроить его параметры сортировки или фильтрации.
3. Привязать его к элементу отображения данных.
4. Инициировать процесс привязки данных.

Рассмотрим пример реализации сортировки данных о товарах, извлеченных из таблицы "Товары" БД. Полный текст кода метода Page_Load, осуществляющего подключение к источнику данных, сортировку и отображение содержимого таблицы "Товары" на странице Web-приложения, приведен ниже.

protected void Page_Load(object sender, EventArgs e)
{
  string ConnectionString = WebConfigurationManager.
   ConnectionStrings["TEST_DB"].ConnectionString;
  SqlConnection con = new SqlConnection(ConnectionString);
  string query = "SELECT * FROM Товары";
  SqlDataAdapter da = new SqlDataAdapter(query, con);
  DataSet ds = new DataSet();
  da.Fill(ds, "Products");

  DataView dv = new DataView(ds.Tables["Products"]);
  dv.Sort = "НаименованиеТовара";
  GridView1.DataSource = dv;
  Page.DataBind();
}

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

Фильтрация данных

Для выполнения фильтрации данных при помощи DataView нужно воспользоваться свойством RowFilter. Содержимое RowFilter очень напоминает команду Where запроса SQL. Здесь записывается условие фильтрации набора данных. При этом возможно применение тех же операций сравнения и логических операций, которые используются в языке SQL. Кроме того, здесь возможно выполнение простейших вычислительных операций, таких как сложение, вычитание, умножение, деление, вычисление остатка от деления.

Рассмотрим пример фильтрации данных таблицы "Товары".

Для отображения информации о товаре "Товар1" необходимо выполнить следующую команду:

dv.RowFilter = "НаименованиеТовара='Товар1'";

Следующий пример отобразит товары, цена которых лежит в диапазоне от 10 до 100:

dv.RowFilter = "Цена>10 AND Цена<100";

DataView позволяет также отображать строки DataTable, находящиеся в одном из следующих состояний: вставленная, удаленная, модифицированная, неизмененная. Для этого необходимо задать соответствующее значение для свойства RowStateFilter. Следующий пример позволит отобразить только вновь добавленные строки:

dv.RowStateFilter = DataViewRowState.Added;

DataView позволяет реализовывать и более сложную логику фильтрации данных. Часто пользователю требуется отобразить данные из одной таблицы, задавая условие на отбор значений, содержащихся в другой таблице.

Например, пусть требуется отобразить только те товары, средняя стоимость закупки которых была выше 50.

Для создания такой фильтрации необходимо создать в DataSet и заполнить две таблицы - "Товары" и "Закупки", а также создать связь между ними. Для этого нужно выполнить следующий код:

string ConnectionString = WebConfigurationManager.
 ConnectionStrings["TEST_DB"].ConnectionString;
SqlConnection con = new SqlConnection(ConnectionString);
string query = "SELECT * FROM Товары";
SqlDataAdapter da = new SqlDataAdapter(query, con);
DataSet ds = new DataSet();
da.Fill(ds, "Products");
string query2 = "SELECT * FROM Закупки";
da.SelectCommand.CommandText = query2;
da.Fill(ds, "Purchase");
DataRelation rel = new DataRelation("ProductsPurchase",
ds.Tables["Products"].Columns["КодТовара"],
ds.Tables["Purchase"]. Columns["КодТовара"]);
ds.Relations.Add(rel);

После этого используем следующее условие для фильтрации, задаваемое в свойстве RowFilter:

DataView dv = new DataView(ds.Tables["Products"]);
dv.Sort = "НаименованиеТовара";
dv.RowFilter = "AVG(Child(ProductsPurchase).Цена)>50";
GridView1.DataSource = dv;

Как видно из примера, для подсчета средней цены используется функция AVG(). Обращение Child позволяет сослаться на подчиненную таблицу, установленную ранее в свойстве связи DataRelation. Для определения подчиненной и главной таблиц применяется созданная ранее связь ProductsPurchase.

Реализация трехуровневой архитектуры доступа к данным в ASP.NET

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

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

1. Необходимо открывать соединение с базой данных перед выполнением операции с ней и закрывать сразу после завершения этой операции.
2. Необходимо реализовывать обработку ошибок для гарантированного закрытия подключения, даже если в процессе выполнения операции возникла исключительная ситуация.
3. Так как реализация логики взаимодействия с базой данных описана в методах класса, необходимо передавать исходные данные методам посредством параметров, а результаты возвращать посредством возвращаемого значения.
4. Нельзя позволять клиенту указывать и изменять строку соединения с базой данных, т. к. это может стать причиной нарушения безопасности приложения.
5. Нельзя подключаться к источнику данных с использованием клиентского идентификатора пользователя. Для более эффективной организации взаимодействия приложения с БД лучше использовать систему безопасности на основе ролей либо билетов.
6. При выборе данных из БД нужно выбирать лишь те строки, которые действительно необходимы приложению для работы. Если не следовать данному принципу, быстродействие приложения может заметно падать по мере роста базы данных, т.к. объемы передаваемой по сети информации могут значительно увеличиваться.

Современные приложения создаются на основе объектно-ориентированного подхода. Основным строительным блоком приложения в данном подходе является класс. Все обрабатываемые приложением данные хранятся в объектах, которые также содержат и методы для их обработки. Тем не менее большинство используемых СУБД сегодня построены на принципах реляционной модели данных. Принципы обработки данных в объектных и реляционных моделях отличаются, это необходимо учитывать при организации взаимодействия с базой данных. Ниже рассматриваются некоторые вопросы организации такого взаимодействия.

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

Рассмотрим пример реализации взаимодействия Web-приложения с таблицей "Товары" базы данных "TestDb" с использованием принципов трехуровневой архитектуры.

Прежде всего, создадим класс Product, который содержит поля, соответствующие полям таблицы "Товары". Доступ к этим полям должен осуществляться посредством соответствующих свойств. В типичном объектно-ориентированном приложении такие классы выполняют роль хранилища данных, доступ к которым предоставляют методам, осуществляющим их обработку. В достаточно большом и сложном приложении классы желательно размещать отдельно от основного кода программы. Это позволяет легко отделять код основной программы от кода, который может быть перенесен в другие приложения и повторно использоваться для облегчения и ускорения создания приложения. Для создания класса рекомендуется выполнить команду Website Add New Item, в появившемся диалоговом окне выбрать в качестве типа создаваемого элемента Class, ввести имя класса и нажать кнопку Add. Появившееся диалоговое окно, изображенное на рис. 10.29, сообщает, что классы, используемые приложением, должны быть помещены в папку Арр_Code. Рекомендуется поместить создаваемый класс в эту папку.

увеличить изображение
Рис. 10.29. Диалоговое окно создания нового класса в папке App_Code

Текст класса Product приведен ниже.

public class Product
{
  private int productID;
  private string productName;
  private double productCost;

  public int ProductID
  {
    get { return productID; }
    set { productID = value; }
  }

  public string ProductName
  {
    get { return productName; }
    set { productName = value; }
  }

  public double ProductCost
  {
    get { return productCost; }
    set { productCost = value; }
  }

  public Product(int productID, string productName, double
  productCost)
  {
    this.productID = productID;
    this.productName = productName;
    this.productCost = productCost;
  }
}

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

Создадим класс, предназначенный для организации взаимодействия приложения с таблицей "Товары" базы данных. Этот класс должен содержать необходимые методы для выполнения всех тех операций, которые необходимо выполнять клиентскому приложению с информацией, расположенной в базе данных. В данном примере создадим класс, который "умеет" извлекать информацию о товарах из таблицы "Товары", а также добавлять, удалять и обновлять эту информацию. Добавление и удаление данных являются самыми простыми процедурами, поэтому начнем с их реализации.

Создадим класс ProductsDB, как было описано выше. Определим в классе закрытую переменную connectionString, содержащую строку подключения, которая в момент создания класса (в конструкторе) считывается из файла web.config и помещается в connectionString. В web.config параметр, содержащий строку подключения, может называться по-разному, поэтому можно предусмотреть два конструктора: один — настроенный на извлечение строки подключения из жестко заданного параметра, второй — принимающий имя данного параметра и использующий его для чтения строки подключения:

public class ProductsDB
{
  private string connectionString;
  public ProductsDB()
  {
    connectionString = WebConfigurationManager.ConnectionStrings
     ["Test_Db"].ConnectionString;
  }
  public ProductsDB(string conString)
  {
    connectionString = WebConfigurationManager.ConnectionStrings
     [conString].ConnectionString;
  }
}

Определим метод, предназначенный для добавления новой записи в таблицу "Товары". Для этого создадим объект соединения с базой данных. В данном примере используется СУБД SQL Server Express 2005, но аналогичный код (с небольшими изменениями) можно применять для подключения практически к любой СУБД. Создадим запрос с параметрами, заполним эти параметры значениями, которые были извлечены из полей объекта Product, переданного в данный метод в качестве параметра, и исполним запрос. Текст метода AddProduct, выполняющего вышеописанные действия, представлен ниже.

public int AddProduct(Product prod)
{
  SqlConnection con = new SqlConnection(connectionString);
  string query = "INSERT INTO Товары (КодТовара,Наименование
   Товара,Цена) VALUES (@id,@name,@cost)";
  SqlCommand cmd = new SqlCommand(query, con);
  cmd.CommandType = CommandType.Text;
  cmd.Parameters.Add(new SqlParameter("@id", SqlDbType.Int, 4));
  cmd.Parameters.Add(new SqlParameter("@name", SqlDbType.
   VarChar, 50));
  cmd.Parameters.Add(new SqlParameter("@cost", SqlDbType.Float, 8));
  cmd.Parameters["@id"].Value = prod.ProductID;
  cmd.Parameters["@name"].Value = prod.ProductName;
  cmd.Parameters["@cost"].Value = prod.ProductCost;
  try
  {
    con.Open();
    return cmd.ExecuteNonQuery();
  }
  catch (SqlException e)
  {
    throw new ApplicationException("Ошибка добавления нового
     товара в таблицу Товары");
  }
  finally
  {
    con.Close();
  }
}

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

Аналогичным образом создаются остальные методы работы с базой данных. Создадим метод для удаления товара из таблицы "Товары". Код метода DeleteProduct, осуществляющего эту операцию, приведен ниже.

public void DeleteProduct(int productID)
{
  SqlConnection con = new SqlConnection(connectionString);
  string query = "DELETE FROM Товары WHERE КодТовара=@ID";
  SqlCommand cmd = new SqlCommand(query, con);
  cmd.CommandType = CommandType.Text;
  cmd.Parameters.Add("@ID", SqlDbType.Int, 4);
  cmd.Parameters["@ID"].Value = productID;
  try
  {
    con.Open();
    cmd.ExecuteNonQuery();
  }
  catch (SqlException e)
  {
    throw new ApplicationException("Ошибка удаления товара
     из таблицы Товары");
  }
  finally
  {
    con.Close();
  }
}

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

При создании метода, обновляющего данные в таблице, необходимо учесть принятую стратегию параллелизма, используемую в приложении. Эта тема уже обсуждалась выше. Код метода UpdateProduct, реализующего механизм обновления информации о товаре в БД, приведен ниже.

public void UpdateProduct(Product prod)
{
  SqlConnection con = new SqlConnection(connectionString);
  string query = "UPDATE Товары SET НаименованиеТовара=
   @pName, Цена=@pCost WHERE КодТовара=@ID";
  SqlCommand cmd = new SqlCommand(query, con);
  cmd.CommandType = CommandType.Text;
  cmd.Parameters.Add("@ID",SqlDbType.Int,4);
  cmd.Parameters.Add("@pName", SqlDbType.VarChar, 50);
  cmd.Parameters.Add("@pCost", SqlDbType.Float, 8);
  cmd.Parameters["@ID"].Value = prod.ProductID;
  cmd.Parameters["@pName"].Value = prod.ProductName;
  cmd.Parameters["@pCost"].Value = prod.ProductCost;
  try
  {
    con.Open();
    cmd.ExecuteNonQuery();
  }
  catch (SqlException e)
  {
    throw new ApplicationException("Ошибка обновления
    информации о товаре в таблице Товары");
  }
  finally
  {
    con.Close();
  }
}