Create, Save or Update Database Table using NHibernate – C# programmatically

Hello everyone, in this article we will see how to update your Database using Nhiberate in you C# code. In my previous article I’ve given steps to configure Nhibernate using configuration XML file. Below is the code to create, save or update data in database table using NHibernate. It is self-explained by the comment in the code.

 using log4net;
 using NHibernate;
 using NHibernate.Mapping.ByCode;
 using NHibernate.Mapping.ByCode.Conformist;
 using NHibernate.Tool.hbm2ddl;
 using System;
 using System.Collections.Generic;
 using System.Linq;
 using System.Reflection;
 using Ncfg = NHibernate.Cfg;
 
 namespace MySolution
 {
     class Program
     {
         private static readonly ILog log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
         static void Main(string[] args)
         {
             //Constructor will be called and all config values are set
             DataBaseOperations dbOperation = new DataBaseOperations();
             //Validate Table and create if not available
             dbOperation.CreateDatabaseSchema();
             //Opening the session
             dbOperation.OpenSessionFactory();
             //To get values from Database
             List<HibDataBase> MyPackage = dbOperation.GetRatesPackages();
             //To save or update Database - You can edit the MyPacakage value if you want to
             dbOperation.SaveOrUpdatePackage(MyPackage);
             //To save the data
             dbOperation.SavePackage(MyPackage);
             //To update the data by ID
             dbOperation.UpdatePackageSharepointSyncDate(1);
             //Closing the session
             dbOperation.CloseSessionFactory();
         }
     }
 
     public class DataBaseOperations
     {
         private Ncfg.Configuration _configuration;
        // private readonly ILoggerHibernate _logger;
         private ISessionFactory _sessionFactory;       
         private ISession _session;
         private static readonly ILog logger = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
 
 
         public DataBaseOperations()
         {
             //Configuration values are taken from .config file
             _configuration = new Ncfg.Configuration();
             _configuration.Configure();
             var mapper = new ModelMapper();
             //Adding Table columns and Table name
             mapper.AddMapping(typeof(HibDataBaseMapping));
             _configuration.AddMapping(mapper.CompileMappingForAllExplicitlyAddedEntities());            
         }
 
         public void CreateDatabaseSchema()
         {
             ValidateSchema(_configuration);
         }
         //To check Database with the same name already exists or not
         public void ValidateSchema(Ncfg.Configuration config)
         {
             try
             {
                 new SchemaValidator(config).Validate();
             }
             catch
             {
                 new SchemaExport(_configuration).Create(false, true);
             }
         }
 
 
         public void OpenSessionFactory()
         {
             if (_sessionFactory == null || _sessionFactory.IsClosed)
                 _sessionFactory = _configuration.BuildSessionFactory();
             _session = _sessionFactory.OpenSession();
         }
 
         public bool SaveOrUpdatePackage(List<HibDataBase> packages)
         {
             using (var transaction = _session.BeginTransaction())
             {
                 _session.SetBatchSize(1000);
                 foreach (var package in packages)
                 {
                     //_session.Save(package);
                     _session.SaveOrUpdate(package);
                 }
                 transaction.Commit();
             }
             return true;
         }
 
         public bool SavePackage(List<HibDataBase> packages)
         {
             using (var transaction = _session.BeginTransaction())
             {
                 _session.SetBatchSize(1000);
                 foreach (var package in packages)
                 {
                     _session.Save(package);
                 }
                 transaction.Commit();
             }
             return true;
         }
 
         public List<HibDataBase> GetRatesPackages()
         {
             using (var transaction = _session.BeginTransaction())
             {
                 var packageList =
                     _session.QueryOver<HibDataBase>()
                         .Where(x => x.LastName == "Buhari")
                         .OrderBy(x => x.UpdatedOn)
                         .Asc.List()
                         .ToList();
                 transaction.Commit();
                 return packageList;
             }
         }
 
         public bool UpdatePackageSharepointSyncDate(int id)
         {
             using (var transaction = _session.BeginTransaction())
             {
                 _session.SetBatchSize(1000);
                 var package = _session.QueryOver<HibDataBase>().Where(x => x.ID == id).SingleOrDefault();
                 if (package != null)
                 {
                     package.UpdatedOn = DateTime.Now;
                     _session.Update(package);
                 }
                 transaction.Commit();
             }
             return true;
         }
 
         public void CloseSessionFactory()
         {
             if (!_sessionFactory.IsClosed)
                 _sessionFactory.Close();
         }
 
     }
 
     public class HibDataBaseMapping : ClassMapping<HibDataBase>
     {
         public HibDataBaseMapping()
         {
             //Each table should have unique ID
             Id<int>(x => x.ID);
             Property<string>(x => x.FirstName);
             Property<string>(x => x.LastName);          
             Property<DateTime?>(x => x.UpdatedOn);          
         }
     }
 
     //This will be the Table name and Properties will become colummns
     public class HibDataBase
     {
         #region Properties
         public virtual int ID { get; set; }
         public virtual string FirstName { get; set; }
         public virtual string LastName { get; set; }       
         public virtual DateTime? UpdatedOn { get; set; }    
         #endregion
     }
 }
 

Happy Coding

Ahamed