CODEWITHSUNDEEP
c#arraysselectfilterdatatable
Andrew
Andrew

Reputation: 7448

filter a data table with values from an array c#

I have a delete method, it gets an array of GUIDs and I have a data table... how can I filter the data table so it will only contain thoes GUIDs?

public void delete(Guid[] guidlist)
{
datatable template = ReadTemplateList()
...
}

Thank you!

Upvotes: 2

Views: 9996

Answers (3)

gmail user
gmail user

Reputation: 2783

A solution based on DataTable and DataRows.

        //fill the datatable from back end
        string connStr = ConfigurationManager.ConnectionStrings["ConsoleApplication1.Properties.Settings.NORTHWNDConnectionString"].ConnectionString;            
        SqlConnection conn = new SqlConnection(connStr);
        conn.Open();
        SqlCommand comm = new SqlCommand("select categoryid,categoryname from Categories order by categoryname");            
        comm.Connection = conn;
        SqlDataReader dr = comm.ExecuteReader();
        DataTable dt = new DataTable();           
        dt.Load(dr);

        //datatable filter logic
        string[] filter = { "1", "2" };


        DataTable filteredTable =   dt.Clone();
        foreach (string str in filter)
        {
            DataRow[] filteredRows = dt.Select("categoryid="+ str); //search for categoryID
            foreach (DataRow dtr in filteredRows)
            {
                filteredTable.ImportRow(dtr);
            }                

        }

EDIT

Without going in the for loop

DataTable dt = new DataTable();           
dt.Load(dr);    //fill the datatable with sqldatareader        
DataTable filteredTable =   dt.Clone();
DataView dv = dt.DefaultView;            
dv.RowFilter = "categoryid in (1,2)";
filteredTable = dv.ToTable();

Upvotes: 1

Sergey Berezovskiy
Sergey Berezovskiy

Reputation: 236208

With Linq to DataSet you can create new DataTable which will have only rows with those Guids:

public void Delete(Guid[] guids)
{
   DataTable table = ReadTemplateList()
                         .AsEnumerable()
                         .Where(r => guids.Contains(r.Field<Guid>("ColumnName")))
                         .CopyToDataTable();

   // ...
}

Another option (which also will work on old .NET versions) is filtering your table with built-in RowFilter functionality which supports IN filters. Let's assume you are filtering by column named ID:

// Check if guids.Length > 0
StringBuilder filter = new StringBuilder("ID IN ("); 
foreach (Guid id in guids)
    filter.AppendFormat("Convert('{0}','System.Guid'),", id);
filter.Append(")");

DataTable template = ReadTemplateList();
DataView view = template.DefaultView;
view.RowFilter = filter.ToString();
DataTable table = view.ToTable();

Upvotes: 4

Tim Schmelter
Tim Schmelter

Reputation: 460068

You can use LINQ:

public static DataTable DeleteGuidsFromTemplate(Guid[] guidlist)
{
    DataTable template = ReadTemplateList();
    var rowsWithGuids = from row in template.AsEnumerable()
                        join guid in guidlist 
                        on row.Field<Guid>("Guid") equals guid
                        select row;
    return rowsWithGuids.CopyToDataTable();
}

If you can't use LINQ since you're lower than NET 3.5:

public static DataTable DeleteGuidsFromTemplate(Guid[] guidlist)
{
    DataTable template = ReadTemplateList();
    DataTable templateGuids = template.Clone();
    foreach(DataRow row in template.Rows)
    { 
        Guid guid = (Guid)row["Guid"];
        int index = Array.IndexOf(guidlist, guid);
        if (index >= 0)
            templateGuids.ImportRow(row);
    }
    return templateGuids;
}

Upvotes: 2

Related Questions