Moving forward, we will build edit, update, delete and insert feature for DataList control using LINQ. Throughout this article, we will construct our own LINQ query to insert, update, delete and select operations.

We will build a sample application which binds all employee information present in the Sql Express database.

Steps

1.      Open a new Asp.Net project in Visual Studio 2008.

2.      Create a new SqlExpress database with a table called Employee in App_Data folder.

3.      Next, we will build the LINQ to SQL class for our Employee table to do our database operations. Right click your project in the solution explorer and select “Add New Item” option. Select LINQ to SQL class, rename it if required and click Add. I have named it as EmployeesDataClasses.dbml.

4.      From the server explorer, just drag and drop the Employees table into the LINQ to SQL designer to create the DataContext class and other necessary classes required for the database operations. The LINQ query will use these DataContext class to interact with the database. Thus, we have our LINQ to SQL class ready and we will start building the DataList control in the coming sections.

Binding the DataList Control

Drag and drop a DataList control into our ASPX page from the data tab of Visual studio toolbar. In order to display data with DataList control, we need to first provide an ItemTemplate and HeaderTemplate with the structure of the data display. I have used tabular view in this article. Refer the below code.

  <HeaderTemplate>

        <table>

        <tr>

             <th id="Th1" align="left" width="100px">

             </th>

             <th id="Th2" align="left" width="20px">

             ID</th>

             <th id="Th3" align="left" width="150px">

             Employee Name</th>

             <th id="Th4" align="left" width="100px">

             Department</th>

             <th id="Th5" align="left" width="100px">

             Address</th>

             <th id="Th6" align="left" width="100px">

             City</th>

             <th id="Th7" align="left" width="100px">

             State</th>

             <th id="Th8" align="left" width="100px">

             Country</th>                                      

         </tr>

         </table>

        </HeaderTemplate>

        <ItemTemplate>

        <table>

        <tr>

            <td width="100px">

            <asp:Button ID="DeleteButton" runat="server" CommandName="Delete"

            Text="Delete" OnClientClick="return confirm('Are You Sure to Delete?')" />

            <asp:Button ID="EditButton" runat="server" CommandName="Edit" Text="Edit" />

            </td>

            <td width="20px">

            <asp:Label ID="EmpIDLabel" runat="server" Text='<%# Eval("EmployeeID") %>' />

            </td>

            <td width="150px">

            <asp:Label ID="EmpNameLabel" runat="server" Text='<%# Eval("EmployeeName") %>' />

            </td>

            <td width="100px">

            <asp:Label ID="DepartmentLabel" runat="server"

            Text='<%# Eval("Department") %>' />

            </td>

            <td  width="100px">

            <asp:Label ID="AgeLabel" runat="server" Text='<%# Eval("Address") %>' />

            </td>

            <td  width="100px">

            <asp:Label ID="AddressLabel" runat="server" Text='<%# Eval("City") %>' />

            </td>

            <td  width="100px">

            <asp:Label ID="Label1" runat="server" Text='<%# Eval("State") %>' />

            </td>

            <td  width="100px">

            <asp:Label ID="Label2" runat="server" Text='<%# Eval("Country") %>' />

            </td>

            </tr>

         </table>

        </ItemTemplate>

Next, we can query the database using LINQ query and bind the DataList control.

Refer the code below,

protected void Page_Load(object sender, EventArgs e)

    {

        if (!IsPostBack)

            BindEmp();

    }

    public void BindEmp()

    {

        EmployeesDataClassesDataContext dbEmp = new EmployeesDataClassesDataContext();

        var LINQQuery = from emp in dbEmp.Employees

                        select emp;

        dlEmployee.DataSource = LINQQuery;

        dlEmployee.DataBind();

    }

Execute the application. You can see the employees data populated in the DataList control.

Refer the below figure.

I have specified the Header style and Alternate row style to apply the style and hence you can the see the colors.

Make sure, you have set the CommandName property of Edit button to “Edit”. This will make sure that clicking edit will call the DataList edit event. Else, you need to handle the edit click in ItemCommand and you will be performing the operations depending upon the CommandName you specifying for the Button.

