DataTable的操作类

       本文主要提供了一个操作DataTable的类，DataTable的常用操作包括Join，Top，Select，Distinct等。

 

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

 

       修改记录：2012-06-11 修改了由于重复列名导致的Right Join和Outer Join的bug。

                     2012-06-12 添加了一个重载，为Left Join、Right Join和Outer Join的空行绑定主键的值，相当于ISNULL(l.Key, R.Key)的效果。