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

Question

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:

<pre>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<TestDataModel> GetAllTestData(string keyName)

{

DataSet ds = new DataSet();

DataNamesMapper<TestDataModel> mapper = new DataNamesMapper<TestDataModel>();

DataTable dataTableALL = new DataTable();

List<TestDataModel> testData = new List<TestDataModel>();

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<DataColumn>().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<string>(ID) equals t2.Field<string>(ID)

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

foreach (var join in joins)

{

foreach (string column in columns.Cast<DataColumn>().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<DataColumn>().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<string>("portal") == "abc").First();

return flatTable;

}

}

}

 

TestData Model

 

namespace AutoFramework.Model.Excel

{

public partial class TestDataModel

{

public TestDataModel() {

}

[DataNames("TestName")]

public string TestName { get; set; }

[DataNames("productId")]

public int productId { get; set; }

[DataNames("orderId")]

public int orderId { get; set; }

[DataNames("designMethod")]

public DesignMethod designMethod { get; set; }

[DataNames("signedIn")]

public bool signedIn { get; set; }

[DataNames("increaseBasketQty")]

public bool increaseBasketQty { get; set; }

[DataNames("signedInCMS")]

public bool signedInCMS { get; set; }

[DataNames("editable")]

public bool editable { get; set; }

//[DataNames("paymentOptions")]

//public PaymentOptions paymentOption { get; set; }

[DataNames("checkInVoice")]

public bool checkInVoice { get; set; }

[DataNames("navigateToDesign")]

public bool navigateToDesign { get; set; }

[DataNames("checkOrderAuthorsie")]

public bool checkOrderAuthorsie { get; set; }

[DataNames("checkSplitOrder")]

public bool checkSplitOrder { get; set; }

[DataNames("SiteId")]

public string SiteId { get; set; }

[DataNames("SiteUrl")]

public string SiteUrl { get; set; }

[DataNames("CultureCode")]

public string CultureCode { get; set; }

[DataNames("SiteGroupId")]

public string SiteGroupId { get; set; }

[DataNames("NickName")]

public string NickName { get; set; }

[DataNames("byCard")]

public string byCard { get; set; }

[DataNames("payLater")]

public string payLater { get; set; }

[DataNames("sliceIt")]

public string sliceIt { get; set; }

[DataNames("portal")]

public PaymentPortal portal { get; set; }

[DataNames("delivery")]

public static DeliveryMethod delivery{get;set;}

}

}

Accessing Data

var param1 = AccessExcelData.GetAllTestData("Test1");

var orderId = param[1].orderId;

Now I want to access say column value of "payLater" as

param1.PaymentOptions.payLater

where PaymentOptions function is

public class PaymentOptions

{

public PaymentPortal portal;

public DeliveryMethod delivery = DeliveryMethod.Billing;

public PaymentOptions()

{

}

public PaymentOptions(Site site)

{

}

}

public class KlarnaOptions : PaymentOptions

{

//default - don't use card payment by deffault

public bool byCard = false;

public bool payLater = false;

public bool sliceIt = false;

public KlarnaOptions()

{

portal = PaymentPortal.Klarna;

}

}