How to perform Inline editing in the gridview?
How to perform Inline editing in the gridview? In this article i am going to explain you the way of performing the inline editing of the gridview row using the stored procedure and layer
architecture.
In this article we will see how can we perform the binding of the gridview and perform updation . We will perform it using the stored procedure and the layared architecture.
Updating if the gridivew is perofrmed in the RowEditing event of the gridview where we will find the textboxes during the edit mode and the values inside it. Then we will perform operation. For this first we will create a table and stored procedure like:
CREATE TABLE [dbo].[VotingCriteria](
[CriteriaID] [int] IDENTITY(1,1) NOT NULL,
[VoteTitle] [nvarchar](50) NULL,
[NoOfCandidates] [int] NULL,
[CandidateCount] [int] NULL,
[StartDate] [datetime] NULL,
[EndDate] [datetime] NULL,
[ResultDate] [datetime] NULL,
[IsActive] [bit] NULL
) ON [PRIMARY]
And stored procedures are:
Create procedure [dbo].[GetVotingCriteria]
as
begin
select * from VotingCriteria where IsActive = '1'
end
CREATE PROCEDURE [dbo].[UpdateVotingCriteria]
@CriteriaID int,
@VoteTitle nvarchar(50),
@NoOfCandidates int,
@StartDate datetime,
@EndDate datetime,
@ResultDate datetime
WITH EXECUTE AS CALLER
AS
BEGIN
IF NOT EXISTS(SELECT * FROM VotingCriteria WHERE VoteTitle = @VoteTitle and StartDate = @StartDate and CriteriaID = @CriteriaID)
Begin
UPDATE VotingCriteria SET
VoteTitle = @VoteTitle,
NoOfCandidates = @NoOfCandidates,
StartDate = @StartDate,
EndDate = @EndDate,
ResultDate = @ResultDate
WHERE CriteriaID = @CriteriaID
End
END
CREATE PROCEDURE [dbo].[DeleteVotingCriteria]
@CriteriaID int = NULL
WITH EXECUTE AS CALLER
AS
BEGIN
UPDATE VotingCriteria SET
IsActive = '0'
WHERE CriteriaID = @CriteriaID AND IsActive = '1'
END
Now we will design our page like:
<asp:GridView ID="grdvotedetail" runat="server" AutoGenerateColumns="False"
AllowPaging="true" PageSize="5" DataKeyNames="CriteriaID"
CellPadding="4" GridLines="Both" OnPageIndexChanging="grdvotedetail_PageIndexChanging"
OnRowDeleting="grdvotedetail_RowDeleting"
OnRowCancelingEdit="grdvotedetail_RowCancelingEdit" OnRowEditing="grdvotedetail_RowEditing"
OnRowUpdating="grdvotedetail_RowUpdating">
<Columns>
<asp:TemplateField HeaderText="ID" Visible="false">
<ItemTemplate>
<asp:Label ID="lblId" runat="server" Text='<%# Eval("CriteriaID") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Vote Title">
<ItemTemplate>
<%# Eval("VoteTitle") %></ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="txtVoteTitle" runat="server" Text='<%# Eval("VoteTitle") %>'></asp:TextBox>
</EditItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="No. Of Candidates">
<ItemTemplate>
<%# Eval("NoOfCandidates") %></ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="txtNoOfCandidates" runat="server" Text='<%# Eval("NoOfCandidates") %>'></asp:TextBox>
</EditItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Start Date">
<ItemTemplate>
<%# Eval("StartDate") %></ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="txtStartDate" runat="server" Text='<%#Eval("StartDate") %>'></asp:TextBox>
<asp:CalendarExtender ID="txtStartDate_CalendarExtender" runat="server" Enabled="True"
Format="dd/MM/yyyy" TargetControlID="txtStartDate">
</asp:CalendarExtender>
</EditItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="End Date">
<ItemTemplate>
<%# Eval("EndDate") %></ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="txtEndDate" runat="server" Text='<%#Eval("EndDate") %>'></asp:TextBox>
<asp:CalendarExtender ID="txtEndDate_CalendarExtender" runat="server" Enabled="True"
Format="dd/MM/yyyy" TargetControlID="txtEndDate">
</asp:CalendarExtender>
</EditItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Result Date">
<ItemTemplate>
<%# Eval("ResultDate") %></ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="txtResultDate" runat="server" Text='<%#Eval("ResultDate") %>'></asp:TextBox>
<asp:CalendarExtender ID="txtResultDate_CalendarExtender" runat="server" Enabled="True"
Format="dd/MM/yyyy" TargetControlID="txtResultDate">
</asp:CalendarExtender>
</EditItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Edt/Delete">
<ItemTemplate>
<asp:ImageButton ID="ImageButton1" runat="server" Width="25" Height="25" CommandName="Edit"
ImageUrl="~/Images/edit_ico.png" />
<asp:ImageButton ID="ImageButton2" runat="server" CommandName="Delete" ImageUrl="~/Images/delete_ico.png"
OnClientClick="return confirm('Are you sure want to delete record?')" />
</ItemTemplate>
<EditItemTemplate>
<asp:LinkButton ID="LinkButton1" runat="server" CommandName="Update">Update</asp:LinkButton>
<asp:LinkButton ID="LinkButton2" runat="server" CommandName="Cancel">Cancel</asp:LinkButton>
</EditItemTemplate>
</asp:TemplateField>
</Columns>
<RowStyle CssClass="grdrowstyle" />
<HeaderStyle CssClass="grdheaderstyle" />
</asp:GridView>
Now the common DAL layer code is:
string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString.Trim();
public DAL()
{
//
// TODO: Add constructor logic here
//
}
public enum executemethods
{
ExecuteReader,
ExecuteScalar,
ExecuteNonQuery
}
public object connectedMethod(SqlCommand sqlcmd, executemethods e)
{
object obj = null;
SqlConnection con = new SqlConnection(constr);
sqlcmd.Connection = con;
con.Open();
if (e == executemethods.ExecuteNonQuery)
{
obj = sqlcmd.ExecuteNonQuery();
con.Close();
}
if (e == executemethods.ExecuteReader)
{
SqlDataReader dr = sqlcmd.ExecuteReader(CommandBehavior.CloseConnection);
obj = dr;
return obj;
}
if (e == executemethods.ExecuteScalar)
{
obj = sqlcmd.ExecuteScalar();
con.Close();
}
return obj;
}
public DataSet DisconectedMethod(SqlCommand sqlcmd)
{
DataSet ds = new DataSet();
SqlConnection con = new SqlConnection(constr);
sqlcmd.Connection = con;
SqlDataAdapter da = new SqlDataAdapter(sqlcmd);
da.Fill(ds);
return ds;
}
Now after that in the code behind page we will write below code:
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
BindGrid();
}
}
protected void grdvotedetail_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
try
{
Label lblId = (Label)grdvotedetail.Rows[e.RowIndex].Cells[0].FindControl("lblId");
SqlCommand cmd = new SqlCommand("DeleteVotingCriteria");
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@CriteriaID", SqlDbType.Int, 4).Value = Convert.ToInt32(lblId.Text);
DAL dal = new DAL();
DataSet ds = dal.DisconectedMethod(cmd);
lblmsg.Text = "Deleted Sucessfully";
grdvotedetail.EditIndex = -1;
BindGrid();
if (ds.Tables[0].Rows.Count > 0)
{
grdvotedetail.Visible = true;
lblmsg.Visible = false;
grdvotedetail.DataSource = ds.Tables[0];
grdvotedetail.DataBind();
ViewState["VotingCriteria"] = ds;
}
else
{
grdvotedetail.Visible = false;
lblmsg.Visible = true;
lblmsg.Text = "No Record Found";
}
}
catch (Exception ex)
{
lblmsg.Text = ex.Message.ToString();
}
}
private void BindGrid()
{
try
{
SqlCommand cmd = new SqlCommand("GetVotingCriteria");
cmd.CommandType = CommandType.StoredProcedure;
DAL dal = new DAL();
DataSet ds = dal.DisconectedMethod(cmd);
if (ds.Tables[0].Rows.Count > 0)
{
grdvotedetail.Visible = true;
lblmsg.Visible = false;
grdvotedetail.DataSource = ds.Tables[0];
grdvotedetail.DataBind();
ViewState["VotingCriteria"] = ds;
}
else
{
grdvotedetail.Visible = false;
lblmsg.Visible = true;
lblmsg.Text = "No Record Found";
}
}
catch (Exception ex)
{
lblmsg.Text = ex.Message.ToString();
}
}
protected void grdvotedetail_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
{
grdvotedetail.EditIndex = -1;
BindGrid();
}
protected void grdvotedetail_RowEditing(object sender, GridViewEditEventArgs e)
{
grdvotedetail.EditIndex = e.NewEditIndex;
BindGrid();
}
protected void grdvotedetail_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
TextBox txtVoteTitle = (TextBox)grdvotedetail.Rows[e.RowIndex].Cells[1].FindControl("txtVoteTitle");
TextBox txtNoOfCandidates = (TextBox)grdvotedetail.Rows[e.RowIndex].Cells[2].FindControl("txtNoOfCandidates");
TextBox txtStartDate = (TextBox)grdvotedetail.Rows[e.RowIndex].Cells[3].FindControl("txtStartDate");
TextBox txtEndDate = (TextBox)grdvotedetail.Rows[e.RowIndex].Cells[4].FindControl("txtEndDate");
TextBox txtResultDate = (TextBox)grdvotedetail.Rows[e.RowIndex].Cells[4].FindControl("txtResultDate");
Label lblId = (Label)grdvotedetail.Rows[e.RowIndex].Cells[0].FindControl("lblId");
try
{
SqlCommand cmd = new SqlCommand("UpdateVotingCriteria");
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@CriteriaID", SqlDbType.Int, 4).Value = Convert.ToInt32(lblId.Text);
cmd.Parameters.Add("@VoteTitle", SqlDbType.NVarChar, 50).Value = txtVoteTitle.Text.Trim();
cmd.Parameters.Add("@NoOfCandidates", SqlDbType.Int, 4).Value = Convert.ToInt32(txtNoOfCandidates.Text);
cmd.Parameters.Add("@StartDate", SqlDbType.DateTime).Value = System.DateTime.Parse(txtStartDate.Text);
cmd.Parameters.Add("@EndDate", SqlDbType.DateTime).Value = System.DateTime.Parse(txtEndDate.Text);
cmd.Parameters.Add("@ResultDate", SqlDbType.DateTime).Value = System.DateTime.Parse(txtResultDate.Text);
DAL dal = new DAL();
int VoterId = (Int32)dal.connectedMethod(cmd, DAL.executemethods.ExecuteNonQuery);
if (VoterId > 0)
{
lblmsg.Text = "Detail Updated Successfully";
grdvotedetail.EditIndex = -1;
BindGrid();
}
else
{
lblmsg.Text = "Record Doesnot Exist";
}
}
catch (Exception ex)
{
lblmsg.Text = ex.Message;
}
}