//******************************************************************************************************//  TableOperations.cs - Gbtc////  Copyright © 2016, 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://  ----------------------------------------------------------------------------------------------------//  02/01/2016 - J. Ritchie Carroll//       Generated original version of source code.////****************************************************************************************************** using ExpressionEvaluator;using GSF.Collections;using GSF.ComponentModel;using GSF.Reflection;using GSF.Security.Cryptography;using System;using System.Collections;using System.Collections.Generic;using System.ComponentModel;using System.Data;using System.Data.Common;using System.Diagnostics.CodeAnalysis;using System.Linq;using System.Reflection;using System.Runtime.CompilerServices;using System.Text; // ReSharper disable UnusedMember.Global// ReSharper disable StaticMemberInGenericType// ReSharper disable UnusedMember.Local// ReSharper disable AssignNullToNotNullAttributenamespace GSF.Data.Model{    /// <summary>    /// Defines database operations for a modeled table.    /// </summary>    /// <typeparam name="T">Modeled table.</typeparam>    [SuppressMessage("Microsoft.Maintainability", "CA1506:AvoidExcessiveClassCoupling")]    public class TableOperations<T> : ITableOperations where T : class, new()    {        #region [ Members ]         // Nested Types        private class CurrentScope : ValueExpressionScopeBase<T>        {            // Define instance variables exposed to ValueExpressionAttributeBase expressions            #pragma warning disable 169, 414, 649            public TableOperations<T> TableOperations;            public AdoDataConnection Connection;            #pragma warning restore 169, 414, 649        }         private class NullConnection : IDbConnection        {            public string ConnectionString { get; set; }            public int ConnectionTimeout { get; } = 0;            public string Database { get; } = null;            public ConnectionState State { get; } = ConnectionState.Open;            public void Open() { }            public void Close() { }            public void Dispose() { }            public void ChangeDatabase(string databaseName) { }            public IDbCommand CreateCommand() => null;            public IDbTransaction BeginTransaction() => null;            public IDbTransaction BeginTransaction(IsolationLevel il) => null;        }         private class IntermediateParameter : IDbDataParameter        {            public DbType DbType { get; set; }            public ParameterDirection Direction { get; set; }            public bool IsNullable { get; } = false;            public string ParameterName { get; set; }            public string SourceColumn { get; set; }            public DataRowVersion SourceVersion { get; set; }            public object Value { get; set; }            public byte Precision { get; set; }            public byte Scale { get; set; }            public int Size { get; set; }        }         // Constants        private const string SelectCountSqlFormat = "SELECT COUNT(*) FROM {0}";        private const string SelectSetSqlFormat = "SELECT {0} FROM {1} ORDER BY {{0}}";        private const string SelectSetWhereSqlFormat = "SELECT {0} FROM {1} WHERE {{0}} ORDER BY {{1}}";        private const string SelectRowSqlFormat = "SELECT * FROM {0} WHERE {1}";        private const string AddNewSqlFormat = "INSERT INTO {0}({1}) VALUES ({2})";        private const string UpdateSqlFormat = "UPDATE {0} SET {1} WHERE {2}";        private const string DeleteSqlFormat = "DELETE FROM {0} WHERE {1}";        private const string TableNamePrefixToken = "<!TNP/>";        private const string TableNameSuffixToken = "<!TNS/>";        private const string FieldListPrefixToken = "<!FLP/>";        private const string FieldListSuffixToken = "<!FLS/>";         // Fields        private readonly string m_selectCountSql;        private readonly string m_selectSetSql;        private readonly string m_selectSetWhereSql;        private readonly string m_selectKeysSql;        private readonly string m_selectKeysWhereSql;        private readonly string m_selectRowSql;        private readonly string m_addNewSql;        private readonly string m_updateSql;        private readonly string m_updateWhereSql;        private readonly string m_deleteSql;        private readonly string m_deleteWhereSql;        private readonly string m_searchFilterSql;        private string m_lastSortField;        private RecordRestriction m_lastRestriction;         #endregion         #region [ Constructors ]         /// <summary>        /// Creates a new <see cref="TableOperations{T}"/>.        /// </summary>        /// <param name="connection"><see cref="AdoDataConnection"/> instance to use for database operations.</param>        /// <param name="customTokens">Custom run-time tokens to apply to any modeled <see cref="AmendExpressionAttribute"/> values.</param>        /// <exception cref="ArgumentNullException"><paramref name="connection"/> cannot be <c>null</c>.</exception>        /// <remarks>        /// The <paramref name="customTokens"/> can be used to apply run-time tokens to any defined <see cref="AmendExpressionAttribute"/> values,        /// for example, given the following amendment expression applied to a modeled class:        /// <code>        /// [AmendExpression("TOP {count}",         ///     TargetExpression = TargetExpression.FieldList,        ///     AffixPosition = AffixPosition.Prefix,        ///     StatementTypes = StatementTypes.SelectSet)]]        /// </code>        /// The <paramref name="customTokens"/> key/value pairs could be set as follows at run-time:        /// <code>        /// int count = 200;        /// customTokens = new[] { new KeyValuePair&lt;string, string&gt;("{count}", $"{count}") };        /// </code>        /// </remarks>        [SuppressMessage("Microsoft.Maintainability", "CA1502:AvoidExcessiveComplexity")]        public TableOperations(AdoDataConnection connection, IEnumerable<KeyValuePair<string, string>> customTokens = null)        {            if ((object)connection == null)                throw new ArgumentNullException(nameof(connection));             Connection = connection;            m_selectCountSql = s_selectCountSql;            m_selectSetSql = s_selectSetSql;            m_selectSetWhereSql = s_selectSetWhereSql;            m_selectKeysSql = s_selectKeysSql;            m_selectKeysWhereSql = s_selectKeysWhereSql;            m_selectRowSql = s_selectRowSql;            m_addNewSql = s_addNewSql;            m_updateSql = s_updateSql;            m_updateWhereSql = s_updateWhereSql;            m_deleteSql = s_deleteSql;            m_deleteWhereSql = s_deleteWhereSql;            m_searchFilterSql = s_searchFilterSql;             // Establish any modeled root query restriction parameters            if ((object)s_rootQueryRestrictionAttribute != null)            {                RootQueryRestriction = new RecordRestriction(s_rootQueryRestrictionAttribute.FilterExpression, s_rootQueryRestrictionAttribute.Parameters);                ApplyRootQueryRestrictionToUpdates = s_rootQueryRestrictionAttribute.ApplyToUpdates;                ApplyRootQueryRestrictionToDeletes = s_rootQueryRestrictionAttribute.ApplyToDeletes;            }             // When any escape targets are defined for the modeled identifiers, i.e., table or field names,            // the static SQL statements are defined with ANSI standard escape delimiters. We check if the            // user model has opted to instead use common database escape delimiters, or no delimiters, that            // will apply to the active database type and make any needed adjustments. As a result, it will            // be slightly faster to construct this class when ANSI standard escape delimiters are used.            if ((object)s_escapedTableNameTargets != null)            {                string derivedTableName = GetEscapedTableName();                string ansiEscapedTableName = $"\"{s_tableName}\"";                 if (!derivedTableName.Equals(ansiEscapedTableName))                {                    m_selectCountSql = m_selectCountSql.Replace(ansiEscapedTableName, derivedTableName);                    m_selectSetSql = m_selectSetSql.Replace(ansiEscapedTableName, derivedTableName);                    m_selectSetWhereSql = m_selectSetWhereSql.Replace(ansiEscapedTableName, derivedTableName);                    m_selectKeysSql = m_selectKeysSql.Replace(ansiEscapedTableName, derivedTableName);                    m_selectKeysWhereSql = m_selectKeysWhereSql.Replace(ansiEscapedTableName, derivedTableName);                    m_selectRowSql = m_selectRowSql.Replace(ansiEscapedTableName, derivedTableName);                    m_addNewSql = m_addNewSql.Replace(ansiEscapedTableName, derivedTableName);                    m_updateSql = m_updateSql.Replace(ansiEscapedTableName, derivedTableName);                    m_updateWhereSql = m_updateWhereSql.Replace(ansiEscapedTableName, derivedTableName);                    m_deleteSql = m_deleteSql.Replace(ansiEscapedTableName, derivedTableName);                    m_deleteWhereSql = m_deleteWhereSql.Replace(ansiEscapedTableName, derivedTableName);                }            }             if ((object)s_escapedFieldNameTargets != null)            {                foreach (KeyValuePair<string, Dictionary<DatabaseType, bool>> escapedFieldNameTarget in s_escapedFieldNameTargets)                {                    string fieldName = escapedFieldNameTarget.Key;                    string derivedFieldName = GetEscapedFieldName(fieldName, escapedFieldNameTarget.Value);                    string ansiEscapedFieldName = $"\"{fieldName}\"";                     if (!derivedFieldName.Equals(ansiEscapedFieldName))                    {                        m_selectKeysSql = m_selectKeysSql.Replace(ansiEscapedFieldName, derivedFieldName);                        m_selectKeysWhereSql = m_selectKeysWhereSql.Replace(ansiEscapedFieldName, derivedFieldName);                        m_selectRowSql = m_selectRowSql.Replace(ansiEscapedFieldName, derivedFieldName);                        m_addNewSql = m_addNewSql.Replace(ansiEscapedFieldName, derivedFieldName);                        m_updateSql = m_updateSql.Replace(ansiEscapedFieldName, derivedFieldName);                        m_updateWhereSql = m_updateWhereSql.Replace(ansiEscapedFieldName, derivedFieldName);                        m_deleteSql = m_deleteSql.Replace(ansiEscapedFieldName, derivedFieldName);                        m_deleteWhereSql = m_deleteWhereSql.Replace(ansiEscapedFieldName, derivedFieldName);                        m_searchFilterSql = m_searchFilterSql.Replace(ansiEscapedFieldName, derivedFieldName);                    }                }            }             // Handle any modeled expression amendments            if ((object)s_expressionAmendments != null)            {                foreach (Tuple<DatabaseType, TargetExpression, StatementTypes, AffixPosition, string> expressionAmendment in s_expressionAmendments)                {                    // See if expression amendment applies to current database type                    if (expressionAmendment.Item1 != Connection.DatabaseType)                        continue;                     // Get expression amendment properties                    TargetExpression targetExpression = expressionAmendment.Item2;                    StatementTypes statementTypes = expressionAmendment.Item3;                    AffixPosition affixPosition = expressionAmendment.Item4;                    string amendmentText = expressionAmendment.Item5;                    string tableNameToken = affixPosition == AffixPosition.Prefix ? TableNamePrefixToken : TableNameSuffixToken;                    string fieldListToken = affixPosition == AffixPosition.Prefix ? FieldListPrefixToken : FieldListSuffixToken;                    string targetToken = targetExpression == TargetExpression.TableName ? tableNameToken : fieldListToken;                     // Apply amendment to target statement types                    if (statementTypes.HasFlag(StatementTypes.SelectCount) && targetExpression == TargetExpression.TableName)                        m_selectCountSql = m_selectCountSql.Replace(targetToken, amendmentText);                     if (statementTypes.HasFlag(StatementTypes.SelectSet))                    {                        m_selectSetSql = m_selectSetSql.Replace(targetToken, amendmentText);                        m_selectSetWhereSql = m_selectSetWhereSql.Replace(targetToken, amendmentText);                        m_selectKeysSql = m_selectKeysSql.Replace(targetToken, amendmentText);                        m_selectKeysWhereSql = m_selectKeysWhereSql.Replace(targetToken, amendmentText);                    }                     if (statementTypes.HasFlag(StatementTypes.SelectRow))                        m_selectRowSql = m_selectRowSql.Replace(targetToken, amendmentText);                     if (statementTypes.HasFlag(StatementTypes.Insert))                        m_addNewSql = m_addNewSql.Replace(targetToken, amendmentText);                     if (statementTypes.HasFlag(StatementTypes.Update))                    {                        m_updateSql = m_updateSql.Replace(targetToken, amendmentText);                        m_updateWhereSql = m_updateWhereSql.Replace(targetToken, amendmentText);                    }                     if (statementTypes.HasFlag(StatementTypes.Delete))                    {                        m_deleteSql = m_deleteSql.Replace(targetToken, amendmentText);                        m_deleteWhereSql = m_deleteWhereSql.Replace(targetToken, amendmentText);                    }                }                 // Remove any remaining tokens from instance expressions                string RemoveRemainingTokens(string sql) => sql                        .Replace(TableNamePrefixToken, "")                        .Replace(TableNameSuffixToken, "")                        .Replace(FieldListPrefixToken, "")                        .Replace(FieldListSuffixToken, "");                 m_selectCountSql = RemoveRemainingTokens(m_selectCountSql);                m_selectSetSql = RemoveRemainingTokens(m_selectSetSql);                m_selectSetWhereSql = RemoveRemainingTokens(m_selectSetWhereSql);                m_selectKeysSql = RemoveRemainingTokens(m_selectKeysSql);                m_selectKeysWhereSql = RemoveRemainingTokens(m_selectKeysWhereSql);                m_selectRowSql = RemoveRemainingTokens(m_selectRowSql);                m_addNewSql = RemoveRemainingTokens(m_addNewSql);                m_updateSql = RemoveRemainingTokens(m_updateSql);                m_updateWhereSql = RemoveRemainingTokens(m_updateWhereSql);                m_deleteSql = RemoveRemainingTokens(m_deleteSql);                m_deleteWhereSql = RemoveRemainingTokens(m_deleteWhereSql);                 // Execute replacements on any provided custom run-time tokens                if ((object)customTokens != null)                {                    foreach (KeyValuePair<string, string> customToken in customTokens)                    {                        m_selectCountSql = m_selectCountSql.Replace(customToken.Key, customToken.Value);                        m_selectSetSql = m_selectSetSql.Replace(customToken.Key, customToken.Value);                        m_selectSetWhereSql = m_selectSetWhereSql.Replace(customToken.Key, customToken.Value);                        m_selectKeysSql = m_selectKeysSql.Replace(customToken.Key, customToken.Value);                        m_selectKeysWhereSql = m_selectKeysWhereSql.Replace(customToken.Key, customToken.Value);                        m_selectRowSql = m_selectRowSql.Replace(customToken.Key, customToken.Value);                        m_addNewSql = m_addNewSql.Replace(customToken.Key, customToken.Value);                        m_updateSql = m_updateSql.Replace(customToken.Key, customToken.Value);                        m_updateWhereSql = m_updateWhereSql.Replace(customToken.Key, customToken.Value);                        m_deleteSql = m_deleteSql.Replace(customToken.Key, customToken.Value);                        m_deleteWhereSql = m_deleteWhereSql.Replace(customToken.Key, customToken.Value);                    }                }            }        }         /// <summary>        /// Creates a new <see cref="TableOperations{T}"/> using provided <paramref name="exceptionHandler"/>.        /// </summary>        /// <param name="connection"><see cref="AdoDataConnection"/> instance to use for database operations.</param>        /// <param name="exceptionHandler">Delegate to handle table operation exceptions.</param>        /// <param name="customTokens">Custom run-time tokens to apply to any modeled <see cref="AmendExpressionAttribute"/> values.</param>        /// <remarks>        /// <para>        /// When exception handler is provided, table operations will not throw exceptions for database calls, any        /// encountered exceptions will be passed to handler for processing.        /// </para>        /// <para>        /// The <paramref name="customTokens"/> can be used to apply run-time tokens to any defined <see cref="AmendExpressionAttribute"/> values,        /// for example, given the following amendment expression applied to a modeled class:        /// <code>        /// [AmendExpression("TOP {count}",         ///     TargetExpression = TargetExpression.FieldList,        ///     AffixPosition = AffixPosition.Prefix,        ///     StatementTypes = StatementTypes.SelectSet)]]        /// </code>        /// The <paramref name="customTokens"/> key/value pairs could be set as follows at run-time:        /// <code>        /// int count = 200;        /// customTokens = new[] { new KeyValuePair&lt;string, string&gt;("{count}", $"{count}") };        /// </code>        /// </para>        /// </remarks>        /// <exception cref="ArgumentNullException"><paramref name="connection"/> cannot be <c>null</c>.</exception>        public TableOperations(AdoDataConnection connection, Action<Exception> exceptionHandler, IEnumerable<KeyValuePair<string, string>> customTokens = null)            : this(connection, customTokens) => ExceptionHandler = exceptionHandler;         #endregion         #region [ Properties ]         /// <summary>        /// Gets <see cref="AdoDataConnection"/> instance associated with this <see cref="TableOperations{T}"/> used for database operations.        /// </summary>        public AdoDataConnection Connection { get; }         /// <summary>        /// Gets the table name defined for the modeled table, includes any escaping as defined in model.        /// </summary>        public string TableName => GetEscapedTableName();         /// <summary>        /// Gets the table name defined for the modeled table without any escape characters.        /// </summary>        /// <remarks>        /// A table name will only be escaped if the model requested escaping with the <see cref="UseEscapedNameAttribute"/>.        /// </remarks>        public string UnescapedTableName => s_tableName;         /// <summary>        /// Gets flag that determines if modeled table has a primary key that is an identity field.        /// </summary>        public bool HasPrimaryKeyIdentityField => s_hasPrimaryKeyIdentityField;         /// <summary>        /// Gets or sets delegate used to handle table operation exceptions.        /// </summary>        /// <remarks>        /// When exception handler is provided, table operations will not throw exceptions for database calls, any        /// encountered exceptions will be passed to handler for processing. Otherwise, exceptions will be thrown        /// on the call stack.        /// </remarks>        public Action<Exception> ExceptionHandler { get; set; }         /// <summary>        /// Gets or sets flag that determines if field names should be treated as case sensitive. Defaults to <c>false</c>.        /// </summary>        /// <remarks>        /// In cases where modeled table fields have applied <see cref="UseEscapedNameAttribute"/>, this flag will be used        /// to properly update escaped field names that may be case sensitive. For example, escaped field names in Oracle        /// are case sensitive. This value is typically <c>false</c>.        /// </remarks>        public bool UseCaseSensitiveFieldNames { get; set; }         /// <summary>        /// Gets or sets primary key cache.        /// </summary>        /// <remarks>        /// <para>        /// The <see cref="QueryRecords(string, bool, int, int, string)"/> overloads that include paging parameters        /// cache the sorted and filtered primary keys of queried records between calls so that paging is fast and        /// efficient. Since the primary keys are cached, an instance of the <see cref="TableOperations{T}"/> should        /// exist per user session when using query functions that support pagination. In web based implementations,        /// the primary cache should be stored with user session state data and then restored between instances of        /// the <see cref="TableOperations{T}"/> that are created along with a connection that is opened per page.        /// </para>        /// <para>        /// The function <see cref="ClearPrimaryKeyCache"/> should be called to manually clear cache when table        /// contents are known to have changed. Note that calls to any <see cref="DeleteRecord(T)"/> overloads will        /// automatically clear any existing primary key cache.        /// </para>        /// <para>        /// Primary keys values are stored in data table without interpretation, i.e., in their raw form as queried        /// from the database. Primary key data in cache will be encrypted for models with primary key fields that        /// are marked with the <see cref="EncryptDataAttribute"/>        /// </para>        /// </remarks>        public DataTable PrimaryKeyCache { get; set; }         /// <summary>        /// Gets or sets root record restriction that applies to query table operations.        /// </summary>        /// <remarks>        /// <para>        /// Defining a root query restriction creates a base query filter that gets applied to all query operations,        /// even when another restriction is applied - in this case the root restriction will be pre-pended to the        /// specified query, e.g.:        /// <code>        /// restriction = RootQueryRestriction + restriction;        /// </code>        /// A root query restriction is useful to apply a common state to the query operations, e.g., always        /// filtering records for a specific user or context.        /// </para>        /// <para>        /// A root query restriction can be manually assigned to a <see cref="TableOperations{T}"/> instance or        /// automatically assigned by marking a model with the <see cref="RootQueryRestrictionAttribute"/>.        /// </para>        /// <para>        /// If any of the <see cref="RecordRestriction.Parameters"/> reference a table field that is modeled with        /// either an <see cref="EncryptDataAttribute"/> or <see cref="FieldDataTypeAttribute"/>, then the function        /// <see cref="GetInterpretedFieldValue"/> will need to be called, replacing the target parameter with the        /// returned value so that the field value will be properly set prior to executing the database function.        /// </para>        /// </remarks>        public RecordRestriction RootQueryRestriction { get; set; }         /// <summary>        /// Gets or sets flag that determines if <see cref="RootQueryRestriction"/> should be applied to update operations.        /// </summary>        /// <remarks>        /// <para>        /// If <see cref="RootQueryRestriction"/> only references primary key fields, then this property value should be set        /// to <c>false</c> since default update operations for a modeled record already work against primary key fields.        /// </para>        /// <para>        /// This flag can be manually set per <see cref="TableOperations{T}"/> instance or handled automatically by marking        /// a model with the <see cref="RootQueryRestrictionAttribute"/> and assigning a value to the attribute property        /// <see cref="RootQueryRestrictionAttribute.ApplyToUpdates"/>.        /// </para>        /// </remarks>        public bool ApplyRootQueryRestrictionToUpdates { get; set; }         /// <summary>        /// Gets or sets flag that determines if <see cref="RootQueryRestriction"/> should be applied to delete operations.        /// </summary>        /// <remarks>        /// <para>        /// If <see cref="RootQueryRestriction"/> only references primary key fields, then this property value should be set        /// to <c>false</c> since default delete operations for a modeled record already work against primary key fields.        /// </para>        /// <para>        /// This flag can be manually set per <see cref="TableOperations{T}"/> instance or handled automatically by marking        /// a model with the <see cref="RootQueryRestrictionAttribute"/> and assigning a value to the attribute property        /// <see cref="RootQueryRestrictionAttribute.ApplyToDeletes"/>.        /// </para>        /// </remarks>        public bool ApplyRootQueryRestrictionToDeletes { get; set; }         #endregion         #region [ Methods ]         /// <summary>        /// Creates a new modeled record instance, applying any modeled default values as specified by a        /// <see cref="DefaultValueAttribute"/> or <see cref="DefaultValueExpressionAttribute"/> on the        /// model properties.        /// </summary>        /// <returns>New modeled record instance with any defined default values applied.</returns>        public T NewRecord()        {            try            {                return s_createRecordInstance(new CurrentScope                {                    TableOperations = this,                    Connection = Connection                });            }            catch (Exception ex)            {                if ((object)ExceptionHandler == null)                    throw;

Lines 1–500 of 2660. Load more