Repeater Edit,Update,Delete in Asp .net using Ado .net

This post will explains how to insert data,Display data in Repeater control, create edit and delete functionality in repeater control using ado .net.

Repeater is one of my favorite databound controls to display repeated data. It allows you to do lots of design customizations.
You can create Tables,Unorderd lists, Ordered lists, anchor tags, database based menus, Image galleries etc. using repeater control.


Lets begin:

1) First of all we need database to insert, update, delete and display records in repeater control.Create Database "EmployeeTest"(You can choose your prefered database name).

2) Create a table named "Employee" (You can also change table name as per your needs).

 

CREATE TABLE Employee(
[Id] [int] PRIMARY KEY IDENTITY(1,1),
[Name] [nvarchar](50) NULL,
[Email] [nvarchar](50) NULL,
[Age] [int] NULL,
[Salary] [float] NULL)

 

3) Make connection string and pass database information.

 


4) Make HTML form to input data.
Note: I have not used any validations for this demo.You can use your validations accordingly.

Repeater-add-edit-delete-codingfusion.com

 

     
Name
Email
Age
Salary
<%--//======= By default we have made update button visible false. So that when ever page loads first time only save and cancel buttons are visible.--%>

5) Now we will do coding to save our records into the database.

 //=-=-=-=-= Save Button
    protected void btnSave_Click(object sender, EventArgs e)
    {

        //====== Getting connection string defined in the web.config file. Pointed to the database we want to use.
        SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["EmployeeConnection"].ConnectionString);

        //======= Insert Query.
        string cmdText = "INSERT INTO employee (Name,Email,Age,Salary) VALUES (@Name,@Email,@Age,@Salary)";

        //====== Providning information to SQL command object about which query to 
        //====== execute and from where to get database connection information.
        SqlCommand cmd = new SqlCommand(cmdText, con);

        //===== Adding parameters/Values.
        cmd.Parameters.AddWithValue("@Name", txtName.Text);
        cmd.Parameters.AddWithValue("@Email", txtEmail.Text);
        cmd.Parameters.AddWithValue("@Age", Convert.ToInt32(txtAge.Text));
        cmd.Parameters.AddWithValue("@Salary", Convert.ToDouble(txtSalary.Text));

        //===== To check current state of the connection object. If it is closed open the connection
        //===== to execute the insert query.
        if (con.State == ConnectionState.Closed)
        {
            con.Open();
        }

        //===== Execute Query.
        cmd.ExecuteNonQuery();

        //===== close the connection.
        con.Close();

        //===== Clear text from textboxes
        clearInputControls();

        //===== Bind data to repeater so that repeater will display updated data.
        bindEmployeeDetailsToRepeater();

    }

6) We have successfully saved our data to database. Now lets create repeater control.

 

    
        
                
                    ">
                        <%--//=-=-=-= For more information on this line of code you can refer:--%>
                        http://www.codingfusion.com/Post/How-to-set-row-background-color-or-apply-Css-to-da
                        
                        <%--//=-=-=-= For more information on this line of code you can refer:--%>
                        http://www.codingfusion.com/Post/How-to-show-row-number-in-Gridview--Repeater-ListV
                        
Sno Name Email Age Salary Action
<%#Container.ItemIndex+1%> <%#Eval("Name") %> <%#Eval("Email") %> <%#Eval("Age") %> <%#Eval("Salary") %> <%--//==== Here we have used CommandName property to distinguish which button is clicked and we have passed our primary key to CommandArgument property. ====//--%>

7) Create some required methods which we have used in save functionality i.e. "bindEmployeeDetailsToRepeater()" and "clearInputControls" Methods.bindEmployeeDetailsToRepeater() method is used to bind records into database and
"clearInputControls" is used to empty textboxes data after save operation.

 

