Generating data updates for only one column

ben_leah

I would like to generate update statements for a data comparison only if the data in one column is different, not all of the columns.

Currently i have the following, as you can see I am able to deduce that an Update is required for a particular column (PresentationSQL) but i dont know how to access the Update block of SQL that will perform the update.
Any help would be very much appreciated! Thanks, Ben

 TableDifference difference = session.TableDifferences[0];
                int i = 0;
                foreach (Row row in difference.ResultsStore)
                {
                    //There is a difference
                    if (row.Type != Row.RowType.Same)
                    {
                        if (row.Type == Row.RowType.In2) //Row Only exists in the New Version (INSERT)
                        {
                            Console.WriteLine("INSERT" + i.ToString());
                        }
                        else if (row.Type == Row.RowType.In1)
                        {
                            //Row Only exists in the Old Version (DELETE)
                            Console.WriteLine("DELETE" + i.ToString());
                        }
                        else
                        {
                            foreach (FieldPair field in difference.ResultsStore.Fields)
                            {
                                int field1 = field.OrdinalInResults1;
                                int field2 = field.OrdinalInResults2;
                                if (field.Field1.Name == "PresentationSQL") //(UPDATE)
                                {
                                    Console.WriteLine("UPDATE" + i.ToString());
                                    //ITS HERE I WANT TO OUTPUT THE UPDATE SCRIPT FOR THIS COLUMN DIFFERENCE, 
                                    //HOW DO I ACCESS THE UPDATE BLOCK FOR THIS DIFFERENCE?
                                }

                            }
                        }
                        i++;
                    }
                }

Brian Donahue

Hi Ben,

I would probably take somewhat of a different approach here. The problem is that the result store object contains only the data as the result of a data comparison. This isn't turned into SQL until you use the provider's GetMigrationSql method.

What you will probably want to do is make an array of integers. When you get to the part of your code where you have put the comment, add the Row.Bookmark to the array of integers. Create a method that checks the bookmark against the current synchronization record's bookmark and conditionally include the record in the synchronization SQL by using the SelectionDelegate.

You can see a brief example of this in the FilterSQLExample.(cs|vb) in the Toolkit sample files that are part of the SQL Bundle 5 installation folder.

If you can't figure out how to implement this to your satisfaction, pelase let me know.

ben_leah

Hi, I have had a look at the example you directed me to, but im having difficulty understanding the way it works. Could you give me some further hints on what the example is achieving and how. Many Thanks, Ben

ben_leah

I have it working, for those interested ....

        private List<int> _bookmarks = new List<int>();

        public void WriteMeasureDefinitionUpdates()
        {
            try
            {
                Database oldDatabase = new Database();
                Database newDatabase = new Database(); 
                ComparisonSession session = new ComparisonSession();
                TableMappings mappings = new TableMappings();
                oldDatabase.RegisterForDataCompare((ConnectionProperties)this._oldConnectionProperties);
                newDatabase.RegisterForDataCompare((ConnectionProperties)this._newConnectionProperties);

                TableMapping tableMapping = (TableMapping)mappings.Join(oldDatabase.Tables["[dbo].[W_CO_Measure_Definition]"], newDatabase.Tables["[dbo].[W_CO_Measure_Definition]"]);

                tableMapping.MatchingMappings.Clear();
                tableMapping.MatchingMappings.Add(tableMapping.FieldMappings["MeasureID"]);
                tableMapping.MatchingMappings.Add(tableMapping.FieldMappings["ApplicationCode"]);
                               
                tableMapping.RefreshMappingStatus();
                
                
                mappings.Add(tableMapping);
                
                session.CompareDatabases(oldDatabase, newDatabase, mappings);
                SqlProvider provider = new SqlProvider();
                provider.Options = session.Options;
                
                TableDifference difference = session.TableDifferences[0];
                int i = 0;
                foreach (Row row in difference.ResultsStore)
                {
                    //row.
                    //row.Type = Row.RowType.In2
                    if (row.Type != Row.RowType.Same)
                    {
                        if (row.Type == Row.RowType.In2) //Row Only exists in the New Version (INSERT)
                        {
                            Console.WriteLine("INSERT" + i.ToString());
                            _bookmarks.Add(row.Bookmark);
                        }
                        else if (row.Type == Row.RowType.In1)
                        {
                            //Row Only exists in the Old Version (DELETE)
                            Console.WriteLine("DELETE" + i.ToString());
                            _bookmarks.Add(row.Bookmark);
                        }
                        else
                        {
                            foreach (FieldPair field in difference.ResultsStore.Fields)
                            {
                                int field1 = field.OrdinalInResults1;
                                int field2 = field.OrdinalInResults2;
                                if (field.Field1.Name == "PresentationSQL") //(UPDATE)
                                {
                                    Console.WriteLine("UPDATE" + i.ToString());
                                    _bookmarks.Add(row.Bookmark);
                                }

                            }
                        }
                        i++;
                    }
                }
                ExecutionBlock block = provider.GetMigrationSQL(session, new SelectionDelegate(this.SyncRecord), true);
                Console.WriteLine(block.ToString());
            }
            catch (Exception ex)
            {
                // Log any error before throwing it to the next highest level
                ActivityLog.LogException(ex);
                throw ex;
            }
        }

        protected bool SyncRecord(SynchronizationRecord syncRecordObject)
        {
            if(this._bookmarks.Contains(syncRecordObject.Bookmark))
                return true;
            return false;
        }