Automatic Table Generation in Any Database by NHibernate ORM and CodeDom

Introduction



First of all I want to explain why I have selected NHibernate, or in a better words Fluent NHibernate, to do this. I have provided some of the most popular advantages and disadvantages of NHibernate.

NHibernate

NHibernate is an Object Relational Mapping ORM framework. NHibernate resides between the database and business layer and provides powerful mapping. It inherits from Hibernate of the Java world. It uses mapping files or attributes besides properties.

NHibernate Advantages
  1. Powerful mapping facilities.
  2. Execute multiple queries in one go to the database instead of going to the DB for each query.
  3. Lazy Loading works for NHibernate and it means that you just fetch your necessary data in memory instead of the entire collection in memory and it reduces the memory overhead.
  4. Decoupling from the database means you can use various database types instead of just using SQL such as Oracle and so on.
  5. Writing code for NHibernate makes developers feel better in the aspect of readability and documentation.
  6. Caching and second level caching.
  7. Its session factory is a thread-safe object in NHibernate.
NHibernate Disadvantages
  1. Learning and mastering in NHibernate takes time and it seems cumbersome to be professional.
  2. NHibernate needs complicated XML configuration per entities.
  3. It takes a long time when you initiate it for the first time because of the preparation operation in the metadata is heavy.
How NHibernate works

NHibernate provides an ORM as a free XML configuration. It depends on a session factory that creates a session to bind to the database. You need a class to define your specific configuration that introduces a connection string for the specific database. Then whenever you call the session you are able to connect to your db. You can either write a code as traditional SQL query or LINQ query using NHibernate. LINQ as your namespace. The session totally encapsulates the unit of work pattern too. You need to produce XML files to work with NHibenate but I have used Fluent NHibernate to prevent any cumbersome something during implementation.



CodeDOM

With Fluent NHibernate you need to write two classes per entity of your table, one is simple and the other is for mapping and introduces a relation to the outer table. It is a bit difficult if you use many tables in your project to write two classes per entity. Therefore I decided to use the Code Document Object Model, CodeDOM, as a generation tool for generating these classes and I get table names, field names and their data types for the parent table from the UI and for the children I get its parent table to build the foreign key.

As a simplest explanation about CodeDOM: You need to generate many classes with the same structures but some parts have a different expression so to save your time and energy it is better to write one class instead of many classes and those classes are a good template for generating all your classes.

The following namespaces enable you to use CodeDOM:
  1. using System.IO;  
  2. using System.CodeDom;  
  3. using System.CodeDom.Compiler;  
  4. using Microsoft.CSharp;  
Using CodeDOM you can either write source code for ASP.NET, XML web services client proxies and so on or compile. Its structure is a tree graph with codecompileunit as its root. Then you should define your namespace and import them. Determine your class name and fields and properties and add them as members to the class.

CodeDOM Advantages
  1. CodeDOM lets you create, compile and execute your source code for the application at run time, without writing many lines or when your parameter should be determined at run time.
  2. CodeDOM uses a single model to generate source code, so any languages that support CodeDOM can work with it.
  3. It is possible to translate VB to C# or inverse.
CodeDOM Disadvantages
  1. There are limitations to defining expressions using CodeDOM such that you need to use Snippet classes.
  2. Snippet classes cannot satisfy any expression so you should not use a heavy function on that.
Using the code step-by-step
  • Creating Database 
You should create a database as "GenerateDB" that is a fundamental requirement for a connection string. I have used SQL Server 2008 but you can use any database because NHibernate is independent of database type.





I have defined the table "DataType" with the two columns, ID and Name, for the data type of entities. You can assign your datat type name to the "Name" field such as int, string and and so on. Be careful that CodeDOM pays attention to your application language data type. For instance I used C# so I used int and string. That is a bit different than the VB language. In VB we use "integer" instead of "int".





  • Create MVC Project with Fluent NHibernate + Microsoft.Build for CodeDOM + AngularJS for Frontend
I have used Microsoft Visual Studio 2013 and MVC ASP.Net for this scenario. I have applied Fluent NHibernate instead of NHibernate because its source code generation is easy by CodeDOM. I imported Microsoft.Build library for CodeDOM.

Eventually I used AngularJS for the frontend and UI sections, you can get more information about Angularjs implementation at http://www.codeproject.com/Articles/869433/AngularJS-MVC-Repository-Dispose and about MVC ASP.NET implementation at http://technical.cosmicverse.info/article/index/3.

