Содержание

• Введение
• Введение в ADO.NET
• Управление соединениями с базами данных
• Обработка ошибок
• Производительность
• Подключение через брандмауэры
• Работа с большими двоичными объектами (BLOB)
• Транзакции
• Загрузка данных порциями
• Приложение

Благодарности

Выражаем признательность всем, кто участвовал в подготовке этой статьи: Bill Vaughn, Mike Pizzo, Doug Rothaus, Kevin White, Blaine Dokter, David Schleifer, Graeme Malcolm (Content Master), Bernard Chen (Sapient), Matt Drucker (Turner Broadcasting) и Steve Kirk.

Вопросы? Замечания? Предложения? С авторами статьи можно связаться по адресу devfdbck@microsoft.com (EN).

Хотите освоить платформу .NET и задействовать всю ее мощь? Для обмена опытом лучше всего поработать бок о бок с экспертами из технологических центров Microsoft (Microsoft Technology Center). Дополнительную информацию можно получить по ссылке www.microsoft.com/business/services/mtc.asp (EN).

Введение

При разработке уровня доступа к данным приложения .NET следует использовать модель доступа к данным Microsoft^® ADO.NET. ADO.NET обладает богатыми возможностями и удовлетворяет требованиям доступа к данным, предъявляемым многоуровневыми слабосвязанными Web-приложениями и Web-сервисами. Как и многие другие объектные модели с поддержкой богатой функциональности, ADO.NET позволяет решать одни и те же задачи несколькими способами.

В этом документе содержатся рекомендации по выбору наиболее подходящего метода доступа к данным. С этой целью детально описывается целый ряд типичных ситуаций доступа к данным, даются рекомендации по повышению производительности и предлагаются наиболее эффективные способы работы. Кроме того, в документе даются ответы на другие часто задаваемые вопросы: где лучше хранить строки подключения к базам данных? Как реализовать поддержку пула соединений (connection pooling)? Как работать с транзакциями? Как загружать данные постранично (paging), чтобы пользователи могли пролистывать наборы записей большого объема?

Заметьте, что в этом документе основное внимание уделяется применению ADO.NET для доступа к данным Microsoft SQL Server™ 2000 с использованием SQL Server .NET Data Provider — одного из двух провайдеров данных, поставляемых с ADO.NET. Там, где это нужно, в документе подчеркиваются особенности, о которых следует знать при использовании OLE DB .NET Data Provider для доступа к другим источникам данных с поддержкой OLE DB.

Конкретную реализацию компонента доступа к данным, разработанного с применением принципов и методов, описанных в этом документе, см. в Data Access Application Block (EN). Обратите внимание, что для этой реализации имеется исходный код, который можно напрямую использовать в ваших приложениях .NET.

Кому адресован этот документ

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

Что вы должны знать

Чтобы применить это руководство для создания приложений .NET, необходим практический опыт разработки кода для доступа к данным с использованием ADO (ActiveX Data Objects) и/или OLE DB, а также опыт работы с SQL Server. Кроме того, вы должны знать, как разрабатывать управляемый код для платформы .NET, и быть в курсе фундаментальных изменений, внесенных моделью доступа к данным ADO.NET. Дополнительную информацию по программированию для платформы .NET см. по ссылке msdn.microsoft.com/net (EN).

Введение в ADO.NET

ADO.NET — модель доступа приложений .NET к данным. Ее можно использовать для доступа к реляционным СУБД, таким как SQL Server 2000, и ко многим дополнительным источникам данных, для работы с которыми предназначен провайдер OLE DB. В известной степени ADO.NET отражает новейшие эволюционные достижения в развитии технологии ADO. Однако в ADO.NET появился ряд серьезных изменений и новшеств, вызванных слабосвязанной природой Web-приложений и тем фактом, что по сути они отсоединены от баз данных. Сравнение ADO и ADO.NET см. в статье “ADO.NET for the ADO Programmer” в MSDN.

Одно из ключевых новшеств ADO.NET — замена ADO-объекта Recordset комбинацией объектов DataTable, DataSet, DataAdapter и DataReader. DataTable представляет набор (collection) записей отдельной таблицы и в этом отношении аналогичен Recordset. DataSet представляет набор объектов DataTable, а также содержит отношения и ограничения, используемые при связывании таблиц. На самом деле DataSet — это хранящаяся в памяти реляционная структура данных со встроенной поддержкой XML (Extensible Markup Language).

Одна из основных особенностей объекта DataSet в том, что ему не известен источник данных, который использовался для его заполнения. Это отсоединенный, автономный объект, который представляет некий набор данных и может передаваться от компонента к компоненту через различные уровни многоуровневого приложения. Кроме того, DataSet можно сериализовать в поток данных XML, благодаря чему этот объект идеально подходит для передачи данных между гетерогенными платформами. Объект DataAdapter используется ADO.NET для двухстороннего обмена данными между DataSet и нижележащим источником данных. DataAdapter также предоставляет расширенные возможности в пакетном обновлении данных — функциональность, которая ранее поддерживалась Recordset.

На рис. 1 показана полная объектная модель DataSet.

Рис. 1. Объектная модель DataSet

Провайдеры данных .NET

В ADO.NET используются так называемые провайдеры данных (Data Providers) .NET. Они обеспечивают доступ к соответствующим источникам данных и заключают в себе четыре ключевых объекта (Connection, Command, DataReader и DataAdapter). В настоящее время с ADO.NET поставляются два провайдера:

• SQL Server .NET Data Provider. Предназначен для работы с базами данных Microsoft SQL Server 7.0 и более поздних версий. Оптимизирован для доступа к SQL Server и взаимодействует с ним напрямую по «родному» протоколу передачи данных SQL Server. Всегда пользуйтесь этим провайдером при работе с SQL Server 7.0 или SQL Server 2000.
• OLE DB .NET Data Provider. Управляемый провайдер для источников данных OLE DB. Немного уступает по эффективности SQL Server .NET Data Provider, так как взаимодействует с базой данных через уровень OLE DB. Имейте в виду, что этим провайдером не поддерживается провайдер OLE DB для ODBC (Open Database Connectivity), MSDASQL. Для источников данных ODBC используйте ODBC .NET Data Provider, описанный ниже. Список провайдеров OLE DB, совместимых с ADO.NET, см. по ссылке msdn.microsoft.com/library/en-us/cpguidnf/html/cpconadonetproviders.asp (EN).

Остальные провайдеры данных .NET в настоящее время находятся в состоянии бета-тестирования:

• ODBC .NET Data Provider. В данный момент доступна для загрузки первая бета-версия. Этот провайдер обеспечивает «родной» доступ к ODBC-драйверам так же, как и OLE DB .NET Data Provider к «родным» провайдерам OLE DB. Получить дополнительную информацию об ODBC .NET и скачать бета-версию можно по ссылке msdn.microsoft.com/downloads/default.asp?URL=/code/sample.asp?url=/MSDN-FILES/027/001/668/msdncompositedoc.xml (EN).
• Управляемый провайдер для считывания XML из SQL Server 2000. XML for SQL Server Web update 2 (в настоящий момент проходит бета-тестирование) включает, помимо всего прочего, управляемый провайдер, предназначенный специально для считывания XML из SQL Server 2000. Дополнительную информацию об этом обновлении см. по ссылке msdn.microsoft.com/code/default.asp?url=/code/sample.asp?url=/msdn-files/027/001/602/msdncompositedoc.xml (EN).

Структура пространств имен

Структура пространств имен

• System.Data.SqlClient. Содержит типы SQL Server .NET Data Provider.
• System.Data.OleDb. Содержит типы OLE DB .NET Data Provider.
• System.Data.Odbc. Содержит типы ODBC .NET Data Provider.
• System.Data. Содержит типы, независимые от провайдеров, например DataSet и DataTable.

Для каждого из провайдеров в его пространстве имен содержатся реализации объектов Connection, Command, DataReader и DataAdapter. Имена реализаций объектов из пространства имен SqlClient начинаются с префикса “Sql”, а имена реализаций из пространства имен OleDb — с префикса “OleDb”. Например, реализация объекта Connection из пространства имен SqlClient называется SqlConnection, тогда как ее эквивалент из OleDb — OleDbConnection. Соответствующие реализации объекта DataAdapter называются SqlDataAdapter и OleDbDataAdapter.

Базовые принципы программирования

Если вы собираетесь работать с различными источниками данных и планируете переносить свой код с одного источника данных на другой, подумайте о реализации интерфейсов IDbConnection, IDbCommand, IDataReader и IdbDataAdapter, принадлежащих пространству имен System.Data. Все реализации объектов Connection, Command, DataReader и DataAdapter должны поддерживать эти интерфейсы.

Дополнительную информацию о разработке провайдеров данных .NET см. по ссылке msdn.microsoft.com/library/en-us/cpguidnf/html/cpconimplementingnetdataprovider.asp (EN).

Рис. 2 иллюстрирует стек доступа ADO.NET к данным и взаимосвязь ADO.NET с другими технологиями доступа к данным, в частности с ADO и OLE DB. Кроме того, показаны два управляемых провайдера и основные объекты, входящие в модель ADO.NET.

Рис. 2. Стек доступа к данным

Дополнительную информацию об эволюции ADO в ADO.NET см. в статье Introducing ADO+: Data Access Services for the Microsoft .NET Framework, опубликованной в номере MSDN Magazine за ноябрь 2000 г.

Сравнение хранимых процедур и операторов SQL

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

Используйте хранимые процедуры, а не встраиваемые операторы SQL по следующим причинам.

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

Свойства и аргументы конструкторов

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

// Объект Command настраивается через аргументы конструктора
SqlCommand cmd = new SqlCommand( "SELECT * FROM PRODUCTS", conn );

// Предыдущая строка по функциональности эквивалентна следующим
// трем строкам, в которых свойства настраиваются явным образом
sqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = "SELECT * FROM PRODUCTS";

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

Выбор в данном случае определяется личными предпочтениями и стилем кодирования. Однако явное присвоение значений свойствам облегчает понимание кода (особенно если вы не очень хорошо знакомы с объектной моделью ADO.NET) и упрощает отладку.

Управление соединениями с базами данных

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

