Check to see if 2 datatable have same schema

I want to make sure that 2 Ado.net datatables h开发者_StackOverflowave the same schema: number of columns + col types etc. How can this be done?

Lets say I have variables: Datatable A and Datatable B. How can I compare to see if the schema of A is same as Schema of B

I don't know of any built in way to compare DataTables and without having defined a complete specification (and you always should) its likely that I'm going to miss some case you care about.

That said the following does manage to compare two DataTables and determine if the following is true

• Is the number of data columns the same in both DataTables
• For each data column in the first dataTable does a column exist in the other table that also is of the same type regardless of order

It's using an Extension Method and implements IEqualityComparer to make the comparison.

Test Cases

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();
            }


        }


    }

Extension Method

    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);


        }
    }

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

Output

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

The answer from Conrad was definitely helpful. But I used the below way to compare the datatable structure.

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

This is the method I went with for my project. I only tested the equality of the properties I cared about. You could add on the remaining properties if you care about more than what's here. Note that none of the other answers here verify the primary keys match up, whereas mine does.

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