Creating the model

by Alexey Shirshov May 14, 2009 10:48

Worm has three ways to create object model

Use code generator
Write mapping schema for object manually
Use attribute-based mapping

If you already have object model you can use POCO functionality to map the classes.

By this time Worm has no full-functional VS designer and the only way to automatically generate model is to use codegen utilitis. There is two

Metadata generator. The program generates xml file from database structure
Code generator. Generate code from xml file

To generate xml from AdventureWorks run the following

Worm.CodeGen.XmlGenerator.exe -S=(local)\sqlexpress -E -D=AdventureWorks

The program produce AdventureWorks.xml (143.19 kb).

Now, you can generate code from the xml via code generator or custom tool. VS custom tool is code generator that can be used with Visual Studio. To use it you should

register custom tool in VS
add xml to solution
assign custom tool to xml file

Here is how to register custom tool in Visual Studio

regasm /codebase <path to custom tool>\Worm.CodeGen.VSTool.dll

Now you can add xml file to solution and set Custom tool property of the AdventureWorks.xml file to WormEntityClassGenerator.

Tags: codegenerator, metagenerator, customtool

AdventureWorks mapping: multitable entity

by Alexey Shirshov April 17, 2009 11:21

Almoust all we have done in previous posts can be implemented in LINQ to SQL. Now I'm gonna show you how to create multitable entity, which is imposible in LINQ to SQL.

Suppose we have the following two tables.

We want unite those table into a single entity called SalesOrder. This is a complex mapping, so we have to use certain class to define the mapping. I called this class entity schema class. Since we have two tables, the entity schema class should implement IMultiTableObjectSchema interface. But first let's start from SalesOrder entity.

    [Entity(typeof(EntitySchema), "1")]
    public class SalesOrder
    {
        public int SalesOrderDetailID { get; set; }
        public short OrderQty { get; set; }
        public decimal LineTotal { get; set; } 

        //SalesOrderHeader fields
        public DateTime OrderDate { get; set; }
        public exam1sharp.Sales.SalesTerritory Territory { get; set; }
        public exam1sharp.Sales.SalesPerson Person { get; set; } 

        public static QueryCmd Query
        {
            get
            {
                return new QueryCmd(exam1sharp.Properties.Settings.Default.connString)
                    .From(typeof(SalesOrder))
                    .Select(typeof(SalesOrder));
            }
        }
    }

As you can see none of the property has any attribute so this is complete POCO. All mapping data aggregated in entity schema.

SalesOrderHeader table has references to SalesTerritory and SalesPerson, so we add them in class. They are from Sales namespace.

C#-Code: EntitySchema

    public class EntitySchema : IMultiTableObjectSchema
    {
        //define tables
        private SourceFragment[] _tables = new SourceFragment[]{
                new SourceFragment("Sales","SalesOrderHeader"),
                new SourceFragment("Sales","SalesOrderDetail")
            }; 

        //define join
        public Worm.Criteria.Joins.QueryJoin GetJoins(SourceFragment left, SourceFragment right)
        {
            return JCtor.join(right).on(left, "SalesOrderID").eq(right, "SalesOrderID");
        } 

        //define mapping
        public Worm.Collections.IndexedCollection<string, MapField2Column> GetFieldColumnMap()
        {
            OrmObjectIndex columns = new OrmObjectIndex();
            columns.Add(new MapField2Column("SalesOrderDetailID", "SalesOrderDetailID", _tables[1], Field2DbRelations.PK));
            columns.Add(new MapField2Column("OrderQty", "OrderQty", _tables[1]));
            columns.Add(new MapField2Column("LineTotal", "LineTotal", _tables[1]));
            columns.Add(new MapField2Column("OrderDate", "OrderDate", _tables[0]));
            columns.Add(new MapField2Column("Territory", "TerritoryID", _tables[0]));
            columns.Add(new MapField2Column("Person", "SalesPersonID", _tables[0]));
            return columns;
        } 

        public SourceFragment[] GetTables()
        {
            return _tables;
        } 

        public SourceFragment Table
        {
            get { return _tables[0]; }
        }
    }

Now you can use the SalesOrder and don't think about in what table the property you requsted is.

The program prints SalesOrder with a LineTotal less than 10 and OrderDate equals to 2003-08-01.

        static void Main(string[] args)
        {
            foreach (SalesOrder s in SalesOrder.Query
                .Where(Ctor
                    .prop(typeof(SalesOrder), "OrderDate").eq("2003-08-01")
                    .and(typeof(SalesOrder), "LineTotal").less_than(10))
                .ToList())
            {
                Console.WriteLine("Date: {0}, LineTotal: {1}, sales territory: {2}", 
                    s.OrderDate, 
                    s.LineTotal, 
                    s.Territory.Name);
            }
        }

Here is generated statement.


declare @p1 VarChar(10);set @p1 = '2003-08-01'
declare @p2 Int;set @p2 = 10
select 
--SalesOrder columns
   t2.LineTotal, t1.OrderDate, t2.OrderQty, t1.SalesPersonID, t2.SalesOrderDetailID, t1.TerritoryID, 
--SalesPerson columns
   t3.Bonus, t3.CommissionPct, t3.SalesPersonID, t3.ModifiedDate, t3.SalesLastYear, t3.SalesQuota, t3.TerritoryID, t3.SalesYTD, t3.rowguid, 
--Order SalesTerritory columns
   t4.CostLastYear, t4.CostYTD, t4.CountryRegionCode, t4.[Group], t4.TerritoryID, t4.ModifiedDate, t4.Name, t4.SalesLastYear, t4.SalesYTD, t4.rowguid, 
--Person SalesTerritory columns
   t5.CostLastYear, t5.CostYTD, t5.CountryRegionCode, t5.[Group], t5.TerritoryID, t5.ModifiedDate, t5.Name, t5.SalesLastYear, t5.SalesYTD, t5.rowguid 
from Sales.SalesOrderHeader t1
 join Sales.SalesOrderDetail t2 on t1.SalesOrderID = t2.SalesOrderID 
 join Sales.SalesPerson t3 on t3.SalesPersonID = t1.SalesPersonID 
 join Sales.SalesTerritory t4 on t4.TerritoryID = t1.TerritoryID 
 join Sales.SalesTerritory t5 on t5.TerritoryID = t3.TerritoryID 
where (t1.OrderDate = @p1 and t2.LineTotal < @p2)

Tags: linq to sql, poco

Relation mapping

by Alexey Shirshov April 15, 2009 09:59

In the last post I wrote that I'm finish with AdventureWorks mapping. I was wrong. :) There are still a lot of things I'm gonna show you.

Ok, let's talk about relations. In the Store class there is a property SalesPerson which means one to many relation between Store and SalesPerson. One SalesPerson has multiple Stores and one Store has single SalesPerson.

So in our model there isn't property in SalesPerson class that returns collection on Stores. Let's add it.

        public QueryCmd Stores
        {
            get
            {
                return Store.Query
                    .Where(Ctor.prop(typeof(Store), "SalesPerson").eq(this));
            }
        }

As you can see we add Stores property which returns QueryCmd. We have seen QueryCmd in previous examples. It's not a collection, it is query command which can be used to get collection by executing it. In the following example we invoke ToList method to execute command and get collection of Store class instances.

            foreach (exam1sharp.Sales.Store s in p.Stores.ToList())
            {
                Console.WriteLine("Store id: {0}, name: {1}, sales territory: {2}",
                    s.ID,
                    s.Name,
                    s.SalesPerson.SalesTerritory.Name);
            }

where p is SalesPerson variable. It can be obtained in the following manner

            exam1sharp.Sales.SalesPerson p = exam1sharp.Sales.SalesPerson.Query
                .Where(Ctor.prop(typeof(exam1sharp.Sales.SalesPerson),"ID").eq(280))
                .Single() as exam1sharp.Sales.SalesPerson;

Here we select SalesPerson with ID property equals to 280.

Tags:

AdventureWorks mapping: comlpete implementation of three entities

by Alexey Shirshov April 13, 2009 12:01

Ok. Let's finish our trip to AdventureWorks mapping. The following steps need to be done:

Create base class containing ModifiedDate and Timestamp properties. Those properties present in all entities.
Fulfil SalesPerson and SalesTerritory classes with appropriate properties
Create Query static property in SalesPerson class.

So, here is complete classes code (single file).

 

using System;
using System.Collections.Generic;
using System.Text;
using Worm.Entities.Meta;
using Worm.Query;
using System.Xml; 

namespace exam1sharp.Sales
{
    public class SalesBase
    {
        public DateTime ModifiedDate { get; set; } 

        [EntityProperty("rowguid", Field2DbRelations.RowVersion)]
        public Guid Timestamp { get; protected set; }
    } 

    [Entity("Sales", "Store", "1")]
    public class Store : SalesBase
    {
        [EntityProperty("CustomerID", Field2DbRelations.PK)]
        public int ID { get; set; } 

        public string Name { get; set; } 

