How to update a SharePoint Online list if a new record is inserted in the database?

Question

I have a MySQL database that holds personal information. Whenever a new employee gets hired he/she fills out some personal information and that data gets stored in a table.

After some research (and since I don't have access to the other systems -only the database) the plan is to build a C# console app that retrieves the data and check it against the SharePoint list. I want to update the list (create a new item) if a new record is in the database that does not exist in the SharePoint list from before.

Note that if the SharePoint list contains more columns, then the table with additional manual information.

I have posted the connection code against the database and how I retrieve the data.

How can I check if the item exists in the SharePoint list? Would anybody be able to provide an answer that includes code for creating and inserting the new item? I have two columns (in both the database and SP list) that could work as a primary key.

There is a REST API that supports CRUD so I guess this should be a no-brainer.

SharePoint list:

using System;
using System.Windows;

public class DbConnection
{
    private String databaseName;
    private String serverAddress;
    private String pwd;
    private String userName;
    private Boolean connected;
    private MySql.Data.MySqlClient.MySqlConnection conn;

    public DbConnection(String databaseName, String serverAddress, String pwd, String userName)
    {
        this.databaseName = databaseName;
        this.serverAddress = serverAddress;
        this.pwd = pwd;
        this.userName = userName;
        connected = false;
    }

    public void Connect()
    {
        if (connected == true)
        {
            Console.Write("There is already a connection");
        }
        else
        {
            connected = false;
            String connectionString = "server=" + serverAddress + ";" + "database=" + databaseName + ";" + "uid=" + userName + ";" + "pwd=" + pwd + ";";
            Console.WriteLine(connectionString);

            try
            {
                conn = new MySql.Data.MySqlClient.MySqlConnection(connectionString);
                conn.Open();
                Console.Write("Connection was succesfull");
            }
            catch (MySql.Data.MySqlClient.MySqlException ex)
            {
                 MessageBox.Show(ex.Message);
            }
        }
    }

    public Boolean IsConnected()
    {
        return connected;
    }

    public MySql.Data.MySqlClient.MySqlConnection getConnection()
    {
        return conn;
    }

    public void Close()
    {
        conn.Close();
        connected = false;
    }
}

Then I retrieve the data like so:

using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace daily_CC_SP_update
{
    class Program
    {
        static void Main()
        {
            DbConnection mySQLConn = new DbConnection(dbName, serverAddress, pwd, userName);
            mySQLConn.Connect();

            string sqlQuery = "SELECT * FROM tbl_CC_SP";
            MySqlCommand sqlCom = new MySqlCommand(sqlQuery, mySQLConn.getConnection());
            MySqlDataReader reader = sqlCom.ExecuteReader();

            Console.WriteLine("Following output from DB");
            if(reader.Read())
            {
                Console.WriteLine(reader.GetString(0));
            }

            //Keep the console alive until enter is pressed, for debugging
            Console.Read();
            mySQLConn.Close();

        }
    }
}

I will create a view in the database to retrieve the correct data.

Answer 1

The solution is to set the items["LockUpColumn"] not to a string but a lockup field