DataTable.Clone Method

Reference

Definition

Namespace:: System.Data

Assembly:: System.Data.Common.dll

Assembly:: System.Data.dll

Assembly:: netstandard.dll

Source:: DataTable.cs

Source:: DataTable.cs

Source:: DataTable.cs

Important

Some information relates to prerelease product that may be substantially modified before it’s released. Microsoft makes no warranties, express or implied, with respect to the information provided here.

Clones the structure of the DataTable, including all DataTable schemas and constraints.

public:
 virtual System::Data::DataTable ^ Clone();

public virtual System.Data.DataTable Clone ();

abstract member Clone : unit -> System.Data.DataTable
override this.Clone : unit -> System.Data.DataTable

Public Overridable Function Clone () As DataTable

Returns

DataTable

A new DataTable with the same schema as the current DataTable.

Examples

The following sample demonstrates how to update the structure and constraints of the destination table after you execute DataTable.Clone. The ClonedDataTable class will return a destination table and includes all the updating events. After the clone, structure changes in the source table won't be reflected in the destination table. Specifically, this sample will:

Update the changes of the columns in source table.
Update the changes of the UniqueConstraint in source table.
Update the changes of the ForeignKeyConstraint in source table.

using System;
using System.Linq;
using System.Data;
using System.ComponentModel;

