Основы ADO.NET

Организация пула соединений

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

Пул соединений - это практика хранения приложением постоянного набора открытых соединений, разделяемых между сеансами при использовании одного и того же источника данных. Это позволяет избежать бесконечного создания и уничтожения соединений. При организации пула вызов клиентом метода Open() обслуживается непосредственно из доступного пула без повторного открытия соединения. Когда клиент освобождает соединение методом Close() или Dispose(), оно не закрывается, а возвращается в пул, чтобы обслужить следующий запрос.

Пулы автоматически организуются самими поставщиками данных только в том случае, если строки соединений в последовательных запросах полностью совпадают. Если имеется хоть небольшое отличие, создается новое подключение в новом пуле.

Пулы соединений SQL Server и Oracle используют механизм полнотекстового сравнения. Это значит, что любое минимальное изменение в строке соединений нарушает пул, даже если в ней просто изменяется порядок параметров или добавляется простой пробел. Поэтому лучше не помещать строку соединений в код установки соединения, а сохранять в файле web.config, как это мы сделали в примере.

Пулы соединений можно настраивать, добавляя соответствующие атрибуты в строку соединений. Параметры настройки пулов соединений приведены в таблице

Некоторые поставщики имеют методы очистки пула соединений. Например, поставщик SqlConnection имеет статические методы SqlConnection.ClearPool(connectionString) - очистка пула конкретного соединения и SqlConnection.ClearAllPools() - очистка пулов всех соединений в текущем домене приложения. Эти методы не удаляют соединения физически, а помечают на удаление для бредущего сзади сборщика мусора.

Класс Command и DataReader

Класс Command позволяет выполнить любой SQL-запрос к открытому соединению базы данных. Для того, чтобы использовать команду, нужно выбрать ее тип, установить ее текст и привязать к открытому соединению. Эту работу можно выполнить, установив значения свойств CommandType, CommandText и Connection, либо передать необходимую информацию в аргументах конструктора класса Command.

Текстом команды может быть SQL-оператор, хранимая процедура ( stored procedure ) или имя таблицы. Это определяется значением перечисления CommandType, присваиваемого свойству Command.CommandType.

Расширим наш пример, чтобы применить код создания объекта Command.

• Создайте из копии файла TestConnection1.aspx файл с именем TestConCommand.aspx и установите его стартовым
• Дополните код страницы TestConCommand.aspx так

  <%@ Page Language="C#" %>
      
  <script runat="server">
      
      protected void Page_Load(object sender, EventArgs e)
      {
          // Извлекли строку соединения из web.config
          string connectString = System.Web.Configuration.WebConfigurationManager.
              ConnectionStrings["Northwind"].ConnectionString;
          
          // Установили минимальный размер пула соединения равный 10
          connectString += "Min Pool Size=10;";
          
          // Создали объект соединения
          System.Data.SqlClient.SqlConnection con =
              new System.Data.SqlClient.SqlConnection(connectString);
      
          using (con)
          {
              // Получить соединение из пула (если есть)
              // или создать пул с 10 соединениями (если нет)
              con.Open();
              lblInfo.Text = "<b>Соединение:</b> " + con.State.ToString();
              
              // Создали объект Command
              System.Data.SqlClient.SqlCommand cmd = 
                  new System.Data.SqlClient.SqlCommand();
              
              // Настроили объект Command
              cmd.Connection = con;
              cmd.CommandType = System.Data.CommandType.Text;
              cmd.CommandText = "SELECT * FROM Employees";
          }
      
          // Вернуть соединение в пул
          lblInfo.Text += "<br /><b>Соединение:</b> ";
          lblInfo.Text += con.State.ToString();
      }
  </script>
      
  <html xmlns="http://www.w3.org/1999/xhtml" >
  <head runat="server">
      <title>Untitled Page</title>
  </head>
  <body>
      <form id="form1" runat="server">
      <div>
          <asp:Label ID="lblInfo" runat="server" Text="Label"></asp:Label></div>
      </form>
  </body>
  </html>
• Исполните страницу, чтобы получить такой результат
  

  
  

Учитывая, что по умолчанию установлено CommandType = CommandType.Text, команду можно сразу указать в перегруженном конструкторе при создании объекта команд. Например, код

// Создали объект Command
System.Data.SqlClient.SqlCommand cmd = 
    new System.Data.SqlClient.SqlCommand();

// Настроили объект Command
cmd.Connection = con;
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = "SELECT * FROM Employees";

можно заменить кодом

// Создали и настроили объект Command
System.Data.SqlClient.SqlCommand cmd = 
    new System.Data.SqlClient.SqlCommand("SELECT * FROM Employees", con);

В данном примере мы просто настроили, но не выполнили объект класса Command, поэтому никакой выборки данных из БД не получили. Объект Command имеет три метода выполнения команды, приведенные в таблице на примере класса SqlCommand

Метод SqlCommand.ExecuteReader()

Объект класса DataReader представляет собой результирующий набор данных только для чтения и является самым простым и быстрым средством доступа к данным. Он является выборкой данных по запросу SQL-команды SELECT и состоит из строк и колонок, которые можно анализировать и отображать. Но в нем нет возможностей сортировки, как в более развитых объектах. Ниже приведены несколько методов DataReader на примере класса System.Data.SqlClient.SqlDataReader

Обработка одного результирующего набора данных SqlDataReader

Приведем пример, в котором создается и выполняется простая команда запроса для выборки всех записей из таблицы Employees учебной базы данных Northwind. Мы помним, что строка соединения была нами задана ранее в конфигурационном файле.

• Создайте копию страницы TestConCommand.aspx с именем TestDataReader.aspx
• Заполните страницу приведенным кодом

  <%@ Page Language="C#" %>
      
  <script runat="server">
      
      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;
          cmd.CommandText = "SELECT * FROM Employees";
      
          // Создаем ссылку на вспомогательную строку
          System.Text.StringBuilder htmlStr = new StringBuilder("");
          
          using (con)
          {
              // Открыли соединение
              con.Open();
      
              // Выполняем команду и получаем результирующий набор данных
              System.Data.SqlClient.SqlDataReader reader =
                  cmd.ExecuteReader();
              
              // Перебираем все записи результирующего набора
              // и строим HTML-строку
              while (reader.Read())
              {
                  htmlStr.Append("<li>Служащий ");
                  htmlStr.Append(reader["TitleOfCourtesy"]);
                  htmlStr.Append(" <b>");
                  htmlStr.Append(reader.GetString(1));
                  htmlStr.Append("</b>, ");
                  htmlStr.Append(reader.GetString(2));
                  htmlStr.Append(" - работает с ");
                  htmlStr.Append(reader.GetDateTime(6).ToString("d"));
                  htmlStr.Append("</li>");
              }
              
              // Закрываем набор данных
              reader.Close();
          }
      
          // Выдаем результаты запроса
          lblInfo.Text = htmlStr.ToString();
      }
  </script>
      
  <html xmlns="http://www.w3.org/1999/xhtml" >
  <head runat="server">
      <title>Untitled Page</title>
  </head>
  <body>
      <form id="form1" runat="server">
      <div>
          <asp:Label ID="lblInfo" runat="server" Text="Label"></asp:Label></div>
      </form>
  </body>
  </html>
• Исполните страницу TestDataReader.aspx, должен получиться такой клиентский результат

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

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