Data access classes for use in NHibernate take the form
public class MyFoo { public virtual int MyBar { get; set; } ... }
i.e. they are public classes and their member variables are declared public virtual with get and set accessors.
Due to this regular pattern and the fact that tables and columns can easily be retrieved in Oracle using the DBA_, ALL_, or USER_ views, we can thus write a stored procedure to generate the data access classes. The stored procedure can then be invoked during the build process to reflect changes in the data model
First, define cursors for tables and views and their columns:
create or replace procedure dev_generate_dal_classes is csdatatype varchar2(50); csname varchar2(50); csaccessor varchar2(50); cursor cObjects is select object_type, object_name from all_objects where owner = 'MYUSER' and object_type in ('TABLE', 'VIEW') order by 1, 2; cursor cColumns(oname in varchar2) is select tc.column_name, tc.data_type, tc.char_length, tc.data_length, tc.data_precision, tc.data_scale, tc.nullable, p.table_name from all_tab_columns tc left outer join all_cons_columns cc inner join all_constraints c on c.owner = cc.owner and c.constraint_name = cc.constraint_name and c.constraint_type = 'R' inner join all_constraints p on c.r_owner = p.owner and c.r_constraint_name = p.constraint_name on tc.owner = cc.owner and tc.table_name = cc.table_name and tc.column_name = cc.column_name where tc.owner = 'MYUSER' and tc.table_name = oname order by tc.column_id;
Next, we loop through all tables and views (outer cursor):
begin dbms_output.enable(1000000); for obj in cObjects loop dbms_output.put_line('public partial class ' || replace(initcap(obj.object_name), '_', '')); dbms_output.put_line('{'); for col in cColumns(obj.object_name) loop csname := replace(initcap(col.column_name), '_', ''); csdatatype := col.data_type; csaccessor := '{ get; set; }';
For the inner loop we use a parameterized cursor. Each column needs to have its data type mapped onto a C# data type:
case col.data_type when 'NVARCHAR2' then csdatatype := 'string'; when 'VARCHAR2' then csdatatype := 'string'; when 'NUMBER' then if col.column_name = 'OID' then csdatatype := 'long'; csaccessor := '{ get; private set; }'; elsif col.column_name like '%\_OID' escape '\' and col.table_name is not null then csdatatype := replace(initcap(col.table_name), '_', ''); csname := replace(initcap(replace(col.column_name, '_OID', '')), '_', ''); elsif col.data_precision is null then csdatatype := 'long'; elsif col.data_precision < 10 then csdatatype := 'int'; else csdatatype := 'long'; end if; else csdatatype := col.data_type; end case; dbms_output.put_line(' public virtual ' || csdatatype || ' ' || csname || ' ' || csaccessor); end loop; dbms_output.put_line('}'); end loop; end;
Restrictions:
- The example only shows string and int data type conversion.
- Primary key columns are always named OID, and foreign key columns end in _OID.
- Table and column names are generated calling INITCAP() and then removing underscores.
Pingback: Generating NHibernate ClassMap in PL/SQL « devioblog
Pingback: informatica question? « El Notas – Tu Portal Web!
thanks for sharing
Pingback: Generating NHibernate Classes for MSSQL in TSQL « devioblog