LLBLGen Pro v2.0 with ASP.NET 2.0

Recently, Scott Guthrie blogged about using DLinq with ASP.NET. I read the interesting article and thought... that must be possible today, with code using normal .NET 2.0! So, I started LLBLGen Pro v2.0 (V2 is currently in beta) and yes, I was able to do everything Scott showed in his article, with very little effort. The steps I took are described below.

I've to add, LLBLGen Pro v2.0 is currently in beta, so similar to DLinq, it's not yet available in final form, though we hope to release v2.0 of LLBLGen Pro later this month. The beta is open for our customers. The current version of LLBLGen Pro is v1.0.2005.1.

Ok, so let's get to work!

Step 1, create an LLBLGen Pro project

Scott starts with creating an ASP.NET project, I start with creating the LLBLGen Pro project first. It doesn't really matter much, but as we don't have to come back to the LLBLGen Pro designer after this step, it's easier to do it as the first thing in this little project. The database used is Northwind on SqlServer, and in this article I'll use SqlServer 2000.

After we've created the project with a few clicks in the LLBLGen Pro designer, it looks something like this:


Step 1, Northwind project. Click to see the full sized version.

As we don't need anything fancy, like inheritance, we can leave it at this and simply move on to step 2.

Step 2, generate code

My step numbering is a bit different than the one Scott used, I'll refer to the proper comparison step later on when it's more appropriate. Generating code is also a simple procedure. Pressing F7, selecting some parameters to meet our goals for the code and we're set. LLBLGen Pro v2.0 has a redesigned code generation setup mechanism, though as I'd like to keep it under wraps for now till the release date, to keep a bit of an advantage over the competition. For this article, the details about the code generation process aren't that important anyway.

I've chosen for C# as my target language, .NET 2.0 as target platform and 'adapter' as the paradigm to use for my code. LLBLGen Pro supports two different persistence paradigms: SelfServicing (which has the persistence logic build into the entities, e.g. customer.Save()) and Adapter, which has the persistence logic placed in a separate class, the adapter, and which makes it possible to write code which targets multiple databases at the same time, and to work with the entities without seeing any persistence logic. This has the advantage to keep logic separated into different tiers for example (so GUI developers can't make shortcuts to save an entity, bypassing the BL logic).

After the code's been generated, which took about 4 seconds, it's time to start Visual Studio.NET 2005 and start some serious application development!

Step 3, setting up the VS.NET project

In the folder in where I've generated my code, I've created a folder called 'Site' and have created a virtual directory on the Windows XP IIS installation's default site called LLBLGenProTest which points to that Site folder. I load the two generated projects (Adapter uses two projects, one for database generic purposes, which can be shared among multiple database specific projects, and a database specific project) into VS.NET and create a new website using the vanilla 'Your Folder Is The Website'-project type as it shipped with VS.NET 2005, using the file system.

After the references have been setup correctly, and I've added a web.config file to the website project, it looks like this:


Step 3, VS.NET project. In the generated database specific project, an app.Config file is generated which contains our connection string. I copy over the appSettings tag with the connection string to the web.config file of my site and it's now ready for data-access. V2 will also support the new .NET 2.0 connectionstrings tag which is used by Scott in his article.

Ok, everything is setup, without a single line of typing, and we're now ready to create webforms which actually do something. On to step 4!

Step 4, build a page that uses our LLBLGen Pro layer

In Scott's article this is step 3. He has a page with a simple grid with 4 columns, and in the code-behind page he loads all customer entities from the database filtered on Country=="USA". Sounds easy enough. As we're working with entities, I think it's a good oppertunity to show off the LLBLGenProDataSource controls. In this step I've dragged an LLBLGenProDataSource2 control onto my form and opened its designer to configure it for this particular purpose. The '2' isn't a typo, the LLBLGenProDataSource control is used by SelfServicing, the LLBLGenProDataSource2 is used by Adapter. The '2' suffix is used for adapter since the beginning, so to keep everything consistent with already familiar constructs, the '2' is used for suffix here as well.

