Display Data from SQLite to TwinCAT HMI
Why to use SQLite
SQLite is an embeddable open source database, written in C and queryable with conventional SQL, that is designed to cover those use cases. SQLite is designed to be fast, portable, and reliable, whether we are storing only kilobytes of data or multi-gigabyte blobs.
SQLite’s greatest advantages is that it can run on major platforms. SQLite has been ported to a wide variety of platforms: Windows, macOS, Linux, iOS, Android, etc.
We have already seen in some examples articles with MSSQL Server. It is quite heavy for storing a small amount of data. You need to install the SQL server, Management Studio and then you can save/retrieve data from the server.
We are talking about TwinCAT HMI, we need to store a small amount of data for HMI. This article will convert the example found in https://www.hemelix.com/sql/nhibernate-and-dll/ and adapt it for TwinCAT HMI.
We shall use Fluent NHibernate. 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.
What Software we need before continuing
=> FluentNHibernate from Nuget
=> System.Data.SQLite.Core from Nuget
=> Newtonsoft.Json from Nuget
=> Be familiar with FluentNHibernate (https://www.hemelix.com/sql/)
=> Build an TwinCAT HMI (https://www.hemelix.com/scada-hmi/twincat-hmi/my-first-twincat-hmi/)
=> Create a C# Extension project (https://www.hemelix.com/scada-hmi/twincat-hmi/twincat-hmi-server-extension/)
How List can be saved as string
We can declare a variable which can save our data.
We have an object like the following in HMI. We want to save these data to database as string to save processing time.
//A list of object should be saved to DB
let obj = { startTime : "", stopTime : "", maxSpeed : 0, runTimeAtMaxSpeed : 0, numberOfSpeedChange : 0
}
//Declare a class in the DB module
public class Data
{
public virtual int Id { get; protected set; }
public virtual string DataString { get; set; } // This will hold the data as string (list of abobe obj)
}
//Save the data to DB
Data d = new Data();
d.DataString = command.WriteValue;
sqlConnector.AddItem(d); //We delete any existing data, since DataString will be saved
//When we are reading back we can do the following.
string s = "";
var list = session.QueryOver<Data>().List();
s = list[0].DataString;
var data = await ReadPLCVariable('%s%ServerExtensionCSharpEmpty1.GetDataValue%/s%'); // Read from HMI
var obje = JSON.parse(data);
Tips
Tips 01: no persister for: Fluent NHibernate Exception Error
This error arises from the mapping of the array for example. The following example will produce this error
public class Data
{
public virtual int Id { get; protected set; }
public virtual string Test1 { get; set; }
}
public class DataMap : ClassMap
{
public DataMap()
{
Id(x => x.Id);
Map(x => x.Test1);
Table("DataTable");
}
}
private static void MainFN()
{
using (var session = sessionFactory.OpenSession())
{
using (var transaction = session.BeginTransaction())
{
Data[] balance = new Data[12];
for (int i = 0; i < 12; i++)
{
balance[i] = new Data();
balance[i].Test1 = "Example Data " + (i + 1).ToString();
session.SaveOrUpdate(balance[i]); //Should be like this
}
//session.SaveOrUpdate(balance); // Causes no persister for: Fluent nHibernate Exception
The above error can occur in the case if we have not mapped our class also. For example, we don’t have the class DataMap
Tips 02:
If we configure the database in the following way then we can have the DB file in a particular folder. Otherwise, it will create the DB file in the c:\programdata folder somewhere. We can write with the full path instead of a single DB file name.
if (!File.Exists(@”C:\TwinCAT\Functions\TF2000-HMI-Server\projectData.db”))
Tips 03:
Initializing[ServerExtension.MyObject#2]-failed to lazily initialize a collection of roles: ServerExtension.MyObject.another object, no session or session was closed
The above error occurred when we are trying to use the object which was not loaded yet and the session was closed already.
using(var session = NHibernateHelper.OpenSession()) {
using(var transaction = session.BeginTransaction()) {
IList < Car > cars = session.QueryOver < Car > ().Where(c =>c.Id == deleteID).List();
if (cars.Count > 0) {
session.Delete(cars[0]);
session.Flush();
}
}
}
//now we are trying to use the car’s model which was not loaded yet but function was returning (session was closed).
console.write(car.Make); // will through the above message
Tips 04: How to pass JavaScript Object to extension with array
const person = {
Test1: test1.getText(),
Test2: parseInt(test2.getText()),
Test3: checkBoxStateChecked,
CurrentTime: timeString,
ComboBox : comboboxState
};
Now say, Test3 is an array of object and we need to pass to extnsion or other module. We can do like the following:
person.Test2 = the object array or
const person = {
Test1: test1.getText(),
Test2: the object array
Test3: checkBoxStateChecked,
CurrentTime: timeString,
ComboBox : comboboxState
};
var myJSONString = JSON.stringify(person);
These two cases will generate different types of data! Check it out or do testing
Download the sample from the link given above.
Next, let’s try to use ADS and Extension to HMI at https://www.hemelix.com/scada-hmi/twincat-hmi/beckhoff-twincat-hmi-javascript-framework-verses-extension-module/
Ask questions related to Hemelix sample code and design at Google group https://groups.google.com/g/hemelix