The ObjectMapper .NET Project

Official blog of the AdFactum ObjectMapper .NET

Archive for the 'HowTo' Category

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 »

How to check if a property has been changed since last loading the object.

Posted by Gerhard Stephan on 20th September 2007

Sometimes it’s important for the business logic to know if the UI changed a special property since last loading the object. Maybe this is important for the workflow or other things. Using the AdFactum ObjectMapper .NET you can use a speical pattern for that.

Have a look at the following example. The entity Company has a state called CompanyState. We now want to know, whether that property has been changed by the UI since last loading the object. Therefore we need a second property that holds the orginal value. This property is filled the first time wehn the object is loaded by the AdFactum ObjectMapper .NET. A property with the Ignore attribute checks those two properties to indicate whether the property has been changed or not. It’s important to define the orginal value as a nullable type, because we only want to fill that property the first time when the setter is called.

    public enum CompanyState

    {

        Active,

        Retired,

        Deleted

    }

 

    public class Company

    {

        private CompanyState companyState;

        private CompanyState? orgCompanyState = null;

 

        /// <summary>

        /// Gets or sets the state of the company.

        /// </summary>

        /// <value>The state of the company.</value>

        public CompanyState CompanyState

        {

            get { return companyState;  }

            set {

                companyState = value;

                if (orgCompanyState == null)

                    orgCompanyState = value;

            }

        }

 

        /// <summary>

        /// Gets a value indicating whether the company state changed or not.

        /// </summary>

        /// <value><c>true</c> if company state changed; otherwise, <c>false</c>.</value>

        [Ignore]

        public bool CompanyStateChanged

        {

            get { return companyState != orgCompanyState;  }

        }

    }

 

In our business logic we can now check the CompanyStateChanged property and react on new values within the company state. E.g. send emails or do something else.

That’s all for now.
Cheers

– Gerhard

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

Posted in HowTo | No Comments »

How to use Generics with the AdFactum ObjectMapper .NET

Posted by Gerhard Stephan on 13th September 2007

It’s not a surprise, but worth to say: The AdFactum ObjectMapper .NET does fully supports the Generics of the dotNet Framework 2.0. Having a first look at the interface of the ObjectMapper class, nothing looks like, that Generics can be used.

For Example: public IList FlatSelect(Type type)

Most of all, this is because of the backward compatibility to the dotNet Framwork 1.0. But let us have a look at the following use case.

List<User> users = new List<User>(

new ListAdapter<User>(

   mapper.FlatSelect(typeof (User), userCondition)));

 

The AdFactum ObjectMapper .NET provides a helper class called ListAdapter to bridge the gap between the untyped IList interface and the Generics, used by the dotNet Framework 2.0. The base principles of the ListAdapter can be found here.

That’s all for now.
Cheers

Gerhard

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

Posted in HowTo | No Comments »

How to get the primary key of an aggregated entity when doing a flat load of the parent.

Posted by Gerhard Stephan on 16th August 2007

Today I will show you a way to retrieve the ID of an aggregated object, even when doing a flat load of the parent.

Normally when doing a flat load of an object, the aggregated child objects will stay untouched and the properties will return null. This is cool, as long you don’t need them. But out of my experience there are not only these two cases – to load the aggregated objects, or not to load. Often you don’t need the content of the aggregated object, but you need the primary key of them. For such cases you have to insert a second property which will be filled by the attribute [SelectFunction] of the AdFactum ObjectMapper .NET.

    public class Invitee : ValueObject

    {

        Contact _invitedContact;

        /// <summary>

        /// The aggregated Contact

        /// </summary>

        [PropertyName("CONTACT")]

        public Contact InvitedContact

        {

            get { return _invitedContact; }

            set { _invitedContact = value; }

        }

             

        /// <summary>

        /// Using the Select Function the ID of the aggregated

        /// object will also be mapped, even if the object

        /// is only flat loaded.

        /// </summary>

        [SelectFunction("CONTACT")]

        public Guid InvitedContactId

        {

            get { return _invitedContactId; }

            set { _invitedContactId = value; }

        }

 

 

 

 

 

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

Posted in HowTo | No Comments »

How to store binary data (blob)

Posted by Gerhard Stephan on 23rd April 2007

The ObjectMapper .NET knows 3 different ways to store binary data (blobs) in database. Two ways are designed to store binary blobs and one to store character blobs.

The straightest way to store binary data is to create a byte array property. This byte array property could look like:

        /// <summary>

        /// Gets or sets the BLOB.

        /// </summary>

        /// <value>The BLOB.</value>

        [PropertyName("Stream")]

        public byte[] Blob

        {

            get { return blob; }

            set { blob = value; }

        }

 

As a second solution the AdFactum ObjectMapper .NET offers you the possibility to use Streams. 

        /// <summary>

        /// Gets or sets the BLOB.

        /// </summary>

        /// <value>The BLOB.</value>

        [PropertyName("Stream")]

        public Stream Stream

        {

            get { return stream; }

            set { stream = value; }

        }

 

When reading from database the ObjectMapper .NET creates a Memory Stream for the blob content.

A third solution is to use character blobs. In oracle known as CBLOB or known as a MEMO field if you’re using Microsoft Access. Such character blobs are defined as regular strings with an unlimited property length.

        /// <summary>

        /// Gets or sets the content.

        /// </summary>

        /// <value>The content.</value>

        [PropertyLength(int.MaxValue)]

        public string Content

        {

            get { return content; }

            set { content = value; }

        }

 

So, that’s all you have to know about blobs if you’re using the AdFactum ObjectMapper .NET.
Hope you enjoy the simplicity.

Cheers
– Gerhard 

kick it on DotNetKicks.com

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

Posted in HowTo | No Comments »

How to build sql queries

Posted by Gerhard Stephan on 22nd January 2007

The ObjectMapper .NET offers you a wide range of possibilities in order to build powerful sql queries with a minimum effort. The ObjectMapper .NET uses conditions in order to build a SQL Where Clause. All database queries are based on the interface ICondition.

A Condition is implemented using the builder pattern. Which means that a condition can be composed of other conditions. The result of the condition builder is the sql statement (ConditionString property). Conditions can be concatinated with the Method Add and a ConditionOperator which tells the Mapper how to connect two conditions.

The interface ICondition is implemented by a wide range of sub classes and some top level categories. The top level categories are:

  • Query Lists
  • Simple Conditions
  • Join Conditions
  • Sub Queries
  • Specialities

Now let us look at the top level categories in detail.

Query Lists : ConditionList, Parenthesize

The implementation ConditionList is primarily a list of concatinated conditions. Typically this object will be used as the top level container for a query. E.g.

new ConditionList(

   new AndCondition(typeof(Employee), "Id", user.Id),

   new CollectionJoin(typeof(Employee), "Contacts", typeof(Contact)) );

The parenthesize class does almost the same. The only difference is, that the parenthesize class will set the result statement within brackets.

Simple Conditions: AndCondition, OrCondition

The most used conditions are the simple conditions. Here are the possible use cases. The examples for the OrCondition are equivalent.

public AndCondition(Type queriedObjectType, string field, object compareValue)

E.g. new AndCondition(typeof(Employee), "Id", user.Id);

or

public AndCondition(Type queriedObjectType, string field, QueryOperator comparer, object compareValue)

E.g. new AndCondition(typeof(Company), "CompanyState", QueryOperator.NotEqual, CompanyState.Deleted);

 

The first example compares always using the QueryOperator.Equal. But the ObjectMapper .NET offers many more query operators.

      /// <summary>

      /// Enumeration class to define the operators for the where clause

      /// </summary>

      public enum QueryOperator

      {

            Equals,

            NotEqual,

            Lesser,

            Greater,

            LesserEqual,

            GreaterEqual,

            Is,

            IsNot,

            In,

            Like,

            NotIn,

            NotLike,

            Like_NoCaseSensitive,

            NotLike_NoCaseSensitive

      }

 

 

Join Conditions: Join, CollectionJoin

Joins are also Conditions. That makes it easy to build joins and combine them with other where clause conditions. A distinction is drawn between "Single Joins" and "Collection Joins". What is the difference? A "Single Join" is always a join to an aggregated object like that in the following example.

      public class Preference : BaseEntity

      {

            private Classification classification;

            public Classification Classification

            {

                  get { return classification; }

                  set { classification = value; }

            }

 

With this knowledge you can build the following Join between Preference and Classification.
new Join(typeof(Preference), "Classification", typeof(Classification), "Id")

The content of field "Classification" must equal the content of the field "Id" of the Classification class. In addtion you could query a field of the class Classification.

A collection join is almost the same, except the fact that the ObjectMapper .NET has to use a link table to establish the join. Have a look at the following example.

    class Company_GenericList_Contacts : Company

    {

        private List<Contact> contacts = new List<Contact>(); 

        public List<Contact> Contacts

        {

            get { return contacts; }

            set { contacts = value; }

        }

    }

 

The join would look like that:
new CollectionJoin(typeof(Company_GenericList_Contacts), "Contacts", typeof(Contact))
 

If you want to use outer joins you have to tag the joined class with a [WeakReferenced] Attribute.

    [WeakReferenced]

    public class Contact : BaseEntity

    {

 

If you want further informations about using outer joins, have a look at the blog entry [WeakReferenced].

Sub Queries: InCondition, NotInCondition, SubSelect, Union

The ObjectMapper .NET masters also InConditions, SubSelects and Unions. An InCondition is almost the same like known from native SQL. You have to use InConditions all the time if you want to use distinct query targets within one query.

E.g. You want to select all activities where the creator or the assigned employee have a special last name. In that example you want to query the last name of an employee that is joined in two different ways. First joined by field "CreatedByEmployee" and second time joined by field "AssignedEmployee".

 

SubSelect employees = new SubSelect(typeof(Employee), "Id",

    new AndCondition(typeof(Employee), "LastName", QueryOperator.Like_NoCaseSensitive, EmployeeLastName));

                        

Parenthesize employeeClause = new Parenthesize(

      new InCondition(ConditionOperator.OR, typeof(Activity), "AssignedEmployee", employees),

      new InCondition(ConditionOperator.OR, typeof(Activity), "CreatedByEmployee", employees));

 

In this example we first create a sub selection that selects the last name of an employee with a like condition. After that we are using two connected InConditions that queries the activity object with two different properties.

Furthermore I want you to have a look at the following example where the Union Condition will be used. The following example searches all companies where the user is employee or the user is a team member of the company. 

new InCondition(typeof(Company), "Id",

new Union(

            new SubSelect(typeof(Company), "Id",

                  new CollectionJoin(typeof(Company), "Employees", typeof(Employee)),

                  new AndCondition(typeof(Employee), "Id", QueryOperator.Equals, user.Id)

                  ),

            new SubSelect(typeof(Company), "Id",

                  new CollectionJoin(typeof(Company), "Teams", typeof(Team)),

              new CollectionJoin(typeof(Team), "Members", typeof(Employee)),

                  new AndCondition(typeof(Employee), "Id", QueryOperator.Equals, user.Id))));

Typically you would query two SQLs in order to get the result list. Using the ObjectMapper .NET you can handle the query all within one SQL statement. The InCondition, combined with the SubSelect and the Union are the most powerful select features of the ObjectMapper .NET. There’re several possibilities to use it. I recommend to play with these statements and check the resulting SQL statement.

Specialities: TableReplacement, CollectionChildCondition, CollectionParentCondition

The specialities of the ObjectMapper .NET can help you to handle special use cases. The TableReplacement condition for example replaces a table name with a self defined sql phrase, e.g. the name of a view or a sub select.

return new ConditionList(

new TableReplacement(typeof(Company), “SELECT * FROM COMPANY WHERE COMPANY.COMPANYTYPE=1”),

new AndCondition(typeof(Company), "CompanyState", QueryOperator.NotEqual, CompanyState.Deleted)

 

Instead of selecting from table company the SQL will use the given Phrase to build the from clause. Using this example the resulting SQL would look like that:
SELECT * FROM (SELECT * FROM COMPANY WHERE COMPANY.COMPANYTYPE=1) Company WHERE Company.CompanyState = 5

That enables you to use views or sub queries for special behaviours.

The CollectionParentCondition and CollectionChildCondition can be used as a shortcut that combines a CollectionJoin and an AndCondition. I implemented these two Conditions because it covers common use case.

new CollectionParentCondition (typeof(Employee), "Contacts", typeof(Contact), user.Id);

 

equals

 

new ConditionList(

   new AndCondition(typeof(Employee), "Id", user.Id),

   new CollectionJoin(typeof(Employee), "Contacts", typeof(Contact)) );

 

 

 

new CollectionChildCondition (typeof(Employee), "Contacts", typeof(Contact), contact.Id);

 

equals

 

new ConditionList(

   new CollectionJoin(typeof(Employee), "Contacts", typeof(Contact)),

   new AndCondition(typeof(Contact), "Id", contact.Id),

);

 

That’s all in all the first introduction to the AdFactum ObjectMapper .NET Query API. If you have any questions I would be glad to get comments on this blog entry.

Wish you a stress-free development and always good mapped classes 🙂
– Gerhard

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

Posted in HowTo | No Comments »

How to do bind collections

Posted by Gerhard Stephan on 24th August 2006

The ObjectMapper .NET offers different possibilities to map collections to database. All different solutions have pro and contras. This post shall lighten those pros and contras in order to give you the ability to choose the proper solution for your use case.

General IList Binding:

        private IList contacts = new ArrayList();

        public IList Contacts

        {

            get { return contacts; }

            set { contacts = value; }

        }

 

Pro:

  • You can add all objects to the List that are derived from IValueObject.

Contra:

  • You can’t use foreign key constraints.
  • The performance is worse, compared to other solutions, because the List can contain all type of objects. Perhaps the ObjectMapper .NET has to read from multiple tables.

Bound IList Binding:

        private IList contacts = new ArrayList();

 

        [BindPropertyTo(typeof(Contact))]

        public IList Contacts

        {

            get { return contacts; }

            set { contacts = value; }

        }

 

Pro:

  • You get best performance, because the ObjectMapper .NET knows which objects you expect.
  • The ObjectMapper .NET creates foreign key constraints.
  • You get a better data integrity due to the use of foreign key constraints.

Contra:

  • It’s only possible to store objects of the type you specified.

Generic List Binding:

        private List<Contact> contacts = new List<Contact>();

 

        public List<Contact> Contacts

        {

            get { return contacts; }

            set { contacts = value; }

        }

Pro:

  • You get best performance, because the ObjectMapper .NET knows which objects you expect.
  • The ObjectMapper .NET creates foreign key constraints.
  • You get a better data integrity due to the use of foreign key constraints.

Contra:

  • It’s only possible to store objects of the type you specified.

General Generic List Binding:

        private List<Contact> contacts = new List<Contact>();

 

        [GeneralLink]

        public List<Contact> Contacts

        {

            get { return contacts; }

            set { contacts = value; }

        }

Pro:

  • You can add all objects to the List that are derived from the type you specified.

Contra:

  • You can’t use foreign key constraints.
  • The performance is worse, compared to other solutions, because the list can contain all types derived from the type you specified. Perhaps the ObjectMapper .NET has to read from multiple tables.

Important

Using the current version of the ObjectMapper .NET it’s not possible to bind generic IList<T> collections. This is because the generic IList<T> interface does not support the IList interface. We try to offer this feature as soon as possible.

So forget about the following example; it will produce failures:

 

        private IList<Contact> contacts = new List<Contact>();

 

        public IList<Contact> Contacts

        {

            get { return contacts; }

            set { contacts = value; }

        }

 

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

Posted in HowTo | 2 Comments »

How to setup database tables instantly

Posted by Gerhard Stephan on 9th August 2006

If you think on NUnit testing it is mandatory to ensure that your starting position is always the same. Especially when working with data that changes during a test run.

One solution for writing NUnit test is to setup new database tables before running the tests. Using the ObjectMapper .NET this can be done with a few lines.

            SqlPersister persister = new SqlPersister();

            ObjectMapper mapper = new ObjectMapper(…);

           

            /*

             * Add the types

             */

            ArrayList types = new ArrayList();

            types.Add(typeof (Contact));

            types.Add(typeof (Company));

           

            /*

             * Create the tables instantly

             */

            StreamWriter writer = new StreamWriter(new MemoryStream());

            persister.WriteSchemaDif(writer, types, mapper);

            writer.BaseStream.Seek(0, SeekOrigin.Begin);

 

            SqlFile file = new SqlFile(writer.BaseStream);

            file.ExecuteScript(persister);

 

The hint is to export the tables to a memory stream and to use this stream for executing the SqlFile.

Cheers
Gerhard

1 Star2 Stars3 Stars4 Stars5 Stars (1 votes, average: 3.00 out of 5)
Loading...

Posted in HowTo | No Comments »