path: root/Software/Visual_Studio/Tango.Core/DB/DbManager.cs

using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Tango.Core.DB
{
    public class DbManager : IDisposable
    {
        private static Random random = new Random();
        private SqlConnection _connection;
        private const int COMMAND_TIMEOUT_SECONDS = 120;

        #region Constructors

        private DbManager(SqlConnection connection)
        {
            _connection = connection;
            _connection.Open();
        }

        public static DbManager FromConnection(SqlConnection connection)
        {
            return new DbManager(connection);
        }

        public static DbManager FromConnectionString(String connectionString)
        {
            return new DbManager(new SqlConnection(connectionString));
        }

        public static DbManager FromAddress(String address)
        {
            return new DbManager(new SqlConnection(String.Format("Server={0};Integrated security=SSPI", address)));
        }

        public static DbManager FromAddressAndName(String address, String database)
        {
            return new DbManager(new SqlConnection(String.Format("Server={0};Integrated security=SSPI;Initial Catalog={1}", address, database)));
        }

        public static DbManager FromDataSource(DataSource dataSource)
        {
            return new DbManager(dataSource.ToConnection() as SqlConnection);
        }

        public static DbManager FromCredentials(String address, String database, String userName, String password)
        {
            return new DbManager(new SqlConnection(String.Format("Server={0};Initial Catalog={1};User Id={2};Password={3}", address, database, userName, password)));
        }

        #endregion

        #region Private Methods

        public string GetRandomString(int length)
        {
            const string chars = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
            return "TEMP_" + new string(Enumerable.Repeat(chars, length).Select(s => s[random.Next(s.Length)]).ToArray());
        }

        #endregion

        #region Public Methods

        public void Create(String name, String filePath = null)
        {
            String command = String.Format("CREATE DATABASE {0}", name);

            if (filePath != null)
            {
                command = $"CREATE DATABASE {name} ON (name='{name}', filename='{filePath}')";
            }

            SqlCommand cmd = new SqlCommand(command, _connection);
            cmd.ExecuteNonQuery();
        }

        public Task ExecuteCommandAsync(String command)
        {
            return Task.Factory.StartNew(() =>
            {
                SqlCommand cmd = new SqlCommand(command, _connection);
                cmd.ExecuteNonQuery();
            });
        }

        public bool Exists(String name)
        {
            try
            {
                String command = String.Format("SELECT database_id FROM sys.databases WHERE Name = '{0}'", name);
                SqlCommand cmd = new SqlCommand(command, _connection);
                object resultObj = cmd.ExecuteScalar();

                int databaseID = 0;

                if (resultObj != null)
                {
                    int.TryParse(resultObj.ToString(), out databaseID);
                }

                return (databaseID > 0);
            }
            catch (Exception e)
            {
                return false;
            }
        }

        public void Delete(String name)
        {
            String command = String.Format("DROP DATABASE {0}", name);
            SqlCommand cmd = new SqlCommand(command, _connection);
            cmd.ExecuteNonQuery();
        }

        public void SetOffline(String name)
        {
            String command = String.Format("ALTER DATABASE {0} SET OFFLINE WITH ROLLBACK IMMEDIATE", name);
            SqlCommand cmd = new SqlCommand(command, _connection);
            cmd.ExecuteNonQuery();
        }

        public void SetOnline(String name)
        {
            String command = String.Format("ALTER DATABASE {0} SET ONLINE", name);
            SqlCommand cmd = new SqlCommand(command, _connection);
            cmd.ExecuteNonQuery();
        }

        public void CloneDB(String sourceDb, String backupPath, String targetDb, String targetMdf, String targetLdf)
        {
            String command = String.Format("BACKUP DATABASE {0} TO DISK = '{1}' WITH INIT", sourceDb, backupPath);
            SqlCommand cmd = new SqlCommand(command, _connection);
            cmd.CommandTimeout = COMMAND_TIMEOUT_SECONDS;
            cmd.ExecuteNonQuery();

            command = String.Format("RESTORE FILELISTONLY FROM DISK='{0}'", backupPath);
            cmd = new SqlCommand(command, _connection);
            cmd.CommandTimeout = COMMAND_TIMEOUT_SECONDS;

            List<String> logical_names = new List<string>();

            using (var reader = cmd.ExecuteReader())
            {
                while (reader.Read())
                {
                    logical_names.Add(reader.GetString(reader.GetOrdinal("LogicalName")));
                }
            }

            command = String.Format("RESTORE DATABASE {0} FROM DISK='{1}' WITH MOVE '{2}' TO '{3}', MOVE '{4}' TO '{5}'", targetDb, backupPath, logical_names[0], targetMdf, logical_names[1], targetLdf);
            cmd = new SqlCommand(command, _connection);
            cmd.CommandTimeout = COMMAND_TIMEOUT_SECONDS;
            cmd.ExecuteNonQuery();

            File.Delete(backupPath);
        }

        public void Backup(String name, String file)
        {
            String command = $"BACKUP DATABASE {name} TO DISK = '{file}'";
            SqlCommand cmd = new SqlCommand(command, _connection);
            cmd.CommandTimeout = COMMAND_TIMEOUT_SECONDS;
            cmd.ExecuteNonQuery();
        }

        public void Restore(String name, String file)
        {
            SetOffline(name);

            String command = $"RESTORE DATABASE {name} FROM DISK = '{file}'";
            SqlCommand cmd = new SqlCommand(command, _connection);
            cmd.CommandTimeout = COMMAND_TIMEOUT_SECONDS;
            cmd.ExecuteNonQuery();

            SetOnline(name);
        }

        public void RestoreAsNew(String name, String file, String dbFolder)
        {
            String command = $"RESTORE DATABASE {name} FROM DISK='{file}' WITH MOVE '{name}' TO '{Path.Combine(dbFolder, name)}.mdf', MOVE '{name}_log' TO '{Path.Combine(dbFolder, name)}.ldf'";
            SqlCommand cmd = new SqlCommand(command, _connection);
            cmd.CommandTimeout = COMMAND_TIMEOUT_SECONDS;
            cmd.ExecuteNonQuery();
        }

        public void ClearDb()
        {
            if (!_connection.ConnectionString.ToLower().Contains("initial catalog"))
            {
                throw new InvalidOperationException("Clear db can only be used when 'initial catalog' is specified at the connection string.");
            }

            SqlCommand cmd = new SqlCommand("EXEC sys.sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'", _connection);
            cmd.ExecuteNonQuery();

            cmd = new SqlCommand("EXEC sys.sp_msforeachtable 'SET QUOTED_IDENTIFIER ON; DELETE FROM ?'", _connection);
            cmd.ExecuteNonQuery();

            cmd = new SqlCommand("EXEC sys.sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'", _connection);
            cmd.ExecuteNonQuery();
        }

        public void DropAllTables()
        {
            if (!_connection.ConnectionString.ToLower().Contains("initial catalog"))
            {
                throw new InvalidOperationException("Clear db can only be used when 'initial catalog' is specified at the connection string.");
            }

            SqlCommand cmd = new SqlCommand(@"USE TANGO
DECLARE @sql NVARCHAR(2000)

WHILE(EXISTS(SELECT 1 from INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE='FOREIGN KEY'))
BEGIN
    SELECT TOP 1 @sql=('ALTER TABLE ' + TABLE_SCHEMA + '.[' + TABLE_NAME + '] DROP CONSTRAINT [' + CONSTRAINT_NAME + ']')
    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
    WHERE CONSTRAINT_TYPE = 'FOREIGN KEY'
    EXEC(@sql)
    PRINT @sql
END

WHILE(EXISTS(SELECT * from INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME != '__MigrationHistory' AND TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME != 'database_firewall_rules'))
BEGIN
    SELECT TOP 1 @sql=('DROP TABLE ' + TABLE_SCHEMA + '.[' + TABLE_NAME + ']')
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_NAME != '__MigrationHistory' AND TABLE_NAME != 'database_firewall_rules'
    EXEC(@sql)
    PRINT @sql
END", _connection);

            cmd.ExecuteNonQuery();
        }

        public void CreateLoginAndUser(String userName, String password, String database)
        {

            SqlCommand cmd = new SqlCommand(String.Format(

                @"

CREATE LOGIN {0} 
WITH 
PASSWORD = '{1}',
DEFAULT_DATABASE=[{2}],
CHECK_EXPIRATION=OFF,
CHECK_POLICY=OFF

USE {2}
CREATE USER {0} FOR LOGIN {0}

EXEC sp_addrolemember 'db_datareader', '{0}';

", userName, password, database),

                _connection);
            cmd.ExecuteNonQuery();
        }

        public void DeleteLoginAndUser(String userName, String database)
        {
            SqlCommand cmd = new SqlCommand(String.Format(

    @"

USE {1}

DECLARE @loginNameToDrop sysname
DECLARE @userNameToDrop sysname
SET @loginNameToDrop = '{0}';
SET @userNameToDrop = '{0}';

DECLARE sessionsToKill CURSOR FAST_FORWARD FOR
    SELECT session_id
    FROM sys.dm_exec_sessions
    WHERE login_name = @loginNameToDrop
OPEN sessionsToKill

DECLARE @sessionId INT
DECLARE @statement NVARCHAR(200)

FETCH NEXT FROM sessionsToKill INTO @sessionId

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT 'Killing session ' + CAST(@sessionId AS NVARCHAR(20)) + ' for login ' + @loginNameToDrop

    SET @statement = 'KILL ' + CAST(@sessionId AS NVARCHAR(20))
    EXEC sp_executesql @statement

    FETCH NEXT FROM sessionsToKill INTO @sessionId
END

CLOSE sessionsToKill
DEALLOCATE sessionsToKill

PRINT 'Dropping user ' + @userNameToDrop
SET @statement = 'DROP USER [' + @userNameToDrop + ']'
EXEC sp_executesql @statement

PRINT 'Dropping login ' + @loginNameToDrop
SET @statement = 'DROP LOGIN [' + @loginNameToDrop + ']'
EXEC sp_executesql @statement

", userName, database),

    _connection);
            cmd.ExecuteNonQuery();
        }

        public DbCredentials CreateRandomLoginAndUser(String database)
        {
            DbCredentials cred = new DbCredentials()
            {
                UserName = GetRandomString(36),
                Password = GetRandomString(36)
            };

            CreateLoginAndUser(cred.UserName, cred.Password, database);
            return cred;
        }

        public String GetValue(String query, String columnName)
        {
            string sql = query;
            var cm = new SqlCommand(sql, _connection);
            var dr = cm.ExecuteReader();
            if (dr.Read())
            {
                var value = dr[columnName];

                if (value != null)
                {
                    return value.ToString();
                }
            }

            return null;
        }

        #endregion

        #region IDisposable

        public void Dispose()
        {
            _connection.Dispose();
        }

        #endregion
    }
}