Управляя соединениями с базами данных и строками подключений, старайтесь:

• обеспечивать масштабируемость приложений за счет совместного использования пула соединений с базой данных несколькими клиентами;
• придерживаться стратегии конфигурируемого и высокопроизводительного пула соединений;
• использовать средства аутентификации Windows при доступе к SQL Сервер;
• избегать олицетворения (impersonation) на промежуточном уровне;
• безопасно хранить строки подключений;
• открывать соединения с базой данных как можно позже, а закрывать — как можно раньше.

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

Создание пула соединений

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

Такие технологии доступа к данным, как ODBC и OLE DB, поддерживают свои разновидности пулов соединений, в той или иной мере допускающих конфигурирование. Оба подхода практически прозрачны для клиентского приложения, работающего с базой данных. Пул соединений OLE DB часто называют сеансовым (session pooling) или ресурсным (resource pooling).

Общее описание создания пулов соединений в MDAC (Microsoft Data Access Components) см. по ссылке msdn.microsoft.com/library/en-us/dnmdac/html/pooling2.asp (EN).

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

• SQL Server .NET Data Provider (EN)
• OLE DB .NET Data Provider (EN)

Поддержка пула соединений в SQL Server .NET Data Provider

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

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

Настройка пула соединений в SQL Server .NET Data Provider

Пул настраивается с помощью набора пар «имя-значение», указываемых в строке подключения. Например, можно настроить, разрешено ли создание пула (по умолчанию — разрешено), указать максимальный и минимальный размер пула , а также время ожидания запроса на открытие соединения, поставленного в очередь. В приведенном ниже примере показана строка подключения, в которой заданы максимальный и минимальный размеры пула.

Server=(local); Integrated Security=SSPI; Database=Northwind;
Max Pool Size=75; Min Pool Size=5

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

Выбор размера пула

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

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

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

Дополнительную информацию о наблюдении за пулами соединений см. в разделе этого документа «Наблюдение за пулами соединений».

Полный список ключевых слов, используемых в строках подключений при создании пула соединения, см. по ссылке msdn.microsoft.com/library/en-us/cpguidnf/html/cpconconnectionpoolingforsqlservernetdataprovider.asp (EN).

Дополнительная информация

При использовании пула соединений в SQL Server .NET Data Provider имейте в виду следующее.

• Соединения включаются в пул по алгоритму строгого соответствия (exact match algorithm) строк подключения. Механизм поддержки пула чувствителен даже к пробелам между парами «имя-значение». Так, следующие две строки подключения приведут к созданию двух разных пулов из-за того, что вторая строка содержит дополнительные пробелы.

  SqlConnection conn = new SqlConnection(
           "Integrated Security=SSPI;Database=Northwind");
  conn.Open(); // Создается пул A
  
  SqlConmection conn = new SqlConnection(
           "Integrated Security=SSPI ; Database=Northwind");
  conn.Open(); // Создается пул B (строка содержит дополнительные
               // пробелы)
  

• В бета-версиях .NET Framework пул соединений всегда отключается при выполнении приложения под отладчиком. Без отладчика пул создается и в отладочном (debug), и в финальном (release) вариантах приложения. В RTM-версии (Released To Manufacture) .NET Framework это ограничение снято, и пул создается во всех случаях.
• Пул соединений делится на несколько специфичных для конкретных транзакций пулов и еще выделяется один пул для соединений, не используемых транзакциями в данный момент. Для потоков, сопоставленных с контекстом определенной транзакции, возвращается соединение из того пула, который включает соединения, участвующие в данной транзакции. Благодаря этому работа с соединениями, используемыми транзакциями, становится транспарентной.

Поддержка пула соединений в OLE DB .NET Data Provider

OLE DB .NET Data Provider поддерживает пулы соединений, обращаясь к соответствующим сервисам механизма поддержки ресурсных пулов в OLE DB. Настройка ресурсного пула возможна несколькими способами:

• использованием строк подключения для настройки, включения и отключения поддержки ресурсного пула соединений;
• через реестр;
• программным конфигурированием ресурсного пула.

Во избежание проблем при развертывании, возникающими в случае настройки пулов через реестр, не пользуйтесь этим способом настройки ресурсного пула OLE DB.

Подробнее о создании ресурсного пула соединений OLE DB см. в MSDN руководство OLE DB Programmer’s Reference (глава 19 OLE DB Services, раздел Resource Pooling).

Управление пулами соединений с помощью объектов пула

Как разработчик для Windows DNA, вы можете отключить создание ресурсного пула OLE DB и/или создание пула соединений ODBC и использовать в качестве пула соединений к базе данных объектный пул COM+. На то могут быть две основных причины:

• размеры пулов и пороговые значения можно настроить явным образом (в COM+ Catalog);
• производительность пула объектов может быть в 2 раза больше производительности стандартного пула.

Однако, так как SQL Server .NET Data Provider работает с пулом соединений на внутреннем уровне, при использовании этого провайдера нет необходимости разрабатывать собственный объектный механизм поддержки пула. Таким образом, вы избежите сложностей, связанных с включением ресурсов в транзакции вручную (manual transaction enlistment).

Если вы используете OLE DB .NET Data Provider, а также хотите добиться удобства настройки и повышения производительности, стоит подумать о поддержке объектного пула COM+. Если в этих целях вы разрабатываете объект пула (pooled object), отключите в OLE DB ресурсный пул и автоматическое включение ресурсов в транзакции (например, указав в строке подключения OLE DB Services=-4). В своей реализации объекта пула вам придется самостоятельно управлять включением ресурсов в транзакции.

Наблюдение за пулами соединений

Для наблюдения за тем, как приложение работает с пулом соединений, можно воспользоваться утилитой Profiler, поставляемой с SQL Server, или оснасткой Performance Monitor в Microsoft Windows 2000.

Для наблюдения за пулом с помощью Profiler выполните следующие действия.

1. Откройте Start | Programs | Microsoft SQL Server | Profiler для запуска Profiler.
2. Выберите File | New | Trace.
3. Укажите параметры соединения и щелкните OK.
4. В диалоговом окне Trace Properties перейдите на вкладку Events.
5. В списке Selected event classes убедитесь, что под Security Audit показываются события Audit Login и Audit Logout. Чтобы не «засорять» трассировочную информацию лишними сведениями, удалите из списка все остальные события.
6. Щелкните Run, чтобы начать трассировку. Вы увидите события Audit Login при установлении соединений, а события Audit Logout — при закрытии соединений.

Для наблюдения за пулом с помощью Performance Monitor выполните следующие действия.

1. Откройте Start | Programs | Administrative Tools | Performance для запуска Performance Monitor.
2. Щелкните правой кнопкой мыши график, показываемый в окне, и выберите Add Counters.
3. В списке Performance object укажите SQL Server: General Statistics.
4. В появившемся списке щелкните User Connections.
5. Щелкните Add, затем — Close.

Управление защитой

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

Аутентификация средствами Windows

При подключении к SQL Server используйте аутентификацию средствами Windows, так как это дает ряд преимуществ.

• Упрощается управление защитой, так как вы имеете дело с одной моделью защиты (предоставляемой Windows), не используя еще и модель защиты SQL Server.
• Имена и пароли пользователей не включаются в строки подключения.
• Имена и пароли пользователей не передаются по сети открытым текстом.
• Повышается безопасность входа в базу данных за счет поддержки сроков действия паролей, минимальной длины пароля и блокировки учетной записи после неоднократных неудачных попыток входа.

Дополнительная информация

При аутентификации доступа к SQL Server средствами Windows учитывайте соображения, изложенные в следующих разделах.

Производительность

Тестирование производительности .NET Beta 2 показало, что при аутентификации средствами Windows на открытие соединения из пула уходит больше времени, чем при аутентификации средствами SQL Server. Однако, хотя аутентификация Windows работает медленнее, снижение производительности незначительно по сравнению со временем, требуемым для выполнения запроса или хранимой процедуры. В результате преимущества аутентификации Windows обычно перевешивают небольшое уменьшение производительности.

Также следует ожидать, что различия в скорости аутентификации средствами Windows и средствами SQL Server при открытии входящих в пул соединений станут менее заметными в RTM-версии .NET Framework.

Избегайте олицетворения на промежуточном уровне

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

Чтобы решить эту проблему, вместо обычных учетных записей, под которыми пользователи входят в Windows, используйте для подключения к SQL Server ограниченный набор учетных записей Windows, каждая из которых соответствует определенной роли.

Попробуйте, например, такой подход.

• Создайте две учетные записи Windows: одну для операций чтения, другую - для операций записи. (Или несколько учетных записей, которые отражают роли, определяемые логикой приложения. Например, вы могли бы задействовать одну учетную запись для пользователей Интернета, а другую - для пользователей, работающих только в локальной сети, и/или администраторов.)
• Сопоставьте каждую учетную запись с ролью, определенной в базе данных SQL Server, и установите для каждой роли необходимые права для доступа к базе данных.
• Перед выполнением какой-либо операции над базой данных определяйте через прикладную логику своего уровня доступа к данным, какую учетную запись Windows следует использовать для доступа к SQL Server.

Используйте в качестве сетевой библиотеки TCP/IP

SQL Server 7.0 и более поздних версий поддерживает аутентификацию средствами Windows для всех сетевых библиотек. Используйте TCP/IP, чтобы добиться выигрыша в возможностях конфигурирования, производительности и масштабируемости. Более подробную информацию о применении TCP/IP см. в разделе «Подключение через брандмауэры» далее в этом документе.

Хранение строк подключения

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

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

Строки подключения к базе данных можно хранить в:

• файле конфигурации приложения, например в файле Web.config веб-приложения ASP.NET;
• UDL-файле (Universal Data Link) (только для OLE DB .NET Data Provider);
• реестре Windows;
• собственном файле;
• COM+ Catalog, используя строки инициализации (только для обслуживаемых компонентов).

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

Безопасное и конфигурируемое решение для Web-приложений ASP.NET - хранение строк подключения в зашифрованном виде в файле Web.config.

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

