Display Data from MS SQL to TwinCAT HMI
Preconditions:
=> Database has been installed according to https://www.hemelix.com/sql/sql-server-installation/
=> Database has been created with the following connection string information:
"Server=(local);initial catalog=MyDatabase; user=my_user_id;password=my_password;"
We shall add several complete samples that will help us to learn the data model. We shall show how data from the sensor can be stored in the database. We shall create reports based on the data accumulated in the SQL database.
We shall use Fluent NHibernate in all of our projects. Fluent NHibernate is a DLL that can be linked to our projects and all SQL data can be saved or updated or even deleted without knowing the different SQL syntaxes. Of course, we can write our SQL syntaxes but those are error-prone and our focus will be in another direction than the automation.
Fluent NHibernate offers an alternative to NHibernate’s standard XML mapping files. Rather than writing XML documents, you write mappings in strongly typed C# code. This allows for easy refactoring, improved readability, and more concise code.
Sample 1
Conventional store and worker (adapted from https://github.com/nhibernate/fluent-nhibernate/wiki/Getting-started)
For this example, we’re going to be mapping a simple domain for a retail company. The company has a couple of stores, each with products in (some products are in both stores, some are exclusive), and each with employees. In database terms, that’s a Store, Employee, and Product table, with a many-to-many table between Store and Product.
First, we need the necessary libraries that support Windows, normally we can add those by Visual Studio Nuget Packages Management or we can download the libraries and use them for our project. Normally, I create a folder called Lib in my projects and I store it there. We shall store those libraries which come as part of Nuget Packages in the Lib folder.
In this example, we shall display the data from the SQL server to our TwinCAT HMI and give the user options to add, delete, and update the SQL database.
The main UI of the sample application is like this:
The application reads 4 tables (Employee, product, store, and store product).
Load From Database => Loads all the contents from SQL Server
Edit From Database => Update the selected data based on store ID from the SQL Server
Delete From Database => Delete the data from the database. If the store is selected then the store is deleted as well
Save To Database => Save all data from the edit box to SQL Server.
(No error checking or status of the operation is shown)
How the data grid is managed:
We shall show how TcHmiDatagrid for the employee table is managed and linked to the Database. If we press F4 while selected the grid then we find columns and srcData as seen in the following image. Columns indicate how the data will be formatted in the table whereas srcData is the actual data in JSON format.
using (var session = NHibernateHelper.OpenSession())
{
using (var tx = session.BeginTransaction())
{
productList = (List<Product>)session.QueryOver<Product>().List<Product>();
employeeList = (List<Employee>)session.QueryOver<Employee>().List<Employee>();
storeList = (List<Store>)session.QueryOver<Store>().List<Store>();
numberOfStore = storeList.Count;
var employeeResult = from p in employeeList
select new { emplid = p.Id, emplfirstname = p.FirstName, empllastname = p.LastName, emplstoreid = p.Store.Id };
//Anonymous object with properties as it matches with the srcData and columns used as JSON.
employeeListJson = JsonConvert.SerializeObject(employeeResult);
//employeeListJson will be used srcData for the grid passed by extension framework
How data can be inserted from the grid to edit box:
There are several data grid in the UI. Those have an id for JavaScript method. We need to configure the grid for calling method as shown in the following picture. Grid identifier, row index and column index are passed to the JavaScript method.
Java Script function for retrieving data from the grid and inserting it to the text editor.
(function (TcHmi) {
var FunctionJS_GridItemClicked = function (GridIdentifier, SelectedRow, SelectedColumn) {
switch (GridIdentifier) {
case 0:
{
var myControl = TcHmi.Controls.get('TcHmiDatagrid_Employee');
if (myControl) {
var dataa = myControl.getSrcData();
var emplid = dataa[SelectedRow].emplid;
var emplfirstname = dataa[SelectedRow].emplfirstname;
var empllastname = dataa[SelectedRow].empllastname;
var emplstoreid = dataa[SelectedRow].emplstoreid;
var firstNameControl = TcHmi.Controls.get('TcHmiTextbox_FirstName');
if (firstNameControl) {
firstNameControl.setText(emplfirstname);
}
var lastNameControl = TcHmi.Controls.get('TcHmiTextbox_LastName');
if (lastNameControl) {
lastNameControl.setText(empllastname);
}
var storeControl = TcHmi.Controls.get('TcHmiCombobox_Store');
if (storeControl) {
storeControl.setSelectedIndex(emplstoreid);
}
}
}
break;
case 1:
{
var myControl = TcHmi.Controls.get('TcHmiDatagrid_Product');
if (myControl) {
var dataa = myControl.getSrcData();
var prdid = dataa[SelectedRow].prdid;
var prdname = dataa[SelectedRow].prdname;
var prdprice = dataa[SelectedRow].prdprice;
//console.log(prdid);
//console.log(prdname);
//console.log(prdprice);
var productNameControl = TcHmi.Controls.get('TcHmiTextbox_ProductName');
if (productNameControl) {
productNameControl.setText(prdname);
}
var productPriceControl = TcHmi.Controls.get('TcHmiTextbox_ProductPrice');
if (productPriceControl) {
productPriceControl.setText(prdprice);
}
}
}
break;
case 2:
{
var myControl = TcHmi.Controls.get('TcHmiDatagrid_Store');
if (myControl) {
var dataa = myControl.getSrcData();
var strid = dataa[SelectedRow].strid;
var strname = dataa[SelectedRow].strname;
//console.log(strid);
//console.log(strname);
var storeControl = TcHmi.Controls.get('TcHmiTextbox_Storeid_Insert');
if (storeControl) {
storeControl.setText(strname);
}
var storeControl = TcHmi.Controls.get('TcHmiCombobox_Store');
if (storeControl) {
storeControl.setSelectedIndex(strid);
}
}
}
break;
case 3:
{
var myControl = TcHmi.Controls.get('TcHmiDatagrid_StoreProduct');
if (myControl) {
var dataa = myControl.getSrcData();
var productid = dataa[SelectedRow].productid;
var storeid = dataa[SelectedRow].storeid;
//console.log(productid);
//console.log(storeid);
}
}
break;
default:
break;
}
};
TcHmi.Functions.registerFunction('FunctionJS_GridItemClicked', FunctionJS_GridItemClicked);
})(TcHmi);
Download the source code (a couple of functionalities removed from the original thinking due to lack of time).
The following samples are not related to TwinCAT HMI, it is more like a windows application. If you want you could take a look at it. This is more like for understanding database and fluent NHibernate
Sample 2 this almost the same as in this example, but it was the original example found in wiki, but it is written as EXE and DLL
Sample 3 this almost the same as in this example, but it was the original example found in wiki, but it is written as EXE only
Sample 4 this example has a different dataset as compared to the previous 3 examples. I found a YouTube (https://www.youtube.com/watch?v=HQcnLP0iYQo&t=8s) video, I watched and wrote myself the code as shown in the video.
Tips
=> Fluent NHibernate uses different log4net file. If you are using log4net in the extension then, you can’t open the session in the NHibernate. One way to disable the log4net in the extension DLL.
=> Missing Data in UI, command.red value is used for reading data from the extension to the UI. If we put the line inside the write block then the data may be missed. The reason is that write is executed only once.
private ErrorValue ComboIndex(Command command)
{
if (command.WriteValue.IsSet && command.WriteValue.Type == TcHmiSrv.ValueType.ValueType_Int)
{
_data.ComboIndex = command.WriteValue;
List authors = new List();
for (int i = 0; i < _data.ComboIndex; i++)
{
authors.Add((i + 1).ToString());
}
_data.ComboString = JsonConvert.SerializeObject(authors);
} // end of write
command.ReadValue = _data.ComboIndex; // read always and continuously executed.
command.ExtensionResult = ExtensionErrorValue.HMI_EXT_SUCCESS;
return ErrorValue.HMI_SUCCESS;
}
Download the sample from the link given above.
Next, let’s try to understand how to display SQLite data to HMI at https://www.hemelix.com/sql/sqlite-data-modeling/
Ask questions related to Hemelix sample code and design at Google group https://groups.google.com/g/hemelix