c# - Compare single elements in a List<DataTable> to another List<DataTable>

Question

I am trying to track changes in a database by running a query pre-change and taking all of the database values and putting them into a DataTable. Each row is put into its own DataTable, then the DataTables are added into a List<>. The same process is run again after a user update is processed. I end up with something like this:

|   | oldData<record "1"> | <record "2"> |  | newData<record "1"> | <record "2"> |
|:-:|:-------------------:|:------------:|::|:-------------------:|:------------:|
|   | record "1"          | record "2"   |  | record "1"          | record "2"   |
| a | 11330002            | 14740032     |  | 11330002            | 14740032     |
| b | null                | null         |  | E                   | E            |
| c | true                | true         |  | true                | true         |
| n | ...                 | ...          |  | ...                 | ...          |

I populate the List I use the below method pre-change to get oldData. The user makes changes then part of the save, after the update/insert SQL statement runs I run the method again to get the post-change List newData.

public static List<DataTable> buildDataTable()
        {
            List<DataTable> JobNumberList = new List<DataTable>();

            string conString = "Data Source=nf-sql1;Initial Catalog=Tooling;Integrated Security=True";
            //List<DataTable> currentData = new List<DataTable>();
            DataTable currentData = new DataTable();

            using (SqlConnection con = new SqlConnection(conString))
            {
                con.Open();
                foreach (string number in updateList)
                {
                    string selectQry = String.Format(@"SELECT *
                                            FROM Tooling.dbo.MoldingData
                                            WHERE JobNumber = {0}", number);

                    using (SqlCommand command = new SqlCommand(selectQry))
                    {
                        command.Connection = con;

                        var resault = command.ExecuteReader();
                        currentData.Load(resault);
                    }

                    JobNumberList.Add(currentData);
                }
                return JobNumberList;
            }
        }

The goal is to create a record showing that null (oldData<1>.[b]) changed to E (newData<1>.[b]) and that null (oldData<2>.[b]) changed to E (newData<2>.[b]). This will be but into a string that will be inserted into an SQL table. That will say something like:

11330002 record b changed from "null" to "E".

14740032 record b changed from "null" to "E".

I know I will need some sort of foreach loop to tunnel into each of the elements in the list with a nested foreach look to look at each of the elements in the DataTable. I am struggling with getting the Datatable elements out to work with. As of right now, this is what I have.

foreach(DataTable oldListElement in oldData)
        {
            foreach(DataRow oldDTelement in oldListElement.Rows)
            {
                oldElement = oldDTelement[0].ToString();
                foreach(DataTable newListElelemt in newData)
                {
                    foreach(DataRow newDTelement in newListElelemt.Rows)
                    {
                        newElement = newDTelement[0].ToString();
                        if (!oldElement.Equals(newElement))
                        {
                            changeString = String.Format("{0} changed to {1}", oldElement, newElement);
                            Console.WriteLine(changeString);
                        }

Am I getting close? This is baked into an application that is written in C#.

question from:https://stackoverflow.com/questions/65850697/compare-single-elements-in-a-listdatatable-to-another-listdatatable

Answer 1

Waitting for answers