SQL Server Installation to PC and PLC

Install and Configure SQL Sever for Automation project

Capturing data from the devices are very important for automation project. There are many ways of capturing, processing, and storing data. It can be stored in the local database or can be stored in the cloud and then it can be processed there. In this tutorial, we shall focus on how to capture data by using the Microsoft SQL Server.

Downloading and installing SQL server and management studio is not difficult. But the configuration of the SQL server can be tricky and in the worst case, it can be frustrating. I have sometimes tried similar computers with different results.

=>Download SQL server developer edition (or express edition) from https://www.microsoft.com/en-us/sql-server/sql-server-downloads 

=>Download SQL Server Management Studio from https://docs.microsoft.com/fi-fi/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-ver15

 

If the installation has been successfully done, we see a similar screenshot.

 

=> After installing SQL Server, customize it.

=> Now follow these to continue the SQL configuration

Open SQL server management studio and login as windows authentication and select the properties of login name sa. 

Follow the following steps. We may not need all steps, but good to follow these.

By default, Windows Authentication mode is active, we need to activate SQL Server and Windows Authentication mode.

=> Now we need to create a user  and password for the database

=> In management studio, create login | new login, this provides the following dialog

=> If we don’t have user and password then we can’t create tables by using script

=> Now select the root of SQL server in the management studio | properties and make sure that SQL Server and Windows Authentication is selected.

=> In the server role page, allow all, meaning select all permission

=> In the status page grant login and connection permission to the database. Now the user should be able to connect to the database by the program.

=> As a final step, select the SQL server and properties | Security | SQL Server and authentication mode.

You might need to put windows fire wall OFF or need to open particular port (not really sure :-))


What could be the reason for this? 

See solution at https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/troubleshoot-connecting-to-the-sql-server-database-engine?view=sql-server-ver15 


A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.

If the above error remains then check the following issue, TCP/IP port number.

Check all the TCP port for all IP and all  and set it to 1433

Now all should be fine. We are able to use SQL server with TwinCAT HMI.

=> If you want to drop a user you could execute the following command in SSMS 

DROP LOGIN testUser ;

If we want to know the current SQL version running in this machine, execute the following command in management studio.

SELECT @@VERSION

If no user has been created but you are trying to use the user by script then we get the following message.

Msg 15007, Level 16, State 1, Server DESKTOP-0GSQE8L, Line 2

‘databaseusername’ is not a valid login or you do not have permission.

Now our database is ready and we shall be able to connect to the database.

Create a Database :

 

By using script

We can create a database by using a script or by using MS SQL server management studio. Download the script (before using please check the content as a good practice) from this link, and unzip it.

=> Take a command prompt as admin and execute as follows:

If there is no such database (using for the first time)

C:\HomeAutomation\SQL_Database\Db>initmydatabase.bat 

If there is already the same database (you are recreating it for example)

C:\HomeAutomation\SQL_Database\Db>initmydatabase.bat -r

If we want to run the script automatically by executing by C# code, we can use the following code.

 

 System.Diagnostics.Process proc2 = new System.Diagnostics.Process();
 proc2.EnableRaisingEvents = false;
 proc2.StartInfo.FileName = "initmydatabase.bat";
 proc2.Start();
 Thread.Sleep(1000);

If you want to recreate the database then use -r option. If you don’t have the database already then you can execute the batch file without any option. If you have already same database and you don’t have  to use r option.

By using management studio

Using management studio is straight forward, follow the graphical menu, we shall add it later.

 

As a final check, we need to match our Database as shown below. MyDatabase is empty, no table yet, my user is visible.

 

Continue with Data Modelling

I found a tutorial on Fluent Nhibernate on YouTube, I did not see the source code anywhere but I made and adapted the sample and that can be downloaded from this link.

As we are progressing this tutorial for our fictitious home automation system, in the mean time we can take a look in this site:

https://github.com/FluentNHibernate/fluent-nhibernate/wiki/Getting-started

https://fluentnhibernate.fandom.com/wiki/Auto_mapping

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

The sample provided by the  Fluent NHibernate that I adapted to my version 15.0.2000.5,  the working sample can be download from this link.

public class Employee
{
  public virtual int Id { get; protected set; }
  public virtual string FirstName { get; set; }
  public virtual string LastName { get; set; }
  public virtual Store Store { get; set; }  //a reference to the Store that they work in.
}
public class EmployeeMap : ClassMap<Employee>
{
  public EmployeeMap()
  {
	Id(x => x.Id); // x is an instance of Employee
	Map(x => x.FirstName);
	Map(x => x.LastName);
	References(x => x.Store);  // References creates a many-to-one relationship between two entities (many x in a store). 
                                                           //a many-to-one to Store (many Employees   to one Store) through the Store property. } }

TIP 01 :  If we create a DLL then we need to make sure to use target framework as (.NET Framework). If we use other version for example .NET standdard, it will not work.

TIP 02 :  How to query numbers of table in the database (execute the following when database has been created)

select count(*) as [tables]
   from sys.tables 

TIP 03 :  

https://fluentnhibernate.fandom.com/wiki/Auto_mapping

https://weblogs.asp.net/ricardoperes/deleting-entities-in-nhibernate

https://nhibernate.info/doc/nhibernate-reference/queryqueryover.html

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


We shall create our home automation system for loading data to SQL Server and later post it to the cloud for generating reports.

See also:  https://www.hemelix.com/automation/structured-text-mqtt/

 

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