Need help when dealing with Excel Complex Data in MSTest

[Palak Shah]

Currently, I am fetching data from particular sheet where it matched a "Key" value - But now I want to access data from all sheets where it matches "Key" value

In my excel - I have two sheets: LogInUserData and Sheet2

Now in LogInUserData

Key	Email	Password	RememberMe
LogInSuccessfully	a@a.a	a	0
LogInWithWrongEmail	a.a	a	0
LogInWithWrongPass	a@a.a	b	0

And in Sheet2

Key	Klarna
LogInSuccessfully	Demo

Now, I want to access values of Key "Loginsuccessfully" from both the sheets - I have code but that fetches it from only first sheet and not from other - so how do i access complex data sheet from excel

I'm using ODBC Connection

public static string TestDataFileConnection(string fileName)
    {
        string Filename = "C:\\Users\\PalakS\\source\\repos\\Daily update\\UnitTestProject2 - 10-04\\UnitTestProject2\\ExcelData\\LogInPageData.xlsx";
        string connectionString = string.Format("Dsn=Excel Files;READONLY=false;DBQ={0};", Filename);
        System.Data.Odbc.OdbcCommand odbcCmd = new System.Data.Odbc.OdbcCommand("", new System.Data.Odbc.OdbcConnection(connectionString));
        return connectionString;
    }
    public static T GetTestData<T>(string fileName, string sheet, string keyName)
    {
        using (var connection = new
                      OdbcConnection(TestDataFileConnection(fileName)))
        {
            connection.Open();
            var query = string.Format("select * from [{0}$]where key = '{1}'", sheet, keyName);
            var value = connection.Query<T>(query).FirstOrDefault();
            connection.Close();
            return value;
        }
    }

Function Call:

LogInUserModel user = AccessExcelData.GetTestData<LogInUserModel>("LogInPageData.xlsx", "LogInUserData", "LogInSuccessfully");
        string email = user.Email.ToString();

 

[Skydiver]

Can you show us what your LogInUserModel class looks like? Currently, it's unclear what the value of the key means in the context of your question. Is the value all of the columns? If so, how do the column values map into your class?

 

[Palak Shah]

Can you show us what your LogInUserModel class looks like? Currently, it's unclear what the value of the key means in the context of your question. Is the value all of the columns? If so, how do the column values map into your class?

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Threading.Tasks;



namespace UnitTestProject2.Models

{

    public class LogInUserModel

    {

        private string key;

        private string email;

        private string password;

        private string rememberme;

        private string klarna;

        public LogInUserModel() { }



        public string Key

        {

            get { return this.key; }

            set { this.key = value; }

        }

        public string Email

        {

            get { return this.email; }

            set { this.email = value; }

        }

        public string Password

        {

            get { return this.password; }

            set { this.password = value; }

        }

        public string RememberMe

        {

            get { return this.rememberme; }

            set { this.rememberme = value; }

        }

        public string Klarna

        {

            get { return this.klarna; }

            set { this.klarna = value; }

        }

    }

}

 

[Palak Shah]

In Model - I've mentioned all the columns from sheets - That's what I could think of

I'm bit confused - How Do I get values from all the sheets when we are dealing with complex data set for test

 

[Skydiver]

And presumably this already succeeds:

LogInUserModel user = AccessExcelData.GetTestData<LogInUserModel>("LogInPageData.xlsx", "LogInUserData", "LogInSuccessfully");

If so, then you should have a helper method like:

LogInUserModel GetAllTestData()
{
    var loginUserData = AccessExcelData.GetTestData<LogInUserModel>("LogInPageData.xlsx", "LogInUserData", "LogInSuccessfully");
    var sheet2 = AccessExcelData.GetTestData<LogInUserModel>("LogInPageData.xlsx", "Sheet2", "LogInSuccessfully");
    return new LogInUserModel()
    {
        Key = loginUserData.Key,
        Email = loginUserData.Email,
        Password = loginUserData.Password,
        RememberMe = loginUserData.RememberMe,
        Klarna = sheet2.Klarna
    }
}

which will combine the two pieces together.

If your question is how to do the combination without using a helper method, then that is not a C# question anymore, but rather a SQL/ODBC question.

 

[Palak Shah]

And presumably this already succeeds:

LogInUserModel user = AccessExcelData.GetTestData<LogInUserModel>("LogInPageData.xlsx", "LogInUserData", "LogInSuccessfully");

If so, then you should have a helper method like:

LogInUserModel GetAllTestData()
{
    var loginUserData = AccessExcelData.GetTestData<LogInUserModel>("LogInPageData.xlsx", "LogInUserData", "LogInSuccessfully");
    var sheet2 = AccessExcelData.GetTestData<LogInUserModel>("LogInPageData.xlsx", "Sheet2", "LogInSuccessfully");
    return new LogInUserModel()
    {
        Key = loginUserData.Key,
        Email = loginUserData.Email,
        Password = loginUserData.Password,
        RememberMe = loginUserData.RememberMe,
        Klarna = sheet2.Klarna
    }
}

which will combine the two pieces together.

If your question is how to do the combination without using a helper method, then that is not a C# question anymore, but rather a SQL/ODBC question.

Thank you for this reply - I can try your above helper method way - which will solve the issue - However I did try below way - but this doe not concate the previous sheet's value;

public static T GetTestData<T>(string fileName, string sheet, string keyName)
        {
            using (var connection = new
                          OdbcConnection(TestDataFileConnection(fileName)))
            {
                connection.Open();
                System.Data.DataTable dt = null;
                dt = connection.GetSchema(OdbcMetaDataCollectionNames.Tables, null);
                String[] excelSheets = new String[dt.Rows.Count];
                var i = 0;
                // Add the sheet name to the string array.
                foreach (DataRow row in dt.Rows)
                {
                    excelSheets[I] = row["TABLE_NAME"].ToString();
                    i++;
                }
                T value = default(T);
                string list1 = "";
                // Loop through all of the sheets if you want too...
                for (int j = 0; j < excelSheets.Length; j++)
                {
                    // Query each excel sheet.
                    var query = string.Format("select * from [{0}]where key = '{1}'", excelSheets[j], keyName);
                    value = connection.Query<T>(query).FirstOrDefault();
                    list1 += string.Concat(value);
                }
                Console.Write(list1);
                connection.Close();
                return value;
                //var query = string.Format("select * from [{0}$]where key = '{1}'", sheet, keyName);
                //var value = connection.Query<T>(query).FirstOrDefault();
                //connection.Close();
                //return value;
            }
        }[/I]

 

[Skydiver]

In your code above, notice that you are overwriting value each time your for loop iterates (see lines 20-26), and you return that last overwritten value on line 29.

C# does not have the concept of taking the last value and merging it in with the current value. Other languages let you write a custom override for their assignment operator depending on the type, but unfortunately, current versions of C# do not. If you could write a custom override, then you can do something that does a merging of the values found.

 

[Palak Shah]

Is there any other approach which I can pick other than above to get data from all the sheets which are having same Key value in c#?