Compare datatable and SQL table

Question

So I googled and searched... is there a simple way to compare the table structure of a SQL table to a CLR datatable?

Here is the issue: we have an API that returns JSON which is constantly evolving. When the return array contains new objects we would like to notify the caller that more data is available.

We can get the SQL schema pretty easy with a query:

select COLUMN_NAME,ORDINAL_POSITION 
from information_schema.columns 
where table_name = 'ApiWork'

But how can we compare the column/ordinal to the DataTable holding the JSON array? A typical return array would look like this:

 {"Index_0":"930477","Index_1":"test789","ArrayID":"1","Result":"OK","Order_ID":"930477","Model_Number":"FGHB2868TF","Ship_Date":"05/30/2018","Allocated":0,"Backordered":1,"Amount":0}

And we could build a datatable with a json deserializer or with a loop:

DataTable dt = new DataTable();
        SqlPipe pipe = SqlContext.Pipe;
        String d = "";
        String col = "";
        int l = 0;
        int l2 = 0;
        int s = 0;
        int s2 = 0;
        o = "{\"Index_0\":\"930477\",\"Index_1\":\"test789\",\"ArrayID\":\"1\",\"Result\":\"OK\",\"DMI_Order_ID\":\"930477\",\"Model_Number\":\"FGHB2868TF\",\"Ship_Date\":\"05/30/2018\",\"Allocated\":0,\"Backordered\":1,\"Amount\":0}";
        int c = o.Length;
        while (c > 0)
        {
            col = o.Substring(0, o.IndexOf(":")).Replace("\"", "").Replace("{", "").Replace("}", "").Replace("[", "").Replace("]", "");
            dt.Columns.Add(col);
            l = o.IndexOf(":");
            l = l + 1;
            s = o.Length - l;
            o = o.Substring(l,s); // here we have removed the name portion
            l2 = o.IndexOf(",");
            l2 = l2 + 1;
            s2 = o.Length - l2;
            o = o.Substring(l2, s2); // here we have removed the value of the previous name

            c = o.Length;
            if (o.IndexOf(":") == -1 && o.IndexOf(",") == -1)
            {
                c = 0;
            }
        }

I suppose looping like this would allow control of the ordinal as well if that were necessary but as I mentioned in one of my comments this is not completely necessary.

Answer 1

So this appears to do the job simply:

  using (SqlCommand command = new SqlCommand("Select name from tempdb.sys.COLUMNS  Where object_id=OBJECT_ID('tempdb.dbo.#ApiWork')EXCEPT Select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where table_name = 'ApiWork'"))
            {
                command.Connection = connection;
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    if (reader.HasRows)
                    {
                        pipe.Send("Something is different!");
                    }else{
                        pipe.Send("we're all good!");
                    }

                }
            }

These two queries can be checked to see if columns are different. As an aside

COLUMN_ID

and

ORDINAL_ID

can be added to the queries and then can be checked as well.