How to avoid code duplication when modeling a table, its layout and its records, all of which have the same basic structure?

Question

This will be a somewhat abstract question.

I am working on an Access Access database that should distinguish between a table, its abstract layout / layout and specific table entries. I am afraid that because of this difference there will be a lot of code duplication. I may need some information on ways to avoid this.

+-----------+
|    Foo    |
+-----------+
| +Id: Guid |
+-----------+

Note that in this diagram you can describe any of them: a table schema, a specific table, or a specific table record that has a Idtype field Guid.

• All that is known in the schema is the name and type of the field.
• In the specific (open) table, the field "column index" is additionally indicated.
• , .

, ( ). , ; , , - :

// these interfaces only need to be implemented once:

interface ISchemaField<T>  {  string Name  { get; }       }


interface ITableField<T>   {  string Name  { get; }        
                              int    Index { get; }       }

interface IRecordField<T>  {  string Name  { get; }        
                              int    Index { get; }       
                              T      Value { get; set; }  }

---

// these three interfaces are an example for one entity; there would be
// three additional types for each additional entity.

interface IFooSchema
{
    ISchemaField<Guid> Id { get; }
    IFooTable Open(IDbConnection dbConnection, string tableName);
}

interface IFooTable
{
    ITableField<Guid> Id { get; }
    ICollection<IFooRecord> ExecuteSomeQuery();
}

interface IFooRecord
{
    IRecordField<Guid> Id { get; }
}

. ?

• (, T4), , "" ( ) .

• , , ... .

Answer 1

, , . . , , F12, , .

, , , , , , . , , . , . (HARDCODING !). :

this.Price = reader["Price"] as decimal?;

, !

, , . ( = ). , , , , . , . , .

Answer 2

AutoMapper. DTO POCO .. .

Answer 3

interface ISchemaField<T>
{
    string Name { get; }
}

interface ITableField<T> : ISchemaField<T>
{
    int Index { get; }
}

interface IRecordField<T> : ITableField<T>
{
    T Value { get; set; }
}

, , .

Answer 4

, , , :

• , . : " " "". , . , .

• Field names are static fields or simply string constants in a table type.

• Then I get some duplication on the left, namely the similarity of the two types: one for one record, the other for a table / schema, but this is acceptable.

To summarize, I chose a more pragmatic solution than I would like; it's not so clean, but most likely, everything will be done.

---

class FooRecord
{
    FooRecord(FooTable table) { … }
    readonly FooTable table;

    public Guid Id { get { … } set { … } }
}

class TableField<T>
{
    public TableField(string name) { … }
    public string Name { get; }
    public int Index { get; internal set; }
}

class FooTable
{
    public void Open(IDbConnection dbConnection, string tableName) { … }
    // ^ opens the table and determines the fields' indices, e.g. Id.Index.

    public TableField<Guid> Id
    {
        get
        {
            return idField;
        }
    }
    TableField<Guid> idField = new TableField<Guid>("ID");
}