DataList with Add & Delete Options

DataList with Add & Delete Options

Asp.Net provides a DataList control which is similar to the Repeater control, the DataList control provide some additional features, it allows us to add/edit/delete the content in the list using <ItemTemplate> & <EditItemTemplate>

In general any additions / updates to the DataList are saved to the database instantly and the new data is bound to the DataList to reflect the changes. There are situations where we will have to retail the changes (Add/Modify/Delete) in the client side and finally save the details to the database, we shall now see on how to accomplish this.

Let us consider a DataList which has 2 columns one TextBox and a Checkbox

First let us define the DataList in the .aspx page with the controls, in this case we are not going to use the as we will be using the controls directly in the < ItemTemplate>, if you require a view and edit mode separately then you can add a set of labels in the <ItemTemplate> for view mode and a set of TextBox/Checkboxes and other controls in the <EditItemTemplate>.

Here is how the .aspx code will look like

<asp:DataList

ID="dlITDocs"

runat="server"

onitemcommand="dlITDocs_ItemCommand"

      onitemdatabound="dlITDocs_ItemDataBound">

    <HeaderTemplate>

        <table style="border: 1px double blue;" >

            <tr>

                <td class="ColumnHeading">

                    Document

                </td>

                <td class="ColumnHeading">

                    Required

                </td>

                <td class="ColumnHeading">

                     

                </td>

            </tr>

    </HeaderTemplate>

    <ItemTemplate>

        <tr>

<asp:Label ID="lblDOC_ID" runat="server" Visible="false"

Text='<%# Eval("DOC_ID") %>'>asp:Label>

            <td>

<asp:TextBox ID="txtDOCUMENT_NAME" runat="server" CssClass="text" Width="300" MaxLength="250" Text='<%# Eval("DOCUMENT_NAME") %>'>asp:TextBox>

            </td>

            <td align="center">

<asp:Label ID="lblREQUIRED " runat="server" Visible="false" Text='<%# Eval("REQUIRED") %>'>asp:Label>

                 

<asp:CheckBox ID="chkREQUIRED" runat="server">asp:CheckBox>                                              

            </td>

            <td>

<asp:Button ID="cmdDelete" runat="server" Text="Remove" CommandName="DeleteITDoc" CausesValidation="false" />

            </td>

        </tr>

    </ItemTemplate>

    <FooterTemplate>

        </tr>

                  <td colspan="3">

                        <asp:Button ID="cmdITDoc"

runat="server" Text="Add IT Doc" CommandName = "AddITDoc" CausesValidation="false" />

                  </td>

      </tr>

      </table>

    </FooterTemplate>

</asp:DataList>

Notice that the controls are directly places in the <ItemTemplate>, hence we will not have and Edit & Update button in this case, however we can add them if we require it. Also notice that 2 events of the datalist are defined

onitemcommand = "dlITDocs_ItemCommand"

          onitemdatabound = "dlITDocs_ItemDataBound"

Now we have the design in place, we shall not move to the code behind and write code to make this design work.

First when the page loads, we will have to show one blank row in the DataList so that the user can start entering the details, to display the 1^st row we will have to bind an empty row to the DataList, if no data is bound to the DataList then the DataList will not be visible in the UI.

if (!Page.IsPostBack)

{
     bindDefaultITDocDetails();
}
//
void bindDefaultITDocDetails()
{

strQuery = "Select To_Char(DOC_ID) As DOC_ID, DOCUMENT_NAME,REQUIRED From DOCUMENTS Where Project_Id = '0'";

dsITDocDetails = DataAccessLayer.GetDataSet(strQuery, "dtITDocDetails");

dlITDocs.DataSource = dsITDocDetails.Tables["dtITDocDetails"];

dlITDocs.DataBind();

Session["dsITDocDetails"] = dsITDocDetails;
}

The above function will fetch an empty row from the table and bind it to the DataList so that the user will see an empty row initially.

Notice that we are saving the details of the dataset in a session variable

Session["dsITDocDetails"]

We will use this variable, to store intermediat changes (Add/Update/Delete) to the DataList, before it finally reaches the database.

Next let us look at the ItemCommand event which will handle the Add/Delete operations of the DataList, here is the code for this event.

protected void dlITDocs_ItemCommand(object source, DataListCommandEventArgs e)