• Применение конфигурационных XML-файлов приложения

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

  <configuration>
   <appSettings>
    <add key="DBConnStr"
       value="server=(local);Integrated Security=SSPI;database=northwind"/>
   </appSettings>
  </configuration>
  

  ---

  Элемент <appSettings> следует за элементом <configuration>, а не непосредственно за <system.web>.

  ---

  Преимущества

  • Простота в распространении. Строка подключения передается вместе с конфигурационными файлами с помощью обычного средства развертывания xcopy, применяемого в .NET.
  • Простота в программном доступе. Благодаря свойству AppSettings класса ConfigurationSettings считать строку подключения к базе данных в период выполнения приложения очень легко.
  • Поддержка динамического обновления (только в ASP.NET). Если администратор обновляет строку подключения в файле Web.config, данное изменение вступает в силу при следующем обращении к строке подключения — для компонента, не поддерживающего состояния (stateless component), это произойдет скорее всего при следующем обращении клиента к компоненту для выполнения запроса к базе данных.

  Недостатки

  • Проблема с защитой. Хотя DLL интерфейса ISAPI (Internet Server Application Programming Interface) ASP.NET не допускает прямого обращения клиентов к файлам с расширением .config, а для еще большего ограничения доступа можно использовать разрешения файловой системы NTFS, вас все равно может не устроить хранение параметров подключения в виде незашифрованного текста на Web-сервере, взаимодействующем с клиентами. Для большей безопасности храните строки подключения в конфигурационном файле в зашифрованном виде.

  Дополнительная информация

  • Нестандартные (custom), или пользовательские, параметры приложения можно считывать через статическое свойство AppSettings класса System.Configuration.ConfigurationSettings. Это демонстрирует приведенный ниже фрагмент кода, где предполагается, что вы считываете показанный ранее нестандартный ключ DBConnStr:

    using System.Configuration;
    private string GetDBaseConnectionString()
    {
      return ConfigurationSettings.AppSettings["DBConnStr"];
    }
    

  • Дополнительную информацию о конфигурировании приложений .NET Framework см. по ссылке msdn.microsoft.com/library/en-us/cpguidnf/html/cpconconfiguringnetframeworkapplications.asp (EN).

• Применение UDL-файлов

  OLE DB .NET Data Provider позволяет указывать в строках подключений имена UDL-файлов (Universal Data Link). ВЫ можете передавать строку подключения как аргумент конструктора объекта OleDbConnection или присваивать ее свойству ConnectionString этого объекта.

  ---

  SQL Server .NET Data Provider не поддерживает UDL-файлы в своих строках подключения. Таким образом, этот способ годится только при работе с OLE DB .NET Data Provider.

  ---

  При работе с провайдером OLE DB для ссылки на UDL-файл используйте в строке подключения конструкцию File Name=name.udl.

  Преимущества

  • Стандартный подход. Возможно, вы уже используете UDL-файлы для управления строками подключения.

  Недостатки

  • Меньшая производительность. Строки подключения со ссылками на UDL-файлы читаются и анализируются при каждом открытии соединения.
  • Проблема защиты. UDL-файлы хранятся как простой текст. Вы можете защищать их с помощью разрешений NTFS, но тогда возникают те же проблемы, что и с файлами .config.
  • SqlClient не поддерживает UDL-файлы. Этот подход не поддерживается SQL Server .NET Data Provider, применяемым для доступа к SQL Server версии 7.0 и выше.

  Дополнительная информация

  • вы должны обеспечить, чтобы у администраторов был доступ к файлу для чтения/записи, а у учетной записи, под которой запускается приложение, — доступ только для чтения. Рабочий процесс веб-приложения ASP.NET по умолчанию выполняется под учетной записью SYSTEM, но ее можно переопределить через элемент конфигурационного файла компьютера (Machine.config). Кроме того, данную учетную запись можно подменить другой (тоже зарегистрированной в системе) через элемент <identity> файла Web.config.
  • Работая с веб-приложениями, убедитесь, что UDL-файл не находится в виртуальном каталоге, — в противном случае появится потенциальная возможность скачивания этого файла через веб.
  • Дополнительную информацию об этих и других особенностях ASP.NET, связанных с безопасностью, см. по ссылке msdn.microsoft.com/library/en-us/dnbda/html/authaspdotnet.asp (EN).

• Применение реестра Windows

  Для хранения строки подключения можно создать свой раздел в реестре Windows, но это не рекомендуется, так как возникают проблем с развертыванием приложения.

  Преимущества

  • Безопасность. Доступ к определенным разделам реестра можно контролировать через списки управления доступом (access control lists, ACL). Для большей безопасности используйте шифрование.
  • Простота в программном доступе. В .NET есть классы, позволяющие читать строки из реестра.

  Недостатки

  • Проблемы с развертыванием. Соответствующие параметры реестра придется распространять вместе с приложением, что не позволит в полной мере использовать преимущества xcopy.

• Применение файла собственного формата

  Строку подключения можно хранить в файле собственного формата. Но этот способ не дает никаких преимуществ и поэтому не рекомендуется.

  Преимущества

  • Нет.

  Недостатки

  • Дополнительные усилия в программировании. Такой подход требует дополнительных усилий в программировании и создает сложности в поддержке одновременного доступа.
  • Проблема развертывания. Файл придется копировать вместе с остальными файлами приложения ASP.NET. Не помещайте файл в каталог приложения ASP.NET или его подкаталог, чтобы этот файл нельзя было скачать через веб.

• Использование COM+ Catalog

  Строку подключения к базе данных можно хранить в COM+ Catalog — тогда она будет автоматически передаваться вашему объекту через строку инициализации объекта (object construction string). COM+ будет вызывать метод Construct объекта сразу после создания экземпляра объекта, передавая указанную строку инициализации.

  ---

  Этот способ годится только для обслуживаемых компонентов (serviced components). Его стоит рассматривать, только если ваши управляемые компоненты используют другие сервисы, например поддержку распределенных транзакций или объектных пулов.

  ---

  Преимущества

  • Администрирование. Администратор может легко настраивать строку подключения через оснастку Component Services консоли MMC.

  Недостатки

  • Проблемы с безопасностью. COM+ Catalog считается небезопасным хранилищем данных (хотя доступ к нему можно ограничить с помощью ролей COM+), поэтому помещать в него строки в виде незашифрованного текста нельзя.
  • Проблемы с развертыванием. Элементы COM+ Catalog должны распространяться вместе с .NET-приложением. Если вы используете другие сервисы масштаба предприятия, например распределенные транзакции или поддержку объектных пулов, то хранение строки подключения к базе данных в этом каталоге не приводит к дополнительным издержкам при развертывании, поскольку тогда COM+ Catalog все равно нужно развертывать для поддержки этих сервисов.
  • Компоненты должны быть обслуживаемыми. Строки инициализации можно использовать только для обслуживаемых компонентов. Не наследуйте класс своего компонента от ServicedComponent (что делает компонент обслуживаемым) лишь для того, чтобы получить возможность работать со строками инициализации.

  Дополнительная информация

  • Подробнее о том, как настраивать .NET-класс на конструирование объекта (object construction), см. в приложении «Как включить поддержку конструирования объектов .NET-класса».
  • Подробнее о разработке обслуживаемых компонентов см. по ссылке msdn.microsoft.com/library/en-us/cpguidnf/html/cpconwritingservicedcomponents.asp (EN).

Примеры использования соединений

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

• Открывать соединение с базой данных как можно позже.
• Использовать это соединение в течение как можно более короткого периода.
• Закрывать его как можно быстрее. Соединение не возвращается в пул, пока оно не закрыто вызовом метода Close или Dispose. Его также следует закрывать, если вы обнаружили, что оно разорвано. В последнем случае соединение возвращается в пул и помечается как неработоспособное. Компонент, управляющий пулом объектов (object pooler), периодически сканирует пул и ищет объекты, помеченные как неработоспособные.

Чтобы гарантированно закрывать соединение до того, как метод возвращает управление, используйте один из подходов, которые проиллюстрированы двумя примерами кода (см. ниже). В первом применяется блок finally, а во втором — оператор using языка C#, обеспечивающий вызов метода Dispose объекта.

В первом фрагменте кода соединение будет обязательно закрыто в блоке finally. Заметьте, что этот способ работает не только в C#, но и в Visual Basic .NET, так как последний тоже поддерживает структурную обработку исключений (structured exception handling, SEH).

public void DoSomeWork()
{
  SqlConnection conn = new SqlConnection(connectionString);
  SqlCommand cmd = new SqlCommand("CommandProc", conn );
  cmd.CommandType = CommandType.StoredProcedure;

  try
  {
    conn.Open();
    cmd.ExecuteNonQuery();
  }
  catch (Exception e)
  {
    // Обрабатываем и протоколируем ошибку
  }
  finally
  {
    conn.Close();
  }
}

Второй фрагмент кода иллюстрирует альтернативный подход с применением оператора using языка C#. Обратите внимание, что в Visual Basic .NET нет оператора using или его эквивалента.

public void DoSomeWork()
{
  // using гарантирует, что для объекта conn будет вызван
  // Dispose, и соединение будет закрыто.
  using (SqlConnection conn = new SqlConnection(connectionString))
  {
    SqlCommand cmd = new SqlCommand("CommandProc", conn);
    fcmd.CommandType = CommandType.StoredProcedure;
    conn.Open();
    cmd.ExecuteQuery();
  }
}

Этот подход применим и к другим объектам, например к SqlDataReader или OleDbDataReader, которые нужно закрывать перед тем, как в рамках текущего соединения делать что-то еще.

Обработка ошибок

Ошибки ADO.NET генерируются и обрабатываются через нижележащую поддержку SEH - неотъемлемую часть .NET Framework. Благодаря этому ошибки при выполнении кода доступа к данным обрабатываются точно так же, как и ошибки, возникающие в любом другом месте приложения. Исключения обнаруживаются и обрабатываются по стандартному для .NET синтаксису и стандартными приемами.

В этом разделе показывается, как разрабатывать надежный код доступа к данным, и объясняется, как обрабатывать ошибки доступа к данным. Здесь же вы найдете руководство по обработке исключений, специфичных для SQL Server .NET Data Provider.

.NET-исключения

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

