Execute A Stored Procedure Programmatically

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