Designing the LINQ to SQL classes

In solution explorer, right the project and select “Add New Item”. Select “LINQ to SQL classes” as shown in below figure. I have named it as EmployeeInfo.

This will add an EmployeeInfo.dbml file inside “App_Code” folder.  In Visual Studio, EmployeeInfo.dbml will have 2 panes. The left pane is for designing the data objects and the right pane can be used for creating methods that operates on the data objects.

Once we include the LINQ to SQL Class in the project, there will be 3 files added to the App_Code.

EmployeeInfo.dbml

Ø       An XML file that contains information about the tables like table name, column name, type, etc

EmployeeInfo.layout

Ø       An XML file that specifies how the designer places the objects.

EmployeeInfo.designer.cs

Ø       The C# code file that will have code to interact with the underlying database called DataContext object. It also has entity class definition.

Open Server Explorer, Expand the database tables.

Drag Employee and Department into LINQ to SQL designer. The Objects will be created automatically. Refer the below figure.

This will update the EmployeeInfo.designer.cs file with class definition for Employee and Department. It will also generate the DataContext object for these objects for interacting with the underlying datasource. In our case, it is EmployeeInfoDataContext class. Open the file EmployeeInfo.designer.cs and we can see the generated code for datasource interactions.

The arrow between the object is the foreign key relationship between them. This means that the Employee class will have the Department class as a member.

Thus, we have modelled LINQ to SQL classes which has O/R mapping. Moving forward, we will use these objects to bind a ListView control.

Binding the ListView

ListView is a new databound control that is shipped with ASP.Net 3.5. In short, ListView control can be thought as a hybrid between GridView and Repeater control which can be used to display data in any custom layout with inbuilt edit, update, and sort feature.

In Part 1, we have used LinqDataSource control to bind the ListView control. In Part 2, we will bind the ListView Control without LinqDataSource control using LINQ to SQL classes and provide edit, update and delete functionality to it by writing custom LINQ query.

Steps

1.      Design the ListView control with LayoutTemplate, ItemTemplate, InsertItemTemplate, and EditItemTemplate. Refer the below code. We will have a dropdownlist for binding the list of available departments in InsertItemTemplate and EditItemTemplate.

<asp:ListView ID="lvEmployee" runat="server"         

InsertItemPosition="LastItem" onitemcanceling="lvEmployee_ItemCanceling"

            onitemediting="lvEmployee_ItemEditing"

             onitemupdating="lvEmployee_ItemUpdating"

             onitemdeleting="lvEmployee_ItemDeleting"

             oniteminserting="lvEmployee_ItemInserting">

        <LayoutTemplate>

                <table id="Table1" 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">

                                    </th>

                                    <th id="Th2" runat="server">

                                        EmpID</th>

                                    <th id="Th3" runat="server">

                                        EmpName</th>

                                    <th id="Th4" runat="server">

                                        Department</th>

                                    <th id="Th5" runat="server">

                                        Age</th>

                                    <th id="Th6" runat="server">

                                        Address</th>

                                </tr>

                                <tr ID="itemPlaceholder" runat="server">

                                </tr>

                            </table>

                        </td>

                    </tr>

                    <tr id="Tr3" runat="server">

                        <td id="Td2" runat="server" style="">

                        </td>

                    </tr>

                </table>

            </LayoutTemplate>

            <ItemTemplate>

        <tr style="">

                    <td>

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

                            Text="Delete" />

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

                    </td>

                    <td>

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

                    </td>

                    <td>

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

                    </td>

                    <td>

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

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

                    </td>

                    <td>

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

                    </td>

                    <td>

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

                    </td>

                </tr>

            </ItemTemplate>

 <EditItemTemplate>

                <tr style="">

                    <td>

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

                            Text="Update" />

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

                            Text="Cancel" />

                    </td>

                    <td>

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

                    </td>

                    <td>

                        <asp:TextBox ID="EmpNameTextBox" runat="server" Text='<%# Bind("EmpName") %>' />

                    </td>

                    <td>

                       <asp:DropDownList ID="DropDownList2" DataSource="<%# BindDept() %>"

                        SelectedValue='<%# Bind("DeptID") %>'            

                DataTextField="DepartmentName" DataValueField="DeptID" runat="server">

                </asp:DropDownList>

                    </td>

                    <td>

                        <asp:TextBox ID="AgeTextBox" runat="server" Text='<%# Bind("Age") %>' />

                    </td>

                    <td>

                        <asp:TextBox ID="AddressTextBox" runat="server" Text='<%# Bind("Address") %>' />

                    </td>

                </tr>

            </EditItemTemplate>

             <InsertItemTemplate>

                <tr style="">

                    <td>

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

                            Text="Insert" />

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

                            Text="Clear" />

                    </td>

                    <td>

                        &nbsp;</td>

                    <td>

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

                    </td>

                    <td>

                         <asp:DropDownList ID="DropDownList1"      

                DataTextField="DepartmentName" DataValueField="DeptID" runat="server">

                </asp:DropDownList>

                    </td>

                    <td>

                        <asp:TextBox ID="AgeTextBox" runat="server" />

                    </td>

                    <td>

                        <asp:TextBox ID="AddressTextBox" runat="server" />

                    </td>

                </tr>

            </InsertItemTemplate>

        </asp:ListView>

