Компонент доступа к данным

107

В примерах предыдущих статей вы получили общее представление о ADO.NET в контексте ASP.NET и ознакомились с доступом к данным, основанным на соединениях (создание соединений, команд, чтение данных, вызов хранимых процедур и транзакции). Теперь самое время собрать код доступа к данным в хорошо спроектированное приложение.

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

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

Хорошее, прямолинейное проектное решение компонентов баз данных использует отдельный класс для каждой таблицы базы (или логически связанной группы таблиц). Общие методы доступа к данным, такие как вставка, удаление и модификация записей, должны быть помещены в отдельные, не поддерживающие состояние методы. И, наконец, каждое обращение к базе данных должно использовать выделенную хранимую процедуру. На рисунке ниже показано многоуровневое проектное решение:

Многоуровневое проектное решение с классом базы данных

В рассматриваемом далее примере демонстрируется простой компонент базы данных. Вместо помещения кода работы с базой на веб-страницу он следует более совершенной практике проектирования, связанной с выделением кода в отдельный класс, который может использоваться на многих страницах. Этот класс при желании может быть затем скомпилирован как часть отдельного компонента. Вдобавок строка соединения извлекается из раздела <connectionStrings> файла web.config, а не кодируется жестко.

Компонент базы данных в действительности состоит из двух классов — класса пакета данных, который является оболочкой для отдельной информационной записи (класс данных), и служебного класса базы данных, который выполняет операции с данными базы в коде ADO.NET [класс доступа к данным).

Пакет данных

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

public class EmployeeDetails
{
    private int employeeID;
    private string firstName;
    private string lastName;
    private DateTime birthDate;

    public int EmployeeID
    {
        get { return employeeID; }
        set { employeeID = value; }
    }
    public string FirstName
    {
        get { return firstName; }
        set { firstName = value; }
    }
    public string LastName
    {
        get { return lastName; }
        set { lastName = value; }
    }
    public DateTime BirthDate
    {
        get { return birthDate; }
        set { birthDate = value; }
    }

    public EmployeeDetails(int employeeID, string firstName, string lastName,
        DateTime birthDate)
    {
        this.employeeID = employeeID;
        this.firstName = firstName;
        this.lastName = lastName;
        this.birthDate = birthDate;
    }

    public EmployeeDetails() { }
}

Обратите внимание, что для краткости этот класс не включает всей информации, которая есть в таблице Employees.

Хранимые процедуры

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

CREATE PROCEDURE InsertEmployee
	@LastName varchar(20),
	@FirstName varchar(10),
	@BirthDate datetime,
	@EmployeeID int OUTPUT
AS
INSERT INTO Employees
	(LastName, FirstName, BirthDate)
	VALUES (@LastName, @FirstName, @BirthDate);

SET @EmployeeID = @@IDENTITY


CREATE PROCEDURE CountEmployees 
AS
SELECT COUNT(EmployeeID) FROM Employees


CREATE PROCEDURE DeleteEmployee
	@EmployeeID int
AS
	DELETE FROM Employees WHERE EmployeeID = @EmployeeID
    
    
CREATE PROCEDURE UpdateEmployee 
	@EmployeeID	int,
	@LastName varchar(20),
	@FirstName varchar(10)
AS
	UPDATE Employees
	SET LastName = @LastName, FirstName = @FirstName WHERE EmployeeID = @EmployeeID
    

CREATE PROCEDURE GetEmployee 
@EmployeeID int
AS
SELECT EmployeeID, FirstName, LastName, BirthDate FROM Employees WHERE EmployeeID = @EmployeeID


CREATE PROCEDURE GetAllEmployees 
AS
SELECT EmployeeID, FirstName, LastName, BirthDate FROM Employees

Чтобы добавить эти хранимые процедуры в базу данных Northwind вы можете воспользоваться либо SQL Server Management Studio, как рассказывалось в статье SQL-инъекции, либо окном Server Explorer IDE-среды Visual Studio. В последнем случае вам нужно открыть иерархию базы данных Northwind, выбрать раздел Stored Procedures (Хранимые процедуры), щелкнуть по нему правой кнопкой мыши и в контекстном меню выбрать команду Add New Stored Procedure (Добавить новую хранимую процедуру). В новом окне нужно ввести необходимые SQL-инструкции и нажать кнопку Update:

Создание хранимой процедуры SQL Server

Служебный класс базы данных

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