Все типы .NET-исключений в конечном счете наследуются от базового класса Exception в пространстве имен System. Провайдеры данных .NET генерируют типы исключений, специфичные для конкретного провайдера. Например, SQL Server .NET Data Provider генерирует объекты SqlException, когда в SQL Server возникает ошибочная ситуация. Аналогично OLE DB .NET Data Provider генерирует исключения типа OleDbException с параметрами, предоставленными нижележащим провайдером OLE DB.

На рис. 3 показана иерархия исключений, генерируемых провайдерами данных .NET. Заметьте, что класс OleDbException наследуется от ExternalException - базового класса для всех исключений COM Interop. Свойство ErrorCode этого объекта содержит COM HRESULT, сгенерированный OLE DB.

Рис 3. Иерархия исключений провайдеров данных .NET

Перехват и обработка .NET-исключений

Для обработки исключений при доступе к данным поместите свой код доступа к данным в блок try и перехватывайте генерируемые исключения в блоках catch с соответствующими фильтрами. Так, в коде доступа к данным, работающем с SQL Server .NET Data Provider, следует перехватывать исключения типа SqlException, как показано в коде, приведенном ниже.

try
{
  // Код доступа к данным
}
catch (SqlException sqlex) // самое специфичное исключение
{
}
catch (Exception ex) // самое универсальное
                     // (наименее специфичное) исключение
{
}

Если у вас несколько блоков catch с разными критериями фильтрации, не забывайте располагать их в порядке от наиболее специфичных к наименее специфичным (наиболее универсальным). Это гарантирует выполнение самого специфичного блока catch.

Ряд свойств класса SqlException предоставляет дополнительную информацию об исключительной ситуации:

• Message - текст, описывающий ошибку;
• Number - номер ошибки, уникально идентифицирующий ее тип;
• State - дополнительная информация о причине возникновения ошибки. Обычно указывает на конкретный случай данной ошибочной ситуации. Например, если в хранимой процедуре есть несколько строк, способных привести к одной и той же ошибке, свойство State позволяет определить конкретное место, где она возникла;
• Errors - набор (collection) с подробными сведениями об ошибках, генерируемых SQL Server. В этот набор всегда входит минимум один объект типа SqlError.

В следующем фрагменте кода показывается, как обрабатывать ошибочные ситуации, возникающие в SQL Server при работе с SQL Server .NET Data Provider.

using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;

// Метод, предоставляемый компонентом DAL (Data Access Layer)
public string GetProductName( int ProductID )
{
  SqlConnection conn = new SqlConnection(
        "server=(local);Integrated Security=SSPI;database=northwind");
  // Весь код доступа к данным помещаем в блок try
  try
  {
    conn.Open();
    SqlCommand cmd = new SqlCommand("LookupProductName", conn );
    cmd.CommandType = CommandType.StoredProcedure;

    cmd.Parameters.Add("@ProductID", ProductID );
    SqlParameter paramPN =
         cmd.Parameters.Add("@ProductName", SqlDbType.VarChar, 40 );
    paramPN.Direction = ParameterDirection.Output;

    cmd.ExecuteNonQuery();
    // Блок finally выполняется до того, как метод возвращает управление
    return paramPN.Value.ToString(); 
  }
  catch (SqlException sqlex)
  {
    // Обрабатываем исключение, возникшее при доступе к данным,
    // и протоколируем сведения о нем
    LogException(sqlex);
    // Включаем текущее исключение в более подходящее внешнее
    // и повторно генерируем исключение
    throw new DALException(
                  "Unknown ProductID: " + ProductID.ToString(), sqlex );
  }
  catch (Exception ex)
  {
    // Обработка универсальных исключений…
    throw ex;
  }
  finally
  {
    conn.Close(); // соединение будет закрыто в любом случае
  }
}

// Вспомогательная процедура, заносящая сведения из SqlException
// в журнал событий приложения
private void LogException( SqlException sqlex )
{
  EventLog el = new EventLog();
  el.Source = "CustomAppLog";
  string strMessage;
  strMessage = "Exception Number : " + sqlex.Number +
               "(" + sqlex.Message + ") has occurred";
  el.WriteEntry( strMessage );

  foreach (SqlError sqle in sqlex.Errors)
  {
    strMessage = "Message: " + sqle.Message +
                 " Number: " + sqle.Number +
                 " Procedure: " + sqle.Procedure +
                 " Server: " + sqle.Server +
                 " Source: " + sqle.Source +
                 " State: " + sqle.State +
                 " Severity: " + sqle.Class +
                 " LineNumber: " + sqle.LineNumber;
    el.WriteEntry( strMessage );
  }
}

В блоке catch для SqlException сначала регистрируются параметры исключения с помощью вспомогательной функции LogException. В ней оператором foreach перебираются специфичные для провайдера объекты из набора Errors, и полученная информация записывается в журнал ошибок. Затем исключение SQL Server включается в исключение типа DALException, имеющее больший смысл для методов, вызывающих GetProductName. Для передачи этого исключения вызывающему методу используется ключевое слово throw.

Дополнительная информация

• Полный список членов класса SqlException см. по ссылке msdn.microsoft.com/library/en-us/cpref/html/frlrfSystemDataSqlClientSqlExceptionMembersTopic.asp (EN).
• Дополнительные сведения о разработке собственных исключений, протоколировании .NET-исключений и включении их в оболочки, а также о различных подходах к передаче исключений см. по ссылке msdn.microsoft.com/library/default.asp?url=/library/en-us/dnbda/html/exceptdotnet.asp (EN).

Генерация ошибок в хранимых процедурах

В Transact-SQL (T-SQL) имеется функция RAISERROR (обратите внимание на регистр букв), позволяющая генерировать нестандартные ошибки и возвращать их клиенту. В случае клиентов ADO.NET провайдер SQL Server .NET Data Provider перехватывает эти ошибки и преобразует их в объекты SqlError.

Самый простой способ использования функции RAISERROR — передать в качестве первого параметра текст сообщения, а затем задать параметры, определяющие уровень значимости ошибки (severity) и состояние:

RAISERROR( 'Unknown Product ID: %s', 16, 1, @ProductID )

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

Дополнительная информация

• Чтобы избежать «зашивки» текста сообщений в код, добавьте свои сообщения в таблицу sysmessages, вызвав системную хранимую процедуру sp_addmessage или воспользовавшись Enterprise Manager в SQL Server. Тогда вы сможете ссылаться на нужное сообщение, передавая его идентификатор функции RAISERROR. Идентификаторы ваших сообщений должны быть больше 50 000:

  RAISERROR( 50001, 16, 1, @ProductID )
  

• Полную информацию о функции RAISERROR ищите по указателю в SQL Server Books Online.

Правильное использование уровней значимости

Тщательно выбирайте уровни значимости (severity levels) для определяемых вами ошибок и учитывайте влияние каждого уровня на работу приложения. Уровни значимости ошибок варьируются в диапазоне от 0 до 25 и указывают тип проблемы, с которой столкнулся SQL Server 2000. В клиентском коде вы можете выяснить уровень значимости ошибки через свойство Class объекта SqlError, принадлежащего набору Errors класса SqlException. В таблице 1 показаны смысл различных уровней значимости и их влияние на работу приложения.

Таблица 1. Уровни значимости ошибок: влияние и смысл

Управление автоматическими транзакциями

При любых ошибках с уровнем значимости выше 10 провайдер SQL Server .NET Data Provider генерирует SqlException. Если компонент участвует в автоматической транзакции (транзакции COM+) и обнаруживает SqlException, он должен «проголосовать» за отмену транзакции. Это может быть сделано автоматически или вручную — в зависимости от того, помечен ли метод атрибутом AutoComplete.

Подробнее об обработке SqlException в контексте автоматических транзакций см. в разделе «Определение результатов транзакций» этого документа.

Получение информационных сообщений

Уровни значимости от 10 и ниже используются для передачи информационных сообщений и не вызывают генерации SqlException.

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

• Создайте обработчик события и подключите его к событию InfoMessage объекта SqlConnection. Делегат этого события показан в следующем фрагменте кода.

  public delegate void SqlInfoMessageEventHandler( object sender,
                                                       SqlInfoMessageEventArgs e );
  

• Данные сообщения доступны через объект SqlInfoMessageEventArgs, передаваемый вашему обработчику события. У этого объекта имеется свойство Errors, содержащее несколько объектов SqlError — по одному на каждое информационное сообщение. Ниже показано, как зарегистрировать обработчик события, протоколирующий информационные сообщения.

  public string GetProductName( int ProductID )
  {
    SqlConnection conn = new SqlConnection(
          "server=(local);Integrated Security=SSPI;database=northwind");
    try
    {
      // Регистрируем обработчик события для информационного сообщения
      conn.InfoMessage += new SqlInfoMessageEventHandler( MessageEventHandler );
      conn.Open();
      // Настраиваем объект команды и выполняем его
      . . .
    }
    catch (SqlException sqlex)
    {
      // Протоколируем и обрабатываем исключение
      . . .
    }
    finally
    {
      conn.Close();
    }
  }
  // Обработчик события для информационного сообщения
  void MessageEventHandler( object sender, SqlInfoMessageEventArgs e )
  {
    foreach( SqlError sqle in e.Errors )
    {
      // Протоколируем содержимое свойств SqlError
      . . .
    }
  }
  

Производительность

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

• Чтение нескольких записей. Считывается набор результатов (result set), затем полученные записи перебираются в цикле.
• Чтение одной записи. Считывается одна запись с заданным первичным ключом.
• Чтение одного поля. Считывается одно поле (item) из заданной записи.
• Проверка существования элемента данных. Проверяется, есть ли запись с заданным первичным ключом. Это не более чем разновидность сценария чтения одного поля, при которой достаточно вернуть простое булево значение.

Чтение нескольких записей

Рис. 4. Типичные сценарии считывания нескольких записей

Сравнение доступных вариантов

Выборка нескольких записей из источника данных возможна следующими способами.

• Использовать объект SqlDataAdapter для генерации DataSet или DataTable.
• Задействовать SqlDataReader для создания потока данных только для чтения в направлении только вперед.
• Применить XmlReader для создания потока XML-данных только для чтения в направлении только вперед.

