SQL Stored Procedure with ASP.NET Repeater Control

Step 3: The Database

Again go to the SQL Server and there create an Insert procedure, a Delete procedure, a Select procedure and an Update procedure as in the following.

1. Select procedure for Repeater control:
   
   1. create procedure sp_FillRepeaterData
   2. As
   3. Begin
   4. set nocount on;
   5. select EmpID, EmpName, Contact, EmailID from EmployeeData
   6. End
2. Insert procedure:
   
   1. create procedure sp_InsertEmployeeData
   2. @EmpName varchar(30),
   3. @Contact nchar(15),
   4. @EmailId nvarchar(50)
   5. As
   6. Begin
   7. set nocount on;
   8. Insert into dbo.EmployeeData
   9. (EmpName,Contact,EmailId)
   10. values
   11. (@EmpName,@Contact,@EmailId)
   12. End
3. Delete procedure:
   
   1. Create procedure sp_DeleteEmployeeData
   2. @EmpID int
   3. As
   4. Begin
   5. set nocount on;
   6. Delete from EmployeeData where EmpID=@EmpID
   7. End
4. Select procedure:
   
   1. create procedure sp_SelectEmployeeData
   2. @EmpID int
   3. As
   4. Begin
   5. set nocount on;
   6. select EmpID, EmpName, Contact, EmailID from EmployeeData where EmpID=@EmpID
   7. End
5. Update procedure:
   
   1. Create procedure sp_UpdateEmployeeData
   2. @EmpID int,
   3. @EmpName varchar(30),
   4. @Contact nchar(15),
   5. @EmailId nvarchar(50)
   6. As
   7. Begin
   8. set nocount off;
   9. UPDATE Employeedata SET EmpName=@EmpName,Contact=@Contact,EmailId=@EmailId WHERE EmpID=@EmpID
   10. End

Step 4: Project Web.Config

In this section maintain the database connection string.

Step 5: UI CODE

Now go to the UI code side section. In this first write code for the TextBox value insertion in the SQL table by the preceding Insert Procedure. Then write code for the inserted record display in the Repeater control. Then write the code for two image buttons, one the delete and the second is an update button code. Write it for the data delete and update in the database records. Both code writes on the Repeater Control an Itemcommand. Itemcommand is created as in the following figure.


Figure 2: Add Item Command

Then create two action commands, update and delete, inside the ItemCommand Event.

This is a UI back side code.

