How to access values from 2 datatable as tabel1.tabel2.columnname in C

I have one test data model and one excel book which has multiple sheets! all the sheet is having the same unique key "Test1" and I have a function which fetches all the data from all sheets having that key name "Test1"

Now after join - I get one table having all the column name and values But now I want to access certain columns as Taable1.someobject(table).columnName

Test Data Sheet: https://drive.google.com/file/d/1FdUq2iOVUXl8yIc2xDyO0bxCelhyRZ-t/view?usp=sharing

What I have tried:

Code:

namespace AutoFramework.Model.Excel

{

public class AccessExcelData

{

public static string TestDataFileConnection()

{

string Filename = "D:\\GIT-TA\\src\\Automation\\Framework\\ExcelData\\TestData.xlsx";

string connectionString = string.Format("Dsn=Excel Files;READONLY=true;DBQ={0};", Filename);

System.Data.Odbc.OdbcCommand odbcCmd = new System.Data.Odbc.OdbcCommand("", new System.Data.Odbc.OdbcConnection(connectionString));

return connectionString;

}

public static IList GetAllTestData(string keyName)

{

DataSet ds = new DataSet();

DataNamesMapper mapper = new DataNamesMapper();

DataTable dataTableALL = new DataTable();

List testData = new List();

using (var connection = new

OdbcConnection(TestDataFileConnection()))

{

connection.Open();

OdbcCommand cmd = new OdbcCommand();

cmd.Connection = connection;

System.Data.DataTable dtSheet = null;

dtSheet = connection.GetSchema(OdbcMetaDataCollectionNames.Tables, null);

foreach (DataRow row in dtSheet.Rows)

{

string sheetName = row["TABLE_NAME"].ToString();

if (!sheetName.EndsWith("$"))

continue;

// Query each excel sheet.

var query = string.Format("select * from [{0}] where TestName = '{1}'", sheetName, keyName);

cmd.CommandText = query;

DataTable dt = new DataTable();

dt.TableName = sheetName;

OdbcDataAdapter da = new OdbcDataAdapter(cmd);

da.Fill(dt);

ds.Tables.Add(dt);

}

cmd = null;

connection.Close();

}

DataTable data= JoinExcelDatatoOneRow(ds);

testData = mapper.Map(data).ToList();

return testData.ToList();

}

public static DataTable JoinExcelDatatoOneRow(DataSet ds)

{

DataTable flatTable = null;

string ID = "TestName";

for (int i = 0; i < ds.Tables.Count; i++)

{

DataTable dt = ds.Tables[i];

if (i == 0)

{

flatTable = dt.AsEnumerable().CopyToDataTable();

}

else

{

DataColumn[] columns = dt.Columns.Cast().ToArray();

foreach (DataColumn col in columns)

{

if (col.ColumnName != ID)

{

flatTable.Columns.Add(col.ColumnName, col.DataType);

}

}

var joins = from t1 in flatTable.AsEnumerable()

join t2 in dt.AsEnumerable()

on t1.Field(ID) equals t2.Field(ID)

select new { t1 = t1, t2 = t2 };

foreach (var join in joins)

{

foreach (string column in columns.Cast().Select(x => x.ColumnName))

{

if (column != ID)

{

join.t1[column] = join.t2[column];

}

}

}

}

}

string[] filteredColumns = { "payLater", "portal", "delivery" };

int[] filteredIndexes = filteredColumns.Select(x => flatTable.Columns.Cast().Where(y => x == y.ColumnName).First().Ordinal).ToArray();

DataTable filteredTable = new DataTable();

foreach (int index in filteredIndexes)

{

filteredTable.Columns.Add(flatTable.Columns[index].ColumnName, flatTable.Columns[index].DataType);

}

foreach (DataRow row in flatTable.AsEnumerable())

{

filteredTable.Rows.Add(filteredIndexes.Select(y => row[y]).ToArray());

};

filteredTable.AsEnumerable().Select((x, i) => flatTable.Rows[i][7]);

DataRow row1 = filteredTable.AsEnumerable().Where(x => x.Field("portal") == "abc").First();

return flatTable;

}

}

}

 

TestData Model