Sameer Gaikwad CRUD Operation SQL stored procedure mesameergaikwad

Background

Sometimes there is a need to insert, update and delete records in a GridView using a single Stored Procedure instead of creating separate Stored Procedures for each operation.

Suppose I have one .aspx web page in which I need to insert, view, update and delete records. To do that, instead of creating four Stored Procedures to perform these tasks I will create a single Stored Procedure to satisfy my requirements and I will access it in code behind depending on the action performed by the end-user on a button click.

I have written this article, especially focusing on newcomers and anyone new who wants to insert, update and delete records in a GridView using a Single Stored Procedure, so let us start with a basic introduction.

First create the table named employee as:

I have set the primary key on the id column and I have set the Identity specification to Yes.

Now we have a table to perform these operations for. Now let us start to create the Stored Procedure.

The Stored Procedure is created using the keyword "Create Procedure" followed by the procedure name. Let us create the Stored Procedure named "EmpEntry" as in the following:

1. create Procedure EmpEntry  
2. (   
3.  @Action Varchar (10),           
4.  @id int=null,  
5.  @FnameVarchar (50)=null,    
6.  @MName Varchar (50)=null,    
7.  @Lname Varchar (50)=null   
8. )  
9. as  
10. Begin   
11.   SET NOCOUNT ON;  
12.   
13. If @Action='Insert'   
14. Begin  
15.    Insert Into employee (FName,MName,LName)values(@Fname,@MName,@Lname)  
16. End    
17. else if @Action='Select'  
18. Begin  
19.     select *from employee  
20. end  
21. else if @Action='Update'   
22. Begin  
23.    update employeeset FName=@Fname,MName=@MName,LName=@Lname where id=@id  
24.  End  
25.  Else If @Action='delete'  
26.  Begin  
27.    delete from employeewhere id=@id  
28.  end  
29. End  

The comments in the Stored Procedure above clearly explain which block is used for which purpose, so I have briefly explained it again. I have used the @Action variable and assigned the string to them and according to the parameter ed to the Stored Procedure, the specific block will be executed because I have kept these blocks or conditions in nested if-else if conditional statements.

Now create the one sample application "Empsys" as:

"Start" -> "All Programs" -> "Microsoft Visual Studio 2012".

"File" -> "New Project" -> 

"Web" -> "ASP.NET Empty Web Application" (.NET framework 3.5).

Provide the website a name such as  "CRUDOPERATION" or another as you wish and specify the location.

Then right-click on Solution Explorer - "Add New Item" - "Default.aspx page".

Drag and drop one button, three textboxes, one GridView, and one hidden field 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:Button ID="Button1"runat="server"Text="save"onclick="Button1_Click" />  
7.     </div>  
8. <asp:HiddenField ID="HiddenField1" runat="server"/>  
9.  <asp:GridView ID="GridView1"runat="server" >  
10.      </asp:GridView>  
11. </form>  

Now use the following GridView event properties to perform events such as update, delete, edit cancel, and so on. Let us see what the properties are:

• DataKeyNames: This property I have used to the row index of GridView  
• OnRowEditing: This property is used to handle the event when the user clicks on the edit button
• OnRowCancelingEdit: This property is used to handle the event when the user clicks on the Cancel button that exists after clicking on the edit button
• OnRowDeleting: This property is used to handle the event when the user clicks on the delete button that deletes the row of the GridView
• OnRowUpdating: This property is used to handle the event when the user clicks on the update button that updates the Grid Record 

Now my grid will look such as the following:

2. <asp:GridView ID="GridView1" runat="server"  DataKeyNames ="id" OnRowEditing ="Edit"                 
3.         OnRowCancelingEdit ="canceledit"    OnRowDeleting ="delete"    OnRowUpdating = "Update">  
4.      </asp:GridView>

On the preceding GridView properties, I have assigned the method name to be called for particular operations.

Method to Insert Data in Database  

Right-click from the design page and view the code and then write the following code in the default.aspx.cs page to save the inserted records in the database:

1. protected void save_Click(object sender, EventArgs e)  
2. {  
3.      
4. string strcon = ConfigurationManager.ConnectionStrings["dbconnection"].ConnectionString;
5.             SqlConnection con = new SqlConnection(strcon);
6.             con.Open();      
7.             SqlCommand com = new SqlCommand("EmpEntry", con);//creating  SqlCommand  object  
8.             com.CommandType = CommandType.StoredProcedure;//here we declaring command type as stored Procedure  
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.   
17. }  