1. using System;
2. using System.Collections.Generic;
3. using System.Linq;
4. using System.Web;
5. using System.Web.UI;
6. using System.Web.UI.WebControls;
7. using System.Web.UI.WebControls.WebParts;
8. using System.Web.UI.HtmlControls;
9. using System.Configuration;
10. using System.Data;
11. using System.Data.SqlClient;
13. public partial class UI_Repeater_ : System.Web.UI.Page
14. {
15. string constr = ConfigurationManager.ConnectionStrings["connstr"].ConnectionString;
16. protected void Page_Load(object sender, EventArgs e)
17. {
18. if (!Page.IsPostBack)
19. {
20. GetEmployeeDetail();
21. }
22. }
23. protected void btnSave_Click(object sender, EventArgs e)
24. {
25. using (SqlConnection con = new SqlConnection(constr))
26. {
27. using (SqlCommand cmd = new SqlCommand("sp_InsertEmployeeData", con))
28. {
29. cmd.CommandType = CommandType.StoredProcedure;
30. con.Open();
31. cmd.Parameters.AddWithValue("@EmpName",SqlDbType.VarChar).Value=txtName.Text.Trim();
32. cmd.Parameters.AddWithValue("@Contact", SqlDbType.NChar).Value = txtContact.Text.Trim();
33. cmd.Parameters.AddWithValue("@EmailId", SqlDbType.NVarChar).Value = txtEmail.Text.Trim();
34. cmd.ExecuteNonQuery();
35. con.Close();
37. txtName.Text = String.Empty;
38. txtContact.Text = String.Empty;
39. txtEmail.Text = String.Empty;
40. }
41. }
42. GetEmployeeDetail();
43. }
45. void GetEmployeeDetail()
46. {
47. SqlConnection con = new SqlConnection(constr);
49. SqlCommand cmd = new SqlCommand("sp_FillRepeaterData",con);
50. cmd.CommandType = CommandType.StoredProcedure;
52. con.Open();
53. RPTEmployee.DataSource = cmd.ExecuteReader();
54. RPTEmployee.DataBind();
55. }
57. protected void RPTEmployee_ItemCommand(object source, RepeaterCommandEventArgs e)
58. {
59. switch (e.CommandName)
60. {
61. case ("Delete"):
62. int EmpID = Convert.ToInt32(e.CommandArgument);
63. deleteEmployee(EmpID);
64. break;
65. case ("Update"):
66. EmpID = Convert.ToInt32(e.CommandArgument);
67. EditEmployeeDetail(EmpID);
68. break;
69. }
70. }
71. void deleteEmployee(int EmpID)
72. {
73. SqlConnection con = new SqlConnection(constr);
74. SqlCommand cmd = new SqlCommand("sp_DeleteEmployeeData",con);
75. cmd.CommandType = CommandType.StoredProcedure;
77. cmd.Parameters.AddWithValue("@EmpID", EmpID);
78. con.Open();
79. cmd.ExecuteNonQuery();
80. con.Close();
81. GetEmployeeDetail();
82. }
84. void EditEmployeeDetail(int EmpID)
85. {
86. SqlConnection con = new SqlConnection(constr);
87. SqlCommand cmd = new SqlCommand("sp_SelectEmployeeData",con);
88. cmd.CommandType = CommandType.StoredProcedure;
89. cmd.Parameters.AddWithValue("@EmpID", EmpID);
91. con.Open();
92. SqlDataReader dr = cmd.ExecuteReader();
93. if (dr.HasRows)
94. {
95. dr.Read();
96. hfRecord.Value = dr["EmpID"].ToString();
97. txtName.Text = dr["EmpName"].ToString();
98. txtContact.Text = dr["Contact"].ToString();
99. txtEmail.Text = dr["EmailId"].ToString();
100. }
101. dr.Dispose();
103. con.Close();
104. btnSave.Visible = false;
105. btnUpdate.Visible = true;
107. }
108. protected void btnUpdate_Click(object sender, EventArgs e)
109. {
110. SqlConnection con = new SqlConnection(constr);
111. SqlCommand cmd = new SqlCommand("sp_UpdateEmployeeData",con);
112. cmd.CommandType = CommandType.StoredProcedure;
114. cmd.Parameters.AddWithValue("@EmpID", Convert.ToInt32(hfRecord.Value));
115. cmd.Parameters.AddWithValue("@EmpName", SqlDbType.VarChar).Value = txtName.Text.Trim();
116. cmd.Parameters.AddWithValue("@Contact", SqlDbType.NChar).Value = txtContact.Text.Trim();
117. cmd.Parameters.AddWithValue("@EmailId", SqlDbType.NVarChar).Value = txtEmail.Text.Trim();
119. con.Open();
120. cmd.ExecuteNonQuery();
121. con.Close();
123. GetEmployeeDetail();
125. btnSave.Visible = true;
126. btnUpdate.Visible = false;
128. hfRecord.Value = string.Empty;
129. txtName.Text = String.Empty;
130. txtContact.Text = String.Empty;
131. txtEmail.Text = String.Empty;
132. }
133. }

Step 6: Browser Side

Now run your new page in any browser.



Figure 3: Fill Record and Save

Press the Save button and save the data into the table and the inserted record is displayed in the browser using the repeater control as in the following.


Figure 4: Display Inserted Record



Figure 5: Check in Database

Now we will try to delete record number 18 with the Name Rushi.


Figure 6: Record Delete Massage

Now you will see that the deleted record is not available in the database and not displayed on the page.

After trying to update a record as in the following, record number 17 for Rakesh is replaced with the name Rakesh Chavda.



Figure 7: Record Update



Figure 8: Record Update Successfully

And also check in the data table.


Figure 9: Record Update and Delete in SQL

See, inside the database, the deleted record is not there and the Employee Name is updated.

I hope you understood how to work with the Repeater control and SQL Procedures.

Have a Nice Day.