Объекты Command в ADO.NET (такие как OleDbCommand и SqlCommand ) являются центральным аспектом стратегии доступа к базам данных .NET. При правильном использовании они обеспечивают отличную производительность и безопасность.
Но для того, чтобы воспользоваться этими преимуществами, вы должны сначала предоставить .NET довольно много информации о запросе, который нужно выполнить, и точных параметрах, которые будут использоваться. Для этой конфигурации вы можете использовать любой из нескольких параметров, включая сам конструктор форм .NET IDE (например, когда вы добавляете объект OleDbCommand или SqlCommand непосредственно в форму) или код, как в:
// Instantiate a new OleDbCommand object OleDbCommand cmdQuery = new OleDbCommand();
// Настраиваем фактическую строку запроса и устанавливаем тип (Text vs. StoredProcedure)
cmdQuery.CommandText = «SELECT * FROM Customer WHERE Id = @ CustId»;
cmdQuery.CommandType = CommandType.Text;
// Настраиваем любые параметры (переменные), которые нужно передать в запрос
cmdQuery.Parameters.Add («@ CustId», OleDbType.Integer);
Оба эти метода имеют небольшой недостаток. Жесткое программирование конфигурации Command в программе означает, что каждый раз, когда вам нужно внести малейшее изменение (например, изменить имя объекта базы данных, увеличить максимальную длину строкового параметра, исправить ошибку в JOIN и т. Д.), Вы должны изменить ваш код, перекомпилировать и заново развернуть — процесс, который не следует воспринимать легкомысленно.
Еще один вариант — сохранить информацию о конфигурации всех ваших запросов в файле конфигурации или базе данных параметров и использовать эту информацию во время выполнения, а не жестко задавать точные свойства вашего запроса во время разработки. Таким образом, можно отделить запросы к базе данных от реального приложения, сохраняя при этом преимущества строгой типизации, обеспечиваемые правильной Command объектов Command . Это позволяет гибко изменять определения запросов и сразу же вступать в силу этих изменений без шага перекомпиляции или повторного развертывания. И, благодаря сочетанию очень простого синтаксиса XML и процедуры синтаксического анализа, эта опция может быть частью вашего инструментария.
Синтаксис XML
После многих лет написания пользовательских файлов конфигурации для моих приложений я наконец понял (как и большая часть остального мира), что для этой цели XML предоставляет гораздо больше функциональности при гораздо меньших усилиях. Синтаксис, который я выбрал для конфигураций запросов, довольно прост. Сначала я указываю фактический текст (или имя) запроса и его тип (для которого я использую значения «Текст» вместо «SP» или «StoredProcedure») и, возможно, значение времени ожидания (указывается в секундах):
<query command='' type='' timeout='' />
Далее следует одно или несколько необязательных описаний параметров:
<param name='' type='' length='' default='' direction='' />
Технически, вся информация выше является необязательной, но на практике строка конфигурации будет бесполезной без определенных частей информации. В исходном описании запроса требуется тег «command», а для описания параметра всегда должны указываться теги «name» и «type».
Итак, как этот синтаксис работает на практике? Предположим, у вас есть хранимая процедура MS SQL Server с определением, например:
GetCustomerList @StoreId int, @CustomerType varchar(50)
Представьте, что эта хранимая процедура находится в базе данных Northwind. Ваша строка конфигурации XML будет выглядеть примерно так:
<query command='Northwind.dbo.GetCustomerList' type='SP'> <param name='retval' type='int' direction='return' /> <param name='StoreId' type='int' /> <param name='CustomerType' type='varchar' length='50' /> </query>
Обратите внимание, что я указал полное имя хранимой процедуры (включая базу данных и владельца) — в качестве легкого повышения производительности я стараюсь всегда делать это с помощью вызовов хранимых процедур (это экономит SQL Server усилия на внутреннем поиске именно там, где процедура проживает). Я также включил возвращаемое значение процедуры в качестве параметра, который может вам понадобиться или не понадобиться в вашем приложении. В отличие от других (входных) параметров в этом запросе, вы не можете установить значение «retval», но вы сможете прочитать его значение после выполнения запроса.
Если вам нужно было переопределить время ожидания ADO.NET для вашей хранимой процедуры (которое по умолчанию составляет 30 секунд), вы можете изменить начальную часть конфигурации запроса следующим образом:
<query command='Northwind.dbo.GetCustomerList' type='SP' timeout='300'>
Это установит время ожидания для запроса в 300 секунд, то есть 5 минут.
Для базового текстового запроса, то есть всего, что не является хранимой процедурой, просто вставьте весь запрос в атрибут «команда», например:
<query command='SELECT * FROM Transactions WHERE TransType NOT IN ("Sales", "Service")' type='Text' />
Если вам нужно, вы можете включить параметры в этот тип запроса, такие как:
<query command='SELECT * FROM Transactions WHERE TransType=@TransType' type='Text'> <param name='TransType' type='varchar' length='50' default='Sales' /> </query>
Когда запрос выполняется, вы можете передать значение параметра «TransType», которое вы хотите подставить в запрос. Параметр имеет значение по умолчанию «Продажи», поэтому, если вы не укажете значение позже в своем коде, оно будет использовано вместо этого.
Вот довольно нестандартный пример, предоставленный на случай, если вам когда-нибудь понадобится выполнить пакет операторов (этот пример работает в MS SQL Server), объявление переменных и т. Д .:
<query command=" IF @StoreId = 0 SET @BatchDesc = '_NoStore_'; ELSE SET @BatchDesc = CAST(@StoreId AS varchar(25)) + '-'; SET @BatchDesc = @BatchDesc + CONVERT(char(10), GETDATE(), 101);" type='Text'> <param name='StoreId' type='int' /> <param name='BatchDesc' type='varchar' length='255' direction='output' /> </query>
Этот запрос будет принимать один параметр («StoreId») и давать выходной параметр («BatchDesc»), который объединяет текущую дату с сервера базы данных со входным значением «StoreId» (или «_NoStore_», если «StoreId» равен 0) в одну строку. Конечно, это странный пример, но идея в том, что любой запрос (или группа запросов), который вы можете написать самостоятельно, включая логику, может быть инкапсулирован в этом формате.
Следует отметить, что, поскольку строка конфигурации запроса будет анализироваться как XML, символы < и > могут доставить вам немного хлопот; Вы можете обойти это, просто зашифровав их как lt; и gt; соответственно (как и в HTML). Итак, вместо того, чтобы писать это:
<query command='SELECT * FROM Customer WHERE Id < 0' type='Text' />
Вы бы написали:
<query command='SELECT * FROM Customer WHERE Id < 0' type='Text' />
Разбор XML в объект OleDbCommand
Учитывая формат XML, описанный выше для определений запросов, остается только написать синтаксический анализатор, который преобразует эту информацию в полностью настроенный объект OleDbCommand в вашем реальном программном обеспечении. Парсер, который я опишу ниже, написан на C # (хотя должно быть просто преобразовать код в другие языки .Net, такие как VB.NET) и возвращает объект OleDbCommand . Если вы имеете дело в первую очередь с MS SQL Server и хотите использовать вместо этого объект SqlCommand , продолжайте читать — я скоро к этому вернусь. OleDbCommand для синтаксического анализатора является строка XML, как описано выше, а возвращаемым значением является экземпляр и настроенный OleDbCommand :
public static OleDbCommand cmdGetCommandFromXML(string sXmlFragment)
Объявив функцию static , вы сможете вызывать ее без фактического создания объекта любого вида. Внутри функции первое, что вам нужно сделать, это объявить переменную OleDbCommand будет служить целью конфигурации, которую вы собираетесь создать:
OleDbCommand cmdResult = new OleDbCommand();
Затем вам нужно открыть программу чтения XML для определения вашего запроса — это даст вам простой способ циклически просматривать все атрибуты и параметры. Классы XmlDocument и XmlNodeReader (находятся в System.Xml ) предоставляют простой способ справиться с этим, хотя для них требуется немного более правильно сформированная строка XML, чем в нашем первоначальном определении запроса. К счастью, достаточно просто заключить оригинальный XML в любой дополнительный тег:
XmlDocument xmlDoc = new XmlDocument(); xmlDoc.LoadXml("<commanddetail>" + sXmlFragment + "</commanddetail>"); XmlNodeReader reader = new XmlNodeReader(xmlDoc);
Класс XmlNodeReader предоставляет метод Read() который в сочетании с простым циклом while и несколькими инструкциями case позволяет нам циклически проходить через узлы нашего XML, а именно <query /> и <param /> информация. Базовый цикл будет выглядеть примерно так:
while (reader.Read()) { switch (reader.Name.ToLower()) { case "query": break; case "param": break; default: break; } }
Теперь у вас есть простая конструкция для разбора всей строки XML на разные теги <query /> и <param /> , но как вы обрабатываете фактическую информацию, содержащуюся в каждом из них? XmlNodeReader предоставляет два свойства / метода, AttributeCount и MoveToAttribute , которые помогут в этом.
Свойство AttributeCount сообщает, сколько атрибутов в текущем узле; Например, <query command='Q' type='SP' /> имеет два атрибута: command и type . MoveToAttribute позволит вам продвинуть XmlNodeReader вместе в XML к этому определенному атрибуту и получить значение (я). Для <query /> мы знаем, что нам нужно обработать, самое большее, теги command , type и timeout , чтобы раздел кода стал:
case "query": for (int i = 0; i < reader.AttributeCount; i++) { reader.MoveToAttribute(i); switch (reader.Name.ToLower()) { case "command": cmdResult.CommandText = reader.Value; break; case "type": cmdResult.CommandType = (reader.Value.ToString().ToLower() == "text") ? CommandType.Text : CommandType.StoredProcedure; break; case "timeout": cmdResult.CommandTimeout = Int32.Parse(reader.Value.ToString()); break; } } break;
Обработка информации <param /> немного сложнее, в основном из-за того, что есть больше атрибутов для учета ( name , type , length , default и direction ). А для атрибутов type и direction значения в нашем входящем XML должны быть сопоставлены с фактическими константами в .NET (например, тип int должен быть преобразован в значение константы OleDbType.Integer , в то время как направление return должно интерпретироваться как ParameterDirection.ReturnValue ). В следующем разделе кода показаны основы для обработки этого:
case "param": OleDbParameter curParm = new OleDbParameter(); for (int i = 0; i < reader.AttributeCount; i++) { reader.MoveToAttribute(i); switch (reader.Name.ToLower()) { case "name": curParm.ParameterName = "@" + reader.Value.ToString(); break; case "type": // Convert the string representation of this data type to // an actual OleDb type constant switch (reader.Value.ToLower()) { case "int": curParm.OleDbType = OleDbType.Integer; break; case "tinyint": curParm.OleDbType = OleDbType.TinyInt; break; case "varchar": curParm.OleDbType = OleDbType.VarChar; break; default: break; } break; case "length": curParm.Size = Int32.Parse(reader.Value.ToString()); break; case "default": curParm.Value = reader.Value; break; case "direction": // Convert the string representation of this value // into an actual ParameterDirection constant switch (reader.Value.ToLower()) { case "return": curParm.Direction = ParameterDirection.ReturnValue; break; case "output": curParm.Direction = ParameterDirection.Output; break; case "inputoutput": curParm.Direction = ParameterDirection.InputOutput; break; default: break; } break; default: break; } } cmdResult.Parameters.Add(curParm); break;
Чтобы сэкономить место, я не указал каждый из возможных типов данных, которые могут обрабатываться OleDbCommand . Но, используя приведенные выше примеры для int , tinyint и varchar качестве шаблонов, вы сможете увидеть, как легко будет расширить код для обработки полного набора значений OleDbType .
SqlCommand vs. OleDbCommand
Если вы работаете в среде MS SQL Server и хотите получить максимальную производительность от доступа к базе данных ADO.NET, то вам лучше использовать объект SqlCommand , а не OleDbCommand . Взять процедуру синтаксического анализа, представленную выше, и создать альтернативную версию, которая настроит объект SqlCommand , довольно легко. Фактически, после того, как вы измените типы OleDbCommand на SqlCommand , вы можете в основном выполнить поиск и замену в остальной части кода, чтобы изменить Ole на Sql . После этого единственное, что вам нужно сделать, это переписать переключатель type для узла <param /> так, чтобы он использовал действительные значения SqlDbType вместо OleDbType (например, SqlDbType.Int вместо OleDbType.Integer ).
Пример использования
Что ж, здесь рассказывается о том, как создавать определения XML-запросов и как их анализировать, но вы, возможно, все еще задаетесь вопросом, как на самом деле реализовать это на практике. Как я упоминал ранее, одним из вариантов является сохранение определений запросов в базе данных и наличие одного запроса, жестко запрограммированного в вашем приложении, для их извлечения во время выполнения. Альтернативный вариант — использовать файл web.config или app.config для вашего .NET Web или настольного приложения для хранения этой информации. Например, рассмотрим следующее определение запроса:
<query command='SELECT * FROM Customer WHERE Id = @CustId' type='Text'> <param name='CustId' type='int' /> </query>
Учитывая это, вы можете сделать следующее дополнение к <appSettings> в файле * .config для представления запроса (предварительно выбрав имя, по которому можно на него сослаться, в данном случае, GetCustomer ):
<?xml version="1.0" encoding="utf-8"?> <configuration> <appSettings> <add key="GetCustomer" value=" <query command='SELECT * FROM Customer WHERE Id = @CustId' type='Text'> <param name='CustId' type='int' /> </query> " /> </appSettings> </configuration>
Обратите внимание на преобразование всех < и > в lt; и gt; соответственно. Встраивание строки XML в то, что само по себе является документом XML, иногда может быть немного сложнее. Фактически, если ваш запрос на самом деле использует в тексте «меньше» или «больше чем», вам придется кодировать его еще дальше: < стал бы &lt; и > станет &gt; , Учитывая эту трудность, если вы решите использовать метод файла * .config, вы можете написать служебное приложение для преобразования между закодированным XML и стандартным форматом.
Теперь, чтобы использовать эту информацию из файла * .config во время выполнения, вы можете использовать объект System.Configuration.AppSettingsReader чтобы сначала получить строку конфигурации XML, используя имя, которое вы изначально выбрали для представления определения запроса ( GetCustomer ) , Затем вызовите класс синтаксического анализа, который мы создали выше для настройки объекта Command , например:
// Retrieve the XML query configuration string System.Configuration.AppSettingsReader rdr = new System.Configuration.AppSettingsReader(); string sQueryDef = (string) rdr.GetValue("GetCustomer", System.Type.GetType("System.String"));
// Создание и настройка объекта OleDbCommand
OleDbCommand cmdQuery = OleDbDataUtils.cmdGetCommandFromXML (sQueryDef);
// Устанавливаем значение любого параметра (ов), например:
cmdQuery.Parameters [«@ CustId»]. Значение = 10;
// Установить конфигурацию базы данных и использовать объект cmdQuery для заполнения
// DataReader или DataSet и т. д.
Вывод
Когда речь идет о приложениях, управляемых базой данных, то, как вы встраиваете запросы в свое программное обеспечение, может оказать большое влияние на то, насколько быстро вы сможете адаптироваться к изменениям. В скомпилированных приложениях это особенно верно. Каждый раз, когда обнаруживается недостаток, вносится изменение в базу данных или даже обнаруживается просто улучшение, программное обеспечение должно быть настроено, скомпилировано и развернуто. Если вам повезло использовать систему баз данных, которая поддерживает хранимые процедуры, тогда ваша проблема значительно уменьшается — но не устраняется! В этом случае единственное время, которое необходимо для компиляции и развертывания программного обеспечения, — это когда имя хранимой процедуры или определение параметра изменяются.
Гибкость и простота обслуживания вашего приложения могут быть улучшены путем отделения запросов к базе данных от остальной части кода — и это может быть достигнуто без значительного ущерба для производительности или безопасности. Какой бы метод вы ни выбрали для хранения вашей информации о конфигурации (будь то в файле конфигурации или в базе данных настроек), использование синтаксиса и парсера, аналогичных описанному здесь, означает, что для многих модификаций потребуется лишь небольшое изменение информации о конфигурации. и, возможно, перезапуск вашего приложения. Это равносильно более легкому обслуживанию и долгосрочной экономии времени — на что, я сомневаюсь, многие разработчики будут жаловаться!