none
how to get data from datatable using column name using C#

    שאלה

  • i have a situation where i load a dataset with excel file.  All the worksheet are loaded as datatable with the appropriate worksheet name as datatable name.  What i am trying to do is get this datatable value using column name.  But i am not get error saying

    "Column 'Execute' does not belong to table Suite".

    While loaded excel to datatabel i have used HDR=YES and IMEX=1.  I also tried with HDR=NO. nothing is working. 

    below code is to write excel to datatable

    foreach (Microsoft.Office.Interop.Excel.Worksheet wsheet in workbook.Worksheets)
                    {
                        //MessageBox.Show(wsheet.Name.ToString());
                        string sql1 = "SELECT * FROM [" + wsheet.Name + "$]";
                        OleDbCommand selectCMD1 = new OleDbCommand(sql1, SQLConn);
                        SQLAdapter.SelectCommand = selectCMD1;

                        SQLAdapter.Fill(dataset.Tables.Add(wsheet.Name));
                       
                    }
    foreach (Microsoft.Office.Interop.Excel.Worksheet wsheet in workbook.Worksheets)
    {
                        
     string sql1 = "SELECT * FROM [" + wsheet.Name + "$]";
                        OleDbCommand selectCMD1 = new OleDbCommand(sql1, SQLConn);
                        SQLAdapter.SelectCommand = selectCMD1;
    
                        SQLAdapter.Fill(dataset.Tables.Add(wsheet.Name));
                       
                    }

    Data from excel loads to each sheet perfectly. but fetching it by column name is the problem. 

    any suggestions please

    יום רביעי 20 פברואר 2013 10:43

תשובות

  • Thanks Amanda, 

    i got the solution for this.

    string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filename +
        "; Extended Properties=\"Excel 12.0 XML;HDR=YES\"";
    DataSet dsValues = new DataSet();
    
    using (OleDbConnection conn = new OleDbConnection(connectionString))
    {
        conn.Open();
    
        using (OleDbCommand cmd = conn.CreateCommand())
        {
            using (OleDbDataAdapter adapter = new OleDbDataAdapter())
            {
                foreach (Excel.Worksheet wsheet in workbook.Worksheets)
                {
                    cmd.CommandText = "SELECT * FROM [" + wsheet.Name + "$]";
                    adapter.SelectCommand = cmd;
                    adapter.Fill(dsValues.Tables.Add(wsheet.Name));
                }
            }
        }
    }

    just changed few thing and it worked.  And sorry for posting in this section.  But this is for Coded UI.

    thanks

    vince

    • סומן כתשובה על-ידי vincent vinod יום שישי 22 פברואר 2013 07:22
    יום שישי 22 פברואר 2013 07:22

כל התגובות

  • Hello Vincent,

    Thank you for your post.

    I have noticed another thread post by you:

    http://social.msdn.microsoft.com/Forums/en-US/vstest/thread/cb9e0445-fc71-4eae-8742-80b2c831ed22/#f496697e-67cf-4553-a435-a724d5e94e07

    If you have appended values into existing excel file and to a particular column, and now you want an approach to get data from datatable using column name using C# code so that you can use the value of a column in coded UI test, I am afraid that the issue is not related to coded UI test itself. Generally we add a data source to the test method and then use TestContext.DataRow[columnname] to get data from a column in data driven coded UI test. I think that your issue is more related to developing to get some data from excel file using C# code. I suggest that you should consult your issue on Visual C# forum to check how to accomplish it using C# code.

    If I have misunderstood anything, please feel free to let me know.

    Best regards,


    Amanda Zhu [MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    יום חמישי 21 פברואר 2013 06:05
  • Hi Amanda,

    thanks for the reply,

    what  u have said is right,  if i use datasource then for each TestMethod i have to use it and it can access only one sheet at a time. (if i am wrong let me know).  I am doing this because i want to get the entire excel workbook, so i can access all the worksheet.  i am just trying to overcome the default fuctionality given by TestMethod and DataSource.

    I have developed data driven test using TestMethod and DataSource, but what in case of higher framework.  (if i am wrong let me know).

    And more over datasource in testmethod, iterates for the entire datasheet.  which can not be stopped.


    I have noticed another thread post by you:

    http://social.msdn.microsoft.com/Forums/en-US/vstest/thread/cb9e0445-fc71-4eae-8742-80b2c831ed22/#f496697e-67cf-4553-a435-a724d5e94e07

    And in this thread i was trying to update excel using TestContext or any other method using datasource.

    I just figured using datatable and excel i can do it, that is why i am using trying to access data through column name.

    If there is any easy way to use CUIT, then please let me know or

    If i am wrong please let me know, so i can use CUIT with ease.

    thanks

    vince

    יום חמישי 21 פברואר 2013 10:55
  • Hi Vincent,

    Yes, you are right that you can only access one sheet at a time for a test method if you do the normal data driven coded UI test. If you want to access all worksheet in an excel file, I am afraid that you have to write your own code to accomplish it. Getting data of a worksheet in an excel file should be performed before performing some actions on some controls, which I think that it is a development issue rather than a testing issue. As I mentioned in my pervious reply, I suggest that you can consult on Visual C#forum to check how to get data of a column in a sheet of an excel file using C#. Then you can apply the solution into coded UI test to accomplish what you want for coded UI test.

    Thank you for your understanding and support.

    Best regards,


    Amanda Zhu [MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    יום שישי 22 פברואר 2013 01:42
  • Thanks Amanda, 

    i got the solution for this.

    string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filename +
        "; Extended Properties=\"Excel 12.0 XML;HDR=YES\"";
    DataSet dsValues = new DataSet();
    
    using (OleDbConnection conn = new OleDbConnection(connectionString))
    {
        conn.Open();
    
        using (OleDbCommand cmd = conn.CreateCommand())
        {
            using (OleDbDataAdapter adapter = new OleDbDataAdapter())
            {
                foreach (Excel.Worksheet wsheet in workbook.Worksheets)
                {
                    cmd.CommandText = "SELECT * FROM [" + wsheet.Name + "$]";
                    adapter.SelectCommand = cmd;
                    adapter.Fill(dsValues.Tables.Add(wsheet.Name));
                }
            }
        }
    }

    just changed few thing and it worked.  And sorry for posting in this section.  But this is for Coded UI.

    thanks

    vince

    • סומן כתשובה על-ידי vincent vinod יום שישי 22 פברואר 2013 07:22
    יום שישי 22 פברואר 2013 07:22
  • Hi Vincent,

    Glad to see this issue has been resolved and thank you for sharing your solutions & experience here. It will be very beneficial for other community members who have similar questions.

    Best regards,


    Amanda Zhu [MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    יום שישי 22 פברואר 2013 07:37