Accessing derived class properties in the base class public method and creating dynamic queries

There are many ORM models that work on Object Relation Mapping. It generates queries on the fly based on the XML config file and reflection.

This post is intended to explain a simple scenario of how we can write a method like on a base class say ”save”, and any class that is derived from this base class should automatically call the save method, create a dynamic query and save the data on database.

Now let’s take a simple class named Person that has only two fields as below-

public class Person {
    public string FirstName { get; set; }
    public string LastName { get; set; }
}

And on doing the following code it should take the value from the instance “p”, create a dynamic query and save the data to database-

Person p = new Person() { FirstName="John", LastName="Smit" };
        p.save();

We can follow few steps for doing that.

Step 1 : Let’s create an class label attribute that will carry the name of the table for saving data in the database. It goes like this-

[System.AttributeUsage(System.AttributeTargets.Class )]
public class TableName : System.Attribute
{
    public string name;
    public TableName() 
    {
    }
    public TableName(string name)
    {
        this.name = name;
    }
}

Step 2 : is to create a base class that will implement a public method “save”. Ideally we should make this class as generic class as we need to access the property and the value of the properties. So, we can have this class as –

public class DBObjectwhere T:class { 
    public void save(){

        //implementation goes here
            
    }
}

So the inheritance will be below. Here we have added TableName attribute and tblPerson is the database table name-

[TableName(name="tblPerson")]
public class Person : DBObject {
    public string FirstName { get; set; }
    public string LastName { get; set; }
}

Step 3 : What we need to do in this step is five things.

1. Get the name of the table
2. Create comma separated table field name
3. Create comma separated table field value to insert
4. Create the query
5. Do database call 

Step 3-1: We are passing type information to DBObject so we can get custom attribute(TableName) value through T like below-

 (typeof(T).GetCustomAttributes(false).FirstOrDefault() as TableName).Name

Step 3-2: We can get comma separated field names from T using reflection like below-

string.Join(",", (from p in typeof(T).GetProperties().AsEnumerable()
                                   select p.Name).ToArray());

Step 3-3: We can get comma separated field values from T using reflection like below-

string.Join(",", (from p in typeof(T).GetProperties().AsEnumerable()
                                   select "'"+ p.GetValue(this,null)+"'").ToArray());

Step 3-4: We can create final query as following-

string.Format("insert into {0} ({1}) values ({2})  ", attr.name, fields, values);

So the complete save method will look like below-

    public void save(){
        var attr = typeof(T).GetCustomAttributes(false).FirstOrDefault() as TableName;
        string fields, values;
        fields = string.Join(",", (from p in typeof(T).GetProperties().AsEnumerable()
                                   select p.Name).ToArray());
        values = string.Join(",", (from p in typeof(T).GetProperties().AsEnumerable()
                                   select "'"+ p.GetValue(this,null)+"'").ToArray());
        string query = string.Format("insert into {0} ({1}) values ({2})  ", attr.name, fields, values);
    }

Step 3-5: Variable query will finally result the following string for this example-

insert into tblPerson (FirstName,LastName) values ('John','Smit')  

This is the query we are looking for. I am not completing the data base call part.