I've been learning LLBLGen Pro by reworking existing code (mine and others) and have had great success so far just with the demo. One example which I'm stuck on is the Atlas SimpleList example here:

http://atlas.asp.net/docs/Walkthroughs/DevScenarios/enrich.aspx

The Select statement they use looks like:

SELECT Id, Name, Priority, IsComplete, DateCreated
FROM List
WHERE (@IsComplete = -1)
OR (@IsComplete IN (0, 1)) AND (IsComplete = @IsComplete)
OR (@IsComplete = -2) AND (Name LIKE '%' + @SearchText + '%')"

My question: how is "@IsComplete = -2" coded in LLBLGen Pro? I found examples of how to code the rest of the filter in the documentation but this part of the filter I don't understand. Poking around with Google, -2 I think indicates an error that occured in the select statement, but I'm not sure.

Thanks in advance,

JC

joncl wrote:

http://atlas.asp.net/docs/Walkthroughs/DevScenarios/enrich.aspx.

The Select statement they use looks like:

SELECT Id, Name, Priority, IsComplete, DateCreated
FROM List
WHERE (@IsComplete = -1)
OR (@IsComplete IN (0, 1)) AND (IsComplete = @IsComplete)
OR (@IsComplete = -2) AND (Name LIKE '%' + @SearchText + '%')"

Your link doesn't work, fyi.

The example you posted needs some context, because at a glance it doesn't make a lot of sense (IMHO). It looks like it is using parameters to cover multiple conditional situations.

This is a pain (and a bad practice) for a number of reasons that I won't go into. The example you posted seems to be attaching a hard-coded meaning to different values that can be passed in via the "@IsComplete" parameter. "-1" seems to mean "fetch all records", "0" or "1" seem to mean "fetch records where iscomplete matches the parameter value" and "-2" seems to mean "ignore the IsComplete column, fetch records that match on name only".

The good news is that with LLBL, you don't have to attach meaning to hard coded values in your stored procs anymore--you can construct your query dynamically via code. So you might have three different methods/overloads for constructing the above query--one that pulls all records, one that matches on IsComplete, and one that matches on name.

That might not be as complete an answer as you were looking for, but part of the benefit of using a dynamic query engine is being able to conditionally construct queries in .Net code (where it's easy and maintainable) instead of in a stored procedure (where it's much more difficult).

HTH,

Phil

Ok, link is fixed, thanks.

To give it some context, the database of the example has only a single table named List:

List: Id (PK, int, not null) Name (nvarchar(max), not null) IsComplete (bit, not null) Priority (int, not null) DataCreated (datetime, not null)

Thanks for your comments. You've given me a different perspective on my original question. After considering what you said, it turns out your answer is 100% complete! My thinking that the "-1" represented null and "-2" represented something else was way off. It's actually exactly as you said: they're using 0, 1, -1, and -2 to represent different buttons as can be seen in the aspx code (check the CommandArgument values):

            <div class="tabs">
                <span class="controlLabel">Show:</span>
                <asp:LinkButton ID="ActiveButton" Text="Active" CommandName="show" CommandArgument="0" OnCommand="Select_Command" runat="server" />
                <asp:LinkButton ID="CompletedButton" Text="Completed" CommandName="show" CommandArgument="1" OnCommand="Select_Command" runat="server" />
                <asp:LinkButton ID="AllButton" Text="All" CommandName="show" CommandArgument="-1" OnCommand="Select_Command" runat="server" />
                <span class="controlLabel leftSpace">Search:</span>
                <span id="SearchTab" runat="server">
                    <asp:TextBox ID="SearchText" CssClass="filterdropdown" Width="150px" runat="server" />
                    <asp:Button ID="SearchBtn" CssClass="topButtons" Text=" >> "
                        CommandName="search" CommandArgument="-2" OnCommand="Select_Command" runat="server" />
                </span>
            </div>

And in the code behind to handle the actions:

        protected void Select_Command( object sender, CommandEventArgs e )
        {
            ListDataSource.SelectParameters[ "IsComplete" ].DefaultValue = e.CommandArgument.ToString();
            if ( e.CommandName == "show" ) SearchText.Text = "";
            ViewState[ "CurrentTab" ] = e.CommandArgument;
        }

Next challenge for me is to figure out how to make this happen using LLBLGen Pro.

Thanks again,

JC

