I’m trying to clean and afterwards insert a table via OPC. (dont want to buy premium SQL server for agent). I get no compile errors… But when I trigger I get the following error: “Test_Sander” could not be built due to errors in the source code. I did some things but I dont get out of it. Does anyone have an advice or can help me? If more info is needed please comment. Thanks in advance!

This is my code:

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
// Check if these namespaces are correct and necessary
using inray.OPCRouter.ScriptPlugIn.Shared;
using inray.OPCRouter.ScriptPlugIn.Runtime;
using inray.OPCRouter.ScriptPlugIn.Runtime.Trigger;
using System.Data.SqlClient;

namespace OPCRouter.Script
{

    //public class Test_Sander : ScriptTransferObjectBase
    public class Test_Sander
    {
        private string masterServer = "XXX";
        private string masterDatabase = "MASTER_DB";
        private string prodlogServer = "XXX";
        private string prodlogDatabase = "test";
        private string username = "XXX";
        private string password = "XXX";
        private SqlConnection masterConn;
        private SqlConnection prodlogConn;
        private SqlCommand masterCmd;
        private SqlCommand prodlogCmd;

        // Temporarily change or remove override if not inheriting from ScriptTransferObjectBase
        public void Initialize()
        {
            OpenConnections();
        }

        private void OpenConnections()
        {
            masterConn = new SqlConnection(String.Format("Server={0};Database={1};User Id={2};Password={3};", masterServer, masterDatabase, username, password));
            masterConn.Open();
            masterCmd = masterConn.CreateCommand();

            prodlogConn = new SqlConnection(String.Format("Server={0};Database={1};User Id={2};Password={3};", prodlogServer, prodlogDatabase, username, password));
            prodlogConn.Open();
            prodlogCmd = prodlogConn.CreateCommand();
        }

        private void CloseConnections()
        {
            masterCmd.Dispose();
            masterConn.Close();
            prodlogCmd.Dispose();
            prodlogConn.Close();
        }

        // Temporarily change or remove override if not inheriting from ScriptTransferObjectBase
        public void Write()
        {
            CleanShiftTable();
            CopyRawDataToShift();
        }

        private Tuple<DateTime, DateTime> GetShiftDatetimes(DateTime currentDateTime)
        {
            DateTime shiftStartDateTime, shiftEndDateTime;
            if (currentDateTime.Hour < 6)
            {
                shiftStartDateTime = new DateTime(currentDateTime.Year, currentDateTime.Month, currentDateTime.Day, 22, 0, 0).AddDays(-1);
                shiftEndDateTime = currentDateTime;
            }
            else if (currentDateTime.Hour < 14)
            {
                shiftStartDateTime = new DateTime(currentDateTime.Year, currentDateTime.Month, currentDateTime.Day, 6, 0, 0);
                shiftEndDateTime = shiftStartDateTime.AddHours(8);
            }
            else if (currentDateTime.Hour < 22)
            {
                shiftStartDateTime = new DateTime(currentDateTime.Year, currentDateTime.Month, currentDateTime.Day, 14, 0, 0);
                shiftEndDateTime = shiftStartDateTime.AddHours(8);
            }
            else
            {
                shiftStartDateTime = new DateTime(currentDateTime.Year, currentDateTime.Month, currentDateTime.Day, 22, 0, 0);
                shiftEndDateTime = currentDateTime;
            }
            return new Tuple<DateTime, DateTime>(shiftStartDateTime, shiftEndDateTime);
        }
        
        
        private void CleanShiftTable()
        {
            try
            {
                string sqlQuery = "DELETE FROM [MASTER_DB].[dbo].[SHIFT]";
                SqlCommand command = new SqlCommand(sqlQuery, masterConn);
                command.ExecuteNonQuery();
                Console.WriteLine("SHIFT table cleaned.");
            }
            catch (SqlException ex)
            {
                Console.WriteLine(String.Format("Error cleaning SHIFT table: {0}", ex.Message));
            }
        }

private void CopyRawDataToShift()
{
    try
    {
        DateTime currentDateTime = DateTime.Now;
        var shiftTimes = GetShiftDatetimes(currentDateTime);
        DateTime shiftStartDateTime = shiftTimes.Item1;
        DateTime shiftEndDateTime = shiftTimes.Item2;
    
        string sqlQuery = @"
        INSERT INTO [MASTER_DB].[dbo].[SHIFT] (Timestamp, [Transaction], SerialNr, PN, CustomerNR, Status, Workcenter, Reason, [Return], Recipe, Raw Material, Quantity, OPS_Timestamp, Cycle, Delta_Timestamp)
        SELECT *
        FROM [test].[dbo].[PRODLOG2]
        WHERE Timestamp BETWEEN @shiftStart AND @shiftEnd";
    
        prodlogCmd.Parameters.Clear();  // Clear existing parameters
        prodlogCmd.CommandText = sqlQuery;
        prodlogCmd.Parameters.AddWithValue("@shiftStart", shiftStartDateTime);
        prodlogCmd.Parameters.AddWithValue("@shiftEnd", shiftEndDateTime);
        prodlogCmd.ExecuteNonQuery();
    
        Console.WriteLine("Raw data copied to SHIFT table successfully.");
    }
    catch (SqlException ex)
    {
        Console.WriteLine(String.Format("An error occurred: {0}", ex.Message));
    }
}

    }
}