        public XmlDocument Demographics { get; set; } 

        [EntityProperty("SalesPersonID")]
        public SalesPerson SalesPerson { get; set; } 

        public static QueryCmd Query
        {
            get
            {
                return new QueryCmd(exam1sharp.Properties.Settings.Default.connString)
                    .From(typeof(Store))
                    .Select(typeof(Store));
            }
        }
    } 

    [Entity("Sales", "SalesPerson", "1")]
    public class SalesPerson : SalesBase
    {
        [EntityProperty("SalesPersonID", Field2DbRelations.PK)]
        public int ID { get; set; } 

        public decimal? SalesQuota { get; set; } 

        [EntityProperty("TerritoryID")]
        public SalesTerritory SalesTerritory { get; set; } 

        public decimal Bonus { get; set; } 

        public decimal CommissionPct { get; set; } 

        public decimal SalesYTD { get; set; } 

        public decimal SalesLastYear { get; set; } 

        public static QueryCmd Query
        {
            get
            {
                return new QueryCmd(exam1sharp.Properties.Settings.Default.connString)
                    .From(typeof(SalesPerson))
                    .Select(typeof(SalesPerson));
            }
        }
    } 

    [Entity("Sales", "SalesTerritory", "1")]
    public class SalesTerritory : SalesBase
    {
        [EntityProperty("TerritoryID", Field2DbRelations.PK)]
        public int ID { get; set; } 

        public string Name { get; set; } 

        public string CountryRegionCode { get; set; } 

        [EntityProperty("[Group]")]
        public string Group { get; set; } 

        public decimal SalesYTD { get; set; } 

        public decimal SalesLastYear { get; set; } 

        public decimal CostYTD { get; set; } 

        public decimal CostLastYear { get; set; }
    }
}

Program almoust not changed.

        static void Main(string[] args)
        {
            foreach (exam1sharp.Sales.Store s in exam1sharp.Sales.Store.Query
                .Where(Ctor.prop(typeof(exam1sharp.Sales.Store), "Name").like("A%"))
                .ToList())
            {
                Console.WriteLine("Store id: {0}, name: {1}, sales territory: {2}", 
                   s.ID, 
                   s.Name, 
                   s.SalesPerson.SalesTerritory.Name);
            }
        }

And the most interesting piece of code for the real docs.


declare @p1 VarChar(2);set @p1 = 'A%'
select 

   t1.Demographics, t1.CustomerID, t1.ModifiedDate, t1.Name, t1.SalesPersonID, t1.rowguid, 
   t2.Bonus, t2.CommissionPct, t2.SalesPersonID, t2.ModifiedDate, t2.SalesLastYear, t2.SalesQuota, t2.TerritoryID, t2.SalesYTD, t2.rowguid, 
   t3.CostLastYear, t3.CostYTD, t3.CountryRegionCode, t3.[Group], t3.TerritoryID, t3.ModifiedDate, t3.Name, t3.SalesLastYear, t3.SalesYTD, t3.rowguid 
from Sales.Store t1 
join Sales.SalesPerson t2 on t2.SalesPersonID = t1.SalesPersonID 
join Sales.SalesTerritory t3 on t3.TerritoryID = t2.TerritoryID 
where t1.Name like @p1

As you can see, the SQL code is simple, readable and solid. It's very similar to human coding. Nothing superfluous.

Tags: poco

AdventureWorks mapping: Entity references

by Alexey Shirshov April 13, 2009 09:48

In the previous example, we create Store entity which has SalesPersonID property type of int. This is a database reference to SalesPerson object. Let's implement this reference in our model.

Obviously, we have to create SalesPerson entity.

    [Entity("Sales", "SalesPerson", "1")]
    public class SalesPerson
    {
        [EntityProperty("SalesPersonID", Field2DbRelations.PK)]
        public int ID { get; set; } 

        public decimal SalesQuota { get; set; }
    }

The next and the last thing we have to do - change type and name of the corresponding property in Store class.

        [EntityProperty("SalesPersonID")]
        public SalesPerson SalesPerson { get; set; }