{

    if (e.CommandName == "AddITDoc")

    {

        // Copies the current state of the DataList into a Dataset in Session

        CopyITDocData();

        dsITDocDetails = (DataSet)Session["dsITDocDetails"];

        DataRow drITDocDetails;

        int intDOC_ID = 0;

     

        // Get the MaxID from the DataTable using LINQ
        var maxVal =    

        dsITDocDetails.Tables["dtITDocDetails"].AsEnumerable().Max(r =>

        int.TryParse(r.Field<string>("DOC_ID"), out intDOC_ID) ?  

        (int?)intDOC_ID : null);

// Add a new row to the DataSet and Bind the DataSet to the DataList   so that the DataList displays a new row in the UI

        drITDocDetails = dsITDocDetails.Tables["dtITDocDetails"].NewRow();

        drITDocDetails["DOC_ID"] = intDOC_ID + 1;

        dsITDocDetails.Tables["dtITDocDetails"].Rows.Add(drITDocDetails);

        dsITDocDetails.AcceptChanges();

        //

        dlITDocs.DataSource = dsITDocDetails.Tables["dtITDocDetails"];

        dlITDocs.DataBind();

        //
        // Save the Updated DataSet into the Session

        Session["dsITDocDetails"] = dsITDocDetails;

    }

    //

    if (e.CommandName == "DeleteITDoc")

    {

        DataRow[] drITDocDetails;

  Label lblDOC_ID =
  (Label)dlITDocs.Items[e.Item.ItemIndex].FindControl("lblDOC_ID");

        //

        // Copy changes to the DataSet

        CopyITDocData();

        //

        // Get the latest DataSet from Session

        dsITDocDetails = (DataSet)Session["dsITDocDetails"];

        //

        // Delete the selected row from the Table.

        drITDocDetails =  

        dsITDocDetails.Tables["dtITDocDetails"].Select("DOC_ID = '" +

        lblDOC_ID.Text + "'");

        if (drITDocDetails.Length > 0)

        {

            foreach (DataRow dr in drITDocDetails)

            {

                dr.Delete();

            }

        }

        // Save the Updated DataSet into the Session 

        dsITDocDetails.AcceptChanges();

        Session["dsITDocDetails"] = dsITDocDetails;

        //

        // Bind the updated datatable to the to the DataList.

        //

        dlITDocs.DataSource = dsITDocDetails.Tables["dtITDocDetails"];

        dlITDocs.DataBind();

    }

}

Now let us see the code for the ItemDataBound event

protected void dlITDocs_ItemDataBound(object sender, DataListItemEventArgs e)

{

    DataList dlITDocs = (DataList)sender;

    if ((e.Item.ItemType == ListItemType.Item) || (e.Item.ItemType ==

    ListItemType.AlternatingItem))

    {

CheckBox chkREQUIRED = (CheckBox)e.Item.FindControl("chkREQUIRED");

Label lblREQUIRED = (Label)e.Item.FindControl("lblREQUIRED");

      //

chkREQUIRE_UPDATE_PROJECT.Checked = (lblREQUIRE_UPDATE_PROJECT.Text == "Y") ? true : false;

    }

}

Since the checkbox.checked property cannot be bound directly from the database, we are using the ItemDataBound event to perform the chek operation while generating the rows in the DataList.

Here we loop through the items in the DataList, check the value in the label lblREQUIRED and check the checkbox chkREQUIRED if the label has value “Y”. Sililarly we need manually set the values for RadioButton and DropDownLists, Textboxes will not require any implementation here as the Text property of the control is directly bound to the value from the database.

Text='<%# Eval("DOCUMENT_NAME") %>'

Finally we can insert the details in the Database, by looking through the controls in the dataset, the code is as follows.

void insertITDocDetails(int ProjectID)

{

    int intDOC_ID = 1;

    // Delete the existing rows

    sbInsertQuery.Append("DELETE FROM DOCUMENTS WHERE PROJECT_ID = '" +
    ProjectID + "';");

//
// Loop throught the rows in the DataList

    for (int i = 0; i < dlITDocs.Items.Count; i++)

    {

      Label lblDOC_ID = (Label)dlITDocs.Items[i].FindControl("lblDOC_ID");

TextBox txtDOCUMENT_NAME = (TextBox)dlITDocs.Items[i].FindControl("txtDOCUMENT_NAME");

CheckBox chkREQUIRE_UPDATE_PROJECT =   (CheckBox)dlITDocs.Items[i].FindControl("chkREQUIRED");

        //

sbInsertQuery.Append("INSERT INTO DOCUMENTS (PROJECT_ID,DOC_ID,DOCUMENT_NAME,REQUIRE_UPDATE_PROJECT) VALUES (");

        sbInsertQuery.Append("'" + ProjectID + "',");

        sbInsertQuery.Append("'" + intDOC_ID + "',");

  sbInsertQuery.Append("'" + txtDOCUMENT_NAME.Text + "',");

        //

        if (chkREQUIRED.Checked == true)

        {

            sbInsertQuery.Append("'Y');");

        }

        else

        {

            sbInsertQuery.Append("'N');");

        }

        intDOC_ID++;

    }

}

Now the code to reload the data from the database into the DataList

void bindITDocDetails()

{

    // Get the details from the Database.

    strQuery = "Select To_Char(PROJECT_ID) As PROJECT_Id,To_Char(DOC_ID) As
    DOC_ID,DOCUMENT_NAME,REQUIRED From DOCUMENTS Where PROJECT_Id =  '" +
    intPROJECTId + "'";

    dsITDocDetails = DataAccessLayer.GetDataSet(strQuery, "dtITDocDetails");

    //

    // Bind Pricing Details Section

    dlITDocs.DataSource = dsITDocDetails.Tables["dtITDocDetails"];

    dlITDocs.DataBind();

    //

    Session["dsITDocDetails"] = dsITDocDetails;

}

That’s it we have seen how to implement Add/Delete operation in a DataList without saving the data to the database for intermediete changes in the DataList