Next, we will add edit, update functionality to DataList control.

Edit Update in DataList Control

In order to perform edit operation with DataList control, we need to specify the EditItemTemplate which can populate the editing row data in input controls like TextBox, DropDownList, etc.

Refer the below code,

<EditItemTemplate>

        <table>

        <tr>

         <td width="100px">

         <asp:Button ID="UpdateButton" runat="server" CommandName="Update"

         Text="Update" />

         <asp:Button ID="CancelButton" runat="server" CommandName="Cancel"

         Text="Cancel" />

         </td>

         <td width="20px">

         <asp:Label ID="EmpIDLabel1" runat="server" Text='<%# Eval("EmployeeID") %>' />

         </td>

         <td width="150px">

         <asp:TextBox ID="EmpNameTextBox" Width="100px" runat="server" Text='<%# Bind("EmployeeName") %>' />

         </td>

         <td width="80px">

         <asp:TextBox ID="txtDept" runat="server" Width="80px" Text='<%# Bind("Department") %>' />

         </td>

         <td width="100px">

         <asp:TextBox ID="txtAddress" runat="server" Width="100px" Text='<%# Bind("Address") %>' />

         </td>

         <td width="100px">

         <asp:TextBox ID="txtCity" runat="server" Width="100px" Text='<%# Bind("City") %>' />

         </td>

         <td width="100px">

         <asp:TextBox ID="txtState" runat="server" Width="100px" Text='<%# Bind("State") %>' />

         </td>

         <td width="100px">

         <asp:TextBox ID="txtCountry" runat="server" Width="100px" Text='<%# Bind("Country") %>' />

         </td>

         </tr>

         </table>

</EditItemTemplate>

Next, when the user clicks Edit button we need to set the DataList control’s EditItemIndex property with the editing row index to populate the data in the textboxes. This should be done on oneditcommand event.

protected void dlEmployee_EditCommand(object source, DataListCommandEventArgs e)

    {

        dlEmployee.EditItemIndex = e.Item.ItemIndex;

       BindEmp();

    }

The above event will actually populate the editing row’s data into the input controls(In our case, its textbox) we have specified in EditItemTemplate. We have also given 2 buttons in edit item template, Update and Cancel. As the name suggest, update button will be used to update the data and cancel button to cancel the edit.

The update event below uses LINQ query to update the data into database.

protected void dlEmployee_UpdateCommand(object source, DataListCommandEventArgs e)

    {

        string EmpID="";

        Label lblTemp = dlEmployee.Items[e.Item.ItemIndex].FindControl("EmpIDLabel1") as Label;

        if (lblTemp != null)

        {

            EmpID = lblTemp.Text;

            EmployeesDataClassesDataContext dbEmp = new EmployeesDataClassesDataContext();

            Employee empTemp = (from emp in dbEmp.Employees

                                where emp.EmployeeID == int.Parse(EmpID)

                                select emp).Single();

            TextBox txtTemp = dlEmployee.Items[e.Item.ItemIndex].FindControl("EmpNameTextBox") as TextBox;

            if (txtTemp != null)

                empTemp.EmployeeName = txtTemp.Text;

            txtTemp = dlEmployee.Items[e.Item.ItemIndex].FindControl("txtDept") as TextBox;

            if (txtTemp != null)

                empTemp.Department = txtTemp.Text;

            txtTemp = dlEmployee.Items[e.Item.ItemIndex].FindControl("txtAddress") as TextBox;

            if (txtTemp != null)

                empTemp.Address = txtTemp.Text;

            txtTemp = dlEmployee.Items[e.Item.ItemIndex].FindControl("txtCity") as TextBox;

            if (txtTemp != null)

                empTemp.City = txtTemp.Text;

            txtTemp = dlEmployee.Items[e.Item.ItemIndex].FindControl("txtState") as TextBox;

            if (txtTemp != null)

                empTemp.State = txtTemp.Text;

            txtTemp = dlEmployee.Items[e.Item.ItemIndex].FindControl("txtCountry") as TextBox;

            if (txtTemp != null)

                empTemp.Country = txtTemp.Text;

            dbEmp.SubmitChanges();

            dlEmployee.EditItemIndex = -1;

            BindEmp();

        }

    }

