Хранимые процедуры в ADO.NET

Приветствую всех, сегодня рассмотрим примеры как использовать хранимые процедуры в ado.net

Но для начала нам необходимо разобраться с созданием и выполнением параметризированных запросов с использование коллекции Parameters объекта SqlCommand.

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();
        }

Использование выходных параметров в параметризированных запросах:

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

 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();
        }

Выполнение хранимой процедуры, возвращайщей данные в табличном представлении:

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();
        }

Выполнение хранимой процедуры, принимающей параметры:

 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();
        }

Выполнение хранимой процедуры, возвращающей значение:

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);
        }

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

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);    // Удаление добавленного клиента
        }
    }