Create and Execute Store Procedure using C# ADO.Net

Content

1. Create Store Procedure using C# ADO.NET
2. List All the Created Store Procedure
3. Insert Records into Table using Store Procedure
4. Retrieve Records using Store Procedure
5. Alter Store Procedure
6. Delete Store Procedure

What is Store Procedure?

A Store Procedure is a Pre Compiled SQL Queries that is saved into server. The Benefit of Store Procedure is you don't need to write same SQL Queries again and again. Store Procedure get compiled once and can be used forever. However, it is easy to create store procedure in SQL Server Management Studio but here I will explain how you can create and execute Store Procedure right from your program.

1. Create Store Procedure using C# ADO.NET

Here, I am going to create Store Procedure for saving and retrieving records from ComputerShop Database.
Create Store Procedure SQL Queries

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

You can create this store procedure in Server Explorer or SQL Server Management Studio. Here, I have created this store procedure using C#.

Programming Example of Creating Store 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 =
              @"
                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. List All the Created Store Procedure

SQL Statement

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

Programming Example

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

Output

3. Insert Records to Table using Store Procedure

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

Output:

4. Retrieve Records using Store Procedure C#

Here, I have created one more Store Procedure to retrieve records from database table.

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

Programming Example

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

Output

5. Alter Store Procedure

You can alter Store Procedure using ALTER keyword.

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

6. Delete Store Procedure

Use DROP keyword for deleting a Store Procedure

DROP PROCEDURE Retrieve_Record_Proc

Summary

In this chapter, you learned how to create and execute Store Procedure using C# ADO.NET. In the next chapter, you will learn how to get data from table using DataReader.