Приветствую всех, сегодня рассмотрим примеры как использовать хранимые процедуры в ado.net
Но для начала нам необходимо разобраться с созданием и выполнением параметризированных запросов с использование коллекции Parameters объекта SqlCommand.
|
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 |
static void Main(string[] args) { var conStr = @"Data Source=.\SQLEXPRESS; Initial Catalog=ShopDB; Integrated Security=True;"; // создание строки подключения var commandStr = "SELECT * FROM Customers WHERE CustomerNo = @CustomerNo;"; // строка с запросом Console.WriteLine("Enter customer ID"); var customerNo = Console.ReadLine(); // получение ID клиента от пользователя SqlConnection connection = new SqlConnection(conStr); // создание подключения SqlCommand cmd = new SqlCommand(commandStr, connection); // создание команды cmd.Parameters.AddWithValue("CustomerNo", customerNo); // добавление параметра в коллекцию параметров команды connection.Open(); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { for (int i = 0; i < reader.FieldCount; i++) Console.WriteLine("{0}: {1}", reader.GetName(i), reader[i]); Console.WriteLine(new string('-',20)); } reader.Close(); connection.Close(); } |
Использование выходных параметров в параметризированных запросах:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
static void Main(string[] args) { string conStr = @"Data Source=.\SQLEXPRESS; Initial Catalog=ShopDB; Integrated Security=True;"; // создание строки подключения SqlConnection connection = new SqlConnection(conStr); SqlCommand cmd = new SqlCommand("SET @Parameter = 2;", connection); SqlParameter parameter = cmd.Parameters.Add(new SqlParameter("Parameter", System.Data.SqlDbType.Int)); parameter.Direction = System.Data.ParameterDirection.Output; // указание направления параметра connection.Open(); cmd.ExecuteNonQuery(); Console.WriteLine("Parameter value: " + parameter.Value); // вывод на экран значения параметра после выполнения запроса connection.Close(); } |
Вот и подошли, к вызовы хранимой процедуры с использованием команды EXECUTE T-SQL
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
static void Main(string[] args) { // код хранимой процедуры selectEmp: CREATE proc dbo.selectEmp // as select * from dbo.Employees string conStr = @"Data Source=.\SQLEXPRESS; Initial Catalog=ShopDB; Integrated Security=True;"; // создание строки подключения SqlConnection connection = new SqlConnection(conStr); SqlCommand cmd = new SqlCommand("EXECUTE selectEmp", connection); // создание команды, выполняющей хранимую процедуру selectEmp connection.Open(); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { for (int i = 0; i < reader.FieldCount; i++) Console.WriteLine("{0}: {1}", reader.GetName(i), reader[i]); Console.WriteLine(); } connection.Close(); } |
Выполнение хранимой процедуры, возвращайщей данные в табличном представлении:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
static void Main(string[] args) { // код хранимой процедуры selectEmp: CREATE proc dbo.selectEmp // as select * from dbo.Employees string conStr = @"Data Source=.\SQLEXPRESS; Initial Catalog=ShopDB; Integrated Security=True;"; // создание строки подключения SqlConnection connection = new SqlConnection(conStr); SqlCommand cmd = new SqlCommand("selectEmp", connection) { CommandType = System.Data.CommandType.StoredProcedure }; // закомментировать connection.Open(); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { for (int i = 0; i < reader.FieldCount; i++) Console.WriteLine("{0}: {1}", reader.GetName(i), reader[i]); Console.WriteLine(); } connection.Close(); } |
Выполнение хранимой процедуры, принимающей параметры:
|
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 |
static void Main(string[] args) { // код хранимой процедуры selectEmp: CREATE proc dbo.proc_p1 @EmployeeID nvarchar(50) // AS // SELECT * from dbo.Employees // WHERE EmployeeID = @EmployeeID string conStr = @"Data Source=.\SQLEXPRESS; Initial Catalog=ShopDB; Integrated Security=True;"; // создание строки подключения SqlConnection connection = new SqlConnection(conStr); Console.WriteLine("Enter employeeID"); int employeeID = int.Parse(Console.ReadLine()); // получение данных от пользователя SqlCommand cmd = new SqlCommand("proc_p1", connection) { CommandType = System.Data.CommandType.StoredProcedure }; // создание команды, вызывающей хранимую процедуру cmd.Parameters.AddWithValue("@EmployeeID", employeeID); // добавление одного параметра connection.Open(); SqlDataReader reader = cmd.ExecuteReader(); // выполнение команды while (reader.Read()) { for (int i = 0; i < reader.FieldCount; i++) Console.WriteLine("{0}: {1}", reader.GetName(i), reader[i]); Console.WriteLine(); } connection.Close(); } |
Выполнение хранимой процедуры, возвращающей значение:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
static void Main(string[] args) { // код хранимой процедуры CREATE PROCEDURE ProcedureReturnValue // AS // BEGIN // return 1; // END string conStr = @"Data Source=.\SQLEXPRESS; Initial Catalog=ShopDB; Integrated Security=True;"; // создание строки подключения SqlConnection connection = new SqlConnection(conStr); SqlCommand cmd = new SqlCommand("ProcedureReturnValue", connection) { CommandType = System.Data.CommandType.StoredProcedure }; SqlParameter parameter = cmd.Parameters.Add(new SqlParameter()); parameter.Direction = System.Data.ParameterDirection.ReturnValue; // после выполнения комманды parameter будет содержать возвращаемое значение хранимой процедуры connection.Open(); cmd.ExecuteNonQuery(); Console.WriteLine(parameter.Value); } |
И напоследок пример получение значения автоинкремента с помощью хранимой процедуры:
|
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 |
class Program { static string conStr = @"Data Source=.\SQLEXPRESS; Initial Catalog=ShopDB; Integrated Security=True;"; // создание строки подключения static int InsertCustomer() { SqlConnection connection = new SqlConnection(conStr); SqlCommand cmd = new SqlCommand("IdentityProcedure", connection) { CommandType = System.Data.CommandType.StoredProcedure }; SqlParameter parameter = cmd.Parameters.Add(new SqlParameter()); parameter.Direction = System.Data.ParameterDirection.ReturnValue; // после выполнения комманды parameter будет содержать возвращаемое значение хранимой процедуры connection.Open(); cmd.ExecuteNonQuery(); connection.Close(); Console.WriteLine("New customer with CustomerNo {0} was added", parameter.Value); return (int)parameter.Value; } static void DeleteCustomerByID(int customerNo) { SqlConnection connection = new SqlConnection(conStr); SqlCommand cmd = new SqlCommand("DELETE CUstomers WHERE CustomerNo = @CustomerNo", connection); SqlParameter parameter = cmd.Parameters.Add(new SqlParameter("CustomerNo", SqlDbType.Int)); parameter.Direction = System.Data.ParameterDirection.Input; parameter.Value = customerNo; connection.Open(); cmd.ExecuteNonQuery(); connection.Close(); Console.WriteLine("Customer with CustomerNo {0} was deleted", parameter.Value); } static void Main(string[] args) { // код хранимой процедуры // CREATE PROCEDURE IdentityProcedure // AS // BEGIN // INSERT Customers // VALUES // ('TEST','TEST', 'TEST', 'TEST', 'TEST', 'TEST', 'TEST', GETDATE()) // RETURN @@IDENTITY -- процедура возвращает значение автоинкремента добавленной строки // END // GO int customerNo = InsertCustomer(); // Добавление нового клиента в базу данных Console.WriteLine("Press any key to continue..."); Console.ReadKey(); DeleteCustomerByID(customerNo); // Удаление добавленного клиента } } |