LLBLGen Pro's datasource controls are very powerful controls. When v2 is released I'll write more articles about these guys, for now I'll continue with the designer part of the LLBLGenProDataSource2 control as it's located on my form:


Step 4, Configuring the datasource control. Click to see the full sized version.

Although I could have setup some combo-boxes to produce a filter at runtime through the datasource control completely declarative, I'll follow Scott's approach with a filter and sorter produced in the code-behind. In this situation, I just have to set the filter and the sorter object and I'm good to go. My code for this page thus looks like:

using System;
using Northwind.EntityClasses;
using Northwind.HelperClasses;
using SD.LLBLGen.Pro.ORMSupportClasses;

public partial class _Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if(!Page.IsPostBack)
        {
            // set initial filter and sorter for datasource control.
            _customerDS.FilterToUse =
                    new RelationPredicateBucket(CustomerFields.Country == "USA");
            _customerDS.SorterToUse =
                    new SortExpression(CustomerFields.CompanyName | SortOperator.Ascending);
        }
    }
}

_customersDS is my LLBLGenProDataSource2 control, which is the datasource for my grid. Running this page will give the following results:


Step 4, results.

Not bad for 2 lines of code and some mousing in the designer. Ok, this is of course rather boring straight forward stuff, so let's move on to something more serious, projections and subsets!

Step 5, Data Shaping and Projections

This is Scott's step 4. In the world of today, we don't have nice features like the things which come with Linq, as there are the anonymous types. So if we want to create a projection of a resultset onto something else, we either have to define the container class which will contain the projected data up-front or we've to store it in a generic container, like a DataTable. As we're going to databind the resultset directly to a grid, a DataTable will do just fine here. Though, don't feel sad, I'll show you both methods here: one using the DataTable and one using the new LLBLGen Pro v2 projection technology which allows you to project any resultset onto any other construct using generic code.

DataTable approach
But first, the DataTable using approach. The query Scott uses is a tricky one, which might not be obvious at first. The query has two scalar queries inside the select list: one for the number of orders and one for the last order date. Because of these scalar queries, it's obvious this data can't be stored in an entity, as an entity is mapped onto tables or views, not dynamicly created resultsets. So in LLBLGen Pro you'll use a dynamic list for this. This is a query, build from strongly typed objects which are the building blocks of the meta-data used by the O/R mapper core, and which resultset is stored inside a DataTable. This gives the following code. In the Page_Load handler, I've placed the following:

// create a dynamic list with in-list scalar subqueries
ResultsetFields fields = new ResultsetFields(6);
// define the fields in the select list, one for each slot.
fields.DefineField(CustomerFields.CustomerId, 0);
fields.DefineField(CustomerFields.CompanyName, 1);
fields.DefineField(CustomerFields.City, 2);
fields.DefineField(CustomerFields.Region, 3);
fields.DefineField(new EntityField2("NumberOfOrders",
    new ScalarQueryExpression(OrderFields.OrderId.SetAggregateFunction( AggregateFunction.Count),
    (CustomerFields.CustomerId == OrderFields.CustomerId))), 4);
fields.DefineField(new EntityField2("LastOrderDate",
    new ScalarQueryExpression(OrderFields.OrderDate.SetAggregateFunction( AggregateFunction.Max),
    (CustomerFields.CustomerId == OrderFields.CustomerId))), 5);

DataTable results = new DataTable();
using(DataAccessAdapter adapter = new DataAccessAdapter())
{
    // fetch it, using a filter and a sort expression
    adapter.FetchTypedList(fields, results,
            new RelationPredicateBucket(CustomerFields.Country == "USA"), 0,
            new SortExpression(CustomerFields.CompanyName | SortOperator.Ascending), true);
}

// bind it to the grid.
GridView1.DataSource = results;
GridView1.DataBind();

