去除DataTable中的重复行

最新推荐文章于 2020-06-15 14:11:02 发布

原创最新推荐文章于 2020-06-15 14:11:02 发布 · 8.7k 阅读

3 ·

本内容遵循CC 4.0 BY-SA版权协议

标签

#output #table #string #object #each

Web开发-.NET 专栏收录该内容

4 篇文章

订阅专栏

本文介绍了一种简单有效的方法来去除DataTable中的重复行，通过使用DataView的ToTable方法，可以轻松实现这一目标，同时提供了详细的代码示例。

在开发过程中，我们有时候会遇到这样或那样的情况以至于DataTable中存在重复行，但是我们有想要去掉这些重复行，我们可以采用如下方法，以代替复杂的循环判断。

string[] straColumn = new string[DataSetName.Tables[DataTableName].Columns.Count]; for (int LoopIndex = 0; LoopIndex < DataSetName.Tables[DataTableName].Columns.Count; LoopIndex++) { straColumn[LoopIndex] = DataSetName.Tables[DataTableName].Columns[LoopIndex].ColumnName; } DataTable dtGrid = DataSetName.Tables[DataTableName].DefaultView.ToTable(true, straColumn);

之后再用新的dtGrid替换DataSet中相应的DataTable即可。

在这个过程中，我们用到了DataView提供的重载方法ToTable。以下内容来源于http://msdn.microsoft.com/zh-cn/library/a8ycds2f.aspx。

1、ToTable()方法

返回值

类型：System.Data . . :: .DataTable
一个新的 DataTable 实例，其中包含所请求的行和列。

备注

由于此方法不允许指定输出 DataTable 的名称，因此其名称与源 DataTable 相同。由于此方法不允许指定可用列的子集，因此输出表与输入表包含相同的列。

示例

下面的控制台应用程序示例创建一个 DataTable，用数据填充 DataTable，并根据原始数据创建一个筛选视图，最后创建一个包含筛选出的行的 DataTable。

