один. Предисловие:
Хранимая процедура — это набор операторов SQL, предназначенных для выполнения определенных функций, которые компилируются и сохраняются в базе данных. Пользователь выполняет хранимую процедуру, указывая ее имя и параметры (если хранимая процедура имеет параметры). Хранимые процедуры являются важным объектом базы данных, и любое хорошо спроектированное приложение базы данных должно использовать хранимые процедуры. В общем, хранимые процедуры имеют следующие преимущества:
◆ Хранимые процедуры позволяют программировать стандартные компоненты
◆ Хранимые процедуры могут обеспечить более высокую скорость выполнения
◆ Хранимые процедуры могут уменьшить сетевой трафик
◆ Хранимые процедуры могут быть полностью использованы в качестве механизма безопасности
Автор этой статьи представит вам о применении хранимых процедур в приложениях баз данных .NET и о том, как их использовать в сочетании с объектом SqlDataAdapter, объектом DataSet и т. д. в ADO.NET для повышения общей производительности приложений баз данных .NET.
два. Системные требования:
Средства разработки: Visual Studio.NET.
Система управления базой данных: SQL Server 2000 (включая базу данных Pubs, используемую в примере программы).
3. Создайте простую хранимую процедуру.
Здесь я покажу вам, как использовать Visual Studio.NET IDE для создания хранимой процедуры. Создавать хранимые процедуры с помощью Visual Studio.NET IDE очень просто и интуитивно понятно. Перейдя к базе данных Pubs в обозревателе серверов и развернув узел, вы обнаружите различные объекты базы данных, включая хранимые процедуры, как показано на рисунке 1. Показывать.
Щелкните правой кнопкой мыши узел хранимой процедуры, чтобы открыть меню, содержащее команду «Новая хранимая процедура». После создания новой хранимой процедуры в окне редактирования кода в IDE появится показанный ниже шаблон кода:
СОЗДАТЬ ПРОЦЕДУРУ dbo.StoredProcedure1
/*
(
Тип данных @parameter1 = значение по умолчанию,
@parameter2 тип данных ВЫХОД)
*/
КАК
/* ВКЛЮЧАЕМ NOCOUNT */
ВОЗВРАЩАТЬСЯ
Приведенный выше шаблон кода соответствует упрощенным правилам синтаксиса для создания хранимых процедур. Полные правила синтаксиса следующие:
CREATE PROC [ EDURE ] имя_процедуры [ ;
[ { @parameter data_type }
[ РАЗНЫЕ ] [ = по умолчанию ] [ ВЫХОД ]
] [ ,...н ]
[ С
{ ПЕРЕКОМПИЛИРОВАНИЕ | ШИФРОВАНИЕ | ПЕРЕКОМПИЛИРОВАНИЕ, ШИФРОВАНИЕ } ]
[ ДЛЯ РЕПЛИКАЦИИ ]
AS sql_statement [ ...n ]
Из-за ограничений по объему здесь не будет представлено значение каждого параметра. Заинтересованные читатели могут обратиться к информации о системе управления базами данных SQL Server 2000.
Ниже я кратко представлю каждый грамматический компонент в этом шаблоне кода. Инструкция CREATE PROCEDURE создает хранимую процедуру, за которой следует имя хранимой процедуры. Компоненты в "/*...*/" являются параметрами хранимой процедуры, которые могут включать входные и выходные параметры. Содержимое, следующее за ключевым словом AS, представляет собой основной текст хранимой процедуры, который представляет собой любое количество операторов SQL любого типа, содержащихся в хранимой процедуре. Ключевое слово RETURN указывает на конец хранимой процедуры и может возвращать вызывающему объекту целочисленное значение состояния. Давайте создадим простую хранимую процедуру без параметров и будем ее использовать:
CREATE PROCEDURE dbo.up_GetPublisherInfo.
КАК
ВЫБЕРИТЕ pub_id, pub_name, город, штат, страну
ОТ издателей
ВОЗВРАЩАТЬСЯ
После создания указанной выше хранимой процедуры сохраните ее. После сохранения узел, соответствующий хранимой процедуре, появится в обозревателе серверов. Также обратите внимание, что ключевое слово CREATE в окне редактирования кода изменилось на ключевое слово ALTER, которое используется для изменения любых существующих хранимых процедур. Чтобы запустить указанную выше хранимую процедуру, просто щелкните ее узел и выберите «Выполнить хранимую процедуру» в контекстном меню, вызываемом правой кнопкой мыши. Результат операции следующий:
Четыре. Создайте хранимую процедуру с параметрами.
Выше мы создали простую хранимую процедуру без параметров, но в реальных приложениях часто используется множество хранимых процедур с параметрами. Хранимые процедуры с параметрами обычно используются для обновления или вставки данных. Ниже мы можем использовать тот же метод операции для создания хранимой процедуры с параметрами:
СОЗДАТЬ ПРОЦЕДУРУ dbo.up_UpdatePublisherInfo
(
@pub_id символ (4),
@pub_name varchar (40),
@cityvarchar(20),
@state символ (2),
@country варчар (30)
)
КАК
ОБНОВЛЕНИЕ издателей
SET pub_name = @pub_name, city = @city, state = @state,
страна = @country
ГДЕ (pub_id = @pub_id)
ВОЗВРАЩАТЬСЯ
В приведенном выше коде для создания хранимой процедуры мы объявляем локальные переменные-параметры хранимой процедуры, добавляя знак «@» перед именем. Мы также объявляем тип каждого параметра и определяем значение направления каждого параметра. is. Указывает, является ли параметр типом ввода, типом вывода, типом ввода-вывода или типом возвращаемого значения. Пользователи могут вызывать хранимую процедуру, используя соответствующее имя хранимой процедуры и правильные и допустимые параметры. Кроме того, вы можете добавить выходные параметры к параметрам, используя ключевое слово OUTPUT. Для получения информации о конкретных методах см. приведенные выше правила синтаксиса. Выходные параметры могут возвращать соответствующую информацию вызывающему объекту.
Вышеупомянутая хранимая процедура может обновить соответствующую информацию об издателе в таблице издателей. Вы можете выполнить ее, щелкнув узел хранимой процедуры и выбрав «Выполнить хранимую процедуру» во всплывающем меню, вызываемом правой кнопкой мыши. После выполнения в IDE появится диалоговое окно для ввода информации об издателе (как показано на рисунке 3). Заполните правильную и действительную информацию об обновлении в этом диалоговом окне. Обратите внимание, что значение pub_id должно существовать в исходной таблице, а затем нажмите кнопку «ОК», чтобы обновить данные.
пять. Создайте приложение базы данных с помощью простой хранимой процедуры.
Далее мы будем использовать указанную выше хранимую процедуру без параметров для создания приложения базы данных, которое также использует объект SqlDataAdapter и объект DataSet в ADO.NET. Объект SqlDataAdapter служит мостом между базой данных SQL Server и объектом DataSet, соединяющим их. Объект SqlDataAdapter содержит два часто используемых метода: метод Fill() и метод Update(). Метод Fill() может получить соответствующие данные из базы данных и заполнить их в объект DataSet, а метод Update(), как следует из названия, обновляет набор данных. Прежде чем вызывать метод Fill(), мы должны установить свойство SelectCommand объекта SqlDataAdapter, который на самом деле является объектом SqlCommand. Свойство SelectCommand содержит допустимые инструкции SQL и может получать соответствующие данные из базы данных и заполнять их в объект DataSet.
Сначала мы создаем приложение Windows Forms, язык программирования — C#. После создания нового проекта в Visual Studio.NET добавьте в проект новый класс — класс Publishers, который инкапсулирует бизнес-логику подключения к серверной базе данных и получения объекта набора данных. Шаги следующие:
1. Добавьте необходимые ссылки на пространство имен: используя System.Data.SqlClient
2; Добавьте в этот класс следующие необходимые переменные:
Private SqlConnection cnPubs;
частный SqlCommand cmdPubs;
частные SqlDataAdapter daPubs;
частный набор данных dsPubs;
3． В конструкторе этого класса завершите подключение к серверной базе данных и получите объект SqlDataAdapter и другую бизнес-логику:
public Publishers()
{
пытаться
{
//Создаем объект подключения к базе данных
cnPubs = new SqlConnection("server=localhost;integrated Security=true;database=pubs");
//Создаем объект SqlCommand и указываем тип его команды как хранимую процедуру
cmdPubs = новый SqlCommand();
cmdPubs.Connection = cnPubs;
cmdPubs.ТипКоманды = ТипКоманды.СохраненнаяПроцедура;
cmdPubs.CommandText = "up_GetPublisherInfo";
//Создаем объект SqlDataAdapter и присваиваем его свойству SelectCommand указанный выше объект SqlCommand.
daPubs = новый SqlDataAdapter ();
daPubs.SelectCommand = cmdPubs;
//Создаем объект DataSet
dsPubs = новый DataSet();
}
улов (Исключение) {}
}
4. Наконец, для этого класса предусмотрен метод GetPublisherInfo(), который заполняет объект DataSet объектом SqlDataAdapter и возвращает заполненный объект DataSet. Метод заключается в следующем (стоит отметить, что объект SqlDataAdapter неявно открывает соединение с базой данных и неявно открыть соединение с базой данных после получения данных. Закрыть соединение формально, что означает, что объект DataSet работает в режиме без подключения, и когда вы явно открываете соединение с базой данных и получаете данные, объект SqlDataAdapter не будет закрывать соединение):
общедоступный набор данных GetPublisherInfo().
{
// Вызов метода Fill() объекта SqlDataAdapter и возврат объекта набора данных
daPubs.Fill(dsPubs);
вернуть dsPubs;
}
Завершив разработку класса Publishers, мы добавляем в главную форму элемент управления DataGrid и используем его для отображения данных в объекте DataSet. Сначала добавьте в класс основной формы следующие переменные-члены:
частные пабы издателей;
частный набор данных ds;
После этого измените конструктор класса основной формы следующим образом:
общедоступная форма1()
{
//
// Требуется для поддержки конструктора Windows Forms
//
ИнициализироватьКомпонент();
//
// TODO: добавьте любой код конструктора после вызова InitializeComponent
// пабы = новые издатели();
ds = pubs.GetPublisherInfo();
dataGrid1.DataSource = ds.Tables[0];
}
Таким образом, как только приложение будет запущено, соответствующие данные, полученные из базы данных Pubs с помощью указанной выше хранимой процедуры без параметров, будут отображены в элементе управления DataGrid основной формы. Схема работы программы выглядит следующим образом:
6. Создайте приложение базы данных с хранимой процедурой с параметрами.
Выше мы создали приложение с хранимой процедурой без параметров, а теперь создадим более сложное приложение базы данных. В реальных приложениях баз данных нам часто необходимо получать данные и обновлять, вставлять или удалять данные. В то же время нам нужно использовать хранимые процедуры с параметрами. В то же время при использовании объекта SqlDataAdapter мы будем вызывать его Update(). метод. Метод Update() автоматически выполнит соответствующую операцию на основе изменений в каждой записи объекта DataTable в объекте DataSet. Объект SqlDataAdapter также содержит такие свойства, как UpdateCommand, InsertCommand, DeleteCommand и т. д. Эти свойства на самом деле являются объектами SqlCommand. Метод Update() выбирает соответствующие атрибуты в зависимости от типа операции.
При использовании хранимых процедур с параметрами для создания приложений баз данных мы обычно используем класс SqlParameter, который инкапсулирует различные свойства и методы, связанные с параметрами Sql. К свойствам относятся имя параметра, SqlDBType, направление, размер, значение, SourceColumn и SourceVersion и т. д. Среди них параметрName, SqlDBType, Direction, Size и другие атрибуты используются для соответствия параметрам, определенным в хранимой процедуре. Например, объект SqlParameter, определенный ниже, используется для сопоставления параметра «@pub_id » в ранее определенной хранимой процедуре up_UpdatePublisherInfo.
SqlParameter updParam = new SqlParameter( "@pub_id", SqlDbType.Char, 4 );
В приведенном выше определении, хотя атрибут Direction не указан явно, его значение по умолчанию — Input, поэтому оно соответствует нашим потребностям. А если свойство Direction объекта SqlParameter имеет значение InputOutput, Output или ReturnValue, то его свойство Direction должно быть четко указано. Например, в следующем коде четко указано, что свойство Direction объекта SqlParameter имеет значение Output.
oParam.Direction = ПараметрDirection.Output;
Свойство SourceColumn используется для сопоставления объекта DataColumn в объекте DataTable. Это сопоставление может неявно импортировать требуемый объект SqlParameter при вызове метода Update() для обновления объекта DataTable. Если это свойство не объявлено при определении, необходимо явно указать свойство SourceColumn объекта SqlParameter в своем коде.
Значением по умолчанию свойства SourceVersion является текущее значение в соответствующем поле объекта DataRow, которое является значением, которое необходимо обновить в базе данных. Разумеется, свойство SourceVersion также может указывать на исходное значение в соответствующем поле объекта DataRow, то есть на исходное значение, полученное из базы данных. В системе обработки транзакций базы данных вопрос синхронизации данных очень важен. Давайте создадим хранимую процедуру, которая сможет обнаруживать синхронизацию данных.
СОЗДАТЬ ПРОЦЕДУРУ dbo.up_UpdatePublisherName
(
@pub_id символ(4),
@pub_name varchar(40),
@Original_pub_name varchar(40)
)
КАК
если существует (выберите pub_id
от издателей
где (pub_id = @pub_id) И (pub_name = @Original_pub_name))
Начинать
ОБНОВЛЕНИЕ издателей SET pub_name = @pub_name
ГДЕ (pub_id = @pub_id)
Конец
ВОЗВРАЩАТЬСЯ
Затем мы вызываем хранимую процедуру в приведенном выше приложении, чтобы обновить имя издателя. Во-первых, улучшите его класс бизнес-логики — класс Publishers на основе исходного приложения:
1. Добавьте новый объект SqlCommand, который можно использовать как свойство UpdateCommand объекта SqlDataAdapter:
частная SqlCommand cmdUpdPubs;
2. Обновите функцию-конструктор Publishers() этого класса, добавив следующее:
// Создаем еще один объект SqlCommand, который ссылается на хранимую процедуру, обновляющую имя издателя.
cmdUpdPubs = новый SqlCommand ();
cmdUpdPubs.Connection = cnPubs;
cmdUpdPubs.ТипКоманды = ТипКоманды.СохраненнаяПроцедура;
cmdUpdPubs.CommandText = "up_UpdatePublisherName";
//Добавляем необходимые параметры в указанный выше объект SqlCommand
cmdUpdPubs.Parameters.Add( "@pub_id", SqlDbType.Char, 4, "pub_id" );
cmdUpdPubs.Parameters.Add( "@pub_name", SqlDbType.VarChar, 40, "pub_name");
SqlParameter updParam = новый SqlParameter
("@Original_pub_name", SqlDbType.VarChar, 40, "pub_name" );
updParam.SourceVersion = DataRowVersion.Original;
cmdUpdPubs.Parameters.Add(updParam);
3． Укажите свойство UpdateCommand объекта SqlDataAdapter как объект SqlCommand, определенный выше:
daPubs.UpdateCommand = cmdUpdPubs;
4. Добавьте метод UpdatePublisherName():
public void UpdatePublisherName (DataSet dsChanges)
{
// Обновляем все изменения
daPubs.Update(dsChanges);
}
После завершения работы класса бизнес-логики приложения добавьте кнопку с именем «Обновить набор данных» в главную форму и добавьте функцию ответа на событие кнопки следующим образом:
Private void button1_Click(object sender, System.EventArgs e) { if ( ds.HasChanges() ) { pubs.UpdatePublisherName( ds.GetChanges() ); ds = pubs.GetPublisherInfo() } );
На данный момент класс бизнес-логики приложения и класс основной формы обновлены. Теперь приложение может обновлять соответствующий контент в базе данных в соответствии с изменениями пользователя.
Семь. Описание:
Эта статья знакомит вас с базовыми знаниями о хранимых процедурах и о том, как комбинировать объекты SqlDataAdapter, объекты DataSet и т. д. для создания управляемых данными приложений в приложениях баз данных .NET. В этой статье мы использовали два типа хранимых процедур: один — простая хранимая процедура без параметров, которую относительно легко использовать; другой — хранимая процедура с параметрами, и вам нужно вызвать этот тип хранимой процедуры. в объект SqlParameter. В то же время нетрудно обнаружить, что инкапсуляция бизнес-логики обновления данных в хранимой процедуре является хорошим методом проектирования, который может улучшить управляемость, масштабируемость и безопасность базы данных приложения. Аналогично, бизнес-логика вставки и удаления данных может быть инкапсулирована в хранимые процедуры и аналогичным образом использоваться в приложениях. Наконец, я надеюсь, что эта статья всем поможет.