//===== Method to bind employee records to repeater control.
    void bindEmployeeDetailsToRepeater()
    {
        //====== Getting connection string defined in the web.config file. Pointed to the database we want to use.
        SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["EmployeeConnection"].ConnectionString);

        //======= Select Query.
        string cmdText = "SELECT * FROM employee";

        //====== Providning information to SQL command object about which query to 
        //====== execute and from where to get database connection information.
        SqlCommand cmd = new SqlCommand(cmdText, con);

        //===== To check current state of the connection object. If it is closed open the connection
        //===== to execute the insert query.
        if (con.State == ConnectionState.Closed)
        {
            con.Open();
        }

        //===== Execute Query and bind data to repeater.
        repEmployeeDetails.DataSource = cmd.ExecuteReader();
        repEmployeeDetails.DataBind();
    }

    //===== Clear Inut control's data.
    void clearInputControls()
    {
        txtAge.Text = string.Empty;
        txtEmail.Text = string.Empty;
        txtName.Text = string.Empty;
        txtSalary.Text = string.Empty;
    }


8) Call "bindEmployeeDetailsToRepeater()" in the pageload so that data get binds to repeater when page loads first time.

 

if (!Page.IsPostBack)
        {
            //===== To bind employee's records from database.
            bindEmployeeDetailsToRepeater();
        }

9) Now we will create edit and delte functionality so that whenever edit button is clicked corresponding data will inserted into related textboxes.For this we will use repeater's "OnItemCommand" Event.

 

//========= Edit,Delete buttons inside repeater.
    protected void repEmployeeDetails_ItemCommand(object source, RepeaterCommandEventArgs e)
    {
        //====== Here we use switch state to distinguish which link button is clicked based 
        //====== on command name supplied to the link button.
        switch (e.CommandName)
        {
            //==== This case will fire when link button placed
            //==== inside repeater having command name "Delete" is clicked.

            case ("Delete"):
                //==== Getting id of the selelected record(We have passed on link button's command argument property).
                int id = Convert.ToInt32(e.CommandArgument);

                //==== Call delete method and pass id as argument.
                deleteEmployee(id);

                break;

            //==== This case will fire when link button placed
            //==== inside repeater having command name "Edit" is clicked.
            case ("Edit"):

                //==== Getting id of the selelected record(We have passed on link button's command argument property).
                id = Convert.ToInt32(e.CommandArgument);

                //==== Call delete method and pass id as argument.
                bindEmployeeDetailToEdit(id);

                break;

        }
    }

    //===== Method to delete employee from database.
    void deleteEmployee(int id)
    {
        //====== Getting connection string defined in the web.config file. Pointed to the database we want to use.
        SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["EmployeeConnection"].ConnectionString);

        //======= Delete Query.
        string cmdText = "DELETE FROM employee WHERE Id=@Id";

        //====== Providning information to SQL command object about which query to 
        //====== execute and from where to get database connection information.
        SqlCommand cmd = new SqlCommand(cmdText, con);

        //===== Adding parameters/Values.
        cmd.Parameters.AddWithValue("@Id", id);

        //===== To check current state of the connection object. If it is closed open the connection
        //===== to execute the insert query.
        if (con.State == ConnectionState.Closed)
        {
            con.Open();
        }

        //===== Execute Query.
        cmd.ExecuteNonQuery();

        //===== close the connection.
        con.Close();

        //===== Bind data to repeater so that repeater will display updated data.
        bindEmployeeDetailsToRepeater();
    }

    //===== Method to bind relevant data into form fields when edit button is clicked.
    void bindEmployeeDetailToEdit(int id)
    {
        //====== Getting connection string defined in the web.config file. Pointed to the database we want to use.
        SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["EmployeeConnection"].ConnectionString);

        //======= Parameterized select Query.
        string cmdText = "SELECT * FROM employee WHERE Id=@Id";

        //====== Providning information to SQL command object about which query to 
        //====== execute and from where to get database connection information.
        SqlCommand cmd = new SqlCommand(cmdText, con);

        //===== Adding parameters/Values.
        cmd.Parameters.AddWithValue("@Id", id);

        //===== To check current state of the connection object. If it is closed open the connection
        //===== to execute the insert query.
        if (con.State == ConnectionState.Closed)
        {
            con.Open();
        }

        //===== Execute Query.
        SqlDataReader dr = cmd.ExecuteReader();

        //===== Check if data present.
        if (dr.HasRows)
        {
            //===== Read data from datareader.
            dr.Read();

            //===== Bind values to textboxes.
            txtName.Text = dr["Name"].ToString();
            txtEmail.Text = dr["Email"].ToString();
            txtAge.Text = dr["Age"].ToString();
            txtSalary.Text = dr["Salary"].ToString();

            //==== Store primary key of the selected record in hiddenfield for future reference.
            //==== This will help us when we write update method.
            hfSelectedRecord.Value = dr["id"].ToString();

        }
        dr.Dispose();

        //===== close the connection.
        con.Close();

        //==== Show Update button and hide Save button.
        btnSave.Visible = false;
        btnUpdate.Visible = true;

    }


