Wiki

Clone wiki

Wxen.NET DbHug / Mapping

##DbHug

## Attribute Mappings ###Mapping. Property / field mapping to DB fields.


using System.ComponentModel.DataAnnotations; namespace.

  • ColumnAttribute to map Property or field. Column name may be specified.
  • KeyAttribute marks a field a primary key. Id name of the property or field considered PK, note that KeyAttribute has higher priority then the name.
  • NotMappedAttribute used when property or field is not mapped to DB.
  • DatabaseGeneratedAttribute indicates if value is generated by database.
  • TableAttribute associates a class to a database table. Table name and Schema can be specified.
  • RequiredAttribute marks a data field as required.
  • Readonly fields and properties with private or absent setter unable to map normally. See constructor mapping below.

Any public instance property or field will be mapped automatically by its name.
Any not scalar type will be ignored in automatic mapping and throw MappingsException if you'll try force it (except entity parts).

To map Property it should be public have both get and set accessors.
To map Field it should be public and not readonly.

###Mapping. Class mapping to DB table.

#!csharp

[Table("DbTable")]
class Test
{
  [Key]
  [Column("p_key")]
  [Required]
  [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
  public int Key { get; set; }

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

  [Column("p_b_date"]
  [DatabaseGenerated(DatabaseGeneratedOption.Computed)]
  public DateTime Date {get; set; }

  [NotMapped]
  public double Age {get{ return (DateTime.Now - Date).GetTotalYears(); } }
  //GetTotalYears() - custom extension method
}

[Table("Table", Schema = "Schema")]
class TestSchema
{
  public int Id { get; set; }
  public string Name { get; set; }
}
When class is mapped to table, CRUD Select() method will try to select table values by a specified name.
#!csharp
var tests = dbHug.Select<TestSchema>();
//Sql statement to order selection
SQL will be
#!sql
SELECT Id, Name FROM Schema.Table;

## Api Mappings The other way to map enteties is to use special API. It allows to keep entity models clean of attributes.
DbHug mapping Api has same functionality as Attribute mapping.
To create custom mappings create class derived from Mappings, override method CreateMappings, map entities and then pass it to DbHug constructor by new MappingsConfig<T>() or Mappings.Configure<T>() where the generic type is the custom mappings class.
This example makes same mappings as an example with attribute for Test class.

#!csharp
class MyMappings : Mappings
{
  public override void CreateMappings()
  {
    Map<Test>().ToTable<Test>("DbTable");
    Map<Test>(map=>
    {
      map.PropOrField(o => o.Key).ToColumn("p_key").MakeKey().Required().MakeDbGenerated();
      map.PropOrField(o => o.Name).ToColumn("p_name");
      map.PropOrField(o => o.Date).ToColumn("p_b_date").MakeDbGenerated();
      map.PropOrField(o => o.Age).DoNotMap();
    });
  }
}
And create instance of DbHug with specified mappings.
#!csharp
//Create `DbHug` instance with custom mappings
dbHug = new DbHug(..., Mappings.Configure<MyMappings>());
###Mapping. Constructor and initialize methods.


In case if there is a need to create entity by constructor that takes parameters or do something in entity just after its creation use InitConsructor and InitMethod functionality.

Constructor mapping

  • InitConstructorAttribute or Map<T>.Constructor() - maps constructor to be used when entity initializes by DbHug from database.

Constructor parameters can be maped to sql result by name (its default behavior), or custom column by FromColumnAttribure or Map<T>.Constructor().MapParameters(). Constructor() method takes types in order which takes them constructor. So constructor public Test(int id, string lastname) will be mapped by Constructor(typeof(int), typeof(string)).

#!csharp
class Test
{
  [InitConstructor]
  public Test(int id, [FromColumn("Name")]string lastname)
  {
    Id = id;
    Name = lastname;
  }

  public int Id { get; private set; }
  public string Name { get; private set; }
}
same by API
#!csharp
class MyMappings : Mappings
{
  protected override void CreateMappings()
  {
    Map<Test>().Constructor(typeof(int), typeof(string)).MapParameters("lastname", "Name");
  }
}

Initialize method mapping

  • InitMethodAttribute or Map<T>().InitMethod().

Initialize method is a void without parameters, which will be executed when entity created, mapped and ready.

#!csharp
class Test
{
  public int Id { get; set; }
  public DateTime InitDate {get; private set;}

  [InitMethod]
  public void Init()
  {
        InitDate = DateTime.Now;
  }
}
same by API
#!csharp
class MyMappings : Mappings
{
  protected override void CreateMappings()
  {
    Map<Test>().InitMethod("Init");
  }
}

Additions

If you want to change DbParameter generated name pattern (see templating) you have to change ParameterNamePlaceholder property. So any generated name for parameter templating will follow that pattern.

#!csharp
class MyMappings : Mappings
{
  protected override void CreateMappings()
  {
    this.ParameterNamePlaceholder = "my_{0}_dbParam";
  }
}
Value must contain {0} where the property name will be inserted.
#!csharp
dbHug.Select<Person>("SELECT * FROM PERSONS WHERE Id = {Id}", new { Id = 1 });
#!sql
SELECT * FROM PERSONS WHERE Id = @my_Id_dbParam;
But this does not work for parameters without templating:
#!csharp
dbHug.Select<Person>("SELECT * FROM PERSONS WHERE Id = @Id", new { Id = 1 });
#!sql
SELECT * FROM PERSONS WHERE Id = @Id;


If you want to change generated names for ComposeCommand method change the value of ParameterNameForFormatter property.

#!csharp
class MyMappings : Mappings
{
  protected override void CreateMappings()
  {
    this.ParameterNameForFormatter= "my_param_";
  }
}
It does not need {0}, it will be a prefix for passed values. For example for first one it will be my_param_0, for second - my_param_1 so on.
Default is dp_.


###Mapping Type To DbType. In some cases you may need to use types that is not supported by DbHug out of the box, or in case if some type does not supported by Database.
To create custom type mappings you should use DbTypeMappings.MapCustom() method.

#!csharp
DbTypeMappings.MapCustom<T>(object customDbType, IDbConverter converter);
As an example we will use well known issue of using Boolean in Oracle database. As we know Oracle has nothing to support bool, of course we may create property inside model that converts any value into Boolean. Like so:
#!csharp
class SomeModel
{
    [Column("can_we")]
    public int Value { get; set; }
    [NotMapped]
    public bool BoolValue
    {
        get { return Value == 1; }
    }
}
But if we want to keep our models clear of such things. We need to create Converter for this type and map the type and the converter.
For this example we will use case when Boolean may be Nullable. But it works for non-nullable as well.
#!csharp
class OracleBooleanConverter : IDbConverter
{
    public object ConvertToDbType(object value, Type fromType, object dbType)
    {
        if (value is bool)
            return ((bool)value) ? 1 : 0;
        return null;
    }

    public object ConvertFromDbType(object value, Type toType)
    {
        if (value == null)
            return null;
        try
        {
            var val = Convert.ToInt32(value);
            return val == 1;
        }
        catch
        {
            return null;
        }
    }

    public void PrepareDbParam(DbParameter param, object originalValue, object convertedValue, Type fromType, object dbType)
    {
        var p = param as OracleParameter;
        if (p != null)
        {
            p.Value = convertedValue;
            p.OracleDbType = (OracleDbType)dbType;
        }
    }
}
As you see it has three simple methods. One to convert to database type, one from.
And one to set parameter when parameter takes this type.
It is pretty understandable, so keep going.
When converter is created we need to map the type.
#!csharp
DbTypeMappings.MapCustom<bool>(OracleDbType.Int32, new OracleBooleanConverter());
DbTypeMappings.MapCustom<bool?>(OracleDbType.Int32, new OracleBooleanConverter());
Thats it. Now the model from example above looks like this:
#!csharp
class SomeModel
{
    [Column("can_we")]
    public bool Value { get; set; }
}

DbTypeMappings is static so any custom mapping will be used by all DbHug instances.

####Querying ... Templating

Updated