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.
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
insert into tblStudentdetails (FirstName,LastName,DOB,EmailId)
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: