检查 2 个数据表是否具有相同的架构

Question

我想确保 2 个 Ado.net 数据表具有相同的架构：列数 + col 类型等。如何做到这一点？

假设我有变量：数据表 A 和数据表 B。如何比较 A 的架构与 B 的架构是否相同

Answer 1

这是我的项目所采用的方法。我只测试了我关心的属性的相等性。如果您关心的不仅仅是这里的内容，您可以添加其余的属性。请注意，这里的其他答案都没有验证主键是否匹配，而我的却验证了。

public static bool SchemaMatches( this DataTable table, DataTable referenceTable )
{
    if( table.Columns.Count != referenceTable.Columns.Count || table.PrimaryKey.Count() != referenceTable.PrimaryKey.Count() )
        return false;
    foreach( DataColumn referenceColumn in referenceTable.Columns )
    {
        try {
            DataColumn column = table.Columns[referenceColumn.ColumnName];
            if( column == null || !referenceColumn.AllowDBNull.Equals(column.AllowDBNull) || !referenceColumn.ColumnName.Equals(column.ColumnName) 
                || !referenceColumn.DataType.Equals(column.DataType) || !referenceColumn.Expression.Equals(column.Expression) || !referenceColumn.ReadOnly.Equals(column.ReadOnly) )
            {
                return false;
            }
        } catch {
            return false;
        }
    }
    foreach( DataColumn referenceKey in referenceTable.PrimaryKey )
    {
        try {
            DataColumn key = table.PrimaryKey.Single(x=>x.ColumnName == referenceKey.ColumnName);
            if( key == null )
                return false;
        } catch {
            return false;
        }
    }
    return true;
}

Answer 2

我不知道有什么内置方法可以比较数据表，并且没有定义完整的规范（而且您总是应该）我可能会错过您关心的某些案例。

也就是说，以下确实设法比较两个数据表并确定以下内容是否为真

• 两个数据表中的数据列数是否相同
• 对于第一个数据表中的每个数据列，另一个表中是否存在也相同的列type 无论顺序

它使用扩展方法并实现 IEqualityComparer 来进行比较。

测试用例

class Program
    {
        static void Main(string[] args)
        {

            DataTable dt1 = new DataTable();
            dt1.Columns.Add(columnName: "a", type: Type.GetType("System.String"));
            dt1.Columns.Add(columnName: "b", type: Type.GetType("System.Int32"));

            DataTable dt2 = new DataTable();
            dt2.Columns.Add(columnName: "a", type: Type.GetType("System.Int32"));
            dt2.Columns.Add(columnName: "b", type: Type.GetType("System.String"));

            DataTable dt3 = new DataTable();
            dt3.Columns.Add(columnName: "a", type: Type.GetType("System.String"));
            dt3.Columns.Add(columnName: "b", type: Type.GetType("System.Int32"));
            dt3.Columns.Add(columnName: "c", type: Type.GetType("System.Int32"));


            DataTable dt4 = new DataTable();
            dt4.Columns.Add(columnName: "b", type: Type.GetType("System.Int32"));
            dt4.Columns.Add(columnName: "a", type: Type.GetType("System.String"));


            DataTable dt5 = new DataTable();
            dt5.Columns.Add(columnName: "a", type: Type.GetType("System.String"));
            dt5.Columns.Add(columnName: "b", type: Type.GetType("System.Int32"));


        Console.WriteLine("dt1.SchemaEquals(dt1) | {0}", dt1.SchemaEquals(dt1));
        Console.WriteLine("dt1.SchemaEquals(dt2) | {0}", dt1.SchemaEquals(dt2));
        Console.WriteLine("dt1.SchemaEquals(dt3) | {0}", dt1.SchemaEquals(dt3));
        Console.WriteLine("dt1.SchemaEquals(dt4) | {0}", dt1.SchemaEquals(dt4));
        Console.WriteLine("dt1.SchemaEquals(dt5) | {0}", dt1.SchemaEquals(dt5));

            if (System.Diagnostics.Debugger.IsAttached)
            {
                Console.ReadLine();
            }


        }


    }

扩展方法

    public static class DataTableSchemaCompare
    {
        public static bool SchemaEquals(this DataTable dt, DataTable value)
        {
            if (dt.Columns.Count != value.Columns.Count)
                return false;

             var dtColumns = dt.Columns.Cast<DataColumn>();
             var valueColumns = value.Columns.Cast<DataColumn>();


            var exceptCount =  dtColumns.Except(valueColumns, DataColumnEqualityComparer.Instance).Count() ;
            return (exceptCount == 0);


        }
    }

IEqualityComparer的实现

    class DataColumnEqualityComparer : IEqualityComparer<DataColumn>
    {
        #region IEqualityComparer Members

        private DataColumnEqualityComparer() { }
        public static DataColumnEqualityComparer Instance = new DataColumnEqualityComparer();


        public bool Equals(DataColumn x, DataColumn y)
        {
            if (x.ColumnName != y.ColumnName)
                return false;
            if (x.DataType != y.DataType)
                return false;

            return true;
        }

        public int GetHashCode(DataColumn obj)
        {
            int hash = 17;
            hash = 31 * hash + obj.ColumnName.GetHashCode();
            hash = 31 * hash + obj.DataType.GetHashCode();

            return hash;
        }

        #endregion
    }

输出

dt1.SchemaEquals(dt1) | True
dt1.SchemaEquals(dt2) | False
dt1.SchemaEquals(dt3) | False
dt1.SchemaEquals(dt4) | True
dt1.SchemaEquals(dt5) | True
Press any key to continue . . .

Answer 3

康拉德的回答绝对有帮助。但我使用下面的方式来比较数据表结构。

Private Function CompareStruture(ByVal dt1 As DataTable, ByVal dt2 As DataTable) As Boolean
        If (dt1.Columns.Count = dt2.Columns.Count) Then
            Dim c1() = (From c As DataColumn In dt1.Columns Select c.ColumnName).ToArray()
            Dim c2() = (From c As DataColumn In dt2.Columns Select c.ColumnName).ToArray()
            If (c1.Intersect(c2).Count() <> c1.Length) Then                    
                Return False
            End If
            Return True
        Else                
            Return False
        End If
    End Function