The ObjectMapper .NET Project

Official blog of the AdFactum ObjectMapper .NET

Archive for December, 2007

New Release – AdFactum ObjectMapper .NET 2.0.2307.0

Posted by Gerhard Stephan on 7th December 2007

This version covers one new query condition, with that you can use the Sql  "with clause" sub query replacement.  That feature gives you a powerful optimization feature, if you have many sub selects (SubSelect) within your sql query. In best case, these sub selects can be reduced to one, when using the "with clause" feature.

More information about using the withclause can be found here: http://blog.objectmapper.net/2007/12/07/using-the-sql-99-with-clause/

So, I wish you lot of fun with the new version.
Cheers

– Gerhard

1 Star2 Stars3 Stars4 Stars5 Stars (No Ratings Yet)
Loading...

Posted in Releases | No Comments »

How to use the SQL-99 with clause.

Posted by Gerhard Stephan on 7th December 2007

The SQL-99 with clause is used for substitute where conditions, if you have a sql with multiple sub selects. Puhh. It’s Hard to explain. But: Imagine you have a sql query like the following one:

 

select * from tp_anwender where anwender in

 (select anwender from tp_entries where Computer = ‘xyz’ /* more queries */ and Rechte = 1 union all

  select anwender from tp_entries where Computer = ‘xyz’ /* more queries */ and Rechte = 2 union all

  select anwender from tp_entries where Computer = ‘xyz’ /* more queries */ and Rechte = 3);

 

Sure. It’s not the best example. But you can see, that the where conditions in the sub selects are almost the same. Only the "Rechte" column differs. This can be very good optimized, when using the with clause.

 

with sub01 as (

      select * from tp_entries e where Computer = ‘xyz’ /* more queries */

)

select * from tp_anwender where anwender in

 (select anwender from sub01 where Rechte = 1 union all

  select anwender from sub01 where Rechte = 2 union all

  select anwender from sub01 where Rechte = 3);

 

To build such queries, the AdFactum ObjectMapper .NET does now support the "with clause" for Oracle and the Sql Server. Microsoft Access and SqlServerCE won’t support the with clause.

 

WithClause subClause = new WithClause("sub01", typeof(TpEntries),

    new AndCondition(typeof (TpEntries), "Computer", "xyz"));

 

SubSelect anwender01 = new SubSelect(typeof(TpEntries), "Anwender",

    new AndCondition(typeof(TpEntries), "Rechte", 1),

    new TableReplacement(typeof(TpEntries), "sub01"));

 

SubSelect anwender02 = new SubSelect(typeof(TpEntries), "Anwender",

    new AndCondition(typeof(TpEntries), "Rechte", 2),

    new TableReplacement(typeof(TpEntries), "sub01"));

 

ICondition selection = new ConditionList( subClause,

    new InCondition(typeof(TpAnwender), "Anwender"

    new UnionAll(activityId01, activityId02)));

 

using (AdFactum.Data.ObjectMapper mapper = OBM.CreateMapper(Connection))

{

    int result = mapper.Count(typeof(TpAnwender), selection);

    …

)

 

If you have any questions to this new functionality, don’t hesitate to ask.

That’s all for now folks.

 

Wish you a great weekend.

 

Cheers

– Gerhard

1 Star2 Stars3 Stars4 Stars5 Stars (No Ratings Yet)
Loading...

Posted in HowTo | 1 Comment »