SQL Stored Procedure with ASP.NET Repeater Control

This is all about operations on SQL tables. So I will explain step-by-step how to insert, edit and delete operations with a Repeater.

Step 1: SQL Database

First create a table in a database store to insert a data value into a table as follows.

Create Table EmployeeData

Create Table EmployeeData
(
   EmpID int identity (1,1) Primary Key,
   EmpName varchar(30),
   Contact nchar(15),
   EmailId nvarchar(50)
)

In this new table the EmpID column is an auto-increment defined for Employee Identity.

Step 2: Visual Studio

Create a UI Design inside Visual Studio using the following procedure: 

• Go to Solution Explorer.
• Right-click on the project and click the Add tab
• Click the Add New Item as in the following:
  
  
  Figure 1: Add Web Form

Now I will write the design code inside Repeater.aspx. In this page add some TextBox controls, Buttons and a Repeater Control.

First create a TextBox Control, Button Control, Repeater Control, Image button and hidden field control. First take the TextBox control for the data to be filled on the UI page.

Also add the Repeater control for the Bind Employee record from the SQL Database and display it in the Repeater Control. Also two image button controls inside the Repeater control for the Repeater data row delete and update. The following is the UI design code.

<%@ Page Title="" Language="C#" MasterPageFile="~/Master/Master.master" AutoEventWireup="true" CodeFile="Repeater .aspx.cs" Inherits="UI_Repeater_" %>

<asp:Content ID="Content1" ContentPlaceHolderID="head" Runat="Server">
</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">


    <div>
        <fieldset style="width: 255px"><legend>Example</legend>
        <asp:Table runat="server" >
            <asp:TableRow>
                <asp:TableCell>Name</asp:TableCell><asp:TableCell><asp:TextBox runat="server" ID="txtName"></asp:TextBox></asp:TableCell>
            </asp:TableRow>
            <asp:TableRow>
                <asp:TableCell>Contact</asp:TableCell><asp:TableCell><asp:TextBox runat="server" ID="txtContact"></asp:TextBox></asp:TableCell>
            </asp:TableRow>
            <asp:TableRow>
                <asp:TableCell>Email</asp:TableCell><asp:TableCell><asp:TextBox runat="server" ID="txtEmail"></asp:TextBox></asp:TableCell>
            </asp:TableRow>
           <asp:TableRow>
               <asp:TableCell></asp:TableCell>
               <asp:TableCell>
                   <asp:Button runat="server" ID="btnSave" Text="SAVE" OnClick="btnSave_Click" />
                    <asp:Button runat="server" ID="btnUpdate" Text="Update" OnClick="btnUpdate_Click" />
               </asp:TableCell>
       </asp:TableRow>
      </asp:Table>
       </fieldset>
    </div>
<h3>Employee Information</h3>
    <asp:HiddenField ID="hfRecord" runat="server" />
    <div>
        <asp:repeater id="RPTEmployee"  runat="server" OnItemCommand="RPTEmployee_ItemCommand" >
            <itemtemplate>
       <table >
           <tr>
                        <td style =" width : 100px">
                            <b>No. :</b>
                            <%#Eval("EmpID") %>
                        </td>
                        <td style =" width : 200px">
                            <b>Name :</b>
                            <%#Eval("EmpName") %>
                        </td>
                        <td style =" width : 200px">
                            <b>Contact :</b>
                            <%#Eval("Contact") %>
                        </td >
                        <td style =" width : 250px">
                            <b>Email :</b>
                            <%#Eval("EmailId") %>
                        </td>
                    <td>
                  <asp:ImageButton ID="btndelete" runat="server" ImageUrl="~/Images/Delete.jpg" tooltip="Delete a record" onclientclick="javascript:return confirm('Are you sure to delete record?')" CommandName="Delete" CommandArgument='<%# DataBinder.Eval(Container.DataItem, "EmpID") %>' />
                  <asp:ImageButton ID="btnupdate" runat="server" ImageUrl="~/Images/Update.jpg" tooltip="Update a record" CommandName="Update" CommandArgument='<%# DataBinder.Eval(Container.DataItem, "EmpID") %>' />
               </td>
            </tr>
       </table>
       </itemtemplate>
  </asp:repeater>
</div>
</asp:Content>

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:
   

   create procedure sp_FillRepeaterData
   As
   Begin
   set nocount on;
   select EmpID, EmpName, Contact, EmailID from EmployeeData
   End

2. Insert procedure:
   

   create procedure sp_InsertEmployeeData
   @EmpName varchar(30),
   @Contact nchar(15),
   @EmailId nvarchar(50)
   As
   Begin
   set nocount on;
   Insert into dbo.EmployeeData
   (EmpName,Contact,EmailId)
   values
   (@EmpName,@Contact,@EmailId)
   End

3. Delete procedure:
   

   Create procedure sp_DeleteEmployeeData
   @EmpID int
   As
   Begin
   set nocount on;
   Delete from EmployeeData where EmpID=@EmpID
   End