В рассматриваемом примере служебный класс называется EmployeeDB. Он инкапсулирует весь код доступа к данным и специфичные для конкретной базы детали. Вот его структура:

public class EmployeeDB
{
    private string connectionString;

    public EmployeeDB()
    {
        // Получить строку соединения из web.config
        connectionString = WebConfigurationManager.ConnectionStrings["Northwind"].ConnectionString;
    }
    public EmployeeDB(string connectionString)
    {
        // Установить указанную строку соединения
        this.connectionString = connectionString;
    }

    public int InsertEmployee(EmployeeDetails emp)
    {
        // ...
    }

    public void UpdateEmployee(EmployeeDetails emp)
    {
        // ...
    }

    public void UpdateEmployee(int EmployeeID, string firstName, string lastName)
    {
        // ...
    }

    public void DeleteEmployee(int employeeID)
    {
        // ...
    }

    public EmployeeDetails GetEmployee(int employeeID)
    {
        // ...
    }

    public List<EmployeeDetails> GetEmployees()
    {
        // ...
    }

    public int CountEmployees()
    {
        // ...
    }
}

Возможно, вы заметили, что класс EmployeeDB использует методы экземпляра, а не статические методы. Причина в том, что несмотря на то, что класс EmployeeDB не сохраняет никакого состояния базы данных, он сохраняет строку соединения как приватную переменную-член. Поскольку это класс, экземпляры которого могут быть созданы, строка соединения может быть извлечена при каждом создании экземпляра класса, а не при каждом вызове метода. Такой подход делает код несколько яснее и позволяет ему работать немного быстрее (за счет отсутствия необходимости в многократном чтении файла web.config). Однако выигрыш относительно мал, поэтому в компонентах базы данных вполне можно применять и статические методы.

Каждый метод использует один и тот же тщательный подход, полагаясь исключительно на хранимые процедуры для взаимодействия с базой. Вот как будет выглядеть код вставки записи, предполагая, что импортировано пространство имен System.Data.SqlClient:

public int InsertEmployee(EmployeeDetails emp)
{
        SqlConnection con = new SqlConnection(connectionString);
        SqlCommand cmd = new SqlCommand("InsertEmployee", con);
        cmd.CommandType = CommandType.StoredProcedure;

        cmd.Parameters.Add(new SqlParameter("@FirstName", SqlDbType.NVarChar, 10));
        cmd.Parameters["@FirstName"].Value = emp.FirstName;
        cmd.Parameters.Add(new SqlParameter("@LastName", SqlDbType.NVarChar, 20));
        cmd.Parameters["@LastName"].Value = emp.LastName;
        cmd.Parameters.Add(new SqlParameter("@BirthDate", SqlDbType.DateTime));
        cmd.Parameters["@BirthDate"].Value = emp.BirthDate;
        cmd.Parameters.Add(new SqlParameter("@EmployeeID", SqlDbType.Int, 4));
        cmd.Parameters["@EmployeeID"].Direction = ParameterDirection.Output;

        try
        {
            con.Open();
            cmd.ExecuteNonQuery();
            return (int)cmd.Parameters["@EmployeeID"].Value;
        }
        catch (SqlException)
        {
            // Замените эту ошибку чем-то более специфичным. 
            // Здесь также можно протоколировать ошибки
            throw new ApplicationException("Ошибка данных");
        }
        finally
        {
            con.Close();
        }
}

Как видите, метод принимает данные как объект EmployeeDetails. Любая ошибка перехватывается, но существенные внутренние детали, связанные с ее возникновением, коду веб-страницы не возвращаются. Это предотвращает выдачу информации на веб-странице, которая может послужить источником для возможного взлома. Также это является идеальным местом для вызова другого метода в протоколирующем компоненте, чтобы записывать полную информацию об ошибке в журнал или другую базу данных.

Методы GetEmployee() и GetEmployees() возвращают данные, используя, соответственно, единственный объект EmployeeDetails или список объектов EmployeeDetails:

public EmployeeDetails GetEmployee(int employeeID)
{
        SqlConnection con = new SqlConnection(connectionString);
        SqlCommand cmd = new SqlCommand("GetEmployee", con);
        cmd.CommandType = CommandType.StoredProcedure;

        cmd.Parameters.Add(new SqlParameter("@EmployeeID", SqlDbType.Int, 4));
        cmd.Parameters["@EmployeeID"].Value = employeeID;

        try
        {
            con.Open();
            SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow);

            // Получить новую строку
            reader.Read();
            EmployeeDetails emp = new EmployeeDetails(
                (int)reader["EmployeeID"], (string)reader["FirstName"],
                (string)reader["LastName"], (DateTime)reader["BirthDate"]);
            reader.Close();
            return emp;
        }
        catch (SqlException)
        {
            throw new ApplicationException("Ошибка данных");
        }
        finally
        {
            con.Close();
        }
}

public List<EmployeeDetails> GetEmployees()
{
        SqlConnection con = new SqlConnection(connectionString);
        SqlCommand cmd = new SqlCommand("GetAllEmployees", con);
        cmd.CommandType = CommandType.StoredProcedure;

        // Создать коллекцию для всех записей о сотрудниках
        List<EmployeeDetails> employees = new List<EmployeeDetails>();

        try
        {
            con.Open();
            SqlDataReader reader = cmd.ExecuteReader();

            while (reader.Read())
            {
                EmployeeDetails emp = new EmployeeDetails(
                    (int)reader["EmployeeID"], (string)reader["FirstName"],
                    (string)reader["LastName"], (DateTime)reader["BirthDate"]);
                employees.Add(emp);
            }
            reader.Close();

            return employees;
        }
        catch (SqlException)
        {
            throw new ApplicationException("Ошибка данных");
        }
        finally
        {
            con.Close();
        }
}

Метод UpdateEmployee() играет специфическую роль. Он определяет стратегию параллелизма вашего компонента базы данных. Вот его код (он имеет перегруженную версию):

public void UpdateEmployee(EmployeeDetails emp)
{
        SqlConnection con = new SqlConnection(connectionString);
        SqlCommand cmd = new SqlCommand("UpdateEmployee", con);
        cmd.CommandType = CommandType.StoredProcedure;

        cmd.Parameters.Add(new SqlParameter("@FirstName", SqlDbType.NVarChar, 10));
        cmd.Parameters["@FirstName"].Value = emp.FirstName;
        cmd.Parameters.Add(new SqlParameter("@LastName", SqlDbType.NVarChar, 20));
        cmd.Parameters["@LastName"].Value = emp.LastName;
        cmd.Parameters.Add(new SqlParameter("@EmployeeID", SqlDbType.Int, 4));
        cmd.Parameters["@EmployeeID"].Value = emp.EmployeeID;

        try
        {
            con.Open();
            cmd.ExecuteNonQuery();
        }
        catch (SqlException)
        {
            throw new ApplicationException("Ошибка данных");
        }
        finally
        {
            con.Close();
        }
}

public void UpdateEmployee(int EmployeeID, string firstName, string lastName)
{
        SqlConnection con = new SqlConnection(connectionString);
        SqlCommand cmd = new SqlCommand("UpdateEmployee", con);
        cmd.CommandType = CommandType.StoredProcedure;

        cmd.Parameters.Add(new SqlParameter("@FirstName", SqlDbType.NVarChar, 10));
        cmd.Parameters["@FirstName"].Value = firstName;
        cmd.Parameters.Add(new SqlParameter("@LastName", SqlDbType.NVarChar, 20));
        cmd.Parameters["@LastName"].Value = lastName;
        cmd.Parameters.Add(new SqlParameter("@EmployeeID", SqlDbType.Int, 4));
        cmd.Parameters["@EmployeeID"].Value = EmployeeID;

        try
        {
            con.Open();
            cmd.ExecuteNonQuery();
        }
        catch (SqlException)
        {
            throw new ApplicationException("Ошибка данных");
        }
        finally
        {
            con.Close();
        }
}

И, наконец, методы DeleteEmployee() и CountEmployees() дополняют последние два ингредиента:

public void DeleteEmployee(int employeeID)
{
        SqlConnection con = new SqlConnection(connectionString);
        SqlCommand cmd = new SqlCommand("DeleteEmployee", con);
        cmd.CommandType = CommandType.StoredProcedure;

        cmd.Parameters.Add(new SqlParameter("@EmployeeID", SqlDbType.Int, 4));
        cmd.Parameters["@EmployeeID"].Value = employeeID;

        try
        {
            con.Open();
            cmd.ExecuteNonQuery();
        }
        catch (SqlException)
        {
            throw new ApplicationException("Ошибка данных");
        }
        finally
        {
            con.Close();
        }
}
    
