Has anybody been successful with enabling the LLBLGen data source to work with the Infragistics grid? I'm talking about making full use of the data source features - updates, inserts, deletes, and paging. Here's a simple scenario - there's a grid and a textbox for filtering the results. The user can type some value in the textbox, hit the Find button, and see the relevant results.

The problem is that the data source needs to be bound to the grid very early in the page's life, before Page_Load (InitializeDataSource event), in order for updates, inserts, and deletes to take place. This is problematic, since the Click event on the Find button is fired after all of these and right before Page_PreRender. Since that event directly affects the data that should be bound to the grid, we have a problem - we have already hit the database for the older, non-filtered, data.

I like to do the data binding in Page_PreRender - that way I can react to UI events that might affect the data displayed. I also have a hunch that the grid should use ViewState to avoid the extra binding hit to the DB in the data initialization process. However I have not been successful in pulling it off - all sorts of weird issues came up.

I'm almost there - I used a funky method of caching data in the data source control itself via ViewState and then binding the EntityCollection directly in InitializeDataSource, while reloading the live data in Page_PreRender. It almost works because, while fully functional on page 1 of the grid, the editing events (updates, inserts, deletes) do not fire on other pages. Sigh.

This has been driving me absolutely crazy - am I missing something?

Thanks!

You shouldn't have to do any coding related to the databinding, i.e. you shouldn't setup the binding yourself, that's automatic. Could you paste some HTML / markup and relevant code from the codebehind which illustrates what you're doing?

Here is a simple example that actually does what I need, except that with the SQL Profiler I can see one or two extra DB fetches for each postback (paging, or editing). Can I get the same thing accomplished, but only going to the database when necessary?

<asp:TextBox ID="txtClient" runat="server"></asp:TextBox>
<asp:Button ID="cmdFind" runat="server" Text="Find" OnClick="cmdFind_Click" />

<cc1:LLBLGenProDataSource2 ID="ormDataSource" runat="server" EnablePaging="True">
</cc1:LLBLGenProDataSource2>

<igtbl:UltraWebGrid ID="grdClients" runat="server" EnableViewState="False" DataSourceID="ormDataSource" OnDeleteRow="grdClients_DeleteRow" OnUpdateRow="grdClients_UpdateRow" >

    <Bands>
        <igtbl:UltraGridBand AllowUpdate="yes" AllowDelete="Yes" DataKeyField="ID" AllowAdd="yes" AddButtonCaption="Client">
            <Columns>
                <igtbl:UltraGridColumn BaseColumnName="Description" Key="Description">
                    <Header Caption="Client"></Header>
                </igtbl:UltraGridColumn>
                <igtbl:UltraGridColumn BaseColumnName="Identifier" Key="Identifier">
                    <Header Caption="Identifier"></Header>
                </igtbl:UltraGridColumn>
                <igtbl:UltraGridColumn BaseColumnName="ID" Key="ID" Hidden="true"></igtbl:UltraGridColumn>
            </Columns>
        </igtbl:UltraGridBand>
    </Bands>

    <DisplayLayout AutoGenerateColumns="false" >
        <Pager AllowPaging="true" PageSize="3" />
        <AddNewBox Hidden="false" />
    </DisplayLayout>

</igtbl:UltraWebGrid>

        protected void Page_Init(object sender, EventArgs e)
        {
            ormDataSource.DataContainerType = DataSourceDataContainerType.EntityCollection;
            ormDataSource.EntityFactoryTypeName = typeof(ClientEntityFactory).AssemblyQualifiedName;
            ormDataSource.AdapterToUse = DataAdapter;
            ormDataSource.SorterToUse = new SortExpression(ClientFields.Description | SortOperator.Ascending);
        }


        protected void Page_Load(object sender, EventArgs e)
        {

        }

        protected void Page_PreRender(object sender, EventArgs e)
        {
            PredicateExpression expr = new PredicateExpression();

            string clientName = (string)ViewState["clientName"];
            if (clientName != null)
            {
                string pattern = string.Concat("%", clientName.ToUpper(), "%");
                FieldLikePredicate filter = (FieldLikePredicate)(ClientFields.Description % pattern);
                filter.CaseSensitiveCollation = true;
                expr.Add(filter);
            }

            ormDataSource.FilterToUse = new RelationPredicateBucket(expr);

            grdClients.DataBind();

        }

        protected void grdClients_DeleteRow(object sender, RowEventArgs e)
        {
            // Stub - causes grid to postback on row delete
        }


        protected void grdClients_UpdateRow(object sender, RowEventArgs e)
        {
            // Stub - causes grid to postback on row update
        }

        protected void cmdFind_Click(object sender, EventArgs e)
        {
            grdClients.DisplayLayout.Pager.CurrentPageIndex = 1;
            ViewState["clientName"] = (txtClient.Text.Length > 0) ? txtClient.Text : null;
        }

P.S. I have the latest components.

Roman wrote:

Here is a simple example that actually does what I need, except that with the SQL Profiler I can see one or two extra DB fetches for each postback (paging, or editing). Can I get the same thing accomplished, but only going to the database when necessary?

