Databases play a crucial role in industrial automation, acting as the central nervous system of the system. They collect, store, and organize large volumes of data from sensors, machines, and control systems, enabling real-time monitoring, informed decision-making, and process optimization.
SQL databases are commonly used to manage data generated by automation systems. This data can be stored locally or in the cloud using different approaches, depending on system requirements. In our fictitious automation system, we will use a PostgreSQL server as the database solution.
In this tutorial, we will learn how to install an SQL server on a local machine or on other devices, such as a PLC. The tutorial is divided into two main parts:
Installing the MS SQL Server
Using the SQL server within an automation project in the TwinCAT HIMI environment
To fully understand how databases integrate with automation systems, we will also explore basic data modeling concepts through sample applications.
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 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.
The best way to understand fluent NHibernate is to make a simple application by using C# on 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.
To interact with our system effectively, we need to model it in a way that allows us to read and write data, as well as update the system based on events. The first step is to identify the relationships between the different entities in the system.
For this purpose, we will use the Fluent NHibernate module. By using this module, we do not need to write SQL statements manually, as it handles database interactions for us.
We will provide practical examples to help illustrate and clarify the data modeling concepts.
Continue reading…
We continue working with Fluent NHibernate. In this step, we convert the application into a Windows Forms application and a DLL. All core functionalities have been moved into the DLL. Continue reading.
Display Data to TwinCAT HMI from SQL Server
To interact effectively with our system, we need to model it so that we can read and write data, as well as update the system based on events. The first step is to identify the relationships between the different entities in the system.
For this purpose, we will use the Fluent NHibernate module. By using this module, we do not need to write SQL commands manually, as it handles database interactions for us.
We will provide a complete example with full source code to demonstrate how data can be retrieved from an SQL server and displayed in TwinCAT HMI using JSON. Continue reading.
The classic store example has been converted using Fluent NHibernate with SQLite as the database. Continue reading.
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 with NHibernate. Continue reading.
PostgreSQL, also known as Postgres, was developed by Michael Stonebraker at the University of California, Berkeley. It is a powerful open-source database system.
In this tutorial, we will explore how PostgreSQL can be integrated with an automation system. We will store data from a PLC into a PostgreSQL database and generate reports based on that data. The database will then be accessed using Node.js to retrieve and process the information.
We need different commands for executing SQL statements. In this article, we gather those, for example, Aliasing, different kinds of joins, and CSV to SQL files. Continue reading.
Historian VS Relational
We shall understand the differences between historian and relational database. Which one is fits where.
Historian VS Relational Database
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://bejamas.com/hub/serverless-database/supabase
//Dot Net SDK, you may need to create sample
Ask questions related to Hemelix sample code and design at Google group https://groups.google.com/g/hemelix