Developing the wrapper classes is one of the complex task for database driven application, which deals with large number tables with many columns.
This procedure makes you to generate complete model of C# class file corresponding to the table, which we have passed as a parameter.
Examples: exec prc_generate_c#_classbuilder_table @tablename = 'employeemaster'
- Alter procedure prc_generate_c#_classbuilder_table
- @tablename sysname
- as
- begin
-
- set nocount on
-
- declare @tbl_classbuilder table (code varchar(max))
-
- /*step 1 : define class name*/
- insert into @tbl_classbuilder
- select 'public class ' + @tablename + char(13) + char(10) + '{'
-
- /*step 2 : define class constructor*/
- insert into @tbl_classbuilder
- select char(13) + char(10) + '#region constructors' + char(13) + char(10)
-
- insert into @tbl_classbuilder
- select char(9) + 'public ' + @tablename + '()'
- + char(13) + char(10) + char(9) + '{'
- + char(13) + char(10) + char(9) + '}'
-
- insert into @tbl_classbuilder
- select '#endregion' + char(13) + char(10)
-
- /*step 3 : define private fields*/
- insert into @tbl_classbuilder
- select char(13) + char(10) + '#region private fields' + char(13) + char(10)
-
- insert into @tbl_classbuilder
- select char(9) + 'private ' +
- case
- when data_type like '%char%' then 'string '
- when data_type like '%int%' then 'int '
- when data_type like '%datetime%' then 'datetime '
- when data_type like '%binary%' then 'byte[] '
- when data_type = 'bit' then 'bool '
- when data_type like '%text%' then 'string '
- when data_type = 'money' then 'double '
- when data_type = 'float' then 'double '
- else 'object '
- end + '_' + column_name + ';' + char(9)
- from information_schema.columns
- where table_name = @tablename
- order by ordinal_position
-
- /*step 4 : define public properties*/
- insert into @tbl_classbuilder
- select '#endregion' +
- char(13) + char(10)
-
- insert into @tbl_classbuilder
- select char(13) + char(10) + '#region public properties' + char(13) + char(10)
-
- insert into @tbl_classbuilder
- select char(9) + 'public ' +
- case
- when data_type like '%char%' then 'string '
- when data_type like '%int%' then 'int '
- when data_type like '%datetime%' then 'datetime '
- when data_type like '%binary%' then 'byte[] '
- when data_type = 'bit' then 'bool '
- when data_type like '%text%' then 'string '
- when data_type = 'money' then 'double '
- when data_type = 'float' then 'double '
- else 'object '
- end + column_name +
- char(13) + char(10) + char(9) + '{' +
- char(13) + char(10) + char(9) + char(9) +
- 'get { return _' + column_name + '; }' +
- char(13) + char(10) + char(9) + char(9) +
- 'set { _' + column_name + ' = value; }' +
- char(13) + char(10) + char(9) + '}'
- from information_schema.columns
- where table_name = @tablename
- order by ordinal_position
-
- insert into @tbl_classbuilder
- select '#endregion' +
- char(13) + char(10) + '}'
-
- /*step 5 : complete model class for the particular table*/
- select code from @tbl_classbuilder
-
- set nocount off
- end