Simple SQL Server Script For Generating NHibernate Classes/Mappings

With the growing popularity of Fluent NHibernate, mapping files are being used less and less. However if you are using HBM/XML mapping files I recently dug up an old SQL script (originally written by my colleague Alan Lai, since modified slightly by myself) which might help.  The SQL Script basically generates some mapping/public property fields from an existing database that you can manually copy around into you application to save you some keystrokes.

Disclaimer: While helpful, this script is pretty simple and only handles basic properties (no keys, relationships, etc).  Still, I think it’s pretty cool and it would be selfish of me not to share it with the NHibernate world :)

 

The SQL Script

Basically the script uses the sysobjects, syscolumns, and systypes tables to inspect a database and generate a row for each table/column.  Take a look at the entire script:

SELECT table_name=sysobjects.name,
column_name=syscolumns.name,
'<property name="' + syscolumns.name + '" column="' + syscolumns.name + '" />' as mapping,
'public virtual ' +
cast (case
when systypes.name in ('varchar', 'nvarchar') then 'string'
when systypes.name = 'bit' then 'bool'
when systypes.name = 'datetime' then 'DateTime'
else systypes.name
end as varchar(10)) + ' ' + syscolumns.name + ' { get; set; }' as property,
datatype=systypes.name,
length=syscolumns.length
FROM sysobjects
JOIN syscolumns ON sysobjects.id = syscolumns.id
JOIN systypes ON syscolumns.xtype=systypes.xtype
WHERE sysobjects.xtype='U' AND systypes.name <> 'sysname'
ORDER BY sysobjects.name,syscolumns.colid

 

The Results

Running the script on the beloved Northwind database generates 133 rows, a few of which I will reproduce below:

image

Now you can open up Customers.hbm.xml and copy in the properties under the mapping column, and in Customers.cs you can do the same using the values in the property column.

Hopefully this will save you some typing if you are using NHibernate mapping files and starting from an existing database.  Enjoy!

19 Comments

  • Same Oracle script may be interesting:
    /*CREATE OR REPLACE FUNCTION MakePascalCase(srcName IN VARCHAR2) RETURN VARCHAR2 IS
    RES VARCHAR2(1000);
    BEGIN
    res := REPLACE(Upper(srcName),'F_','IS_');
    res := INITCAP(REPLACE(res,'_',' '));
    res := REPLACE(res,' Id',' ID');
    res := REPLACE(res,' ' ,'');
    RETURN res;
    END;
    */

    SELECT sysobjects.OBJECT_NAME table_name ,
    syscolumns.COLUMN_NAME column_name,
    MakePascalCase(syscolumns.COLUMN_NAME) Prop,
    '' AS mapping,
    'public virtual ' ||
    (CASE
    when syscolumns.DATA_TYPE IN ('VARCHAR', 'VARCHAR2','CHAR') THEN 'String'
    WHEN syscolumns.DATA_TYPE = 'NUMBER' THEN
    CASE
    WHEN NVL(syscolumns.DATA_SCALE,0) >0 THEN 'Double'
    WHEN NVL(syscolumns.DATA_SCALE,0) = 0 AND syscolumns.DATA_PRECISION =1 THEN 'Boolean'
    WHEN NVL(syscolumns.DATA_SCALE,0) = 0 AND syscolumns.DATA_PRECISION 10 THEN 'Int64'
    END
    WHEN syscolumns.DATA_TYPE = 'DATE' THEN 'DateTime'
    ELSE 'UnknownType'
    END)
    ||
    CASE
    when syscolumns.NULLABLE = 'Y' THEN '?'
    ELSE ''
    END
    || ' ' || MakePascalCase(syscolumns.COLUMN_NAME) ||
    ' { get; set; }' AS property,
    syscolumns.DATA_TYPE datatype,
    syscolumns.DATA_PRECISION,
    syscolumns.DATA_LENGTH LENGTH
    from user_objects sysobjects
    join user_tab_columns syscolumns ON sysobjects.OBJECT_NAME = syscolumns.TABLE_NAME
    where sysobjects.object_type = 'TABLE' AND OBJECT_NAME=:TABLEname
    ORDER BY sysobjects.OBJECT_NAME, syscolumns.COLUMN_ID

  • thanks for sharing such valuable information in regards of Generating NHibernate Classes/Mappings, hopefully it will be beneficial to others also....

  • interesting stuff, but why fo databases have to be so complex!

  • SQL is something I am trying to self teach.Practical examples like this help a lot. Thanks!

  • Hello! Thanks for sharing such valuable information in regards of Generating NHibernate Classes/Mappings, hopefully it will be beneficial to others also....

  • This design is steller! You definitely know how to keep a reader amused. Between your wit and your videos, I was almost moved to start my own blog (well, almost...HaHa!) Fantastic job. I really enjoyed what you had to say, and more than that, how you presented it. Too cool!

  • I prefer this course because it is a good neverwinter gold i truly constantly supply.

  • I actually have Ranaud's disease which are my very own hands and feet simply turn light blue not to mention reduce to look at become particular...which is often. Since i have experienced my very own innovative Buy Mulberry I've not used them all off of! Little numbness with my feet at least! Now i'm sooooo thrilled! Only could afford the idea, I would have a garage loaded with Buy Mulberry

  • I truly appreciate this post. I¡¦ve been looking all over for this! Thank goodness I found it on Bing. You have made my day! Thanks again

  • This is really interesting, You're a very skilled blogger. I've joined your feed and look forward to seeking more of your excellent post. Also, I've shared your website in my social networks!

  • That i deliver d3 gold year round it doesn't matter how heated its out-of-doors. All aspects are great regarding d3 gold.

  • Make yourself Indispensable at Work

  • Authentic! Where to buy LV cheapest? If you want to buy the cheapest LV genuine. LV Country of Origin France is well deserved.

  • You can find one to go with every look.

  • Of course once without the occasional. Let's just called. I've been buried alive I've been married six times I burned on a boat how's that sort of killed my Brothers.

  • If you want to rock the hobo look, there are some things you should know.

  • You completed several good points there. I did a search on the topic and found most persons will have the same opinion with your blog.

  • Simple SQL Server Script For Generating NHibernate Classes/Mappings - Scott's Blog epcoyut sac longchamp

  • Simple SQL Server Script For Generating NHibernate Classes/Mappings - Scott's Blog gcbyqwh burberry

Comments have been disabled for this content.