How to Sync Two SQL Server Databases – Part 3

private void CompareColumnsFromTable(string tableName, JArray tableDb, JArray tableJson,
            bool remove)
{
    JObject missingColumnsFromDb = new JObject();
    JObject missingColumnsFromJson = new JObject();
    JObject differentColumns = new JObject();

    // Compare each column of database with each column of schema
    foreach(var (coldb, colnamedb) in from JToken coldb in tableDb
                                      let colnamedb = (string)coldb["Column Name"]
                                      select (coldb, colnamedb))
    {
        bool found = false;
        foreach(var coljs in from JToken coljs in tableJson
                             let colnamejs = (string)coljs["Column Name"]
                             where colnamejs != null && colnamedb == colnamejs
                             select coljs)
        {
            found = true;
            if(!JToken.DeepEquals(coldb, coljs)) // we have differences
            {
                _diffmsg += "\r\nIn table " + tableName + " column [" +
                colnamedb + "] is different in database";

                // Nqw we store the full columns definition
                // We need this when deciding if column change is possible
                JObject differences = new JObject
                {
                    ["database"] = coldb,
                    ["json"] = coljs
                };
                                _haveDiff = true;
			if(remove)
                differentColumns[colnamedb] = differences.DeepClone();
				// See if the column (which will be changed) is part of a constraint or index constraint
                                RemoveConstraintIfContainsColumn(tableName, colnamedb);
                                RemoveIndexIfContainsColumn(tableName, colnamedb);
					}
                break;
            }
        }
        if(!found)
        {
            _diffmsg += "\r\nIn table " + tableName + " column [" +
                colnamedb + "] is missing from schema file";
            _haveDiff = true;
                                if(remove)
                missingColumnsFromJson[colnamedb] = coldb;
			// See if the column (which will be changed) is part of a constraint or index contraint
				RemoveConstraintIfContainsColumn(tableName, colnamedb););););););););););););););
                                RemoveIndexIfContainsColumn(tableName, colnamedb);
                }
        }
    }

    // Compare each column of schema with each column of database
    foreach(var (coljs, colnamejs) in from JToken coljs in tableJson
                                      let colnamejs = (string)coljs["Column Name"]
                                      select (coljs, colnamejs))
    {
        bool found = false;

        // Here we look ONLY for columns in json file which are NOT in the database
        // These columns need to be added in the database
        foreach(var _ in from JToken coldb in tableDb
                         let colnamedb = (string)coldb["Column Name"]
                         where colnamejs == colnamedb
                         select new
                         {
                         })
        {
            found = true;
            break;
        }

        if(!found)
        {
            _diffmsg += "\r\nIn table " + tableName + " column [" +
                colnamejs + "] is missing from database";
                        _haveDiff = true;
                                                if(remove)
                                                missingColumnsFromDb[colnamejs] = coljs;
        }
        }
                                                if(remove)
                                                {
                                                // Add elements collected
                                                if(missingColumnsFromDb.Count > 0)
    	        	        	        	    _extraColumnsInJson[tableName] = missingColumnsFromDb.DeepClone();
          	    	if(missingColumnsFromJson.Count > 0)
    	        	        	        	    _extraColumnsInDb[tableName] = missingColumnsFromJson.DeepClone();
          	    	if(differentColumns.Count > 0)
    	        	        	        	    _differentColumns[tableName] = differentColumns.DeepClone();
                                                }
}

/*
 * We try to encrypt or decrypt a column. This is done as follow:
 * 
 *  - create a temporary table with that column
 *  - copy the column into the new table
 *  - delete the existing column from the table
 *  - create the column according to the new rules
 *  - copy the column from temp table back
 *  - delete temp table
 *
 * We need to build the correspoding strings for both columns:
 *  - for the column in the table ADD COLUMN according to the definition in the json
 *  - for the column in the temp table with no encryption, regardless the status of the
 *    column in the table
*/
if(!string.IsNullOrWhiteSpace(encryptionTypeDb) || !string.IsNullOrWhiteSpace(encryptionTypeJson))
{
    SqlTransaction transaction = _conn.BeginTransaction();
    cmd.Transaction = transaction;
    try
    {
        // Create a temp table and copy table into it
        string tmptablename = " [$__" + tableName.Replace("[", "").Replace("]", "") + "__$]";
        query = "Drop Table IF EXISTS " + tmptablename + ";\n";
        query += "select * into " + tmptablename + " From " + tableName;
        cmd.CommandText = query;
        cmd.ExecuteNonQuery();

        // Remove column from table
        query = "Alter table " + tableName + " drop column IF EXISTS [" + columnName + "];";
        cmd.CommandText = query;
        cmd.ExecuteNonQuery();

        // Empty original table
        query = "Truncate table " + tableName;
        cmd.CommandText = query;
        cmd.ExecuteNonQuery();

        // Add the column according to the definition in json file
        if(!string.IsNullOrWhiteSpace(encryptionTypeJson))
            encrypttext=" COLLATE Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY=["+
                _cek + "], ENCRYPTION_TYPE = " + encryptionTypeJson +
                ", ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256')";
        else
            encrypttext = string.Empty;

        query = "ALTER TABLE " + tableName + " ADD [";
        query += columnName + "] " + datatypejson;

        if(precisionJson.HasValue)
        {
            query += "(" + precisionJson.Value;
            if(scaleJson.HasValue)
                query += ", " + scaleJson.Value;
            query += ") ";
        }
        else if(maxLengthJson.HasValue)
            query += "(" + maxLengthJson.Value + ") ";
        if(!isNullableJson)
            query += " NOT NULL";
        if(!string.IsNullOrWhiteSpace(encrypttext))
            query += encrypttext;
        if(!isNullableJson)
            query += " NOT NULL";

        cmd.CommandText = query;
        cmd.ExecuteNonQuery();

        // Copy back the column from temp table using bulk copy
        query = "select * from " + tmptablename + ";";
        cmd.CommandText = query;
        using DataTable dt = new DataTable();
        using(SqlDataAdapter adapter = new SqlDataAdapter(cmd))
        {
            cmd.CommandType = CommandType.Text;
            adapter.SelectCommand.CommandTimeout = 240;
            adapter.Fill(dt);
        }
        using SqlBulkCopy bulkCopy = new SqlBulkCopy(_conn,
            SqlBulkCopyOptions.AllowEncryptedValueModifications | SqlBulkCopyOptions.KeepIdentity,
            transaction);
        bulkCopy.DestinationTableName = tableName;
        bulkCopy.BatchSize = 1000;
        bulkCopy.BulkCopyTimeout = 240;
        bulkCopy.NotifyAfter = 1000;
        bulkCopy.WriteToServer(dt);

        // Remove the temporary table
        query = "Drop Table IF EXISTS " + tmptablename + ";";
        cmd.CommandText = query;
        cmd.ExecuteNonQuery();

        transaction.Commit();
    }
    catch(Exception e)
    {
        _error += "\r\nError changing column [" + columnName + "] in table " + tableName + ": " +
            e.Message;
        _haveErrors = true;
        transaction.Rollback();
    }
    cmd.Transaction = null;
    continue;
}