Filter a data from the dataset

Sairam Soham

I have populated the dataset from loading the data from the spreadsheet by using the following code

DataSet ds = new DataSet();
                ds.DataSetName = "Configurations";

                using (

               OleDbConnection conn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source= " + xlsxPath + ";Extended Properties=\"Excel 12.0 Xml;HDR=YES\""))
                {
                    try
                    {
                        using (OleDbCommand command = new OleDbCommand("select * from [Sheet1$] ", conn))
                        {
                            using (OleDbDataAdapter adapter = new OleDbDataAdapter(command))
                            {
                                try
                                {
                                    adapter.Fill(ds);
                                }
                                catch (Exception ex1)
                                {
                                    MessageBox.Show(ex1.ToString());
                                }
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show(ex.ToString());
                    }
                }
                //this can be extended in to dofferent formats since we got the data in an object.
                ds.Tables[0].TableName = "Meters";

                ds.WriteXml(xlsxPath.Substring(0, xlsxPath.IndexOf(".")) + ".xml")

Question:

From the attached spreadsheet, I want to filter a rows from dataset where value of column1 is 2 (Header name is MeterID)?

Any clue how shall I do it?

Will it be possible once the data get populated in the dataset, assign a column names as user friendly name?

Thanks

7 Replies

Know the answer? Post it — somebody with the same question will find it here.

Vulpes11y ago
Accepted answer
You could do something like this where columnName and columnValue are variables to be written to dynamically:

string filterExpression = String.Format("{0} = {1}", columnName, columnValue);
DataRow[] filteredRows = ds.Tables[0].Select(filterExpression);
Sairam Soham11y ago
Thank you so much!
Vulpes11y ago
They're just placeholders for the rest of the arguments to the String.Format method.

So {0} means substitute the first argument (i.e. columnName) and {1} means substitute the second argument (i.e. columnValue).

You can have as many arguments as you like referenced by numbers in this way and can repeat some arguments or insert them in a different order if that is convenient for whatever it is you're doing at the time.
Sairam Soham11y ago
Thanks it worked.

I am curious what does 0 and 1 means in the following line.

string filterExpression = String.Format("{0} = {1}", columnName, columnValue);
Sairam Soham11y ago
Sorry It was typo I had rows instead of Rows.

When I tried the following it worked.

ds.Tables[0].Rows[5]["Test"]
I want to do the following dynamically
DataRow[] filteredRows = ds.Tables[0].Select("MeterID = 2");

Instead of MeterID =2 (Hardcoding), I want to write
select the value of the column MeterID =2
Sairam Soham11y ago
I am able to change a column name on the fly. Thank you.

? ds.Tables[0].Columns[0].ToString()
"MeterID"
? ds.Tables[0].Columns[0].ColumnName
"MeterID"
? ds.Tables[0].Columns[0].ColumnName = "Test"
"Test"
? ds.Tables[0].Columns[0].ToString()
"Test"

If I want to access row 5 of column "Test", how shall I do this?
Vulpes11y ago
Try:

DataRow[] filteredRows = ds.Tables[0].Select("MeterID = 2");

To change the column name:

ds.Tables[0].Columns[0].ColumnName = "Whatever";