Tag Archives: QueryExtender

[RESOLVED] datetime in queryExtender

Written By admin

Web Forms, Post, QueryExtender , ,

Hi,

I like to use a QueryExtender to filter records between two dates. (From ... Untill)

anybody knows how to do this?

regards

 

Rob

Here is an article, it talks about filtering grid data based on a range but I am sure you will get an idea to implement your requirement

http://www.4guysfromrolla.com/articles/081110-1.aspx

This gives you the code

http://stackoverflow.com/questions/5301385/asp-net-queryextender-paramater-date-format-string

Hope this helps

hi Robwarning,

write like this

SELECT [SYS_ID]
      ,[FinancialYear]
      ,[BusinessGroup_ID]
      ,[CreatedOn]
  FROM [ARR_Master]
  where cast(cast(datepart(mm,[CreatedOn]) as varchar)+'-'+cast( datepart(dd,[CreatedOn]) as varchar)+'-'+cast(datepart(yyyy,[CreatedOn]) as varchar) as datetime) between (cast(('5-20-2011') as datetime)) and (cast(('5-21-2011') as datetime))

Hi ramreddyav

Are you using a QueryExtender range expression?  or a other QueryExtender  filter expression?

regards

 

Rob

[RESOLVED] DataSource types and performance

Written By admin

Web Forms, Table, Linq, EntityDataSource, ObjectDataSource, QueryExtender , , , , , 
Hi,

There are a couple of Datasource that I can use:
Access Database,  Sql Database, Entity, LINQ, Object,  EntityDataSource and QueryExtender.

What I see is that most of the code are done in the aspx page ( declarative ).
My question, is there a performance boost using ObjectDataSource EntityDataSource over ADO.Net....
 I personaly feel more comfortable using ado.net or objectDataSource.
Bob

this will give you some more idea

http://forums.asp.net/t/1823655.aspx/1?ObjectDataSource+Vs+EntityDataSource

[RESOLVED] "SearchExpression-QueryExtender"

Written By admin

Web Forms, Panel, Table, View, AJAX, DropDownList, TextBox, GridView, Post, Label, CheckBox, CheckBoxList, UpdatePanel, ScriptManager, Paging, FormView, EntityDataSource, ObjectDataSource, QueryExtender , , , , , , , , , , , , , , , , , , 
<asp:EntityDataSource ID="CustomersEDS" runat="server" ConnectionString="name=NORTHWNDEntities"
            DefaultContainerName="NORTHWNDEntities" EnableFlattening="False" EntitySetName="Customers"
            Select="it.[CustomerID], it.[CompanyName], it.[ContactName], it.[ContactTitle], it.[Country]"
            OrderBy="it.[CustomerID]" />
      
        </asp:EntityDataSource>
        <asp:QueryExtender ID="CustomersQE" runat="server" TargetControlID="CustomersEDS">
            <asp:SearchExpression DataFields="Country" SearchType="Contains">
                <asp:ControlParameter ControlID="TextBox1" />
            </asp:SearchExpression>
        </asp:QueryExtender>

Hi 

I'm using "SearchExpression" for "QueryExtender". I have attached my code here. Whenever I run my code I get an error saying:

"'Country' is not a member of type 'System.Data.Common.DbDataRecord'"

PS: I'm usnig NorthWind Database

Do you know why I'm getting this error message? Country is one of the fields!!!

Thanks

Please read :-

Web.Config Configuration

<configuration>
  <system.web>
    <pages>
      <controls>
        <!-- register web controls for use on all pages -->
        <add tagPrefix="data" namespace="Northwind.Web.Data" assembly="Northwind.Web"/>
        <add tagPrefix="data" namespace="Northwind.Web.UI" assembly="Northwind.Web"/>
      </controls>
    </pages>
    <httpModules>
      <!--
        Enable transaction-per-request pattern;
        This module allows a single TransactionManager to be shared by
        all data access operations executed during a page request.
        Used by the EntityDataSource control when
        the EntityDataSource.EnableTransaction property is set to True.
      -->
      <add name="EntityTransactionModule"
            type="Northwind.Web.Data.EntityTransactionModule, Northwind.Web"/>
    </httpModules>
  </system.web>
</configuration>
  



Using the Typed DataSource Controls

Strongly Typed DataSource controls: You will find that just by typing <data: you will see a list of your DataSource controls ready to be used to get ahold of your data. These maintain the ObjectDataSource controls event model, except they are geared to your data, relationships, and domain.
 
Intellisense:





Productivity Enabler
When you select the SelectMethod Attribute, you will see via intellisense a list of all available methods from your domain. How cool is that!

Intellisense:

Simple WebForm Example:
Here's an example of a simple webform in it's minimilist form, that would display a grid of all Employees with Auto-Generated columns.


 
Design View:|
Taking the form and selecting only the columns that I want, we create a nice grid.







Data View Results:
Here's the result of of the form. The wonderful part of the Typed DataSource control is that you don't have to do anything when you want to edit/insert/delete an object.  You simply have the grid call the Edit/Delete/Insert command, which can be done a variety of ways in ASP.net 2.0.


   

Edit:


After Edit:





TypedDataSource w/ Atlas Example
GridView Paging, Sorting and inline Update/Delete




Click to Enlarge











Creating the Grid

First, create a new page and add the following GridView control:

NOTE: It's very important that you specify the DataKeyNames property of the Gridview, otherwise, Insert/Update/Delete will not work.


<asp:GridView ID="GridView1" AutoGenerateColumns="false" runat="server"
       DataSourceID="CustomersDataSource1"
       DataKeyNames="CustomerID"
       PageSize="10" AllowPaging="true" AllowSorting="true">
   <Columns>
      <asp:CommandField ShowEditButton="True" />
      <asp:BoundField DataField="CustomerID" HeaderText="Cust #" SortExpression="CustomerID" />
      <asp:BoundField DataField="CompanyName"
            HeaderText="Company" SortExpression="CompanyName" />
      <asp:BoundField DataField="ContactTitle" HeaderText="Title" SortExpression="ContactTitle" />
      <asp:BoundField DataField="ContactName" HeaderText="Name"
            SortExpression="ContactName" />
      <asp:BoundField DataField="City" HeaderText="City" SortExpression="City" />
      <asp:BoundField DataField="Region" HeaderText="Region" SortExpression="Region" />
      <asp:BoundField DataField="PostalCode" HeaderText="Postal Code"
            SortExpression="PostalCode" />
      <asp:BoundField DataField="Country" HeaderText="Country" SortExpression="Country" />
      <asp:CommandField ShowDeleteButton="true" />
   </Columns>
</asp:GridView>


Next, add the CustomersDataSource control to your page:
This is the tough part, so strap it in!!!

<data:CustomersDataSource ID="CustomersDataSource1"
   runat="server"
   SelectMethod="GetPaged"
   EnablePaging="true"
   EnableSorting="true"/>

Umm... That's it!
And you should have a functional GridView with paging and sorting!
Using Atlas:
Now here comes the fun part, Atlasifying (see dictionary) your grid.  As you know microsoft released an extremely useful Ajax & more library called Atlas.

More information can be found:
http://atlas.asp.net



Demonstrating Atlas with .netTiers
Now we want to add a filter to our page to demonstrate a new feature to NetTiers:

<table border="0" width="310" style="margin-bottom:10px;">
<tr>
   <td>
      <asp:Label ID="CountryLabel"
            AssociatedControlID="CountryList" runat="server" Text="Country:" />
   </td>
   <td>
      <asp:DropDownList ID="CountryList" AutoPostBack="true" runat="server">
            <asp:ListItem Value="">All</asp:ListItem>
            <asp:ListItem>Brazil</asp:ListItem>
            <asp:ListItem>Canada</asp:ListItem>
            <asp:ListItem>France</asp:ListItem>
            <asp:ListItem>Germany</asp:ListItem>
            <asp:ListItem>Italy</asp:ListItem>
            <asp:ListItem>Mexico</asp:ListItem>
            <asp:ListItem>Spain</asp:ListItem>
            <asp:ListItem>UK</asp:ListItem>
            <asp:ListItem>USA</asp:ListItem>
            <asp:ListItem>Venezuela</asp:ListItem>
      </asp:DropDownList>
   </td>
   <td align="right">
      <asp:Label ID="TimeLabel" runat="server" Font-Names="Verdana" Font-Size="10px">
            <%= DateTime.Now %/></asp:Label>
   </td>
</tr>
</table>

Next, change the data source control above to add a parameter for the whereClause parameter of the GetPaged method.  The SqlParameter allows you to define external filter controls that will be combined to generate the filter expression:

<data:CustomersDataSource ID="CustomersDataSource1" runat="server"
   SelectMethod="GetPaged" EnablePaging="true" EnableSorting="true">
   <Parameters>
      <data:SqlParameter Name="WhereClause" UseParameterizedFilters="false">
         <Filters>
            <data:CustomersFilter Column="Country" ControlID="CountryList" />
         </Filters>
      </data:SqlParameter>
   </Parameters>