2.      Bind the ListView from the CodeBehind using LINQ query and LINQ to SQL classes.

protected void Page_Load(object sender, EventArgs e)

    {

        if (!IsPostBack)

        {

            BindEmp();

//Dropdown in InsertItemtemplate

            DropDownList ddl = lvEmployee.InsertItem.FindControl("DropDownList1") as DropDownList;

            ddl.DataSource = BindDept();

            ddl.DataBind();

        }

    }

    public void BindEmp()

    {

        EmployeeInfoDataContext dbEmp = new EmployeeInfoDataContext();

        var LINQQuery = from emp in dbEmp.Employees

                        select emp;

        lvEmployee.DataSource = LINQQuery;

        lvEmployee.DataBind();

    }

    public IEnumerable BindDept()

    {       

        EmployeeInfoDataContext dbEmp = new EmployeeInfoDataContext();

        var LINQQuery = from dept in dbEmp.Departments

                        select dept;

        return LINQQuery as IEnumerable;

    }

Execute the page; you can see ListView with employee records.

3.      For ListView edit, delete and insert command to work we need to write custom LINQ query and execute it using LINQ to SQL classes. The custom LINQ queries are bolded in the below code.

  protected void lvEmployee_ItemEditing(object sender, ListViewEditEventArgs e)

    {

        lvEmployee.EditIndex = e.NewEditIndex;

        BindEmp();

    }

    protected void lvEmployee_ItemCanceling(object sender, ListViewCancelEventArgs e)

    {

        lvEmployee.EditIndex = -1;

        BindEmp();

    }

    protected void lvEmployee_ItemUpdating(object sender, ListViewUpdateEventArgs e)

    {

        EmployeeInfoDataContext dbEmp = new EmployeeInfoDataContext();

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

       if(lblTemp !=null)

        string EmpID = lblTemp.Text;

        Employee empTemp = (from emp in dbEmp.Employees

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

                           select emp).Single();

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

        if (txtTemp != null)

            empTemp.EmpName = txtTemp.Text;

         DropDownList ddlTemp = lvEmployee.Items[e.ItemIndex].FindControl("DropDownList2") as DropDownList;

        if (txtTemp != null)

            empTemp.DeptID = int.Parse(ddlTemp.SelectedValue);

        txtTemp = lvEmployee.Items[e.ItemIndex].FindControl("AgeTextBox") as TextBox;

        if (txtTemp != null)

            empTemp.Age = int.Parse(txtTemp.Text);

        txtTemp = lvEmployee.Items[e.ItemIndex].FindControl("AddressTextBox") as TextBox;

        if (txtTemp != null)

            empTemp.Address = txtTemp.Text;

        dbEmp.SubmitChanges();

        lvEmployee.EditIndex = -1;

        BindEmp();

    }

    protected void lvEmployee_ItemDeleting(object sender, ListViewDeleteEventArgs e)

    {

        EmployeeInfoDataContext dbEmp = new EmployeeInfoDataContext();

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

       if(lblTemp !=null)

        string EmpID = lblTemp.Text;

        Employee empTemp = (from emp in dbEmp.Employees

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

                            select emp).Single();

        dbEmp.Employees.DeleteOnSubmit(empTemp);

        dbEmp.SubmitChanges();

        BindEmp();

    }

    protected void lvEmployee_ItemInserting(object sender, ListViewInsertEventArgs e)

    {

        EmployeeInfoDataContext dbEmp = new EmployeeInfoDataContext();

        Employee emp = new Employee();

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

       emp.EmpName = txtTemp.Text;

       DropDownList ddlTemp = e.Item.FindControl("DropDownList1") as DropDownList;

       emp.DeptID = int.Parse(ddlTemp.SelectedValue);

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

       emp.Age = int.Parse(txtTemp.Text);

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

       emp.Address = txtTemp.Text;

       dbEmp.Employees.InsertOnSubmit(emp);

       dbEmp.SubmitChanges();

       BindEmp();

    }  

Execute the page and see ListView edit/update/delete/insert in action using LINQ.