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 '}'
devioblog 