Generating a Stored Procedure Wrapper for NHibernate in PL/SQL

I discussed the execution of Oracle stored procedures via NHibernate in a previous post, and also the generation of the NHibernate .hbm.xml mapping files for Stored Procedures.

We use the same cursor declaration as in my post on generating the .hbm.xml file, and also the same parameter type conversion logic:

  csdatatype varchar2(50);
  args varchar2(500);

begin
  dbms_output.enable(1000000);
  dbms_output.put_line('using System.Collections;');
  dbms_output.put_line('using NHibernate;');
  dbms_output.put_line('');
  dbms_output.put_line('namespace MyDAL');
  dbms_output.put_line('{');
  dbms_output.put_line('    public static class Procedures');
  dbms_output.put_line('    {');

  for obj in cObjects loop
    args := '';

    for col in cColumns(obj.object_name) loop
      csdatatype := col.data_type;

      case col.data_type
        when 'NVARCHAR2' then
          csdatatype := 'string';
        when 'VARCHAR2' then
          csdatatype := 'string';
        when 'CHAR' then
          if col.char_length=1 then
            csdatatype := 'bool';
          else
            csdatatype := 'string';
          end if;
        when 'NUMBER' then
          if 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;

      args := args || ', ' || csdatatype || ' ' || col.argument_name;
    end loop;

After concatenating the parameter list for the C# method, we create an IQuery object, add parameters using the SetParameter<>() method, and return the result as an IList:

    dbms_output.put_line('        public static IList ' ||
      replace(initcap(obj.object_name), '_', '')
        || '(ISession session' || args || ')');
    dbms_output.put_line('        {');
    dbms_output.put_line('            IQuery sp = session.GetNamedQuery("'
        || replace(initcap(obj.object_name), '_', '') || '");');

    for col in cColumns(obj.object_name) loop
      csdatatype := col.data_type;

      case col.data_type
        when 'NVARCHAR2' then
          csdatatype := 'string';
        when 'VARCHAR2' then
          csdatatype := 'string';
        when 'CHAR' then
          if col.char_length=1 then
            csdatatype := 'bool';
          else
            csdatatype := 'string';
          end if;
        when 'NUMBER' then
          if 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('            sp.SetParameter<'
          || csdatatype || '>("' ||
        replace(initcap(col.argument_name), '_', '') || '", ' ||
        replace(initcap(col.argument_name), '_', '') || ');');
    end loop;

    dbms_output.put_line('            return sp.List();');
    dbms_output.put_line('        }');
  end loop;

  dbms_output.put_line('    }');
  dbms_output.put_line('}');

The IQuery object references the generated HBM declaration, and executes the PL/SQL code behind the sql-query statement

BEGIN MyStoredProc(params); END;

About these ads

One Response to Generating a Stored Procedure Wrapper for NHibernate in PL/SQL

  1. Ecko says:

    nice share,,, thanks

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 65 other followers

%d bloggers like this: