DP44/Common/DTS.Common.Storage/LocalOnlyOperations.cs

using System;
using System.Data;
// ReSharper disable ConditionIsAlwaysTrueOrFalse
using System.Data.SqlClient;

namespace DTS.Common.Storage
{
    // this class is just to access the local db while the remote db is still connected
    public class LocalOnlyOperations
    {
        public const string BeginStatement = "BEGIN TRAN;";

        public const string CommitStatement = "COMMIT TRAN;";

        public const bool UsingNtlmAuthentication = true;

        public static void CreateParam(IDbCommand icmd, string name, SqlDbType type, object value)
        {
            switch (type)
            {
                case SqlDbType.DateTime:
                    if (null == value)
                    {
                        value = (DateTime)System.Data.SqlTypes.SqlDateTime.MinValue;
                    }
                    else if (value is DateTime)
                    {
                        if ((DateTime)value < (DateTime)System.Data.SqlTypes.SqlDateTime.MinValue)
                        {
                            value = (DateTime)System.Data.SqlTypes.SqlDateTime.MinValue;
                        }
                        value = $"{(DateTime)value:yyyy-MM-dd} {((DateTime)value).ToString("HH:mm:ss")}";
                    }
                    break;
                case SqlDbType.NVarChar:

                    break;
            }
            var param = new SqlParameter(name, type);
            param.Value = value;
            icmd.Parameters.Add(param);
        }

        public static IDbCommand GetCommand()
        {
            return GetSQLCommand();
        }

        public static SqlCommand cmd { get; set; } = null;

        public static SqlCommand GetSQLCommand()
        {
            return GetSQLCommand(false);
        }

        public static SqlCommand GetSQLCommand(bool newCommand)
        {
            if (cmd == null) { cmd = new SqlCommand(); }
            var currentCmd = cmd;
            if (newCommand) { currentCmd = new SqlCommand(); }

            if (currentCmd.Connection == null || currentCmd.Connection.State != ConnectionState.Open)
            {
                currentCmd.Connection = new SqlConnection(Connection.GetLocalConnectionString());
                currentCmd.Connection.Open();
            }

            currentCmd.Parameters.Clear();
            return currentCmd;
        }
        public static IDbCommand GetSQLOnlyCommand()
        {
            return new SqlCommand();
        }
        private string _localConnection = null;

        public void ResetLocalConnectionString()
        {
            lock (DbLock)
            {
                _localConnection = null;
            }
        }

        public string GetLocalConnectionString()
        {
            lock (DbLock)
            {
                if (null != _localConnection) return _localConnection;
                if (null == Server)
                {
                    throw new Exception("db connection not initialized");
                }
                _localConnection = $"Server={Server};Database={DbName};Trusted_Connection=TRUE;";
            }
            return _localConnection;
        }

        public string Server { get; set; } = null;

        public string DbName { get; set; } = null;

        public string Username { get; set; } = "";

        public string Password { get; set; }

        private static LocalOnlyOperations _dbOperations = null;
        private static readonly object DbLock = new object();
        public static LocalOnlyOperations Connection
        {
            get
            {
                lock (DbLock)
                {
                    if (null == _dbOperations) { _dbOperations = new LocalOnlyOperations(); }
                }
                return _dbOperations;
            }
        }

        protected LocalOnlyOperations() { }

        public const string NoConnection = "NoConnection";

        public int ExecuteCommand(IDbCommand icmd)
        {
            var cmd = icmd as SqlCommand;
            using (var msSqlConnection = new SqlConnection(GetLocalConnectionString()))
            {
                try
                {
                    msSqlConnection.Open();
                    cmd.Connection = msSqlConnection;
                }
                catch (Exception ex)
                {
                    throw new SystemException(NoConnection, ex);
                }
                if (cmd != null)
                {
                    cmd.Connection = msSqlConnection;
                }
                return cmd.ExecuteNonQuery();
            }
        }
    }

}