By now you might wonder how I'm able to use compile-time checked filter constructs and sortexpression constructs in vanilla .NET 2.0. LLBLGen Pro uses operator overloading for this, to have a compile-time checked way to formulate queries without the necessity of formulating a lot of code. A string-based query language is perhaps for some an alternative but it won't be compile time checked, so if an entity's name or a fieldname changes, the compiler won't notice it and your code will break at runtime. With this mechanism it won't as these name changes will be spotted by the compiler.

LLBLGen Pro v2's projection approach
I promissed I'd also show a different approach, namely with projections. For this we first write our simple CustomerData class which will contain the 6 properties we've to store for each row. It's as simple as this:

public class CustomerData
{
    private string _customerId, _companyName, _city, _region;
    private int _numberOfOrders;
    private DateTime _lastOrderDate;

    public string CustomerId
    {
        get { return _customerId; }
        set { _customerId = value; }
    }

    public string CompanyName
    {
        get { return _companyName; }
        set { _companyName = value; }
    }
    
    public string City
    {
        get { return _city; }
        set { _city = value; }
    }
    
    public string Region
    {
        get { return _region; }
        set { _region = value; }
    }

    public int NumberOfOrders
    {
        get { return _numberOfOrders; }
        set { _numberOfOrders = value; }
    }

    public DateTime LastOrderDate
    {
        get { return _lastOrderDate; }
        set { _lastOrderDate = value; }
    }
}

For our query we simply use the same setup as we've used with the DataTable fetch, only now we'll specify a projector and a set of projector definitions. We furthermore tell LLBLGen Pro to fetch the data as a projection, which means as much as that LLBLGen Pro will project the IDataReader directly onto the constructs passed in using the projector objects. As you can see below, this is generic code and it's a standard approach which can be used in other contexts as well, for example by projecting in-memory entity collection data onto different constructs. The projectors are all defined through interfaces so you can create your own projection engines as well. One nice thing is that users will also be able to project stored procedure resultsets to whatever construct they might want to use, including entity classes. So fetching data with a stored procedure into a class, for example an entity, will be easy and straightforward as well.

Ok back to the topic at hand. The code to fetch and bind the resultset using custom classes looks as follows:

// create a dynamic list with in-list scalar subqueries
ResultsetFields fields = new ResultsetFields(6);
// define the fields in the select list, one for each slot.
fields.DefineField(CustomerFields.CustomerId, 0);
fields.DefineField(CustomerFields.CompanyName, 1);
fields.DefineField(CustomerFields.City, 2);
fields.DefineField(CustomerFields.Region, 3);
fields.DefineField(new EntityField2("NumberOfOrders",
    new ScalarQueryExpression(OrderFields.OrderId.SetAggregateFunction( AggregateFunction.Count),
    (CustomerFields.CustomerId == OrderFields.CustomerId))), 4);
fields.DefineField(new EntityField2("LastOrderDate",
    new ScalarQueryExpression(OrderFields.OrderDate.SetAggregateFunction( AggregateFunction.Max),
    (CustomerFields.CustomerId == OrderFields.CustomerId))), 5);

// the container the results will be stored in.
List<CustomerData> results = new List<CustomerData>();

// Define the projection.
DataProjectorToCustomClass<CustomerData> projector =
        new DataProjectorToCustomClass<CustomerData>(results);
List<IDataValueProjector> valueProjectors = new List<IDataValueProjector>();
valueProjectors.Add(new DataValueProjector("CustomerId", 0, typeof(string)));
valueProjectors.Add(new DataValueProjector("CompanyName", 1, typeof(string)));
valueProjectors.Add(new DataValueProjector("City", 2, typeof(string)));
valueProjectors.Add(new DataValueProjector("Region", 3, typeof(string)));
valueProjectors.Add(new DataValueProjector("NumberOfOrders", 4, typeof(int)));
valueProjectors.Add(new DataValueProjector("LastOrderDate", 5, typeof(DateTime)));

