bo/Console/Commands/Base/DbBaseCommand.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using Quadarax.Foundation.Core.Data.Extensions;
using Quadarax.Foundation.Core.QConsole;
using Quadarax.Foundation.Core.QConsole.Argument;
using Quadarax.Foundation.Core.QConsole.Value;

namespace BO.Console.Commands.Base
{
    internal abstract class DbBaseCommand : BaseLocalCommand
    {

        #region *** Constants ***
        private const string ARG_SERVER_CODE = "srv";
        private const string ARG_SERVER_HINT = "database_server";
        private const string ARG_SERVER_DESC = "Sql Server name and/or instance name";
        private const string ARG_DB_CODE = "db";
        private const string ARG_DB_HINT = "database_name";
        private const string ARG_DB_DESC = "Database name";
        private const string ARG_ISEC_CODE = "isec";
        private const string ARG_ISEC_HINT = "is_trusted_security";
        private const string ARG_ISEC_DESC = "Flag if trusted security, otherwise user credentials must be specified";
        private const string ARG_USER_CODE = "u";
        private const string ARG_USER_HINT = "user_name";
        private const string ARG_USER_DESC = "User name to connect to databse";
        private const string ARG_PWD_CODE = "p";
        private const string ARG_PWD_HINT = "user_password";
        private const string ARG_PWD_DESC = "User password to connect to databse";
        #endregion

        #region *** Properties ***
        protected SqlConnectionStringBuilder ConnectionString => GetConnectionString();
        protected string ServerName => GetArgumentValueOrDefault<string>(ARG_SERVER_CODE);
        protected string DatabseName => GetArgumentValueOrDefault<string>(ARG_DB_CODE);
        #endregion


        #region *** Constructors ***
        protected DbBaseCommand(Engine engine) : base(engine)
        {
        }
        #endregion

        #region *** Overrides ***
        protected override IEnumerable<AbstractArgument> OnSetupArguments()
        {
            var arguments =  new List<AbstractArgument>(base.OnSetupArguments());
            arguments.Add(new NamedArgument(ARG_SERVER_CODE, ARG_SERVER_DESC, ARG_SERVER_HINT, TypeValuesEnum.String,string.Empty,true));
            arguments.Add(new NamedArgument(ARG_DB_CODE, ARG_DB_DESC, ARG_DB_HINT, TypeValuesEnum.String,string.Empty,true));
            arguments.Add(new FlagArgument(ARG_ISEC_CODE, ARG_ISEC_DESC, ARG_ISEC_HINT, false));
            arguments.Add(new NamedArgument(ARG_USER_CODE, ARG_USER_DESC, ARG_USER_HINT, TypeValuesEnum.String,string.Empty,false));
            arguments.Add(new NamedArgument(ARG_PWD_CODE, ARG_PWD_DESC, ARG_PWD_HINT, TypeValuesEnum.String,string.Empty,false));
            return arguments;
        }

        protected override void OnValidateArguments()
        {
            base.OnCommandInit();
            if (!GetArgumentValueOrDefault(ARG_ISEC_CODE, false))
            {
                if (!GetArgument(ARG_USER_CODE).Value.HasValue)
                    throw new ArgumentException($"Argument '{ARG_USER_CODE}' must be set with flag '{ARG_ISEC_CODE}'.", ARG_USER_CODE);
                if (!GetArgument(ARG_PWD_CODE).Value.HasValue)
                    throw new ArgumentException($"Argument '{ARG_PWD_CODE}' must be set with flag '{ARG_ISEC_CODE}'.", ARG_PWD_CODE);
            }

        }
        #endregion

        #region *** Protected Operations ***
        protected SqlConnection OpenConnection()
        {
            WriteDebugInfo($"Opening connection [{ConnectionString.InitialCatalog}@{ConnectionString.DataSource}]...");
            var conn = new SqlConnection(ConnectionString.ToString());
            conn.Open();
            WriteInfo($"Connection to {ConnectionString.InitialCatalog}@{ConnectionString.DataSource} established.");
            return conn;
        }

        protected SqlConnection OpenMasterConnection()
        {
            var builder = ConnectionString;
            builder.InitialCatalog = "master";
            WriteDebugInfo($"Opening connection [{builder.InitialCatalog}@{builder.DataSource}]...");
            var conn = new SqlConnection(builder.ToString());
            conn.Open();
            WriteInfo($"Connection to {builder.InitialCatalog}@{builder.DataSource} established.");
            return conn;
        }

        protected virtual SqlConnectionStringBuilder GetConnectionString()
        {
            var builder = new SqlConnectionStringBuilder();
            builder.DataSource = GetArgumentValueOrDefault<string>(ARG_SERVER_CODE);
            builder.InitialCatalog = GetArgumentValueOrDefault<string>(ARG_DB_CODE);
            builder.IntegratedSecurity = GetArgumentValueOrDefault<bool>(ARG_ISEC_CODE);
            if (!builder.IntegratedSecurity)
            {
                builder.UserID = GetArgumentValueOrDefault<string>(ARG_USER_CODE);
                builder.Password = GetArgumentValueOrDefault<string>(ARG_PWD_CODE);
            }
            return builder;
        }

        protected bool CheckIfDbExists(SqlConnection conn,string databaseName)
        {
            var query = $"select count(*) from master.dbo.sysdatabases WHERE ('[' + name + ']' = '{databaseName}' OR name = '{databaseName}')";
            return conn.ExecuteScalar<int>(CommandType.Text, query) == 1;
        }
        #endregion

        #region *** Private Operations ***
        #endregion
    }

}