jdescopingtool/OLD/DataModel/Process/LotFinderDB.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using Dapper;
using DataModel.Models;
using NLog;

namespace DataModel.Process
{
    /// <summary>
    /// LotFinder cache database interface
    /// </summary>
    public partial class LotFinderDB
    {
        /// <summary>
        /// Default command timeout (ms)
        /// </summary>
        protected const int DEFAULT_TIMEOUT = 600;

        /// <summary>
        /// Shared logger instance
        /// </summary>
        protected static readonly Logger logger = LogManager.GetCurrentClassLogger();

        /// <summary>
        /// Gets opened connection to LotFinder database
        /// </summary>
        /// <returns>Open SqlConnection to LotFinder database</returns>
        public static SqlConnection GetConnection()
        {
            SqlConnection connection = null;

            try
            {
                //Create and open connection to LotFinder database
                connection = new SqlConnection(Config.LotFinderDBCS);
                connection.Open();
            }
            catch (Exception error)
            {
                //Log error and forward
                logger.Error("GetConnection: failed to open connection to DB: {0}.", error.Message);
                throw new Exception("LotFinderDB: failed to open connection to database.", error);
            }

            return connection;
        }

        /// <summary>
        /// SQL to rebuild all indices on table with fillfactor of 95
        /// </summary>
        private const string SQL_REBUILD_INDICES = "ALTER INDEX ALL ON {0} REBUILD WITH (FILLFACTOR = 95);";

        /// <summary>
        /// Rebuilds all the indices on the given table with fillfactor of 95
        /// </summary>
        /// <param name="tableName">Name of table to rebuild indices on</param>
        public static void RebuildIndices(string tableName)
        {
            using (SqlConnection connection = GetConnection())
            {
                connection.Execute(string.Format(SQL_REBUILD_INDICES, tableName), commandTimeout: DEFAULT_TIMEOUT);
            }
        }

        /// <summary>
        /// Generates table-valued parameter data table
        /// </summary>
        /// <typeparam name="T">Key data type</typeparam>
        /// <param name="keys">Keys to store in table</param>
        /// <returns>Populated table-value parameter data table</returns>
        public static DataTable GenerateTableParameter<T>(List<T> keys)
        {
            DataTable dataTable = new DataTable();
            dataTable.Columns.Add("Key", typeof(T));
            foreach (T key in keys)
            {
                dataTable.Rows.Add(key);
            }

            return dataTable;
        }

        private const string SQL_GET_LAST_DATA_UPDATES = @"
            WITH DU_CTE AS (
                SELECT du.*,
                       ROW_NUMBER() OVER (PARTITION BY du.TableName, du.UpdateType ORDER BY du.StartDT DESC) RN
                FROM   dbo.DataUpdate AS du
            )
            SELECT  cte.SourceSystem,
                    cte.SourceData,
                    cte.TableName,
                    cte.StartDT,
                    cte.EndDT,
                    cte.UpdateType,
                    cte.WasSuccessful,
                    cte.NumberRecords
            FROM    DU_CTE cte
            WHERE   cte.RN = 1";

        public static List<DataUpdate> GetLastDataUpdates()
        {
            List<DataUpdate> dataUpdates = new List<DataUpdate>();

            using (SqlConnection connection = GetConnection())
            {
                dataUpdates.AddRange(connection.Query<DataUpdate>(SQL_GET_LAST_DATA_UPDATES));
            }

            return dataUpdates;
        }

        private const string SQL_GET_TABLE_COLUMNS = @"
            SELECT  c.name AS Name,
                    CASE t2.name
                      WHEN 'varchar' THEN 'VARCHAR(' + CAST(c.max_length AS VARCHAR(10)) + ')'
                      WHEN 'decimal' THEN 'DECIMAL(' + CAST(c.precision AS VARCHAR(4)) + ',' + CAST(c.scale AS VARCHAR(4)) + ')'
                      ELSE UPPER(t2.name)
                    END AS Definition
            FROM    sys.columns c INNER JOIN 
                    sys.types AS t2 ON (c.system_type_id = t2.system_type_id) INNER JOIN 
                    sys.tables t ON (c.object_id = t.object_id)
            WHERE   t.name = @name
            ORDER BY c.column_id";

        private const string SQL_GET_TABLE_PRIMARY_KEY = @"
            SELECT  COLUMN_NAME AS Name
            FROM    INFORMATION_SCHEMA.KEY_COLUMN_USAGE
            WHERE   OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_SCHEMA + '.' + QUOTENAME(CONSTRAINT_NAME)), 'IsPrimaryKey') = 1 AND
                    TABLE_NAME = @name
            ORDER BY ORDINAL_POSITION";

        public static TableSpec GetTableSpec(string name)
        {
            TableSpec tableSpec = new TableSpec() { Name = name };

            using (SqlConnection connection = GetConnection())
            {
                //Load columns
                tableSpec.Columns.AddRange(connection.Query<ColumnSpec>(SQL_GET_TABLE_COLUMNS, new { name }));

                //Load primary key
                tableSpec.PrimaryKey.AddRange(connection.Query<string>(SQL_GET_TABLE_PRIMARY_KEY, new { name }).Select(cn => tableSpec.GetColumn(cn)));
            }

            return tableSpec;
        }
    }
}