using(DataAccessAdapter adapter = new DataAccessAdapter())
{
    // let LLBLGen Pro fetch the data and directly project it into the List of custom classes
    // by using the projections we've defined above.
    adapter.FetchProjection(valueProjectors, projector, fields,
            new RelationPredicateBucket(CustomerFields.Country == "USA"), 0,
            new SortExpression(CustomerFields.CompanyName | SortOperator.Ascending),
            true);
}

// bind it to the grid.
GridView1.DataSource = results;
GridView1.DataBind();

It's a bit more code as you've to define the projections, but it at the same time has the nice aspect of having the data in a typed class (CustomerData) instead of a DataTable row.

The code in action
These two approaches give the same output, as you would expect:


Step 5, Results. Click to see the full sized version.

In the next step we'll take a different approach as we'll go back to the entities and fetch related entities in an efficient manner. I'll combine the second part of Scott's step 5 and his step 6 into one step, as enabling paging is in the case of LLBLGen Pro a matter of clicking a checkbox and changing 'false' into 'true'.

Step 6, fetching entities with related entities and paging

We'll leave the dynamic resultsets and projections for now and will go back to entities and the LLBLGenProDataSource2 control from step 4. Now, one particular aspect of Scott's queries is that he uses a scalar query in the select list. As I've described earlier, this is a bit tricky with entities as these are mapped onto tables or views, not on a dynamic resultset. But, of course we won't let that spoil our party . I'll show you how to easily adjust the entity classes so the scalar query is injected into the resultset and available at runtime for the binding logic, as a normal property of the entity class (in this case CustomerEntity).

In LLBLGen Pro's design, entities in a bulk entity fetch action are created through factories. So all we need here is a special factory which will create the required fields list at runtime for us so the scalar query for the Number of orders is present in the query for the entity data. We need this approach as this last step not only fetches the customer data but also for each fetched customer it has to fetch the last 5 order entities, in one go. As this has to be done efficiently, we'll use the LLBLGen Pro's prefetch path functionality, which defines paths of related entities to be fetched in the same fetch action, using 1 query per path node. Per path node you can specify if you want a sort action, limiter on the resultset and additional filters, and with this particular example we'll sort on the order date (descending) and will have at most 5 of these orders in our resultset. Let's see how we solve this in LLBLGen Pro v2, without the availability of anonymous types.

Extending the CustomerEntityFactory
To be able to inject the scalar query into the query, we simply extend the factory and append our scalar query expression field to the list of fields of the entity. This list of fields is used to build the query for the entity, so the scalar query will end up in the select list as if it's a normal mapped entity field.

using System;
using System.Collections.Generic;
using System.Text;
using Northwind.FactoryClasses;
using SD.LLBLGen.Pro.ORMSupportClasses;
using Northwind.HelperClasses;

namespace Northwind.FactoryClasses
{
    public class ExtendedCustomerEntityFactory : CustomerEntityFactory
    {
        /// <summary>
        /// Create the fields for the customer entity and adjust the fields collection
        /// with the entity field object which contains the scalar expression
        /// </summary>
        /// <returns></returns>
        public override IEntityFields2 CreateFields()
        {
            IEntityFields2 toReturn = base.CreateFields();
            toReturn.Expand(1);
            IEntityField2 scalarField = new EntityField2("NumberOfOrders",
                    new ScalarQueryExpression(OrderFields.OrderId.SetAggregateFunction(
                        AggregateFunction.Count),
                    (CustomerFields.CustomerId == OrderFields.CustomerId)));
            toReturn.DefineField(scalarField, toReturn.Count-1);
            return toReturn;
        }
    }
}

This of course doesn't make it appear as a property in the CustomerEntity class. As .NET 2.0 code is generated by LLBLGen Pro into partial classes, we can simply add a new partial class file for the CustomerEntity class to the project and add our little property there:

using System;
using System.Collections.Generic;
using System.Text;

namespace Northwind.EntityClasses
{
    public partial class CustomerEntity
    {
        public Nullable<int> NumberOfOrders
        {
            // read the last field in the fieldslist, as we've added our scalar
            // expression field to the end of the fields list.
            get
            {
                object value = this.Fields[this.Fields.Count - 1].CurrentValue;
                if(value!=null)
                {
                    return Convert.ToInt32(value);
                }
                else
                {
                    return null;
                }
            }
        }
    }
}

