Introduction
Here is a small trick that can benefit a few of you who use nHibernate for creating entities in their application.
For creating the Entities to make it work with nHibernate, one needs to create entity class and mapping files. Most of the time developers have got to do this manually. But when there are too many columns in your datatables, this particular process of creating entity classes and mappings becomes very tedious. Therefore, it's worth giving this small trick a try that can save your precious time.
Step by Setp process
First we are going to create two macros that we will use for creating entity classes and nHibernate mappings.
Creating Macros
1) Open Visual Studio, go to macro explorer (Alt-F8 or Tools-Macros->Macro Explorer)
2) Right click Macros, select "New Macro Project", It will ask you to save the macro project to some physical location, give it some name e.g. MyUtilities
3) Use double click on the Module1 to open it in Macro Editor
4) Copy-paste following code in the Mcaro Editor, save and close it.
Imports System
Imports EnvDTE
Imports EnvDTE80
Imports EnvDTE90
Imports System.Diagnostics
Imports System.IO
Imports System.Text
Imports System.Text.RegularExpressions
Public Module Module1
Sub CreateHBMFromSqlTypes()
Dim selection As TextSelection = DTE.ActiveDocument.Selection()
Dim selectedText = selection.Text
selection.Insert(_GetHBMPropertyStrings(selectedText))
End Sub
Sub CreatePropertiesFromSqlTypes()
Dim selection As TextSelection = DTE.ActiveDocument.Selection()
Dim selectedText = selection.Text
selection.Insert(_GetPropertyStrings(selectedText))
End Sub
Private Function _GetHBMPropertyStrings(ByVal input As String) As String
Try
Dim _sbPrivateVars As StringBuilder = New StringBuilder()
Dim _sbProperties As StringBuilder = New StringBuilder()
Dim _line As String
Dim _strReader As StringReader = New StringReader(input)
While True
_line = _strReader.ReadLine()
If _line Is Nothing Then
Exit While
Else
_line = _line.Trim()
Dim wordArr As String() = Regex.Split(_line, "\W+")
Dim strpropline As String = String.Format("<property name=""{0}"" column=""{0}"" />", wordArr(0))
_sbProperties.Append(strpropline).Append(Environment.NewLine)
End If
End While
_GetHBMPropertyStrings = _sbProperties.ToString() & vbCrLf
Catch errorVariable As Exception
MsgBox(errorVariable.ToString())
End Try
End Function
Private Function _GetPropertyStrings(ByVal input As String) As String
Try
Dim _sbPrivateVars As StringBuilder = New StringBuilder()
Dim _sbProperties As StringBuilder = New StringBuilder()
Dim _line As String
Dim _strReader As StringReader = New StringReader(input)
While True
_line = _strReader.ReadLine()
If _line Is Nothing Then
Exit While
Else
_line = _line.Trim()
Dim wordArr As String() = Regex.Split(_line, "\W+")
Dim varType As String = _GetCorrespondingCLRType(wordArr(1))
Dim propText As String() = _GetPropertyText(wordArr(0), varType)
_sbPrivateVars = _sbPrivateVars.Append(propText(0))
_sbProperties = _sbProperties.Append(propText(1))
End If
End While
_GetPropertyStrings = _sbPrivateVars.ToString() & vbCrLf & vbCrLf & _sbProperties.ToString()
Catch errorVariable As Exception
MsgBox(errorVariable.ToString())
End Try
End Function
Private Function _GetCorrespondingCLRType(ByVal sqlType As String) As String
Dim trimmedSqlType As String
If sqlType.Contains("(") Then
trimmedSqlType = Left(sqlType, sqlType.IndexOf("("))
End If
Dim returnType As String
Select Case sqlType.ToUpper()
Case "INT", "BIGINT", "SMALLINT", "TINYINT"
returnType = "int"
Case "NVARCHAR", "CHAR", "NCHAR", "NTEXT", "NVARCHAR", "TEXT", "VARCHAR"
returnType = "string"
Case "BIT"
returnType = "bool"
Case "DATE", "DATETIME", "DATETIME2", "SMALLDATETIME", "TIMESTAMP"
returnType = "DateTime"
Case "DECIMAL", "MONEY"
returnType = "decimal"
Case "FLOAT", "REAL"
returnType = "double"
Case "UNIQUEIDENTIFIER"
returnType = "Guid"
Case Else
returnType = "object"
End Select
_GetCorrespondingCLRType = returnType
End Function
Private Function _GetPropertyText(ByVal varName As String, ByVal varType As String) As String()
Dim arrProp(2) As String
Dim memberVarName As String = "_" & varName
Dim prop1 As String = "private " & varType & " " & memberVarName & ";" & vbCrLf
Dim prop2 As String = "public virtual " & varType & " " & varName & "" & vbCrLf & _
"{" & vbCrLf & _
" get { return " & memberVarName & "; }" & vbCrLf & _
" set { " & memberVarName & " = value; }" & vbCrLf & _
"}" & vbCrLf & vbCrLf
arrProp(0) = prop1
arrProp(1) = prop2
_GetPropertyText = arrProp
End Function
End Module
5) Now you should be able to see the two macro in the Module1 in your Macro Explorer named CreateHBMFromSqlTypes and CreatePropertiesFromSqlTypes
That's it now we are going to make use of the macros we have just created.
Creating Entity Classes
1) Open server explorer in the Visual Studio, connect to your database, select table for which you want to create entity class
2)Right click on the table and select "Open Table Definition", this should open the table definition in the one tab. Select the ColumnName and DataType and copy them (Ctrl+c)
3) Paste them in a blank entity class, say Employee.cs
4) Paste the copied content, select the pasted content
5) Click the
CreatePropertiesFromSqlTypes, this should create the property nodes corresponding to the column names e.g.
private int _EmployeeID;
private string _LastName;
...
...
public virtual int EmployeeID
{
get { return _EmployeeID; }
set { _EmployeeID = value; }
}
public virtual string LastName
{
get { return _LastName; }
set { _LastName = value; }
}
...
...
Creating the mapping files (.hbm) file
1) Repeat step 1 and 2 of "Creating Entity Classes"
2) Paste the content in your mapping file say Employee.hbm.xml in class node
4)Selected the pasted content
5)Click the
CreateHBMFromSqlTypes, this should create the property nodes corresponding to the column names e.g.
<property name="EmployeeID" column="EmployeeID" />
<property name="LastName" column="LastName" />
<property name="FirstName" column="FirstName" />
<property name="Title" column="Title" />
<property name="TitleOfCourtesy" column="TitleOfCourtesy" />
<property name="BirthDate" column="BirthDate" />
<property name="HireDate" column="HireDate" />
<property name="Address" column="Address" />
<property name="City" column="City" />
<property name="Region" column="Region" />
<property name="PostalCode" column="PostalCode" />
<property name="Country" column="Country" />
<property name="HomePhone" column="HomePhone" />
<property name="Extension" column="Extension" />
<property name="Photo" column="Photo" />
<property name="Notes" column="Notes" />
<property name="ReportsTo" column="ReportsTo" />
<property name="PhotoPath" column="PhotoPath" />
Conclusion
Writing entity classes and mapping files for nHibernate becomes time consuming and erroneous when there are many tables with many columns in it. This trick requires little bit of manual work however will save from many mistakes that might get introduced by typos.