Prash's Blog

How to Select or Project some columns from a Datatable programmatically without SQL November 7, 2011

Filed under: ADO — prazjain @ 11:26 am
Tags: , , , , , , , ,

If you want to filter a few rows you a give a conditional expression that will be applied and you get only the resulting rows that satify the conditional expression.

But what if you have around 50 columns and you do not want to see all of them in the result after a select query.

So how do you remove the extra columns? This is how I worked around removing the columns I did not need.

Steps:

  1. Get the rows that satify the filter search criteria
  2. Import them and create a new table
  3. Now all I am doing is, if the the column name is not part of SelectedColumn (string property) then I just remove it from the data table’s column collection. (Of course you cannot remove it in the ‘for’ loop because you cannot edit the collection underlying the enumerator while you are running through the enumerator. So just store it in a temporary collection and remove them later).
  4. Thats it, you have your results!
        private void btnExecute_Click(object sender, RoutedEventArgs e)
        {
            StringBuilder queryBuilder = new StringBuilder();
            //Filter the rows that satisfy the conditional expression
            DataRow[] rows = QueryCSVModel.Data.Table.Select(QueryCSVModel.FilterCriteria);
            //Clone the table to create a new one, and import the eligible rows
            DataTable dt = QueryCSVModel.Data.Table.Clone();
            foreach (DataRow item in rows)
            {
                dt.ImportRow(item);
            }
            List<DataColumn> toRemove = new List<DataColumn>();
            foreach (DataColumn col in dt.Columns)
            {
                //Any column name that is not desired (not in selected columns) is to be removed.
                if (!string.IsNullOrWhiteSpace(QueryCSVModel.SelectedColumns) && !QueryCSVModel.SelectedColumns.Contains(col.ColumnName))
                {
                    toRemove.Add(col);
                }
            }
            // iterate over data column collection and remove the unwanted columns.
            foreach (DataColumn col in toRemove)
	        {
                dt.Columns.Remove(col);
	        }
            //thats it, you have your data with fewer columns
            QueryCSVModel.FilteredData = dt.DefaultView;
        }
     
Advertisements
 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s