Компоненты данных ADO.NET

Файлы к лекции Вы можете скачать здесь.

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

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

Построение компонента доступа к данным

При создании хорошо спроектированного класса-компонента данных нужно следовать некоторым рекомендациям:

1. Быстрое открытие и закрытие соединения. Соединения никогда не должны удерживаться открытыми и клиент никогда не должен ими управлять. Удерживаемое открытым соединение занимает ресурсы, что пагубно влияет на масштабируемость (мало клиентов можно обслужить одновременно).
2. Детальная обработка ошибок. При любом исключении нужно гарантированно закрыть соединение с базой данных и известить об этом пользователя.
3. Практика дизайна без сохранения состояния. Передавать всю необходимую методу информацию в его входных параметрах и возвращать извлеченные данные через выходные параметры метода.
4. Запрет для клиента указывать параметры строки соединения. Это нарушает безопасность и ослабляет использование пулов соединений, которые требуют точных совпадений в повторных соединениях.
5. Исключение возможности соединения под клиентским идентификатором пользователя. Аутентификацию и ограничение прав пользователя нужно выявлять отдельно и заранее, а затем подключаться к базе данных от своего имени с последующим формированием нужного SQL-запроса. Это исключит возможность вариации параметров в строке соединений и сохранит работу механизма пула и работает быстрее, чем попытка выполнения запроса от имени некорректной учетной записи с ожиданием ошибки.
6. Ограничение количества информации, предоставляемой пользователю за один запрос. Каждый запрос пользователя должен благоразумно выбирать лишь ту информацию, которая ему действительно нужна. Необходимо интенсивно использовать в SQL-запросах конструкцию WHERE или TOP, ограничивающие диапазон выборки данных. Это разгрузит как саму базу данных, так и сеть передачи данных клиенту.
7. По-возможности использовать отдельный класс для каждой таблицы базы данных или логически связанной группы таблиц.
8. Запросы к базе данных выполнять через соответствующие тщательно разработанные и отлаженные хранимые процедуры. Это обеспечит должную безопасность и быстродействие.

На следующем рисунке показан рекомендуемый многослойный дизайн работы с базой данных.

Реализуем этот простой дизайн в непростом примере. Разработаем страницу, в которой будем работать с таблицей Employees учебной базы данных Northwind.

• Создайте новый сайт командой File/New/Web Site с именем WebSite8
  

  
  увеличить изображение
  

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

Создание страницы для одноразовой записи хранимых процедур

Прежде чем начать кодировать логику доступа к таблице Employees, создадим и запишем в базу данных набор хранимых процедур SQL-запросов, необходимых для извлечения, вставки и обновления информации. Хранимые процедуры - это ничто иное, как именованные SQL-команды, хранящиеся прямо в базе данных. Удобство хранимых процедур в их гибкости и безопасности - они принимают фактические значения параметров SQL-запроса и недоступны вне кода приложения.

• Добавьте к приложению командой Website/Add Existing Item страницу TestStoredProcedure.aspx из каталога WebSite7 и переименуйте ее в SaveStoredProcedure.aspx
• Аналогичным образом добавьте к приложению файл Web.Config из каталога WebSite7 с готовой строкой соединения и откорректируйте его так

  <?xml version="1.0"?>
  <configuration>
  	<connectionStrings>
  		<add name="Northwind" connectionString="Data Source=localhost; 
     Initial Catalog=Northwind; user id=sa; password=;" />
  	</connectionStrings>
  	<system.web>
  		<compilation debug="true"/>
  	</system.web>
  </configuration>