</data:CustomersDataSource>



Atlas Magic:
Now we can add the magic!  Make sure you have a reference to the Atlas Script Manager:

<atlas:ScriptManager ID="ScriptManager1" EnablePartialRendering="true" runat="server" />

We want to wrap the GridView control with an UpdatePanel:

<atlas:UpdatePanel ID="GridPanel" Mode="Conditional" runat="server">
   <ContentTemplate>

      <!-- GridView declaration goes here -->

   </ContentTemplate>
   <Triggers>
      <atlas:ControlEventTrigger ControlID="CountryList" EventName="SelectedIndexChanged" />
   </Triggers>
</atlas:UpdatePanel>

Notice that the trigger is tied to the CountryList control so that the GridView is updated every time the selected country is changed.
 Poof!!!  All Done.



Using the EntityDataSource Control
The EntityDataSource controls is useful because it uses 100% reflection to work with the data providers.  This is great when you need to change the type at runtime, or maybe you have written your own methods within the DataAccessLayer that you would like to call.    You would simply use the EntityDataSource control.



<data:EntityDataSource ID="dataSourceID" runat="server"
   TypeName="Namespace.Data.DataRepository, Namespace.Data"
   TypeProperty=""
   ProviderName=""
   EntityTypeName=""
   EntityKeyTypeName="System.Guid"
   EntityKeyName=""
   SelectMethod="GetAll"
   InsertMethod="Insert"
   UpdateMethod="Update"
   DeleteMethod="Delete"
   EnablePaging="false"
   EnableSorting="false"
   EnableTransaction="true"
   InsertDateTimeNames=""
   UpdateDateTimeNames=""
   Filter=""
   Sort=""

   OnSelecting=""
   OnSelected=""
   OnInserting=""
   OnInserted=""
   OnUpdating=""
   OnUpdated=""
   OnDeleting=""
   OnDeleted=""
>
   <Parameters>
   </Parameters>
</data:EntityDataSource>

ProviderName (required) - an alias for TypeProperty; the name of the static property of the DataRepository class which returns a reference to the specific provider instance.
   ex:  ProviderName="CustomersProvider"  for Northwind.Data.DataRepository.CustomersProvider

EntityTypeName (required) - the fully qualified class name of the entity class that is handled by the specified data provider.

EntityKeyName - the name of the property that holds the primary key value.

EntityKeyTypeName - the fully qualified class name for the return type of the EntityKeyName property.

EnableTransaction - set to True if the EntityDataSource should rely on the EntityTransactionModule class to supply a TransactionManager object.

InsertDateTimeNames - a comma-separated list of DateTime property names to initialize to the current DateTime.Now value during an insert operation.

UpdateDateTimeNames - a comma-separated list of DateTime property names to initialize to the current DateTime.Now value during an update operation.

Filter - the value to apply to the ListBase.Filter property after a select operation (only if EnablePaging is false)

Sort - the value of the parameter to pass to the ListBase.Sort(string) method after a select operation (only if EnableSorting is false)
 
Example:
This is an example of populating a DropDownList control with data retrieved using the EntityDataSource control.

<asp:Label ID="ShipViaLabel" runat="server" Text="Ship Via" AssociatedControlID="ShipViaList" />

<asp:DropDownList ID="ShipViaList" runat="server"
    SelectedValue='<%# Bind("ShipVia") %>'
    AppendDataBoundItems="True"
    DataSourceID="ShippersDataSource"
    DataTextField="CompanyName"
    DataValueField="ShipperID"
>
    <!-- an empty ListItem along with the AppendDataBoundItems property allow for a nullable relationship -->
    <asp:ListItem Value="" Text="" />
</asp:DropDownList>

<data:EntityDataSource ID="ShippersDataSource" runat="server"
    ProviderName="ShippersProvider"
    EntityTypeName="Northwind.BLL.Shippers, Northwind.BLL"
    EntityKeyTypeName="System.Int32"
    SelectMethod="GetAll"
    Sort="CompanyName ASC"
/>

 Using Many To Many Relationship Controls
The ManyToManyListRelationship control manages the junction table that links your primary entity with a foreign key table.  Please see highlighted code below.

