//******************************************************************************************************//  DataInserter.cs - Gbtc////  Copyright © 2012, 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://www.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://  ----------------------------------------------------------------------------------------------------//  06/28/2010 - J. Ritchie Carroll//       Generated original version of source code from code written in 2003.//  08/21/2008 - Mihir Brahmbhatt//       Converted to C# extensions.//  09/27/2010 - Mihir Brahmbhatt//       Edited code comments.//  10/12/2010 - Mihir Brahmbhatt//       Updated preserve value functionality for auto-inc fields//  12/20/2012 - Starlynn Danyelle Gilliam//       Modified Header.////****************************************************************************************************** using System;using System.Collections;using System.Collections.Generic;using System.Data;using System.Data.OleDb;using System.Diagnostics.CodeAnalysis;using System.IO;using System.Linq;using System.Text;using GSF.IO;using GSF.Reflection; namespace GSF.Data{    // Note: if you have triggers that insert records into other tables automatically that have defined records to    // be inserted, this class will check for this occurrence and do SQL updates instead of SQL inserts.  However,    // just like in the DataUpdater class, you must define the 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 DataInserter : BulkDataOperationBase    {        #region [ Members ]         //Fields        private bool m_attemptBulkInsert;        private bool m_forceBulkInsert;        private string m_bulkInsertSettings = "FIELDTERMINATOR = '\\t', ROWTERMINATOR = '\\n', CODEPAGE = 'OEM', FIRE_TRIGGERS, KEEPNULLS";        private Encoding m_bulkInsertEncoding = Encoding.ASCII;         private string m_bulkInsertFilePath = AssemblyInfo.ExecutingAssembly.Location;        private string m_delimiterReplacement = " - ";        private bool m_clearDestinationTables;        private bool m_attemptTruncateTable;        private bool m_preserveAutoIncValues;        private bool m_forceTruncateTable;         /// <summary>        /// Table cleared event.        /// </summary>        public event EventHandler<EventArgs<string>> TableCleared;                /// <summary>        /// Bulk-insert executing event.        /// </summary>        public event EventHandler<EventArgs<string>> BulkInsertExecuting;                /// <summary>        /// Bulk-insert completed event.        /// </summary>        public event EventHandler<EventArgs<string, int, int>> BulkInsertCompleted;                /// <summary>        /// Bulk-insert exception event.        /// </summary>        public event EventHandler<EventArgs<string, string, Exception>> BulkInsertException;         /// <summary>        /// Disposed event.        /// </summary>        public event EventHandler Disposed;         #endregion         #region [ Constructors ]         /// <summary>        /// Creates a new <see cref="DataInserter"/>.        /// </summary>        public DataInserter()        {         }         /// <summary>        /// Creates a new <see cref="DataInserter"/>.        /// </summary>        public DataInserter(string FromConnectString, string ToConnectString)            : base(FromConnectString, ToConnectString)        {         }         /// <summary>        /// Creates a new <see cref="DataInserter"/>.        /// </summary>        public DataInserter(Schema FromSchema, Schema ToSchema)            : base(FromSchema, ToSchema)        {        }         #endregion         #region [ Properties ]         /// <summary>        /// Get or set to attempt use of a BULK INSERT on a destination SQL Server connection        /// </summary>        public bool AttemptBulkInsert        {            get            {                return m_attemptBulkInsert;            }            set            {                m_attemptBulkInsert = value;            }        }         /// <summary>        /// Get or set to force use of a BULK INSERT on a destination SQL Server connection regardless of whether or not it looks        /// like the referential integrity definition supports this.        /// </summary>        public bool ForceBulkInsert        {            get            {                return m_forceBulkInsert;            }            set            {                m_forceBulkInsert = value;            }        }         /// <summary>        /// This setting defines the SQL Server BULK INSERT settings that will be used if a BULK INSERT is performed.         /// </summary>        public string BulkInsertSettings        {            get            {                return m_bulkInsertSettings;            }            set            {                m_bulkInsertSettings = value;            }        }         /// <summary>        /// This setting defines the text encoding that will be used when writing a temporary BULK INSERT file that will be needed        /// if a SQL Server BULK INSERT is performed - make sure the encoding output matches the specified CODEPAGE value in the        /// BulkInsertSettings property.        /// </summary>        public Encoding BulkInsertEncoding        {            get            {                return m_bulkInsertEncoding;            }            set            {                m_bulkInsertEncoding = value;            }        }         /// <summary>        /// This setting defines the file path that will be used when writing a temporary BULK INSERT file that will be needed if a        /// SQL Server BULK INSERT is performed - make sure the destination SQL Server has rights to this path.        /// </summary>        public string BulkInsertFilePath        {            get            {                return m_bulkInsertFilePath;            }            set            {                m_bulkInsertFilePath = value;            }        }         /// <summary>        /// This specifies the string that will be substituted for the field terminator or row terminator if encountered in a database        /// value while creating a BULK INSERT file.  The field terminator and row terminator values are defined in the BulkInsertSettings        /// property specified by the FIELDTERMINATOR and ROWTERMINATOR keywords respectively.        /// </summary>        public string DelimiterReplacement        {            get            {                return m_delimiterReplacement;            }            set            {                m_delimiterReplacement = value;            }        }          /// <summary>        /// Set to True to clear all data from the destination database before processing data inserts.        /// </summary>        public bool ClearDestinationTables        {            get            {                return m_clearDestinationTables;            }            set            {                m_clearDestinationTables = value;            }        }         /// <summary>        /// Set to True to attempt use of a TRUNCATE TABLE on a destination SQL Server connection if ClearDestinationTables is True        /// and it looks like the referential integrity definition supports this.  Your SQL Server connection will need the rights        /// to perform this operation.        /// </summary>        public bool AttemptTruncateTable        {            get            {                return m_attemptTruncateTable;            }            set            {                m_attemptTruncateTable = value;            }        }         /// <summary>        /// Set to True to force use of a TRUNCATE TABLE on a destination SQL Server connection if ClearDestinationTables is True regardless        /// of whether or not it looks like the referential integrity definition supports this.  Your SQL Server connection will need the        /// rights to perform this operation        /// </summary>        public bool ForceTruncateTable        {            get            {                return m_forceTruncateTable;            }            set            {                m_forceTruncateTable = value;            }        }         /// <summary>        /// Set to True to preserve primary key value data to the destination database before processing data inserts.        /// </summary>        public bool PreserveAutoIncValues        {            get            {                return m_preserveAutoIncValues;            }            set            {                m_preserveAutoIncValues = value;            }        }         #endregion         #region [ Methods ]         /// <summary>        /// Close        /// </summary>        public override void Close()        {            base.Close();             if ((object)Disposed != null)                Disposed(this, EventArgs.Empty); //-V3083        }         /// <summary>        /// Execute this <see cref="DataInserter"/>        /// </summary>        public override void Execute()        {            List<Table> tablesList = new List<Table>();            Table tableLookup;            Table table;            int x;             m_overallProgress = 0;            m_overallTotal = 0;             if (m_tableCollection.Count == 0)                Analyze();             if (m_tableCollection.Count == 0)                throw new NullReferenceException("No tables to process even after analyze.");             // Clear data from destination tables, if requested            if (m_clearDestinationTables)            {                // We do not consider table exclusions when deleting data from destination tables as these may have triggered inserts                List<Table> allSourceTables = new List<Table>(m_fromSchema.Tables.Cast<Table>());                 // Clear data in a child to parent direction to help avoid potential constraint issues                allSourceTables.Sort((table1, table2) => table1.Priority > table2.Priority ? 1 : (table1.Priority < table2.Priority ? -1 : 0));                 for (x = allSourceTables.Count - 1; x >= 0; x += -1)                {                    // Lookup table name in destination data source                    tableLookup = m_toSchema.Tables.FindByMapName(((Table)allSourceTables[x]).MapName);                     if ((object)tableLookup != null)                    {                        if (ClearTable(tableLookup) && tableLookup.HasAutoIncField)                            ResetAutoIncValues(tableLookup);                    }                }            }             // 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 inserting data into destination tables            for (x = 0; x <= tablesList.Count - 1; x++)            {                table = (Table)tablesList[x];                 // Lookup table name in destination data source                tableLookup = m_toSchema.Tables.FindByMapName(table.MapName);                 if ((object)tableLookup != null)                {                    if (table.RowCount > 0)                    {                        // Inform clients of table copy                        OnTableProgress(table.Name, true, x + 1, tablesList.Count);                         // Copy source table to destination                        ExecuteInserts(table, tableLookup);                    }                    else                    {                        // Inform clients of table skip                        OnTableProgress(table.Name, false, x + 1, tablesList.Count);                    }                }                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>        /// Clear destination schema table        /// </summary>        /// <param name="table">schema table</param>        private bool ClearTable(Table table)        {            string deleteSql;            bool useTruncateTable = false;             if (m_attemptTruncateTable || m_forceTruncateTable)            {                // We only attempt a truncate table if the destination data source type is SQL Server                // and table has no foreign key dependencies (or user forces procedure)                useTruncateTable = m_forceTruncateTable || (table.Parent.Parent.DataSourceType == DatabaseType.SQLServer && !table.ReferencedByForeignKeys);            }             if (useTruncateTable)                deleteSql = "TRUNCATE TABLE " + table.SQLEscapedName;            else                deleteSql = "DELETE FROM " + table.SQLEscapedName;             try            {                table.Connection.ExecuteNonQuery(deleteSql, Timeout);                 if ((object)TableCleared != null)                    TableCleared(this, new EventArgs<string>(table.Name)); //-V3083                 return true;            }            catch (Exception ex)            {                if (useTruncateTable)                {                    // SQL Server connection may not have rights to use TRUNCATE TABLE, fall back on DELETE FROM                    m_attemptTruncateTable = false;                    m_forceTruncateTable = false;                    return ClearTable(table);                }                 OnSQLFailure(deleteSql, ex);            }             return false;        }         private void ResetAutoIncValues(Table table)        {            string resetAutoIncValueSQL = "unknown";             try            {                switch (table.Parent.Parent.DataSourceType) //-V3002                {                    case DatabaseType.SQLServer:                        resetAutoIncValueSQL = "DBCC CHECKIDENT('" + table.SQLEscapedName + "', RESEED)";                        table.Connection.ExecuteNonQuery(resetAutoIncValueSQL, Timeout);                        break;                    case DatabaseType.MySQL:                        resetAutoIncValueSQL = "ALTER TABLE " + table.SQLEscapedName + " AUTO_INCREMENT = 1";                        table.Connection.ExecuteNonQuery(resetAutoIncValueSQL, Timeout);                        break;                    case DatabaseType.SQLite:                        resetAutoIncValueSQL = "DELETE FROM sqlite_sequence WHERE name = '" + table.Name + "'";                        table.Connection.ExecuteNonQuery(resetAutoIncValueSQL, Timeout);                        break;                    case DatabaseType.PostgreSQL:                        // The escaping of names here is very deliberate; for certain table names,                        // it is necessary to escape the table name in the pg_get_serial_sequence() call,                        // but the call will fail if you attempt to escape the autoIncField name                        resetAutoIncValueSQL = $"SELECT setval(pg_get_serial_sequence('{table.SQLEscapedName}', '{table.AutoIncField.Name.ToLower()}'), (SELECT MAX({table.AutoIncField.SQLEscapedName}) FROM {table.SQLEscapedName}))";                        table.Connection.ExecuteNonQuery(resetAutoIncValueSQL, Timeout);                        break;                }            }            catch (Exception ex)            {                OnSQLFailure(resetAutoIncValueSQL, new InvalidOperationException(string.Format("Failed to reset auto-increment seed for table \"{0}\": {1}", table.Name, ex.Message), ex));            }        }         /// <summary>        /// Execute a command to insert or 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 ExecuteInserts(Table fromTable, Table toTable)        {            Table sourceTable = (m_useFromSchemaRI ? fromTable : toTable);            Field autoIncField = null;            Field lookupField;            Field commonField;            bool usingIdentityInsert;             // Progress process variables            int progressIndex = 0;            int progressTotal;             // Bulk insert variables            bool useBulkInsert = false;

Lines 1–500 of 1259. Load more