Sharing my Tech knowledge and experiences - Microsoft MVP ASP.Net: Insert/Add, Update, Edit, Delete in ASPXgridview

This code demonstrates how to Add/Insert, Update, delete, edit in Devexpress aspxgridview. Sorting and changing position of columns is by default enabled. Only to disable it you need to specify.

Note : You can download Devexpress controls from www.devexpress.com

For validation on controls visit this link

I have tried this with versions 9.2.3 and 9.3.2. For example purpose I have used connection strings and queries in same file but for you, you need to retrieve it from web.config file and also use stored procedures.

Aspx Page

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="GridviewWithAddUpdateEditDelete.aspx.cs" Inherits="GridviewWithDelete" %>

<%@ Register Assembly="DevExpress.Web.v9.2, Version=9.2.3.0, Culture=neutral, PublicKeyToken=b88d1754d700e49a"
   Namespace="DevExpress.Web.ASPxPopupControl" TagPrefix="dx" %>
<%@ Register Assembly="DevExpress.Web.ASPxGridView.v9.2, Version=9.2.3.0, Culture=neutral, PublicKeyToken=b88d1754d700e49a"
   Namespace="DevExpress.Web.ASPxGridView" TagPrefix="dx" %>
<%@ Register Assembly="DevExpress.Web.ASPxEditors.v9.2, Version=9.2.3.0, Culture=neutral, PublicKeyToken=b88d1754d700e49a"
   Namespace="DevExpress.Web.ASPxEditors" TagPrefix="dxe" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
   <title>Gridview With Delete</title>
</head>
<body>
   <form id="form1" runat="server">
   <div>
   <dxe:ASPxLabel ID="lblMessage" runat="server" Text="" ForeColor="Red" Font-Bold="true" Visible="false" />
   <dxe:ASPxButton ID="btnDelete" runat="server" Text="Delete" onclick="btnDelete_Click">
   </dxe:ASPxButton>
   <dx:ASPxGridView ID="devg" runat="server" ClientInstanceName="devg"
   KeyFieldName="intGLCode" AutoGenerateColumns="False"
   Width="554px" onafterperformcallback="devg_AfterPerformCallback"
   onrowinserting="devg_RowInserting" onrowupdating="devg_RowUpdating">
   <Columns>
   <dx:GridViewCommandColumn Caption="New" VisibleIndex="1">
   <NewButton Visible="true" />
   <EditButton Visible="true" />
   </dx:GridViewCommandColumn>
   <dx:GridViewCommandColumn Caption="Delete" ShowSelectCheckbox="True" VisibleIndex="0">
   <HeaderTemplate>
   <%--To select/unselect all rows in gridview--%>
   Delete<input type="checkbox" id="CheckBoxControl" name="SelectAllCheckbox"
   onclick="devg.SelectAllRowsOnPage(this.checked);" runat="server" />
   </HeaderTemplate>
   </dx:GridViewCommandColumn>

   <dx:GridViewDataTextColumn Caption="ID" FieldName="ID" Name="ID" Visible="false">
   </dx:GridViewDataTextColumn>
   <dx:GridViewDataTextColumn Caption="Name" FieldName="varName" Name="varName" Settings-AllowSort="False" VisibleIndex="2">
   </dx:GridViewDataTextColumn>
   <dx:GridViewDataTextColumn Caption="Contact No" FieldName="varContactNo" Name="varContactNo" Settings-AllowSort="False" VisibleIndex="3">
   </dx:GridViewDataTextColumn>
   <dx:GridViewDataTextColumn Caption="Email" FieldName="varEmail" Name="varEmail" VisibleIndex="4">
   </dx:GridViewDataTextColumn>
   <dx:GridViewDataTextColumn Caption="Address" FieldName="varAddress" Name="varAddress" VisibleIndex="5">
   </dx:GridViewDataTextColumn>
   </Columns>
   <Settings ShowStatusBar="Visible" />
   <SettingsEditing Mode="PopupEditForm" />
   </dx:ASPxGridView>
   <dx:ASPxPopupControl ID="popupSample" runat="server" ShowCloseButton="true" ShowHeader="true"
   PopupHorizontalAlign="WindowCenter" PopupVerticalAlign="WindowCenter">
   </dx:ASPxPopupControl>
   </div>
   </form>
</body>
</html>

Code behind page

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;
using System.Text;
using DevExpress.Web.Data;
using DevExpress.Web.ASPxGridView;

public partial class GridviewWithDelete : Page
{
   protected void Page_Load(object sender, EventArgs e)
   {

   }

   protected void Page_Init(object sender, EventArgs e)
   {
   BindGrid();
   }