• Откорректируйте страницу SaveStoredProcedure.aspx следующим образом

  <%@ Page Language="C#" %>
      
  <script runat="server">
      
      // Объявления хранимых процедур
      // Вставка записи
      string sql1 =
          "CREATE PROCEDURE InsertEmployee "
        + "@EmployeeID		int OUTPUT,"
        + "@FirstName		varchar(10),"
        + "@LastName		varchar(20),"
        + "@TitleOfCourtesy	varchar(25) "
        + "AS "
        + "INSERT INTO Employees "
        + "(TitleOfCourtesy, LastName, FirstName, HireDate) "
        + "VALUES(@TitleOfCourtesy, @LastName, @FirstName, GETDATE()) "
        + "SET @EmployeeID = @@IDENTITY";
      
      // Удаление записи
      string sql2 =
          "CREATE PROCEDURE DeleteEmployee "
        + "@EmployeeID		int "
        + "AS "
        + "DELETE FROM Employees WHERE EmployeeID = @EmployeeID";
      
      // Обновление записи
      string sql3 =
          "CREATE PROCEDURE UpdateEmployee "
        + "@EmployeeID		int, "
        + "@FirstName		varchar(10),"
        + "@LastName		varchar(20),"
        + "@TitleOfCourtesy	varchar(25) "
        + "AS "
        + "UPDATE Employees "
        + "SET "
        + "FirstName = @FirstName,"
        + "LastName = @LastName,"
        + "TitleOfCourtesy = @TitleOfCourtesy "
        + "WHERE EmployeeID = @EmployeeID";
      
      // Выбрать все
      string sql4 =
          "CREATE PROCEDURE GetAllEmployees "
        + "AS "
        + "SELECT EmployeeID, FirstName, LastName, TitleOfCourtesy "
        + "FROM Employees";
      
      // Подсчитать число записей
      string sql5 =
          "CREATE PROCEDURE CountEmployees "
        + "AS "
        + "SELECT COUNT(EmployeeID) "
        + "FROM Employees";
      
      // Выбрать запись
      string sql6 =
          "CREATE PROCEDURE GetEmployee "
        + "@EmployeeID		int "
        + "AS "
        + "SELECT EmployeeID, FirstName, LastName, TitleOfCourtesy "
        + "FROM Employees "
        + "WHERE EmployeeID = @EmployeeID";
      
      protected void Page_Load(object sender, EventArgs e)
      {
          // Извлекли строку соединения из web.config
          string connectString = System.Web.Configuration.WebConfigurationManager.
              ConnectionStrings["Northwind"].ConnectionString;
      
          // Создали объект соединения
          System.Data.SqlClient.SqlConnection con =
              new System.Data.SqlClient.SqlConnection(connectString);
      
          // Создали объект Command
          System.Data.SqlClient.SqlCommand cmd =
              new System.Data.SqlClient.SqlCommand();
      
          // Настроили объект Command
          cmd.Connection = con;
          cmd.CommandType = System.Data.CommandType.Text;
      
          lblInfo.Text = "";
      
          // Открываем соединение в безопасном режиме
          try
          {
              con.Open();
          }
          catch
          {
              con.Close();
              return;
          }
      
          // Выполняем команды в безопасном режиме
          // Удаляем процедуры на случай повторного запуска этой страницы
          try
          {
              cmd.CommandText = "DROP PROCEDURE InsertEmployee";
              cmd.ExecuteNonQuery();
              lblInfo.Text += "Прошли \"DROP PROCEDURE InsertEmployee\"<br />";
          }
          catch { }
          try
          {
              cmd.CommandText = "DROP PROCEDURE DeleteEmployee";
              cmd.ExecuteNonQuery();
              lblInfo.Text += "Прошли \"DROP PROC DeleteEmployee\"<br />";
          }
          catch { }
          try
          {
              cmd.CommandText = "DROP PROCEDURE UpdateEmployee";
              cmd.ExecuteNonQuery();
              lblInfo.Text += "Прошли \"DROP PROCEDURE UpdateEmployee\"<br />";
          }
          catch { }
          try
          {
              cmd.CommandText = "DROP PROCEDURE GetAllEmployees";
              cmd.ExecuteNonQuery();
              lblInfo.Text += "Прошли \"DROP PROCEDURE GetAllEmployees\"<br />";
          }
          catch { }
          try
          {
              cmd.CommandText = "DROP PROCEDURE CountEmployees";
              cmd.ExecuteNonQuery();
              lblInfo.Text += "Прошли \"DROP PROCEDURE CountEmployees\"<br />";
          }
          catch { }
          try
          {
              cmd.CommandText = "DROP PROCEDURE GetEmployee";
              cmd.ExecuteNonQuery();
              lblInfo.Text += "Прошли \"DROP PROCEDURE GetEmployee\"<br />";
          }
          catch { }
      
          // Выполняем команды в безопасном режиме
          // Добавляем в базу данных хранимые процедуры
          try
          {
              cmd.CommandText = sql1;
              cmd.ExecuteNonQuery();
              lblInfo.Text += "Прошли \"CREATE PROCEDURE InsertEmployee\"<br />";
          }
          catch { }
          try
          {
              cmd.CommandText = sql2;
              cmd.ExecuteNonQuery();
              lblInfo.Text += "Прошли \"CREATE PROCEDURE DeleteEmployee\"<br />";
          }
          catch { }
          try
          {
              cmd.CommandText = sql3;
              cmd.ExecuteNonQuery();
              lblInfo.Text += "Прошли \"CREATE PROCEDURE UpdateEmployee\"<br />";
          }
          catch { }
          try
          {
              cmd.CommandText = sql4;
              cmd.ExecuteNonQuery();
              lblInfo.Text += "Прошли \"CREATE PROCEDURE GetAllEmployees\"<br />";
          }
          catch { }
          try
          {
              cmd.CommandText = sql5;
              cmd.ExecuteNonQuery();
              lblInfo.Text += "Прошли \"CREATE PROCEDURE CountEmployees\"<br />";
          }
          catch { }
          try
          {
              cmd.CommandText = sql6;
              cmd.ExecuteNonQuery();
              lblInfo.Text += "Прошли \"CREATE PROCEDURE GetEmployee\"<br />";
      
          }
          catch { }
          finally
          {
              con.Close();
          }
      }
  </script>
      
  <html xmlns="http://www.w3.org/1999/xhtml">
  <head id="Head1" runat="server">
      <title>Untitled Page</title>
  </head>
  <body>
      <form id="form1" runat="server">
          <asp:Label ID="lblInfo" runat="server" />
      </form>
  </body>
  </html>
• Выполните страницу SaveStoredProcedure.aspx, чтобы добавить в базу хранимые процедуры, которые мы далее будем использовать при разработке примера