File -> New Project -> ASP.NET MVC 4 Project then select "Internet Application".



Select "Empty".



Install Fluent NHibernate

Go to the project in the Solution Exlorer then right-click on "References" then select "Manage Nuget Packages".



Search "FluentNHibernate" then click on "Install" to add its references.









Look at the References for the project. There are the following three new references:
  1. FluentNHibernate
  2. Iesi.Collections
  3. NHibernate


In order to use CodeDOM you should add "Microsoft.Build" and "Microsoft.Build.Framework".



Install AngularJS

Go to the project in  the Solution Explorer and right-click on "References" then select "Manage Nuget Packages".

Search: "AngularJS" then click on "Install".


  • Review on Scenario and Testing
In this scenario I have assigned two text boxes just for two fields, you can create more than one depending on your requirements. I will improve this section by creating text boxes at run time in the newer version of this article so please bookmark this article for a newer version.


I create a parent table and child table that have a one-to-many relation to each other. In the following picture the parent table has no parent so the last combo box should be empty.



But the child table has a relationship with the parent by calling its parent table and finally the combo box.



Look at the solution and you will see that new classes have been generated by CodeDOM inside "Entities" and "Mapping". For each entitiy there are two classes, one in the "Entities" folder such as "Parent.cs" and another is in the "Mapping" folder as "ChildMap.cs".



After generation look at your database too. There is no difference, whether there your database is SQL, Oracle or whatever. Your tables have been generated here too by FluentNHibernate.

 



In a nutshell

You determine your table spesification on the UI and then Angular sends info to the controller and there by using "GenerateTable.cs" and "GenerateMap.cs" with a GenerateDBController/Generate Action all the classes will be created on the "Entities" and "Mapping" folders. These classes, such as "Parent.cs" and "ParentMap.cs" by the FluentNHibernate utilities "NHibernate.cs" will create tables inside the database.


  • Using Code
Configuration Fluent NHibernate
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Web;  
  5. using FluentNHibernate.Cfg;  
  6. using FluentNHibernate.Cfg.Db;  
  7. using NHibernate;  
  8. using NHibernate.Tool.hbm2ddl;  
  9. using MVCNHibernate.Entities; //ProjectName.Entites  
  10. using MVCNHibernate.Controllers; //ProjectName.Controllers  
  11.   
  12. namespace MVCNHibernate  
  13. {  
  14.     public class NHibernate  
  15.     {  
  16.         private static ISessionFactory _sessionFactory;  
  17.         private static ISessionFactory SessionFactory  
  18.         {  
  19.             get {  
  20.                 if (_sessionFactory == null) InitializeSessionFactory();  
  21.                 return _sessionFactory;  
  22.             }  
  23.         }  
  24.   
  25.         private static void InitializeSessionFactory()  
  26.         {  
  27.             _sessionFactory = Fluently.Configure()  
  28.                 .Database(MsSqlConfiguration.MsSql2008.ConnectionString(@  
  29.             "Server=.;initial catalog=GenerateDB;integrated security=True")  
  30.                 .ShowSql())  
  31.                 .Mappings(m = > m.FluentMappings.AddFromAssemblyOf())  
  32.                 .ExposeConfiguration(cfg = > new SchemaUpdate(cfg)  
  33.                 .Execute(falsetrue))  
  34.                 .BuildSessionFactory();  
  35.         }  
  36.   
  37.         public static ISession OpenSession()  
  38.         {  
  39.             return SessionFactory.OpenSession();  
  40.         }  
  41.     }  
  42. }  
