Generating Documentation of Entity Framework edmx Files

The upcoming version 1.02 of dbscript supports uploading, versioning and documenting Entity Framework Model Files (extension .edmx).

I have described earlier how to write XSLT files to convert the contents of edmx files into MediaWiki markup.

Follow these links to view sample output generated by dbscript:

• List of table mappings (including links to table and view documentation generated by dbscript)
• List of table/column mappings
• generated C# Entity Names

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;

Generating a Stored Procedure Declarations for NHibernate in PL/SQL

I discussed the execution of Oracle stored procedures via NHibernate in a previous post, and the promise to be continued will now be fulfilled with the generation of the NHibernate mapping file for Stored Procedures.

We need two cursors, one iterating through all procedures, the second one to iterate through the parameter list of a given procedure:

cursor cObjects is
select object_type, object_name
from all_objects
where object_type in ('PROCEDURE')
and [your criteria here]
order by 1, 2;

cursor cColumns(oname in varchar2) is
select p.position, p.argument_name, p.data_type, p.default_length,
p.in_out, p.data_length, p.data_precision, p.data_scale, p.char_length
from all_arguments p
where p.object_name = oname
and [your criteria here]
order by p.position;

We need an XML header, and an sql-query element for each stored procedure:

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

begin
  dbms_output.enable(1000000);
  dbms_output.put_line('<?xml version="1.0" encoding="utf-8"?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
                   assembly="protodal" namespace="protodal">');

  for obj in cObjects loop
    dbms_output.put_line('  <sql-query name="' ||
      replace(initcap(obj.object_name), '_', '') || '" callable="true">');

    args := '';

The most “difficult” part of the generator is to translate the PL/SQL data types into C#/.Net data types. This can be achieved in a CASE statement:

    for col in cColumns(obj.object_name) loop
      if (args is null) then
        args := '(:' || col.argument_name;
      else
        args := args || ', :' || col.argument_name;
      end if;

      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('    <query-param name="' || col.argument_name ||
        '" type="' || csdatatype || '" />');
    end loop;

Args contains the list of parameters for each stored procedure, invoking it inside a BEGIN … END; clause:

    if (args is not null) then
      args := args || ')';
    end if;

    dbms_output.put_line('begin ' || obj.object_name || args || '; end;');
    dbms_output.put_line('  </sql-query>');
  end loop;

  dbms_output.put_line('</hibernate-mapping>');
end;

xxx

ASP.Net Postbacks in iframe

One of my projects contains a couple of summary pages which take some time to collect their data. Originally, the ASPX page contained a set of GridViews displaying the result of different database queries, and I wanted to make the page more responsive by moving the GridViews to separate aspx files using iframes.

While this worked in principle, it did not work out in the details: paging and sorting via postback would always generate a full postback to the parent page. A question on Stack Overflow tells me I am not the only one with the problem.

Search the intertubes resulted in a project called UFRAME, which is hosted on codeplex:

UFrame combines the goodness of UpdatePanel and IFRAME in a cross browser and cross platform solution.

Instead of an iframe, the developer creates div’s with a non-standard src attribute. The UFRAME library scans through all these div’s, retrieves the HTML contents of the referenced ASP.NET URL, and merges its controls into the div using a bit of JavaScript magic. A detailed description can be found on the author’s codeproject page.

I needed to fix two problems, though:

The HTML parser throws an exception with pages containing a <meta http-equiv> attribute. This can be fixed by editing the regular expressions in htmlparser.js and replacing \w+ with \w+(-\w+)? in the startTag and attr regexes.

Next, UFRAME does not handle link targets: Every link is redirected to load the referenced URL into the containing div using the UFrameManager.loadHtml() function.

I changed the .click() function by replacing the call to loadHtml() with “return true;”, so that the links would open in the window containing the div.

graspx 0.16

My application graspx, which allows developers to search and analyze ASPX source files by its XML structure, now recognizes (and skips) comments of the form

<%– comment… –%>

The latest version of graspx is available for download here.

T-SQL Syntax Error due to Compatibility Level

My application dbscript queries the system catalog views to retrieve a database schema, and uses different SELECT statements for SQL Server 2000 and for 2005 and higher. To my surprise, it recently raised a syntax error when reading from a 2008 database:

Msg 102, Level 15, State 1, Line 4
Incorrect syntax near ‘sys’.

The statements typically look like this

SELECT    sys.xml_schema_collections.xml_collection_id,
  sys.schemas.name as schema_name,
  sys.xml_schema_collections.name, ep.value as Description
FROM    sys.xml_schema_collections
INNER JOIN sys.schemas
  ON sys.schemas.schema_id = sys.xml_schema_collections.schema_id
OUTER APPLY fn_listextendedproperty('MS_Description',
  'SCHEMA', sys.schemas.name,
  'XML SCHEMA COLLECTION', sys.xml_schema_collections.name, null, null) ep
WHERE    xml_collection_id > 1
ORDER BY sys.xml_schema_collections.name

Note the OUTER APPLY invocation of the table-valued function fn_listextendedproperty().

A quick search led me to this thread suggesting that the compatibility level of the database may not be the most current (100 for SQL Server 2008). I checked, and indeed compatibility level was 80 as a result of restoring a SQL Server 2000 database. Setting the compatibility level to 100 fixed the problem.

Personally, I find the compatibility level a bit inconsistent (see descriptions for sp_dbcmptlevel and ALTER DATABASE).

T-SQL 2000 simply did not have an OUTER APPLY, nor a fn_listextendedproperty() (without preceding ::), nor the sys schema.

Its description

Sets certain database behaviors to be compatible with the specified version of SQL Server

does not mention APPLYs or table-valued functions. And it’s not clear why exactly a parameter to a TVF is causing the syntax error. I guess that’s outside the scope of “certain behaviors”.

Lesson learned: when restoring a database from a different server version, check the compatibility levels

Here’s the statement to do so:

select name, compatibility_level
from sys.databases