Ok, I've now got this SimpleList Atlas example working using the LLBLGen Pro v2.0 LLBLGenProDataSource2 control with LivePersistence=False. It took me a bit to figure out what and when things were happening to be able to duplicate the original example (not to mention ramp up on LLBLGen which I'm new at btw). I've got the whole solution zipped, but don't know how to post it If anyone wants it let me know.

JC

Which db did you use, northwind? We could put it up in the 3rd party downloads as an example if you like.

The example uses it's own database with only a single table (List):

List:
Id (PK, int, not null)
Name (nvarchar(max), not null)
IsComplete (bit, not null)
Priority (int, not null)
DataCreated (datetime, not null)

One would need to attach the mdf and ldf files to Sql Server (I'm using express) and also install Web Application Projects to get the example running. Probably some setup instructions would help. Should I send the ~600Kb zip as-is or should I prepare it in some way for the 3rd party downloads section? Also, where do I send it?

You can send it to support AT llblgen.com. please add a small readme how the users should install it / use it. If you want we can add a link to your site.

It's available

Thanks Frans!

In case anyone is curious what the example is but would like to save a trip downloading it just to find out, here's some blurb out of the readme and some code:

This is the SimpleList Atlas example that I've reworked to use LLBLGen Pro v2.0 for the DAL/business objects layers and the new LLBLGenProDataSource2 control as the datasource. The original example is here:

http://atlas.asp.net/docs/Walkthroughs/DevScenarios/enrich.aspx

This example is basically a GridView on a page that gets populated from a single table (named List) in the database. What makes the example great for the LLBLGenProDataSource2 control is that it's got all the runtime paging, sorting, selecting, editing, and item adding, but it's still relatively simple, which makes the events of the LLBLGenProDataSource2 control a little easier to setup and diagnose.

• Replaced the original data access code with new LLBLGen Pro v2.0 data access and business object layers.
• Replaced the original SqlDataSource control with the new LLBLGenProDataSource2 control.
• Achieved full paging, sorting, and runtime selection through the LLBLGenProDataSource2 control.
• Achieved full insert, update, and delete functionality also through the LLBLGenProDataSource2 control.
• Added the Atlas functionality intended in the original example.
• Ran the LLBLGenProDataSource2 control with the option LivePersistence=False to allow full control over database interaction and allow for future encapsulation of business logic away from the UI layer.

These are the three event methods of the LLBLGenProDataSource2 control that intercept the GridView's requests for items:

        protected void OnPerformGetDbCount( object sender, PerformGetDbCountEventArgs2 e )
        {
            // Determine the total number of items in the resultset which is needed for paging.

            // Use the filter from the ListState object.
            e.Filter.PredicateExpression.Add( listState.PredicateExpression );

            using ( DataAccessAdapter adapter = new DataAccessAdapter() )
                e.DbCount = adapter.GetDbCount( e.ContainedCollection, e.Filter );
        }


        protected void OnPerformSelect( object sender, PerformSelectEventArgs2 e )
        {
            // Get the RelationPredicateBucket from PerformSelectEventArgs2.  Note that this 
            // could come from a new RelationPredicateBucket created in OnLoad (case: not 
            // posting back) OR from ViewState(Session) (case: posting back).
            IRelationPredicateBucket filterBucket = e.Filter;
            filterBucket.PredicateExpression.Add( listState.PredicateExpression );

            // If there's a SortClause defined in the ListState object, use it.  Otherwise,
            // use the one from PerformSelectEventArgs2.
            ISortExpression sorter;
            if ( listState.SortClause != null ) sorter = new SortExpression( listState.SortClause );
            else sorter = e.Sorter;

            // Fetch the collection.
            using ( DataAccessAdapter adapter = new DataAccessAdapter() )
            {
                adapter.FetchEntityCollection( e.ContainedCollection,
                                               filterBucket,
                                               0,
                                               sorter,
                                               listState.PageIndex + 1,
                                               simpleListGridView.PageSize );
            }
        }


        protected void OnPerformWork( object sender, PerformWorkEventArgs2 e )
        {
            // An update to the list has occurred so grab the UnitOfWork object from 
            // PerfromWorkEventArgs2 and commit it.  Earlier, I saved the UnitOfWork to 
            // session, then retrieved it and did the commit during the next PerformGetDbCount 
            // event in an attempt to save a round trip to the database.  But Frans informed me 
            // of a simpler way: "simply execute the work and be done with it. Creating an 
            // adapter is fast, as connection pooling will get you a connection from the pool 
            // anyway, and it won't save roundtrips."

            using ( DataAccessAdapter adapter = new DataAccessAdapter() )
                e.Uow.Commit( adapter, true );
        }