<asp:FormView ID="FormView1" runat="server" DataSourceID="CustomersDataSource" DefaultMode="Edit">
    <EditItemTemplate>
        <table border="0">
        <tr>
            <td>Cust #:</td>
            <td><asp:Label ID="CustomerIDLabel" runat="server" Text='<%# Bind("CustomerID") %>' /></td>
        </tr>
        <tr>
            <td>Company Name:</td>
            <td><asp:TextBox ID="CompanyNameTextBox" runat="server" Text='<%# Bind("CompanyName") %>' /></td>
        </tr>
        <tr>
            <td valign="top">Demographics:</td>
            <td>
                <!-- the visual representation of the many-to-many relationship -->
                <asp:CheckBoxList ID="CustomerDemographicsList" runat="server"
                    DataSourceID="CustomerDemographicsDataSource"
                    DataTextField="CustomerDesc"
                    DataValueField="CustomerTypeID"
                />
                
                <!-- provides the list of available data for the relationship -->
                <data:EntityDataSource ID="CustomerDemographicsDataSource" runat="server"
                    ProviderName="CustomerDemographicsProvider"
                    EntityTypeName="Northwind.BLL.CustomerDemographics, Northwind.BLL"
                    SelectMethod="GetAll"
                    Filter="CustomerDesc != 'Temporary'"
                    Sort="CustomerDesc ASC"
                />
                
                <!-- provides management of the link table -->
                <data:EntityDataSource ID="CustomerCustomerDemoDataSource" runat="server"
                    ProviderName="CustomerCustomerDemoProvider"
                    EntityTypeName="Northwind.BLL.CustomerCustomerDemo, Northwind.BLL"
                    EntityKeyTypeName="System.String"
                    SelectMethod="GetByCustomerID"
                >
                    <Parameters>
                        <asp:QueryStringParameter Name="EntityId" QueryStringField="id" />
                    </Parameters>
                </data:EntityDataSource>
                
                <!--
                    The relationship controls hook one or more EntityDataSource controls to the
                    EntityDataSource specified by the PrimaryMember.EntityDataSourceID property.
                    This allows multiple insert/update operations to be executed during a single
                    form submission.
                -->
                <data:ManyToManyListRelationship ID="CustomerCustomerDemoRelationship"                                         runat="server">
                    <%-- represents the Customers table --%>
                    <PrimaryMember runat="server"
                        DataSourceID="CustomersDataSource"
                        EntityKeyName="CustomerID"
                    />
                    <%-- represents the CustomerCustomerDemo link table --%>
                    <LinkMember runat="server"
                        DataSourceID="CustomerCustomerDemoDataSource"
                        EntityKeyName="CustomerID"
                        ForeignKeyName="CustomerTypeID"
                    />
                    <%-- represents the CustomerDemographics table --%>
                    <ReferenceMember runat="server"
                        DataSourceID="CustomerDemographicsDataSource"
                        ListControlID="CustomerDemographicsList"
                        EntityKeyName="CustomerTypeID"
                    />
                </data:ManyToManyListRelationship>
            </td>
        </tr>
        </table>
    </EditItemTemplate>
    
    <FooterTemplate>
        <asp:Button ID="UpdateButton" runat="server" CausesValidation="True" CommandName="Update" Text="Update" />
        <asp:Button ID="CancelButton" runat="server" CausesValidation="False" CommandName="Cancel" Text="Cancel" />
    </FooterTemplate>
</asp:FormView>

<!-- primary entity data source -->
<data:EntityDataSource ID="CustomersDataSource" runat="server"
    ProviderName="CustomersProvider"
    EntityTypeName="Northwind.BLL.Customers, Northwind.BLL"
    EntityKeyTypeName="System.String"
    EntityKeyName="CustomerID"
    SelectMethod="GetByCustomerID"
>
    <Parameters>
        <asp:QueryStringParameter Name="EntityId" QueryStringField="id" Type="String" />
    </Parameters>
</data:EntityDataSource>



It seems you have copied this text from somewhere and the pictures are not copied properlly.(For example see"Click to Enlarge", but no picture). And I guess you did not understand my quetion, since the answer is not about QueryExtender! However, I found my answer in the following link. My question may be the question of many other people in the future too, so I put the link which is answer of my question.

http://social.msdn.microsoft.com/Forums/en-US/adodotnetentityframework/thread/58ca70a1-72f1-4f2d-bf12-0741cace347d

have you tried this - http://social.msdn.microsoft.com/Forums/en-US/adodotnetentityframework/thread/58ca70a1-72f1-4f2d-bf12-0741cace347d 

Thanks,

Thats the link I mentioned in my previous post! and yes that link answered my question. Thanks :-)

Yes , i have copied e this text from somewhere else, just to read query extender before implimenting it.

if you have read That text itsself a reply your answer in select statement use select All