Now that the property's there, we can focus on the page itself. As we're fetching entities, we'll build upon our form from step 4:


Step 6, form design.

As you can see, the form is already setup for paging. This is done by simply enabling paging on the GridView and by enabling paging on the LLBLGenProDataSource2 control. If you leave paging off on the DataSource control, it will fetch all data for you and you'll have client-side paging inside the grid. As paging on the server is much more efficient in most cases, we'll use server side paging here. The full HTML of this page looks like:

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Sample3.aspx.cs" Inherits="Sample3" %>
<%@ Register Assembly="SD.LLBLGen.Pro.ORMSupportClasses" Namespace="SD.LLBLGen.Pro.ORMSupportClasses" TagPrefix="cc1" %>
<html>
<body>
    <form id="form1" runat="server">
        <h1>
            Northwind Customers</h1>
        <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="CustomerId"
            DataSourceID="_customerDS" AllowPaging="True" PageSize="3">
            <Columns>
                <asp:BoundField DataField="CustomerId" HeaderText="Customer ID" ReadOnly="True" SortExpression="CustomerId" />
                <asp:BoundField DataField="CompanyName" HeaderText="Name" SortExpression="CompanyName" />
                <asp:BoundField DataField="City" HeaderText="City" SortExpression="City" />
                <asp:BoundField DataField="Region" HeaderText="State" SortExpression="Region" />
                <asp:BoundField DataField="NumberOfOrders" HeaderText="Number of orders" SortExpression="NumberOfOrders" />
                <asp:TemplateField HeaderText="Recent Orders">
                    <ItemTemplate>
                        <ul>
                            <asp:Repeater runat="server" DataSource='<%# Eval("OrderCollection") %>'>
                                <ItemTemplate>
                                    <li><%# Eval("OrderId") %> (<%# Eval("OrderDate", "{0:dd MMM yyyy}")%>) </li>
                                </ItemTemplate>
                            </asp:Repeater>
                        </ul>
                    </ItemTemplate>
                </asp:TemplateField>
            </Columns>
        </asp:GridView>
        <cc1:LLBLGenProDataSource2 ID="_customerDS" runat="server"
            AdapterTypeName="Northwind.DatabaseSpecific.DataAccessAdapter, NorthwindDBSpecific"
            CacheLocation="Session" DataContainerType="EntityCollection"
            EntityFactoryTypeName="Northwind.FactoryClasses.ExtendedCustomerEntityFactory, Northwind" EnablePaging="True">
        </cc1:LLBLGenProDataSource2>
    </form>
</body>
</html>

What's also to be noticed here is that the factory specification has changed to the new ExtendedCustomerEntityFactory class. Ok, the last step is the code behind page where we'll setup the prefetch paths, the filter and the sort clauses for the fetch action:

public partial class Sample3 : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if(!Page.IsPostBack)
        {
            // set initial filter and sorter for datasource control.
            _customerDS.FilterToUse =
                    new RelationPredicateBucket(CustomerFields.Country == "USA");
            _customerDS.SorterToUse =
                    new SortExpression(CustomerFields.CompanyName | SortOperator.Ascending);
            // specify the prefetch path for the fetch, so it will fetch in 2
            // queries the customers and the orders for these customers, instead of 1
            // query per customer. We'll also specify '5' for the limit of those orders,
            // and they should be sorted descending.
            PrefetchPath2 path = new PrefetchPath2((int)EntityType.CustomerEntity);
            path.Add(CustomerEntity.PrefetchPathOrderCollection, 5, null, null,
                    new SortExpression(OrderFields.OrderDate | SortOperator.Descending));
            _customerDS.PrefetchPathToUse = path;
        }
    }
}

