CSharp - LINQ SQL CopyToDataTable<DataRow>

Introduction

CopyToDataTable operator gets sequence of modified DataRow objects into a DataTable.

Prototypes

The CopyToDataTable operator has two prototypes we cover.

This first prototype is called on an IEnumerable<DataRow> and returns a DataTable.

This is used to create a new DataTable object from a sequence of DataRow objects.

public static DataTable CopyToDataTable<T> (
        this IEnumerable<T> source
      ) where T : DataRow;

The second prototype is called on an IEnumerable<DataRow> of the source DataTable to update an already existing destination DataTable based on the LoadOption value specified.

public static void CopyToDataTable<T> (
     this IEnumerable<T> source,
     DataTable table,
     LoadOption options
   ) where T : DataRow;

The following are the available values for LoadOption:

  • OverwriteChanges: Both the current value and original value will be updated for each column.
  • PreserveChanges: Only the original value will be updated for each column.
  • Upsert: Only the current value will be updated for each column.

Demo

using System;
using System.Linq;
using System.Collections;
using System.Collections.Generic;
using System.Data;

class Program/* w  ww.j  av  a2s .  c  o  m*/
{
    static void Main(string[] args)
    {
        Student[] students = {
          new Student { Id = 1, Name = "Joe Ruby" },
          new Student { Id = 7, Name = "Apache Python" },
          new Student { Id = 13, Name = "Scala CSS" },
          new Student { Id = 72, Name = "Django SQL" }
        };

        DataTable dt1 = GetDataTable(students);

        Console.WriteLine("Original DataTable:");
        foreach (DataRow dataRow in dt1.AsEnumerable())
        {
            Console.WriteLine("Student Id = {0} is {1}", dataRow.Field<int>("Id"),
              dataRow.Field<string>("Name"));
        }
      (from s in dt1.AsEnumerable()
       where s.Field<string>("Name") == "Apache Python"
       select s).Single<DataRow>().SetField("Name", "C++");

        DataTable newTable = dt1.AsEnumerable().CopyToDataTable();

        Console.WriteLine("{0}New DataTable:", System.Environment.NewLine);
        foreach (DataRow dataRow in newTable.AsEnumerable())
        {
            Console.WriteLine("Student Id = {0} is {1}", dataRow.Field<int>("Id"),
              dataRow.Field<string>("Name"));
        }


    }
    static DataTable GetDataTable(Student[] students)
    {
        DataTable table = new DataTable();

        table.Columns.Add("Id", typeof(Int32));
        table.Columns.Add("Name", typeof(string));

        foreach (Student student in students)
        {
            table.Rows.Add(student.Id, student.Name);
        }

        return (table);
    }
    static void OutputDataTableHeader(DataTable dt, int columnWidth)
    {
        string format = string.Format("{0}0,-{1}{2}", "{", columnWidth, "}");

        //  Display the column headings.
        foreach (DataColumn column in dt.Columns)
        {
            Console.Write(format, column.ColumnName);
        }
        Console.WriteLine();
        foreach (DataColumn column in dt.Columns)
        {
            for (int i = 0; i < columnWidth; i++)
            {
                Console.Write("=");
            }
        }
        Console.WriteLine();
    }

}


class Student
{
    public int Id;
    public string Name;
}

Result

Related Topics