I’m making a website for storing my audiobooks which based on WordPress. I get the audio files from the internet, post it to my Google Drive and stream them over my website. But I have to create a playlist for them which I do it manually for now. It’s really time-consuming, therefore, I decided to write a small service to write directly to the WordPress database and create the playlist at one click. In this post, I will show you how to do CRUD action with MySQL by FluentNHibernate.

1. Packages

Let’s install MySql.Data and FluentNHibernate

Install-Package MySql.Data -Version 8.0.12
Install-Package FluentNHibernate -Version 2.1.2	


<h1>2. NHibernateHelper</h1>
We do need a helper so that they can create a session for each query. Because the <em>System.Type</em> can't be serialized for now within .Net core so I have to create the <em>FluentConfiguration</em> each time the app starts. When Microsoft team improves the .Net core with serializable capability, we can serialize the configuration to a binary file and load them when the app gets started. It'll improve performance on startup but for now, I just create it from snatch.


public class NHibernateHelper : INHibernateHelper
{
	private readonly string _connectionString;
	private readonly object _lockObject = new object();
	private ISessionFactory _sessionFactory;

	public NHibernateHelper(IConfiguration configuration)
	{
		_connectionString = configuration["ConnectionString"];
	}

	private ISessionFactory SessionFactory
	{
		get
		{
			if (_sessionFactory == null)
			{
				CreateSessionFactory();
			}

			return _sessionFactory;
		}
	}

	public ISession OpenSession()
	{
		return SessionFactory.OpenSession();
	}

	private void CreateSessionFactory()
	{
		lock (_lockObject)
		{
			var fluentConfiguration = Fluently.Configure()
				.Database(
					MySQLConfiguration.Standard.ConnectionString(_connectionString)
				//.ShowSql()
				)
				.Mappings(m => m.FluentMappings.AddFromAssemblyOf<PostMap>());

			_sessionFactory = fluentConfiguration.BuildSessionFactory();
		}
	}
}

The appsettings.json with connection string has following format

{
  "ConnectionString": "Server=server_ip;Database=database_name;Uid=db_user_name;Pwd=db_user_password;SslMode=none",
  "BasicAuth": {
    "UserName": "user_name",
    "Password": "password"
  },
  "Logging": {
    "IncludeScopes": false,
    "Debug": {
      "LogLevel": {
        "Default": "Warning"
      }
    },
    "Console": {
      "LogLevel": {
        "Default": "Warning"
      }
    }
  }
}

The PostMap is the mapping class for the entity Post

public class PostMap : ClassMap<Post>
{
	public PostMap()
	{
		Table("wp_posts");
		LazyLoad();
		Id(x => x.Id).GeneratedBy.Identity().Column("ID");
		Map(x => x.PostAuthor).Column("post_author");
		Map(x => x.PostDate).Column("post_date");
	}
}

2. Repository

When the NHibernateHelper is ready we can use it in our repository.

public class BaseRepository
{
	protected INHibernateHelper NhibernateHelper;

	public BaseRepository(
		INHibernateHelper nHibernateHelper
	)
	{
		NhibernateHelper = nHibernateHelper;
	}

	public virtual ISession OpenSession()
	{
		return NhibernateHelper.OpenSession();
	}
}

For example, we get all posts from the database

public class PostRepository : BaseRepository, IPostRepository
{
	public PostRepository(INHibernateHelper nHibernateHelper) : base(nHibernateHelper)
	{

	}

	public List<Post> GetAll()
	{
		using (var session = OpenSession())
		{
			return session.Query<Post>().ToList();
		}
	}
}

3. Dependencies

Remember to register the dependencies injection when the app starts

public void ConfigureServices(IServiceCollection services)
{
	services.AddMvc();

	services.AddSingleton<INHibernateHelper, NHibernateHelper>();
	services.AddSingleton<IPostRepository, PostRepository>();
}