4. Select procedure:
   

   create procedure sp_SelectEmployeeData
   @EmpID int
   As
   Begin
   set nocount on;
   select EmpID, EmpName, Contact, EmailID from EmployeeData where EmpID=@EmpID
   End

5. Update procedure:
   

   Create procedure sp_UpdateEmployeeData
   @EmpID int,
   @EmpName varchar(30),
   @Contact nchar(15),
   @EmailId nvarchar(50)
   As
   Begin
   set nocount off;
   UPDATE Employeedata SET EmpName=@EmpName,Contact=@Contact,EmailId=@EmailId WHERE EmpID=@EmpID
   End

Step 4: Project Web.Config

In this section maintain the database connection string.

<connectionStrings>
   <add name="connstr" connectionString="Data Source=RAKESH-PC;Initial Catalog=SqlServerTech;User ID=sa;Password=password" providerName="System.Data.SqlClient"/>
</connectionStrings>

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.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;

public partial class UI_Repeater_ : System.Web.UI.Page
{
    string constr = ConfigurationManager.ConnectionStrings["connstr"].ConnectionString;
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!Page.IsPostBack)
        {
            GetEmployeeDetail();
        }
   }
    protected void btnSave_Click(object sender, EventArgs e)
    {
        using (SqlConnection con = new SqlConnection(constr))
        {
            using (SqlCommand cmd = new SqlCommand("sp_InsertEmployeeData", con))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                con.Open();
                cmd.Parameters.AddWithValue("@EmpName",SqlDbType.VarChar).Value=txtName.Text.Trim();
                cmd.Parameters.AddWithValue("@Contact", SqlDbType.NChar).Value = txtContact.Text.Trim();
                cmd.Parameters.AddWithValue("@EmailId", SqlDbType.NVarChar).Value = txtEmail.Text.Trim();
                cmd.ExecuteNonQuery();
                con.Close();

                txtName.Text = String.Empty;
                txtContact.Text = String.Empty;
                txtEmail.Text = String.Empty;
            }
        }
        GetEmployeeDetail();
    }

    void GetEmployeeDetail()
    {
        SqlConnection con = new SqlConnection(constr);

        SqlCommand cmd = new SqlCommand("sp_FillRepeaterData",con);
        cmd.CommandType = CommandType.StoredProcedure;

       con.Open();
       RPTEmployee.DataSource = cmd.ExecuteReader();
       RPTEmployee.DataBind();
    }

    protected void RPTEmployee_ItemCommand(object source, RepeaterCommandEventArgs e)
    {
        switch (e.CommandName)
        {
            case ("Delete"):
                int EmpID = Convert.ToInt32(e.CommandArgument);
                deleteEmployee(EmpID);
                break;
            case ("Update"):
               EmpID = Convert.ToInt32(e.CommandArgument);
                EditEmployeeDetail(EmpID);
                break;
        }
    }
    void deleteEmployee(int EmpID)
    {
        SqlConnection con = new SqlConnection(constr);
        SqlCommand cmd = new SqlCommand("sp_DeleteEmployeeData",con);
        cmd.CommandType = CommandType.StoredProcedure;

        cmd.Parameters.AddWithValue("@EmpID", EmpID);
        con.Open();
        cmd.ExecuteNonQuery();
        con.Close();
        GetEmployeeDetail();
    }

    void EditEmployeeDetail(int EmpID)
    {
        SqlConnection con = new SqlConnection(constr);
        SqlCommand cmd = new SqlCommand("sp_SelectEmployeeData",con);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@EmpID", EmpID);

        con.Open();
        SqlDataReader dr = cmd.ExecuteReader();
        if (dr.HasRows)
        {
            dr.Read();
            hfRecord.Value = dr["EmpID"].ToString();
            txtName.Text = dr["EmpName"].ToString();
            txtContact.Text = dr["Contact"].ToString();
            txtEmail.Text = dr["EmailId"].ToString();
        }
        dr.Dispose();

        con.Close();
        btnSave.Visible = false;
        btnUpdate.Visible = true;

    }
    protected void btnUpdate_Click(object sender, EventArgs e)
    {
        SqlConnection con = new SqlConnection(constr);
        SqlCommand cmd = new SqlCommand("sp_UpdateEmployeeData",con);
        cmd.CommandType = CommandType.StoredProcedure;

        cmd.Parameters.AddWithValue("@EmpID", Convert.ToInt32(hfRecord.Value));
        cmd.Parameters.AddWithValue("@EmpName", SqlDbType.VarChar).Value = txtName.Text.Trim();
        cmd.Parameters.AddWithValue("@Contact", SqlDbType.NChar).Value = txtContact.Text.Trim();
        cmd.Parameters.AddWithValue("@EmailId", SqlDbType.NVarChar).Value = txtEmail.Text.Trim();

        con.Open();
        cmd.ExecuteNonQuery();
        con.Close();

        GetEmployeeDetail();

        btnSave.Visible = true;
        btnUpdate.Visible = false;

        hfRecord.Value = string.Empty;
        txtName.Text = String.Empty;
        txtContact.Text = String.Empty;
        txtEmail.Text = String.Empty;
    }
}

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.