Post
Ask Question

Execute A Stored Procedure 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 that the code can be reused repeatedly. 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.
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 creates 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.
CREATE PROC PROC_NAME (@FIRST_NAME NVARCHAR(50), @LAST_NAME NVARCHAR(50), @AGE INT)  
AS  
BEGIN  
INSERT INTO TABLE_NAME VALUES(@FIRST_NAME,@LAST_NAME,@AGE)  
END  
EXEC PROC_NAME  

Step 3 - How to Select Table Columns?

The “SELECT” keyword is used to select data from a database.

SELECT * FROM TABLE_NAME 

Step 4 - App. config

Windows Authentication Connection string.

<connectionStrings>  
   <add name="SqlConnectionString" connectionString="Data Source=Servername;Initial Catalog=Database_Name;Integrated Security=true"/>  
</connectionStrings>  

SQL Server Authentication Connection string.

<connectionStrings>  
   <add name="SqlConnectionString" connectionString="Data Source=Servername;Initial Catalog=Database_Name;Integrated Security=false;Uid=sa;Pwd=*****"/>  
</connectionStrings> 

Step 5

  1. Create a class named "Common," or keep it as you wish.
  2. Write the following code to call the stored procedure

Coding

using System;  
using System.Collections.Generic;  
using System.Linq;  
using System.Text;  
using System.Threading.Tasks;  
using System.Data;  
using System.Data.SqlClient;  
using System.Configuration;  
  
namespace Storeprocedure  
{  
   public  class Common  
   {  
     
   SqlConnection sqlCon=null;  
     String SqlconString=ConfigurationManager.ConnectionStrings["SqlConnectionString"].ConnectionString;  
   public void Test(string firstName,string lastName,int age)  
       {  
          using(sqlCon=new SqlConnection(SqlconString))  
           {  
             sqlCon.Open();  
             SqlCommand sql_cmnd = new SqlCommand("PROC_NAME", sqlCon);  
             sql_cmnd.CommandType = CommandType.StoredProcedure;  
             sql_cmnd.Parameters.AddWithValue("@FIRST_NAME", SqlDbType.NVarChar).Value=firstName;  
             sql_cmnd.Parameters.AddWithValue("@LAST_NAME", SqlDbType.NVarChar).Value=lastName;  
             sql_cmnd.Parameters.AddWithValue("@AGE", SqlDbType.Int).Value = age;  
             sql_cmnd.ExecuteNonQuery();  
             sqlCon.Close();  
           }  
        }          
  }  
}  

Step 6

  1. To create a method, call the Common class and implement the methods.
  2. Write the following code.

Coding

using System;  
using System.Collections.Generic;  
using System.Linq;  
using System.Text;  
using System.Threading.Tasks;  
using System.Windows;  
using System.Windows.Controls;  
using System.Windows.Data;  
using System.Windows.Documents;  
using System.Windows.Input;  
using System.Windows.Media;  
using System.Windows.Media.Imaging;  
using System.Windows.Navigation;  
using System.Windows.Shapes;  
  
  
namespace Storeprocedure  
{  
    /// <summary>  
    /// Interaction logic for MainWindow.xaml  
    /// </summary>  
    public partial class MainWindow : Window  
    {  
           
                  
        public MainWindow()  
        {  
            InitializeComponent();  
        }  
        public void MethodName()  
        {     
            Common Ocommon = new Common();  
            int age = Convert.ToInt32(txtAge.Text);  
            Ocommon.Test(txtFirstName.Text, txtLastName.Text, age);  
            MessageBox.Show("Insert Successfully...");  
        }  
  
        private void BtnInsert_Click(object sender, RoutedEventArgs e)  
        {  
            MethodName();  
        }  
    }  
}  

Output

Output

Summary

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


Similar Articles