Sunday, August 23, 2015

The models is the base for all the ORM libraries. Let’s create two models which will be used in order to demonstrate how PetaPoco works. We will have two tables. The first table will be the Authors and the second table will be the books that they have published. The first model is going to be the author and we save the file at the location "~/App_Code/ComputerMagic/PetaPoco/Models/Author.cs".

using System;
using Umbraco.Core.Persistence;
using Umbraco.Core.Persistence.DatabaseAnnotations;

namespace ComputerMagic.PetaPoco.Models
{
    [TableName("CMAuthors")]
    [PrimaryKey("AuthorID", autoIncrement = true)]
    [ExplicitColumns]
    public class Author
    {
        [Column("AuthorID")]
        [PrimaryKeyColumn(AutoIncrement = true)]
        public int AuthorID { get; set; }

        [Column("Name")]
        [Length(20)]
        public string Name { get; set; }

        [Column("Surname")]
        [Length(20)]
        public string Surname { get; set; }

        [Column("DateOfBirth")]
        public DateTime DateOfBirth { get; set; }
    }
}

The second model is going to be the book and we save the file at the location "~/App_Code/ComputerMagic/PetaPoco/Models/Book.cs".

using System;
using Umbraco.Core.Persistence;
using Umbraco.Core.Persistence.DatabaseAnnotations;

namespace ComputerMagic.PetaPoco.Models
{
    [TableName("CMBooks")]
    [PrimaryKey("BookID", autoIncrement = true)]
    [ExplicitColumns]
    public class Book
    {
        [Column("BookID")]
        [PrimaryKeyColumn(AutoIncrement = true)]
        public int BookID { get; set; }

        [Column("AuthorID")]
        [ForeignKey(typeof(Author), Name = "FK_Book_Author")]
        [IndexAttribute(IndexTypes.NonClustered, Name = "IX_AuthorID")]
        public int AuthorID { get; set; }

        [Column("Title")]
        [Length(120)]
        public string Title { get; set; }

        [Column("Description")]
        [SpecialDbType(SpecialDbTypes.NTEXT)]
        [NullSetting(NullSetting = NullSettings.Null)]
        public string Description { get; set; }

        [Column("Year")]
        public int Year { get; set; }
    }
}

As we can see PetaPoco comes with a list of attributes which allow us to configure properly all the items in our model. For the class we have three attributes.

The first attribute is the TableName which allow us to ignore the class name give a different name for the table. In our example we took advantage of this attribute in order to add a prefix to the table names so that they will group together when the list of the tables will appear.

The second attribute is the PrimaryKey which allow us to inform the system which field is going to be our primary key. We also inform the system that this field is going to be an auto increment field.

The third attribute is the ExplicitColumns attribute which will have as a result not to map all the fields of the class with the equivalent database fields but we can define which fields should be mapped using the Column attribute for each of the fields that we want to map. This is very useful because we can use partial classes which will contain the Calculated fields in order to cover all our needs.

For each of the fields we have a variety of attributes. The first attribute that we will talk about is the Column attribute which is used to define which column of the table it is going to be mapped with. We provide the name of the column which can be different from the property name.

For the primary key column we use the PrimaryKeyColumn attribute which informs the system about which field is going to be the primary key and if this field is going to have auto increment values or not.

We can use the ForeignKey attribute which allow us to define which class is going to be used in order to create the foreign key relationship and we also supply the name of the relationship.

We also can use the IndexAttribute attribute in order to give information about the index that we want to use.

For the string properties we can use the Length attribute to define the maximum length of the string and the SpecialDbType attribute in order to give information about the data type for long strings.

We also used the NullSetting attribute in order to give information about allowing null values in a field.