Создание и выполнение хранимой процедуры с помощью C# ADO.Net

ЧТО ТАКОЕ ХРАНИМАЯ ПРОЦЕДУРА?

Store Procedure -это предварительно скомпилированные запросы SQL, которые сохраняются на сервере. Преимущество процедуры хранилища заключается в том, что вам не нужно снова и снова писать одни и те же SQL-запросы. Процедура хранения компилируется один раз и может использоваться навсегда. Тем не менее, это легко создать процедуру хранения в среде SQL Server Management Studio, но здесь я объясню, как можно создать и выполнить процедуру хранения прямо из вашей программы.

1. СОЗДАНИЕ ПРОЦЕДУРЫ ХРАНИЛИЩА С ПОМОЩЬЮ C# ADO.NET

Здесь я собираюсь создать процедуру хранения для сохранения и извлечения записей из базы данных ComputerShop.
Создание хранимой процедуры SQL-запросов

    CREATE PROCEDURE Insert_Record_Procedure
                    (
                    @Name VARCHAR(50),
                    @Price VARCHAR(50),
                    @Date DATETIME
                    )
                  AS
                    INSERT INTO Products(Name,Price,Date) Values(@Name,@Price,@Date)

Эту процедуру хранения можно создать в Обозревателе серверов или среде SQL Server Management Studio. Здесь я создал эту процедуру хранения, используя C#

    using System;
    using System.Data.SqlClient;
     
    namespace Create_Store_Procedure
    {
        class Program
        {
            static void Main(string[] args)
            {
                SqlConnection con = new SqlConnection(@"Data Source=.\SQLEXPRESS;Initial Catalog=ComputerShop;Integrated Security=True");
                string query =
                  @"
                    CREATE PROCEDURE Insert_Record_Procedure
                    (
                    @Name VARCHAR(50),
                    @Price VARCHAR(50),
                    @Date DATETIME
                    )
                  AS
                    INSERT INTO Products(Name,Price,Date) Values(@Name,@Price,@Date)
                ";
     
                SqlCommand cmd = new SqlCommand(query, con);
                try
                { 
                    con.Open();
                    cmd.ExecuteNonQuery();
                    Console.WriteLine("Store Procedure Created Successfully");
                }
                catch (SqlException e)
                {
                    Console.WriteLine("Error Generated. Details: " + e.ToString());
                }
                finally
                {
                    con.Close();
                    Console.ReadKey();
                }
            }
        }
    }

 

2. ПЕРЕЧИСЛЯЕМ ВСЕ СОЗДАННЫЕ ПРОЦЕДУРЫ ХРАНЕНИЯ

инструкция SQL

select SPECIFIC_NAME from ComputerShop.information_schema.routines where routine_type = 'PROCEDURE'

    using System;
    using System.Data.SqlClient;
     
    namespace Create_Store_Procedure
    {
        class Program
        {
            static void Main(string[] args)
            {
                SqlConnection con = new SqlConnection(@"Data Source=.\SQLEXPRESS;Initial Catalog=ComputerShop;Integrated Security=True");
                string query =
                  @"
                    select SPECIFIC_NAME from ComputerShop.information_schema.routines where routine_type = 'PROCEDURE'
                ";
     
                SqlCommand cmd = new SqlCommand(query, con);
                try
                { 
                    con.Open();             
                    SqlDataReader dr = cmd.ExecuteReader();
                    while (dr.Read())
                    {
                        Console.WriteLine(dr["SPECIFIC_NAME"].ToString());
                    }
                }
                catch (SqlException e)
                {
                    Console.WriteLine("Error Generated. Details: " + e.ToString());
                }
                finally
                {
                    con.Close();
                    Console.ReadKey();
                }
            }
        }
    }

3. ВСТАВКА ЗАПИСЕЙ В ТАБЛИЦУ С ПОМОЩЬЮ ПРОЦЕДУРЫ ХРАНЕНИЯ

using System;
    using System.Data.SqlClient;
    using System.Data;
     
    namespace Create_Store_Procedure
    {
        class Program
        {
            static void Main(string[] args)
            {
                SqlConnection con = new SqlConnection(@"Data Source=.\SQLEXPRESS;Initial Catalog=ComputerShop;Integrated Security=True");           
                SqlCommand cmd = new SqlCommand("Insert_Record_Procedure", con);
                try
                { 
                    con.Open();
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add(new SqlParameter("@Name", "SSD DRIVE"));
                    cmd.Parameters.Add(new SqlParameter("@Price", "$300"));
                    cmd.Parameters.Add(new SqlParameter("@Date" , "25 August 14"));
                    int i = cmd.ExecuteNonQuery();
                    if(i>0)
                    {
                        Console.WriteLine("Records Inserted Successfully.");
                    }
     
                }
                catch (SqlException e)
                {
                    Console.WriteLine("Error Generated. Details: " + e.ToString());
                }
                finally
                {
                    con.Close();
                    Console.ReadKey();
                }
            }
        }
    }

 

4. ПОЛУЧЕНИЕ ЗАПИСЕЙ С ПОМОЩЬЮ ПРОЦЕДУРЫ ХРАНЕНИЯ C#

Здесь я создал еще одну процедуру хранения для извлечения записей из таблицы базы данных.

    CREATE PROCEDURE Retrieve_Record_Proc
    (
    @Name VARCHAR(50)
    )
    AS
    SELECT * FROM Products where Name=@Name

    using System;
    using System.Data.SqlClient;
    using System.Data;
     
    namespace Create_Store_Procedure
    {
        class Program
        {
            static void Main(string[] args)
            {
                SqlConnection con = new SqlConnection(@"Data Source=.\SQLEXPRESS;Initial Catalog=ComputerShop;Integrated Security=True");                       
                try
                { 
                    con.Open();
                    SqlCommand cmd = new SqlCommand("Retrieve_Record_Proc", con);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add(new SqlParameter("@Name", "HardDisk"));
                    SqlDataReader dr = cmd.ExecuteReader();
                    while(dr.Read())
                    {
                        Console.WriteLine("Product Name : " + dr[1].ToString());
                        Console.WriteLine("Price : " + dr[2].ToString());
                        Console.WriteLine("Date : " + dr[3].ToString());
                    }
     
                }
                catch (SqlException e)
                {
                    Console.WriteLine("Error Generated. Details: " + e.ToString());
                }
                finally
                {
                    con.Close();
                    Console.ReadKey();
                }
            }
        }
    }

5. ИЗМЕНИТЬ ПРОЦЕДУРУ ХРАНЕНИЯ

Вы можете изменить процедуру хранения с помощью ALTERключевого слова

    ALTER PROCEDURE Retrieve_Record_Proc
    (
    @Name VARCHAR(50)
    )
    AS
    SELECT * FROM Products where Name=@Name

6. УДАЛЕНИЕ ПРОЦЕДУРЫ ХРАНЕНИЯ

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

DROP PROCEDURE Retrieve_Record_Proc