Хранимые процедуры являются еще одной формой выполнения запросов к базе данных. Но по сравнению с ранее рассмотренными запросами, которые посылаются из приложения базе данных, хранимые процедуры определяются на сервере и предоставляют большую производительность и являются более безопасными.
Объект SqlCommand имеет встроенную поддержку хранимых процедур. В частности у него определено свойство CommandType, которое в качестве значения принимает значение из перечисления System.Data.CommandType. И значение System.Data.CommandType.StoredProcedure как раз указывает, что будет использоваться хранимая процедура.
Но чтобы использовать хранимые процедуры, нам надо их вначале создать. Для этого перейдем в SQL Server Management Studio к нашей базе данных usersdb, раскроем ее узел и далее выберем Programmability->Stored Procedures. Нажмем на этот узел правой кнопкой мыши и в контекстном меню выберем пункт Stored Procedure…:
В центральной части программы открывает код процедуры, который генерируется по умолчанию. Заменим этот код следующим:
|
1 2 3 4 5 6 7 8 9 |
CREATE PROCEDURE [dbo].[sp_InsertUser] @name nvarchar(50), @age int AS INSERT INTO Users (Name, Age) VALUES (@name, @age) SELECT SCOPE_IDENTITY() GO |
Эта процедура выполняет добавление данных. После выражения CREATE PROCEDURE идет название процедуры. Процедура называется “sp_InsertUser”, и по этому названию мы ее будем вызывать в коде C#. Название процедуры может быть любое.
Процедура имеет два входных параметра: @name и @age. Через эти параметры будут передаваться значения для имени и возраста пользователя. В самом теле процедуры после выражения AS идет стандартное sql-выражение INSERT, которое выполняет добавление данных. И в конце с помощью выражения SELECT возвращается результат. Выражение SCOPE_IDENTITY()возвращает id добавленной записи, поэтому на выходе из процедуры мы получим id новой записи. И завершается процедура ключевым словом GO.
И затем нажмем на кнопку Execute. После этого в базу данных добавляется хранимая процедура.
Подобным образом добавим еще одну процедуру, которая будет возвращать объекты:
|
1 2 3 4 |
CREATE PROCEDURE [dbo].[sp_GetUsers] AS SELECT * FROM Users GO |
И также для ее добавления нажмем на кнопку Execute.
Теперь перейдем к коду C# и определим следующую программу:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 |
class Program { static string connectionString = @"Data Source=.\SQLEXPRESS;Initial Catalog=usersdb;Integrated Security=True"; static void Main(string[] args) { Console.Write("Введите имя пользователя:"); string name = Console.ReadLine(); Console.Write("Введите возраст пользователя:"); int age = Int32.Parse(Console.ReadLine()); AddUser(name, age); Console.WriteLine(); GetUsers(); Console.Read(); } // добавление пользователя private static void AddUser(string name, int age) { // название процедуры string sqlExpression = "sp_InsertUser"; using (SqlConnection connection = new SqlConnection(connectionString)) { connection.Open(); SqlCommand command = new SqlCommand(sqlExpression, connection); // указываем, что команда представляет хранимую процедуру command.CommandType = System.Data.CommandType.StoredProcedure; // параметр для ввода имени SqlParameter nameParam = new SqlParameter { ParameterName = "@name", Value = name }; // добавляем параметр command.Parameters.Add(nameParam); // параметр для ввода возраста SqlParameter ageParam = new SqlParameter { ParameterName = "@age", Value = age }; command.Parameters.Add(ageParam); var result = command.ExecuteScalar(); // если нам не надо возвращать id //var result = command.ExecuteNonQuery(); Console.WriteLine("Id добавленного объекта: {0}", result); } } // вывод всех пользователей private static void GetUsers() { // название процедуры string sqlExpression = "sp_GetUsers"; using (SqlConnection connection = new SqlConnection(connectionString)) { connection.Open(); SqlCommand command = new SqlCommand(sqlExpression, connection); // указываем, что команда представляет хранимую процедуру command.CommandType = System.Data.CommandType.StoredProcedure; var reader = command.ExecuteReader(); if (reader.HasRows) { Console.WriteLine("{0}\t{1}\t{2}", reader.GetName(0), reader.GetName(1), reader.GetName(2)); while (reader.Read()) { int id = reader.GetInt32(0); string name = reader.GetString(1); int age = reader.GetInt32(2); Console.WriteLine("{0} \t{1} \t{2}", id, name, age); } } reader.Close(); } } } |
Для упрощения кода обращения к процедурам здесь вынесены в отдельные методы. В методе AddUser вызывается процедура sp_InsertUser. Ее название передается в конструктор объекта SqlCommand также, как и обычное sql-выражение. И с помощью выражения command.CommandType = System.Data.CommandType.StoredProcedure устанавливается, что это выражение система будет рассматривать как хранимую процедуру.
Поскольку процедура получает данные через параметры, то соответственно нам надо определить эти параметры с помощью объектов SqlParameter. Ему передается название параметра и значение. Названия параметров должны соответствовать тем названиям, которые мы определили в коде процедуры.
С помощью метода command.Parameters.Add() параметры добавляются к процедуре. И затем происходит выполнение.
Так как в коде процедуры добавления мы определили возвращение id новой записи, то есть возвращение скалярного значения, то для выполнения команды и его получения мы можем использовать метод ExecuteScalar(). Но мы также можем использовать и метод ExecuteNonOuery(), только он вернет количество добавленных записей, а не id.
В случае второго метода все еще проще: объекту команды просто передается название процедуры, и так как процедура фактически выполняет выражение SELECT и возвращает набор данных, то для выполнения команды мы можем использовать метод ExecuteReader(). И с помощью ридера получить все данные.
Запустим программу и введем какие-либо данные на добавление: