Example #1
0
        /// <summary>
        /// Löscht die Temporären Tabellen
        /// </summary>
        internal static void DeleteTempTables()
        {
            string sql = $"DROP TABLE IF EXISTS {TBL_tmp_AD_Computers}; " +
                         $"DROP TABLE IF EXISTS {TBL_tmp_AD_Groups}; " +
                         $"DROP TABLE IF EXISTS {TBL_tmp_AD_UserInGroup}; " +
                         $"DROP TABLE IF EXISTS {TBL_tmp_AD_Users}; " +
                         $"DROP TABLE IF EXISTS {TBL_tmp_FS_ACEs}; " +
                         $"DROP TABLE IF EXISTS {TBL_tmp_FS_ACLs}; " +
                         $"DROP TABLE IF EXISTS {TBL_tmp_FS_Dirs}; " +
                         $"DROP TABLE IF EXISTS {TBL_tmp_FS_Shares}; ";

            var mssql = new MsSql();

            mssql.Open();

            SqlCommand cmd = new SqlCommand(sql, mssql.Con);

            cmd.ExecuteNonQuery();
        }
Example #2
0
        /// <summary>
        /// Löscht den Inhalt der live Tabelle, kopiert den Inhalt der temp Tabelle zur live Tabelle und löscht die temp Tabelle
        /// </summary>
        internal static void WriteTempToLive()
        {
            //CreateLiveTables();

            var mssql = new MsSql();

            mssql.Open();

            #region ADUsers
            SqlCommand cmd = new SqlCommand($"SELECT CASE WHEN OBJECT_ID('{TBL_tmp_AD_Users}', 'U') IS NOT NULL THEN 1 ELSE 0 END", mssql.Con);

            if ((int)cmd.ExecuteScalar() == 1)
            {
                string sql = $"TRUNCATE TABLE {TBL_AD_Users}; " +
                             $"INSERT INTO {TBL_AD_Users} SELECT * FROM {TBL_tmp_AD_Users}; " +
                             $"DROP TABLE IF EXISTS {TBL_tmp_AD_Users}; ";

                cmd = new SqlCommand(sql, mssql.Con);
                cmd.ExecuteNonQuery();
            }
            else
            {
                Log.writeLine("Temp Users existiert nicht");
            }
            #endregion

            #region ADUserInGroup
            cmd = new SqlCommand($"SELECT CASE WHEN OBJECT_ID('{TBL_tmp_AD_UserInGroup}', 'U') IS NOT NULL THEN 1 ELSE 0 END", mssql.Con);

            if ((int)cmd.ExecuteScalar() == 1)
            {
                string sql = $"TRUNCATE TABLE {TBL_AD_UserInGroup}; " +
                             $"INSERT INTO {TBL_AD_UserInGroup} SELECT * FROM {TBL_tmp_AD_UserInGroup}; " +
                             $"DROP TABLE IF EXISTS {TBL_tmp_AD_UserInGroup}; ";

                cmd = new SqlCommand(sql, mssql.Con);
                cmd.ExecuteNonQuery();
            }
            else
            {
                Log.writeLine("Temp UsersInGroup existiert nicht");
            }
            #endregion

            #region ADGroups
            cmd = new SqlCommand($"SELECT CASE WHEN OBJECT_ID('{TBL_tmp_AD_Groups}', 'U') IS NOT NULL THEN 1 ELSE 0 END", mssql.Con);

            if ((int)cmd.ExecuteScalar() == 1)
            {
                string sql = $"TRUNCATE TABLE {TBL_AD_Groups}; " +
                             $"INSERT INTO {TBL_AD_Groups} SELECT * FROM {TBL_tmp_AD_Groups}; " +
                             $"DROP TABLE IF EXISTS {TBL_tmp_AD_Groups}; ";

                cmd = new SqlCommand(sql, mssql.Con);
                cmd.ExecuteNonQuery();
            }
            else
            {
                Log.writeLine("Temp Groups existiert nicht");
            }
            #endregion

            #region ADComputers
            cmd = new SqlCommand($"SELECT CASE WHEN OBJECT_ID('{TBL_tmp_AD_Computers}', 'U') IS NOT NULL THEN 1 ELSE 0 END", mssql.Con);

            if ((int)cmd.ExecuteScalar() == 1)
            {
                string sql = $"TRUNCATE TABLE {TBL_AD_Computers}; " +
                             $"INSERT INTO {TBL_AD_Computers} SELECT * FROM {TBL_tmp_AD_Computers}; " +
                             $"DROP TABLE IF EXISTS {TBL_tmp_AD_Computers}; ";

                cmd = new SqlCommand(sql, mssql.Con);
                cmd.ExecuteNonQuery();
            }
            else
            {
                Log.writeLine("Temp Computers existiert nicht");
            }
            #endregion

            #region FSDirs
            cmd = new SqlCommand($"SELECT CASE WHEN OBJECT_ID('{TBL_tmp_FS_Dirs}', 'U') IS NOT NULL THEN 1 ELSE 0 END", mssql.Con);

            if ((int)cmd.ExecuteScalar() == 1)
            {
                string sql = $"TRUNCATE TABLE {TBL_FS_Dirs}; " +
                             $"INSERT INTO {TBL_FS_Dirs} SELECT * FROM {TBL_tmp_FS_Dirs}; " +
                             $"DROP TABLE IF EXISTS {TBL_tmp_FS_Dirs}; ";

                cmd = new SqlCommand(sql, mssql.Con);
                cmd.ExecuteNonQuery();
            }
            else
            {
                Log.writeLine("Temp Dirs existiert nicht");
            }
            #endregion

            #region FSShares
            cmd = new SqlCommand($"SELECT CASE WHEN OBJECT_ID('{TBL_tmp_FS_Shares}', 'U') IS NOT NULL THEN 1 ELSE 0 END", mssql.Con);

            if ((int)cmd.ExecuteScalar() == 1)
            {
                string sql = $"TRUNCATE TABLE {TBL_FS_Shares}; " +
                             $"INSERT INTO {TBL_FS_Shares} SELECT * FROM {TBL_tmp_FS_Shares}; " +
                             $"DROP TABLE IF EXISTS {TBL_tmp_FS_Shares}; ";

                cmd = new SqlCommand(sql, mssql.Con);
                cmd.ExecuteNonQuery();
            }
            else
            {
                Log.writeLine("Temp Shares existiert nicht");
            }
            #endregion

            #region FSACLs
            cmd = new SqlCommand($"SELECT CASE WHEN OBJECT_ID('{TBL_tmp_FS_ACLs}', 'U') IS NOT NULL THEN 1 ELSE 0 END", mssql.Con);

            if ((int)cmd.ExecuteScalar() == 1)
            {
                string sql = $"TRUNCATE TABLE {TBL_FS_ACLs}; " +
                             $"INSERT INTO {TBL_FS_ACLs} SELECT * FROM {TBL_tmp_FS_ACLs}; " +
                             $"DROP TABLE IF EXISTS {TBL_tmp_FS_ACLs}; ";

                cmd = new SqlCommand(sql, mssql.Con);
                cmd.ExecuteNonQuery();
            }
            else
            {
                Log.writeLine("Temp ACLs existiert nicht");
            }
            #endregion

            #region FSACEs
            cmd = new SqlCommand($"SELECT CASE WHEN OBJECT_ID('{TBL_tmp_FS_ACEs}', 'U') IS NOT NULL THEN 1 ELSE 0 END", mssql.Con);

            if ((int)cmd.ExecuteScalar() == 1)
            {
                string sql = $"TRUNCATE TABLE {TBL_FS_ACEs}; " +
                             $"INSERT INTO {TBL_FS_ACEs} SELECT * FROM {TBL_tmp_FS_ACEs}; " +
                             $"DROP TABLE IF EXISTS {TBL_tmp_FS_ACEs}; ";

                cmd = new SqlCommand(sql, mssql.Con);
                cmd.ExecuteNonQuery();
            }
            else
            {
                Log.writeLine("Temp ACEs existiert nicht");
            }
            #endregion

            mssql.Close();
            Log.writeLine("Alle Datenbanken ins live System übertragen");
        }