Now create the method to view the records in the GridView:

1. public void viewdata()  
2. {  
3.     
4. string strcon = ConfigurationManager.ConnectionStrings["dbconnection"].ConnectionString;
5.             SqlConnection con = new SqlConnection(strcon);
6.             con.Open();
7.             SqlCommand com = new SqlCommand("EmpEntry", con);
8.             com.CommandType = CommandType.StoredProcedure;
9.             com.Parameters.AddWithValue("@Action", HiddenField1.Value).ToString();
10.             DataSet ds = new DataSet();
11.             SqlDataAdapter da = new SqlDataAdapter(com);
12.             da.Fill(ds);
13.             GridView1.DataSource = ds;
14.             GridView1.DataBind();
15. }  

The following is the method for the "OnRowEditing" Event:

1. protected void edit(object sender, GridViewEditEventArgs e)  
2. {  
3.     GridView1.EditIndex= e.NewEditIndex;  
4.     gedata();  
5. }  

The following is method for the "OnRowCancelingEdit" Event:

1. protected void  canceledit(object sender, GridViewCancelEditEventArgs e)  
2. {  
3.     GridView1.EditIndex = -1;  
4.     gedata();  
5. }  

The following is the method for the "OnRowDeleting" Event:

1. protected void delete(object sender, GridViewDeleteEventArgs e)  
2. {  
3.      
4.  string strcon = ConfigurationManager.ConnectionStrings["dbconnection"].ConnectionString;
5.             SqlConnection con = new SqlConnection(strcon);
6.             con.Open();
7.             HiddenField1.Value = "Delete";
8.             SqlCommand com = new SqlCommand("EmpEntry", con);
9.             com.CommandType = CommandType.StoredProcedure;
10.             com.Parameters.AddWithValue("@Action", HiddenField1.Value).ToString();
11.             com.ExecuteNonQuery();
12.             con.Close();
13. }  

The following is method for the "OnRowUpdating" Event:

1. protected void update(object sender, GridViewUpdateEventArgs e)  
2. {  
3.      
4. string strcon = ConfigurationManager.ConnectionStrings["dbconnection"].ConnectionString;
5.             SqlConnection con = new SqlConnection(strcon);
6.             con.Open();
7.             HiddenField1.Value = "update";
8.             SqlCommand com = new SqlCommand("EmpEntry", con);
9.             com.CommandType = CommandType.StoredProcedure;
10.             com.Parameters.AddWithValue("@Action", HiddenField1.Value).ToString();
11.             com.Parameters.AddWithValue("@FName", TextBox1.Text.ToString());//first Name  
12.             com.Parameters.AddWithValue("@Mname ", TextBox2.Text.ToString());//middle Name  
13.             com.Parameters.AddWithValue("@LName ", TextBox3.Text.ToString());//Last Name
14.             com.ExecuteNonQuery();
15.             con.Close();
16.             GridView1.EditIndex = -1;
17. }  

A brief introduction to the code

In the sample code above I have used the two string queries for giving the Stored Procedure name and the constr for storing the connection from the web.config file and another thing is that I have used a hidden field by which I am ing the action values that are required to our Stored Procedure.

Now our application is ready to use, press F5 or other as you know, then enter some values to TextBox and press the "Save" button.

Now after clicking on the "Save" button, the hidden field value takes the value "Insert" and es it to the Stored Procedure as the action and because of this, the Stored Procedure will execute a particular type of block.

Now at page load, I have called the method, so after that, the grid will fill as in:

Now click on the Edit button that calls the edit method as shown in the following grid:

If you click on the "Cancel" button then the edit cancel method will be called and edit mode will be canceled. Now enter some values into the grid TextBox and click on an update button that calls the update method and then the records in the GridView will be updated as in:

Now click on the delete button that calls the delete method and deletes the records from the GridView

Note

• For detailed code please download the zip file attached above.
• Don't forget to update the Web.config file for your server location. 

Summary
 

From all the examples above we see how to reduce the code required to perform these tasks. In the next article, I will explain how to Implement a 2-tier architecture that makes my code much simpler and reusable, I hope this article is useful for all students and beginners. If you have any suggestions related to this article then please contact me.