locked
Capturing the SQL used by a GridView RRS feed

  • Question

  • User-1847166894 posted

    Let's say I have 3 tables. 

    1) TypeOfQuestions

    2) Questions ... which might as well have some other filters acting as Foreign Keys

    3) AnswersToQuestions

    If I filter (2) Questions by (1) TypeOfQuestions and all other filters that might have been chosen by the end-user, then, somehow, I think I could use that data and the query with all its filtering (don't even know how) as DataSource for feeding the (3)  Questions table so I can save each questions plus its answer in table number (2)

    Is that doable?

    Friday, March 31, 2017 10:29 PM

Answers

  • User-330204900 posted

    I'm no expert, I just started SQL Profiler up and then stopped it once the query I wanted to capture had been run then I went through each entry until I found the actual query I wanted :)

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, April 7, 2017 3:13 PM

All replies

  • User-1509636757 posted

    I understand that you are filtering a GridView or some Data Control with some filter options like TypeOfQuestions, etc. and you require to further use the filtered resulting Data. If this is correct then I would suggest to save the filter conditions in Session or (preferably) save the filtered resulting data to session to use.

    Sunday, April 2, 2017 8:30 AM
  • User-1847166894 posted

    Thanks for your answer. It might be very, very helpful, if I knew how to do that? Is there a way which you might recommend?

    Sunday, April 2, 2017 12:58 PM
  • User-1509636757 posted

    How do you filter and bind your GridView right now?  You might be using List or DataTable/DataSet of some other kind of DataSource? You just need to save that data source in a session variable. This way you will be able to get the filtered result data directly. And if you wish to get the query for this data, then I would suggest to store the filter criteria in session variable to be used in further, when required. You may post your affecting code, if you need further assistance.

    Sunday, April 2, 2017 1:35 PM
  • User-1458155879 posted
    It might be like that anywhere but dynamic data ... It is toó different here
    Sunday, April 2, 2017 2:16 PM
  • User-707554951 posted

    Hi CPorras,

    From your description. it is hard for us to provide you with a specific solution for you.

    Would you please provide us with related code you have done at present and with the description of your next step.

    So that we could get what is your problem. and provide you with some code for you.

    Best regards

    Cathy

    Monday, April 3, 2017 3:27 AM
  • User-1847166894 posted

    List page code is

    The purpose of QueryableFilterRepeater is to generate set of filters for a set of columns. It should contain DynamicFilter control which is the actual placeholder for a FilterTemplate control. QueryableFilterRepeater implements IFilterExpressionProvider interface that is supported by QueryExtender via DynamicFilterExpression control.

    using System;
    using System.ComponentModel;
    using System.ComponentModel.DataAnnotations;
    using System.Web.DynamicData;
    using System.Web.Routing;
    using System.Web.UI;
    using System.Web.UI.WebControls;
    using System.Web.UI.WebControls.Expressions;
    
    namespace WebApplication4
    {
        public partial class List : System.Web.UI.Page
        {
            protected MetaTable table;
    
            protected void Page_Init(object sender, EventArgs e)
            {
                table = DynamicDataRouteHandler.GetRequestMetaTable(Context);
                GridView1.SetMetaTable(table, table.GetColumnValuesFromRoute(Context));
                GridDataSource.EntityTypeFilter = table.EntityType.Name;
    
            }
    
            protected void Page_Load(object sender, EventArgs e)
            {
                Title = table.DisplayName;
                GridDataSource.Include = table.ForeignKeyColumnsNames;
    
                // Disable various options if the table is readonly
                if (table.IsReadOnly)
                {
                    GridView1.Columns[0].Visible = false;
                    InsertHyperLink.Visible = false;
                    GridView1.EnablePersistedSelection = false;
                }
            }
    
            protected void Label_PreRender(object sender, EventArgs e)
            {
                Label label = (Label)sender;
                DynamicFilter dynamicFilter = (DynamicFilter)label.FindControl("DynamicFilter");
                QueryableFilterUserControl fuc = dynamicFilter.FilterTemplate as QueryableFilterUserControl;
                if (fuc != null && fuc.FilterControl != null)
                {
                    label.AssociatedControlID = fuc.FilterControl.GetUniqueIDRelativeTo(label);
                }
            }
    
            protected override void OnPreRenderComplete(EventArgs e)
            {
                RouteValueDictionary routeValues = new RouteValueDictionary(GridView1.GetDefaultValues());
                InsertHyperLink.NavigateUrl = table.GetActionPath(PageAction.Insert, routeValues);
                base.OnPreRenderComplete(e);
            }
    
            protected void DynamicFilter_FilterChanged(object sender, EventArgs e)
            {
                GridView1.PageIndex = 0;
            }
    
        }
    }
    

    <%@ Page Language="C#" MasterPageFile="~/Site.master" CodeBehind="List.aspx.cs" Inherits="WebApplication4.List" %>
    
    <%@ Register src="~/DynamicData/Content/GridViewPager.ascx" tagname="GridViewPager" tagprefix="asp" %>
    
    <asp:Content ID="headContent" ContentPlaceHolderID="head" Runat="Server">
    </asp:Content>
    
    <asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
        <asp:DynamicDataManager ID="DynamicDataManager1" runat="server" AutoLoadForeignKeys="true">
            <DataControls>
                <asp:DataControlReference ControlID="GridView1" />
            </DataControls>
        </asp:DynamicDataManager>
    
        <h2 class="DDSubHeader"><%= table.DisplayName%></h2>
    
        <asp:UpdatePanel ID="UpdatePanel1" runat="server">
            <ContentTemplate>
                <div class="DD">
                    <asp:ValidationSummary ID="ValidationSummary1" runat="server" EnableClientScript="true"
                        HeaderText="List of validation errors" CssClass="DDValidator" />
                    <asp:DynamicValidator runat="server" ID="GridViewValidator" ControlToValidate="GridView1" Display="None" CssClass="DDValidator" />
    
                    <asp:QueryableFilterRepeater runat="server" ID="FilterRepeater">
                        <ItemTemplate>
                            <asp:Label runat="server" Text='<%# Eval("DisplayName") %>' OnPreRender="Label_PreRender" />
                            <asp:DynamicFilter runat="server" ID="DynamicFilter" OnFilterChanged="DynamicFilter_FilterChanged" /><br />
                        </ItemTemplate>
                    </asp:QueryableFilterRepeater>
                    <br />
                </div>
    
                <asp:GridView ID="GridView1" runat="server" DataSourceID="GridDataSource" EnablePersistedSelection="true"
                    AllowPaging="True" AllowSorting="True" CssClass="DDGridView"
                    RowStyle-CssClass="td" HeaderStyle-CssClass="th" CellPadding="6">
                    <Columns>
                        <asp:TemplateField>
                            <ItemTemplate>
                                <asp:DynamicHyperLink runat="server" Action="Edit" Text="Edit"
                                />&nbsp;<asp:LinkButton runat="server" CommandName="Delete" Text="Delete"
                                    OnClientClick='return confirm("Are you sure you want to delete this item?");'
                                />&nbsp;<asp:DynamicHyperLink runat="server" Text="Details" />
                            </ItemTemplate>
                        </asp:TemplateField>
                    </Columns>
    
                    <PagerStyle CssClass="DDFooter"/>        
                    <PagerTemplate>
                        <asp:GridViewPager runat="server" />
                    </PagerTemplate>
                    <EmptyDataTemplate>
                        There are currently no items in this table.
                    </EmptyDataTemplate>
                </asp:GridView>
    
                <ef:EntityDataSource ID="GridDataSource" runat="server" EnableDelete="true" />
                
                <asp:QueryExtender TargetControlID="GridDataSource" ID="GridQueryExtender" runat="server">
                    <asp:DynamicFilterExpression ControlID="FilterRepeater" />
                </asp:QueryExtender>
    
                <br />
    
                <div class="DDBottomHyperLink">
                    <asp:DynamicHyperLink ID="InsertHyperLink" runat="server" Action="Insert"><img runat="server" src="~/DynamicData/Content/Images/plus.gif" alt="Insert new item" />Insert new item</asp:DynamicHyperLink>
                </div>
            </ContentTemplate>
        </asp:UpdatePanel>
    </asp:Content>
    
    



    I am just respectful saying what I know about ASP.Net Dynamic Data

    So filtering works like this

    So I add this 2 pictures in order to let you know what ASP.Net Dynamic Data is about. So in its simplest way, it doesn't really manages postback, state, ...and traditional ASP.Net Web Forms stuff

    It works quite different because it works based on templates. You only have 4 pages: List, Edit, Insert and Details and a ListDetails alternative one 

    .

    .

     

    Monday, April 3, 2017 6:34 AM
  • User-1847166894 posted

    Maybe quite overwhelming, isn't it?
    But there are people in the dynamic data team, as Mr. Eboo, that clearly know this well.
    I was hoping maybe one of you might request him an answer to this

    What I'd like to accomplish is this: having a filtered datasource which will be used in a gridview to displaying some data, then I'd like to have such LINQ sentence or SQL query so I can build on the fly a new datasource (using a diferent table than the one used in the gridview from which I extracted the LINQ) as to accomplish that the saving process goes directly into another table which will be defined in the new datasource ...

    ERD

    https://ibb.co/nnzYAa

    Filtering Conditions

    https://ibb.co/hsEN3v

    GridView Source Table

    https://ibb.co/jv3tAa

    Entity in which I'd like to save the data

    https://ibb.co/fLQ6Va



    Sounds crazy but I thinks is doable and I just like to know:

    AT WHICH POINT INSIDE SOME METHOD MAY I CAPTURE SUCH LINQ SENTENCE (OR SQL SENTENCE) so I could reach it via debugging and grab the wanted sentence

    Having this, I could simulate the many-to-many improved control that currently seems not to be available anywhere else on the web

    Tuesday, April 4, 2017 5:58 PM
  • User-1847166894 posted

    It should be better like this to do the filtering at once .. isn't it?

    Tuesday, April 4, 2017 6:06 PM
  • User-330204900 posted

    Hi Carlos, if you just need to see the query I usually use SQL Profiler to get the SQL DD the EF Data Source creates, it's very messy but it works.

    Friday, April 7, 2017 11:25 AM
  • User-1847166894 posted

    good steve, ... excellent !

    I just need to know "where" ... at what step in the process may I reach such messy sentence 

    I'd be delighted if I knew that

    Yo Are My Hero Steve !!!!!!!!!!!!!!!!!!!

    Friday, April 7, 2017 2:41 PM
  • User-330204900 posted

    I'm no expert, I just started SQL Profiler up and then stopped it once the query I wanted to capture had been run then I went through each entry until I found the actual query I wanted :)

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, April 7, 2017 3:13 PM