In the general sense you're talking about converting the schema/metadata of the database to a different form. Most RDBMS expose this data as tables. For example this is a query I used to use to get all of the column data, clean it (like removing underscores), and transform it to .net types and statements:
with metadata as
(
select
utc.table_name,
utc.column_name,
replace(initcap(replace( lower(utc.column_name) ,'_',' ')),' ','') as column_name_clean,
initcap(replace( lower(utc.column_name) ,'_',' ')) as column_name_space,
rtrim(substr(utc.column_name,1,26),'_') as column_name_26,
case utc.data_type
when 'DATE' then 'DateTime'
when 'VARCHAR2' then 'String'
when 'CLOB' then 'String'
when 'NUMBER' then
case when utc.data_scale=0 then
case
when utc.data_precision = 19 then 'Int64'
when utc.data_precision = 9 then 'Int32'
when utc.data_precision = 4 then 'Int16'
when utc.data_precision = 1 then 'Boolean'
else 'Int'|| utc.data_precision end
else 'Decimal' end
when 'CHAR' then
case when utc.data_length = 1 then 'Char'
else 'String' end
else '' end as clr_data_type,
case utc.data_type
when 'DATE' then 'DateTime'
when 'VARCHAR2' then 'Text'
when 'CLOB' then 'MultilineText'
when 'CHAR' then 'Text'
else '' end as mvc_data_type,
case utc.data_type
when 'DATE' then 'Date'
when 'TIMESTAMP' then 'TimeStamp'
when 'VARCHAR2' then 'Varchar2'
when 'CLOB' then 'Clob'
when 'NUMBER' then
case when utc.data_scale=0 then
case
when utc.data_precision = 19 then 'Int64'
when utc.data_precision = 9 then 'Int32'
when utc.data_precision = 4 then 'Int16'
when utc.data_precision = 1 then 'Decimal-Boolean'
else 'Int'|| utc.data_precision end
else 'Decimal' end
when 'CHAR' then 'Char'
else '' end as odp_data_type,
utc.Data_Type as native_data_type,
case when utc.data_type = 'VARCHAR2' or utc.data_type='CHAR' then Data_Length
else null end as mvc_data_range,
utc.data_length,
utc.data_precision,
utc.data_scale,
utc.nullable,
case
when utc.data_scale > 0 then
'^\d{' || (nvl(utc.data_precision,1)-nvl(utc.data_scale,0)) || '}(\.\d{' || nvl(utc.data_scale,0) || '})?$'
else '' end as validation_reg_ex,
'n.' || trim(rpad(' ', nvl(utc.data_scale,0), 'd')) as validation_format,
case utc.data_type
when 'NUMBER' then
case when utc.data_scale=0 then utc.data_precision
else utc.data_precision + 1 end -- +1 for the decimal
else
utc.data_length end as max_string_length,
case ac.constraint_type when 'P' then 'Y' else 'N' end as PRIMARY_KEY,
ac.constraint_type
from user_tab_columns utc
left join all_cons_columns acc
join all_constraints ac on ac.constraint_name = acc.constraint_name and ac.owner = acc.owner and ac.constraint_type='P'
on utc.column_name=acc.column_name and utc.table_name=acc.table_name
where utc.table_name not like 'BIN%'
order by utc.table_name, utc.column_id
)
select 'public ' || clr_data_type || ' ' || column_name_clean || ' {get; set;}' as ClassProperty, m.*
from metadata m;
You'll note the first column, concatenates some of these fields together to form class properties:
In the past, I've added columns to generate everything from OracleCommand parameters, to inputs for the web. You can then take that farther with some sort of templating mechanism to generate whole classes, including data access methods, etc. T4 is an option but I used CodeSmith's Generator product.
While object-relational mappers like Entities or NHibernate, do a lot of this generation too, I don't appreciate their other purpose, which is to abstract the actual movement of data from database to middle tiers - the query languages they use are just too weak/convoluted to me. I prefer to stick with sql itself and maybe a micro-orm like dapper - of course i use the same techniques above to generate the sql for CRUD operations as well :).
