Search:
Welcome Guest | Register | Login
logo

Insert, update and delete in asp.net gridview step by step

We will do insert, update and delete functionality in a GridView with modal popup, we will proceed step by step to learn how easily we can complete this functionality in any project. We will try to create a basic user page where you can see all the users in a grid with paging and buttons to add new user, update an existing user and delete any existing user with confirmation box. We will always open modal popup from code behind so we can execute all the necessary methods.

First of all we will create a table Users

CREATE TABLE [Users](
  [UserID] [int] IDENTITY(100,1) NOT NULL,
  [UserName] [varchar](50) NULL,
  [Address] [varchar](100) NULL,
  [City] [varchar](50) NULL,
  [State] [varchar](50) NULL,
  CONSTRAINT [PK_Users] PRIMARY KEY  ([UserID])
)

Now we will create UserEntity class to pass data from here to there (it should be in a separate layer but we will use in the same application)

public class UserEntity
{
    public Int32 UserId { get; set; }
    public String UserName { get; set; }
    public String Address { get; set; }
    public String City { get; set; }
    public String State { get; set; }
}

We will user ajax script manager and update panel to stop page post back on every event, so add them on the page:

<asp:ScriptManager ID="ScriptManager" runat="server" />
<asp:UpdatePanel ID="upnlUsers" runat="server">
   <ContentTemplate> 

   </ContentTemplate>
</asp:UpdatePanel>

alt text

All the code we will put inside the content template.

Here is the complete page HTML:

<!—Add button to add new user -->
<div>
    <asp:Button ID="btnAdd" runat="server" Text="Add New Role" 
        OnClick="btnAdd_Click"  />
</div> 
<!— GridView code HTML to show users -->
<asp:GridView ID="gvUsers" runat="server"
    AutoGenerateColumns="false"
    Width="100%"
    CssClass="grid"
    GridLines="none"
    DataKeyNames="UserID"
    EmptyDataText = "No record found!"
    EmptyDataRowStyle-CssClass ="gvEmpty">
    <Columns>
      <asp:BoundField HeaderText="ID" DataField="UserID" />
      <asp:BoundField HeaderText="User Name" DataField="UserName" />  
      <asp:BoundField HeaderText="Address" DataField="Address" />
      <asp:BoundField HeaderText="City" DataField="City" />
      <asp:TemplateField HeaderText="Action">
        <ItemTemplate>
           <asp:ImageButton ID="ibtnEdit" runat="server"
              ImageUrl="/Images/btn-edit.png" OnClick="ibtnEdit_Click" />
           <asp:ImageButton ID="ibtnDelete" runat="server"
              ImageUrl="/Images/btn-delete.jpg"
              OnClientClick="javascript:return confirm('Do you want to delete it?');" 
              OnClick="ibtnDelete_Click" />
        </ItemTemplate>
      </asp:TemplateField>
    </Columns>
</asp:GridView>
<div id="pnlAddPopup" runat="server" style="width:500px; background-color:#ffffff;">
    <div id="popupheader" class="popuHeader">
        <asp:Label ID="lblHeader" runat="server" Text="Add New User" />
        <span style="float:right">
            <img id="imgClose" src="/Images/btn-close.png" alt="close" title="Close" />
        </span>
    </div>
    <div>
        <asp:HiddenField ID="hfUserID" runat="server" Value="0" />
        <table border="0" class="table-border" >
          <tr>
            <td>User Name</td>
            <td><asp:TextBox ID="txtUserName" runat="server" /></td>
          </tr>
          <tr>
            <td>Address</td>
            <td>
              <asp:TextBox ID="txtAddress" runat="server" 
                   TextMode="MultiLine" Rows="3" Columns="40" />
            </td>
          </tr>
          <tr>
            <td>City</td>
            <td><asp:TextBox ID="txtCity" runat="server" /></td>
          </tr>
          <tr>
            <td>State</td>
            <td><asp:TextBox ID="txtState" runat="server" /></td>
          </tr>
          <tr>
            <td>&nbsp;</td>
            <td>
               <asp:Button ID="btnSave" runat="server" Text="  Save  " 
                  OnClick="btnSave_Click" />
                    &nbsp;&nbsp;&nbsp;
               <asp:Button ID="btnCancel" runat="server" Text=" Cancel "
            OnClientClick="javascript:$find('mpeUserBehavior').hide();return false;" />
            </td>
          </tr>
        </table>
    </div>

    <ajax:ModalPopupExtender ID="mpeUser" runat="server" 
        TargetControlID="hfUserID"
        PopupControlID="pnlAddPopup" 
        BehaviorID="mpeUserBehavior"
        DropShadow="true"
        CancelControlID="imgClose" 
        PopupDragHandleControlID="popupheader"  />
</div>

Some points which you need to understand, in ModalPopupExtender we used BehaviorID which will help to find the modal popup and close it.

