Generating NHibernate Access to MSSQL Stored Procedures

This series describes the generation of C# classes and database access for NHibernate 2.1.2GA.

To store the information on which Stored Procedures to script out, we need a table Meta_Procedure, similar to Meta_Table presented earlier.

CREATE TABLE [dbo].[Meta_Procedure](
  [OID] [int] IDENTITY(1,1) NOT NULL,
  [ID] [nvarchar](50) NOT NULL,
  [UseDAL] [bit] NOT NULL,
  CONSTRAINT [PK_Meta_Procedure] PRIMARY KEY CLUSTERED
  ( [OID] ASC )
)

We fill this table from the list of stored procedures stored in sys.objects:

INSERT INTO Meta_Procedure (ID)
SELECT o.name
FROM sys.objects o
LEFT OUTER JOIN Meta_Procedure t ON o.name = t.id
WHERE o.type IN ('P')
AND t.OID IS NULL;

The developer now only needs to set the UseDAL field to true (1) for each procedure to be scripted.

Declaration of stored procedures for NHibernate consists of two parts: a .hbm.xml containing named queries with a TSQL statement to invoke each stored procedure, which is compiled as Embedded Resource:

CREATE PROCEDURE [dbo].[dev_Generate_DAL_HBM_XML] AS

  PRINT '<?xml version="1.0" encoding="utf-8"?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
    assembly="My.Project" namespace="My.Project.DAL">'
  
  DECLARE @oid INT, @id NVARCHAR(50)

  DECLARE cT CURSOR FOR
  SELECT object_id, name
  FROM sys.procedures p
  INNER JOIN Meta_Procedure mp ON p.name = mp.ID
  WHERE mp.UseDAL = 1
  ORDER BY name

  OPEN cT
  FETCH cT INTO @oid, @id

  WHILE @@FETCH_STATUS = 0 BEGIN
    DECLARE @cid NVARCHAR(50), @datatype NVARCHAR(50), 
      @isoutput BIT, @parameterid INT
    
    DECLARE cP cursor FOR
    SELECT  REPLACE(p.name, '@', ''), t.name, p.is_output, p.parameter_id
    FROM  sys.parameters p
    INNER JOIN sys.types t ON p.user_type_id = t.user_type_id
    WHERE   p.object_id = @oid
    ORDER BY p.parameter_id

    PRINT '    <sql-query name="' + @id + '" callable="true">'

    OPEN cP
    FETCH cP INTO @cid, @datatype, @isoutput, @parameterid

    WHILE @@FETCH_STATUS = 0 BEGIN
      IF @datatype = 'nvarchar' 
        SET @datatype = 'string'
      ELSE IF @datatype = 'date'
        SET @datatype = 'DateTime?'
      ELSE
        SET @datatype = @datatype + '?'

      PRINT '        <query-param name="' + @cid + '" type="' + @datatype + '" />'
      
      FETCH cP INTO @cid, @datatype, @isoutput, @parameterid
    END

    CLOSE cP

    PRINT 'begin'
    PRINT '    execute ' + @id 
    
    OPEN cP
    FETCH cP INTO @cid, @datatype, @isoutput, @parameterid

    WHILE @@FETCH_STATUS = 0 BEGIN
      IF @datatype = 'nvarchar' 
        SET @datatype = 'string'
      ELSE IF @datatype = 'date'
        SET @datatype = 'DateTime?'
      ELSE
        SET @datatype = @datatype + '?'

      IF @parameterid = 1
        PRINT '        :' + @cid
      ELSE BEGIN
        PRINT '        , :' + @cid
      END
      
      FETCH cP INTO @cid, @datatype, @isoutput, @parameterid
    END
    CLOSE cP

    DEALLOCATE cP
    
    PRINT 'end'
    PRINT '    </sql-query>'
    
    FETCH cT into @oid, @id
  END

  CLOSE cT
  DEALLOCATE cT

  PRINT '</hibernate-mapping>'

For each stored procedure, we need a C# method which calls the corresponding named query. The SP’s parameters are retrieved from the sys.parameters catalog view, and their data types translated just as we handled table columns.

CREATE PROCEDURE [dbo].[dev_Generate_DAL_Procedures] AS

  PRINT 'using System;'
  PRINT 'using System.Collections;'
  PRINT 'using NHibernate;'
  PRINT ''
  PRINT 'namespace My.Project.DAL'
  PRINT '{'
  PRINT '    public static class Procedures'
  PRINT '    {'
  
  DECLARE @oid INT, @id NVARCHAR(50)

  DECLARE cT CURSOR FOR
  SELECT object_id, name
  FROM sys.procedures p
  INNER JOIN Meta_Procedure mp ON p.name = mp.ID
  WHERE mp.UseDAL = 1
  ORDER BY name

  OPEN cT
  FETCH cT INTO @oid, @id

  WHILE @@FETCH_STATUS = 0 BEGIN
    DECLARE @cid NVARCHAR(50), @datatype NVARCHAR(50), @isoutput BIT
    
    DECLARE cP cursor FOR
    SELECT  REPLACE(p.name, '@', ''), t.name, p.is_output
    FROM  sys.parameters p
    INNER JOIN sys.types t ON p.user_type_id = t.user_type_id
    WHERE   p.object_id = @oid
    ORDER BY p.parameter_id

    PRINT '        public static IList ' + @id + '(ISession session'

    OPEN cP
    FETCH cP INTO @cid, @datatype, @isoutput

    WHILE @@FETCH_STATUS = 0 BEGIN
      IF @datatype = 'nvarchar' 
        SET @datatype = 'string'
      ELSE IF @datatype = 'date'
        SET @datatype = 'DateTime?'
      ELSE
        SET @datatype = @datatype + '?'

      PRINT '            , ' + 
        CASE WHEN @isoutput = 1 THEN 'out ' ELSE '' END + 
        @datatype + ' ' + @cid
      
      FETCH cP INTO @cid, @datatype, @isoutput
    END

    PRINT '            )'
    PRINT '        {'
    PRINT '            IQuery sp = session.GetNamedQuery("' + @id + '");'

    CLOSE cP

    OPEN cP
    FETCH cP INTO @cid, @datatype, @isoutput

    WHILE @@FETCH_STATUS = 0 BEGIN
      IF @datatype = 'nvarchar' 
        SET @datatype = 'string'
      ELSE IF @datatype = 'date'
        SET @datatype = 'DateTime?'
      ELSE
        SET @datatype = @datatype + '?'

      IF @datatype = 'string'
        PRINT '            sp.SetParameter<' + @datatype + 
          '>("' + @cid + '", ' + @cid + ');'
      ELSE BEGIN
        PRINT '            sp.SetParameter<' + @datatype + 
          '>("' + @cid + '", ' + @cid + ');'
      END
      
      FETCH cP INTO @cid, @datatype, @isoutput
    END
    CLOSE cP

    DEALLOCATE cP
    
    PRINT '            return sp.List();'
    PRINT '        }'
    
    FETCH cT INTO @oid, @id
  END

  CLOSE cT
  DEALLOCATE cT

  PRINT '    }'
  PRINT '}'