SQL  database can be used for managing data generated by the automation system. Generated data can be stored in a local database or in the cloud by using different methods. In our fictitious home automation system, we shall use the MS SQL server. We shall learn how to install the SQL server to the local machine or on any other devices such as PLC. There are two parts to this tutorial, installing the SQL server and using the SQL server for our home automation project, to understand we need to study a bit about modeling of data which means we need to go through some sample applications.

SQL Server Installation

We need to install a database on our machine so that data generated by different sensors can be stored in the database. We need to install a database on our computer. Follow these simple steps to install it and learn how we can make a sample application that fits our automation project. Continue reading.

SQL Server Configuration

SQL Server Configuration Manager allows us to configure server and client network protocols, and connectivity options. After the correct protocols are enabled, we do not need to change the server network connections. However, we can use SQL Server Configuration Manager if we need to reconfigure the server connections so SQL Server listens on a particular network protocol, port, or pipe. Continue reading for the settings.

Form SQL Car, Model, Manufacture

The best way to understand fluent NHibernate is to make a simple application by using C#  in the windows platform.  We shall explain step by step our first sample. Actually, this code should be understood before doing the TwinCAT example. This is a typical CAR and MANUFACTURING model.  Explore and continue reading.

SQL APP Store Product Modelling

We need to model our system so that we can read and write data to the system or we can update the system based on the event. First, we need to find relations among different entities in the system. We shall use the fluent-NHibernate module. If we use this module then we don’t need to write all the SQL statements by ourselves. We shall provide examples that will help us to understand the data modeling. Continue reading.

SQL APP Store Product Modelling in DLL

We continue using the Fluent NHibernate. This time we convert the application to Windows Form app and a DLL. All functionalities have been transferred to the DLL. Continue reading.

Display Data from SQL to HMI
Store Product Modelling

Display Data to TwinCAT HMI from SQL Server

We need to model our system so that we can read and write data to the system or we can update the system based on the event. First, we need to find relations among different entities in the system. We shall use the fluent-NHibernate module. If we use this module then we don’t need to write all the SQL command by ourselves. We shall provide an example with full source code to understand how we display data from SQL server to TwinCAT HMI using JSON. Continue reading.

TwinCAT HMI and SQLite

The classic example of store example has been converted by using Fluent Nhibernate and SQLite  Continue reading.

Direct SQL Connection

This section describes how to use connections directly for different purposes. We shall show how to delete a row, table, etc. This can be used in conjunction with the fluent NHibernate if we can’t do something by NHibernate.  Continue reading.

PostgreSQL and REST API

PostgreSQL also known as Postgres, was developed by Michael Stonebraker of the University of California, Berkley. PostgreSQL is open source database. This tutorial will examine how we can match with an automation system. We shall store our data from PLC to PostgreSQL and generate reports from there. We shall access the data from the database by using Node JS.  Continue reading.

Generating report from SQL

Generate a report from SQL database coming by using C# and SQL Server Management Studio  Continue reading.

Discussion:

1 => Reference is the same as many-to-one, for example References(x => x.Store); // many x in a store

 

HasMany(x => x.Staff).Inverse().Cascade.All();

HasMany creats one-to-many relationship with Employee (one Store to many Employees), which is the other side of the Employee.Store relationship.

2 => Taking log from NHibernate

If you use NHibernate  with extension, by default NHIbernate will produce huge debug logs. If you want to get logs from your own extension then it will be difficult. Following is a way to keep only error message and filter out other message from NHibernate

<log4net>
  <root>
    <level value="ALL" />
    <appender-ref ref="file" />
  </root>
 <logger name="NHibernate">
   <level value="ERROR" />
 </logger>
 <logger name="NHibernate.SQL">
   <level value="ERROR" />
 </logger>
  <appender name="file" type="log4net.Appender.RollingFileAppender">
    <file value="logfile.log" />
    <appendToFile value="true" />
    <rollingStyle value="Size" />
    <maxSizeRollBackups value="5" />
    <maximumFileSize value="10MB" />
    <staticLogFileName value="true" />    
    <layout type="log4net.Layout.PatternLayout">
      <conversionPattern value="%date [%thread] %level %logger - %message%newline" />
    </layout>
  </appender>
</log4net>

3 => log file path where the file will be generated

We can control the log file path of NHibernate by the following way, we shall put the expected directory in the log file

<file value="c:\\temp\\logfile.log" />

 

References:

=> https://github.com/FluentNHibernate/fluent-nhibernate/wiki/Getting-started (Git hub page for NHibernate)

=> https://nhibernate.info/doc/nhibernate-reference/queryqueryover.html (queryqueryover and other references)

=> https://weblogs.asp.net/ricardoperes/deleting-entities-in-nhibernate (Deleting Entities in NHibernate)

=> https://www.hemelix.com/wp-content/uploads/2022/02/nhibernate_reference.pdf (NHibernate Reference @Hemelix)

=> https://www.tutorialspoint.com/nhibernate/nhibernate_inverse_relationships.htm (NHibernate Inverse Relations)

=> https://stackoverflow.com/questions/1150854/saveorupdate-vs-update-and-save-in-nhibernate (Saveorupdate, Save)

=> https://stackoverflow.com/questions/37178455/nhibernate-difference-between-queryt-gett-and-loadt

=> https://www.c-sharpcorner.com/article/sql-server-2019-download-and-installation/ (download SQL 2019)

=> https://social.msdn.microsoft.com/Forums/en-US/4d6158a1-b601-428c-aad4-a1716e76de1a/install-sql-server-problem?forum=sqlexpress (Clean SQL Server 2008 r2 manually)

//SQL Joins

https://www.youtube.com/watch?v=0OQJDd3QqQM

//Automapping

https://tpodolak.com/blog/2013/03/25/fluent-nhibernate-automappings/

https://fluentnhibernate.fandom.com/wiki/Auto_mapping#Abstract_base-classes

https://steemit.com/utopian-io/@haig/nhibernate-tutorial-3-using-the-auto-mapping-feature

//SQLite tutorial

https://zetcode.com/csharp/sqlite/

https://www.w3schools.com/sql/default.asp

//postgreSQL database

https://www.postgresqltutorial.com/

https://www.atdatabases.org/

//Dot Net SDK, you may need to create sample

https://dotnet.microsoft.com/en-us/download/visual-studio-sdks?utm_source=getdotnetsdk&utm_medium=referral (different .Net .Net Core version)

Ask questions related to Hemelix sample code and design at Google group https://groups.google.com/g/hemelix