Выбор между SqlDataReader и DataSet/DataTable — это, по сути, выбор между производительностью и функциональностью. SqlDataReader обеспечивает оптимальную производительность, а DataSet — дополнительную гибкость и функциональность.

Связывание с данными

Все три этих объекта могут выступать в качестве источников данных для элементов управления, связываемых с данными (data-bound controls), но DataSet и DataTable способны работать с более широкой группой элементов управления, чем SqlDataReader. Это объясняется тем, что DataSet и DataTable реализуют интерфейс IListSource (возвращающий IList), тогда как SqlDataReader реализует интерфейс IEnumerable. Некоторые элементы управления Windows Forms поддерживают связывание с данными, если их источники реализуют IList.

Различие между объектами объясняется их разным предназначением. DataSet (включающий в себя DataTable) — это мощная отсоединенная структура, подходящая для работы как с Web-формами, так и с формами Windows Forms. С другой стороны, класс чтения данных (data reader) оптимизирован для Web-приложений, требующих быстрого доступа к данным в направлении только вперед.

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

Передача данных между уровнями приложения

DataSet дает реляционное представление данных, с которым можно работать и как с XML, а также позволяет передавать отсоединенную кэшированную копию данных между уровнями и компонентами приложения. Однако SqlDataReader обеспечивает оптимальную производительность из-за того, что не тратит память и время на создание DataSet. Запомните, что создание объекта DataSet часто влечет за собой создание массы подобъектов вроде DataTable, DataRow и DataColumn и что объекты-наборы служат контейнерами для этих подобъектов.

Применение DataSet

Используйте DataSet, заполняемый объектом SqlDataAdapter, если:

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

Дополнительная информация

Используя SqlDataAdapter для генерации DataSet или DataTable, имейте в виду следующее.

• Явно открывать или закрывать соединение с базой данных не требуется. Метод Fill объекта SqlDataAdapter открывает соединение с базой данных, а перед возвратом управления закрывает его. Если соединение уже открыто, Fill оставляет его открытым.
• Если соединение нужно для каких-то других целей, откройте его перед вызовом метода Fill. Тогда вы избежите лишних открытий/закрытий соединения и получите выигрыш в производительности.
• Хотя один и тот же объект SqlCommand можно использовать повторно, чтобы несколько раз выполнить одну и ту же команду (оператор SQL или хранимую процедуру), не делайте этого для выполнения разных команд.
• Пример кода, показывающий, как с помощью SqlDataAdapter заполнить DataSet или DataTable, см. в приложении: «Использование SqlDataAdapter для чтения нескольких записей».

Применение SqlDataReader

Используйте SqlDataReader, получаемый при вызове метода ExecuteReader объекта SqlCommand, если:

• вы имеете дело с большими объемами данных — слишком большими, чтобы они могли уместиться в одном кэше;
• вам нужно уменьшить объем памяти, занимаемый приложением;
• вы хотите избежать издержек, связанных с созданием объектов при использовании DataSet;
• вы хотите связать с данными элемент управления, который поддерживает источник, реализующий интерфейс IEnumerable;
• вам нужно упростить и оптимизировать доступ к данным;
• вы считываете записи, содержащие поля с большими двоичными объектами (binary large objects, BLOB). SqlDataReader позволяет считывать BLOB-данные с разбиением на удобные для обработки порции. Подробнее об обработке BLOB см. в разделе этого документа «Работа с большими двоичными объектами (BLOB)».

Дополнительная информация

При использовании SqlDataReader имейте в виду следующее.

• Соединение с базой данных остается открытым и не может использоваться в других целях, пока активен класс чтения данных. Вызывайте метод Close объекта SqlDataReader как можно быстрее.
• На одно соединение приходится только один класс чтения.
• вы можете явно закрыть соединение, закончив работу с классом чтения, или увязать срок существования соединения со сроком жизни объекта SqlDataReader, передав методу ExecuteReader значение CommandBehavior.CloseConnection. Этот параметр означает, что соединение должно быть закрыто, как только будет закрыт SqlDataReader.
• При доступе к данным с помощью класса чтения используйте типизированные методы-аксессоры (typed accessor methods) (например, GetInt32 или GetString), если вам известен тип данных поля, — тогда сокращается число преобразований типов, выполняемых при чтении данных поля.
• Чтобы избежать лишней передачи данных от сервера клиенту в том случае, когда вы хотите закрыть класс чтения и отбросить оставшиеся результаты, вызовите метод Cancel объекта команды перед вызовом метода Close класса чтения. Вызов Cancel гарантирует, что результаты будут отброшены на сервере и не попадут к клиенту. Вызов только метода Close класса чтения данных, напротив, приведет к тому, что оставшиеся результаты будут без всякой необходимости переданы с сервера, чтобы опустошить поток данных.
• Если вам нужно получить выходные параметры хранимой процедуры или ее возвращаемое значение и вы используете метод ExecuteReader объекта SqlCommand, обязательно вызовите метод Close класса чтения перед обращением к выходным параметром или возвращаемому значению.
• Пример кода, показывающий, как работать с SqlDataReader, см. в приложении к этому документу: «Использование SqlDataReader для чтения нескольких записей».

Применение XmlReader

Используйте XmlReader, получаемый при вызове метода ExecuteXmlReader объекта SqlCommand, в следующих случаях.

• Считываемые данные обрабатываются как XML, но нужно избежать издержек, связанных с созданием DataSet, и нет необходимости в отсоединенном кэше данных.
• Требуется функциональность блока FOR XML оператора SQL, позволяющая гибко считывать из базы данных XML-фрагменты (т. е. XML-документы без корневого элемента). Такой подход дает возможность, например, указывать точные имена элементов независимо от того, надо ли использовать схему, основанную на элементах или атрибутах (element or attribute-centric schema), должна ли она возвращаться вместе с XML-данными и т. д.

Дополнительная информация

Если вы работаете с XmlReader, учтите следующее:

• Соединение должно оставаться открытым в течение всего времени, пока XmlReader считывает данные. Метод ExecuteXmlReader объекта SqlCommand в настоящее время не поддерживает значение CommandBehavior.CloseConnection, поэтому вы должны явным образом закрывать соединение, закончив работу с классом чтения.
• Пример кода, в котором показывается, как использовать XmlReader, см. в приложении к этому документу «Использование XmlReader для чтения нескольких записей».

Чтение одной записи

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

Сравнение возможных вариантов

Если требуется связать данные с одной записью, считываемой из источника данных, можно использовать SqlDataAdapter для заполнения DataSet или DataTable точно так же, как и при считывании нескольких записей. Однако если функциональность DataSet/DataTable не нужна, вы должны избегать создания этих объектов.

Считывание одной записи возможно следующими способами:

• использование выходных параметров хранимой процедуры;
• применение объекта SqlDataReader.

Оба варианта позволяют обойтись без ненужных издержек, связанных с созданием набора результатов на сервере и DataSet на клиенте. Относительная производительность при этих вариантах зависит от уровня нагрузки и от того, используется ли пул соединений с базой данных. Как показало тестирование, при наличии пула соединений и в условиях высокой нагрузки (более 200 одновременных подключений) хранимая процедура работает примерно на 30% быстрее, чем SqlDataReader.

• Использование выходных параметров хранимой процедуры

  Используйте выходные параметры хранимой процедуры, когда:

  • вам нужно считать одну запись из многоуровневого веб-приложения, в котором включена поддержка пула соединений.

  Дополнительная информация:

  • Пример кода, в котором показывается, как использовать выходные параметры хранимой процедуры, см. в приложении: «Использование выходных параметров хранимой процедуры для чтения одной записи».

• Применение SqlDataReader

  Используйте SqlDataReader, когда:

  • вам требуется считать не только значения данных, но и метаданные. Для получения метаданных поля вызывайте метод GetSchemaTable класса чтения данных;
  • пул соединений не используется. Тогда SqlDataReader является хорошим выбором независимо от нагрузки; тестирование производительности показало, что при 200 подключенных браузерах SqlDataReader обеспечивает примерно на 20% более высокую производительность, чем хранимая процедура.

  Дополнительная информация:

  • Если известно, что запрос вернет только одну запись, то при вызове метода ExecuteReader объекта SqlCommand указывайте значение CommandBehavior.SingleRow. Некоторые провайдеры, например OLE DB .NET Data Provider, используют это значение для оптимизации производительности. Этот провайдер при задании CommandBehavior.SingleRow выполняет связывание с данными через интерфейс IRow (если он доступен), а не через более ресурсоемкий IRowset. При работе с SQL Server .NET Data Provider этот аргумент ни на что не влияет.
  • Используя объект SqlDataReader, всегда считывайте выходные параметры типизированными методами-аксессорами объекта SqlDataReader, например GetString или GetDecimal. Это позволит избежать лишних преобразований типов.
  • Пример кода, в котором показывается, как с помощью объекта SqlDataReader считывать одну запись, см. в приложении «Использование SqlDataReader для чтения одной записи».

Чтение одного поля

В этом случае требуется считать одно поле данных. Например, может потребоваться найти наименование одного продукта по идентификатору или оценку кредитоспособности (credit rating) одного клиента по его имени. Тогда ради чтения одного поля обычно нет необходимости идти на издержки, связанные с применением DataSet или даже DataTable.

Кроме того, вам может понадобиться просто проверить, есть ли в базе данных определенная запись. Например, когда на веб-сайте регистрируется новый пользователь, вы должны проверить, нет ли в базе указанного им имени. Это частный случай чтения одного поля. Здесь достаточно вернуть булево значение.

Сравнение возможных вариантов

Считать одно поле из источника данных можно:

• вызовом метода ExecuteScalar объекта SqlCommand, содержащего хранимую процедуру;
• использованием выходного параметра или возвращаемого значения хранимой процедуры;
• через объект SqlDataReader.

Метод ExecuteScalar возвращает непосредственно поле данных, так как он рассчитан на запросы, возвращающие единственное значение. Этот способ требует меньше кода, чем остальные.

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

Дополнительная информация