Image button in popup header is used as CancelControlID but we also have a cancel button in our modal popup and here is the code to close the modal popup by cancel button without postback:

 OnClientClick =”javascript:$find('mpeUserBehavior').hide();return false;”

How to add events with image button in gridview for edit and delete

  • Open page in design view
  • Select GridView and right click
  • Select Edit Template -> Column[4] – Action
  • Select the button with which you want to add event
  • Open property (press F4 to open property)
  • Select Events and double Click on Click to generate event.

Now we will see code behind methods one by one, so start from page load method:

String _connStr = ConfigurationManager
                    .ConnectionStrings["CrudConnection"].ConnectionString;
#region[Page Load event]
protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
        LoadData();
}
#endregion

We declared a global variable _connStr to read the connection string from web.config file.

 <connectionStrings>
   <add name="CrudConnection"
     connectionString="data source=my_sqlsrv;Trusted_Connection=true; 
         Initial Catalog=SampleDB"
     providerName="System.Data.SqlClient" />
 </connectionStrings>

If you can see we used if not is postback because we don’t want to load record on every postback but only for the first time, on page load.

And here is the code to load data method, which gets data from database and bind to gridview

#region[Load user data from database]
public void LoadData()
{
    using(SqlConnection conn = new SqlConnection())
    {
        conn.ConnectionString = _connStr;
        SqlCommand cmd = conn.CreateCommand();
        cmd.CommandText = "select * from users Order By userId DESC";
        cmd.CommandType = System.Data.CommandType.Text;
        DataTable dTable = new DataTable();
        // Open connection to use execute reader
        if (conn.State == ConnectionState.Closed) conn.Open();
        // I used execute reader to read data quickly
        SqlDataReader dReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
        dTable.Load(dReader);
        gvUsers.DataSource = dTable;
        gvUsers.DataBind();
    }
}
#endregion

Note, we used Sql DataReader to load data into data table because it is faster than dataset and ado data adapter.

Add New User button click event handler, which clear all the control in modal popup and show the it

#region[Add button event]
protected void btnAdd_Click(object sender, EventArgs e)
{
    ClearPopupControls();
    mpeUser.Show();
}
#endregion

#region[Clear Modal Popup controls]
private void ClearPopupControls()
{
    hfUserID.Value = "0";
    txtUserName.Text = String.Empty;
    txtAddress.Text = String.Empty;
    txtCity.Text = String.Empty;
    txtState.Text = String.Empty;
}
#endregion

Create methods to insert, get, update and delete user record from/to database

#region[Add New User record into database]
public bool AddNewUser(UserEntity user)
{
    String insertQuery = @"INSERT INTO [Users]
                  ([UserName], [Address], [City], [State])
                  VALUES(@UserName, @Address, @City, @State)";

    using (SqlConnection conn = new SqlConnection())
    {
        conn.ConnectionString = _connStr;
        SqlCommand cmd = conn.CreateCommand();
        cmd.CommandText = insertQuery;
        cmd.CommandType = System.Data.CommandType.Text;
        cmd.Parameters.AddWithValue("@UserName", user.UserName);
        cmd.Parameters.AddWithValue("@Address", user.Address);
        cmd.Parameters.AddWithValue("@City", user.City);
        cmd.Parameters.AddWithValue("@State", user.State);

        if (conn.State == ConnectionState.Closed) conn.Open();
        cmd.ExecuteNonQuery();
        return true;
    }
}

#region[Update User record into database]
public bool UpdateUser(UserEntity user)
{
    String updateQuery = @"Update [Users]
                 SET [UserName] = @UserName
                    ,[Address]  = @Address 
                    ,[City]     = @City 
                    ,[State]    = @State 
                  Where [UserID] = @UserId";

    using (SqlConnection conn = new SqlConnection())
    {
        conn.ConnectionString = _connStr;
        SqlCommand cmd = conn.CreateCommand();
        cmd.CommandText = updateQuery;
        cmd.CommandType = System.Data.CommandType.Text;
        cmd.Parameters.AddWithValue("@UserId", user.UserId);
        cmd.Parameters.AddWithValue("@UserName", user.UserName);
        cmd.Parameters.AddWithValue("@Address", user.Address);
        cmd.Parameters.AddWithValue("@City", user.City);
        cmd.Parameters.AddWithValue("@State", user.State);

        if (conn.State == ConnectionState.Closed) conn.Open();
        cmd.ExecuteNonQuery();
        return true;
    }
}
#endregion 

