//******************************************************************************************************//  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 System;using System.Collections;using System.Collections.Generic;using System.Data;using System.Linq;using System.Reflection;using System.Runtime.CompilerServices;using System.Text;using GSF.Collections;using GSF.Reflection; // ReSharper disable StaticMemberInGenericTypenamespace GSF.Data.Model{    /// <summary>    /// Defines database operations for a modeled table.    /// </summary>    /// <typeparam name="T">Modeled table.</typeparam>    public class TableOperations<T> : ITableOperations where T : class, new()    {        #region [ Members ]         // Constants        private const string CountSqlFormat = "SELECT COUNT(*) FROM {0}";        private const string OrderBySqlFormat = "SELECT {0} FROM {1} ORDER BY {{0}}";        private const string OrderByWhereSqlFormat = "SELECT {0} FROM {1} WHERE {{0}} ORDER BY {{1}}";        private const string SelectSqlFormat = "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}";         // Fields        private readonly AdoDataConnection m_connection;        private Action<Exception> m_exceptionHandler;        private IEnumerable<DataRow> m_primaryKeyCache;        private string m_lastSortField;        private bool m_useCaseSensitiveFieldNames;        private readonly string m_countSql;        private readonly string m_orderBySql;        private readonly string m_orderByWhereSql;        private readonly string m_selectSql;        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;         #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>        /// <exception cref="ArgumentNullException"><paramref name="connection"/> cannot be <c>null</c>.</exception>        public TableOperations(AdoDataConnection connection)        {            if ((object)connection == null)                throw new ArgumentNullException(nameof(connection));             m_connection = connection;            m_countSql = s_countSql;            m_orderBySql = s_orderBySql;            m_orderByWhereSql = s_orderByWhereSql;            m_selectSql = s_selectSql;            m_addNewSql = s_addNewSql;            m_updateSql = s_updateSql;            m_updateWhereSql = s_updateWhereSql;            m_deleteSql = s_deleteSql;            m_deleteWhereSql = s_deleteWhereSql;            m_searchFilterSql = s_searchFilterSql;             // 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_countSql = m_countSql.Replace(ansiEscapedTableName, derivedTableName);                    m_orderBySql = m_orderBySql.Replace(ansiEscapedTableName, derivedTableName);                    m_orderByWhereSql = m_orderByWhereSql.Replace(ansiEscapedTableName, derivedTableName);                    m_selectSql = m_selectSql.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_orderBySql = m_orderBySql.Replace(ansiEscapedFieldName, derivedFieldName);                        m_orderByWhereSql = m_orderByWhereSql.Replace(ansiEscapedFieldName, derivedFieldName);                        m_selectSql = m_selectSql.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);                    }                }            }        }         /// <summary>        /// Creates a new <see cref="TableOperations{T}"/>.        /// </summary>        /// <param name="connection"><see cref="AdoDataConnection"/> instance to use for database operations.</param>        /// <param name="exceptionHandler">Delegate to handle table operation exceptions.</param>        /// <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.        /// </remarks>        /// <exception cref="ArgumentNullException"><paramref name="connection"/> cannot be <c>null</c>.</exception>        public TableOperations(AdoDataConnection connection, Action<Exception> exceptionHandler) : this(connection)        {            m_exceptionHandler = exceptionHandler;        }         #endregion         #region [ Properties ]         /// <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            {                return m_exceptionHandler;            }            set            {                m_exceptionHandler = value;            }        }         /// <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.        /// </remarks>        public bool UseCaseSensitiveFieldNames        {            get            {                return m_useCaseSensitiveFieldNames;            }            set            {                m_useCaseSensitiveFieldNames = value;            }        }         #endregion         #region [ Methods ]         /// <summary>        /// Queries database and returns modeled table records for the specified parameters.        /// </summary>        /// <param name="orderByExpression">Field name expression used for sort order, include ASC or DESC as needed - does not include ORDER BY; defaults to primary keys.</param>        /// <param name="restriction">Record restriction to apply, if any.</param>        /// <param name="limit">Limit of number of record to return.</param>        /// <returns>An enumerable of modeled table row instances for queried records.</returns>        /// <remarks>        /// If no record <paramref name="restriction"/> or <paramref name="limit"/> is provided, all rows will be returned.        /// </remarks>        public IEnumerable<T> QueryRecords(string orderByExpression = null, RecordRestriction restriction = null, int limit = -1)        {            if (string.IsNullOrWhiteSpace(orderByExpression))                orderByExpression = s_primaryKeyFields;             string sqlExpression = null;             try            {                if (limit < 1)                {                    // No record limit specified                    if ((object)restriction == null)                    {                        sqlExpression = string.Format(m_orderBySql, orderByExpression);                        return m_connection.RetrieveData(sqlExpression).AsEnumerable().Select(row => LoadRecord(GetPrimaryKeys(row)));                    }                     sqlExpression = string.Format(m_orderByWhereSql, UpdateFieldNames(restriction.FilterExpression), orderByExpression);                    return m_connection.RetrieveData(sqlExpression, restriction.Parameters).AsEnumerable().Select(row => LoadRecord(GetPrimaryKeys(row)));                }                 if ((object)restriction == null)                {                    sqlExpression = string.Format(m_orderBySql, orderByExpression);                    return m_connection.RetrieveData(sqlExpression).AsEnumerable().Take(limit).Select(row => LoadRecord(GetPrimaryKeys(row)));                }                 sqlExpression = string.Format(m_orderByWhereSql, UpdateFieldNames(restriction.FilterExpression), orderByExpression);                return m_connection.RetrieveData(sqlExpression, restriction.Parameters).AsEnumerable().Take(limit).Select(row => LoadRecord(GetPrimaryKeys(row)));            }            catch (Exception ex)            {                InvalidOperationException opex = new InvalidOperationException($"Exception during record query for {typeof(T).Name} \"{sqlExpression ?? "undefined"}, {ValueList(restriction?.Parameters)}\": {ex.Message}", ex);                 if ((object)m_exceptionHandler == null)                    throw opex;                 m_exceptionHandler(opex);                return Enumerable.Empty<T>();            }        }         IEnumerable ITableOperations.QueryRecords(string orderByExpression, RecordRestriction restriction, int limit)        {            return QueryRecords(orderByExpression, restriction, limit);        }         /// <summary>        /// Queries database and returns modeled table records for the specified sorting, paging and search parameters.        /// </summary>        /// <param name="sortField">Field name to order-by.</param>        /// <param name="ascending">Sort ascending flag; set to <c>false</c> for descending.</param>        /// <param name="page">Page number of records to return (1-based).</param>        /// <param name="pageSize">Current page size.</param>        /// <param name="searchText">Text to search.</param>        /// <returns>An enumerable of modeled table row instances for queried records.</returns>        /// <remarks>        /// This function is used for record paging. Primary keys are cached server-side, typically per user session, to maintain desired per-page sort order.        /// </remarks>        public IEnumerable<T> QueryRecords(string sortField, bool ascending, int page, int pageSize, string searchText)        {            return QueryRecords(sortField, ascending, page, pageSize, GetSearchRestriction(searchText));        }         IEnumerable ITableOperations.QueryRecords(string sortField, bool ascending, int page, int pageSize, string searchText)        {            return QueryRecords(sortField, ascending, page, pageSize, searchText);        }         /// <summary>        /// Queries database and returns modeled table records for the specified sorting and paging parameters.        /// </summary>        /// <param name="sortField">Field name to order-by.</param>        /// <param name="ascending">Sort ascending flag; set to <c>false</c> for descending.</param>        /// <param name="page">Page number of records to return (1-based).</param>        /// <param name="pageSize">Current page size.</param>        /// <param name="restriction">Record restriction to apply, if any.</param>        /// <returns>An enumerable of modeled table row instances for queried records.</returns>        /// <remarks>        /// This function is used for record paging. Primary keys are cached server-side, typically per user session, to maintain desired per-page sort order.        /// </remarks>        public IEnumerable<T> QueryRecords(string sortField, bool ascending, int page, int pageSize, RecordRestriction restriction = null)        {            if (string.IsNullOrWhiteSpace(sortField))                sortField = s_fieldNames[s_primaryKeyProperties[0].Name];             if ((object)m_primaryKeyCache == null || string.Compare(sortField, m_lastSortField, StringComparison.OrdinalIgnoreCase) != 0)            {                string orderByExpression = $"{sortField}{(ascending ? "" : " DESC")}";                string sqlExpression = null;                 try                {                    if ((object)restriction == null)                    {                        sqlExpression = string.Format(m_orderBySql, orderByExpression);                        m_primaryKeyCache = m_connection.RetrieveData(sqlExpression).AsEnumerable();                    }                    else                    {                        sqlExpression = string.Format(m_orderByWhereSql, UpdateFieldNames(restriction.FilterExpression), orderByExpression);                        m_primaryKeyCache = m_connection.RetrieveData(sqlExpression, restriction.Parameters).AsEnumerable();                    }                 }                catch (Exception ex)                {                    InvalidOperationException opex = new InvalidOperationException($"Exception during record query for {typeof(T).Name} \"{sqlExpression ?? "undefined"}, {ValueList(restriction?.Parameters)}\": {ex.Message}", ex);                     if ((object)m_exceptionHandler == null)                        throw opex;                     m_exceptionHandler(opex);                    return Enumerable.Empty<T>();                }                 m_lastSortField = sortField;            }             return m_primaryKeyCache.ToPagedList(page, pageSize).Select(row => LoadRecord(row.ItemArray)).Where(record => record != null);        }         IEnumerable ITableOperations.QueryRecords(string sortField, bool ascending, int page, int pageSize, RecordRestriction restriction)        {            return QueryRecords(sortField, ascending, page, pageSize, restriction);        }         /// <summary>        /// Gets the total record count for the modeled table based on search parameter.        /// </summary>        /// <param name="searchText">Text to search.</param>        /// <returns>Total record count for the modeled table.</returns>        public int QueryRecordCount(string searchText)        {            return QueryRecordCount(GetSearchRestriction(searchText));        }         /// <summary>        /// Gets the total record count for the modeled table.        /// </summary>        /// <param name="restriction">Record restriction to apply, if any.</param>        /// <returns>Total record count for the modeled table.</returns>        public int QueryRecordCount(RecordRestriction restriction = null)        {            string sqlExpression = null;             try            {                if ((object)restriction == null)                {                    sqlExpression = m_countSql;                    return m_connection.ExecuteScalar<int>(sqlExpression);                }                 sqlExpression = $"{m_countSql} WHERE {UpdateFieldNames(restriction.FilterExpression)}";                return m_connection.ExecuteScalar<int>(sqlExpression, restriction.Parameters);            }            catch (Exception ex)            {                InvalidOperationException opex = new InvalidOperationException($"Exception during record count query for {typeof(T).Name} \"{sqlExpression ?? "undefined"}, {ValueList(restriction?.Parameters)}\": {ex.Message}", ex);                 if ((object)m_exceptionHandler == null)                    throw opex;                 m_exceptionHandler(opex);                return -1;            }        }         /// <summary>        /// Creates a new modeled table record queried from the specified <paramref name="primaryKeys"/>.        /// </summary>        /// <param name="primaryKeys">Primary keys values of the record to load.</param>        /// <returns>New modeled table record queried from the specified <paramref name="primaryKeys"/>.</returns>        public T LoadRecord(params object[] primaryKeys)        {            try            {                return LoadRecord(m_connection.RetrieveRow(m_selectSql, primaryKeys));            }            catch (Exception ex)            {                InvalidOperationException opex = new InvalidOperationException($"Exception during record load for {typeof(T).Name} \"{m_selectSql}, {ValueList(primaryKeys)}\": {ex.Message}", ex);                 if ((object)m_exceptionHandler == null)                    throw opex;                 m_exceptionHandler(opex);                return null;            }        }         object ITableOperations.LoadRecord(params object[] primaryKeys)        {            return LoadRecord(primaryKeys);        }         /// <summary>        /// Creates a new modeled table record queried from the specified <paramref name="row"/>.        /// </summary>        /// <param name="row"><see cref="DataRow"/> of queried data to be loaded.</param>        /// <returns>New modeled table record queried from the specified <paramref name="row"/>.</returns>        public T LoadRecord(DataRow row)        {            try            {                T record = new T();                 // Make sure record exists, if not return null instead of a blank record                if (s_hasPrimaryKeyIdentityField && GetPrimaryKeys(row).All(Common.IsDefaultValue))                    return null;                 foreach (PropertyInfo property in s_properties.Values)                {                    try                    {                        property.SetValue(record, row.ConvertField(s_fieldNames[property.Name], property.PropertyType), null);                    }                    catch (Exception ex)                    {                        InvalidOperationException opex = new InvalidOperationException($"Exception during record load field assignment for \"{typeof(T).Name}.{property.Name} = {row[s_fieldNames[property.Name]]}\": {ex.Message}", ex);                         if ((object)m_exceptionHandler == null)                            throw opex;                         m_exceptionHandler(opex);                    }                }                 return record;            }            catch (Exception ex)            {                InvalidOperationException opex = new InvalidOperationException($"Exception during record load for {typeof(T).Name} from data row: {ex.Message}", ex);                 if ((object)m_exceptionHandler == null)                    throw opex;                 m_exceptionHandler(opex);                return null;            }        }         object ITableOperations.LoadRecord(DataRow row)        {            return LoadRecord(row);        }         /// <summary>        /// Deletes the record referenced by the specified <paramref name="primaryKeys"/>.        /// </summary>        /// <param name="primaryKeys">Primary keys values of the record to load.</param>        /// <returns>Number of rows affected.</returns>        public int DeleteRecord(params object[] primaryKeys)        {            try            {                int affectedRecords = m_connection.ExecuteNonQuery(m_deleteSql, primaryKeys);                 if (affectedRecords > 0)                    m_primaryKeyCache = null;                 return affectedRecords;            }

Lines 1–500 of 1307. Load more