c# DataTable Copy / Filter / Sort & Clone

datatableIf you’re like me (I hope not too much like me) you probably find yourself using DataTables to hold lots of data for fast, flexible in memory usage. I get asked often, “how can I copy a DataTable?” or even more often, “how can I copy a DataTable, but change the sort or modify the rows”. Look no further. Below you’ll find my thoughts:

 

Example one (a simple copy with a sort difference):

    //datatable
    var table = new DataTable();

    //get some data
    using (var conn = new SqlConnection(yourSqlConn))
        {
            var comm = new SqlCommand(@"select * from someTable order by someColumn", conn);
            comm.CommandType = CommandType.Text;
            conn.Open();
            var data = comm.ExecuteReader();
            table.Load(data);
        }

    //bind to some control (repeater)
    rptFirstList.DataSource = table;
    rptFirstList.DataBind();

    //second table
    var secondTable = new DataTable();
    secondTable = table.Copy();
    secondTable.DefaultView.Sort = "someOtherColumn";

    //bind second
    rptSecondList.DataSource = secondTable;
    rptSecondList.DataBind();

Example two (creating a copy of the table but allowing a filter, sort or other criteria):

    //datatable
    var table = new DataTable();

    //get some data
    using (var conn = new SqlConnection(yourSqlConn))
        {
            var comm = new SqlCommand(@"select * from someTable order by someColumn", conn);
            comm.CommandType = CommandType.Text;
            conn.Open();
            var data = comm.ExecuteReader();
            table.Load(data);
        }

    //bind to some control (repeater)
    rptFirstList.DataSource = table;
    rptFirstList.DataBind();

    //second table
    var secondTable = new DataTable();

    //clone to get columns - NO data is copied
    secondTable = table.Clone();
    //loop through rows and import based on filter
    foreach (DataRow dr in table.Select("someColumn = 'value'","someColumnToSort")) {

    secondTable.ImportRow(dr);

    }

    //bind second
    rptSecondList.DataSource = secondTable;
    rptSecondList.DataBind();
c# DataTable Copy / Filter / Sort & Clone

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