在 ADO.NET 1.x & 2.0 中, System.Data.DataTable 类虽然公开 Select 方法,但不支持 DISTINCT 关键字,而我们在实际开发中免不了有这样的需求:对已有 DataTable 进行 SELECT DISTINCT ,返回指定列具有唯一值的行,这些行存在新的 DataTable 中。
对于此问题,很早就有人提出来了,大家也做了很多尝试。
看到 CSDN 中常有人有此疑问,这里再次总结下,便于参阅。
ADO.NET 1.x
对于此版本,没有任何捷径,只有自己手工实现,这里收集两个比较常用的方法:
Select DISTINCT on DataTable
http://weblogs.asp.net/eporter/archive/2005/02/10/370548.aspx
HOWTO: VisualC # .NET 中实现 DataSet SELECTDISTINCT Helper 类
http://support.microsoft.com/kb/326176/zh-cn
推荐方法1,此实现更具性能比。
ADO.NET 2.0
对于此版本, 虽然 DataTable.Select 方法依然不支持 DISTINCT ,但是与 DataTable 密切相关的 DataView 类实现了几个重载版本的 ToDataTable 方法,根据现有 DataView 中的行,创建并返回一个新的 DataTable。
其中
public DataTable ToTable (
bool distinct,
params string[] columnNames
)
public DataTable ToTable (
string tableName,
bool distinct,
params string[] columnNames
)
支持 distinct 指示是否对 columnNames 内所有列进行 DISTINCT 操作。
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();
}
今天的文章如何对 DataTable 执行 SELECT DISTINCT分享到此就结束了,感谢您的阅读,如果确实帮到您,您可以动动手指转发给其他人。
版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 举报,一经查实,本站将立刻删除。
如需转载请保留出处:https://bianchenghao.cn/29428.html