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.

How to upload multiple records in C# and stored procedure without looping

Recently I come across a question regarding how to save multiple records in sql server using C# and without using any loop.

Let’s take a simple case of saving list of persons. Let’s have the following class as person-

public class Person {
        public string PFirstName { get; set; }
        public string PLastName { get; set; }
        public int PAge { get; set; }
    }

Then let’s create a simple data base table called Person as below-

CREATE TABLE [dbo].[Person](
	[FirstName] [varchar](100) NULL,
	[LastName] [varchar](100) NULL,
	[Age] [int] NULL
) ON [PRIMARY] 

Now we can solve this problem by passing XML data as input to a stored procedure and using SQL XML for parsing data and saving to database.

So, let’s first create a list of persons for this example like below-

 List personList =new List(){
                new Person(){ PFirstName="abc", PLastName= "smit", PAge=32},
                new Person(){ PFirstName="bcd", PLastName= "pal", PAge=32}
            };
 

Now let’s parse this list to XML for saving and put it into a string variable-

 string SProc = "dbo.save_person_bulk";
            string ConnectonString = @"Data Source=(local);Initial Catalog=sample;Integrated Security=True;";
            using (SqlConnection sqlConnection = new SqlConnection(ConnectonString))
            {
                sqlConnection.Open();

                using (SqlCommand sqlCommand = new SqlCommand(SProc, sqlConnection))
                {
                    sqlCommand.CommandType = CommandType.StoredProcedure;
                    sqlCommand.Parameters.Add(new SqlParameter("@person_data", SqlDbType.VarChar)
                          {
                              Value = xmlPersonData
                          });
                    using (DataTable dataTable = new DataTable())
                    {
                        using (SqlDataAdapter sqlDataAdapter = new SqlDataAdapter())
                        {
                            sqlDataAdapter.SelectCommand = sqlCommand;
                            sqlDataAdapter.Fill(dataTable);
                        }
                    }
                }
            }

On doing the list parsing the resulting XML will look like this-

 CREATE PROCEDURE [dbo].[save_person_bulk]   
 @person_data xml  
AS  
BEGIN  
 SET NOCOUNT ON;  
 
 INSERT INTO [dbo].[person]  (FirstName, LastName, Age)    
           select  
           e.value('(PFirstName/text())[1]', 'varchar(100)')   
           ,e.value('(PLastName/text())[1]', 'varchar(100)')   
           ,e.value('(PAge/text())[1]', 'int')   
      FROM  
            @person_data.nodes('//ArrayOfPerson/Person') pdata(e)  
END

Looking at the generated XML and the stored procedure the code is self explanatory.