GenerateTable.cs
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Text;  
  5. using System.Threading.Tasks;  
  6. using System.Reflection;  
  7. using System.IO;  
  8. using System.CodeDom;  
  9. using System.CodeDom.Compiler;  
  10. using Microsoft.CSharp;  
  11. using System.Reflection;  
  12. using System.Web.Mvc;  
  13. using System.Web;  
  14. using System.Collections;  
  15. using System.ComponentModel;  
  16. using System.Data;  
  17. using System.Diagnostics;  
  18.   
  19.   
  20. namespace MVCNHibernate   
  21. {  
  22.     public class GenerateTable   
  23.     {  
  24.         CodeCompileUnit targetUnit;  
  25.   
  26.         CodeTypeDeclaration targetClass;  
  27.   
  28.         public GenerateTable(string tableName)  
  29.         {  
  30.             targetUnit = new CodeCompileUnit();  
  31.   
  32.             //Path  
  33.             CodeNamespace samples = new CodeNamespace("MVCNHibernate.Entities");  
  34.   
  35.             //Namespace  
  36.             samples.Imports.Add(new CodeNamespaceImport("System"));  
  37.             samples.Imports.Add(new CodeNamespaceImport("System.Collections.Generic"));  
  38.             samples.Imports.Add(new CodeNamespaceImport("System.Linq"));  
  39.             samples.Imports.Add(new CodeNamespaceImport("System.Text"));  
  40.             samples.Imports.Add(new CodeNamespaceImport("MVCNHibernate.Entities"));  
  41.   
  42.             targetClass = new CodeTypeDeclaration(tableName);  
  43.             targetClass.IsClass = true;  
  44.             targetClass.TypeAttributes = TypeAttributes.Public;  
  45.             samples.Types.Add(targetClass);  
  46.             targetUnit.Namespaces.Add(samples);  
  47.         }  
  48.   
  49.         public void AddFields(string fld1, string dt1, string fld2, string dt2)  
  50.         {  
  51.             CodeMemberField field1 = new CodeMemberField();  
  52.             field1.Attributes = MemberAttributes.Private;  
  53.             if (dt1 == "int")   
  54.             {  
  55.                 field1.Type = new CodeTypeReference(typeof(System.Int32));  
  56.             } else if (dt1 == "string")   
  57.             {  
  58.                 field1.Type = new CodeTypeReference(typeof(System.String));  
  59.             }  
  60.   
  61.             field1.Name = "_" + fld1;  
  62.             targetClass.Members.Add(field1);  
  63.   
  64.             CodeMemberProperty property1 = new CodeMemberProperty();  
  65.             property1.GetStatements.Add(new CodeMethodReturnStatement(new CodeFieldReferenceExpression(new CodeThisReferenceExpression(), "_" + fld1)));  
  66.             property1.SetStatements.Add(new CodeAssignStatement(new CodeFieldReferenceExpression(new CodeThisReferenceExpression(), "_" + fld1), new CodePropertySetValueReferenceExpression()));  
  67.             property1.Attributes = MemberAttributes.Public;  
  68.             property1.Name = fld1;  
  69.             if (dt1 == "int")   
  70.             {  
  71.                 property1.Type = new CodeTypeReference(typeof(System.Int32));  
  72.             } else if (dt1 == "string")   
  73.             {  
  74.                 property1.Type = new CodeTypeReference(typeof(System.String));  
  75.             }  
  76.   
  77.             targetClass.Members.Add(property1);  
  78.   
  79.             CodeMemberField field2 = new CodeMemberField();  
  80.             field2.Attributes = MemberAttributes.Private;  
  81.             if (dt2 == "int")  
  82.             {  
  83.                 field2.Type = new CodeTypeReference(typeof(System.Int32));  
  84.             } else if (dt2 == "string")   
  85.             {  
  86.                 field2.Type = new CodeTypeReference(typeof(System.String));  
  87.             }  
  88.             field2.Name = "_" + fld2;  
  89.             targetClass.Members.Add(field2);  
  90.   
  91.             CodeMemberProperty property2 = new CodeMemberProperty();  
  92.             property2.GetStatements.Add(new CodeMethodReturnStatement(new CodeFieldReferenceExpression(new CodeThisReferenceExpression(), "_" + fld2)));  
  93.             property2.SetStatements.Add(new CodeAssignStatement(new CodeFieldReferenceExpression(new CodeThisReferenceExpression(), "_" + fld2), new CodePropertySetValueReferenceExpression()));  
  94.             property2.Attributes = MemberAttributes.Public;  
  95.             property2.Name = fld2;  
  96.             if (dt2 == "int")  
  97.             {  
  98.                 property2.Type = new CodeTypeReference(typeof(System.Int32));  
  99.             } else if (dt2 == "string")   
  100.             {  
  101.                 property2.Type = new CodeTypeReference(typeof(System.String));  
  102.             }  
  103.   
  104.             targetClass.Members.Add(property2);  
  105.         }  
  106.   
  107.         public void RelationalAddFields(string tableName, string fld1, string dt1, string fld2, string dt2, string parent)  
  108.         {  
  109.             CodeMemberField field1 = new CodeMemberField();  
  110.             field1.Attributes = MemberAttributes.Private;  
  111.             if (dt1 == "int")  
  112.             {  
  113.                 field1.Type = new CodeTypeReference(typeof(System.Int32));  
  114.             } else if (dt1 == "string")  
  115.             {  
  116.                 field1.Type = new CodeTypeReference(typeof(System.String));  
  117.             }  
  118.   
  119.             field1.Name = "_" + fld1;  
  120.             targetClass.Members.Add(field1);  
  121.   
  122.             CodeMemberProperty property1 = new CodeMemberProperty();  
  123.             property1.GetStatements.Add(new CodeMethodReturnStatement(new CodeFieldReferenceExpression(new CodeThisReferenceExpression(), "_" + fld1)));  
  124.             property1.SetStatements.Add(new CodeAssignStatement(new CodeFieldReferenceExpression(new CodeThisReferenceExpression(), "_" + fld1), new CodePropertySetValueReferenceExpression()));  
  125.             property1.Attributes = MemberAttributes.Public;  
  126.             property1.Name = fld1;  
  127.             if (dt1 == "int")  
  128.             {  
  129.                 property1.Type = new CodeTypeReference(typeof(System.Int32));  
  130.             } else if (dt1 == "string")   
  131.             {  
  132.                 property1.Type = new CodeTypeReference(typeof(System.String));  
  133.             }  
  134.   
  135.             targetClass.Members.Add(property1);  
  136.   
  137.             CodeMemberField field2 = new CodeMemberField();  
  138.             field2.Attributes = MemberAttributes.Private;  
  139.             if (dt2 == "int")  
  140.             {  
  141.                 field2.Type = new CodeTypeReference(typeof(System.Int32));  
  142.             } else if (dt2 == "string")   
  143.             {  
  144.                 field2.Type = new CodeTypeReference(typeof(System.String));  
  145.             }  
  146.             field2.Name = "_" + fld2;  
  147.             targetClass.Members.Add(field2);  
  148.   
  149.             CodeMemberProperty property2 = new CodeMemberProperty();  
  150.             property2.GetStatements.Add(new CodeMethodReturnStatement(new CodeFieldReferenceExpression(new CodeThisReferenceExpression(), "_" + fld2)));  
  151.             property2.SetStatements.Add(new CodeAssignStatement(new CodeFieldReferenceExpression(new CodeThisReferenceExpression(), "_" + fld2), new CodePropertySetValueReferenceExpression()));  
  152.             property2.Attributes = MemberAttributes.Public;  
  153.             property2.Name = fld2;  
  154.             if (dt2 == "int")   
  155.             {  
  156.                 property2.Type = new CodeTypeReference(typeof(System.Int32));  
  157.             } else if (dt2 == "string")   
  158.             {  
  159.                 property2.Type = new CodeTypeReference(typeof(System.String));  
  160.             }  
  161.   
  162.             targetClass.Members.Add(property2);  
  163.   
  164.             CodeMemberField field3 = new CodeMemberField();  
  165.             field3.Attributes = MemberAttributes.Private;  
  166.   
  167.             // field3.Type = new CodeTypeReference(typeof(System.Int32));  
  168.             Type myType = Type.GetType("MVCNHibernate.Entities." + parent);  
  169.             //dynamic instance = Activator.CreateInstance(myType);  
  170.   
  171.             field3.Type = new CodeTypeReference(myType);  
  172.   
  173.             field3.Name = "_" + parent + tableName;  
  174.             targetClass.Members.Add(field3);  
  175.   
  176.             CodeMemberProperty property3 = new CodeMemberProperty();  
  177.             property3.GetStatements.Add(new CodeMethodReturnStatement(new CodeFieldReferenceExpression(new CodeThisReferenceExpression(), "_" + parent + tableName)));  
  178.             property3.SetStatements.Add(new CodeAssignStatement(new CodeFieldReferenceExpression(new CodeThisReferenceExpression(), "_" + parent + tableName), new CodePropertySetValueReferenceExpression()));  
  179.             property3.Attributes = MemberAttributes.Public;  
  180.             property3.Name = parent + tableName;  
  181.             Type myType2 = Type.GetType("MVCNHibernate.Entities." + parent);  
  182.             // dynamic instance2 = Activator.CreateInstance(myType2);  
  183.             property3.Type = new CodeTypeReference(myType2);  
  184.   
  185.             targetClass.Members.Add(property3);  
  186.         }  
  187.   
  188.         CodeDomProvider provider;  
  189.         public void GenerateCSharpCode(string fileName)  
  190.         {  
  191.   
  192.             provider = CodeDomProvider.CreateProvider("CSharp");  
  193.             CodeGeneratorOptions options = new CodeGeneratorOptions();  
  194.             options.BracingStyle = "C";  
  195.   
  196.             using(StreamWriter sourceWriter = new StreamWriter(fileName))  
  197.             {  
  198.                 provider.GenerateCodeFromCompileUnit(  
  199.                 targetUnit, sourceWriter, options);  
  200.             }  
  201.         }  
  202.     }  
  203. }  
GenerateMap.cs
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Text;  
  5. using System.Threading.Tasks;  
  6. using System.Reflection;  
  7. using System.IO;  
  8. using System.CodeDom;  
  9. using System.CodeDom.Compiler;  
  10. using Microsoft.CSharp;  
  11. using System.Linq.Expressions;  
  12.   
  13. namespace MVCNHibernate  
  14. {  
  15.     public class GenerateMap   
  16.     {  
  17.         CodeCompileUnit targetUnit;  
  18.   
  19.         CodeTypeDeclaration targetClass;  
  20.   
  21.         public GenerateMap(string tableName)   
  22.         {  
  23.             targetUnit = new CodeCompileUnit();  
  24.   
  25.             //Path  
  26.             CodeNamespace samples = new CodeNamespace("MVCNHibernate.Mapping");  
  27.   
  28.             //Namespace  
  29.             samples.Imports.Add(new CodeNamespaceImport("System"));  
  30.             samples.Imports.Add(new CodeNamespaceImport("System.Collections.Generic"));  
  31.             samples.Imports.Add(new CodeNamespaceImport("System.Linq"));  
  32.             samples.Imports.Add(new CodeNamespaceImport("System.Web"));  
  33.             samples.Imports.Add(new CodeNamespaceImport("FluentNHibernate.Mapping"));  
  34.             samples.Imports.Add(new CodeNamespaceImport("MVCNHibernate.Entities"));  
  35.   
  36.             targetClass = new CodeTypeDeclaration(tableName + "Map");  
  37.             targetClass.BaseTypes.Add(new CodeTypeReference  
  38.             {  
  39.                 BaseType = "ClassMap`1[" + tableName + "]", Options = CodeTypeReferenceOptions.GenericTypeParameter  
  40.             });  
  41.             targetClass.IsClass = true;  
  42.             targetClass.TypeAttributes = TypeAttributes.Public;  
  43.             samples.Types.Add(targetClass);  
  44.             targetUnit.Namespaces.Add(samples);  
  45.   
  46.         }  
  47.   
  48.         public void AddConstructor(string fld1, string fld2, string tbl)   
  49.         {  
  50.             // Declare the constructor  
  51.             CodeConstructor constructor = new CodeConstructor();  
  52.             constructor.Attributes = MemberAttributes.Public | MemberAttributes.Final;  
  53.   
  54.             CodeExpression newType = new CodeExpression();  
  55.             CodeSnippetExpression snippet = new CodeSnippetExpression();  
  56.   
  57.             string hh = string.Format("Table(\"{0}\"", tbl);  
  58.             string lambda = @  
  59.             "Id(x => x." + fld1 + "); Map(x => x." + fld2 + ");" + hh + ")";  
  60.   
  61.             var lambdaExpression = new CodeSnippetExpression(lambda);  
  62.   
  63.             constructor.Statements.Add(lambdaExpression);  
  64.   
  65.             targetClass.Members.Add(constructor);  
  66.         }  
  67.   
  68.   
  69.         public void RelationalAddConstructor(string fld1, string fld2, string tbl, string parent)  
  70.         {  
  71.             CodeConstructor constructor = new CodeConstructor();  
  72.             constructor.Attributes = MemberAttributes.Public | MemberAttributes.Final;  
  73.   
  74.             CodeExpression newType = new CodeExpression();  
  75.             CodeSnippetExpression snippet = new CodeSnippetExpression();  
  76.   
  77.             string parenttbl = parent + tbl;  
  78.             string fk = parent + "id";  
  79.             string cc = string.Format("\"{0}\"", fk);  
  80.             string hh = string.Format("Table(\"{0}\"", tbl);  
  81.             string lambda = @  
  82.             "Id(x => x." + fld1 + "); Map(x => x." + fld2 + "); References(x => x." + parenttbl + ").Column(" + cc + "); " + hh + ")";  
  83.   
  84.             var lambdaExpression = new CodeSnippetExpression(lambda);  
  85.   
  86.             constructor.Statements.Add(lambdaExpression);  
  87.   
  88.             targetClass.Members.Add(constructor);  
  89.         }  
  90.   
  91.         public void GenerateCSharpCode(string fileName)  
  92.         {  
  93.             CodeDomProvider provider = CodeDomProvider.CreateProvider("CSharp");  
  94.             CodeGeneratorOptions options = new CodeGeneratorOptions();  
  95.             options.BracingStyle = "C";  
  96.   
  97.             using(StreamWriter sourceWriter = new StreamWriter(fileName))   
  98.             {  
  99.                 provider.GenerateCodeFromCompileUnit(  
  100.                 targetUnit, sourceWriter, options);  
  101.             }  
  102.         }  
  103.     }  
  104. }  