That is all. Here is the code of all classes.

 

    [Entity("Sales", "Store", "1")]
    public class Store4
    {
        [EntityProperty("CustomerID", Field2DbRelations.PK)]
        public int ID { get; set; } 

        public string Name { get; set; } 

        public DateTime ModifiedDate { get; set; } 

        public XmlDocument Demographics { get; set; } 

        [EntityProperty("rowguid", Field2DbRelations.RowVersion)]
        public Guid Timestamp { get; protected set; } 

        [EntityProperty("SalesPersonID")]
        public SalesPerson SalesPerson { get; set; } 

        public static QueryCmd Query
        {
            get
            {
                return new QueryCmd(exam1sharp.Properties.Settings.Default.connString)
                    .From(typeof(Store4))
                    .Select(typeof(Store4));
            }
        }
    } 

    [Entity("Sales", "SalesPerson", "1")]
    public class SalesPerson
    {
        [EntityProperty("SalesPersonID", Field2DbRelations.PK)]
        public int ID { get; set; } 

        public decimal SalesQuota { get; set; }
    }

And the program prints sales person quota information.

        static void Main(string[] args)
        {
            foreach (Store4 s in Store4.Query
                .Where(Ctor.prop(typeof(Store4), "Name").like("A%"))
                .ToList())
            {
                Console.WriteLine("Store id: {0}, name: {1}, sales person quota: {2}", s.ID, s.Name, s.SalesPerson.SalesQuota);
            }
        }

Tags: poco

AdventureWorks mapping: almost full Sales.Store implementation

by Alexey Shirshov April 08, 2009 10:21

It's time to complicate an example.

First of all I'm going to assign database table to our entity. In the previous examples, we have to specify table in From method. To avoid this we should mark entity class with EntityAttribute attribute.

[Entity("Sales", "Store", "1")]

Here we specify Sales database schema and Store table name. 1 - entity schema version (forget it).

The next improvment - implement static Query property returns QueryCmd instance.

    [Entity("Sales", "Store", "1")]
    public class Store3
    {
        [EntityProperty("CustomerID", Field2DbRelations.PK)]
        public int ID { get; set; }

        public string Name { get; set; }
        
        public DateTime ModifiedDate { get; set; }
        
        public XmlDocument Demographics { get; set; }        
        
        [EntityProperty("rowguid", Field2DbRelations.RowVersion)]
        public Guid Timestamp { get; protected set; }

        public int SalesPersonID { get; set; }

        public static QueryCmd Query
        {
            get
            {
                return new QueryCmd(exam1sharp.Properties.Settings.Default.connString)
                    .From(typeof(Store3))
                    .Select(typeof(Store3));
            }
        }
    }

The program prints stores whos name starts with A.

        static void Main5(string[] args)
        {
            foreach (Store3 s in Store3.Query
                .Where(Ctor.prop(typeof(Store3), "Name").like("A%"))
                .ToList())
            {
                Console.WriteLine("Store id: {0}, name: {1}, timestamp: {2}", s.ID, s.Name, s.Timestamp);
            }
        }

Tags:

Continue AdventureWorks mapping

by Alexey Shirshov April 07, 2009 16:01

In the previous post we have seen how simple we can map database table to class. Let's extend the mapping.

Suppose we don't want to use CustomerID. Instead, we want ID property. Also we want to add ModifiedDate column. Here is the code

    public class Store2
    {
        [EntityProperty("CustomerID")]
        public int ID { get; set; }
        public string Name { get; set; }
        public DateTime ModifiedDate { get; set; }
    }

The program might look like previous version with Store class.

        static void Main(string[] args)
        {
            var query = new QueryCmd(exam1sharp.Properties.Settings.Default.connString);

            foreach (Store2 s in query
                .From(new SourceFragment("Sales", "Store"))
                .ToPODList())
            {
                Console.WriteLine("Store id: {0}, name: {1}", s.ID, s.Name);
            }
        }

Tags:

Let's getting started

by Alexey Shirshov December 23, 2008 11:06

Consider the following table "Store"

taked from standard AdventureWorks database. How can we map the table with a minimum cost to access the data from code.

Well first of all you should add reference to Worm.Orm.dll and CoreFramework.dll assemblies. The next thing - create Store class.

    public class Store
    {
        public int CustomerID { get; set; }
        public string Name { get; set; }
    }

As you can see it just has two properties CustomerID and Name.

Third thing you should do is to add connection string to you database. For instance

Server=.\sqlexpress;Initial Catalog=AdventureWorks;Integrated security=true;

And the final step - writing a program.

        static void Main(string[] args)
        {
            var query = new QueryCmd(exam1sharp.Properties.Settings.Default.connString);

            foreach (Store s in query
                .From(new SourceFragment("Sales", "Store"))
                .ToPODList())
            {
                Console.WriteLine("Store id: {0}, name: {1}", s.CustomerID, s.Name);
            }
        }

Tags:

Powered by BlogEngine.NET 1.4.5.0
Theme by Mads Kristensen | Modified by Mooglegiant