Create your Business Object using SQL Query?

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

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.

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
///

/// Rajesh Kamalakshan
/// 16-12-2011
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.

Type initializer for threw an exception


I thought of sharing this information to others as  it may help someone having the same issues .I have an application which is done in vb .net . It works fine when run from source. This application is a dll bases and it uses crystal report. Everything worked fine with the source.

But the real problem started when I compiled the source to exe and dll .In the compiled version whenever a dll in this application is called form an exe it was throwing an exception as “type initializer for threw an exception”, I googled a lot and found that this issues is due to missing of crystal report files in the clinet machine or Live Pc.

Note: This error can also happed due to some missing files, in my case it was Crystal reports’s supporting files. You can get more details about the missing reference from the stack trace which will be available in the exception.

The solution for this issue was to download and apply the crystal Report redistributable pack, which is relevant for your OS platform where the Application is running. We can get these packs from the below site of crystal report.

http://resources.businessobjects.com/support/additional_downloads/runtime.asp#09

Since my application was compiled in VS 2008, i downloaded the Crystal Reports Basic for Visual Studio .NET 2008

As per the version of crystal report used in your application ,you have to download the respective redistributable package.