GenerateDBController.cs
  1. using System;  
  2. using System.Collections;  
  3. using System.Collections.Generic;  
  4. using System.Linq;  
  5. using System.Web;  
  6. using System.Web.Mvc;  
  7. using MVCNHibernate.Entities;  
  8. using Microsoft.Build;  
  9. using Microsoft.Build.Construction;  
  10. using Microsoft.Build.Evaluation;  
  11. using Microsoft.Build.Execution;  
  12. using NHibernate.Linq;  
  13. using NHibernate;  
  14. using System.Collections;  
  15.   
  16. namespace MVCNHibernate.Controllers  
  17. {  
  18.     public class GenerateDBController: Controller  
  19.     {  
  20.   
  21.         public ActionResult Index()   
  22.         {  
  23.             return View();  
  24.         }  
  25.   
  26.         public JsonResult getDataType()  
  27.         {  
  28.             using(var session = NHibernate.OpenSession())  
  29.             {  
  30.                 using(var transaction = session.BeginTransaction())   
  31.                 {  
  32.                     var query = session.CreateSQLQuery("select Name from DataType");  
  33.                     var result = query.List();  
  34.                     return Json(result, JsonRequestBehavior.AllowGet);  
  35.                 }  
  36.             }  
  37.         }  
  38.   
  39.         public JsonResult getTable()   
  40.         {  
  41.             using(var session = NHibernate.OpenSession())   
  42.             {  
  43.                 using(var transaction = session.BeginTransaction())  
  44.                 {  
  45.                     var sql = String.Format("SELECT * FROM information_schema.tables");  
  46.                     var query = session.CreateSQLQuery(sql);  
  47.                     var result = query.List();  
  48.   
  49.                     List tableName = new List();  
  50.                     object tableSpec;  
  51.   
  52.                     IList collection;  
  53.                     for (int i = 0; i < result.Count; i++)  
  54.                     {  
  55.                         tableSpec = result[i];  
  56.                         collection = (IList) tableSpec;  
  57.                         tableName.Add(collection[2].ToString());  
  58.   
  59.                     }  
  60.   
  61.                     return Json(tableName, JsonRequestBehavior.AllowGet);  
  62.                 }  
  63.             }  
  64.         }  
  65.   
  66.         public string Generator(string tableName, string fieldName1, string dataType1, string fieldName2, string dataType2, string Paren            {  
  67.             try  
  68.             {  
  69.   
  70.                 if (Parent == null//=> No Relation  
  71.                 {  
  72.                     var projectCollection = ProjectCollection.GlobalProjectCollection;  
  73.                     string projPath = "~/MVCNHibernate.csproj";  
  74.   
  75.                     var p = projectCollection.LoadProject(Server.MapPath(projPath));  
  76.   
  77.   
  78.                     string projItem1 = "~/Entities/" + tableName + ".cs";  
  79.                     GenerateTable genTable = new GenerateTable(tableName);  
  80.                     genTable.AddFields(fieldName1, dataType1, fieldName2, dataType2);  
  81.                     genTable.GenerateCSharpCode(Server.MapPath(projItem1));  
  82.   
  83.                     p.AddItem("Compile", Server.MapPath(projItem1));  
  84.                     p.Save();  
  85.   
  86.                     string projItem2 = "~/Mapping/" + tableName + "Map.cs";  
  87.                     GenerateMap genMap = new GenerateMap(tableName);  
  88.                     genMap.AddConstructor(fieldName1, fieldName2, tableName);  
  89.                     genMap.GenerateCSharpCode(Server.MapPath(projItem2));  
  90.   
  91.                     p.AddItem("Compile", Server.MapPath(projItem2));  
  92.                     p.Save();  
  93.                     ProjectCollection.GlobalProjectCollection.UnloadProject(p);  
  94.   
  95.                     p.Build();  
  96.   
  97.                     NHibernate.OpenSession();  
  98.   
  99.                 }   
  100.                 else if (Parent != null//=> Relation To Parent  
  101.                 {  
  102.                     var projectCollection = ProjectCollection.GlobalProjectCollection;  
  103.                     string projPath = "~/MVCNHibernate.csproj";  
  104.   
  105.                     var p = projectCollection.LoadProject(Server.MapPath(projPath));  
  106.   
  107.   
  108.                     string fileNameEn = "~/Entities/" + tableName + ".cs";  
  109.                     GenerateTable genTable = new GenerateTable(tableName);  
  110.                     genTable.RelationalAddFields(tableName, fieldName1, dataType1, fieldName2, dataType2, Parent);  
  111.                     genTable.GenerateCSharpCode(Server.MapPath(fileNameEn));  
  112.   
  113.                     string projItem1 = "~/Entities/" + tableName + ".cs";  
  114.                     p.AddItem("Compile", Server.MapPath(projItem1));  
  115.                     p.Save();  
  116.   
  117.                     string fileNameMap = "~/Mapping/" + tableName + "Map.cs";  
  118.                     GenerateMap genMap = new GenerateMap(tableName);  
  119.                     genMap.RelationalAddConstructor(fieldName1, fieldName2, tableName, Parent);  
  120.                     genMap.GenerateCSharpCode(Server.MapPath(fileNameMap));  
  121.   
  122.                     string projItem2 = "~/Mapping/" + tableName + "Map.cs";  
  123.                     p.AddItem("Compile", Server.MapPath(projItem2));  
  124.                     p.Save();  
  125.                     //ProjectCollection.GlobalProjectCollection.UnloadProject(p);  
  126.                     ProjectCollection.GlobalProjectCollection.UnloadAllProjects();  
  127.                     p.Build();  
  128.   
  129.                     NHibernate.OpenSession();  
  130.   
  131.                 }  
  132.                 return "Database generated Successfully ";  
  133.   
  134.             }   
  135.             catch   
  136.             {  
  137.                 return "Database did not generate Successfully ";  
  138.             }  
  139.   
  140.         }  
  141.   
  142.     }  
  143. }  
Entities -> DataType.cs
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Web;  
  5.   
  6. namespace MVCNHibernate.Entities  
  7. {  
  8.     public class DataType  
  9.     {  
  10.         public virtual int ID  
  11.         {  
  12.             get;  
  13.             set;  
  14.         }  
  15.         public virtual string Name  
  16.         {  
  17.             get;  
  18.             set;  
  19.         }  
  20.     }  
  21. }  
Mapping -> DataTypeMap.cs
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Web;  
  5. using FluentNHibernate.Mapping;  
  6. using MVCNHibernate.Entities;  
  7.   
  8. namespace MVCNHibernate.Mapping   
  9. {  
  10.     public class DataTypeMap: ClassMap  
  11.     {  
  12.         public DataTypeMap()   
  13.         {  
  14.             Id(x = > x.ID);  
  15.   
  16.             Map(x = > x.Name);  
  17.   
  18.             Table("DataType");  
  19.         }  
  20.     }  
  21. }  
Content -> Angular -> Module.js
  1. var app = angular.module('MyApp', ['ngRoute']);  
Content -> Angular -> Controller.js
  1. app.controller("AngularCtrlCreateTable", function($scope, angularServiceCreateTable) {  
  2.     $scope.divGen = true;  
  3.     $scope.divNewGen = false;  
  4.     GetAllDataType1();  
  5.     GetAllTable();  
  6.   
  7.     //To Get All Records   
  8.     function GetAllDataType1()   
  9.     {  
  10.         var Data = angularServiceCreateTable.getDataType();  
  11.         Data.then(function(data)  
  12.         {  
  13.             $scope.items1 = data.data;  
  14.             $scope.items2 = data.data;  
  15.         }, function()   
  16.         {  
  17.             alert('Error');  
  18.         });  
  19.     }  
  20.   
  21.   
  22.     //To Get All Records   
  23.     function GetAllTable()   
  24.     {  
  25.         var Data = angularServiceCreateTable.getTable();  
  26.         Data.then(function(tb)   
  27.         {  
  28.             $scope.itemsPT = tb.data;  
  29.         }, function()   
  30.         {  
  31.             alert('Error');  
  32.         });  
  33.     }  
  34.   
  35.     $scope.Generator = function()   
  36.     {  
  37.         var dt1 = $scope.selectedItem1;  
  38.         var dt2 = $scope.selectedItem2;  
  39.         var dt3 = $scope.selectedItemParentTable;  
  40.         var getmsg = angularServiceCreateTable.Generator($scope.TableName, $scope.Field1, dt1, $scope.Field2, dt2, dt3);  
  41.         getmsg.then(function(messagefromcontroller)  
  42.         {  
  43.             $scope.divGen = false;  
  44.             $scope.divNewGen = true;  
  45.             alert(messagefromcontroller.data);  
  46.         }, function()   
  47.         {  
  48.             alert('There is error in database generation');  
  49.         });  
  50.     }  
  51.   
  52.     $scope.GeneratorNew = function()   
  53.     {  
  54.         $scope.divGen = true;  
  55.         $scope.divNewGen = false;  
  56.         GetAllTable();  
  57.         GetAllDataType2();  
  58.         GetAllDataType1();  
  59.         $scope.TableName = "";  
  60.         $scope.Field1 = "";  
  61.         $scope.Field2 = "";  
  62.     }  
  63.   
  64. });  
Content -> Angular -> Service.js
  1. app.service("angularServiceCreateTable", function($http) {  
  2.     this.getDataType = function()  
  3.     {  
  4.         return $http.get("/GenerateDB/getDataType");  
  5.     };  
  6.   
  7.     this.getTable = function()   
  8.     {  
  9.         return $http.get("/GenerateDB/getTable");  
  10.     };  
  11.   
  12.     //Database Generation   
  13.     this.Generator = function(tblName, fldName1, dType1, fldName2, dType2, prtTableName) {  
  14.         var response = $http({  
  15.             method: "post",  
  16.             url: "/GenerateDB/Generator",  
  17.             params:   
  18.             {  
  19.                 tableName: tblName,  
  20.                 fieldName1: fldName1,  
  21.                 dataType1: dType1,  
  22.                 fieldName2: fldName2,  
  23.                 dataType2: dType2,  
  24.                 Parent: prtTableName  
  25.             }  
  26.         });  
  27.         return response;  
  28.     }  
  29.   
  30. });  
Views -> GenerateDB -> Index.cshtml
  1. @
  2. {  
  3.    ViewBag.Title = "Index";  
  4. }  
Index



Views -> GenerateDB -> Shared -> _Layout.cshtml
  1. @System.Web.Optimization.Styles.Render("~/Content/css")  
  2. @RenderBody() @System.Web.Optimization.Scripts.Render("~/bundles/jquery") @RenderSection("scripts", required: false)  
References:

http://nhibernate.info/doc/nh/en/

http://www.codeguru.com/vb/gen/article.php/c19573/Microsoft-NET-CodeDom-Technology.htm

http://ayende.com/blog/4351/nhibernate-vs-entity-framework-4-0

http://www.codeproject.com/Articles/148959/How-the-new-C-dynamic-type-can-simplify-access-to

http://technical.cosmicverse.info/article/index/3

http://www.codeproject.com/Articles/869433/AngularJS-MVC-Repository-Dispose?msg=5029301#xx5029301xx

History

First Version: 03/28/2015

Feedback

Feel free to leave any feedback on this article. It is a pleasure to see your comments and to get votes about this code. If you have any questions, please do not hesitate to ask me here.

Up Next
    Ebook Download
    View all
    SQL Jobs
    Read by 0 people
    Download Now!
    Learn
    View all