Stored Procedure is already written queries which are saved in database. If you want to use any SELECT, INSERT, UPDATE, DELETE query multiple times then you can put your query in stored procedure and use it anywhere. You can execute your stored procedure using “EXEC” SQL Command.

For Example:

Exec Sp_InsertStudentDetail

Here, Sp_InsertStudentDetail is the name of stored procedure and Exec is the command which tells Sql server to run the query inside the stored procedure.

The query inside the stored procedure can be like:

Select * from tblStudentDetails

So, First we will create a table named tblStudentDetails in the database in which we are having for columns FirstName, LastName, DOB, Email-id and we have defined DataTypes as well and all are not allowed to be null so we have not ticked the column ALLOWNulls. Refer the screen-shot for table design:

As I want to insert the data in database, I am creating a stored procedure in which I can use Insert query like:

insert into tablename (col_name) values (col_value)

Now we are creating an stored procedure named Sp_InsertStudentDetails

Create Procedure Sp_InsertStudentDetail
@FirstName nvarchar(max),
@LastName nvarchar(max),
@DOB nvarchar(max),
@EmailId nvarchar(max)

As
Begin

insert into tblStudentdetails (FirstName,LastName,DOB,EmailId) 
values (@FirstName,@LastName,@DOB,@EmailId)

End

Now for the execution of above mentioned stored procedure in c# application we will follow some steps:

Step 1: First we will include namespace 'using system.data.sqlclient'

Step 2: We will create a Sql connection using parameters Server name,database name, username and password.

Step 3: we will open that sql connection.

Step 4: Now create an object of sqlcommand in which we will pass query and connection object.

Step 5: now set the command object to its commandType so it knows to execute stored procedure.

Step 6: Add parameters in sqlcommand which we want to insert in our database through stored procedure.

Step 7: we will execute command using ExecuteNonQuery as it will return no.of rows effected in database.

Step 8: After execution,we will close Sqlconnection.

First in Default.aspx page we will add four labels and four textboxes and one button to submit the data in database. Follow the below screenshot for reference.

To insert the value in database on button click,we can use the following c# code on button click event:

try
{
 SqlConnection  SqlCon = new SqlConnection("Data Source=SHIKHABANSAL-PC; Initial Catalog=StudentDetails;Integrated Security=True");
SqlCon.Open();
  SqlCommand SqlCmd= new SqlCommand("Sp_InsertStudentDetail", SqlCon);
  SqlCmd.CommandType = CommandType.StoredProcedure;
  SqlCmd.Parameters.Add("@FirstName", SqlDbType.nVarChar).Value = txtFirstName.Text;
 SqlCmd.Parameters.Add("@LastName", SqlDbType.nVarChar ).Value = txtLastName.Text;
 SqlCmd.Parameters.Add("@DOB", SqlDbType.DateTime).Value = txtDOB.Text;
 SqlCmd.Parameters.Add("@EmailId", SqlDbType.nVarChar ).Value = txtEmailId.Text;
return SqlCmd.ExecuteNonQuery();
 SqlCon.Close();

}

catch (SqlException ex)
{
  Console.WriteLine("SQL Error" + ex.Message.ToString());
  return 0;
}

Now you can check output in database table by using query :  Exec Sp_InsertStudentDetail