Execute A Stored Procedure Programmatically

In this article, I will explain how to create a Stored procedure (SP) and execute an SP programmatically.

Introduction

 
A Stored Procedure is a group of SQL statements compiled into a single execution. A Stored Procedure is a prepared SQL code that you can save, so the code can be reused over and over again. Input and output parameters are used in the stored procedure. I have written this article focusing on students and beginners.
 

Advantages

  1. Stored Procedures can reduce network traffic and lead to faster execution.
  2. It is very easily maintainable and well secure.
  3. Reusable code
Step 1 - How to create a Table
  1. The “CREATE TABLE” statement is used to create a new table in a database.
  2. Declare all column names and data types.
  1. CREATE TABLE TABLE_NAME (FIRST_NAME NVARCHAR(50),LAST_NAME NVARCHAR(50),AGE INT)  
Step 2 - How to create a Stored Procedure
  1. The “CREATE PROCEDURE” statement is used to create a new stored procedure in a database.
  2. Declare the parameters and data types.
  3. The “EXEC” keyword is used to execute the stored procedure.
  1. CREATE PROC PROC_NAME (@FIRST_NAME NVARCHAR(50), @LAST_NAME NVARCHAR(50), @AGE INT)  
  2. AS  
  3. BEGIN  
  4. INSERT INTO TABLE_NAME VALUES(@FIRST_NAME,@LAST_NAME,@AGE)  
  5. END  
  6. EXEC PROC_NAME  
Step 3 - How to Select Table Columns
 
The “SELECT” keyword is used to select data from a database.
  1. SELECT * FROM TABLE_NAME 
Step 4 - App.config
 
Windows Authentication Connection string.
  1. <connectionStrings>  
  2.    <add name="SqlConnectionString" connectionString="Data Source=Servername;Initial Catalog=Database_Name;Integrated Security=true"/>  
  3. </connectionStrings>  
SQL Server Authentication Connection string.
  1. <connectionStrings>  
  2.    <add name="SqlConnectionString" connectionString="Data Source=Servername;Initial Catalog=Database_Name;Integrated Security=false;Uid=sa;Pwd=*****"/>  
  3. </connectionStrings> 
Step 5
  1. Create a class named as "Common" or keep it as you wish.
  2. Write the following code to call the stored procedure
Coding
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Text;  
  5. using System.Threading.Tasks;  
  6. using System.Data;  
  7. using System.Data.SqlClient;  
  8. using System.Configuration;  
  9.   
  10. namespace Storeprocedure  
  11. {  
  12.    public  class Common  
  13.    {  
  14.      
  15.    SqlConnection sqlCon=null;  
  16.      String SqlconString=ConfigurationManager.ConnectionStrings["SqlConnectionString"].ConnectionString;  
  17.    public void Test(string firstName,string lastName,int age)  
  18.        {  
  19.           using(sqlCon=new SqlConnection(SqlconString))  
  20.            {  
  21.              sqlCon.Open();  
  22.              SqlCommand sql_cmnd = new SqlCommand("PROC_NAME", sqlCon);  
  23.              sql_cmnd.CommandType = CommandType.StoredProcedure;  
  24.              sql_cmnd.Parameters.AddWithValue("@FIRST_NAME", SqlDbType.NVarChar).Value=firstName;  
  25.              sql_cmnd.Parameters.AddWithValue("@LAST_NAME", SqlDbType.NVarChar).Value=lastName;  
  26.              sql_cmnd.Parameters.AddWithValue("@AGE", SqlDbType.Int).Value = age;  
  27.              sql_cmnd.ExecuteNonQuery();  
  28.              sqlCon.Close();  
  29.            }  
  30.         }          
  31.   }  
  32. }  
Step 6
  1. To create a method, call the class named Common and implement the methods.
  2. Write the following code.
Coding
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Text;  
  5. using System.Threading.Tasks;  
  6. using System.Windows;  
  7. using System.Windows.Controls;  
  8. using System.Windows.Data;  
  9. using System.Windows.Documents;  
  10. using System.Windows.Input;  
  11. using System.Windows.Media;  
  12. using System.Windows.Media.Imaging;  
  13. using System.Windows.Navigation;  
  14. using System.Windows.Shapes;  
  15.   
  16.   
  17. namespace Storeprocedure  
  18. {  
  19.     /// <summary>  
  20.     /// Interaction logic for MainWindow.xaml  
  21.     /// </summary>  
  22.     public partial class MainWindow : Window  
  23.     {  
  24.            
  25.                   
  26.         public MainWindow()  
  27.         {  
  28.             InitializeComponent();  
  29.         }  
  30.         public void MethodName()  
  31.         {     
  32.             Common Ocommon = new Common();  
  33.             int age = Convert.ToInt32(txtAge.Text);  
  34.             Ocommon.Test(txtFirstName.Text, txtLastName.Text, age);  
  35.             MessageBox.Show("Insert Successfully...");  
  36.         }  
  37.   
  38.         private void BtnInsert_Click(object sender, RoutedEventArgs e)  
  39.         {  
  40.             MethodName();  
  41.         }  
  42.     }  
  43. }  
Output
 
Output
 

Summary

 
In this article, you have got an overview of how to create a stored procedure and execute it programmatically. I have written this article focusing on beginners and students.