Calling Stored Procedure in C#

In Previous post we learnt how to use Sqlparameter to pass the parameters to command.So that in this way we can avoid SQL Injection. If you have not read that post , please click on the given link.Using SQLParameter in C#
Today we are going to learn how to call Stored Procedure created in Database from C# using Sqlparameter . Just like we pass parameter to sqlcommand object , we will be passing parameters to the stored procedure. Let's understand in more detail.

Explanation

We have created a stored procedure in SQL Server names GetDetails with the input parameter as ID having INT datatype. The Stored procedure just select the records from a table in database having data of Employees based on ID passed to procedure.
Then we have a Windows Application program in C# which is having a textbox and a Datagridview and a Button. We are going to enter the ID of employee on the textbox and after click on button , we can view the details of the employee in the datagridview.
In the Back end , we have used sqlparameter to call the stored procedure and pass the ID parameter to it to retrieve the employee data.

Stored Procedure

USE [Test]
GO
/****** Object: StoredProcedure [dbo].[GetDetails] Script Date: 13/05/2018 11:59:40 AM ******/ SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GetDetails] @ID int
AS
BEGIN
SET NOCOUNT ON;
Select * from tbl_Record where ID=@ID
END

Program

using System;
using System.Data.SqlClient;
namespace CallStoredProc
{
    class Connection
    {
     public SqlConnection getcon()
     {
      SqlConnection con = new SqlConnection("data source=VISHAL-PC\\SQL2014;database=Test;uid=sa;password=sql@123");
      con.Open();
      return con;
     }
    }
}

using System;
using System.Data;
using System.Drawing;
using System.Windows.Forms;
using System.Data.SqlClient;
namespace CallStoredProc
{
    public partial class Form1 : Form
    {
     Connection con = new Connection();
     DataTable dt = new DataTable();
     public Form1()
     {
      InitializeComponent();
     }
     private void getdetailbutton_Click(object sender, EventArgs e)
     {
      SqlConnection sqlcon = con.getcon();
      SqlDataAdapter da = new SqlDataAdapter("GetDetails", sqlcon);
      da.SelectCommand.CommandType = CommandType.StoredProcedure;
      da.SelectCommand.Parameters.AddWithValue("@ID", Idbox.Text);
      da.Fill(dt);
      empgrid.DataSource = dt;
     }
    }
}

Output