We recognize the filter and sorter setup as what's done in step 4, though what's new is the setup for the prefetch path for the Orders entity. We define a sort expression and a limit of 5 on the prefetch path node for the orders. This code then gives the result:


Step 6, Results. Click to see the full sized version.

And that's it!

Summary

Using LLBLGen Pro v2, we were able to do complex data-access with a minimum of effort. The article showed how LLBLGen Pro v2 will be a powerful companion for every ASP.NET 2.0 developer. V2 isn't available yet, it's in beta (which is available for our customers) and will be released later this June.

I've also put online a small video which shows the LLBLGenProDataSource2 control in action in a scenario where a set of customers is filtered based on a selected value in a dropdownbox. The video shows how to create such a webform without writing any code: no mapping files, no code behind logic, no HTML, nothing: Look Ma! No code! .

17 Comments

  • Ah funny you created your own ObjectDatasource control. Even doing some standard hierarchical editing scenario (order, orderlines) is near impossible with the default ObjectDataSource control provided by MS. They really should have given it more TLC, as the current only works reasonable in their "RAD" demo scenario's. Well..just my 2 cents. Succes met v2.0!

  • Very cool, Frans. You apparently thought the same thing I thought when looking at Scott's post.

    I'm not using LLBLGen Pro v2 yet but most of the "playing" I have done with .NET 2's Design Time data controls have used my LLBLGen projects for their data, just to see if it would work. They worked surprisingly well.

    How much of your post is possible now with the current version of LLBLGen Pro?

  • Excellent post, thorough and clear. Thanks! You should post this over at www.dzone.com - a digg-like site specifically for developers.

  • I understand the power, but I don't think the 'easy' qualifier applies ;), that's a lot of hard to understand code!

  • Pete: it's actually quite easy. :) But every power comes with a user manual and of course the more power the more things you have to know to avoid shooting yourself in the foot ;)

  • Pete, I understand your concerns, and you're right there is certainly some ramp-up time, but llbl has got to be one of the most useful products I've ever purchased. Additionally, the forums (www.llblgen.com/tinyforum) is crawling with very talented, very helpful developers to help get you started.

  • So, how about releasing a version that I can use at home to learn from without spending hundreds of dollars?



    I like the tool you have, but all the pricing seems geared toward some guy working in a Ivory tower with a big budget.



    Help the guys like me out who would love to use the software, but it's way too expensive to justify from working at home.

  • Steve: I don't think a few hundred bucks is too much for a tool which saves you 40% and maybe more of the time you've to spend on a given project.

    Furthermore, while I understand that some people don't have a lot of money, it's a tool geared towards developers. If you want to write software, you need a computer too and not some old brick, but a decent one to be able to run VS.NET 2005 for example. I'm sure Dell won't give you one for free either ;)

  • Steve, for what you get, LLBLGen is one of the cheapest around. It will repay easily if you do a project with it.
    If you are really on a tight budget consider the other free alternatives, some of good quality.

  • Graham: no it's in LLBLGen Pro v2.0, which will be released early next week :)

  • Thank you for making that data source control!!! I have to create template fields to make databinding work right now with ODS, not to mention the default designer is useless. This release should be pretty slick!

    Oh and... LMAO @ "ivory tower" comment!!!

    LLBLGen is very reasonably priced. Plus there's always the 30-day free trial if you want to learn it at home.

  • Hello there,

    Could you possibly repost the video link? It does not seem to be working.

    Thanks,

    Matthew

  • The video works fine here, could you check if you have a flash plugin in your browser? It's a flash video.

  • Hello,

    It works fine now, trust me it was timing out this afternoon :-)

    Just started evaluating the software today, looks great, although more videos of that quality would be a great starting point for the likes of me.

    Thanks again,

    Matthew

  • :)

    More videos will be posted very soon now :)

  • Does exist any project envolving LLBGen with another UI, like Infrgistics NetAdvantage ?

  • Hi Matthew,

    We'll be looking into the thread shortly. Please continue the discussion in the thread on our forum.

Comments have been disabled for this content.