class Program {
   static void Main(string[] args) {
      DataTable courses = NewCourseDataTable();
      Console.WriteLine("This is the source table:");
      WriteDataTable(courses);

      ClonedDataTable clonedResult = new ClonedDataTable(courses);
      DataTable clonedCourses = clonedResult.DestinationTable;
      Console.WriteLine("This is the destination table:");
      WriteDataTable(clonedCourses);

      // Add the events of updating column collection into the source table.
      clonedResult.UpdateAddedColumn();
      clonedResult.UpdateDeletedColumn();
      // Add a DataColumn in source table.
      DataColumn columnCredits = new DataColumn("Credits", typeof(Int32));
      courses.Columns.Add(columnCredits);
      Console.WriteLine("After add a column in source table, it's the result in the destination:");
      WriteDataTable(clonedCourses);

      // Add the event of updating UniqueConstraint into the source table.
      clonedResult.UpdateUniqueConstraint();

      // Add the unique constraint in source table.
      UniqueConstraint uniqueConstraint = new UniqueConstraint(courses.Columns["CourseId"]);
      courses.Constraints.Add(uniqueConstraint);

      Console.WriteLine(@"If we add the unique constraint in source table and then insert the duplicate
rows into the destination table, we will get the following error:");
      InsertDuplicateData(clonedCourses);
      Console.WriteLine();

      // Add the event of updating ForeignKeyConstraint into the source table.
      clonedResult.UpdateForeignKeyConstraint();

      // Add the ForeignKeyConstraint into the source table.
      DataTable departments = NewDepartmentDataTable();
      DataSet dataset = new DataSet();

      dataset.Tables.Add(courses);
      dataset.Tables.Add(clonedCourses);
      dataset.Tables.Add(departments);

      ForeignKeyConstraint foreignKey = new ForeignKeyConstraint(departments.Columns["DepartmentId"], courses.Columns["DepartmentId"]);
      courses.Constraints.Add(foreignKey);

      Console.WriteLine(@"If we add the foreign key constraint in source table and then insert a row
without the parent  into the destination table, we will get the following error:");
      InsertNoParentRow(clonedCourses);
      Console.WriteLine();

      Console.WriteLine("Please press any key to exit...");
      Console.ReadKey();
   }

   static private DataTable NewCourseDataTable() {
      DataTable newTable = new DataTable();

      DataColumn[] columns ={
                                      new DataColumn("CourseId", typeof(String)),
                                      new DataColumn("CourseName",typeof(String)),
                                      new DataColumn("DepartmentId", typeof(Int32))
                                  };

      newTable.Columns.AddRange(columns);

      newTable.Rows.Add("C1045", "Calculus", 7);
      newTable.Rows.Add("C1061", "Physics", 1);
      newTable.Rows.Add("C2021", "Composition", 2);
      newTable.Rows.Add("C2042", "Literature", 2);

      return newTable;
   }

   static private DataTable NewDeparmentDataTable() {
      DataTable newTable = new DataTable();

      DataColumn[] columns ={
                                      new DataColumn("DepartmentId", typeof(Int32)),
                                      new DataColumn("Name",typeof(String)),
                                  };

      newTable.Columns.AddRange(columns);

      newTable.Rows.Add(1, "Engineering");
      newTable.Rows.Add(2, "English");
      newTable.Rows.Add(4, "Economics");
      newTable.Rows.Add(7, "Mathematics");

      return newTable;
   }

   static private void WriteDataTable(DataTable table) {
      if (table == null)
         return;

      foreach (DataColumn column in table.Columns) {
         Console.Write("{0,-15}", column.ColumnName);
      }
      Console.WriteLine();

      foreach (DataRow row in table.Rows) {
         for (int i = 0; i < table.Columns.Count; i++)
            Console.Write("{0,-15}", row[i].ToString());
         Console.WriteLine();
      }

      Console.WriteLine();
   }

   static private void InsertDuplicateData(DataTable table) {
      try {
         table.Rows.Add("C1045", "Calculus", 7);
         table.Rows.Add("C1045", "Calculus", 7);
      } catch (Exception e) {
         Console.WriteLine("\"" + e.Message + "\"");
      }
   }

   private static void InsertNoParentRow(DataTable table) {
      try {
         table.Rows.Add("C1061", "Physics", 11);
      } catch (Exception e) {
         Console.WriteLine("\"" + e.Message + "\"");
      }
   }
}

public class ClonedDataTable {
   private DataTable sourceTable;
   private DataTable destinationTable;

   public ClonedDataTable(DataTable source) {
      sourceTable = source;
      // set the cloned result
      destinationTable = sourceTable.Clone();
   }

   public void UpdateAddedColumn() {
      sourceTable.Columns.CollectionChanged += new CollectionChangeEventHandler(ColumnAdded);
   }

   public void UpdateDeletedColumn() {
      sourceTable.Columns.CollectionChanged += new CollectionChangeEventHandler(ColumnDeleted);
   }

   public void UpdateUniqueConstraint() {
      sourceTable.Constraints.CollectionChanged += new CollectionChangeEventHandler(UniqueConstraint_Changed);
   }

   public void UpdateForeignKeyConstraint() {
      sourceTable.Constraints.CollectionChanged += new CollectionChangeEventHandler(ForeignKeyConstraint_Changed);
   }

   // After the source table adds a column, the method will add the same column in the destination table.
   void ColumnAdded(object sender, System.ComponentModel.CollectionChangeEventArgs e) {
      if (e.Action == CollectionChangeAction.Add) {
         DataColumn column = e.Element as DataColumn;

         if (column != null) {
            DataColumn newColumn = new DataColumn(column.ColumnName, column.DataType, column.Expression, column.ColumnMapping);

            if (!destinationTable.Columns.Contains(newColumn.ColumnName))
               destinationTable.Columns.Add(newColumn);
         }
      }
   }

   // After the source table deletes a column, the method will delete the same column in the destination table.
   void ColumnDeleted(object sender, CollectionChangeEventArgs e) {
      if (e.Action == CollectionChangeAction.Remove) {
         DataColumn column = e.Element as DataColumn;

         if (column != null)
            if (destinationTable.Columns.Contains(column.ColumnName))
               destinationTable.Columns.Remove(column.ColumnName);
      }
   }

   // After the source table changes the UniqueConstraint, this method changes the same UniqueConstraint in destination table.
   void UniqueConstraint_Changed(object sender, CollectionChangeEventArgs e) {
      UniqueConstraint constraint = e.Element as UniqueConstraint;

      if (constraint == null)
         return;

      String constraintName = constraint.ConstraintName;

      if (e.Action == CollectionChangeAction.Add) {
         DataColumn[] columns = new DataColumn[constraint.Columns.Count()];
         Boolean isPrimaryKey = constraint.IsPrimaryKey;

         // Get the columns used in new constraint from the destination table.
         for (Int32 i = 0; i < constraint.Columns.Count(); i++) {
            String columnName = constraint.Columns[i].ColumnName;

            if (destinationTable.Columns.Contains(columnName))
               columns[i] = destinationTable.Columns[columnName];
            else
               return;
         }

         UniqueConstraint newConstraint = new UniqueConstraint(constraintName, columns, isPrimaryKey);

         if (!destinationTable.Constraints.Contains(constraintName))
            destinationTable.Constraints.Add(newConstraint);

      } else if (e.Action == CollectionChangeAction.Remove)
         if (destinationTable.Constraints.Contains(constraintName))
            destinationTable.Constraints.Remove(constraintName);
   }

   // After the source table changes the ForeignKeyConstraint, this method changes
   // the same ForeignKeyConstraint in the destination table.
   void ForeignKeyConstraint_Changed(object sender, CollectionChangeEventArgs e) {
      ForeignKeyConstraint constraint = e.Element as ForeignKeyConstraint;

      if (constraint == null)
         return;

      // If the source and destination are not in the same DataSet, don't change the ForeignKeyConstraint.
      if (sourceTable.DataSet != destinationTable.DataSet)
         return;

      String constraintName = constraint.ConstraintName;

      if (e.Action == CollectionChangeAction.Add) {
         DataColumn[] columns = new DataColumn[constraint.Columns.Count()];
         DataColumn[] parentColumns = constraint.RelatedColumns;

         // Get the columns used in new constraint from the destination table.
         for (int i = 0; i < constraint.Columns.Count(); i++) {
            String columnName = constraint.Columns[i].ColumnName;

            if (destinationTable.Columns.Contains(columnName))
               columns[i] = destinationTable.Columns[columnName];
            else
               return;
         }

         ForeignKeyConstraint newConstraint = new ForeignKeyConstraint(constraintName, parentColumns, columns);
         newConstraint.AcceptRejectRule = constraint.AcceptRejectRule;
         newConstraint.DeleteRule = constraint.DeleteRule;
         newConstraint.UpdateRule = constraint.UpdateRule;

         if (!destinationTable.Constraints.Contains(constraintName))
            destinationTable.Constraints.Add(newConstraint);
      } else if (e.Action == CollectionChangeAction.Remove)
         if (destinationTable.Constraints.Contains(constraintName))
            destinationTable.Constraints.Remove(constraintName);
   }

   // return the destination table.
   public DataTable DestinationTable {
      get { return destinationTable; }
   }
}

This sample shows how to modify data in a DataTable and update the data source.

First, create a database:

USE [master]
GO

CREATE DATABASE [MySchool]

GO

USE [MySchool]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Course]([CourseID] [nvarchar](10) NOT NULL,
[Year] [smallint] NOT NULL,
[Title] [nvarchar](100) NOT NULL,
[Credits] [int] NOT NULL,
[DepartmentID] [int] NOT NULL,
 CONSTRAINT [PK_Course] PRIMARY KEY CLUSTERED
(
[CourseID] ASC,
[Year] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]

GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Department]([DepartmentID] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](50) NOT NULL,
[Budget] [money] NOT NULL,
[StartDate] [datetime] NOT NULL,
[Administrator] [int] NULL,
 CONSTRAINT [PK_Department] PRIMARY KEY CLUSTERED
(
[DepartmentID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]

GO

INSERT [dbo].[Course] ([CourseID], [Year], [Title], [Credits], [DepartmentID]) VALUES (N'C1045', 2012, N'Calculus', 4, 7)
INSERT [dbo].[Course] ([CourseID], [Year], [Title], [Credits], [DepartmentID]) VALUES (N'C1061', 2012, N'Physics', 4, 1)
INSERT [dbo].[Course] ([CourseID], [Year], [Title], [Credits], [DepartmentID]) VALUES (N'C2021', 2012, N'Composition', 3, 2)
INSERT [dbo].[Course] ([CourseID], [Year], [Title], [Credits], [DepartmentID]) VALUES (N'C2042', 2012, N'Literature', 4, 2)

SET IDENTITY_INSERT [dbo].[Department] ON

INSERT [dbo].[Department] ([DepartmentID], [Name], [Budget], [StartDate], [Administrator]) VALUES (1, N'Engineering', 350000.0000, CAST(0x0000999C00000000 AS DateTime), 2)
INSERT [dbo].[Department] ([DepartmentID], [Name], [Budget], [StartDate], [Administrator]) VALUES (2, N'English', 120000.0000, CAST(0x0000999C00000000 AS DateTime), 6)
INSERT [dbo].[Department] ([DepartmentID], [Name], [Budget], [StartDate], [Administrator]) VALUES (4, N'Economics', 200000.0000, CAST(0x0000999C00000000 AS DateTime), 4)
INSERT [dbo].[Department] ([DepartmentID], [Name], [Budget], [StartDate], [Administrator]) VALUES (7, N'Mathematics', 250024.0000, CAST(0x0000999C00000000 AS DateTime), 3)
SET IDENTITY_INSERT [dbo].[Department] OFF

ALTER TABLE [dbo].[Course]  WITH CHECK ADD  CONSTRAINT [FK_Course_Department] FOREIGN KEY([DepartmentID])
REFERENCES [dbo].[Department] ([DepartmentID])
GO
ALTER TABLE [dbo].[Course] CHECK CONSTRAINT [FK_Course_Department]
GO

using System;
using System.Data;
using System.Data.SqlClient;

class Program {
   static void Main(string[] args) {

   string MySchoolConnectionString = "Data Source=(local);Initial Catalog=MySchool;Integrated Security=True";

      // Get Data
      String selectString =
       @"Select [CourseID],[Year],[Title],[Credits],[DepartmentID] From [dbo].[Course];
               Select [DepartmentID],[Name],[Budget],[StartDate],[Administrator] From [dbo].[Department] ";

      DataSet dataSet = new DataSet();
      DataTable course = dataSet.Tables.Add("Course");
      DataTable department = dataSet.Tables.Add("Department");

      Console.WriteLine("Get data from database:");
      GetDataTables(MySchoolConnectionString, selectString, dataSet, course, department);
      Console.WriteLine();

      // Use DataTable Edits to edit the data
      String updateString =
              @"Update [dbo].[Course] Set [Credits]=@Credits Where [CourseID]=@CourseID;";

      course.ColumnChanged += OnColumnChanged;

      // Set the Credits of first row is negative value, and set the Credits of second row is plus.
      ChangeCredits(course, course.Rows[0], -1);
      ChangeCredits(course, course.Rows[1], 11);

      UpdateDataTables(MySchoolConnectionString, updateString, dataSet, "Course",
          new SqlParameter("@CourseID", SqlDbType.NVarChar, 10, "CourseID"),
          new SqlParameter("@Credits", SqlDbType.Int, 4, "Credits"));
      Console.WriteLine("Only the Credits of second row is changed.");
      ShowDataTable(course);
      Console.WriteLine();

      // Delete and Remove from DataTable
      // Create the foreign key constraint, and set the DeleteRule with Cascade.
      ForeignKeyConstraint courseDepartFK = new ForeignKeyConstraint("CourseDepartFK", department.Columns["DepartmentID"], course.Columns["DepartmentID"]);
      courseDepartFK.DeleteRule = Rule.Cascade;
      courseDepartFK.UpdateRule = Rule.Cascade;
      courseDepartFK.AcceptRejectRule = AcceptRejectRule.None;
      course.Constraints.Add(courseDepartFK);

      String deleteString = @"Delete From [dbo].[Course] Where [CourseID]=@CourseID;";

      department.Rows[0].Delete();
      Console.WriteLine("If One row in Department table is deleted, the related rows in Course table will also be deleted.");
      Console.WriteLine("Department DataTable:");
      ShowDataTable(department);
      Console.WriteLine();
      Console.WriteLine("Course DataTable:");
      ShowDataTable(course);
      Console.WriteLine();
      // Update the delete operation
      DeleteDataTables(MySchoolConnectionString, deleteString, dataSet, "Course",
          new SqlParameter("@CourseID", SqlDbType.NVarChar, 10, "CourseID"));
      Console.WriteLine("After delete operation:");
      Console.WriteLine("Course DataTable:");
      ShowDataTable(course);
      Console.WriteLine();

      course.Rows.RemoveAt(0);
      Console.WriteLine("Now we remove one row from Course:");
      ShowDataTable(course);
      DeleteDataTables(MySchoolConnectionString, deleteString, dataSet, "Course",
          new SqlParameter("@CourseID", SqlDbType.NVarChar, 10, "CourseID"));
   }

   // Use SqlDataAdapter to get data.
   private static void GetDataTables(String connectionString, String selectString,
       DataSet dataSet, params DataTable[] tables) {
      using (SqlDataAdapter adapter = new SqlDataAdapter()) {
         adapter.SelectCommand = new SqlCommand(selectString);
         adapter.SelectCommand.Connection = new SqlConnection(connectionString);

         adapter.Fill(0, 0, tables);

         foreach (DataTable table in dataSet.Tables) {
            Console.WriteLine("Data in {0}:", table.TableName);
            ShowDataTable(table);
            Console.WriteLine();
         }
      }
   }

   // Use SqlDataAdapter to update the updata operation.
   private static void UpdateDataTables(String connectionString, String updateString,
       DataSet dataSet, String tableName, params SqlParameter[] parameters) {
      using (SqlDataAdapter adapter = new SqlDataAdapter()) {
         adapter.UpdateCommand = new SqlCommand(updateString);
         adapter.UpdateCommand.Parameters.AddRange(parameters);
         adapter.UpdateCommand.Connection = new SqlConnection(connectionString);

         adapter.Update(dataSet, tableName);
      }
   }

   // Use SqlDataAdapter to update delete operation.
   private static void DeleteDataTables(String connectionString, String deleteString,
       DataSet dataSet, String tableName, params SqlParameter[] parameters) {
      using (SqlDataAdapter adapter = new SqlDataAdapter()) {
         adapter.DeleteCommand = new SqlCommand(deleteString);
         adapter.DeleteCommand.Parameters.AddRange(parameters);
         adapter.DeleteCommand.Connection = new SqlConnection(connectionString);

         adapter.Update(dataSet, tableName);
      }
   }

   // Use DataTable Edits to modify the data.
   private static void ChangeCredits(DataTable table, DataRow row, Int32 credits) {
      row.BeginEdit();
      Console.WriteLine("We change row {0}", table.Rows.IndexOf(row));
      row["Credits"] = credits;
      row.EndEdit();
   }

   // The method will be invoked when the value in DataTable is changed.
   private static void OnColumnChanged(Object sender, DataColumnChangeEventArgs args) {
      Int32 credits = 0;
      // If Credits is changed and the value is negative, we'll cancel the edit.
      if ((args.Column.ColumnName == "Credits") &&
          (!Int32.TryParse(args.ProposedValue.ToString(), out credits) || credits < 0)) {
         Console.WriteLine("The value of Credits is invalid. Edit canceled.");
         args.Row.CancelEdit();
      }
   }

   // Display the column and value of DataTable.
   private static void ShowDataTable(DataTable table) {
      foreach (DataColumn col in table.Columns) {
         Console.Write("{0,-14}", col.ColumnName);
      }
      Console.WriteLine("{0,-14}", "RowState");

      foreach (DataRow row in table.Rows) {
         if (row.RowState == DataRowState.Deleted) {
            foreach (DataColumn col in table.Columns) {
               if (col.DataType.Equals(typeof(DateTime)))
                  Console.Write("{0,-14:d}", row[col, DataRowVersion.Original]);
               else if (col.DataType.Equals(typeof(Decimal)))
                  Console.Write("{0,-14:C}", row[col, DataRowVersion.Original]);
               else
                  Console.Write("{0,-14}", row[col, DataRowVersion.Original]);
            }
         }
         else {
            foreach (DataColumn col in table.Columns) {
               if (col.DataType.Equals(typeof(DateTime)))
                  Console.Write("{0,-14:d}", row[col]);
               else if (col.DataType.Equals(typeof(Decimal)))
                  Console.Write("{0,-14:C}", row[col]);
               else
                  Console.Write("{0,-14}", row[col]);
            }
         }
         Console.WriteLine("{0,-14}", row.RowState);
      }
   }
}

Remarks

If these classes have been derived, the clone will also be of the same derived classes.

Clone creates a new DataTable with the same structure as the original DataTable, but does not copy any data (the new DataTable will not contain any DataRows). To copy both the structure and data into a new DataTable, use Copy.

Applies to

Dela via