Please, stick with one issue at a time. Does it or doesn't it work? If it works, OK, then we can move on to the other issues.

[code] protected void Page_Init(object sender, EventArgs e) { ormDataSource.DataContainerType = DataSourceDataContainerType.EntityCollection; ormDataSource.EntityFactoryTypeName = typeof(ClientEntityFactory).AssemblyQualifiedName; ormDataSource.AdapterToUse = DataAdapter; ormDataSource.SorterToUse = new SortExpression(ClientFields.Description | SortOperator.Ascending); }

This code is unnecessary, except perhaps the sorter. You can set the sorter in the PageLoad as well, when there's no postback. This is all done by the asp.net framework.

        protected void Page_PreRender(object sender, EventArgs e)
        {
            PredicateExpression expr = new PredicateExpression();

            string clientName = (string)ViewState["clientName"];
            if (clientName != null)
            {
                string pattern = string.Concat("%", clientName.ToUpper(), "%");
                FieldLikePredicate filter = (FieldLikePredicate)(ClientFields.Description % pattern);
                filter.CaseSensitiveCollation = true;
                expr.Add(filter);
            }

            ormDataSource.FilterToUse = new RelationPredicateBucket(expr);

            grdClients.DataBind();

        }

Don't call databind, that's done by the asp.net framework. Also, you should set the filter in the page load.

Please, stick with one issue at a time. Does it or doesn't it work? If it works, OK, then we can move on to the other issues.

