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

[Palak Shah]

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: TestData.xlsx

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;
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[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;
}
}

 

[NoUserHere]

How many times do we need to clean up after you?

Please use code tags when posting code to the forums, I have asked you before not to post code on the forums without enclosing all code within code tags.

Re-post your question with your code enclosed in the appropriate code blocks, or you will find continued infractions to ignore this request will lead to your topic being closed.

Thank you
Closing.