How to generate Datarows based on a delimiter in a Datacolumn from a DataTable in C#

Question

In my office, we use a software called Blue Prism, a Robotic Process Automation System. The idea is to allocate tasks for the robots to perform thereby simulating users. The system enables you to use a code stage ( with an option of C# with dotnet framework 2.0) and to import some objects in C#, you require a DataTable. My problem is

Currently, The datatable has one row with ID, FirstName, Initial and LastName as a columns but the chances are thousands of rows will be added subsequently.

The row contains values such as

table.Rows.Add(new object[] { 1, "Mary;Josh;Sam", "K;;", "Edward;Rashidi;Martha" });

but what i want is

FirstName    Initial     LastName
Mary           K          Edward
Josh                      Rashidi
Sam                       Grunua

P.S I have never had to use so many nested loops until now because Linq existed and it's currently not available in .net 2.0

Based on my research of DataTable, when you are aiming at retrieving data faster you use DataTableReader. Hence I used it here. I defined four methods

1. PrintColumns(DataTableReader reader) - to print out all the cells of the datatable

2. TransformDataTble(Datatable datatable) - which generates the new datatable i am aiming for

3. GetCustomers() - returns an input datatable containing required data

4. TestCreateDataReader(Datatable dt) - to check if the datatable has rows before attempting to print. Hence this calls printColumns()

---

 public static DataTable TransformDataTable(DataTable dataTable)
        {
            DataRow row;
            int counter = 0;
            DataTable newTable = new DataTable("FormattedTable");
            // Gives the newtable the same column Names as the oldtabble
            foreach (DataColumn column in dataTable.Columns)
            {
                newTable.Columns.Add(column.ColumnName);
            }
            using (System.Data.DataTableReader reader = dataTable.CreateDataReader())
            {
                while (reader.Read())
                {
                    if (reader.HasRows)
                    {
                        for (int i = 0; i < reader.FieldCount; i++)
                        {
                            if (Convert.ToString(reader[i]).Contains(";")
                            {
                                String[] splitString = Convert.ToString(reader[i]).Split(Convert.ToChar(";"));
                                foreach (string item2 in splitString)
                                {
                                    if (newTable.Rows.Count != splitString.Length)
                                    {
                                        row = newTable.NewRow();
                                        row[reader.GetName(i)] = item2;
                                        newTable.Rows.Add(row);
                                    }
                                    else
                                    {
                                        for (int j = counter; j < newTable.Rows.Count;)
                                        {
                                            newTable.Rows[j][reader.GetName(i)] = item2;
                                            break;
                                        }
                                        counter++;
                                    }
                                }
                                counter = 0;
                            }
                        }
                    }
                }
                return newTable;
            }
        }

---

private static void PrintColumns(DataTableReader reader)
    {
        // Loop through all the rows in the DataTableReader
        while (reader.Read())
        {
            for (int i = 0; i < reader.FieldCount; i++)
            {
                Console.Write(reader[i] + " ");
                var b = reader[i];
            }
            Console.WriteLine();
        }

    }

---

   private static void TestCreateDataReader(DataTable dt)
        {
            // Given a DataTable, retrieve a DataTableReader
            // allowing access to all the tables' data:
            using (DataTableReader reader = dt.CreateDataReader())
            {
                do
                {
                    if (!reader.HasRows)
                    {
                        Console.WriteLine("Empty DataTableReader");
                    }
                    else
                    {
                        PrintColumns(reader);
                    }
                    Console.WriteLine("========================");
                } while (reader.NextResult());
            }
        }

---

 private static DataTable GetCustomers()
        {
            // Create sample Customers table, in order
            // to demonstrate the behavior of the DataTableReader.
            DataTable table = new DataTable();

            // Create two columns, ID and Name.
            DataColumn idColumn = table.Columns.Add("ID", typeof(int));
            table.Columns.Add("FirstName", typeof(string));
            table.Columns.Add("Initial", typeof(string));
            table.Columns.Add("LastName", typeof(string));
            //table.Columns.Add("DOB", typeof(string));
            //table.Columns.Add("Member", typeof(string));
            //table.Columns.Add("Number", typeof(string));

            // Set the ID column as the primary key column.
            table.PrimaryKey = new DataColumn[] { idColumn };

            table.Rows.Add(new object[] { 1, "Mary;Josh;Sam", "k;;", "Edward;rashidi;Grunua" });

            return table;
        }
                    } while (reader.NextResult());
                }
            }

So far this code works, but i know his can be improved . Any suggestions would be helpful

P.S kindly comment on other forms of implemetations asides Linq as Linq is non existent in the .net 2.0 framework

Answer 1

Move code to methods. By the time you hit for (int j = counter; j < newTable.Rows.Count;) you're nearly ten levels deep. Not only are you losing valuable screen estate, it also doesn't help the clarity of your code.

You could also use continue; to reduce indentation:

if (!reader.HasRows)
{
   continue;
}

And:

if (!Convert.ToString(reader[i]).Contains(";")
{
   continue;
}

---

I don't see the point of defining DataRow row; at the top, considering row is only used inside if (newTable.Rows.Count != splitString.Length).

---

Why don't you simply Clone() the DataTable instead of doing foreach (DataColumn column in dataTable.Columns)?

---

Convert.ToString(reader[i]) is used twice, so perhaps it would be beneficial to store its results. Same for reader.GetName(i), and in that case it is even more important considering it is used inside a loop.

---

The name of item2 doesn't tell me anything about what it contains.

---

Avoid "negative" logic like if (newTable.Rows.Count != splitString.Length) ... else; instead do it the other way around: if (newTable.Rows.Count == splitString.Length).

---

";" should be defined as a private const char, and that variable should be used in IndexOf instead of Contains(";"); that way you can also avoid Convert.ToChar(";").