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 Visual Studio 2008.

2.      Click New> Website and Select “ASP.Net Web Site”. You can select the language of your choice. I have selected C#. Rename the website name as per your need.

3.      Include a new SQL express database inside App_Data folder and create a table called Employee.

Note

You can add database by right clicking App_Data folder in the solution and clicking Add New Item. This will bring a dialog box where you need to select “Sql Server Database” and click Add. Then, create a table called Employee with the necessary columns using the “Server Explorer”. Just right click the added database and click “Open” to open your database using Server Explorer on the left pane of your Visual Studio 2008. Refer my Server Explorer.

4.      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.

5.      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 and delete. 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.