1

I have an extremely complicated relational database, which is causing me to use the same code over and over again. I'm trying to clean it up using a fairly sophisticated TableRow class, however I've came across one major problem. The best way I can explain what I'm trying to do is by some example code:

public abstract class TableRow
{
    public abstract string TableName { get; }
    public abstract string PrimaryKey { get; }

    //there's a lot of these sort of methods for multi-to-multi links, links by two columns, etc
    protected T GetLinkedRow<T>() where T : TableRow
    {
        //here I need to get TableName and PrimaryKey (without creating a new instance of T)
    }
 }

 public class Person : TableRow
 {
    public override string TableName { get { return "People"; } }
    public override string PrimaryKey { get { return "PersonID"; } }
 }

 public class Dog : TableRow
 {
    public override string TableName { get { return "Dogs"; } }
    public override string PrimaryKey { get { return "DogID"; } }

    public Person GetOwner() { return GetLinkedRow<Person>(); }
 }

Now I realise this is not the way to go about what I'm trying to achieve, because I COULD make a class that has a variable value for TableName or PrimaryKey, but I'm not going to. I'd like to be able to override a static property, but I know this is not possible. I did previously have a class per table, but that meant there was 70 fairly useless classes and that's what I'm trying to avoid. What would be a better way to go around this sort of issue?

Update:

I eventually went with creating a new instance of T to get the TableName, as I will be initialising the new instance anyway. Although SLaks' solution is much better than this, it doesn't guarantee that the derived class has the TableName and PrimaryKey attributes. For the majority of people, SLaks' solution will be more appropriate, so I will mark that as the accepted answer.

protected T GetLinkedRow<T>() where T : TableRow, new()
{
    T row = new T();
    DbDataReader reader = ExecuteReader("SELECT * FROM [" + row.TableName + "] WHERE [" + this.PrimaryKey + "] = " + this.ID);
    if(!reader.Read())
        throw new Exception("No linked row found");
    row.Load(reader);
    return row;
}

//here's the reason I didn't really want to do this in the first place. Many-to-many relationship. quite messy
protected IEnumerable<T> GetLinkedRows<T>(string junctionTable) where T : TableRow, new()
{
    T row = new T();
    DbDataReader reader = ExecuteReader("SELECT * FROM [" + row.TableName + "] INNER JOIN [" + junctionTable + "] ON [" + row.TableName + "].[" + row.PrimaryKey + "] = [" + junctionTable + "].[" + row.PrimaryKey + "] WHERE [" + junctionTable + "].[" + this.PrimaryKey + "] = " + this.ID;
    while(reader.Read()) {
        row.Load(reader);
        yield return row;
        if(reader.HasRows)
            row = new T();
    }
}
Connell
  • 13,925
  • 11
  • 59
  • 92
  • The 'abstract' doesn't make sense on `Person` or `Dog`, or am I missing something? – Benjamin Podszun May 16 '11 at 22:22
  • My mistake. It should've said override. Thanks to Mikant for correcting that. – Connell May 16 '11 at 22:26
  • Have you considered using an ORM like Entity Framework, which will take care of all these details for you? – StriplingWarrior May 16 '11 at 22:40
  • Have considered it yes, but there's nothing I can find that will do exactly what I need. All the tables are build in a very specific structure, some linked by a simple dictionary table, some just linked one-to-many and some linked by two columns (PageType, and PageID). Just under half of the tables inherit other tables too which I handle within the DAL. Besides, I like a good challenge ;) – Connell May 16 '11 at 22:55

2 Answers2

1

It is completely impossible to call a virtual method (get_TableName) without an instance.

Instead, you can use an attribute on the class, and call typeof(T).GetCustomAttributes(typeof(YourAttribute), false)

SLaks
  • 868,454
  • 176
  • 1,908
  • 1,964
  • I know its impossible, I did mention that it obviously wasnt the way to do it in the question ;) But this is much better than your other answer. I'll give it a go, but I'm pretty sure it'll work. Thanks! – Connell May 16 '11 at 22:38
  • Note, by the way, that getting the attribute involves slow reflection. You may want to cache that in a static generic class. http://stackoverflow.com/questions/686630/static-generic-class-as-dictionary – SLaks May 16 '11 at 22:41
0

Based on updated information...

Why bother? Why not just use an abstract method (or, better, an interface) that defines the method "GetLinkedRow" where the concrete implementation can provide the specifics.

That is, you know that the linked row to Dog will always be a Person, so you have an interface (Warning: Not compiled!)

public interface ILinkedRow
{
    public TableRow GetLinkedRow();
}

Then Dog has this implementation:

public class Dog : TableRow, ILinkedRow
{
    //Implementation here

    public TableRow GetLinkedRow()
    {
        Person p = //implement method
        return p
    }
}

Your handling code can either ignore the fact that the returned row is a Person (if it never needs to know that), or cast it to (Person)myDog.GetLinkedRow();

AllenG
  • 8,112
  • 29
  • 40