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
PrintColumns(DataTableReader reader)
- to print out all the cells of the datatableTransformDataTble(Datatable datatable)
- which generates the new datatable i am aiming forGetCustomers()
- returns an input datatable containing required dataTestCreateDataReader(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
splitString.Count()
in the first method which is definitely linq and doesn't compile for .net 2.0. Also thereader[i]).Contains((char)59)
is illegal in .net 2.0. Are you not telling us something? You seem to be using linq anyway. Are you sure you're working with .net 2.0 and not something higher like 3.5? \$\endgroup\$ – t3chb0t Jun 13 '16 at 5:51