• Если запрос, выполняемый через ExecuteQuery, возвращает несколько полей и/или записей, то способы, рассмотренные в этом подразделе, — только первое поле первой записи.
• Пример кода, в котором показывается, как работать с ExecuteScalar, см. в приложении «Использование ExecuteScalar для чтения одного поля».
• Пример кода, иллюстрирующий, как считывать одно поле через выходной параметр или возвращаемое значение хранимой процедуры, см. в приложении «Использование выходного параметра или возвращаемого значения хранимой процедуры для чтения одного поля».
• Пример кода, демонстрирующий, как с помощью объекта SqlDataReader считать одно поле, см. в приложении «Использование SqlDataReader для чтения одного поля».

Подключение через брандмауэры

Интернет-приложения часто приходится настраивать на подключение к SQL Server через брандмауэр (firewall). Так, ключевым элементом архитектуры многих веб-приложений и их брандмауэров является сеть периметра (perimeter network) (также называемая DMZ, или демилитаризованной зоной), используемая для изоляции Web-серверов, взаимодействующих с клиентами, от внутренних сетей.

При подключении к SQL Server через брандмауэр требуется специальная настройка брандмауэра, клиента и сервера. В составе SQL Server поставляются программы Client Network Utility и Server Network Utility, помогающие выполнить эту настройку.

Выбор сетевой библиотеки

Для упрощения настройки при подключении к SQL Server через брандмауэр используйте сетевую библиотеку TCP/IP. При установке SQL Server 2000 она устанавливается по умолчанию. Если вы работаете с одной из предыдущих версий SQL Server, убедитесь, что TCP/IP выбран в качестве сетевой библиотеки по умолчанию и на клиенте, и на сервере. Это можно сделать с помощью Client Network Utility и Server Network Utility соответственно.

Помимо удобства конфигурирования, библиотека TCP/IP обеспечивает следующие преимущества:

• повышенную производительность при работе с большими объемами данных и более высокую масштабируемость;
• исключение дополнительных проблем с безопасностью, возникающих при работе с именованными каналами.

Клиентские и серверные компьютеры нужно настроить под TCP/IP. Поскольку большинство брандмауэров ограничивает набор портов, через которые пропускается трафик, тщательно проанализируйте, какие порты использует SQL Server.

Конфигурирование сервера

По умолчанию экземпляры SQL Server прослушивают порт 1433. Однако именованным экземплярам (named instances) SQL Server 2000 номер порта назначается динамически при первом запуске. Администратор вашей сети скорее всего не захочет открывать диапазон номеров портов на брандмауэре, поэтому, если вы используете именованный экземпляр SQL Server в сети с брандмауэром, настройте с помощью Server Network Utility свой экземпляр на прослушивание определенного порта. Тогда администратор сети настроит брандмауэр так, чтобы тот пропускал трафик на заданный IP-адрес и порт, прослушиваемый экземпляром сервера.

Динамическое обнаружение именованных экземпляров

Если вы изменили номер порта, прослушиваемого SQL Server по умолчанию, настройте клиент на подключение к этому порту. Подробности см. в разделе «Конфигурирование клиента».

Если вы изменили номер порта для экземпляра SQL Server 2000, используемого по умолчанию, учтите, что неудачное изменение конфигурации клиента приведет к ошибке соединения. Если у вас несколько экземпляров SQL Server, используйте последнюю версию стека доступа к данным MDAC (версию 2.6), чтобы для динамического распознавания и согласования по протоколу UDP (User Datagram Protocol) через UDP-порт 1434. Хотя в среде разработки все это может функционировать нормально, вы должны понимать, что на практике брандмауэры обычно блокируют трафик согласования по протоколу UDP.

Чтобы обойти эту ситуацию, всегда настраивайте клиент на подключение к выделенному для него порту на сервере.

Конфигурирование клиента

Для подключения к SQL Server настройте клиент на использование сетевой библиотеки TCP/IP. Кроме того, убедитесь, что эта библиотека на клиентской стороне использует правильный порт назначения.

Использование сетевой библиотеки TCP/IP

Клиент можно настроить с помощью Client Network Utility, поставляемой с SQL Server. В некоторых вариантах установки эта утилита может отсутствовать на клиенте. Тогда настройте клиент на использование библиотеки TCP/IP одним из следующих способов.

• Укажите в строке подключения пару «имя-значение» (Network Library=dbmssocn). Строка dbmssocn нужна для идентификации TCP/IP-сокетов.

   

  ---

  В случае провайдера SQL Server .NET Data Provider параметр сетевой библиотеки dbmssocn используется по умолчанию.

  ---

• Внесите изменения в реестр клиента, чтобы задать TCP/IP как библиотеку по умолчанию. Дополнительную информацию о конфигурировании сетевой библиотеки SQL Server см. по ссылке HOWTO: Change SQL Server Default Network Library Without Using Client Network Utility (Q250550) (EN).

Задание порта

Если ваш экземпляр SQL Server настроен на прослушивание порта, отличного от порта 1433 по умолчанию, вы можете указать номер порта для подключения следующими способами:

• через Client Network Utility;
• задав номер порта в паре «имя-значение» (Server или Data Source), указываемой в строке подключения. Используйте строку следующего формата:

  "Data Source=ServerName,PortNumber"
  

   

  ---

  ServerName может быть IP-адресом или DNS-именем. Для оптимальной производительности указывайте IP-адрес, чтобы не тратить время на просмотр DNS.

  ---

Распределенные транзакции

Если вы занимаетесь разработкой обслуживаемых компонентов, использующих распределенные транзакции COM+ и сервисные функции DTC (Microsoft Distributed Transaction Coordinator), вам может понадобиться настройка брандмауэра на пропуск трафика DTC между разными экземплярами DTC и между DTC и диспетчерами ресурсов (например, SQL Server).

Дополнительную информацию об открытии портов для DTC см. по ссылке INFO: Configuring Microsoft Distributed Transaction Coordinator (DTC) to Work Through a Firewall (EN).

Работа с большими двоичными объектами (BLOB)

В настоящее время многим приложениям приходится иметь дело не только с традиционными символьными и числовыми данными, но и с такими форматами данных, как графика и звук, и даже с еще более сложными типами данных вроде видео. Существует масса разных форматов графики, звука и видео. Однако, с точки зрения хранения данных, все данных таких форматов можно рассматривать как крупные массивы двоичных данных, обычно называемые большими двоичными объекта (Binary Large Objects, BLOB).

В SQL Server для хранения BLOB предназначены типы данных binary, varbinary и image. Несмотря на свое название, BLOB-данные используются для хранения и текстовой информации. Например, вам может потребоваться хранить длинные примечания произвольного размера, относящиеся к определенным записям. Для этих целей предназначены типы данных SQL Server ntext и text.

Вообще говоря, для хранения двоичных данных с размером, меньшим 8 Кб, лучше применять тип данных varbinary, а для хранения двоичных данных большего размера — тип image. В таблице 2 перечислены характеристики каждого из типов данных.

Таблица 2. Характеристики типов данных

Где хранить BLOB

SQL Server версии 7.0 и выше обеспечивает повышенную производительность при работе с BLOB, которые содержатся в базе данных. Одна из причин — увеличение размера страницы базы данных до 8 Кб. Благодаря этому отпадает необходимость хранить текстовые или графические данные размером меньше 8 Кб в отдельной двоичной древовидной структуре страниц. Такие данные теперь можно хранить в одной записи. Таким образом, чтение/запись данных text, ntext или image выполняется так же быстро, как и чтение/запись символьных или двоичных строк. При превышении размера 8 Кб в запись помещается указатель, а сами данные приходится размещать в узлах древовидной структуры страниц, что неизбежно снижает производительность.

Дополнительную информацию о том, как добиться, чтобы данные типов text, ntext и image хранились в одной записи, см. в разделе “Using Text and Image Data” в SQL Server Books Online.

Широко используемый альтернативный подход к работе с BLOB-данными заключается в том, что они помещаются в файловую систему, а в поле базы данных хранится указатель на соответствующий файл (лучше всего подходит URL-ссылка). В версиях SQL Server до 7.0 хранение BLOB-данных в файловой системе — вне базы данных — может повысить производительность.

Однако благодаря усовершенствованной поддержке BLOB в SQL Server 2000 в сочетании с поддержкой чтения и записи BLOB в ADO.NET предпочтительным подходом является хранение BLOB в самой базе данных.

Преимущества хранения BLOB в базе данных

Хранение BLOB в базе данных дает следующие преимущества.

• Проще синхронизировать BLOB-данные с остальными полями записи.
• Резервное копирование BLOB выполняется вместе с резервным копированием всей базы данных. А наличие единственной системы хранения упрощает администрирование.
• вы можете обращаться к BLOB через XML, поддерживаемый SQL Server 2000. При этом возвращается закодированное в формате base 64 представление данных в потоке XML.
• Над полями, содержащими символьные данные постоянной или переменной длины (в том числе в формате Unicode), можно выполнять операции полнотекстового поиска (Full Text Search, FTS) SQL Server. Кроме того, можно выполнять FTS-операции над форматированными текстовыми данными, содержащимися в полях типа image, например над документами Microsoft Word или Microsoft Excel.

Запись BLOB в базу данных

В следующем фрагменте кода показывается, как использовать ADO.NET для записи двоичных данных, считываемых из файла, в поле SQL Server типа image.

public void StorePicture( string filename )
{
  // Считываем файл в байтовый массив
  FileStream fs = new FileStream( filename, FileMode.Open, FileAccess.Read );
  byte[] imageData = new Byte[fs.Length];
  fs.Read( imageData, 0, (int)fs.Length );
  fs.Close();

  SqlConnection conn = new SqlConnection("");
  SqlCommand cmd = new SqlCommand("StorePicture", conn);
  cmd.CommandType = CommandType.StoredProcedure;
  cmd.Parameters.Add("@filename", filename );
  cmd.Parameters["@filename"].Direction = ParameterDirection.Input;
  cmd.Parameters.Add("@blobdata", SqlDbType.Image);
  cmd.Parameters["@blobdata"].Direction = ParameterDirection.Input;
  // Записываем байтовый массив в поле типа image
  cmd.Parameters["@blobdata"].Value = imageData;
  try
  {
    conn.Open();
    cmd.ExecuteNonQuery();
  }
  catch
  {
    throw;
  }
  finally
  {
    conn.Close();
  }
}

