Connecting an PLC Siemens S7-1500 to an SQL Server Database

After a lot of searches and reading documents, I found out Siemens still developing the MSSql Server Module, and now, it isn't completed. The data will be inside the Receivebuffer: InstLsql_Microsoft.statRcvData.bytes[]. But you have to search for your information.

But still, the best way to read and write on PLC Registers is using S7netplus library (A .NET Library for Siemens S7 Connectivity). S7.Net Plus is a continuation of the work done on the S7.Net project by Juergen1969. I found the library simple and effective, but the project has languished unchanged since late 2009.

Download and install on C# Project:

PM> Install-Package S7netplus -Version 0.4.0

To create an instance of the driver you need to use this constructor:

public Plc(CpuType cpu, string ip, Int16 rack, Int16 slot)

Example:

This code creates a Plc object for a S7-300 plc at the IP address 127.0.0.1, for a plc in rack 0 with the cpu in slot 2:

Plc plc = new Plc(CpuType.S7300, "127.0.0.1", 0, 2);

Connecting to the PLC

public void Open()

For example this line of code open the connection:

plc.Open();

Disconnecting from the PLC

public void Close()

For example this closes the connection:

plc.Close();

Read bytes / Write bytes

The library offers several methods to read variables. The basic one and the most used is ReadBytes.

public byte[] ReadBytes(DataType dataType, int db, int startByteAdr, int count)

public void WriteBytes(DataType dataType, int db, int startByteAdr, byte[] value)

This reads all the bytes you specify from a given memory location. This method handles multiple requests automatically in case the number of bytes exceeds the maximum bytes that can be transferred in a single request.

dataType: you have to specify the memory location with the enum DataType

public enum DataType
{
    Input = 129,
    Output = 130,
    Memory = 131,
    DataBlock = 132,
    Timer = 29,
    Counter = 28
}

db: the address of the dataType, for example, if you want to read DB1, this field is “1”; if you want to read T45, this field is 45. startByteAdr: the address of the first byte that you want to read, for example, if you want to read DB1.DBW200, this is 200. count: contains how many bytes you want to read. Value[]: array of bytes to be written to the plc. Example: This method reads the first 200 bytes of DB1:

var bytes = plc.ReadBytes(DataType.DataBlock, 1, 0, 200);

More Documention is here:
S7.Net documentation

Noticable Update!!!

Siemens recently (11/20/2020) published a document about Connecting S7-* series to Microsoft SQL Server Database, directly and with NO middle-ware like OPC Servers or S7netplus package. It means you can run SQL queries like select and insert via an internal function in PLC.
This is a faster & native solution.

First, via TiaPortal I made a string array in a Program block.

You should set up connection strings like shown in the picture below, which the IP Address of Microsoft Sql Server is 172.16.62.1:

Then I made a built-in PLC function by following instruction in these pdf published by Simense.

I have a bunch of queries in my predefined string array, I choose one of them by its index as an input parameter to the PLC function.
then I downloaded changes to PLC. as a result I have hello world in MSSQL Server. enter code here By far I ran a SQL Server Query by PLC itself!
Let's run another query and the hardest one: select.
I said the hardest because select has a bunch of records with various data type: like varchar, bool, DateTime.

The "select" instruction Using the "select" instruction, you can read data out of a database table and perform other operations on them in your controller. Below, we use an example to show how this instruction works and which modifications you will need to make for your query. The example uses the following query:

Select * from Mytable

The data queried are stored in the "SqlReceive" data block in the data type structure "typeUseCaseSpecificData". You must modify these data types individually for each query. You can do this as follows: 1. Start a Wireshark recording to find the packet length of the metadata. 2. Run the query "Select Amount from PLCDATA_2 where Fruit = $'Apple$'". 3. Stop the Wireshark recording and search for the response frame from the SQL server using the filter "TDS". 4. Select the frame and click on the line "Token – ColumnMetaData".

Ok, Now I wanna choose select query in the sqlqueries array:

SELECT _barcode FROM mytable where id = 4

Again I Active/deactive the input then I get my result, here I get 'Peyman Majidi' string by its id.

Output:

'Peyman Majidi'

I can show you how it works by Wireshark by listening to tds protocol.
The Tabular Data Stream protocol (TDS) gives you the ability to establish a direct connection with a Microsoft SQL server. Using TDS, you can log in to an SQL server database and transmit SQL instructions. In this way it is possible to read data from the database, or send them to the database for storage.