public int CountEmployees()
{
        SqlConnection con = new SqlConnection(connectionString);
        SqlCommand cmd = new SqlCommand("CountEmployees", con);
        cmd.CommandType = CommandType.StoredProcedure;

        try
        {
            con.Open();
            return (int)cmd.ExecuteScalar();
        }
        catch (SqlException)
        {
            throw new ApplicationException("Ошибка данных");
        }
        finally
        {
            con.Close();
        }
}

Стратегии параллелизма

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

Существует несколько распространенных подходов к управлению параллелизмом. Важнее всего понять, что эта стратегия определяется способом написания команд UPDATE и DELETE (в частности, способом формирования конструкции WHERE). Ниже описаны наиболее типичные примеры:

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

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

Чтобы лучше понять все это, рассмотрим, что случится, если два пользователя попытаются зафиксировать разные обновления записи о сотруднике с использованием такого метода, как UpdateEmployee(), реализующего подход "последний выигрывает". Первый пользователь обновляет почтовый адрес. Второй пользователь обновляет имя сотрудника и в то же время нечаянно применяет старый почтовый адрес. Проблема в том, что метод UpdateEmployee() никак не сможет узнать, какие изменения фиксируются. Это значит, что он вытолкнет весь блок значений, находящийся в памяти, обратно в источник данных, даже если старые значения не изменялись (при этом отменив чьи-то изменения).

При наличии больших, сложных записей и необходимости поддержки различных типов редактирования, простейший путь решения состоит в создании более узконаправленных методов. Вместо создания обобщенного метода UpdateEmployee() используйте узконаправленные методы вроде UpdateEmployeeAddress() или ChangeEmployeeStatus(). Эти методы могут выполнять более ограниченные операторы UPDATE без риска повторного применения старых значений.

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

Тестирование компонента базы данных

Теперь, когда компонент данных создан, понадобится простая тестовая страница, чтобы испытать его. Как и с любым другим компонентом, начать следует с добавления ссылки на сборку компонента. Затем можно импортировать используемое пространство имен, чтобы облегчить реализацию классов EmployeeDetails и EmployeeDB. Единственное, что останется — написать код взаимодействия с классами. В данном примере этот код помещен в обработчик события Page.Load.

Для начала код извлекает и записывает количество и список сотрудников, используя вспомогательный метод WriteEmployeesList(), который транслирует детали в HTML-разметку и отображает ее в элементе управления Label. Далее код добавляет запись и снова выводит содержимое таблицы. И, наконец, код удаляет добавленную запись и вновь отображает содержимое таблицы Employees. Полный код страницы выглядит следующим образом:

using System;
using System.Collections.Generic;
using System.Linq;

public partial class _Default : System.Web.UI.Page
{
    // Создать компонент базы данных, чтобы он был доступен в пределах всей страницы
    EmployeeDB db = new EmployeeDB();

    protected void Page_Load(object sender, EventArgs e)
    {
        Label1.Text = "<h1>Сотрудники компании Northwind</h1>";
        WriteEmployeesList();

        // Добавим нового сотрудника. Обратите внимание, что
        // значение идентификатора просто устанавливается в 0, т.к. оно генерируется 
        // сервером базы данных и заполняется автоматически при вызове
        int id = db.InsertEmployee(
            new EmployeeDetails(0, "Ivan", "Ivanov", new DateTime(1980, 12, 25)));
        Label1.Text += "<br>Добавлен 1 новый сотрудник<br><br>";
        WriteEmployeesList();

        // Удалить теперь этого сотрудника
        db.DeleteEmployee(id);
        Label1.Text += "<br>Новый сотрудник удален<br><br>";
        WriteEmployeesList();
    }

    // Вспомогательный метод для вывода записей таблицы Employees
    private void WriteEmployeesList()
    {
        string result = "Число сотрудников: <b>" + db.CountEmployees() + "</b><br><br>";

        // Используем LINQ чтобы наполнить список сотрудников
        db.GetEmployees().Select(p =>
        {
            result += String.Format("<li>{0} {1} {2} (<em>{3:d}</em>)<br>",
                p.EmployeeID, p.LastName, p.FirstName, p.BirthDate);
            return p;
        }).ToList();    // Сделать запрос неотложенным

        Label1.Text += result;
    }
}

В результате будет получена следующая страница:

Использование компонента базы данных
Пройди тесты
Лучший чат для C# программистов