Today I was designing my application and I need to write about 30 classes as the business object layer. As per my design, the business objects in my application are exact replica of my databases table. In order to save time in writing these classes I came up with a tricky logic using SQL server query .I thought of putting in this; in my blog, as it may help some others who needs to do a same exercises.
For your reference please find below a sample of my table ('CountryMaster') in databases
select 'public '+
case when system_type_id=56 then ' int '+sys.columns.name+' { get; set; }'
when system_type_id=167 then ' string '+sys.columns.name+' { get; set; }'
when system_type_id=239 then ' string '+sys.columns.name+' { get; set; }'
when system_type_id=231 then ' string '+sys.columns.name+' { get; set; }'
when system_type_id=104 then ' string '+sys.columns.name+' { get; set; }'
when system_type_id=61 then ' DateTime '+sys.columns.name+' { get; set; }'
when system_type_id=108 then ' double '+sys.columns.name+' { get; set; }'
when system_type_id=175 then ' string '+sys.columns.name+' { get; set; }'
when system_type_id=34 then ' byte[] '+sys.columns.name+' { get; set; }'
when system_type_id=99 then ' string '+sys.columns.name+' { get; set; }'
end AS 'Class Properties'
from sys.columns
inner join sys.tables on sys.tables.name='CountryMaster'
where sys.columns.object_id=sys.tables.object_id
On running the above query will be getting the entry for my business object as below.
Note: the above query may not be complete for all SQL data types, If your databases is having more data types columns, the please add the appropriate c# equal ant code in the case statements.
copy the results from SQL and paste in your C#.net projects in the corresponding class file as below.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
///
/// Business Objects Class
///
For your reference please find below a sample of my table ('CountryMaster') in databases
Ignored to write the C# business objects, I used the below query which will gives me the property entry’s for my business objects class.
select 'public '+
case when system_type_id=56 then ' int '+sys.columns.name+' { get; set; }'
when system_type_id=167 then ' string '+sys.columns.name+' { get; set; }'
when system_type_id=239 then ' string '+sys.columns.name+' { get; set; }'
when system_type_id=231 then ' string '+sys.columns.name+' { get; set; }'
when system_type_id=104 then ' string '+sys.columns.name+' { get; set; }'
when system_type_id=61 then ' DateTime '+sys.columns.name+' { get; set; }'
when system_type_id=108 then ' double '+sys.columns.name+' { get; set; }'
when system_type_id=175 then ' string '+sys.columns.name+' { get; set; }'
when system_type_id=34 then ' byte[] '+sys.columns.name+' { get; set; }'
when system_type_id=99 then ' string '+sys.columns.name+' { get; set; }'
end AS 'Class Properties'
from sys.columns
inner join sys.tables on sys.tables.name='CountryMaster'
where sys.columns.object_id=sys.tables.object_id
On running the above query will be getting the entry for my business object as below.
Note: the above query may not be complete for all SQL data types, If your databases is having more data types columns, the please add the appropriate c# equal ant code in the case statements.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
///
/// Business Objects Class
///
///
///
namespace FiesPackage.Country.BusinessObjects
{
public class Country
{
public int CountryId { get; set; }
public string CountryCode { get; set; }
public string CountryName { get; set; }
public string InBuilt { get; set; }
public int CompanyId { get; set; }
public string Misc { get; set; }
}
}
The highlighted portion has been pasted from the SQL result using the above query.
