Background

I have decided to write this article after considering the requirements of students and beginners that often ask how to insert form data and display the data in a gridview programmatically using a Single Stored Procedure. As a result I have explained those requirements with the basics so they can get an idea of how to submit form data and display it in a gridview using a single Stored Procedure.

So let us start the walkthrough.

Create a table "Student" as in the following:
studentTable.png

Create a Stored Procedure named "Studenentryview" as:
  1. Create procedure studentEntryView
  2. (
  3. @Action varchar (10),
  4. @fname Varchar (50),
  5. @Mname varchar (50),
  6. @Lname Varchar (50)
  7. )
  8. as
  9. begin
  10. If @Action='Insert' --used to insert records
  11. Begin
  12. Insert into Student (Fname,MName,Lastname) values (@fname,@Mname,@Lname)
  13. End
  14. else if @Action='View' --used to view records
  15. Begin
  16. select *from Student
  17. End
  18. End
In the preceding Stored Procedure I have declared four variables, they are @Action, @fname, @Mname and @Lname to store Fname, MName and Lastname into the table and the action is used to perform the exact operation; that is view or display as the user requests; in other words if the user inserts the record then the Insert action is executed and if user the user views the record then the view action is to be executed.
Now create the one sample application "StudentEntryView" as:
  1. Start - All Programs - Microsoft Visual Studio 2010.
  2. File - New Website - C# - Empty website (to avoid adding a master page).
  3. Provide the web site a name such as "StudentEntryView" or another as you wish and specify the location.
  4. Then right-click on Solution Explorer - Add New Item - Default.aspx page.
  5. Drag and drop one button, three textboxes, one gridview and two hidden fields to the hidden value to the database and one label on the <form> section of the Default aspx page.
Then switch to the design view; the <form> section of the Default aspx page source will look as in the following:
  1. <form id="form1"runat="server">
  2. <div>
  3. First Name <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
  4. Middle Name<asp:TextBox ID="TextBox2" runat="server"></asp:TextBox>
  5. Last Name <asp:TextBox ID="TextBox3" runat="server"></asp:TextBox>
  6. <asp:ButtonID="Button1"runat="server"Text="Add"onclick="Button1_Click" />
  7. </div>
  8. <asp:HiddenFieldID="HiddenField1"runat="server"Value="insert" />
  9. <asp:HiddenField ID="HiddenField2" runat="server" Value="View" />
  10. <asp:GridViewID="GridView1"runat="server">
  11. </asp:GridView>
  12. </form>
In the preceding source code.
Insert Data To DataBase as
Double-click on "Button" then write the following code:
  1. protected void Btnsave_Click(object sender, EventArgs e)
  2. {
  3. connection();
  4. query = "studentEntryView"; //Stored Procedure name
  5. SqlCommand com = new SqlCommand(query, con); //creating SqlCommand object
  6. com.CommandType = CommandType.StoredProcedure; //here we declaring command type as stored Procedure
  8. /* adding paramerters to SqlCommand below *\
  9. com.Parameters.AddWithValue("@Action", HiddenField1.Value).ToString(); //for ing hidden value to preform insert operation
  10. com.Parameters.AddWithValue("@FName",TextBox1.Text.ToString()); //first Name
  11. com.Parameters.AddWithValue("@Mname ", TextBox2.Text.ToString()); //middle Name
  12. com.Parameters.AddWithValue("@LName ",TextBox3.Text.ToString()); //Last Name
  13. com.ExecuteNonQuery(); //executing the sqlcommand
  14. Label1.Visible = true;
  15. Label1.Text = "Records are Submitted Successfully";
  16. }

In the preceding code, I have explained in detail in each line through comments what each line is used for and what purpose, I hope you understand it.

Now run the application; the starting page should be as follows:
demo.png

Now insert some data and click on the submit button, the following message will be shown after successful submission of the data:
Final.png

Display Data In Gridview
Now we have inserted the data into the database as above, next we learn how to display the data in the gridview that we inserted above
Use the following code to display the data in the gridview at page load or any event that you want:
  1. protected void Page_Load(object sender, EventArgs e)
  2. {
  3. connection();
  4. query = "studentEntryView";
  5. SqlCommand com = new SqlCommand(query, con);
  6. com.CommandType = CommandType.StoredProcedure;
  7. com.Parameters.AddWithValue("@Action", HiddenField2.Value).ToString();
  8. DataSet ds =new DataSet();
  9. SqlDataAdapter da = new SqlDataAdapter(com);
  10. da.Fill(ds);
  11. GridView1.DataSource = ds;
  12. GridView1.DataBind();
  13. }
In the preceding page load code the view action value of the hidden field is ed to the database and performs the exact block of code.
Note
  • For detailed code please download the zip file attached above.
  • Don't forget to apply the relevant changes in the Web.config file depending on your Server location.
Summary
Now we have learned how to submit the form data into the database and display it in the gridview using a single Stored Procedure. I hope this article is useful for all students and beginners. If you have any suggestion related to this article please contact me.