The issue is that I need the grid to work properly, with all features I mentioned and hitting the database just the right amount of times. The sample above has the correct functionality (so you can have an idea of what I'm trying to accomplish), but there are unnecessary database fetches, which is not acceptable. The idea is to retain the functionality while keeping only the necessary DB hits. I have already killed 3 days on this and would appreciate any help I can get.

This code is unnecessary, except perhaps the sorter. You can set the sorter in the PageLoad as well, when there's no postback. This is all done by the asp.net framework.

I agree that DataContainerType and EntityFactoryTypeName could be set at design-time in the .aspx, however no can do for AdapterToUse. The DataAdapter property actually refers to a singleton that instantiates a data adapter based on the connection string (application must work with SQL Server and Oracle). In other words, there is no type name I can specify at design-time.

Don't call databind, that's done by the asp.net framework.

I thought the same, except the grid did not update properly on inserts and deletes without that DataBind call.

Also, you should set the filter in the page load.

Can't do that, as the filter should be set only when the user clicks the Find button. cmdFind_Click runs after Page_Load.

Roman wrote:

Please, stick with one issue at a time. Does it or doesn't it work? If it works, OK, then we can move on to the other issues.

The issue is that I need the grid to work properly, with all features I mentioned and hitting the database just the right amount of times. The sample above has the correct functionality (so you can have an idea of what I'm trying to accomplish), but there are unnecessary database fetches, which is not acceptable. The idea is to retain the functionality while keeping only the necessary DB hits. I have already killed 3 days on this and would appreciate any help I can get.

Ok, but first things first: you shouldn't add that code in that method as the asp.net form will bind the controls ALSO. You don't have to do anything, other than setting the filter and sorter in the page load and then also only when the page isn't a postback (unless the filter changes of course).

This code is unnecessary, except perhaps the sorter. You can set the sorter in the PageLoad as well, when there's no postback. This is all done by the asp.net framework.

I agree that DataContainerType and EntityFactoryTypeName could be set at design-time in the .aspx, however no can do for AdapterToUse. The DataAdapter property actually refers to a singleton that instantiates a data adapter based on the connection string (application must work with SQL Server and Oracle). In other words, there is no type name I can specify at design-time.

I then think in that case you should use livepersistence set to false and add eventhandlers to the events for select, dbcount and persisting changes. LivePersistence = false is created especially for this situation.

The thing is: these controls work very simple: the grid and the datasourcecontrol get instantiated by the form and as the datasource control is the default datasource for the grid, the grid gets this datasourcecontrol assigned to it, automatically. Then the grid will ask the datasourcecontrol for data. The datasourcecontrol will either give the grid the data it gave to the grid last time (if nothing changed) or will fetch the data if something changed (filter, sorter, etc. ).

that's it. that's the mechanism. So all you should do is make sure that the datasourcecontrol is setup ok and asp.net does the rest. In YOUR case, with the 2 databases, I'd opt for Livepersistence = false, so you have control over which adapter to use when the data has to be fetched. It's a couple of lines of code, nothing to worry about. If you'll set things in code yourself at various stages of the page-lifecycle, the process asp.net will do for you will get disturbed probably.

Please take a look at this thread for some livepersistence background info/ideas: http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=7029

Success! LivePersistence = False seems to have done the trick. The code got a tiny bit more complex and is still somewhat funky (though nowhere near the levels of my other potential solut... err, workarounds), but hey, it works! See my comments after the code.

<asp:TextBox ID="txtClient" runat="server"></asp:TextBox>
<asp:Button ID="cmdFind" runat="server" Text="Find" OnClick="cmdFind_Click" />

<cc1:LLBLGenProDataSource2 ID="ormDataSource" runat="server" EnablePaging="True" EnableViewState="True" LivePersistence="false" OnInit="ormDataSource_Init" OnPerformGetDbCount="ormDataSource_PerformGetDbCount" OnPerformSelect="ormDataSource_PerformSelect" OnPerformWork="ormDataSource_PerformWork">
</cc1:LLBLGenProDataSource2>

<igtbl:UltraWebGrid ID="grdClients" runat="server" EnableViewState="True" DataSourceID="ormDataSource" OnDeleteRow="grdClients_DeleteRow" OnUpdateRow="grdClients_UpdateRow" >

    <Bands>
        <igtbl:UltraGridBand AllowUpdate="yes" AllowDelete="Yes" DataKeyField="ID" AllowAdd="yes" AddButtonCaption="Client">
            <Columns>
                <igtbl:UltraGridColumn BaseColumnName="Description" Key="Description">
                    <Header Caption="Client"></Header>
                </igtbl:UltraGridColumn>
                <igtbl:UltraGridColumn BaseColumnName="Identifier" Key="Identifier">
                    <Header Caption="Identifier"></Header>
                </igtbl:UltraGridColumn>
                <igtbl:UltraGridColumn BaseColumnName="ID" Key="ID" Hidden="true"></igtbl:UltraGridColumn>
            </Columns>
        </igtbl:UltraGridBand>
    </Bands>

    <DisplayLayout AutoGenerateColumns="false" >
        <Pager AllowPaging="true" PageSize="3" />
        <AddNewBox Hidden="false" />
    </DisplayLayout>

</igtbl:UltraWebGrid>

        protected void ormDataSource_Init(object sender, EventArgs e)
        {
            ormDataSource.AdapterToUse = DataAdapter;
        }       

        protected void ormDataSource_PerformGetDbCount(object sender, PerformGetDbCountEventArgs2 e)
        {
            if (ormDataSource.Refetch)
                e.DbCount = DataAdapter.GetDbCount(e.ContainedCollection, e.Filter, e.GroupBy);
        }

        protected void ormDataSource_PerformSelect(object sender, PerformSelectEventArgs2 e)
        {
            if (ormDataSource.Refetch)
                DataAdapter.FetchEntityCollection(e.ContainedCollection, e.Filter, e.MaxNumberOfItemsToReturn, e.Sorter, e.PageNumber, e.PageSize);
        }

        protected void ormDataSource_PerformWork(object sender, PerformWorkEventArgs2 e)
        {
            e.Uow.Commit(DataAdapter, true);
        }

        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                ormDataSource.DataContainerType = DataSourceDataContainerType.EntityCollection;
                ormDataSource.EntityFactoryTypeName = typeof(ClientEntityFactory).AssemblyQualifiedName;
                ormDataSource.SorterToUse = new SortExpression(ClientFields.Description | SortOperator.Ascending);
            }
        }

        protected void Page_PreRender(object sender, EventArgs e)
        {
            PredicateExpression expr = new PredicateExpression();

            string clientName = (string)ViewState["clientName"];
            if (clientName != null)
            {
                string pattern = string.Concat("%", clientName.ToUpper(), "%");
                FieldLikePredicate filter = (FieldLikePredicate)(ClientFields.Description % pattern);
                filter.CaseSensitiveCollation = true;
                expr.Add(filter);  
            }

            ormDataSource.FilterToUse = new RelationPredicateBucket(expr);
            grdClients.DataBind();
        }

        protected void grdClients_DeleteRow(object sender, RowEventArgs e)
        {
            // Stub - causes grid to postback on row delete
        }

        protected void grdClients_UpdateRow(object sender, RowEventArgs e)
        {
            // Stub - causes grid to postback on row update
        }

        protected void cmdFind_Click(object sender, EventArgs e)
        {
            grdClients.DisplayLayout.Pager.CurrentPageIndex = 1;
            ViewState["clientName"] = (txtClient.Text.Length > 0) ? txtClient.Text : null;
        }

Comments:

1. I had to enable viewstate on both the grid and the data source. As far as I can tell from observations and grid docs that doesn't actually save two copies of data. The Infragistics grid does not save the rows in viewstate if DataSourceID is declared. Why? Who knows...

2. I still had to set AdapterToUse manually in the initialization code, otherwise the data source does not perform deletes, inserts, and updates. I confirmed this via Reflector.

3. Paging through the grid caused it to fetch page data twice, once before Page_Load and once during the normal flow, in Page_PreRender. I noticed that in the first, unwanted, case the Refetch property was false, hence my check for it in PerformGetDbCount and PerformSelect. That even makes some sort of logical sense - if Refetch is false, database should not be hit.

4. I still had to call DataBind on the grid in Page_PreRender as the grid would not refresh the page after deletes and inserts.

Thank you for your help, Frans - I was going crazy over this problem. Is there anything in my code that could potentially cause future problems?