How to implement all Inserts, Updates, Delete operations at once in bulk in ASP:ListView

Question

I have an Asp:ListView control which I am showing in a pop up in a web form. Currently, it does all the changes directly in database as soon as I click the Insert, Update or Delete LinkButtons.

But I need all the changes to be done in database only when Web Form is submitted.
So, what I figured was I would need to keep the data which I am binding to ListView in a session, and on all inserts, updates and delete operations, do all the changes manually on OnInserting, OnDeleting, OnUpdating events by
Step 1) retrieving data every time from Session["ListView1"],
Step 2) doing the Insert/Delete/Update operation on retrieved data
Step 3) binding it to ListView
Step 4) replacing data in Session["ListView1"] after update.
Step 5) On web form submit, taking the updated data and Replace All in database?

I did these steps but it is not working as expected. On Update, ListView displays blank values for 2 columns and binds data incorrectly. I have binded with the right columns. After Insert, TextBox doesn't get changed to Labels as expected. Below is my ListView and code-behind code:

ListView:

    <asp:updatepanel id="UpdatePanel1" runat="server">
    <ContentTemplate>
        <asp:ListView ID="ListView1" runat="server" 
            OnItemCanceling="ListView1_ItemCanceling" OnItemEditing="ListView1_ItemEditing" 
            OnItemDeleting="ListView1_ItemDeleting" OnItemUpdating="ListView1_ItemUpdating" 
            OnItemInserting="ListView1_ItemInserting" DataKeyNames="Id" 
            InsertItemPosition="LastItem">
        <AlternatingItemTemplate>
            <tr style="">
                <td width="200">
                    <asp:LinkButton ClientIDMode="AutoID" ID="DeleteButton" runat="server" 
                        CommandName="Delete" Text="Delete" CausesValidation="false" />                                                            
                    <asp:LinkButton ClientIDMode="AutoID" ID="EditButton" runat="server" 
                        CommandName="Edit" Text="Edit" CausesValidation="false" />                 
                </td>
                <td>
                    <asp:Label ID="IdLabel" runat="server" Text='<%# Eval("Id") %>' />
                </td>
                <td>
                    <asp:Label ID="FirstNameLabel" runat="server" Text='<%# Eval("FirstName") %>' />
                </td>
                <td>
                    <asp:Label ID="LastNameLabel" runat="server" Text='<%# Eval("LastName") %>' />
                </td>
                <td>
                    <asp:Label ID="Column3Label" runat="server" Text='<%# Eval("Column3") %>' />
                </td>
                <td>
                    <asp:Label ID="Column4Label" runat="server" Text='<%# Eval("Column4") %>' />
                </td>
            </tr>
        </AlternatingItemTemplate>
        <EditItemTemplate>
            <tr style="">
                <td>
                    <asp:LinkButton ClientIDMode="AutoID" ID="UpdateButton" runat="server" 
                        CommandName="Update" Text="Update" ValidationGroup="popup1" CausesValidation="true" />
                    <asp:LinkButton ClientIDMode="AutoID" ID="CancelButton" runat="server" 
                        CommandName="Cancel" Text="Cancel" CausesValidation="false" />
                </td>
                <td>
                    <asp:Label ID="IdLabel1" runat="server" Text='<%# Eval("Id") %>' />
                </td>
                <td>
                    <asp:TextBox ID="FirstNameTextBox" Style="width: 100px; background-color: white;" 
                        runat="server" Text='<%# Bind("FirstName") %>' CausesValidation="true" />
                    <asp:RequiredFieldValidator ID="NameReq" runat="server" ControlToValidate="FirstNameTextBox" 
                        ErrorMessage="First name is required." ToolTip="First name is required." 
                        ValidationGroup="popup1">*</asp:RequiredFieldValidator>
                </td>
                <td>
                    <asp:TextBox ID="LastNameTextBox" Style="width: 100px; background-color: white;" 
                        runat="server" Text='<%# Bind("LastName") %>' CausesValidation="true" />
                    <asp:RequiredFieldValidator ID="RequiredFieldValidator2" runat="server" 
                        ControlToValidate="LastNameTextBox" ErrorMessage="Last name is required." 
                        ToolTip="Last name is required." ValidationGroup="popup1">*</asp:RequiredFieldValidator>
                </td>
                <td>
                    <asp:TextBox ID="Column3TextBox" Style="width: 100px; background-color: white;" 
                        runat="server" Text='<%# Bind("Column3") %>' CausesValidation="true" />
                    <asp:RequiredFieldValidator ID="RequiredFieldValidator6" runat="server" 
                        ControlToValidate="Column3TextBox" ErrorMessage="ID is required." ToolTip="ID is required." 
                        ValidationGroup="popup1">*</asp:RequiredFieldValidator>
                </td>
                <td>
                    <asp:TextBox ID="Column4Text" placeholder="mm/dd/yyyy" Style="width: 100px; 
                        background-color: white;" runat="server" Text='<%# Bind("Column4") %>' 
                        CausesValidation="true" />
                    <asp:RequiredFieldValidator ID="RequiredFieldValidator7" runat="server" 
                        ControlToValidate="Column4Text" ErrorMessage="Date is required." 
                        ToolTip="Date is required." ValidationGroup="popup1">*</asp:RequiredFieldValidator>
                </td>
            </tr>
            <tr>
                <td>
                    <asp:RegularExpressionValidator ID="dateValRegex" runat="server" 
                        ControlToValidate="Column4Text" ErrorMessage="Please Enter a valid date in the format (mm/dd/yyyy)" 
                        ValidationExpression="^(0[1-9]|1[012])[- /.](0[1-9]|[12][0-9]|3[01])[- /.](19|20)\d\d$" 
                        ValidationGroup="popup"></asp:RegularExpressionValidator>
                </td> 
            </tr>
        </EditItemTemplate>
        <EmptyDataTemplate>
            <table id="Table1" runat="server" style="">
                <tr>
                    <td>No data was returned.</td>
                </tr>
            </table>
        </EmptyDataTemplate>
        <InsertItemTemplate>
            <tr>
                <td width="200">
                    <asp:LinkButton ClientIDMode="AutoID" ID="InsertButton" runat="server" CommandName="Insert" 
                        Text="Insert" ValidationGroup="popup" CausesValidation="true" />
                    <asp:LinkButton ClientIDMode="AutoID" ID="LinkButton1" runat="server" CommandName="Cancel" 
                        Text="Clear" CausesValidation="false" />
                </td>
                <td>
                    <asp:Label ID="Label1" runat="server" Text='<%# Bind("Id") %>' />
                </td>
                <td>
                    <asp:TextBox ID="TextBox1" Style="width: 100px; background-color: white;" runat="server" 
                        Text='<%# Bind("FirstName") %>' CausesValidation="true" />
                    <asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server" 
                        ControlToValidate="FirstNameTextBox" ErrorMessage="First name is required." 
                        ToolTip="First name is required." ValidationGroup="popup">*</asp:RequiredFieldValidator>
                </td>
                <td>
                    <asp:TextBox ID="TextBox2" Style="width: 100px; background-color: white;" runat="server" 
                        Text='<%# Bind("LastName") %>' CausesValidation="true" />
                    <asp:RequiredFieldValidator ID="RequiredFieldValidator3" runat="server" 
                        ControlToValidate="LastNameTextBox" ErrorMessage="Last name is required." 
                        ToolTip="Last name is required." ValidationGroup="popup">*</asp:RequiredFieldValidator>
                </td>
                <td>
                    <asp:TextBox ID="TextBox3" Style="width: 100px; background-color: white;" runat="server" 
                        Text='<%# Bind("Column3") %>' CausesValidation="true" />
                    <asp:RequiredFieldValidator ID="RequiredFieldValidator4" runat="server" 
                        ControlToValidate="Column3TextBox" ErrorMessage="ID is required." 
                        ToolTip="ID is required." ValidationGroup="popup">*</asp:RequiredFieldValidator>
                </td>
                <td>
                    <asp:TextBox ID="TextBox4" placeholder="mm/dd/yyyy" Style="width: 100px; 
                        background-color: white;" runat="server" Text='<%# Bind("Column4") %>' CausesValidation="true" />
                    <asp:RequiredFieldValidator ID="RequiredFieldValidator5" runat="server" ControlToValidate="Column4Text" 
                        ErrorMessage="Date is required." ToolTip="Date is required." 
                        ValidationGroup="popup">*</asp:RequiredFieldValidator>
            </tr>
            <tr>
                <td>
                    <asp:RegularExpressionValidator ID="RegularExpressionValidator1" runat="server" 
                        ControlToValidate="Column4Text" ErrorMessage="Please Enter a valid date in the format (mm/dd/yyyy)" 
                        ValidationExpression="^(0[1-9]|1[012])[- /.](0[1-9]|[12][0-9]|3[01])[- /.](19|20)\d\d$" 
                        ValidationGroup="popup"></asp:RegularExpressionValidator>
                </td> 
            </tr>
        </InsertItemTemplate>
        <ItemTemplate>
            <tr>
                <td width="200">
                    <asp:LinkButton ClientIDMode="AutoID" ID="LinkButton2" runat="server" CommandName="Delete" 
                        Text="Delete" CausesValidation="false" />
                    <asp:LinkButton ClientIDMode="AutoID" ID="LinkButton3" runat="server" CommandName="Edit" 
                        Text="Edit" CausesValidation="false" />
                </td>
                <td>
                    <asp:Label ID="Label2" runat="server" Text='<%# Eval("Id") %>' />
                </td>
                <td>
                    <asp:Label ID="Label3" runat="server" Text='<%# Eval("FirstName") %>' />
                </td>
                <td>
                    <asp:Label ID="Label4" runat="server" Text='<%# Eval("LastName") %>' />
                </td>
                <td>
                    <asp:Label ID="Label5" runat="server" Text='<%# Eval("Column3") %>' />
                </td>
                <td>
                    <asp:Label ID="Label6" runat="server" Text='<%# Eval("Column4") %>' />
                </td>
            </tr>
        </ItemTemplate>
        <LayoutTemplate>
            <table id="Table2" runat="server">
                <tr id="Tr1" runat="server">
                    <td id="Td1" runat="server">
                        <table id="itemPlaceholderContainer" runat="server" border="0" style="">
                            <tr id="Tr2" runat="server" style="">
                                <th id="Th1" runat="server">Action</th>
                                <th id="Th2" runat="server">Id</th>
                                <th id="Th3" runat="server">First Name</th>
                                <th id="Th4" runat="server">Last Name</th>
                                <th id="Th5" runat="server">Column3 Name</th>
                                <th id="Th6" runat="server">Column4 Name</th>
                            </tr>
                            <tr id="itemPlaceholder" runat="server">
                            </tr>
                        </table>
                    </td>
                </tr>
            </table>
            <table width="900" align="center" style="text-align: center;">
                <tr id="Tr3" runat="server">
                    <td id="Td2" runat="server" style="">
                        <asp:DataPager ID="DataPager1" runat="server">
                            <Fields>
                                <asp:NextPreviousPagerField ButtonType="Link" ShowFirstPageButton="True" 
                                    ShowNextPageButton="False" ShowPreviousPageButton="False" />
                                <asp:NumericPagerField />
                                <asp:NextPreviousPagerField ButtonType="Link" ShowLastPageButton="True" 
                                    ShowNextPageButton="False" ShowPreviousPageButton="False" />
                            </Fields>
                        </asp:DataPager>
                    </td>
                </tr>
            </table>
        </LayoutTemplate>
        <SelectedItemTemplate>
                <tr style="">
                    <td>
                        <asp:LinkButton ID="LinkButton4" runat="server" CommandName="Delete" Text="Delete" 
                            CausesValidation="false" />
                        <asp:LinkButton ID="LinkButton5" runat="server" CommandName="Edit" Text="Edit" 
                            CausesValidation="false" />
                    </td>
                    <td>
                        <asp:Label ID="Label7" runat="server" Text='<%# Eval("Id") %>' />
                    </td>
                    <td>
                        <asp:Label ID="Label8" runat="server" Text='<%# Eval("FirstName") %>' />
                    </td>
                    <td>
                        <asp:Label ID="Label9" runat="server" Text='<%# Eval("LastName") %>' />
                    </td>
                    <td>
                        <asp:Label ID="Label10" runat="server" Text='<%# Eval("Column3") %>' />
                    </td>
                    <td>
                        <asp:Label ID="Label11" runat="server" Text='<%# Eval("Column4") %>' />
                    </td>
                </tr>
            </SelectedItemTemplate>
        </asp:ListView>
    </ContentTemplate>
