Udostępnij za pośrednictwem


DataTable.Clone Metoda

Definicja

Klonuje strukturę obiektu DataTable, w tym wszystkie DataTable schematy i ograniczenia.

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

Zwraca

Nowy DataTable z tym samym schematem co bieżący DataTable.

Przykłady

W poniższym przykładzie pokazano, jak zaktualizować strukturę i ograniczenia tabeli docelowej po wykonaniu polecenia DataTable.Clone. Klasa ClonedDataTable zwróci tabelę docelową i zawiera wszystkie zdarzenia aktualizacji. Po sklonowania zmiany struktury w tabeli źródłowej nie zostaną odzwierciedlone w tabeli docelowej. W szczególności ten przykład obejmuje następujące elementy:

  • Zaktualizuj zmiany kolumn w tabeli źródłowej.

  • Zaktualizuj zmiany w tabeli źródłowej UniqueConstraint.

  • Zaktualizuj zmiany w tabeli źródłowej ForeignKeyConstraint.

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; }
   }
}

W tym przykładzie pokazano, jak zmodyfikować dane w tabeli DataTable i zaktualizować źródło danych.

Najpierw utwórz bazę danych:

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);
      }
   }
}

Uwagi

Jeśli te klasy zostały pochodne, klon będzie również z tych samych klas pochodnych.

Klon tworzy nową DataTable strukturę o tej samej strukturze co oryginalna DataTable, ale nie kopiuje żadnych danych (nowa nie DataTable będzie zawierać żadnego DataRowselementu ). Aby skopiować zarówno strukturę, jak i dane do nowego DataTableelementu , użyj polecenia Copy.

Dotyczy

Zobacz też