private static void DemonstrateDataView() { // Create a DataTable with three columns. DataTable table = new DataTable("NewTable"); Console.WriteLine("Original table name: " + table.TableName); DataColumn column = new DataColumn("ID", typeof(System.Int32)); table.Columns.Add(column); column = new DataColumn("Category", typeof(System.String)); table.Columns.Add(column); column = new DataColumn("Product", typeof(System.String)); table.Columns.Add(column); column = new DataColumn("QuantityInStock", typeof(System.Int32)); table.Columns.Add(column); // Add some items. DataRow row = table.NewRow(); row.ItemArray = new object[] { 1, "Fruit", "Apple", 14 }; table.Rows.Add(row); row = table.NewRow(); row.ItemArray = new object[] { 2, "Fruit", "Orange", 27 }; table.Rows.Add(row); row = table.NewRow(); row.ItemArray = new object[] { 3, "Bread", "Muffin", 23 }; table.Rows.Add(row); row = table.NewRow(); row.ItemArray = new object[] { 4, "Fish", "Salmon", 12 }; table.Rows.Add(row); // Mark all rows as "accepted". Not really required // for this particular example. table.AcceptChanges(); // Print current table values. PrintTableOrView(table, "Current Values in Table"); DataView view = new DataView(table); view.RowFilter = "QuantityInStock > 15"; PrintTableOrView(view, "Current Values in View"); DataTable newTable = view.ToTable(); PrintTableOrView(newTable, "Table created from filtered DataView"); Console.WriteLine("New table name: " + newTable.TableName); Console.WriteLine("Press any key to continue."); Console.ReadKey(); } private static void PrintTableOrView(DataView dv, string label) { System.IO.StringWriter sw; string output; DataTable table = dv.Table; Console.WriteLine(label); // Loop through each row in the view. foreach (DataRowView rowView in dv) { sw = new System.IO.StringWriter(); // Loop through each column. foreach (DataColumn col in table.Columns) { // Output the value of each column's data. sw.Write(rowView[col.ColumnName].ToString() + ", "); } output = sw.ToString(); // Trim off the trailing ", ", so the output looks correct. if (output.Length > 2) { output = output.Substring(0, output.Length - 2); } // Display the row in the console window. Console.WriteLine(output); } Console.WriteLine(); } private static void PrintTableOrView(DataTable table, string label) { System.IO.StringWriter sw; string output; Console.WriteLine(label); // Loop through each row in the table. foreach (DataRow row in table.Rows) { sw = new System.IO.StringWriter(); // Loop through each column. foreach (DataColumn col in table.Columns) { // Output the value of each column's data. sw.Write(row[col].ToString() + ", "); } output = sw.ToString(); // Trim off the trailing ", ", so the output looks correct. if (output.Length > 2) { output = output.Substring(0, output.Length - 2); } // Display the row in the console window. Console.WriteLine(output); } // Console.WriteLine(); }

该示例在控制台窗口中显示以下文本：

Original table name: NewTable
Current Values in Table
1, Fruit, Apple, 14
2, Fruit, Orange, 27
3, Bread, Muffin, 23
4, Fish, Salmon, 12

Current Values in View
2, Fruit, Orange, 27
3, Bread, Muffin, 23

Table created from filtered DataView
2, Fruit, Orange, 27
3, Bread, Muffin, 23

New table name: NewTable

2、ToTable(String)方法

参数

tableName

类型：System . . :: .String
返回的 DataTable 的名称。

返回值

类型：System.Data..::.DataTable
一个新的 DataTable 实例，其中包含所请求的行和列。

备注

由于此方法不允许指定可用列的子集，因此输出表与输入表包含相同的列。

示例

下面的控制台应用程序示例创建一个 DataTable，用数据填充 DataTable，并根据原始数据创建一个筛选视图，最后用一个新名称创建包含筛选出的行的 DataTable。

private static void DemonstrateDataView() { // Create a DataTable with three columns. DataTable table = new DataTable("NewTable"); Console.WriteLine("Original table name: " + table.TableName); DataColumn column = new DataColumn("ID", typeof(System.Int32)); table.Columns.Add(column); column = new DataColumn("Category", typeof(System.String)); table.Columns.Add(column); column = new DataColumn("Product", typeof(System.String)); table.Columns.Add(column); column = new DataColumn("QuantityInStock", typeof(System.Int32)); table.Columns.Add(column); // Add some items. DataRow row = table.NewRow(); row.ItemArray = new object[] { 1, "Fruit", "Apple", 14 }; table.Rows.Add(row); row = table.NewRow(); row.ItemArray = new object[] { 2, "Fruit", "Orange", 27 }; table.Rows.Add(row); row = table.NewRow(); row.ItemArray = new object[] { 3, "Bread", "Muffin", 23 }; table.Rows.Add(row); row = table.NewRow(); row.ItemArray = new object[] { 4, "Fish", "Salmon", 12 }; table.Rows.Add(row); // Mark all rows as "accepted". Not really required // for this particular example. table.AcceptChanges(); // Print current table values. PrintTableOrView(table, "Current Values in Table"); DataView view = new DataView(table); view.RowFilter = "QuantityInStock > 15"; PrintTableOrView(view, "Current Values in View"); DataTable newTable = view.ToTable("FilteredTable"); PrintTableOrView(newTable, "Table created from filtered DataView"); Console.WriteLine("New table name: " + newTable.TableName); Console.WriteLine("Press any key to continue."); Console.ReadKey(); } private static void PrintTableOrView(DataView dv, string label) { System.IO.StringWriter sw; string output; DataTable table = dv.Table; Console.WriteLine(label); // Loop through each row in the view. foreach (DataRowView rowView in dv) { sw = new System.IO.StringWriter(); // Loop through each column. foreach (DataColumn col in table.Columns) { // Output the value of each column's data. sw.Write(rowView[col.ColumnName].ToString() + ", "); } output = sw.ToString(); // Trim off the trailing ", ", so the output looks correct. if (output.Length > 2) { output = output.Substring(0, output.Length - 2); } // Display the row in the console window. Console.WriteLine(output); } Console.WriteLine(); } private static void PrintTableOrView(DataTable table, string label) { System.IO.StringWriter sw; string output; Console.WriteLine(label); // Loop through each row in the table. foreach (DataRow row in table.Rows) { sw = new System.IO.StringWriter(); // Loop through each column. foreach (DataColumn col in table.Columns) { // Output the value of each column's data. sw.Write(row[col].ToString() + ", "); } output = sw.ToString(); // Trim off the trailing ", ", so the output looks correct. if (output.Length > 2) { output = output.Substring(0, output.Length - 2); } // Display the row in the console window. Console.WriteLine(output); } // Console.WriteLine(); }

该示例在控制台窗口中显示以下文本：

Original table name: NewTable
Current Values in Table
1, Fruit, Apple, 14
2, Fruit, Orange, 27
3, Bread, Muffin, 23
4, Fish, Salmon, 12

Current Values in View
2, Fruit, Orange, 27
3, Bread, Muffin, 23

Table created from filtered DataView
2, Fruit, Orange, 27
3, Bread, Muffin, 23

New table name: FilteredTable

3、ToTable(Boolean,String[])方法

参数

distinct

类型：System . . :: .Boolean
如果为 true，则返回的 DataTable 将包含所有列都具有不同值的行。默认值为 false。

columnNames

类型： array<System . . :: .String > [] () []
一个字符串数组，包含要包括在返回的 DataTable 中的列名的列表。 DataTable 包含指定的列，其顺序与这些列在该数组中的顺序相同

返回值

类型：System.Data..::.DataTable
一个新的 DataTable 实例，其中包含所请求的行和列。

备注

由于此方法不允许指定输出 DataTable 的名称，因此其名称与源 DataTable 的名称相同。

示例

下面的控制台应用程序示例创建一个 DataTable，用数据填充 DataTable ，对 DataView 进行排序，最后创建一个仅包含两个列的 DataTable ，这两个列仅限于所有值都唯一的行。

private static void DemonstrateDataView() { // Create a DataTable with three columns. DataTable table = new DataTable("NewTable"); Console.WriteLine("Original table name: " + table.TableName); DataColumn column = new DataColumn("ID", typeof(System.Int32)); table.Columns.Add(column); column = new DataColumn("Category", typeof(System.String)); table.Columns.Add(column); column = new DataColumn("Product", typeof(System.String)); table.Columns.Add(column); column = new DataColumn("QuantityInStock", typeof(System.Int32)); table.Columns.Add(column); // Add some items. DataRow row = table.NewRow(); row.ItemArray = new object[] { 1, "Fruit", "Apple", 14 }; table.Rows.Add(row); row = table.NewRow(); row.ItemArray = new object[] { 2, "Fruit", "Orange", 27 }; table.Rows.Add(row); row = table.NewRow(); row.ItemArray = new object[] { 3, "Bread", "Muffin", 23 }; table.Rows.Add(row); row = table.NewRow(); row.ItemArray = new object[] { 4, "Fish", "Salmon", 12 }; table.Rows.Add(row); row = table.NewRow(); row.ItemArray = new object[] { 5, "Fish", "Salmon", 15 }; table.Rows.Add(row); row = table.NewRow(); row.ItemArray = new object[] { 6, "Bread", "Croissant", 23}; table.Rows.Add(row); // Mark all rows as "accepted". Not required // for this particular example. table.AcceptChanges(); // Print current table values. PrintTableOrView(table, "Current Values in Table"); DataView view = new DataView(table); view.Sort = "Category"; PrintTableOrView(view, "Current Values in View"); DataTable newTable = view.ToTable(true, "Category", "QuantityInStock"); PrintTableOrView(newTable, "Table created from sorted DataView"); Console.WriteLine("New table name: " + newTable.TableName); Console.WriteLine("Press any key to continue."); Console.ReadKey(); } private static void PrintTableOrView(DataView dv, string label) { System.IO.StringWriter sw; string output; DataTable table = dv.Table; Console.WriteLine(label); // Loop through each row in the view. foreach (DataRowView rowView in dv) { sw = new System.IO.StringWriter(); // Loop through each column. foreach (DataColumn col in table.Columns) { // Output the value of each column's data. sw.Write(rowView[col.ColumnName].ToString() + ", "); } output = sw.ToString(); // Trim off the trailing ", ", so the output looks correct. if (output.Length > 2) { output = output.Substring(0, output.Length - 2); } // Display the row in the console window. Console.WriteLine(output); } Console.WriteLine(); } private static void PrintTableOrView(DataTable table, string label) { System.IO.StringWriter sw; string output; Console.WriteLine(label); // Loop through each row in the table. foreach (DataRow row in table.Rows) { sw = new System.IO.StringWriter(); // Loop through each column. foreach (DataColumn col in table.Columns) { // Output the value of each column's data. sw.Write(row[col].ToString() + ", "); } output = sw.ToString(); // Trim off the trailing ", ", so the output looks correct. if (output.Length > 2) { output = output.Substring(0, output.Length - 2); } // Display the row in the console window. Console.WriteLine(output); } // Console.WriteLine(); }

该示例在控制台窗口中显示以下文本：

Original table name: NewTable
Current Values in Table
1, Fruit, Apple, 14
2, Fruit, Orange, 27
3, Bread, Muffin, 23
4, Fish, Salmon, 12
5, Fish, Salmon, 15
6, Bread, Croissant, 23

Current Values in View
3, Bread, Muffin, 23
6, Bread, Croissant, 23
4, Fish, Salmon, 12
5, Fish, Salmon, 15
1, Fruit, Apple, 14
2, Fruit, Orange, 27

Table created from sorted DataView
Bread, 23
Fish, 12
Fish, 15
Fruit, 14
Fruit, 27

New table name: NewTable

4、ToTable(String,Boolean,String[])方法

参数

tableName

类型：System . . :: .String
返回的 DataTable 的名称。

distinct

类型：System . . :: .Boolean
如果为 true，则返回的 DataTable 将包含所有列都具有不同值的行。默认值为 false。

columnNames

类型： array<System . . :: .String > [] () []
一个字符串数组，包含要包括在返回的 DataTable 中的列名的列表。 DataTable 包含指定的列，其顺序与这些列在该数组中的顺序相同。

返回值

类型：System.Data . . :: .DataTable
一个新的 DataTable 实例，其中包含所请求的行和列。

备注

如果必须检索可用列的子集中的不同的值，请使用 ToTable 方法的此重载版本，为返回的 DataTable 指定一个新名称。如果不需要不同的行或列的子集，请参见 ToTable。

示例

下面的控制台应用程序示例创建一个 DataTable，用数据填充 DataTable，对 DataView 进行排序，最后使用新的名称创建一个仅包含两个列的 DataTable ，这两个列仅限于所有值都唯一的行。

private static void DemonstrateDataView() { // Create a DataTable with three columns. DataTable table = new DataTable("NewTable"); Console.WriteLine("Original table name: " + table.TableName); DataColumn column = new DataColumn("ID", typeof(System.Int32)); table.Columns.Add(column); column = new DataColumn("Category", typeof(System.String)); table.Columns.Add(column); column = new DataColumn("Product", typeof(System.String)); table.Columns.Add(column); column = new DataColumn("QuantityInStock", typeof(System.Int32)); table.Columns.Add(column); // Add some items. DataRow row = table.NewRow(); row.ItemArray = new object[] { 1, "Fruit", "Apple", 14 }; table.Rows.Add(row); row = table.NewRow(); row.ItemArray = new object[] { 2, "Fruit", "Orange", 27 }; table.Rows.Add(row); row = table.NewRow(); row.ItemArray = new object[] { 3, "Bread", "Muffin", 23 }; table.Rows.Add(row); row = table.NewRow(); row.ItemArray = new object[] { 4, "Fish", "Salmon", 12 }; table.Rows.Add(row); row = table.NewRow(); row.ItemArray = new object[] { 5, "Fish", "Salmon", 15 }; table.Rows.Add(row); row = table.NewRow(); row.ItemArray = new object[] { 6, "Bread", "Croissant", 23}; table.Rows.Add(row); // Mark all rows as "accepted". Not required // for this particular example. table.AcceptChanges(); // Print current table values. PrintTableOrView(table, "Current Values in Table"); DataView view = new DataView(table); view.Sort = "Category"; PrintTableOrView(view, "Current Values in View"); DataTable newTable = view.ToTable("UniqueData", true, "Category", "QuantityInStock"); PrintTableOrView(newTable, "Table created from sorted DataView"); Console.WriteLine("New table name: " + newTable.TableName); Console.WriteLine("Press any key to continue."); Console.ReadKey(); } private static void PrintTableOrView(DataView dv, string label) { System.IO.StringWriter sw; string output; DataTable table = dv.Table; Console.WriteLine(label); // Loop through each row in the view. foreach (DataRowView rowView in dv) { sw = new System.IO.StringWriter(); // Loop through each column. foreach (DataColumn col in table.Columns) { // Output the value of each column's data. sw.Write(rowView[col.ColumnName].ToString() + ", "); } output = sw.ToString(); // Trim off the trailing ", ", so the output looks correct. if (output.Length > 2) { output = output.Substring(0, output.Length - 2); } // Display the row in the console window. Console.WriteLine(output); } Console.WriteLine(); } private static void PrintTableOrView(DataTable table, string label) { System.IO.StringWriter sw; string output; Console.WriteLine(label); // Loop through each row in the table. foreach (DataRow row in table.Rows) { sw = new System.IO.StringWriter(); // Loop through each column. foreach (DataColumn col in table.Columns) { // Output the value of each column's data. sw.Write(row[col].ToString() + ", "); } output = sw.ToString(); // Trim off the trailing ", ", so the output looks correct. if (output.Length > 2) { output = output.Substring(0, output.Length - 2); } // Display the row in the console window. Console.WriteLine(output); } // Console.WriteLine(); }

该示例在控制台窗口中显示以下输出：

Original table name: NewTable
Current Values in Table
1, Fruit, Apple, 14
2, Fruit, Orange, 27
3, Bread, Muffin, 23
4, Fish, Salmon, 12
5, Fish, Salmon, 15
6, Bread, Croissant, 23

Current Values in View
3, Bread, Muffin, 23
6, Bread, Croissant, 23
4, Fish, Salmon, 12
5, Fish, Salmon, 15
1, Fruit, Apple, 14
2, Fruit, Orange, 27

Table created from sorted DataView
Bread, 23
Fish, 12
Fish, 15
Fruit, 14
Fruit, 27

New table name: UniqueData