</asp:updatepanel>

Below is Code Behind code:


        protected void Page_Load(object sender, EventArgs e)
        {
            try
            {
                if (!IsPostBack)
                {
            LoadListView();
        }
        }


    private void LoadListView()
        {
            try
            {
                 List lstDetails = Session["ListView1Data"] as List;
           ListView1.DataSource = lstDetails;
                ListView1.DataBind();
                Session["ListView1Data"] = lstDetails ;
            }
            catch (Exception ex)
            {
                ExceptionLogger.LogException(ex);
            }
        }


    protected void ListView1_ItemDeleting(object sender, ListViewDeleteEventArgs e)
        {
            List lstDetails = Session["ListView1Data"] as List;
            int id = Convert.ToInt32(e.Values["Id"]);
            if (lstDetails.Exists(x => x.Id == id))
            {
                var tableDetail = lstDetails.Where(x => x.Id == id).FirstOrDefault();
                lstDetails.Remove(tableDetail);
                hdnListDeleteIds.Value = id + "|";
                ListView1.DataSource = lstDetails;
                ListView1.DataBind();
                ListView1.EditIndex = -1;
            }
        }
        protected void ListView1_ItemUpdating(object sender, ListViewUpdateEventArgs e)
        {
            List lstDetails = Session["ListView1Data"] as List;
            int id = Convert.ToInt32(e.OldValues["Id"]);
            if (lstDetails.Exists(x => x.Id == id))
            {
                var tableDetail = lstDetails.Where(x => x.Id == id).FirstOrDefault();
                tableDetail.FirstName = e.NewValues["FirstName"].ToString();
                tableDetail.LastName = e.NewValues["LastName"].ToString();
                tableDetail.Column3 = e.NewValues["Column3"].ToString();
                tableDetail.Column4 = Convert.ToDateTime(e.NewValues["Column4"]);
                hdnListUpdateIds.Value = id + "|";
                ListView1.DataSource = lstDetails;
                ListView1.DataBind();
                ListView1.EditIndex = -1;
                Session["ListView1Data"] = lstDetails;
            }
        }
        protected void ListView1_ItemInserting(object sender, ListViewInsertEventArgs e)
        {
            List lstDetails = Session["ListView1Data"] as List;

            EntityFrameworkModel.TableDetails tableDetail = new EntityFrameworkModel.TableDetails()
            {
                FirstName = e.Values["FirstName"].ToString(),
                LastName = e.Values["LastName"].ToString(),
                Column3 = e.Values["Column3"].ToString(),
                Column4 = Convert.ToDateTime(e.Values["Column4"]),
                Id = 0
            };
            lstDetails.Add(tableDetail);
            Session["ListView1Data"] = lstDetails;
            ListView1.DataSource = lstDetails;
            ListView1.DataBind();
            ListView1.EditIndex = -1;
        }
        protected void ListView1_ItemCanceling(object sender, ListViewCancelEventArgs e)
        {
            List lstDetails = Session["ListView1Data"] as List;
            ListView1.DataSource = lstDetails;
            ListView1.DataBind();
            e.Cancel = true;
            ListView1.EditIndex = -1;
        }
        protected void ListView1_ItemEditing(object sender, ListViewEditEventArgs e)
        {
            ListView1.EditIndex = e.NewEditIndex;
        }

What am I doing wrong? Please help.

Many Thanks

@BillRuhl , Yes. Using sql server 2008. Please see my edit 2. — Ridhi Jain, Jun 30 '15 at 02:10
@shanish , I am trying to figure out how to upload image here. It has a grid like structure. Multiple columns and multiple rows. Please see my edit 2 — Ridhi Jain, Jun 30 '15 at 02:11
It is difficult to answer without seeing how and what data you are binding to the ListView. — shanish, Jun 30 '15 at 02:21
ok then help me figure out how to add image here. It's a simple listview with grid layout @shanish — Ridhi Jain, Jun 30 '15 at 03:32
What you can try is, while updating your db after making changes in your ListView, delete all the records which are related to that particular primary key. Then insert whatever data you have currently in your session(the source for your ListView). So, you will have only the latest changes. — shanish, Jun 30 '15 at 06:12
create a table valued parameter in the database then create a stored procedure that gets passed the new table valued parameter and handles the update from there. In your C# code create a new dataTable, populate it with the values from your listview and pass the entire table to the new stored procedure. — BillRuhl, Jun 30 '15 at 14:30
@shanish Can you please read my updated question? I have added the markup. — Ridhi Jain, Jun 30 '15 at 22:21

shanish · Answer 1 · 2015-07-02T02:42:05.160

I have modified your code to Insert/Update/Delete data at one shot. Hope it will help you.

While editing you need to bind the data again,

protected void ListView1_ItemEditing(object sender, ListViewEditEventArgs e)
{
    ListView1.EditIndex = e.NewEditIndex;
    BindData();
}

private void BindData()
{
    ListView1.DataSource = Session["ListView1Data"] as List<TableDetails>;
    ListView1.DataBind();
}

While deleting the items, you can consider keeping them(only the existing items, not newly added) in a separate session.

protected void ListView1_ItemDeleting(object sender, ListViewDeleteEventArgs e)
{
    List<TableDetails> lstDetails = Session["ListView1Data"] as List<TableDetails>;

    if (lstDetails.Count >= e.ItemIndex + 1)
    {
        var tableDetail = lstDetails[e.ItemIndex];
        DeletedItems(tableDetail);
        lstDetails.Remove(tableDetail);
        ListView1.EditIndex = -1;
        Session["ListView1Data"] = lstDetails;
        BindData();
    }
}

private void DeletedItems(TableDetails tableDetail)
{
    List<TableDetails> lstDelItems = new List<TableDetails>();

    if (Session["DeletedItems"] != null)
    {
        lstDelItems = Session["DeletedItems"] as List<TableDetails>;
    }

    // No need to keep track of New Items(whose ID is 0)
    if (tableDetail.Id > 0)
    {
        lstDelItems.Add(tableDetail);
        Session["DeletedItems"] = lstDelItems;
    }
 }

Now, your save button click event can be

protected void btnSave_Click(object sender, EventArgs e)
{
    List<TableDetails> lstDelItems = new List<TableDetails>();
    List<TableDetails> lstDetails = Session["ListView1Data"] as List<TableDetails>;
    EntityFrameworkModel entity = new EntityFrameworkModel();

    if (Session["DeletedItems"] != null)
    {
        lstDelItems = Session["DeletedItems"] as List<TableDetails>;
    }

    foreach (TableDetails det in lstDetails)
    {
        // Insert. For all the new entries, the ID value will be 0
        if (det.Id == 0)
        {
            entity.TableDetails.Add(det);                    
        }
        // Update. If ID exists in the db, its a modified data
        else if (entity.TableDetails.Any(d => d.Id == det.Id))
        {
            TableDetails detail = entity.TableDetails.First(d => d.Id == det.Id);
            detail.FirstName = det.FirstName;
            detail.LastName = det.LastName;
            detail.Column3 = det.Column3;
            detail.Column4 = det.Column4;
        }

        entity.SaveChanges();
    }

    // Delete.
    foreach (TableDetails det in lstDelItems)
    {
        TableDetails detail = entity.TableDetails.First(d => d.Id == det.Id);
        entity.TableDetails.Remove(detail);
        entity.SaveChanges();
    }

    LoadListView();
    Session["DeletedItems"] = null;
}

Update:

Yes, you need to modify your Updating event. Since all of your new items will have 0 as your ID value, you can't depend with that. You can depend with the RowIndex for this.

protected void ListView1_ItemUpdating(object sender, ListViewUpdateEventArgs e)
{
    List<TableDetails> lstDetails = Session["ListView1Data"] as List<TableDetails>;

    if (lstDetails.Count >= e.ItemIndex + 1)
    {
        var tableDetail = lstDetails[e.ItemIndex];
        tableDetail.FirstName = e.NewValues["FirstName"].ToString();
        tableDetail.LastName = e.NewValues["LastName"].ToString();
        tableDetail.Column3 = e.NewValues["Column3"].ToString();
        tableDetail.Column4 = Convert.ToDateTime(e.NewValues["Column4"]);             
        ListView1.EditIndex = -1;
        Session["ListView1Data"] = lstDetails;
        BindData();
    }
}

Do my Cancel and Update events require some change as well? Insert and Clear(Cancel in case of Insert) are working fine. But for Update, or CancelUpdate, postback happens but no change occurs on the UI at first click and on the second click, it doesn't postback but the row goes from TextBoxes view(EditTemplateView) to Labels view(ItemTemplate) with empty cells. No data is shown for that row except the id. — Ridhi Jain, Jul 01 '15 at 15:51

How to implement all Inserts, Updates, Delete operations at once in bulk in ASP:ListView

1 Answers1