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.
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# 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.
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.
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 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.
The classic example of store example has been converted by using Fluent Nhibernate and SQLite 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 by NHibernate. Continue reading.
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
//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
//Dot Net SDK, you may need to create sample