DP44/Common/DTS.Common.Storage/DatabaseServices.cs

using System.IO;
using DTS.Common.Utilities.Logging;
using System;
using DbAPI.Errors;

namespace DTS.Common.Storage
{
    public abstract class DatabaseServices
    {
        public static void RestoreLocalDatabase(string defaultDbName)
        {
            LocalOnlyOperations.Connection.ResetLocalConnectionString();

            Utils.Database.StopInstance(INSTANCE_NAME, APILogger.Log);

            var dbFileNameSource = Path.Combine(Environment.CurrentDirectory, LOCAL_DB_FOLDER, defaultDbName) + ".mdf";
            var logFileNameSource = Path.Combine(Environment.CurrentDirectory, LOCAL_DB_FOLDER, defaultDbName) + "_log.ldf";

            var dbFileNameBak = Path.Combine(Environment.CurrentDirectory, LOCAL_DB_FOLDER, defaultDbName) + ".BAK";
            var logFileNameBak = Path.Combine(Environment.CurrentDirectory, LOCAL_DB_FOLDER, defaultDbName) + "_log.BAK";

            if (!File.Exists(logFileNameBak) || !File.Exists(dbFileNameBak))
            {
                throw new Utils.Database.SqlServerLocalDbException(Utils.Database.SqlServerLocalDbException.Errors.LocalDbDoesntExist);
            }

            if (File.Exists(dbFileNameSource))
            {
                Utils.FileUtils.DeleteFileOrMove(dbFileNameSource, APILogger.Log);
            }

            if (File.Exists(logFileNameSource))
            {
                Utils.FileUtils.DeleteFileOrMove(logFileNameSource, APILogger.Log);
            }

            File.Copy(dbFileNameBak, dbFileNameSource);
            File.Copy(logFileNameBak, logFileNameSource);

            LocalOnlyOperations.Connection.Server = LOCAL_SERVER;

            Utils.Database.StartInstance(INSTANCE_NAME, APILogger.Log);

            LocalOnlyOperations.Connection.DbName = defaultDbName;
        }


        public static void BackupLocalDatabase(string defaultDbName)
        {
            LocalOnlyOperations.Connection.ResetLocalConnectionString();
            Utils.Database.StopInstance(INSTANCE_NAME, APILogger.Log);

            var dbFileNameSource = Path.Combine(Environment.CurrentDirectory, LOCAL_DB_FOLDER, defaultDbName) + ".mdf";
            var logFileNameSource = Path.Combine(Environment.CurrentDirectory, LOCAL_DB_FOLDER, defaultDbName) + "_log.ldf";

            var dbFileNameBak = Path.Combine(Environment.CurrentDirectory, LOCAL_DB_FOLDER, defaultDbName) + ".BAK";
            var logFileNameBak = Path.Combine(Environment.CurrentDirectory, LOCAL_DB_FOLDER, defaultDbName) + "_log.BAK";

            if (File.Exists(dbFileNameBak))
            {
                Utils.FileUtils.DeleteFileOrMove(dbFileNameBak, APILogger.Log);
            }

            if (File.Exists(logFileNameBak))
            {
                Utils.FileUtils.DeleteFileOrMove(logFileNameBak, APILogger.Log);
            }

            if (!File.Exists(dbFileNameSource))
            {
                throw new FileNotFoundException(dbFileNameSource);
            }

            if (!File.Exists(logFileNameSource))
            {
                throw new FileNotFoundException(logFileNameSource);
            }

            File.Copy(dbFileNameSource, dbFileNameBak);
            File.Copy(logFileNameSource, logFileNameBak);

            LocalOnlyOperations.Connection.Server = LOCAL_SERVER;

            Utils.Database.StartInstance(INSTANCE_NAME, APILogger.Log);

            LocalOnlyOperations.Connection.DbName = defaultDbName;
        }