10) Create update functionality.

 //========= Update Button
    protected void btnUpdate_Click(object sender, EventArgs e)
    {
        //====== Getting connection string defined in the web.config file. Pointed to the database we want to use.
        SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["EmployeeConnection"].ConnectionString);

        //======= Insert Query.
        string cmdText = "UPDATE employee SET Name=@Name,Email=@Email,Age=@Age,Salary=@Salary WHERE Id=@Id";

        //====== Providning information to SQL command object about which query to 
        //====== execute and from where to get database connection information.
        SqlCommand cmd = new SqlCommand(cmdText, con);

        //===== Adding parameters/Values.
        cmd.Parameters.AddWithValue("@Name", txtName.Text);
        cmd.Parameters.AddWithValue("@Email", txtEmail.Text);
        cmd.Parameters.AddWithValue("@Age", Convert.ToInt32(txtAge.Text));
        cmd.Parameters.AddWithValue("@Salary", Convert.ToDouble(txtSalary.Text));

        //====== Remember we have stored primary key in hiddenfield during 
        //====== binding values into textboxes method:(bindEmployeeDetailToEdit).
        //====== We will use same id to pass id parameter.
        cmd.Parameters.AddWithValue("@Id", Convert.ToInt32(hfSelectedRecord.Value));


        //===== To check current state of the connection object. If it is closed open the connection
        //===== to execute the insert query.
        if (con.State == ConnectionState.Closed)
        {
            con.Open();
        }

        //===== Execute Query.
        cmd.ExecuteNonQuery();

        //===== close the connection.
        con.Close();

        //===== Clear text from textboxes
        clearInputControls();

        //===== Bind data to repeater so that repeater will display updated data.
        bindEmployeeDetailsToRepeater();

        //===== Show Save button and hide update button.
        btnSave.Visible = true;
        btnUpdate.Visible = false;

        //===== Clear Hiddenfield
        hfSelectedRecord.Value = string.Empty;
    }

11) Create CSS to give repeater some good looks.

 

Final output:

Best quality Asp .Net Ajax Control Toolkit tutorials.
Thank you so much Mr. Anuj for this wonderful post.. it helped me better understand .
13-Jan-2014 From  shweta sharma
Thanks Shweta glad to help you.
15-Jan-2014 From  Anuj
Thank you for the great concept but when i have tried myself ... it is not binding with the textboxes on edit button... can you please help me in this matter... i don't actually understand the reason... I think after postback it restore the default positions of all the controls... suggest me some this better...thanks in advance
17-Jan-2014 From  Arslan
I cant figure it out without viewing your code... I'll try to post source code soon
28-Jan-2014 From  Anuj
Thank u for the coding u have given above and plz clarify my doubt, i have created a Registration Form in which i had given First Name,Last Name,User Name,Date of Birth and Email....After Editing these columns (First Name ,Last Name,Date of Birth,Email),when i click the update button it is not updating the edited information.....plz clarify the doubt Thanks in advance
7-Mar-2014 From  Kisan
Hi @Kisan, Kindly send me your code. I'll check.
6-Apr-2014 From  Anuj
Can you please keep the download option for the code Repeater Edit,Update,Delete in Asp .net using Ado .net.
16-Feb-2015 From  Mahesh Aelakurthi
Thanks for pointing the issue. You can now download the source code from download button.
18-Feb-2015 From  Anuj

Give your valuable comments.

Name
Email
Comment
1 + 7 =
 

About Us | Terms of Use | Privacy Policy | Disclaimer | Contact Us Copyright © 2012-2024 CodingFusion
50+ C# Programs for beginners to practice