//******************************************************************************************************//  DataUpdater.cs - Gbtc////  Copyright © 2018, Grid Protection Alliance.  All Rights Reserved.////  Licensed to the Grid Protection Alliance (GPA) under one or more contributor license agreements. See//  the NOTICE file distributed with this work for additional information regarding copyright ownership.//  The GPA licenses this file to you under the MIT License (MIT), the "License"; you may not use this//  file except in compliance with the License. You may obtain a copy of the License at:////      http://opensource.org/licenses/MIT////  Unless agreed to in writing, the subject software distributed under the License is distributed on an//  "AS-IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. Refer to the//  License for the specific language governing permissions and limitations.////  Code Modification History://  ----------------------------------------------------------------------------------------------------//  11/12/2018 - Stephen Wills//       Generated original version of source code from code written in 2003.////****************************************************************************************************** using System;using System.Collections.Generic;using System.Data;using System.Data.OleDb;using System.Diagnostics.CodeAnalysis;using System.Text; namespace GSF.Data{    // Note: user must define primary key fields in the database or through code to be used for updates for each table    // in the table collection in order for the updates to occur, hence any tables which have no key fields defined    // yet appear in the table collection will not be updated...     /// <summary>    /// This class defines a common set of functionality that any data operation implementation can use     /// </summary>    public class DataUpdater : BulkDataOperationBase    {        #region [ Members ]         // Events         /// <summary>        /// Disposed event.        /// </summary>        public event EventHandler Disposed;         #endregion         #region [ Constructors ]         /// <summary>        /// Creates a new <see cref="DataUpdater"/>.        /// </summary>        public DataUpdater()        {        }         /// <summary>        /// Creates a new <see cref="DataUpdater"/>.        /// </summary>        public DataUpdater(string fromConnectString, string toConnectString)            : base(fromConnectString, toConnectString)        {        }         /// <summary>        /// Creates a new <see cref="DataUpdater"/>.        /// </summary>        public DataUpdater(Schema fromSchema, Schema toSchema)            : base(fromSchema, toSchema)        {        }         #endregion         #region [ Methods ]         /// <summary>        /// Close        /// </summary>        public override void Close()        {            base.Close();             if ((object)Disposed != null)                Disposed(this, EventArgs.Empty);        }         /// <summary>        /// Execute this <see cref="DataUpdater"/>        /// </summary>        public override void Execute()        {            List<Table> tablesList = new List<Table>();            Table tableLookup;            int x;             if (m_tableCollection.Count == 0)                Analyze();             if (m_tableCollection.Count == 0)                throw new NullReferenceException("No tables to process even after analyze.");             // We copy the tables into an array list so we can sort and process them in priority order            foreach (Table sourceTable in m_tableCollection)            {                if (sourceTable.Process)                {                    sourceTable.CalculateRowCount();                     if (sourceTable.RowCount > 0)                    {                        tablesList.Add(sourceTable);                        m_overallTotal += sourceTable.RowCount;                    }                }            }             tablesList.Sort((table1, table2) => table1.Priority > table2.Priority ? 1 : (table1.Priority < table2.Priority ? -1 : 0));             // Begin updating data in destination tables            for (x = 0; x <= tablesList.Count - 1; x++)            {                Table table = tablesList[x];                 // Lookup table name in destination datasource                tableLookup = ToSchema.Tables.FindByMapName(table.MapName);                 if ((object)tableLookup != null)                {                    // We can only do Sql updates where key fields are defined...                    if (table.RowCount > 0 && (m_useFromSchemaRI ? table.PrimaryKeyFieldCount : tableLookup.PrimaryKeyFieldCount) > 0)                    {                        // Inform clients of table update                        OnTableProgress(table.Name, true, x + 1, tablesList.Count);                         // Update destination table based on source table records                        ExecuteUpdates(table, tableLookup);                    }                    else                    {                        // Inform clients of table skip                        OnTableProgress(table.Name, false, x + 1, tablesList.Count);                         // If we skipped rows because of lack of key fields, make sure and                        // synchronize overall progress event                        if (table.RowCount > 0)                        {                            m_overallProgress += table.RowCount;                            OnOverallProgress((int)m_overallProgress, (int)m_overallTotal);                        }                    }                }                else                {                    // Inform clients of table skip                    OnTableProgress(table.Name, false, x + 1, tablesList.Count);                }            }             // Perform final update of progress information            OnTableProgress("", false, tablesList.Count, tablesList.Count);        }         /// <summary>        /// Execute a command to update data from source to destination table        /// </summary>        /// <param name="fromTable">Source table</param>        /// <param name="toTable">Destination table</param>        [SuppressMessage("Microsoft.Maintainability", "CA1502:AvoidExcessiveComplexity")]        private void ExecuteUpdates(Table fromTable, Table toTable)        {            Table sourceTable = m_useFromSchemaRI ? fromTable : toTable;            Field lookupField;            Field commonField;             // Progress process variables            int progress = 0;            int totalProgress;             // Bulk update variables            string updateSQLStub;            StringBuilder updateSQL;            StringBuilder whereSQL;            string value;            bool isPrimary;            bool addedFirstUpdate;             // Create a field list of all of the common fields in both tables            // Use toTable as the parent to retrieve the appropriate SQLEncodedValues            Fields fieldsCollection = new Fields(toTable);             foreach (Field fld in fromTable.Fields)            {                // Lookup field name in destination table                lookupField = toTable.Fields[fld.Name];                 if ((object)lookupField != null)                {                    // We currently don't handle binary fields...                    if (!(fld.Type == OleDbType.Binary || fld.Type == OleDbType.LongVarBinary || fld.Type == OleDbType.VarBinary) && !(lookupField.Type == OleDbType.Binary || lookupField.Type == OleDbType.LongVarBinary || lookupField.Type == OleDbType.VarBinary))                    {                        // Copy field information from destination field                        if (m_useFromSchemaRI)                        {                            commonField = new Field(fld.Name, fld.Type);                            commonField.AutoIncrement = fld.AutoIncrement;                        }                        else                        {                            commonField = new Field(lookupField.Name, lookupField.Type);                            commonField.AutoIncrement = lookupField.AutoIncrement;                        }                         fieldsCollection.Add(commonField);                    }                }            }             // Exit if no common field names were found            if (fieldsCollection.Count == 0)            {                m_overallProgress += fromTable.RowCount;                return;            }             totalProgress = fromTable.RowCount;            OnRowProgress(fromTable.Name, 0, totalProgress);            OnOverallProgress((int)m_overallProgress, (int)m_overallTotal);             // Execute source query            using (IDataReader fromReader = fromTable.Connection.ExecuteReader("SELECT " + fieldsCollection.GetList(sqlEscapeFunction: fromTable.Parent.Parent.SQLEscapeName) + " FROM " + fromTable.SQLEscapedName, CommandBehavior.SequentialAccess, Timeout))            {                // Create Sql update stub                updateSQLStub = "UPDATE " + toTable.SQLEscapedName + " SET ";                 // Insert data for each row...                while (fromReader.Read())                {                    updateSQL = new StringBuilder(updateSQLStub);                    whereSQL = new StringBuilder();                    addedFirstUpdate = false;                     // Coerce all field data into proper Sql format                    foreach (Field fld in fieldsCollection)                    {                        try                        {                            fld.Value = fromReader[fld.Name];                        }                        catch (Exception ex)                        {                            fld.Value = "";                            OnSQLFailure("Failed to get field value for [" + sourceTable.Name + "." + fld.Name + "]", ex);                        }                         // Check to see if this is a key field                        lookupField = sourceTable.Fields[fld.Name];                         if ((object)lookupField != null)                            isPrimary = lookupField.IsPrimaryKey;                        else                            isPrimary = false;                         value = fld.SQLEncodedValue;                         if (!value.Equals("NULL", StringComparison.CurrentCultureIgnoreCase))                        {                            if (isPrimary)                            {                                if (whereSQL.Length == 0)                                    whereSQL.Append(" WHERE ");                                else                                    whereSQL.Append(" AND ");                                 whereSQL.Append(fld.SQLEscapedName);                                whereSQL.Append(" = ");                                whereSQL.Append(value);                            }                            else                            {                                if (addedFirstUpdate)                                    updateSQL.Append(", ");                                else                                    addedFirstUpdate = true;                                 updateSQL.Append(fld.SQLEscapedName);                                updateSQL.Append(" = ");                                updateSQL.Append(value);                            }                        }                    }                     if (whereSQL.Length > 0)                    {                        // Add where criteria to Sql update statement                        updateSQL.Append(whereSQL.ToString());                         try                        {                            // Update record in destination table                            if (addedFirstUpdate)                                toTable.Connection.ExecuteNonQuery(updateSQL.ToString(), Timeout);                        }                        catch (Exception ex)                        {                            OnSQLFailure(updateSQL.ToString(), ex);                        }                    }                    else                    {                        OnSQLFailure(updateSQL.ToString(), new DataException("ERROR: No \"WHERE\" criteria was generated for Sql update statement, primary key value missing?  Update not performed"));                    }                     progress += 1;                    m_overallProgress += 1;                     if (progress % RowReportInterval == 0)                    {                        OnRowProgress(fromTable.Name, progress, totalProgress);                        OnOverallProgress((int)m_overallProgress, (int)m_overallTotal);                    }                }                 fromReader.Close();            }             OnRowProgress(fromTable.Name, totalProgress, totalProgress);            OnOverallProgress((int)m_overallProgress, (int)m_overallTotal);        }         #endregion    }}