DataSet Select Column

Question

I would like to select a specific column from a Dataset once it's been populated (e.g. Column Grades) and put the values into a list

string excelFile = @"C:\Scores.xlsx";
if (File.Exists(excelFile))
{
    string connString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source="+excelFile+";Extended Properties=Excel 12.0;";
    var dataAdapter = new OleDbDataAdapter();
    var objConn = new OleDbConnection(connString);

    //SELECT [Name],[Grade],[Location] ect...
    const string query = "SELECT * FROM [TeamScores$]"; 
    var objCmd = new OleDbCommand(query, objConn);

    var table = new DataSet();
    dataAdapter.SelectCommand = objCmd;
    dataAdapter.Fill(table);               

    //I would like to filter the DataSet to select only [Name] and populate the values into a List<string>

    dataGridView1.DataSource = table.Tables[0]; //Will show all results                

}

I have edited your title. Please see, "[Should questions include “tags” in their titles?](http://meta.stackexchange.com/questions/19190/)", where the consensus is "no, they should not". — John Saunders, Nov 17 '14 at 03:14

score 5 · Accepted Answer · edited Apr 27 '15 at 04:19

5

Your variable naming is confusing you.

var table = new DataSet();  // not good at all

A DataSet is not a table. A DataSet contains DataTables.

Try:

DataSet ScoresDataSet = new DataSet();

Then you can use the Select method on the table (something like...):

DataTable ScoresTable = ScoresDataSet.Tables[0]; 

dataGridView1.DataSource = ScoresTable.Select("Your criteria");

edited Apr 27 '15 at 04:19

shA.t

16,580
5
54
111

answered Nov 17 '14 at 01:59

Steve Wellens

20,506
2
28
69

Thank you for taking the time to help I have posted the way I have done it – sw2020 Nov 17 '14 at 03:06

score 2 · Answer 2 · edited Nov 17 '14 at 03:15

2

I have now solved the issue I can change "Grade" to which ever column I wish and it'll show the values associated.

DataTable scoresTable = ScoresDataSet.Tables[0];                
var result = scoresTable.AsEnumerable()
    .Select(r => r.Field<string>("Grade")).Where(r => r != null);

var listOfGrades = result.ToList();

edited Nov 17 '14 at 03:15

John Saunders

160,644
26
247
397

answered Nov 17 '14 at 03:03

sw2020

195
1
2
10

score 1 · Answer 3 · answered Mar 28 '18 at 18:34

You need adjust this line:

dataGridView1.DataSource = table.Tables[0]; //Will show all results

for example this one:

dataGridView1.DataSource = table.Tables[0].Select("yourField=5")); // you filter the datarows where yourField is 5.

or

dataGridView1.DataSource = table.Tables[0].Select("yourField>5 and yourField<21")); // is another example

score 0 · Answer 4 · edited Nov 17 '14 at 03:16

Hopefully this can help you :

System.Data.DataSet dsTemp2 = new System.Data.DataSet();


if (dsTemp2.Tables[0].Rows.Count <= 0)
    MessageBox.Show("Records not found");
else
{
    foreach (DataRow dRow in dsTemp2.Tables[0].Rows)
    {
        yourtextbox1.Text = dsTemp2.Tables[0].Rows[0][4].ToString();
        yourtextbox2.Text = dsTemp2.Tables[0].Rows[0][5].ToString();
        yourtextbox3.Text = dsTemp2.Tables[0].Rows[0][7].ToString();
    }
}

DataSet Select Column

4 Answers4