#region[Delete User record from database]
public bool DeleteUserByID(Int32 userId)
{
    String updateQuery = @"Delete [Users] Where [UserID] = @UserId";

    using (SqlConnection conn = new SqlConnection())
    {
        conn.ConnectionString = _connStr;
        SqlCommand cmd = conn.CreateCommand();
        cmd.CommandText = updateQuery;
        cmd.CommandType = System.Data.CommandType.Text;
        cmd.Parameters.AddWithValue("@UserId", userId);
        if (conn.State == ConnectionState.Closed) conn.Open();
        cmd.ExecuteNonQuery();
        return true;
    }
}
#endregion 

#region[Get User to show in popup for edit]
public UserEntity GetUserByID(Int32 userID)
{
    String updateQuery = @"Select * From  [Users]
                            Where [UserID] = @UserId";

    using (SqlConnection conn = new SqlConnection())
    {
        conn.ConnectionString = _connStr;
        SqlCommand cmd = conn.CreateCommand();
        cmd.CommandText = updateQuery;
        cmd.CommandType = System.Data.CommandType.Text;
        cmd.Parameters.AddWithValue("@UserId", userID);


        if (conn.State == ConnectionState.Closed) conn.Open();
        SqlDataReader dataReader =
        cmd.ExecuteReader(CommandBehavior.CloseConnection);

        DataTable dataTable = new DataTable();
        dataTable.Load(dataReader);
        UserEntity existinguser = new UserEntity();
        foreach (DataRow row in dataTable.Rows)
        {
            existinguser.UserId = Convert.ToInt32(row["UserID"]);
            existinguser.UserName = Convert.ToString(row["UserName"]);
            existinguser.Address = Convert.ToString(row["Address"]);
            existinguser.City = Convert.ToString(row["City"]);
            existinguser.State = Convert.ToString(row["State"]);
        }

        return existinguser;
    }
}
#endregion 

Save Button click event handler to save record into database, nothing is complicated, as we already seen at the time of adding new user we are setting userid to 0 means need to create a user otherwise update user.

#region[Save User]
protected void btnSave_Click(object sender, EventArgs e)
{
    UserEntity user = new UserEntity();
    user.UserId = Convert.ToInt32(hfUserID.Value);
    user.UserName = txtUserName.Text.Trim();
    user.Address = txtAddress.Text.Trim();
    user.City = txtCity.Text.Trim();
    user.State = txtState.Text.Trim();
    if (user.UserId == 0)
        AddNewUser(user);
    else
        UpdateUser(user);

    LoadData();
}
#endregion

Edit button event from grid view:

#region[Delete button event]
protected void ibtnDelete_Click(object sender, ImageClickEventArgs e)
{
    GridViewRow gvRow = (GridViewRow)((ImageButton)sender).NamingContainer;
    Int32 UserId = Convert.ToInt32(gvUsers.DataKeys[gvRow.RowIndex].Value);
    // delete and hide the row from grid view
    if (DeleteUserByID(UserId))
        gvRow.Visible = false;
}
#endregion

With the help of sender and Namingcontainer we are getting the row and then user id to delete the record but before deleting we used JavaScript confirmation box, if user confirms then we delete the record from database and this time we are not re-loading the record from database but directly hiding the row which is deleted so next time the page will refresh, that row will never come. Here is the confirmation box code with delete button in grid view

<asp:ImageButton ID="ibtnDelete" runat="server"
  ImageUrl="/Images/btn-delete.jpg"
  OnClientClick="javascript:return confirm('Do you want to delete it?');"
     OnClick="ibtnDelete_Click" />

Download source code

Hamden Process manager with a reputed organization, Fond of learning new features and technology related to C#, ASP.Net, SQL Server, MVC etc.I like to help others, if I can
  • asp.net
  • gridview
  • insert
  • update
  • delete
By Hamden
at 01 Sep, 13
Viewed: 6,580
Other blogs you may like
Comments
The ibtnEdit event is missing.
By Andy on 28 Nov 2013 04:43:35 AM
Hi-

Insert, update and delete in asp.net gridview step by step

Excellent! 

Thanks,
-Senthil
By Senthil on 28 Nov 2013 06:38:38 PM
imgbtnEdit event code missing
By Nilesh Wagh on 14 Mar 2014 04:26:28 AM
ibtnEdit_Click code is missing.
By Manish on 31 May 2014 10:58:16 AM
Hi I am Arul. i want grid view step by step process. how to create database and connect database in web application please provide me. 
By Arulvendhan on 10 Jun 2014 11:52:33 AM
Boss Edit is missing :)
By karthikeyan kanan on 30 Aug 2014 03:18:01 PM
ajax:ModalPopupExtender 
By mahi on 16 Sep 2014 02:19:47 PM
<%@ Register assembly="AjaxControlToolkit" namespace="AjaxControlToolkit" tagprefix="asp" %> 
 I have added this at the of the source but still i am getting an error....please reply..
By Jyoti on 18 Oct 2014 08:00:13 AM
I accept terms & condition as well as privacy policy!
Note: If you are writing some code in comment and after post it is not formatting properly then please refresh the page.