locked
Entity DataSource Where Clause: Get -Select All- in dropdown RRS feed

  • Question

  • User1864550844 posted

    I have used EntityDataSource to fill up the GridView. Here i am pasting my code snippet.

    <asp:EntityDataSource ID="edsCourse" runat="server" ConnectionString="name=SchoolEntities"
                        DefaultContainerName="SchoolEntities" EnableFlattening="False" EntitySetName="Courses"
                        EntityTypeFilter="Course" AutoGenerateWhereClause="true">
                        <WhereParameters>
                            <asp:ControlParameter ControlID="ddlDepartment" 
                                DefaultValue="0" Name="DepartmentID" PropertyName="SelectedValue" 
                                Type="Int32" />
                        </WhereParameters>
                    </asp:EntityDataSource>
                    <asp:GridView ID="gvCourse" runat="server" DataSourceID="edsCourse">
                        <Columns>
                            <asp:CommandField ShowEditButton="True" ShowDeleteButton="True" />
                            <asp:BoundField DataField="CourseID" HeaderText="CourseID" ReadOnly="True" SortExpression="CourseID" />
                            <asp:BoundField DataField="Title" HeaderText="Title" SortExpression="Title" />
                            <asp:BoundField DataField="Credits" HeaderText="Credits" SortExpression="Credits" />
                        </Columns>
                    </asp:GridView>

    and that is populate the Courses based on Department Dropdown which is here...

    <asp:EntityDataSource ID="edsDepartmentList" runat="server" 
                        EnableFlattening="False" EntitySetName="Departments" 
                        Select="it.[DepartmentID], it.[Name]" ConnectionString="name=SchoolEntities" 
                        DefaultContainerName="SchoolEntities" EntityTypeFilter="Department">
                    </asp:EntityDataSource>
                    Select a department:
                    <asp:DropDownList ID="ddlDepartment" runat="server" DataSourceID="edsDepartmentList"
                        DataTextField="Name" DataValueField="DepartmentID" AutoPostBack="True" AppendDataBoundItems="true" >
                        <asp:ListItem Text="All" Value="0"></asp:ListItem>
                    </asp:DropDownList>

    Now, it works fine. But I have two main concer...

    1) I want to add --All-- in my dropdown which gives me all the courses e-respective of department so, i can by default select that.

    2) Grid should not populate the record untill i press search button which would be beside of Drodown. So, User will select the department and then press search the Grid will populate the available courses..

    Can anyone tell me how can i achieve these two ?

    Saturday, July 2, 2011 5:31 AM

Answers

  • User3866881 posted

    Hello shrij.forum:)

    You can just handle Selecting event of the EF, I'd like to do a Category(One)——(Many)Product as an example——

    <%@ Page Language="C#" AutoEventWireup="true" CodeBehind="WebForm1.aspx.cs" Inherits="WebApplications.WebForm1" %>

    <%@ Register Assembly="System.Web.Entity, Version=3.5.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"
        Namespace="System.Web.UI.WebControls" TagPrefix="asp" %>
    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
    <html xmlns="http://www.w3.org/1999/xhtml">
    <head runat="server">
        <title></title>
    </head>
    <body>
        <form id="form1" runat="server">
        <div>
            <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataSourceID="ProductDataSource"
                EnableModelValidation="True">
                <Columns>
                    <asp:BoundField DataField="ProductID" HeaderText="ProductID" ReadOnly="True" SortExpression="ProductID" />
                    <asp:BoundField DataField="ProductName" HeaderText="ProductName" ReadOnly="True"
                        SortExpression="ProductName" />
                    <asp:BoundField DataField="SupplierID" HeaderText="SupplierID" ReadOnly="True" SortExpression="SupplierID" />
                </Columns>
            </asp:GridView>
            <asp:EntityDataSource ID="ProductDataSource" runat="server" ConnectionString="name=NorthwindEntities"
                DefaultContainerName="NorthwindEntities" EntitySetName="Products" EntityTypeFilter="Product"
                OnSelecting="ProductDataSource_Selecting" Select="it.[ProductID], it.[ProductName], it.[SupplierID]"
                Where="[it].SupplierId=@SupplierId">
                <WhereParameters>
                    <asp:ControlParameter ControlID="DropDownList1" DefaultValue="0" Name="SupplierId"
                        PropertyName="SelectedValue" Type="Int32" />
                </WhereParameters>
            </asp:EntityDataSource>
            <br />
            Choose a Category:<asp:DropDownList ID="DropDownList1" runat="server" AppendDataBoundItems="True"
                AutoPostBack="True" DataSourceID="CategoryDataSource" DataTextField="CategoryName"
                DataValueField="CategoryID">
                <asp:ListItem Value="0">All</asp:ListItem>
            </asp:DropDownList>
            <asp:EntityDataSource ID="CategoryDataSource" runat="server" ConnectionString="name=NorthwindEntities"
                DefaultContainerName="NorthwindEntities" EntitySetName="Categories" Select="it.[CategoryID], it.[CategoryName]">
            </asp:EntityDataSource>
        </div>
        </form>
    </body>
    </html>

     protected void ProductDataSource_Selecting(object sender, EntityDataSourceSelectingEventArgs e)
            {
               //if TotalRowCount = 0, this means you have choosen "All"
                ProductDataSource.WhereParameters.Clear();

                if (DropDownList1.SelectedValue=="0")
                {
                    ProductDataSource.Where = "";
                }
                else
                {
                    ProductDataSource.Where = "[it].SupplierId=@SupplierId";
                    ProductDataSource.WhereParameters.Add(new ControlParameter { ControlID = "DropDownList1", Name = "SupplierId", DefaultValue = "0", PropertyName = "SelectedValue", Type = System.TypeCode.Int32 });
                }
            }

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, July 4, 2011 10:16 PM