Example #3
0
        /// <summary>
        /// Erstellt die Tabellen für die Live Version (Wird nur beim ersten ausführen benutzt)
        /// </summary>
        internal static void CreateLiveTables()
        {
            #region SQL Befehl
            string sql = $"CREATE TABLE {TBL_AD_Computers} " +
                         $"( " +
                         $"	[SID] [nvarchar](100) NOT NULL, " +
                         $"	[Name] [nvarchar](100) NULL, " +
                         $"	[SamAccountName] [nvarchar](100) NULL, " +
                         $"	[DistinguishedName] [nvarchar](300) NULL, " +
                         $"	[DisplayName] [nvarchar](100) NULL, " +
                         $"	[Description] [nchar](300) NULL, " +
                         $"	[Enabled] [smallint] NULL, " +
                         $"	[LastLogon] [datetime] NULL, " +
                         $"	[LastPasswordSet] [datetime] NULL, " +
                         $"CONSTRAINT [PK_adcomputers] PRIMARY KEY CLUSTERED  " +
                         $"	( " +
                         $"		[SID] ASC "+
                         $"	) " +
                         $"); " +
                         $" " +
                         $" " +
                         $"CREATE TABLE {TBL_AD_Groups} " +
                         $"( " +
                         $"	[SID] [nvarchar](100) NOT NULL, " +
                         $"	[SamAccountName] [nvarchar](100) NULL, " +
                         $"	[DistinguishedName] [nvarchar](300) NULL, " +
                         $"	[Name] [nvarchar](100) NULL, " +
                         $"	[Description] [nvarchar](max) NULL, " +
                         $"	[IsSecurityGroup] [smallint] NULL, " +
                         $"	[GroupScope] [nvarchar](50) NULL, " +
                         $"CONSTRAINT [PK_adgroups] PRIMARY KEY CLUSTERED  " +
                         $"	( " +
                         $"		[SID] ASC "+
                         $"	) " +
                         $"); " +
                         $" " +
                         $" " +
                         $"CREATE TABLE {TBL_AD_Users} " +
                         $"( " +
                         $"	[SID] [nvarchar](100) NOT NULL, " +
                         $"	[DisplayName] [nvarchar](100) NULL, " +
                         $"	[SamAccountName] [nvarchar](100) NULL, " +
                         $"	[DistinguishedName] [nvarchar](300) NULL, " +
                         $"	[UserPrincipalName] [nvarchar](100) NULL, " +
                         $"	[Enabled] [smallint] NOT NULL, " +
                         $" CONSTRAINT [PK_adusers] PRIMARY KEY CLUSTERED  " +
                         $"	( " +
                         $"		[SID] ASC "+
                         $"	) " +
                         $"); " +
                         $" " +
                         $" " +
                         $"CREATE TABLE {TBL_AD_UserInGroup} " +
                         $"( " +
                         $"	[grpSID] [nvarchar](100) NOT NULL, " +
                         $"	[userSID] [nvarchar](100) NOT NULL " +
                         $"); " +
                         $" " +
                         $" " +
                         $"CREATE TABLE {TBL_FS_ACEs} " +
                         $"( " +
                         $"	[_ace_id] [int] NOT NULL, " +
                         $"	[_sid] [varchar](100) NOT NULL, " +
                         $"	[_rights] [int] NOT NULL, " +
                         $"	[_type] [bit] NOT NULL, " +
                         $"	[_fsr] [varchar](500) NOT NULL, " +
                         $"	[_is_inherited] [bit] NOT NULL, " +
                         $"	[_inheritance_flags] [int] NOT NULL, " +
                         $"	[_propagation_flags] [int] NOT NULL, " +
                         $"	[_ace_hash] [varchar](50) NOT NULL, " +
                         $" CONSTRAINT [PK_aces] PRIMARY KEY CLUSTERED  " +
                         $"	( " +
                         $"		[_ace_id] ASC "+
                         $"	) " +
                         $"); " +
                         $" " +
                         $" " +
                         $"CREATE TABLE {TBL_FS_ACLs} " +
                         $"( " +
                         $"	[_path_id] [int] NOT NULL, " +
                         $"	[_ace_id] [int] NOT NULL, " +
                         $"	[_type] [int] NOT NULL, " +
                         $" CONSTRAINT [PK_acls] PRIMARY KEY CLUSTERED  " +
                         $"	( " +
                         $"		[_path_id] ASC, "+
                         $"		[_ace_id] ASC, "+
                         $"		[_type] ASC "+
                         $"	) " +
                         $"); " +
                         $" " +
                         $" " +
                         $"CREATE TABLE {TBL_FS_Dirs}( " +
                         $"	[_path_id] [int] NOT NULL, " +
                         $"	[_path_name] [nvarchar](max) NOT NULL, " +
                         $"	[_owner_sid] [nvarchar](100) NOT NULL, " +
                         $"	[_parent_path_id] [int] NOT NULL, " +
                         $"	[_is_root] [bit] NOT NULL, " +
                         $"	[_has_children] [bit] NOT NULL, " +
                         $"	[_scan_deepth] [int] NOT NULL, " +
                         $"	[_size] [bigint] DEFAULT 0, " +
                         $"	[_path_hash] [nchar](40) NOT NULL, " +
                         $" CONSTRAINT [PK_path_name] PRIMARY KEY CLUSTERED  " +
                         $"	( " +
                         $"		[_path_id] ASC "+
                         $"	), " +
                         $" CONSTRAINT [IX_path_hash] UNIQUE NONCLUSTERED  " +
                         $"	( " +
                         $"		[_path_hash] ASC "+
                         $"	) " +
                         $"); " +
                         $" " +
                         $" " +
                         $"CREATE TABLE {TBL_FS_Shares} ( " +
                         $"	[_path_id] [int] NOT NULL, " +
                         $"	[_unc_path_name] [nvarchar](max) NOT NULL, " +
                         $"	[_owner_sid] [nvarchar](100) NOT NULL, " +
                         $"	[_has_children] [bit] NOT NULL, " +
                         $"	[_size] [bigint] NOT NULL, " +
                         $"	[_path_hash] [nchar](40) NOT NULL, " +
                         $"	[_path_name] [nvarchar](max) NULL, " +
                         $"	[_display_name] [nvarchar](50) NULL, " +
                         $"	[_remark] [nvarchar](300) NULL, " +
                         $"	[_share_type] [nchar](30) NOT NULL, " +
                         $"	[_hidden] [bit] NOT NULL, " +
                         $" CONSTRAINT [PK_shares] PRIMARY KEY CLUSTERED  " +
                         $"	( " +
                         $"		[_path_id] ASC "+
                         $"	) " +
                         $"); ";
            #endregion


            var mssql = new MsSql();
            mssql.Open();

            SqlCommand cmd = new SqlCommand(sql, mssql.Con);
            cmd.ExecuteNonQuery();
        }