Get filtered data from dataset to datatable

Question

How can I filter data from dataset to datatable? like the code->

DataRow[] dr = DS.Tables[0]
    .Select("STAGENAME='Develop' AND DEVLAPSEDAYS IS NOT NULL");        

How can I use datatable here?

following code doesn`t reflect changes->

DataTable FilteredDataD = DS.Tables[0];
if (FilteredDataD.Rows.Count > 0) {
    FilteredDataD.DefaultView.RowFilter = "STAGENAME='Develop' AND DEVLAPSEDAYS IS NOT NULL";
    FilteredDataD.DefaultView.ToTable();
}

Is is possible to remove a column using above filter,like "STAGENAME='Develop' AND DEVLAPSEDAYS IS NOT NULL" + FilteredDataD.column("col_name")... Suppose I have 5 columns display only 4,I can`t remove col_name from my query.Is there a way?

Reply

Solution 1

Try using LINQ instead:

var table = DS.Tables[0].AsEnumerable().Where(
    r => r.Field<string>("STAGENAME") == "Develop" && r.Field<int?>("DEVLAPSEDAYS").HasValue).AsDataView().ToTable();

EDIT Changed AsDataView to AsDataView() for syntactical accuracy.
EDIT Provided .NET 2.0 compatible solution

DataTable table = DS.Tables[0];
if (table.Rows.Count > 0)
{
    table.DefaultView.RowFilter = "STAGENAME = 'DEVELOP' AND DEVLAPSEDAYS IS NOT NULL";
    table = table.DefaultView.ToTable(); 
}

Solution 2

You could write an extension method (using C# 3) like follows:

public static DataTable Filter(this DataTable dataTable, string selectFilter)
{
    var filteredTable = dataTable.Clone();
    var rows = dataTable.Select(selectFilter).ToList();
    rows.ForEach(filteredTable.ImportRow);
    return filteredTable;
}

Then use it like follows:

DataTable dataTable = DS.Tables[0]
    .Filter("STAGENAME='Develop' AND DEVLAPSEDAYS IS NOT NULL");

---

Update, since you said you are using C# 2.0 (and thus extension methods and LINQ aren't an option) you could use this instead:

public static DataTable GetFilteredTable(
    DataTable sourceTable, string selectFilter)
{
    var filteredTable = sourceTable.Clone();
    var rows = sourceTable.Select(selectFilter);
    foreach (DataRow row in rows)
    {
        filteredTable.ImportRow(row);
    }
    return filteredTable;
}

DataTable dataTable = GetFilteredTable(
    DS.Tables[0], "STAGENAME='Develop' AND DEVLAPSEDAYS IS NOT NULL");

Comments

[palak]

Matt,Thanks for the reply,Im not finding reference for ToList(),im using C# 2.0..

[palak]

James,AsEnumerable() couldnt find in my VS,Im using .net 2.0 version.Reply

[James Johnson]

@Matt: Your first answer is a roundabout way of doing what DataTable.DefaultView.RowFilter does.

[Matt]

@JamesJohnson +1, I wan't aware of "views" being used on datasets and such. This is a nice solution.

[palak]

@JamesJohnson:Hi james thanks for the answer,but that`s not working for me,filtering is not happening.

[James Johnson]

You need to redimension the table: FilteredDataD = FilteredDataD.DefaultView.ToTable();

[palak]

@JamesJohnson:ohh I forgot to do it,well thanks for the answer too.

[James Johnson]

@palak: Create a new question for this, and we'll figure it out.

[palak]

@JamesJohnson:I found it,I used it like datatable.columns.Remove("col_name")..is there a way then this???plz don`t ask me to create a new question...

[vapcguy]

2nd example above is far less complex than the LINQ query version, but one does need to check if table != null && table.Rows != null && table.Rows.Count > 0, not just if the row count > 0.

[vapcguy]

@palak - I know this is 5 years late, but if you don't want a column, don't add it to your DataTable to begin with, or remove it prior to the query, as you already figured out. If you want to show rows where a column does not have a particular value, just use that 2nd example with table.DefaultView.RowFilter = "col_name <> 'Whatever'"; table = table.DefaultView.ToTable(); There are many examples of different filters here: csharp-examples.net/dataview-rowfilter