Home > .NET Domain Model, Developing Using CodeFluent Entities > DataSet Support in CodeFluent Entities

DataSet Support in CodeFluent Entities


Let’s say we have an Address entity such as:

<Address>
<Id typeName=”int” />
<Street />
<City />
</Address>

And for a very particular screen we only need to list all cities contained in this table and we don’t need to retrieve all columns of our Address table. It sounds like a nice scenario to add a custom method doing just a select on that column, but how can I model that using CodeFluent Entities?

Well to model that we’re going to leverage 3 features of CodeFluent Entities:

First we’re going to add our raw method to our Address entity:

<cf:method name=”LoadAllCities” body=”raw” returnTypeName=”System.Data.DataSet”>
SELECT $Address::City$ FROM $Address$
</cf:method>

As you can see, instead of writing 100% raw T-SQL I’m using Target Name Transformation to retrieve persistence names, so that my custom method won’t be broken if we change our naming convention later on. Furthermore, we’re setting the returnTypeName of our method to the standard .NET type System.Data.DataSet so my method will be able to return pretty much all I want.

In the end my SQL Server Producer will generate the corresponding stored procedure, and the Business Object Model producer the corresponding C# method which calls the underlying stored procedure and fills in my dataset.

Finally in my .NET application all I have to do is:

DataSet ds = Address.LoadAllCities();
foreach (DataTable table in ds.Tables)
{
    foreach (DataRow row in table.Rows)
    {
        Console.WriteLine("City: " + row[0]);
    }
}

Now obviously using a DataSet to retrieve a simple column is a bit oversized, but it illustrates a way of modeling custom queries retrieving data from multiple tables and filling them up in a DataSet. By the way, another way to do it would be by using persistent Views but this will be for another day Smile

  1. Peter Stanford
    May 28, 2013 at 7:29 am

    I have a query (which I know how to write in TSQL) but I’m looking for the most effective and efficient means of adding it to my CodeFluent Entities project. I need to end up with a collection of string values that I’ll bind to an AutoComplete Dropdown List (a Telerik control which I’ve used before for a similar purpose).

    The string will be comprised of the following parts:

    [Product Description] + [Brand} + [Size] + [Quantity] + per [Unit] + [Product Code] and will look something like this:

    Peas – Edgell 2kg – 6 per Carton (EDPE2)

    The elements come from the following entities:

    [Product Description] – Product Entity
    [Brand] – Brand Entity (one Brand to many Product(s))
    [Size] – Product Entity
    [Quantity] – Product Entity
    [Unit] – UOM_Unit Entity (one UOM to many Product(s))
    [Product Code] – Product Entity

    In addition to the concatenated string I’ll retrieve the ID from the Product entity and bind that as the Value member of the dropdown list.

    This data is already persistent so my question is whether I should use a lightweight entity, a persistent view or a dataset? There is no need to update anything so a dataset seems like overkill. Could I also write a method using Target Name Transformations and place it inside my Product entity?

    Which of these options (or others you might suggest) would be the most efficient?

  1. June 23, 2011 at 7:42 am

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s