本文主要提供了一个操作DataTable的类，DataTable的常用操作包括Join，Top，Select，Distinct等。
View Code
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
namespace Common.Data
{
/// <summary>
/// This helper class provides some useful function for processing the in-memory DataTable.
/// Reference:http://weblogs.sqlteam.com/davidm/archive/2004/01/20/748.aspx
/// </summary>
public static class DataTableHelper
{
public static DataTable Join(DataTable leftTable, DataTable rightTable, DataColumn[] leftKeyColumnArray, DataColumn[] rightKeyColumnArray, DataColumn[] leftResultColumnArray, DataColumn[] rightResultColumnArray, JoinType joinType, bool fillKeyColumn, string prefixForDuplicatedColumn)
{
DataTable result = new DataTable();
// Use a DataSet to leverage DataRelation
using (DataSet ds = new DataSet())
{
{
DataTable left = leftTable.Copy();
left.TableName = "leftTable";
DataTable right = rightTable.Copy();
right.TableName = "rightTable";
ds.Tables.AddRange(new DataTable[] { left, right });
}
//Identify joining columns from the left table.
DataColumn[] parentcolumns = new DataColumn[leftKeyColumnArray.Length];
for (int i = 0; i < parentcolumns.Length; i++)
{
parentcolumns[i] = ds.Tables[0].Columns[leftKeyColumnArray[i].ColumnName];
}
//Identify joining columns from the right table.
DataColumn[] childcolumns = new DataColumn[rightKeyColumnArray.Length];
for (int i = 0; i < childcolumns.Length; i++)
{
childcolumns[i] = ds.Tables[1].Columns[rightKeyColumnArray[i].ColumnName];
}
DataRelation r = new DataRelation(string.Empty, parentcolumns, childcolumns, false);
ds.Relations.Add(r);
//Create columns for result table
foreach (DataColumn dc in leftResultColumnArray)
{
result.Columns.Add(dc.ColumnName, dc.DataType);
}
foreach (DataColumn dc in rightResultColumnArray)
{
if (!result.Columns.Contains(dc.ColumnName))
{
result.Columns.Add(dc.ColumnName, dc.DataType);
}
else
{
//The caller should make sure the prefix can make a unique column name.
result.Columns.Add(prefixForDuplicatedColumn + dc.ColumnName, dc.DataType);
}
}
result.BeginLoadData();
if (joinType == JoinType.InnerJoin)
{
foreach (DataRow leftRow in ds.Tables[0].Rows)
{
DataRow[] rightRows = leftRow.GetChildRows(r);
if (rightRows.Length > 0)
{
foreach (DataRow rightRow in rightRows)
{
DataRow dr = result.NewRow();
for (int i = 0; i < leftResultColumnArray.Length; i++)
{
dr[i] = leftRow[leftResultColumnArray[i].ColumnName];
}
for (int i = 0; i < rightResultColumnArray.Length; i++)
{
dr[leftResultColumnArray.Length + i] = rightRow[rightResultColumnArray[i].ColumnName];
}
result.Rows.Add(dr);
}
}
}
}
else if (joinType == JoinType.LeftJoin)
{
foreach (DataRow leftRow in ds.Tables[0].Rows)
{
DataRow[] rightRows = leftRow.GetChildRows(r);
if (rightRows.Length > 0)
{
foreach (DataRow rightRow in rightRows)
{
DataRow dr = result.NewRow();
for (int i = 0; i < leftResultColumnArray.Length; i++)
{
dr[i] = leftRow[leftResultColumnArray[i].ColumnName];
}
for (int i = 0; i < rightResultColumnArray.Length; i++)
{
dr[leftResultColumnArray.Length + i] = rightRow[rightResultColumnArray[i].ColumnName];
}
result.Rows.Add(dr);
}
}
else
{
DataRow dr = result.NewRow();
if (fillKeyColumn)
{
for (int i = 0; i < rightKeyColumnArray.Length; ++i)
{
if (result.Columns.Contains(rightKeyColumnArray[i].ColumnName))
{
dr[rightKeyColumnArray[i].ColumnName] = leftRow[leftKeyColumnArray[i].ColumnName];
}
}
}
for (int i = 0; i < leftResultColumnArray.Length; i++)
{
dr[i] = leftRow[leftResultColumnArray[i].ColumnName];
}
result.Rows.Add(dr);
}
}
}
else if (joinType == JoinType.RightJoin)
{
foreach (DataRow rightRow in ds.Tables[1].Rows)
{
DataRow[] leftRows = rightRow.GetParentRows(r);
if (leftRows.Length > 0)
{
foreach (DataRow leftRow in leftRows)
{
DataRow dr = result.NewRow();
for (int i = 0; i < leftResultColumnArray.Length; i++)
{
dr[i] = leftRow[leftResultColumnArray[i].ColumnName];
}
for (int i = 0; i < rightResultColumnArray.Length; i++)
{
dr[leftResultColumnArray.Length + i] = rightRow[rightResultColumnArray[i].ColumnName];
}
result.Rows.Add(dr);
}
}
else
{
DataRow dr = result.NewRow();
if (fillKeyColumn)
{
for (int i = 0; i < leftKeyColumnArray.Length; ++i)
{
if (result.Columns.Contains(leftKeyColumnArray[i].ColumnName))
{
dr[leftKeyColumnArray[i].ColumnName] = rightRow[rightKeyColumnArray[i].ColumnName];
}
}
}
for (int i = 0; i < rightResultColumnArray.Length; i++)
{
dr[leftResultColumnArray.Length + i] = rightRow[rightResultColumnArray[i].ColumnName];
}
result.Rows.Add(dr);
}
}
}
else if (joinType == JoinType.OutJoin)
{
foreach (DataRow leftRow in ds.Tables[0].Rows)
{
DataRow[] rightRows = leftRow.GetChildRows(r);
if (rightRows.Length > 0)
{
foreach (DataRow rightRow in rightRows)
{
DataRow dr = result.NewRow();
for (int i = 0; i < leftResultColumnArray.Length; i++)
{
dr[i] = leftRow[leftResultColumnArray[i].ColumnName];
}
for (int i = 0; i < rightResultColumnArray.Length; i++)
{
dr[leftResultColumnArray.Length + i] = rightRow[rightResultColumnArray[i].ColumnName];
}
result.Rows.Add(dr);
}
}
else
{
DataRow dr = result.NewRow();
if (fillKeyColumn)
{
for (int i = 0; i < rightKeyColumnArray.Length; ++i)
{
if (result.Columns.Contains(rightKeyColumnArray[i].ColumnName))
{
dr[rightKeyColumnArray[i].ColumnName] = leftRow[leftKeyColumnArray[i].ColumnName];
}
}
}
for (int i = 0; i < leftResultColumnArray.Length; i++)
{
dr[i] = leftRow[leftResultColumnArray[i].ColumnName];
}
result.Rows.Add(dr);
}
}
foreach (DataRow rightRow in ds.Tables[1].Rows)
{
DataRow[] leftRows = rightRow.GetParentRows(r);
if (leftRows.Length <= 0)
{
DataRow dr = result.NewRow();
if (fillKeyColumn)
{
for (int i = 0; i < leftKeyColumnArray.Length; ++i)
{
if (result.Columns.Contains(leftKeyColumnArray[i].ColumnName))
{
dr[leftKeyColumnArray[i].ColumnName] = rightRow[rightKeyColumnArray[i].ColumnName];
}
}
}
for (int i = 0; i < rightResultColumnArray.Length; i++)
{
dr[leftResultColumnArray.Length + i] = rightRow[rightResultColumnArray[i].ColumnName];
}
result.Rows.Add(dr);
}
}
}
result.EndLoadData();
}
return result;
}
public static DataTable Join(DataTable leftTable, DataTable rightTable, DataColumn[] leftKeyColumnArray, DataColumn[] rightKeyColumnArray, DataColumn[] leftResultColumnArray, DataColumn[] rightResultColumnArray, JoinType joinType, string prefixForDuplicatedColumn)
{
return Join(leftTable, rightTable, leftKeyColumnArray, rightKeyColumnArray, leftResultColumnArray, rightResultColumnArray, joinType, false, prefixForDuplicatedColumn);
}
public static DataTable Join(DataTable leftTable, DataTable rightTable, DataColumn leftKeyColumn, DataColumn rightKeyColumn, DataColumn[] leftResultColumnArray, DataColumn[] rightResultColumnArray, JoinType joinType, string prefixForDuplicatedColumn)
{
return Join(leftTable, rightTable, new DataColumn[] { leftKeyColumn }, new DataColumn[] { rightKeyColumn }, leftResultColumnArray, rightResultColumnArray, joinType, prefixForDuplicatedColumn);
}
public static DataTable Join(DataTable leftTable, DataTable rightTable, DataColumn leftKeyColumn, DataColumn rightKeyColumn, DataColumn[] leftResultColumnArray, DataColumn[] rightResultColumnArray, JoinType joinType, bool fillKeyColumn, string prefixForDuplicatedColumn)
{
return Join(leftTable, rightTable, new DataColumn[] { leftKeyColumn }, new DataColumn[] { rightKeyColumn }, leftResultColumnArray, rightResultColumnArray, joinType, fillKeyColumn, prefixForDuplicatedColumn);
}
public static DataTable Join(DataTable leftTable, DataTable rightTable, string leftKeyColumns, string rightKeyColumns, string leftResultColumns, string rightResultColumns, JoinType joinType, string prefixForDuplicatedColumn)
{
return Join(leftTable, rightTable, Parse(leftKeyColumns, leftTable), Parse(rightKeyColumns, rightTable), Parse(leftResultColumns, leftTable), Parse(rightResultColumns, rightTable), joinType, prefixForDuplicatedColumn);
}
public static DataTable Join(DataTable leftTable, DataTable rightTable, string leftKeyColumns, string rightKeyColumns, string leftResultColumns, string rightResultColumns, JoinType joinType, bool fillKeyColumn, string prefixForDuplicatedColumn)
{
return Join(leftTable, rightTable, Parse(leftKeyColumns, leftTable), Parse(rightKeyColumns, rightTable), Parse(leftResultColumns, leftTable), Parse(rightResultColumns, rightTable), joinType, fillKeyColumn, prefixForDuplicatedColumn);
}
private static DataColumn[] Parse(string columnNames, DataTable table)
{
DataColumn[] result;
if (string.IsNullOrEmpty(columnNames) || columnNames == "*")
{
result = new DataColumn[table.Columns.Count];
table.Columns.CopyTo(result, 0);
}
else
{
string[] names = columnNames.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
result = new DataColumn[names.Length];
for (int i = 0; i < result.Length; i++)
{
result[i] = table.Columns[names[i]];
}
}
return result;
}
public static DataTable Top(DataTable dt, int top, string filterExpression, string sort)
{
DataRow[] drArray = dt.Select(filterExpression, sort);
top = Math.Min(top, drArray.Length);
DataTable result = dt.Clone();
result.BeginLoadData();
for (int i = 0; i < top; i++)
{
result.ImportRow(drArray[i]);
}
result.EndLoadData();
return result;
}
public static DataTable Top(DataTable dt, int top, string filterExpression, string sort, params string[] columnNames)
{
DataTable newTable = Select(dt, filterExpression, sort, columnNames);
if (newTable.Rows.Count > top)
{
DataTable result = newTable.Clone();
result.BeginLoadData();
for (int i = 0; i < top; i++)
{
result.ImportRow(newTable.Rows[i]);
}
result.EndLoadData();
return result;
}
else
{
return newTable;
}
}
public static DataTable Select(DataTable dt, string filterExpression, string sort)
{
DataView dv = new DataView(dt, filterExpression, sort, DataViewRowState.CurrentRows);
return dv.ToTable();
}
public static DataTable Select(DataTable dt, string filterExpression, string sort, params string[] columnNames)
{
DataView dv = new DataView(dt, filterExpression, sort, DataViewRowState.CurrentRows);
return dv.ToTable(false, columnNames);
}
public static DataTable Distinct(DataTable dt, params string[] columnNames)
{
return dt.DefaultView.ToTable(true, columnNames);
}
public static DataTable Trim(DataTable sourceTable, string sortColumn, params string[] checkColumns)
{
if (checkColumns == null || checkColumns.Length == 0)
{
throw new ArgumentException("checkColumns can not be omitted.", "checkColumns");
}
string condition = string.Join(" is not null or ", checkColumns);
condition = condition + " is not null";
DataView dv = new DataView(sourceTable);
dv.Sort = sortColumn;
dv.RowFilter = condition;
if (dv.Count == 0)
{
return sourceTable.Clone();
}
object startValue = dv[0].Row[sortColumn];
object endValue = dv[dv.Count - 1].Row[sortColumn];
DataView resultDv = new DataView(sourceTable);
resultDv.RowFilter = string.Format("{0} >= '{1}' and {0} <= '{2}'", sortColumn, startValue, endValue);
return resultDv.ToTable();
}
}
public enum JoinType
{
InnerJoin,
LeftJoin,
RightJoin,
OutJoin
}
}
using System.Collections.Generic;
using System.Text;
using System.Data;
namespace Common.Data
{
/// <summary>
/// This helper class provides some useful function for processing the in-memory DataTable.
/// Reference:http://weblogs.sqlteam.com/davidm/archive/2004/01/20/748.aspx
/// </summary>
public static class DataTableHelper
{
public static DataTable Join(DataTable leftTable, DataTable rightTable, DataColumn[] leftKeyColumnArray, DataColumn[] rightKeyColumnArray, DataColumn[] leftResultColumnArray, DataColumn[] rightResultColumnArray, JoinType joinType, bool fillKeyColumn, string prefixForDuplicatedColumn)
{
DataTable result = new DataTable();
// Use a DataSet to leverage DataRelation
using (DataSet ds = new DataSet())
{
{
DataTable left = leftTable.Copy();
left.TableName = "leftTable";
DataTable right = rightTable.Copy();
right.TableName = "rightTable";
ds.Tables.AddRange(new DataTable[] { left, right });
}
//Identify joining columns from the left table.
DataColumn[] parentcolumns = new DataColumn[leftKeyColumnArray.Length];
for (int i = 0; i < parentcolumns.Length; i++)
{
parentcolumns[i] = ds.Tables[0].Columns[leftKeyColumnArray[i].ColumnName];
}
//Identify joining columns from the right table.
DataColumn[] childcolumns = new DataColumn[rightKeyColumnArray.Length];
for (int i = 0; i < childcolumns.Length; i++)
{
childcolumns[i] = ds.Tables[1].Columns[rightKeyColumnArray[i].ColumnName];
}
DataRelation r = new DataRelation(string.Empty, parentcolumns, childcolumns, false);
ds.Relations.Add(r);
//Create columns for result table
foreach (DataColumn dc in leftResultColumnArray)
{
result.Columns.Add(dc.ColumnName, dc.DataType);
}
foreach (DataColumn dc in rightResultColumnArray)
{
if (!result.Columns.Contains(dc.ColumnName))
{
result.Columns.Add(dc.ColumnName, dc.DataType);
}
else
{
//The caller should make sure the prefix can make a unique column name.
result.Columns.Add(prefixForDuplicatedColumn + dc.ColumnName, dc.DataType);
}
}
result.BeginLoadData();
if (joinType == JoinType.InnerJoin)
{
foreach (DataRow leftRow in ds.Tables[0].Rows)
{
DataRow[] rightRows = leftRow.GetChildRows(r);
if (rightRows.Length > 0)
{
foreach (DataRow rightRow in rightRows)
{
DataRow dr = result.NewRow();
for (int i = 0; i < leftResultColumnArray.Length; i++)
{
dr[i] = leftRow[leftResultColumnArray[i].ColumnName];
}
for (int i = 0; i < rightResultColumnArray.Length; i++)
{
dr[leftResultColumnArray.Length + i] = rightRow[rightResultColumnArray[i].ColumnName];
}
result.Rows.Add(dr);
}
}
}
}
else if (joinType == JoinType.LeftJoin)
{
foreach (DataRow leftRow in ds.Tables[0].Rows)
{
DataRow[] rightRows = leftRow.GetChildRows(r);
if (rightRows.Length > 0)
{
foreach (DataRow rightRow in rightRows)
{
DataRow dr = result.NewRow();
for (int i = 0; i < leftResultColumnArray.Length; i++)
{
dr[i] = leftRow[leftResultColumnArray[i].ColumnName];
}
for (int i = 0; i < rightResultColumnArray.Length; i++)
{
dr[leftResultColumnArray.Length + i] = rightRow[rightResultColumnArray[i].ColumnName];
}
result.Rows.Add(dr);
}
}
else
{
DataRow dr = result.NewRow();
if (fillKeyColumn)
{
for (int i = 0; i < rightKeyColumnArray.Length; ++i)
{
if (result.Columns.Contains(rightKeyColumnArray[i].ColumnName))
{
dr[rightKeyColumnArray[i].ColumnName] = leftRow[leftKeyColumnArray[i].ColumnName];
}
}
}
for (int i = 0; i < leftResultColumnArray.Length; i++)
{
dr[i] = leftRow[leftResultColumnArray[i].ColumnName];
}
result.Rows.Add(dr);
}
}
}
else if (joinType == JoinType.RightJoin)
{
foreach (DataRow rightRow in ds.Tables[1].Rows)
{
DataRow[] leftRows = rightRow.GetParentRows(r);
if (leftRows.Length > 0)
{
foreach (DataRow leftRow in leftRows)
{
DataRow dr = result.NewRow();
for (int i = 0; i < leftResultColumnArray.Length; i++)
{
dr[i] = leftRow[leftResultColumnArray[i].ColumnName];
}
for (int i = 0; i < rightResultColumnArray.Length; i++)
{
dr[leftResultColumnArray.Length + i] = rightRow[rightResultColumnArray[i].ColumnName];
}
result.Rows.Add(dr);
}
}
else
{
DataRow dr = result.NewRow();
if (fillKeyColumn)
{
for (int i = 0; i < leftKeyColumnArray.Length; ++i)
{
if (result.Columns.Contains(leftKeyColumnArray[i].ColumnName))
{
dr[leftKeyColumnArray[i].ColumnName] = rightRow[rightKeyColumnArray[i].ColumnName];
}
}
}
for (int i = 0; i < rightResultColumnArray.Length; i++)
{
dr[leftResultColumnArray.Length + i] = rightRow[rightResultColumnArray[i].ColumnName];
}
result.Rows.Add(dr);
}
}
}
else if (joinType == JoinType.OutJoin)
{
foreach (DataRow leftRow in ds.Tables[0].Rows)
{
DataRow[] rightRows = leftRow.GetChildRows(r);
if (rightRows.Length > 0)
{
foreach (DataRow rightRow in rightRows)
{
DataRow dr = result.NewRow();
for (int i = 0; i < leftResultColumnArray.Length; i++)
{
dr[i] = leftRow[leftResultColumnArray[i].ColumnName];
}
for (int i = 0; i < rightResultColumnArray.Length; i++)
{
dr[leftResultColumnArray.Length + i] = rightRow[rightResultColumnArray[i].ColumnName];
}
result.Rows.Add(dr);
}
}
else
{
DataRow dr = result.NewRow();
if (fillKeyColumn)
{
for (int i = 0; i < rightKeyColumnArray.Length; ++i)
{
if (result.Columns.Contains(rightKeyColumnArray[i].ColumnName))
{
dr[rightKeyColumnArray[i].ColumnName] = leftRow[leftKeyColumnArray[i].ColumnName];
}
}
}
for (int i = 0; i < leftResultColumnArray.Length; i++)
{
dr[i] = leftRow[leftResultColumnArray[i].ColumnName];
}
result.Rows.Add(dr);
}
}
foreach (DataRow rightRow in ds.Tables[1].Rows)
{
DataRow[] leftRows = rightRow.GetParentRows(r);
if (leftRows.Length <= 0)
{
DataRow dr = result.NewRow();
if (fillKeyColumn)
{
for (int i = 0; i < leftKeyColumnArray.Length; ++i)
{
if (result.Columns.Contains(leftKeyColumnArray[i].ColumnName))
{
dr[leftKeyColumnArray[i].ColumnName] = rightRow[rightKeyColumnArray[i].ColumnName];
}
}
}
for (int i = 0; i < rightResultColumnArray.Length; i++)
{
dr[leftResultColumnArray.Length + i] = rightRow[rightResultColumnArray[i].ColumnName];
}
result.Rows.Add(dr);
}
}
}
result.EndLoadData();
}
return result;
}
public static DataTable Join(DataTable leftTable, DataTable rightTable, DataColumn[] leftKeyColumnArray, DataColumn[] rightKeyColumnArray, DataColumn[] leftResultColumnArray, DataColumn[] rightResultColumnArray, JoinType joinType, string prefixForDuplicatedColumn)
{
return Join(leftTable, rightTable, leftKeyColumnArray, rightKeyColumnArray, leftResultColumnArray, rightResultColumnArray, joinType, false, prefixForDuplicatedColumn);
}
public static DataTable Join(DataTable leftTable, DataTable rightTable, DataColumn leftKeyColumn, DataColumn rightKeyColumn, DataColumn[] leftResultColumnArray, DataColumn[] rightResultColumnArray, JoinType joinType, string prefixForDuplicatedColumn)
{
return Join(leftTable, rightTable, new DataColumn[] { leftKeyColumn }, new DataColumn[] { rightKeyColumn }, leftResultColumnArray, rightResultColumnArray, joinType, prefixForDuplicatedColumn);
}
public static DataTable Join(DataTable leftTable, DataTable rightTable, DataColumn leftKeyColumn, DataColumn rightKeyColumn, DataColumn[] leftResultColumnArray, DataColumn[] rightResultColumnArray, JoinType joinType, bool fillKeyColumn, string prefixForDuplicatedColumn)
{
return Join(leftTable, rightTable, new DataColumn[] { leftKeyColumn }, new DataColumn[] { rightKeyColumn }, leftResultColumnArray, rightResultColumnArray, joinType, fillKeyColumn, prefixForDuplicatedColumn);
}
public static DataTable Join(DataTable leftTable, DataTable rightTable, string leftKeyColumns, string rightKeyColumns, string leftResultColumns, string rightResultColumns, JoinType joinType, string prefixForDuplicatedColumn)
{
return Join(leftTable, rightTable, Parse(leftKeyColumns, leftTable), Parse(rightKeyColumns, rightTable), Parse(leftResultColumns, leftTable), Parse(rightResultColumns, rightTable), joinType, prefixForDuplicatedColumn);
}
public static DataTable Join(DataTable leftTable, DataTable rightTable, string leftKeyColumns, string rightKeyColumns, string leftResultColumns, string rightResultColumns, JoinType joinType, bool fillKeyColumn, string prefixForDuplicatedColumn)
{
return Join(leftTable, rightTable, Parse(leftKeyColumns, leftTable), Parse(rightKeyColumns, rightTable), Parse(leftResultColumns, leftTable), Parse(rightResultColumns, rightTable), joinType, fillKeyColumn, prefixForDuplicatedColumn);
}
private static DataColumn[] Parse(string columnNames, DataTable table)
{
DataColumn[] result;
if (string.IsNullOrEmpty(columnNames) || columnNames == "*")
{
result = new DataColumn[table.Columns.Count];
table.Columns.CopyTo(result, 0);
}
else
{
string[] names = columnNames.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
result = new DataColumn[names.Length];
for (int i = 0; i < result.Length; i++)
{
result[i] = table.Columns[names[i]];
}
}
return result;
}
public static DataTable Top(DataTable dt, int top, string filterExpression, string sort)
{
DataRow[] drArray = dt.Select(filterExpression, sort);
top = Math.Min(top, drArray.Length);
DataTable result = dt.Clone();
result.BeginLoadData();
for (int i = 0; i < top; i++)
{
result.ImportRow(drArray[i]);
}
result.EndLoadData();
return result;
}
public static DataTable Top(DataTable dt, int top, string filterExpression, string sort, params string[] columnNames)
{
DataTable newTable = Select(dt, filterExpression, sort, columnNames);
if (newTable.Rows.Count > top)
{
DataTable result = newTable.Clone();
result.BeginLoadData();
for (int i = 0; i < top; i++)
{
result.ImportRow(newTable.Rows[i]);
}
result.EndLoadData();
return result;
}
else
{
return newTable;
}
}
public static DataTable Select(DataTable dt, string filterExpression, string sort)
{
DataView dv = new DataView(dt, filterExpression, sort, DataViewRowState.CurrentRows);
return dv.ToTable();
}
public static DataTable Select(DataTable dt, string filterExpression, string sort, params string[] columnNames)
{
DataView dv = new DataView(dt, filterExpression, sort, DataViewRowState.CurrentRows);
return dv.ToTable(false, columnNames);
}
public static DataTable Distinct(DataTable dt, params string[] columnNames)
{
return dt.DefaultView.ToTable(true, columnNames);
}
public static DataTable Trim(DataTable sourceTable, string sortColumn, params string[] checkColumns)
{
if (checkColumns == null || checkColumns.Length == 0)
{
throw new ArgumentException("checkColumns can not be omitted.", "checkColumns");
}
string condition = string.Join(" is not null or ", checkColumns);
condition = condition + " is not null";
DataView dv = new DataView(sourceTable);
dv.Sort = sortColumn;
dv.RowFilter = condition;
if (dv.Count == 0)
{
return sourceTable.Clone();
}
object startValue = dv[0].Row[sortColumn];
object endValue = dv[dv.Count - 1].Row[sortColumn];
DataView resultDv = new DataView(sourceTable);
resultDv.RowFilter = string.Format("{0} >= '{1}' and {0} <= '{2}'", sortColumn, startValue, endValue);
return resultDv.ToTable();
}
}
public enum JoinType
{
InnerJoin,
LeftJoin,
RightJoin,
OutJoin
}
}
修改记录：2012-06-11 修改了由于重复列名导致的Right Join和Outer Join的bug。
2012-06-12 添加了一个重载，为Left Join、Right Join和Outer Join的空行绑定主键的值，相当于ISNULL(l.Key, R.Key)的效果。