   private void BindGrid()
   {
   SqlConnection conn = new SqlConnection("Connection string here");
   try
   {
   conn.Open();
   SqlCommand cmd;

   cmd = new SqlCommand("select ID,varName,varContactNo,varEmail,varAddress from ContactDetails");
   cmd.Connection = conn;
   DataSet dsClaimCat = new DataSet();

   SqlDataAdapter dapt = new SqlDataAdapter(cmd);
   dapt.Fill(dsClaimCat);
   devg.DataSource = dsClaimCat;
   devg.DataBind();
   conn.Close();
   }
   catch
   {
   Response.Write("Error");
   }
   }
   protected void btnDelete_Click(object sender, EventArgs e)
   {
   string strMultiIds;
   List<object> selectedRowList;

   strMultiIds = string.Empty;
   selectedRowList = (devg.GetSelectedFieldValues(new string[] { "ID" }));
   if (selectedRowList.Count == 0)
   {
   System.Windows.Forms.MessageBox.Show("Please Select Atleast one Record to Delete");
   return;
   }

   for (int i = 0; i < selectedRowList.Count; i++)
   { strMultiIds += selectedRowList[i].ToString() + ","; }

   strMultiIds.TrimEnd(',');
   try
   {
   delete(strMultiIds.TrimEnd(','));
   }
   catch(Exception ex)
   {
   lblMessage.Visible = true;
   lblMessage.Text = ex.Message;
   }
   BindGrid();
   }

   private void delete(string p)
   {
   if (p.Equals(""))
   return;
   try
   {
   deleteRows(p, "ContactDetails");
   }
   catch(Exception ex)
   {
   lblMessage.Visible = true;
   lblMessage.Text = ex.Message;
   }
   }

   private void deleteRows(string selectedid, string tablename)
   {
   StringBuilder sqlQuery = new StringBuilder();
   selectedid = selectedid.TrimEnd(',') + ",0";
   StringBuilder whereClause = new StringBuilder();
   whereClause.Append(" WHERE ID IN (" + selectedid + ") ");
   sqlQuery.Append(" DELETE FROM ");
   sqlQuery.Append(tablename + whereClause.ToString());
   try
   {
   SqlConnection conn = new SqlConnection("Connection string here");
   conn.Open();
   SqlCommand cmd;

   cmd = new SqlCommand(sqlQuery.ToString());
   cmd.Connection = conn;
   DataSet dsClaimCat = new DataSet();

   SqlDataAdapter dapt = new SqlDataAdapter(cmd);
   dapt.Fill(dsClaimCat);
   devg.DataSource = dsClaimCat;
   devg.DataBind();
   conn.Close();
   lblMessage.Visible = true;
   lblMessage.Text = "Record(s) Deleted";
   }
   catch (Exception ex)
   {
   lblMessage.Visible = true;
   lblMessage.Text = ex.Message;
   }
   }
   protected void devg_AfterPerformCallback(object sender, ASPxGridViewAfterPerformCallbackEventArgs e)
   {
   BindGrid();
   }
   protected void devg_RowUpdating(object sender, ASPxDataUpdatingEventArgs e)
   {
   StringBuilder sqlQuery = new StringBuilder();
   SqlConnection conn = new SqlConnection("Connection string here");
   sqlQuery.Append("UPDATE ContactDetails SET ");
   sqlQuery.Append("varName = '" + e.NewValues["varName"].ToString() + "',");
   sqlQuery.Append("varContactNo = '" + e.NewValues["varContactNo"].ToString() + "',");
   sqlQuery.Append("varEmail = '" + e.NewValues["varEmail"].ToString() + "',");
   sqlQuery.Append("varAddress = '" + e.NewValues["varAddress"].ToString() + "'");
   sqlQuery.Append(" WHERE ID= "+ e.Keys["ID"]+"");
   conn.Open();
   SqlCommand cmd;

   cmd = new SqlCommand(sqlQuery.ToString());
   cmd.Connection = conn;
   DataSet dsClaimCat = new DataSet();

   SqlDataAdapter dapt = new SqlDataAdapter(cmd);
   int i = cmd.ExecuteNonQuery();
   if (i > 0)
   {
   e.Cancel = true;
   devg.CancelEdit();
   }
   lblMessage.Visible = true;
   lblMessage.Text = "Record(s) Updated";
   BindGrid();
   conn.Close();
   }
   protected void devg_RowInserting(object sender, ASPxDataInsertingEventArgs e)
   {
   StringBuilder sqlQuery = new StringBuilder();
   SqlConnection conn = new SqlConnection("connection string here");
   sqlQuery.Append("INSERT INTO ContactDetails(varName,varContactNo,varEmail,varAddress) VALUES ( ");
   sqlQuery.Append("'" + e.NewValues["varName"].ToString() + "',");
   sqlQuery.Append("'" + e.NewValues["varContactNo"].ToString() + "',");
   sqlQuery.Append("'" + e.NewValues["varEmail"].ToString() + "',");
   sqlQuery.Append("'" + e.NewValues["varAddress"].ToString() + "')");
   conn.Open();
   SqlCommand cmd;

   cmd = new SqlCommand(sqlQuery.ToString());
   cmd.Connection = conn;
   DataSet dsClaimCat = new DataSet();

   SqlDataAdapter dapt = new SqlDataAdapter(cmd);
   int i = cmd.ExecuteNonQuery();
   if (i > 0)
   {
   e.Cancel = true;
   devg.CancelEdit();
   }
   lblMessage.Visible = true;
   lblMessage.Text = "Record(s) Saved";
   BindGrid();
   conn.Close();
   }

}

Sharing my Tech knowledge and experiences - Microsoft MVP ASP.Net

May 10, 2010

Insert/Add, Update, Edit, Delete in ASPXgridview - Devexpress

No comments:

Post a Comment

Followers

Tags