Siemens PLC Database Interface Development: Seamless Integration of Industrial Data and Enterprise Information Systems

Storing PLC data into a database may seem simple, but there are many pitfalls. After years of working on automation projects, I’ve encountered this kind of requirement the most. Today, I will discuss this topic and share my practical experience and various precautions.

System Architecture Selection

For such projects, the architecture scheme is the first decision to make. The common options are:

1. Direct connection of PLC to database – using Siemens’ CP communication card

2. OPC scheme – PLC data goes to OPC first, then stored in the database

3. Middleware scheme – writing a program for data transfer

Honestly, I do not recommend the first option. Although direct connection with the CP communication card is convenient, it lacks flexibility, and changing the data structure can be troublesome. I personally prefer the middleware scheme, writing a program for data collection and processing, and then storing it in the database.

Communication Protocol Selection

For the PLC side, the S7 protocol is my first choice. Using Siemens’ own protocol ensures stability. I’ve previously used the open-source libraries Snap7 and Sharp7, both of which are quite useful. However, before development, be sure to check the PLC model and firmware version, as some older devices may not support the optimized S7 protocol.

Data Structure Design

This area is the easiest to have issues. I’ve seen too many projects struggle with data structure changes later on.

On the PLC side, it’s best to organize data blocks (DB) by functional modules, such as one DB for device status, one for production parameters, and one for alarm information. The database side also needs to design the table structure accordingly.

Data type conversion is also a pitfall. The REAL type in the PLC may have precision issues when stored in the database, so make sure to do type mapping and value range checks.

Real-time Processing

Many people jump straight to collecting all data in real-time, resulting in CPU spikes on the server and database write delays. My suggestions are:

1. Tiered collection – collect important data at high frequency, ordinary data at low frequency

2. Batch writing – collect a batch of data and write to the database together

3. Set up caching – cache locally when the database has issues

In one project I worked on, the client requested data collection every 100ms. During testing, we found the database couldn’t handle it, so we changed to collect data based on changes, which solved the problem.

Error Handling

This is really important. I often see programs crash when the network is interrupted, and even restarting cannot recover. Error handling must consider these situations:

• Network interruption

• Database connection failure

• PLC communication exception

• Data type conversion error

• Insufficient disk space

Each type of exception should have a corresponding handling mechanism and recovery strategy. I usually create an error log to record detailed information for later maintenance.

Code Implementation

// Data collection main program example
while (true)
{
    try
    {
        // Read PLC data
        var plcData = plcClient.ReadDB(1, 0, 100);
        // Data processing and conversion
        var processedData = ProcessData(plcData);
        // Cache data
        dataCache.Add(processedData);
        // Write to database when batch size is reached
        if (dataCache.Count >= batchSize)
        {
            using (var conn = new SqlConnection(connString))
            {
                conn.Open();
                using (var trans = conn.BeginTransaction())
                {
                    try
                    {
                        // Batch insert into database
                        BulkInsert(dataCache, conn, trans);
                        trans.Commit();
                        dataCache.Clear();
                    }
                    catch
                    {
                        trans.Rollback();
                        throw;
                    }
                }
            }
        }
    }
    Thread.Sleep(scanInterval);
}
catch (Exception ex)
{
    LogError(ex);
    // Exception recovery handling
    Recovery();
}

Would you like me to explain or break down the code?

Maintenance Experience

Maintenance after the program goes live is also crucial. It is recommended to do the following:

1. Monitor system performance

2. Regularly clean up historical data

3. Backup important data

4. Record system logs

I remember once the database hard drive at the production site was full, and no one noticed, resulting in data loss for several days. Since then, I’ve been particularly careful to add monitoring and alerting features.

Practical Training

You can first set up a test environment:

• Install a Siemens PLC simulator

• Create a local database

• Write a simple collection program using C# or Python

Start with simple data collection, gradually adding error handling, data processing, and other features.

What experiences and ideas do you have when developing similar projects? Feel free to share and discuss in the comments.