Options are not reflected in the generated script

dshah

Hello,
I am using the following code to generate a data compare script. Script generation works fine for the most part. My problem is I am unable to have Options reflected in the script.

Couple of examples of what my expectation is and how the generated script is different.
SqlOptions.ForceCheck option is turned ON, and I still see a bunch of constraints with the "WITH NOCHECK" clause. I was expecting NO "WITH NOCHECK" clause in the output, if I had the ForceCheck ON.
SqlOptions.DontUseTransactions option is turned ON, and I was expecting to see NO "BEGIN TRANSACTION" in the script, but it was there. (This option was only turned ON for testing purposes).

Am I missing something here?

Thanks in advance!

            using (Database db1 = new Database(), db2 = new Database())
            {
                var username = "xxx";
                var sourceUserName = username;
                var destinationUserName = username;

                if (!string.IsNullOrWhiteSpace(username) && username.Contains("{0}"))
                {
                    sourceUserName = String.Format(username, SourceDatabase);
                    destinationUserName = String.Format(username, DestinationDatabase);
                }

                var password = "yyy";

                db1.RegisterForDataCompare(new ConnectionProperties(SourceServer, SourceDatabase, sourceUserName, password), Options.Default);
                db2.RegisterForDataCompare(new ConnectionProperties(DestinationServer, DestinationDatabase, destinationUserName, password), Options.Default);
                
                var tableMappings = new TableMappings();
                tableMappings.CreateMappings(db2.Tables, db1.Tables);

                tableMappings.Options = new EngineDataCompareOptions(
                    MappingOptions.IncludeTimestamps | MappingOptions.Default,
                    ComparisonOptions.TrimTrailingSpaces | ComparisonOptions.Default,
                    SqlOptions.DontUseTransactions | SqlOptions.ForceCheck); 

                using (var session = new ComparisonSession())
                {
                    session.Options = tableMappings.Options;
                    session.CompareDatabases(db2, db1, tableMappings);

                    var provider = new SyncProvider(); 
                    provider.Options = session.Options;
                    ExecutionBlock block;

                    try
                    {
                        block = provider.GetMigrationSQL(session);
                        output = block.GetString();
                    }
                    catch (Exception ex)
                    {
                        output = ex.Message;
                        //todo: add code to continue with the loop
                    }
                    finally
                    {
                        block = provider.Block;
                        if (block != null)
                        {
                            block.Dispose(); // dispose of the objects to delete temporary files 
                        }
                    }
                }
            }

Chris Auckland

Thanks for your post.

The options should be behaving as you expect, so I tried to test your code.

I couldn't get it to work so I ended up testing the following:

using (Database db1 = new Database(), db2 = new Database())
            {
                

                db1.RegisterForDataCompare(new ConnectionProperties(Program.DevServerName, Program.DevDatabaseName), Options.Default);
                db2.RegisterForDataCompare(new ConnectionProperties(Program.LiveServerName, Program.LiveDatabaseName), Options.Default);

                var tableMappings = new TableMappings();
                tableMappings.CreateMappings(db2.Tables, db1.Tables);

                tableMappings.Options = new EngineDataCompareOptions(
                    MappingOptions.IncludeTimestamps | MappingOptions.Default,
                    ComparisonOptions.TrimTrailingSpaces | ComparisonOptions.Default,
                    SqlOptions.DontUseTransactions | SqlOptions.ForceCheck);

                using (var session = new ComparisonSession())
                {
                    session.Options = tableMappings.Options;
                    session.CompareDatabases(db2, db1, tableMappings);

                    SqlProvider provider = new SqlProvider();
                    provider.Options = session.Options;
                   

                    try
                    {
                        ExecutionBlock block = provider.GetMigrationSQL(session, true);
                        Console.WriteLine("The SQL to be run is:");
                        Console.WriteLine(block.GetString());
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine(ex.Message);
                        //todo: add code to continue with the loop 
                    }
                    finally
                    {
                        ExecutionBlock block = provider.Block;
                        if (block != null)
                        {
                            block.Dispose(); // dispose of the objects to delete temporary files 
                        }
                    }
                }
            }

This seems to work ok for me, so maybe the difference is you using the SyncProvider() class instead of sqlprovider()