Read DataTable and rearrange it while changing timestamp column efficiently

Question

First of all, is my first time asking here, I hope my explanation is good enough.

I have to read from a database a table with this format:

IdSignal	Value	Year	Month	Day	Hour	Minute	Second	Millisecond
1	50.4	2021	06	21	13	25	34	751
2	71.0	2021	06	21	13	25	34	764
3	21.3	2021	06	21	13	25	34	783

And change it to a table that looks like:

Timestamp	1	2	3
2021/06/21 13:25:34.7	50.4	71.0	21.3

I have developed a functional code, but is too slow (I have been able to reduce the execution time, before it was worse, but now I'm stuck):

using (SqlConnection connection = new SqlConnection(connString))
            {
                connection.Open();
                SqlCommand command = new SqlCommand(queryString, connection);
                if ((int)connection.State == 1)
                {
                    MessageBox.Show("Connection opened!");
                }
                else
                {
                    MessageBox.Show("Something went wrong openning the connection! " + connection.State.ToString());
                }

                SqlDataAdapter da = new SqlDataAdapter(command);
                da.Fill(valueTable);
                foreach (DataRow row in valueTable.Rows)
                {
                    DateTime timestamp = new DateTime(row.Field<int>("Year"),
                        row.Field<int>("Month"),
                        row.Field<int>("Day"),
                        row.Field<int>("Hour"),
                        row.Field<int>("Minute"),
                        row.Field<int>("Second"),
                        row.Field<int>("Millisecond"));

                    string idSignal = row.Field<int>("IdSignal").ToString();
                    double value = row.Field<double>("Value");

                    try
                    {
                        if (finalTable.Select().Last().Field<String>("Timestamp") == timestamp.ToString("MM/dd/yyyy HH:mm:ss.f"))
                        {
                            finalTable.Select().Last()[idSignalNames[idSignal]] = value;
                        }
                        else
                        {
                            DataRow tempRow = finalTable.NewRow();

                            tempRow["Timestamp"] = timestamp.ToString("MM/dd/yyyy HH:mm:ss.f");

                            tempRow[idSignalNames[idSignal]] = value;
                            finalTable.Rows.Add(tempRow);
                        }
                    }
                    catch (Exception ex)
                    {
                        DataRow tempRow = finalTable.NewRow();

                        tempRow["Timestamp"] = timestamp.ToString("MM/dd/yyyy HH:mm:ss.f");

                        tempRow[idSignalNames[idSignal]] = value;
                        finalTable.Rows.Add(tempRow);
                        //System.Console.WriteLine("The first row has been added to finalTable");
                    }

Now, the elapsed time is 3.5 seconds

Thanks!

EDIT 1

Can you describe in words what you are trying to achieve there? What is column 1,2 and 3 in that result table? – Tim Schmelter

The first table, the one from the DB, has a row for each IdSignal, in total there are 9 IdSignal. This table has over 20k rows, repeating measures for each IdSignal, i.e. 1,2,3,4,5,6,7,8,9,1,2,3,4, etc. Every group of measures are within the same decimal part of the second, i.e. 1 --> 13:25:34.7xx, 2-->13:25:34.7xx, when it starts again from IdSignal 1 it is at 13:25:34.8xx and so on.

My purpose is to change that and have a column for each signal (1,2,3,etc) and a row for each decimal second.

Answer 1

Leaving aside the possibility that you might be able to do this in the database more efficiently, if you want to do it in memory you could optimize in readability and performance with this approach:

var timeGroups = valueTable.AsEnumerable()
    .Select(GetRowInfo)
    .GroupBy(x => x.Time)
    .ToList();
            
DataTable finalTable = new DataTable();
finalTable.Columns.Add("Timestamp", typeof(DateTime));
int maxColumn = timeGroups.Max(g => g.Count());
for(int i = 1; i <= maxColumn; i++)
    finalTable.Columns.Add(i.ToString(), typeof(double));

foreach(var grp in timeGroups)
{
    DataRow addedRow = finalTable.Rows.Add();
    addedRow.SetField("Timestamp", grp.Key);
    int col = 0;
    foreach(var row in grp)
    {
        addedRow.SetField((++col).ToString(), row.Value);
    }
}

(DataRow Row, DateTime Time, string IdSignal, double Value) GetRowInfo(DataRow r)
{
    DateTime time = new DateTime(r.Field<int>("Year"), r.Field<int>("Month"), r.Field<int>("Day"), r.Field<int>("Hour"), r.Field<int>("Minute"), r.Field<int>("Second"), r.Field<int>("Millisecond"));
    string idSignal = r.Field<int>("IdSignal").ToString();
    double value = r.Field<double>("Value");
    return (r, time, idSignal, value);
}

This replaces your whole code apart from the Ado.Net part that fills the source table.