Group a DataTable into separate DataTables?

Question

I have a DataTable which is like this -

EmpId,Country 
1,Germany
2,Germany
3,UK
4,UK
5,UK
6,France
7,USA
8,USA

I want to group by Country and extract all groups into separate datatables. So, we will have 4 groups/data tables. How do I do this ?

Stackoverflow won't let me paste fully working code. So here it is, thanks to @Myles B. Currie

Tested code -

public void main()
   {
        DataSet ds = (DataSet) Dts.Variables["obj_Rs"].Value;
        DataTable dt = ds.Tables[0];

        List<DataTable> allCountryTables = new List<DataTable>();

        DataView view = new DataView(dt);
        DataTable distinctValues = view.ToTable(true, "Country");

        //Create new DataTable for each of the distinct countries 
        //identified and add to allCountryTables list

        foreach (DataRow row in distinctValues.Rows)
        {
            //Remove filters on view
            view.RowFilter = String.Empty;
            //get distinct country name
            String country = row["Country"].ToString();
            //filter view for that country
            view.RowFilter = "Country = " + "'" + country + "'";
            //export filtered view to new datatable
            DataTable countryTable = view.ToTable();
            //add new datatable to allCountryTables 
            allCountryTables.Add(countryTable);
        }//for each

        foreach(DataTable tbl in allCountryTables){

            String table = getDataTable(tbl);
            MessageBox.Show(table);

        }//for
}//main


    public static string getDataTable(DataTable dt){

        string table = "";

        foreach (DataRow dataRow in dt.Rows)
        {
            foreach (var item in dataRow.ItemArray)
            {
                table = table + item.ToString() + "|";
            }//for

            table = table + Environment.NewLine;

        }//for

        return table;

    }//method

Answer 1

Certainly not the shortest method of doing this but since you are saying you are having .Net version issues this is a long way to get the job done. (Below code is untested)

DataTable table; //Your Datatable
List<DataTable> allCountryTables = new List<DataTable>();

//Get distinct countries from table
DataView view = new DataView(table);
DataTable distinctValues = view.ToTable(true, "Country");

//Create new DataTable for each of the distinct countries identified and add to allCountryTables list    
foreach (DataRow row in distinctValues.Rows)
{
    //Remove filters on view
    view.RowFilter = String.Empty;
    //get distinct country name
    String country = row["Country"].ToString());
    //filter view for that country
    view.RowFilter = "Country = " + country;
    //export filtered view to new datatable
    DataTable countryTable = view.ToTable();
    //add new datatable to allCountryTables 
    allCountryTables.Add(countryTable);
}

Answer 2

This should do the job:

var countryGroups = dataTable.AsEnumerable()
    .GroupBy(row => row.Field<string>("Country"));

Please remember about adding the following references: System.Data.DataSetExtensions

Answer 3

You can use LINQ to create a subset of DataTables for every country:

Dim groups = From row In tblEmp
             Let country = row.Field(Of String)("Country")
             Group row By country Into Group
             Select Group.CopyToDataTable()
Dim allCountryTables = groups.ToList()

Whoops, i was certain that there was a VB.NET tag....

var groups = from row in tblEmp.AsEnumerable()
             let country = row.Field<string>("Country")
             group row by country into Group
             select Group.CopyToDataTable();
List<DataTable> allCountryTables = groups.ToList();