Чтение BLOB из базы данных

При создании объекта SqlDataReader методом ExecuteReader для чтения записей, содержащих BLOB, указывайте значение CommandBehavior.SequentialAccess. Без этого значения класс чтения будет передавать данные с сервера на клиент по одной записи единовременно. Если запись содержит поле BLOB, это может привести к расходу большого объема памяти. Значение CommandBehavior.SequentialAccess обеспечивает более тонкий контроль над чтением, так как BLOB-данные извлекаются только при ссылке на них (например, при вызове метода GetBytes, позволяющего задавать число считываемых байтов). Все это показано в следующем фрагменте кода.

// Предполагается, что команда и соединение уже подготовлены.
// Эта команда извлекает оператором SELECT из таблицы поле типа IMAGE.
conn.Open();
SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess);
reader.Read();
// Получаем размер данных поля типа image;
// в качестве параметра - массива байтов передаем null
long bytesize = reader.GetBytes(0, 0, null, 0, 0);
// Выделяем память под массив байтов, предназначенный для хранения
// данных поля
byte[] imageData = new byte[bytesize];
long bytesread = 0;
int curpos = 0;
while (bytesread < bytesize)
{
  // chunkSize - произвольное значение, определяемое приложением
  bytesread += reader.GetBytes(0, curpos, imageData, curpos, chunkSize);
  curpos += chunkSize;
}
// Теперь байтовый массив imageData содержит поле BLOB

Транзакции

Практически всем коммерческим приложениям, изменяющим данные, нужна поддержка транзакций. Транзакции гарантируют целостность состояния системы в рамках одного или нескольких источников данных. Это реализуется за счет общеизвестных свойств транзакции ACID: атомарности (atomicity), целостности (consistency), изоляции (isolation) и отказоустойчивости (durability).

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

• остаток товара уменьшается в соответствии с заказанным количеством;
• кредит покупателя уменьшается на сумму заказа;
• в базу данных заказов добавляется новый заказ.

Эти три операции должны выполняться атомарно — как единое целое. Возможны два варианта: либо все операции выполняются успешно, либо ни одна из них не выполняется. Любой другой вариант означал бы нарушение целостности данных. Транзакции гарантируют соблюдение принципа «все или ничего» и ряд других возможностей.

Дополнительную базовую информацию по основам обработки транзакций см. по ссылке msdn.microsoft.com/library/en-us/cpguidnf/html/cpcontransactionprocessingfundamentals.asp (EN).

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

• Транзакции вручную. Код, использующий поддержку транзакций ADO.NET или Transact-SQL, пишется соответственно или в компонентах, или в хранимых процедурах.
• Автоматические транзакции (транзакции COM+). вы добавляете в .NET-классы декларативные атрибуты (declarative attributes), в которых указываете требования объектов к транзакциям в период выполнения. Эта модель позволяет легко настроить несколько компонентов на работу в рамках одной и той же транзакции.

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

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

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

Выбор модели транзакций

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

• Используйте транзакции, только когда вам нужны блокировки для выполнения группы операций и введение в действие правил ACID.
• Выполняйте транзакции как можно быстрее, чтобы свести к минимуму время, в течение которого в базе данных действуют блокировки.
• Никогда не возлагайте на клиента управление сроком действия транзакции.
• Не используйте транзакции для отдельных SQL-операторов. SQL Server автоматически выполняет каждый оператор в рамках индивидуальной транзакции.

Автоматические транзакции и транзакции вручную

Хотя автоматические транзакции несколько упрощают модель программирования — особенно, когда изменения в базе данных выполняются несколькими компонентами, — локальные транзакции вручную всегда значительно быстрее, так как не требуют взаимодействия с Microsoft DTC. Это верно (хотя и в меньшей степени), даже если автоматические транзакции выполняются с одним локальным диспетчером ресурсов (например, SQL Server), так как при локальных транзакциях вручную не требуется межпроцессного взаимодействия (interprocess communication, IPC) с DTC.

Используйте ручные транзакции, когда:

• транзакции выполняются над одной базой данных.

Используйте автоматические транзакции, когда:

• одна транзакция охватывает несколько удаленных баз данных;
• одна транзакция требует участия нескольких диспетчеров ресурсов, например базы данных и ресурсов MSMQ (Message Queuing) в Windows 2000.

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

Применение транзакций вручную

При применении транзакций вручную код, использующий поддержку транзакций ADO.NET или Transact-SQL, пишется соответственно в компонентах или хранимых процедурах. В большинстве случаев следует выбирать управление транзакциями в хранимых процедурах, поскольку такой подход великолепно обеспечивает инкапсуляцию и дает производительность, сравнимую с производительностью транзакций ADO.NET.

• Выполнение транзакций вручную в ADO.NET

  ADO.NET поддерживает объект транзакции, который можно использовать, чтобы начать новую транзакцию, а затем зафиксировать (commit) или откатить (roll back) ее. Объект транзакции сопоставляется с соединением, с базой данных; для получения этого объекта вызывается метод BeginTransaction объекта соединения. Вызов данного метода не означает, что последующие команды начнут неявно выполняться в контексте транзакции. Вы должны явно связать каждую команду с транзакцией, установив свойство Transaction команды. С объектом транзакции можно связать несколько команд, тем самым объединив несколько операций над базой данных в одну транзакцию.

  Пример использования поддержки транзакций в ADO.NET см. в приложении «Программирование ручных транзакций ADO.NET».

  Дополнительная информация

  • По умолчанию в ручных транзакциях ADO.NET используется уровень изоляции Read Committed. Это означает, что в базе данных на время чтения из нее устанавливаются разделяемые блокировки (shared locks), но данные можно изменять до завершения транзакции. При таком уровне изоляции возможно чтение одной и той же записи по-разному (non-repeatable reads), или появление фантомным данных (phantom data). Уровень изоляции транзакции можно изменить, присвоив свойству IsolationLevel ее объекта одно из значений перечислимого IsolationLevel.
  • Тщательно выбирайте подходящий уровень изоляции транзакций. Здесь приходится идти на компромисс между целостностью данных и производительностью. Самый высокий уровень изоляции (Serialized) обеспечивает абсолютную целостность данных за счет снижения общей производительности системы. Более низкие уровни изоляции могут повысить масштабируемость приложения, но при этом возможны ошибки, связанные с нарушением целостности данных. В приложениях, главным образом читающих и лишь изредка записывающих данные, лучше использовать более низкие уровни изоляции.
  • Ценную информацию о выборе подходящего уровня изоляции транзакций можно найти в книге Кэйлина Дилэни (Kalen Delaney) Inside SQL Server 2000, Microsoft Press.

• Выполнение ручных транзакций в хранимых процедурах

  В хранимых процедурах можно напрямую управлять ручными транзакциями с помощью операторов Transact-SQL. Например, можно выполнить транзакционные операции в одной хранимой процедуре, используя такие операторы Transact-SQL, как BEGIN TRANSACTION, END TRANSACTION и ROLLBACK TRANSACTION.

  Дополнительная информация

  • При необходимости в хранимой процедуре можно управлять уровнем изоляции транзакции с помощью оператора SET TRANSACTION ISOLATION LEVEL. По умолчанию в SQL Server используется уровень изоляции Read Committed. Подробнее об уровнях изоляции транзакций SQL Server см. в SQL Server Books Online (раздел Accessing and Changing Relation Data, подраздел Isolation Levels).
  • Пример кода, иллюстрирующий, как выполнять изменения в рамках транзакции с помощью транзакционных операторов языка Transact-SQL, см. в приложении «Выполнение транзакций с помощью Transact-SQL».

Применение автоматических транзакций

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

Автоматические транзакции опираются на COM+-поддержку распределенных транзакций и, следовательно, могут использоваться только обслуживаемыми компонентами, т. е. компонентами, производными от класса ServicedComponent.

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

• Наследуйте свой класс от класса ServicedComponent в пространстве имен System.EnterpriseServices.
• Определите требования класса к транзакциям с помощью атрибута Transaction. Значение, выбираемое из перечислимого TransactionOption, определяет, как класс конфигурируется в COM+ Catalog. Этот атрибуте позволяет указать и такие свойства, как уровень изоляции транзакций и время ожидания.
• Чтобы не приходилось явно определять результат транзакции (фиксацию или откат), пометьте методы атрибутом AutoComplete. Если метод с таким атрибутом генерирует исключение, транзакция автоматически откатывается. Имейте в виду, что у вас сохраняется возможность явно указать результат транзакции (transaction outcome). Детали см. в разделе этого документа «Определение результатов транзакций».

Дополнительная информация

• Дополнительную информацию об автоматических транзакциях COM+ см. в документации Platform SDK в разделе Automatic Transactions Through COM+.
• Пример транзакционного .NET-класса см. в приложении «Программирование транзакционного класса .NET».

Настройка уровней изоляции транзакций

В COM+ 1.0, т. е. в версии, предоставляемой Windows 2000, используется уровень изоляции транзакций Serialized. Это обеспечивает высшую степень изоляции, но за счет производительности. Общая производительность системы снижается, так как задействованные в транзакции диспетчеры ресурсов (обычно базы данных) должны поддерживать на время транзакции блокировки и по чтению, и по записи. В течение этого времени остальные транзакции блокируются, что может значительно ухудшить масштабируемость приложения.

В COM+ версии 1.5, поставляемой с Microsoft Windows .NET, допускается настройка уровня изоляции транзакций в COM+ Catalog индивидуально для каждого компонента. Уровень изоляции определяется параметром, сопоставленным с корневым компонентом, участвующим в транзакции. Кроме того, у внутренних подкомпонентов, являющихся частью той же транзакции, не должен быть более высокий уровень изоляции, чем у корневого компонента. Иначе при создании экземпляров подкомпонентов будут возникать ошибки.

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

[Transaction(TransactionOption.Supported, Isolation=TransactionIsolationLevel.ReadCommitted)]
public class Account : ServicedComponent
{
  . . .
}

Дополнительная информация