protected void dlEmployee_CancelCommand(object source, DataListCommandEventArgs e)

    {

        dlEmployee.EditItemIndex = -1;

        BindEmp();

    }

Execute the application to see edit/update in action.

Deleting Record in DataList control

Like Edit and Update event, the DataList control is packed with a delete event to delete a record. The below delete event uses LINQ query to delete the current record.

protected void dlEmployee_DeleteCommand(object source, DataListCommandEventArgs e)

    {

        EmployeesDataClassesDataContext dbEmp = new EmployeesDataClassesDataContext();

        Label lblTemp = dlEmployee.Items[e.Item.ItemIndex].FindControl("EmpIDLabel") as Label;

        if (lblTemp != null)

        {

            string EmpID = lblTemp.Text;

            Employee empTemp = (from emp in dbEmp.Employees

                                where emp.EmployeeID == int.Parse(EmpID)

                                select emp).Single();

            dbEmp.Employees.DeleteOnSubmit(empTemp);

            dbEmp.SubmitChanges();

            BindEmp();

        }

    }

Execute and see delete feature in action.

Insert Feature with DataList control

By default, the DataList control does not provide any template to insert new record like edit. But, we can provide this feature in DataList control using the Footer Template and ItemCommand of DataList control. We can construct the FooterTemplate to provide insert feature using DataList control. Refer the below code,

<FooterTemplate>

         <table>

         <tr>

         <td width="100px">

         <asp:Button ID="btnInsertButton" runat="server" CommandName="Insert"

         Text="Insert" />                       

         </td>

         <td  width="20px">

         <asp:Label ID="EmpIDLabel1" runat="server" />

         </td>

         <td  width="150px">

         <asp:TextBox ID="EmpNameTextBox" runat="server" Width="100px"/>

         </td>

         <td width="80px">

         <asp:TextBox ID="txtDept" runat="server" Width="80px"/>

         </td>

         <td width="100px">

         <asp:TextBox ID="txtAddress" runat="server" Width="100px"/>

         </td>

         <td width="100px">

         <asp:TextBox ID="txtCity" runat="server" Width="100px"/>

         </td>

         <td width="100px">

         <asp:TextBox ID="txtState" runat="server" Width="100px"/>

         </td>

         <td width="100px">

         <asp:TextBox ID="txtCountry" runat="server" Width="100px"/>

         </td>

         </tr>

        </table>

</FooterTemplate>

The Insert button in the above code will generate an ItemCommand event where we can get the values and insert it into the database using LINQ.

Refer the below code,

protected void dlEmployee_ItemCommand(object source, DataListCommandEventArgs e)

    {

        string CommandName = e.CommandName;

        if (CommandName == "Insert")

        {

             EmployeesDataClassesDataContext dbEmp = new EmployeesDataClassesDataContext();

             Employee empTemp = new Employee();

             TextBox txtTemp = e.Item.FindControl("EmpNameTextBox") as TextBox;

             if (txtTemp != null)

                 empTemp.EmployeeName = txtTemp.Text;

             txtTemp = e.Item.FindControl("txtDept") as TextBox;

             if (txtTemp != null)

                 empTemp.Department = txtTemp.Text;

             txtTemp = e.Item.FindControl("txtAddress") as TextBox;

             if (txtTemp != null)

                 empTemp.Address = txtTemp.Text;

             txtTemp = e.Item.FindControl("txtCity") as TextBox;

             if (txtTemp != null)

                 empTemp.City = txtTemp.Text;

             txtTemp = e.Item.FindControl("txtState") as TextBox;

             if (txtTemp != null)

                 empTemp.State = txtTemp.Text;

             txtTemp = e.Item.FindControl("txtCountry") as TextBox;

             if (txtTemp != null)

                 empTemp.Country = txtTemp.Text;

             dbEmp.Employees.InsertOnSubmit(empTemp);

             dbEmp.SubmitChanges();

             BindEmp();

        }

    }

Execute the project and see it in action. You can also move this insert feature outside on the DataList control if it is required.

Download the source attached with this article and see it in action.