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!
3 Answers
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();
answered Jan 23, 2014 at 13:22
Sergey Berezovskiy
237k44 gold badges441 silver badges468 bronze badges
4 Comments
Andrew
I do not have the Contains method, only CompareTo, Equals, GetHashCode, GetType, ToByteArray, ToString
Sergey Berezovskiy
@Andrew just add
using System.Linq; namespace - this is extension method, which declared thereAndrew
Now I get an error that "Cannot implicitly convert type "data table" to type "templates_viewDataTable". An explicit conversion exists (are you missing a cast?)"
Sergey Berezovskiy
@Andrew are you using typed data tables?
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;
}
Comments
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();
DataTablewithout the filtered rows?