Подробнее о настраиваемых уровнях изоляции транзакций и других усовершенствованиях COM+ в Windows .NET см. статью Windows XP: Make Your Components More Robust with COM+ 1.5 Innovations в журнале MSDN Magazine за август 2001 г.

Определение результатов транзакций

Результат автоматической транзакции регулируется флагом отмены транзакции (transaction abort flag), а также флагами целостности (consistent flags) в контексте всех транзакционных компонентов одного потока транзакции. Результат транзакции определяется в момент деактивации корневого компонента, когда управление возвращается вызывающему методу (см. рис. 5, который иллюстрирует классическую банковскую транзакцию по переводу денег).

Рис. 5. Поток транзакции и контекст

Результат транзакции определяется, когда корневой объект (в данном примере — объект Transfer) деактивируется и управление возвращается методу клиента. Если какой-либо флаг целостности в любом контексте установлен в false или если флаг отмены транзакции установлен в true, соответствующая физическая транзакция DTC отменяется.

вы можете управлять результатом транзакции из .NET-объекта одним из двух способов.

• Пометить методы атрибутом AutoComplete, что позволит .NET автоматически управлять результатом транзакции в соответствии с вашими требованиями. При наличии этого атрибута — в случае, если метод генерирует исключение, — флагу целостности автоматически присваивается false (что в конечном счете приводит к отмене транзакции). Если метод завершается без генерации исключения, флагу целостности присваивается true, и это указывает на готовность компонента к фиксации транзакции. Но фиксация транзакции не гарантируется, так как она зависит от того, как проголосуют другие объекты, относящиеся к тому же потоку транзакции.
• Вызывать статический метод SetComplete или SetAbort класса ContextUtil — при этом флагу целостности присваивается соответственно true или false.

Ошибки SQL Server с уровнями значимости выше 10 приводят к тому, что управляемый провайдер данных генерирует исключения типа SqlException. Если ваш метод перехватывает и обрабатывает исключение, вы должны вручную указать, что транзакцию нужно отменить, или, когда метод помечен как [AutoComplete], передать исключение вызывающему методу.

• Методы [AutoComplete]

  Для методов с атрибутом AutoComplete выполните одно из следующих действий.

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

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

  В следующем фрагменте кода перехватывается SqlException, которое затем передается непосредственно вызывающему методу. В конечном счете, транзакция отменяется, так как при деактивации объекта его флаг целостности автоматически приравнивается false.

  [AutoComplete]
  void SomeMethod()
  {
    try
    {
      // Открываем соединение и выполняем операции над базой данных
      . . .
    }
    catch (SqlException sqlex )
    {
      LogException( sqlex ); // протоколируем параметры исключения
      throw;                 // снова генерируем то же исключение, чтобы
                             // флаг целостности получил значение false
    }
    finally
    {
      // Закрываем соединение с базой данных
      . . .
    }
  }
  

• Методы без [AutoComplete]

  В случае методов без атрибута AutoComplete вы должны:

  • вызывать ContextUtil.SetAbort в блоке catch, чтобы при исключении проголосовать за отмену транзакции. При этом флаг целостности получит значение false;
  • вызывать ContextUtil.SetComplete, если исключения не было, и проголосовать таким образом за фиксацию транзакции. При этом флаг целостности получит значение true, что совпадает с его значением по умолчанию.

  Этот подход иллюстрирует следующий фрагмент кода.

  void SomeOtherMethod()
  {
    try
    {
      // Открываем соединение и выполняем операции над базой данных
      . . .
      ContextUtil.SetComplete(); // Вручную голосуем за фиксацию транзакции
    }
    catch (SqlException sqlex)
    {
      LogException( sqlex );   // протоколируем параметры исключения
      ContextUtil.SetAbort();  // вручную голосуем за отмену транзакции
      // Теперь исключение обработано, и нет необходимости
      // передавать его вызывающему методу
    }
    finally
    {
      // Закрываем соединение с базой данных
      . . .
    }
  }
  

   

  ---

  При наличии нескольких блоков catch проще вызвать ContextUtil.SetAbort в начале метода и поместить вызов ContextUtil.SetComplete в конец блока try. Тогда не придется вызывать метод ContextUtil.SetAbort в каждом блоке catch. Значение, присваиваемое флагу целостности при вызове этих методов, играет роль, только когда ваш метод возвращает управление.

  ---

  Всегда передавайте исключения (или исключения-оболочки) обратно по стеку вызовов, так как тогда вызывающий код получает информацию о том, что транзакция закончится неудачно, и может предпринять соответствующие действия. Например, в примере с переводом денег компонент Transfer мог бы отказаться от кредитной операции, зная, что дебетную операцию выполнить не удалось.

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

Загрузка данных порциями

Загрузка данных порциями (data paging) — типичное требование, предъявляемое в распределенных приложениях. Например, пользователь может получить длинный список книг, который нет смысла показывать целиком за один раз; пользователь предпочел бы выполнять такие привычные действия, как просмотр следующей/предыдущей страницы списка или переход к его началу/концу.

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

Сравнение возможных вариантов

Для загрузки данных порциями выберите один из следующих вариантов.

• Вызов метода Fill объекта SqlDataAdapter для записи в DataSet диапазона результатов.
• Применение ADO через COM Interop и использование серверного курсора (server-side cursor).
• Загрузка данных порциями вручную с помощью хранимых процедур.

Какой из вариантов окажется для вас наилучшим, зависит от:

• требований к масштабируемости;
• требований к производительности;
• полосы пропускания сети;
• объема памяти и мощности сервера базы данных;
• объема памяти и мощности сервера промежуточного уровня;
• числа возвращаемых записей, которые вы хотите разбить на порции;
• размера нужной вам порции данных.

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

Ниже рассматриваются все варианты.

Применение SqlDataAdapter

Как уже говорилось, объект SqlDataAdapter используется для заполнения DataSet информацией из базы данных. Один из его перегруженных методов Fill (показанный ниже) принимает два целочисленных индекса.

public int Fill(
   DataSet dataSet,
   int startRecord,
   int maxRecords,
   string srcTable
);

Здесь startRecord — индекс первой записи (отсчет от нуля), а maxRecords — число записей, которые копируются в новый DataSet, начиная со startRecord.

На внутреннем уровне SqlDataAdapter использует SqlDataReader для выполнения запроса и возвращения результатов. SqlDataAdapter считывает результаты и создает DataSet, содержащий считанные SqlDataReader данные. SqlDataAdapter копирует в новый DataSet все результаты, а не только maxRecords записей, начиная со startRecord, и отбрасывает все ненужные. Таким образом, потенциально возможна передача клиенту через сеть большого объема лишних данных. В этом и заключается основной недостаток такого подхода.

Например, если имеется 1000 записей, из которых вас интересуют записи с 900-й по 950-ю, то первые 899 записей все равно считываются через сеть, а затем отбрасываются. Такие издержки, возможно, окажутся минимальными при наборах результатов малого размера, но для больших объемов данных издержки могут оказаться очень велики.

Применение ADO

Еще один вариант реализации загрузки порциями — использование ADO средствами COM. Основная идея такого подхода — получить доступ к серверным курсорам, предоставляемым ADO-объектом Recordset. Вы присваиваете свойству CursorLocation объекта Recordset значение adUseServer. Если ваш провайдер OLE DB поддерживает этот параметр (как, например, SQLOLEDB), это приведет к использованию курсора на серверной стороне. И вы с помощью этого курсора сможете перейти прямо к начальной записи, не передавая все записи клиенту через сеть.

У этого подхода два основных недостатка:

• В большинстве случаев требуется преобразовывать записи, возвращаемые в объекте Recordset, в DataSet для использования в управляемом коде клиента. Хотя OleDbDataAdapter содержит перегруженный метод Fill, позволяющий преобразовать ADO-объект Recordset в DataSet, этот метод не позволяет задавать начальную и конечную запись. Единственное, что можно сделать, — перейти на начальную запись в объекте Recordset, перебрать все записи и вручную скопировать данные в созданный вами DataSet. Однако затрачиваемые на это усилия и, в частности, издержки вызовов COM Interop могут перевесить преимущества меньшего сетевого трафика — особенно в случае DataSet небольшого размера.
• Соединение и серверный курсор остаются открытыми, пока вы извлекаете с сервера нужные вам данные. Обычно курсоры, открываемые и поддерживаемые на сервере базы данных, — ресурсы дорогостоящие. И хотя они позволяют увеличить производительность, есть вероятность снижения масштабируемости из-за неэкономного расходования ценных ресурсов сервера в течение длительных периодов.

Реализация вручную

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

Загрузка данных порциями из таблиц с уникальным ключом

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

CREATE PROCEDURE GetProductsPaged
@lastProductID int,
@pageSize int
AS
SET ROWCOUNT @pageSize
SELECT *
FROM Products
WHERE [стандартное условие поиска]
AND ProductID > @lastProductID
ORDER BY [сортировка, при которой ProductID по-прежнему монотонно возрастает]
GO

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

Загрузка данных порциями из таблиц без уникального ключа

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

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

Рассмотрим следующую таблицу.

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

CREATE PROCEDURE RetrieveDataPaged
@lastKey char(40),
@pageSize int
AS
SET ROWCOUNT @pageSize
SELECT
Col1, Col2, Col3, Col4, Col1+Col2+Col3 As KeyField
FROM SampleTable
WHERE [стандартное условие поиска]
AND Col1+Col2+Col3 > @lastKey
ORDER BY Col1 ASC, Col2 ASC, Col3 ASC
GO

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

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

Приложение

• Как включить поддержку конструирования объектов .NET-класса
• Использование SqlDataAdapter для чтения нескольких записей
• Использование SqlDataReader для чтения нескольких записей
• Использование XmlReader для чтения нескольких записей
• Использование выходных параметров хранимой процедуры для чтения одной записи
• Использование SqlDataReader для чтения одной записи
• Использование ExecuteScalar для чтения одного поля
• Использование возвращаемого значения или выходного параметра хранимой процедуры для чтения одного поля
• Использование SqlDataReader для чтения одного поля
• Программирование ручных транзакций ADO.NET
• Выполнение транзакций с помощью Transact-SQL
• Программирование транзакционного .NET-класса