        public const string INSTANCE_NAME = "DataPROInstance";
        public const string LOCAL_SERVER = @"(localdb)\DataPROInstance";
        public const string SCRIPTS_FOLDER = "SQL Server Scripts";
        public const string LOCAL_DB_FOLDER = "db";
        private const string ISO = "ISO";
        public const string ATTACH_DBS_BAT = "AttachDBs.bat";
        public static int SetupLocal(string defaultDbName)
        {
            DbOperations.Connection.ResetLocalConnectionString();
            var details = new DbAPI.Connections.ConnectionDetails();
            details.ClientDbVersion = DbOperations.CURRENT_DB_VERSION;
            details.AttachDbsBatPath = Path.Combine(SCRIPTS_FOLDER, ATTACH_DBS_BAT);
            details.DbFolderPath = Path.Combine(LOCAL_DB_FOLDER);
            details.DbName = defaultDbName;
            details.UsingCentralizedDb = false;
            details.DbServer = LOCAL_SERVER;
            details.InstanceName = INSTANCE_NAME;
            details.UseNTLMAuthentication = true;
            details.SqlDbPath = Utils.Database.GetSqlServerLocalDbPath();
            details.ODBCToolsPath = Utils.Database.GetODBCToolsPath(APILogger.Log);
            APILogger.Log($"SqlDbPath is {details.SqlDbPath}; ODBCToolsPath is {details.ODBCToolsPath}");

            DbOperations.Connection.Server = details.DbServer;
            DbOperations.Connection.DBName = details.DbName;
            DbOperations.Connection.Username = details.DbUser;
            DbOperations.Connection.Password = details.DbUserPassword;
            DbOperations._usingNTLMAuthentication = details.UseNTLMAuthentication;
            DbOperations._usingCentralizedDB = details.UsingCentralizedDb;

            var res = DbAPI.DbAPI.Connections.ConnectToDb(details);
            if (ErrorCodes.ERROR_SUCCESS != res)
            {
                throw new SystemException($"Couldn't connect to database: {details} - {res}");
            }
            var connectionDbVersion = DbOperations.GetConnectionDbVersion();
            return connectionDbVersion;
        }
        public static int SetupLocal2(string defaultDbName)
        {
            LocalOnlyOperations.Connection.Server = LOCAL_SERVER;

            var details = new DbAPI.Connections.ConnectionDetails();
            details.ClientDbVersion = DbOperations.CURRENT_DB_VERSION;
            details.AttachDbsBatPath = Path.Combine(SCRIPTS_FOLDER, ATTACH_DBS_BAT);
            details.DbFolderPath = LOCAL_DB_FOLDER;
            details.DbName = defaultDbName;
            details.UsingCentralizedDb = false;
            details.DbServer = LOCAL_SERVER;
            details.InstanceName = INSTANCE_NAME;
            details.UseNTLMAuthentication = true;
            details.SqlDbPath = Utils.Database.GetSqlServerLocalDbPath();
            details.ODBCToolsPath = Utils.Database.GetODBCToolsPath(APILogger.Log);

            LocalOnlyOperations.Connection.Server = details.DbServer;
            LocalOnlyOperations.Connection.Username = details.DbUser;
            LocalOnlyOperations.Connection.Password = details.DbUserPassword;
            LocalOnlyOperations.Connection.DbName = details.DbName;

            var res = DbAPI.DbAPI.Connections.ConnectToDb(details);
            if (ErrorCodes.ERROR_SUCCESS != res)
            {
                throw new SystemException($"Couldn't connect to database: {details} - {res}");
            }
            LocalOnlyOperations.Connection.DbName = details.DbName;

            var connectionDbVersion = DbOperations.GetConnectionDbVersion();
            return connectionDbVersion;
        }
        public static int SetupForRemoteDb(string dbHost, bool userNTLMAuthentication, string user, string password, string defaultDbName)
        {
            var connectionDbVersion = 0;

            DbOperations.Connection.ResetLocalConnectionString();
            var details = new DbAPI.Connections.ConnectionDetails();
            details.ClientDbVersion = DbOperations.CURRENT_DB_VERSION;
            details.AttachDbsBatPath = Path.Combine(SCRIPTS_FOLDER, ATTACH_DBS_BAT);
            details.DbFolderPath = LOCAL_DB_FOLDER;
            details.DbName = defaultDbName;
            details.UsingCentralizedDb = true;
            details.DbServer = dbHost;
            details.DbUser = user;
            details.DbUserPassword = password;
            details.InstanceName = INSTANCE_NAME;
            details.UseNTLMAuthentication = userNTLMAuthentication;
            details.SqlDbPath = Utils.Database.GetSqlServerLocalDbPath();
            details.ODBCToolsPath = Utils.Database.GetODBCToolsPath(APILogger.Log);

            DbOperations.Connection.Server = details.DbServer;
            DbOperations.Connection.DBName = details.DbName;
            DbOperations.Connection.Username = details.DbUser;
            DbOperations.Connection.Password = details.DbUserPassword;
            DbOperations._usingNTLMAuthentication = details.UseNTLMAuthentication;
            DbOperations._usingCentralizedDB = details.UsingCentralizedDb;

            var res = DbAPI.DbAPI.Connections.ConnectToDb(details);
            if (ErrorCodes.ERROR_SUCCESS != res)
            {
                throw new SystemException($"Couldn't connect to database: {details} - {res}");
            }
            connectionDbVersion = DbOperations.GetConnectionDbVersion();

            return connectionDbVersion;
        }

        public static bool SimpleDbTest()
        {
            try
            {
                using (var cmd = DbOperations.GetSQLCommand(true))
                {
                    cmd.CommandText = "SELECT TOP 1 [DASId] FROM [DAS]";
                    using (var ds = DbOperations.Connection.QueryDataSet(cmd))
                    {
                        if (ds.Tables[0].Rows.Count > 0) { return true; }
                    }
                    cmd.Connection.Dispose();
                }
            }
            catch (Exception)
            {
                return false;
            }
            return true;
        }
    }
}