All replies

  • User-843484705 posted

    you need to add manually the code

    ddlDepartment.Items.Insert(0, new ListItem("-Select-", ""));
    
    
    and set ddlDepartment.SelectedIndex=0;
    It will resolved your both query.
    Saturday, July 2, 2011 7:03 AM
  • User3866881 posted

    Hello shrij.forum:)

    You can just handle Selecting event of the EF, I'd like to do a Category(One)——(Many)Product as an example——

    <%@ Page Language="C#" AutoEventWireup="true" CodeBehind="WebForm1.aspx.cs" Inherits="WebApplications.WebForm1" %>

    <%@ Register Assembly="System.Web.Entity, Version=3.5.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"
        Namespace="System.Web.UI.WebControls" TagPrefix="asp" %>
    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
    <html xmlns="http://www.w3.org/1999/xhtml">
    <head runat="server">
        <title></title>
    </head>
    <body>
        <form id="form1" runat="server">
        <div>
            <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataSourceID="ProductDataSource"
                EnableModelValidation="True">
                <Columns>
                    <asp:BoundField DataField="ProductID" HeaderText="ProductID" ReadOnly="True" SortExpression="ProductID" />
                    <asp:BoundField DataField="ProductName" HeaderText="ProductName" ReadOnly="True"
                        SortExpression="ProductName" />
                    <asp:BoundField DataField="SupplierID" HeaderText="SupplierID" ReadOnly="True" SortExpression="SupplierID" />
                </Columns>
            </asp:GridView>
            <asp:EntityDataSource ID="ProductDataSource" runat="server" ConnectionString="name=NorthwindEntities"
                DefaultContainerName="NorthwindEntities" EntitySetName="Products" EntityTypeFilter="Product"
                OnSelecting="ProductDataSource_Selecting" Select="it.[ProductID], it.[ProductName], it.[SupplierID]"
                Where="[it].SupplierId=@SupplierId">
                <WhereParameters>
                    <asp:ControlParameter ControlID="DropDownList1" DefaultValue="0" Name="SupplierId"
                        PropertyName="SelectedValue" Type="Int32" />
                </WhereParameters>
            </asp:EntityDataSource>
            <br />
            Choose a Category:<asp:DropDownList ID="DropDownList1" runat="server" AppendDataBoundItems="True"
                AutoPostBack="True" DataSourceID="CategoryDataSource" DataTextField="CategoryName"
                DataValueField="CategoryID">
                <asp:ListItem Value="0">All</asp:ListItem>
            </asp:DropDownList>
            <asp:EntityDataSource ID="CategoryDataSource" runat="server" ConnectionString="name=NorthwindEntities"
                DefaultContainerName="NorthwindEntities" EntitySetName="Categories" Select="it.[CategoryID], it.[CategoryName]">
            </asp:EntityDataSource>
        </div>
        </form>
    </body>
    </html>

     protected void ProductDataSource_Selecting(object sender, EntityDataSourceSelectingEventArgs e)
            {
               //if TotalRowCount = 0, this means you have choosen "All"
                ProductDataSource.WhereParameters.Clear();

                if (DropDownList1.SelectedValue=="0")
                {
                    ProductDataSource.Where = "";
                }
                else
                {
                    ProductDataSource.Where = "[it].SupplierId=@SupplierId";
                    ProductDataSource.WhereParameters.Add(new ControlParameter { ControlID = "DropDownList1", Name = "SupplierId", DefaultValue = "0", PropertyName = "SelectedValue", Type = System.TypeCode.Int32 });
                }
            }

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, July 4, 2011 10:16 PM