web forms query extender linq entity framework

Written By admin

Web Forms, DropDownList, Post, Linq, EntityDataSource, QueryExtender , , , , ,

I am using webforms with dropdown list,query extender . In the drop down list I have included All to display all the records without filtering.

using query extender with custom expression.   When I try to filter it is filering for all the records first time. and whenever I change the value in the dropdown list nothing is happening ... Herewith I have attached the dropdown code,query extender,linq query to filter..

dropdown list

==========

 <asp:DropDownList ID="DropDownList1" runat="server" AppendDataBoundItems ="true"
            AutoPostBack="True" DataSourceID="DeptEntityDataSource"
            DataTextField="deptname" DataValueField="deptid" Height="25px"
            Width="189px" >
           <asp:ListItem Value="-1" Selected="True" >All</asp:ListItem>
           </asp:DropDownList>

------------------

query extender

===========

   <asp:QueryExtender ID="staffsQE" runat="server"
            Targetcontrolid="staffDataSource">
    
            <asp:CustomExpression OnQuerying="FilterAll"></asp:CustomExpression>

        </asp:QueryExtender>

-------------------------

linq query

==========

   protected void FilterAll(object sender, CustomExpressionEventArgs e)
        {
            idept = Convert.ToInt32(DropDownList1.SelectedValue);

            if (idept==-1)
                
                 
            {

//  to display all recs//
                e.Query = from p in e.Query.Cast<staff>()
                          where p.deptid == p.deptid
                          select p;
            }
            else
            {

//to display select deptid recs //
                idept = Convert.ToInt32(DropDownList1.SelectedValue);
                e.Query = from p in e.Query.Cast<staff>()
                          where p.deptid == idept
                          select p;
                
                          
            }

        }

-------------------------

[RESOLVED] Which datasource to use

Written By admin

Web Forms, Table, Xml, Linq, Wizard, SqlDataSource, Entity Framework, QueryExtender , , , , , , ,

Till now I've pretty much stuck to using the sqldatasource to read/write data.

I've been looking at the other options and am not sure which one to learn/use in future (for usability and to keep up to date).
I've just watched some video that has pretty much walked through each without pausing for breath.
Object, Entity, Linq, Query?

Can someone shed some light on the pros and cons, the ones likely to be continue to be used, benefits etc please?

Object, Entity, LinQ, Sqdatasouce etc, All are same, hard to understand.

Try to understand one of thus first, then go to another.

Hi, not sure I understand your reply....
I know and use the sql and xml datasources but am looking to extend my learning by looking at other datasource types.

When I look in the data toolbox (and read the options online) I see object, entity and linq datasources.
I have tried the object and played with some linq in the CS file. I have played with the entityframework and added an entity datasource and queryextender tags to my aspx file.

As they both (and perhaps the linq datasource too?) all seem to ultimately achieve the same thing, I wondered what the advantages/disadvantages of each were?

I can see that using an object over a sql datasource allows me to separate my business logic out to a reusable location but I also see a lot more typing (no wizard) to achieve a similar result.

 

 

hi,

There are some differences between Entity Framework and L2S.

  • Entity framework has a full provider model. It supports not only SQL Server but also other database like Oracle, DB2, MySQL etc.
  • Most of the time L2S classes must be one-to-one with database objects e.g. Customer class can be mapped only with Customer table. Where as in Entity Framework you can map your domain class with multiple tables using various inheritance strategies like table per type (class) or table per hierarchy of classes etc.
  • You can have multiple modeling techniques using Entity Framework 4.1 like code first, model first or database first.
  • Microsoft has long term strategy to support and integrate Entity Framework with multiple Microsoft products.

 

we usually use Entity Framework and L2S to insert/updata/select/delete data.and this is hard to grasp.

Best Regards.

 

Choosing a Data source depends on what type of project you want to build. Wizard DataSource such as SqlDataSource are good especiall for simple queries and small projects. But it tends to get more complicated and confusing when it comes to intertwined queries. SqlDataSource is also hard to maintain in case of huge blocks of code and we have to generate umpteen DataSources for different data controls.  I typically use Custom Entity as my data source or sometimes using Entity Framework (object relational mapper) and sometimes with the traditional ADO.NET with DataTable as we have full control over our codes and we can reuse the code without any hiccups changing the basic query here and there according to requirements and It is also a lot easier to maintain it.. This short discussions may help: http://forums.asp.net/t/1276704.aspx, http://stackoverflow.com/questions/2698151/entity-framework-vs-linq-to-sql-vs-ado-net-with-stored-procedures