Beispiel #1
0
        private void initSqlDatabases()
        {
            DateTime dt = DateTime.Now;

            _sqlDatabases.Clear();

            try
            {
                foreach (MySqlServerObject so in _sqlServerObjects)
                {
                    MyOutput.NewMessage(EOutputMessageType.PROGRESS, string.Format("Loading databases from server ({0}) '{1}'...", so.ServerObjectId, getDataSourceName(so.ServerObjectId)));

                    var conn = ConnectionPool.GetConnection(so.ServerObjectId);
                    if (conn != null)
                    {
                        SqlCommand cmd = (SqlCommand)ConnectionPool.GetConnection(so.ServerObjectId).CreateCommand();
                        cmd.CommandText    = QueryReader.Read("databases.sql");
                        cmd.CommandTimeout = UserSettings.Default.CommandTimeout;
                        cmd.CommandType    = CommandType.Text;
                        SqlDataReader rdr = cmd.ExecuteReader();
                        while (rdr.Read())
                        {
                            _sqlDatabases.Add(new MySqlDatabase
                            {
                                ServerObjectId     = so.ServerObjectId,
                                ServerObjectName   = so.ServerObjectName,
                                DatabaseId         = (int)rdr["database_id"],
                                DatabaseName       = (string)rdr["database_name"],
                                State              = (string)rdr["state_desc"],
                                UserHasDbAccess    = (bool)rdr["has_db_access"],
                                CompatibilityLevel = Convert.ToInt32(rdr["compatibility_level"]),
                                CollationName      = (string)rdr["collation_name"],
                                RecoveryModel      = (string)rdr["recovery_model_desc"]
                            });
                        }
                        rdr.Close();

                        MyOutput.AppendToLastMessage(EOutputMessageType.INFORMATION, string.Format("Done. Loaded {0:n0} databases.", _sqlDatabases.Count(x => x.ServerObjectId == so.ServerObjectId)), (DateTime.Now - dt).TotalSeconds);
                    }
                }
            }
            catch (Exception ex)
            {
                MyOutput.NewMessage(EOutputMessageType.ERROR, string.Format("Error loading databases: {0}", ex.Message));
                var exForm = new ExceptionForm(ex);
                exForm.ShowDialog();
            }
        }
Beispiel #2
0
        //private void initSqlExpressionDependencies(int serverObjectId, int databaseId)
        //{
        //    try
        //    {
        //        var serverObjectName = SqlServerObjects.First(so => so.ServerObjectId == serverObjectId).ServerObjectName;
        //        var databaseName = SqlDatabases.First(d => d.ServerObjectId == serverObjectId && d.DatabaseId == databaseId).DatabaseName;

        //        MyOutput.NewMessage(EOutputMessageType.PROGRESS, string.Format("Loading expression dependencies from server object '{0}', database '{1}'... ", serverObjectName, databaseName));

        //        int counter = 0;
        //        DateTime dt = DateTime.Now;

        //        string query = QueryReader.Read("expression_dependencies.sql");

        //        ConnectionPool.GetConnection(serverObjectId).ChangeDatabase(databaseName);
        //        SqlCommand cmd = (SqlCommand)ConnectionPool.GetConnection(serverObjectId).CreateCommand();
        //        cmd.CommandText = query;
        //        cmd.CommandTimeout = UserSettings.Default.CommandTimeout;
        //        cmd.CommandType = CommandType.Text;

        //        SqlDataReader rdr = cmd.ExecuteReader();
        //        while (rdr.Read())
        //        {
        //            counter++;

        //            int referencedId = -1;
        //            int.TryParse(rdr["referenced_id"].ToString(), out referencedId);

        //            _sqlExpressionDependencies.Add(new MySqlExpressionDependency
        //            {
        //                ServerObjectId = serverObjectId,
        //                ServerObjectName = serverObjectName,
        //                DatabaseId = databaseId,
        //                DatabaseName = databaseName,
        //                ReferencingId = (int)rdr["referencing_id"],
        //                ReferencedServerName = (string)rdr["referenced_server_name"],
        //                ReferencedDatabaseName = (string)rdr["referenced_database_name"],
        //                ReferencedSchemaName = (string)rdr["referenced_schema_name"],
        //                ReferencedEntityName = (string)rdr["referenced_entity_name"],
        //                ReferencedId = (referencedId != -1) ? referencedId : (int?)null
        //            });
        //        }
        //        rdr.Close();

        //        MyOutput.AppendToLastMessage(EOutputMessageType.INFORMATION, string.Format("Done. Loaded {0:n0} expression dependencies.", counter), (DateTime.Now - dt).TotalSeconds);
        //    }
        //    catch (Exception ex)
        //    {
        //        MyOutput.NewMessage(EOutputMessageType.ERROR, string.Format("Error loading expression dependencies: {0}", ex.Message));
        //        var exForm = new ExceptionForm(ex);
        //        exForm.ShowDialog();
        //    }
        //}

        private void initSqlExpressionDependencies(int serverObjectId, int databaseId)
        {
            var serverObjectName = SqlServerObjects.First(so => so.ServerObjectId == serverObjectId).ServerObjectName;
            var databaseName     = SqlDatabases.First(d => d.ServerObjectId == serverObjectId && d.DatabaseId == databaseId).DatabaseName;

            MyOutput.NewMessage(EOutputMessageType.PROGRESS, string.Format("Loading expression dependencies from server '{0}', database '{1}'... ", serverObjectName, databaseName));

            int      initialNumberOfRows = SqlExpressionDependencies.Length;
            DateTime dt = DateTime.Now;
            //DateTime timeCounterInner;

            DataTable ExpressionDependenciesDataTable = new DataTable();
            string    query = QueryReader.Read("expression_dependencies.sql");

            var svr = SqlObjects.FirstOrDefault(o => o.ServerObjectId == serverObjectId);
            var db  = SqlObjects.FirstOrDefault(o => o.ServerObjectId == serverObjectId && o.DatabaseId == databaseId);

            if (svr == null || db == null)
            {
                MyOutput.AppendToLastMessage(EOutputMessageType.INFORMATION, string.Format("Done. Loaded {0:n0} dependencies.", SqlExpressionDependencies.Length - initialNumberOfRows), (DateTime.Now - dt).TotalSeconds);
                return;
            }

            ConnectionPool.GetConnection(serverObjectId).ChangeDatabase(databaseName);
            SqlCommand cmd = ConnectionPool.GetConnection(serverObjectId).CreateCommand();

            cmd.CommandText    = query;
            cmd.CommandTimeout = UserSettings.Default.CommandTimeout;
            cmd.CommandType    = CommandType.Text;

            ExpressionDependenciesDataTable.Load(cmd.ExecuteReader());

            //var results = from myRow in dataTable.AsEnumerable()
            //              select new
            //              {
            //                  ReferencingId = Convert.ToInt32(myRow["referencing_id"]),
            //                  ReferencedId = Convert.ToInt32(((myRow["referenced_id"] != DBNull.Value) ? Convert.ToInt32(myRow["referenced_id"]) : -1)),
            //                  ReferencedSchemaId = _objectList.First(o => o.ServerName.Equals(serverName, StringComparison.InvariantCultureIgnoreCase)
            //                      && o.DatabaseName.Equals(databaseName, StringComparison.InvariantCultureIgnoreCase)
            //                      && o.SchemaName.Equals((string)myRow["referenced_schema_name"], StringComparison.InvariantCultureIgnoreCase)).SchemaId,
            //                ReferencedEntityName = (string)myRow["referenced_entity_name"]
            //              };


            var databaseDependencies = from myRow in ExpressionDependenciesDataTable.AsEnumerable()
                                       select new
            {
                ReferencingId      = Convert.ToInt32(myRow["referencing_id"]),
                ReferencedId       = Convert.ToInt32(((myRow["referenced_id"] != DBNull.Value) ? Convert.ToInt32(myRow["referenced_id"]) : -1)),
                ReferencedSchemaId = (SqlObjects.FirstOrDefault(o => o.ServerObjectId == serverObjectId &&
                                                                o.DatabaseId == databaseId &&
                                                                o.SchemaName.Equals((string)myRow["referenced_schema_name"], StringComparison.InvariantCultureIgnoreCase)) != null) ?
                                     (SqlObjects.FirstOrDefault(o => o.ServerObjectId == serverObjectId &&
                                                                o.DatabaseId == databaseId &&
                                                                o.SchemaName.Equals((string)myRow["referenced_schema_name"], StringComparison.InvariantCultureIgnoreCase)).SchemaId)
                                               : -1,
                ReferencedServerName   = (string)myRow["referenced_server_name"],
                ReferencedDatabaseName = (string)myRow["referenced_database_name"],
                ReferencedSchemaName   = (string)myRow["referenced_schema_name"],
                ReferencedEntityName   = (string)myRow["referenced_entity_name"]
            };



            var res = from dbDep in databaseDependencies
                      join parentObj in SqlObjects.Where(p => p.ServerObjectId == serverObjectId && p.DatabaseId == databaseId)
                      on dbDep.ReferencingId equals parentObj.ObjectId
                      join childObj in SqlObjects.Where(c => c.ServerObjectId == serverObjectId && c.DatabaseId == databaseId)
                      on new { SchemaId = dbDep.ReferencedSchemaId, ObjectId = dbDep.ReferencedId, ObjectName = dbDep.ReferencedEntityName } equals
            new { SchemaId = childObj.SchemaId, ObjectId = childObj.ObjectId, ObjectName = childObj.ObjectName }
            select new MySqlExpressionDependency
            {
                ServerObjectId       = serverObjectId,
                DatabaseId           = databaseId,
                ParentServerObjectId = parentObj.ServerObjectId,
                ParentDatabaseId     = parentObj.DatabaseId,
                ParentObjectId       = parentObj.ObjectId,
                ChildServerObjectId  = childObj.ServerObjectId,
                ChildDatabaseId      = childObj.DatabaseId,
                ChildObjectId        = childObj.ObjectId,
                ChildObjectExists    = true
            };

            var uwq = from dbDep in databaseDependencies.Where(dd => dd.ReferencedId == -1 || dd.ReferencedSchemaId == -1)
                      join parentObj in SqlObjects.Where(p => p.ServerObjectId == serverObjectId && p.DatabaseId == databaseId)
                      on dbDep.ReferencingId equals parentObj.ObjectId
                      select new MySqlExpressionDependency
            {
                ServerObjectId        = serverObjectId,
                DatabaseId            = databaseId,
                ParentServerObjectId  = parentObj.ServerObjectId,
                ParentDatabaseId      = parentObj.DatabaseId,
                ParentObjectId        = parentObj.ObjectId,
                ChildServerObjectName = dbDep.ReferencedServerName,
                ChildDatabaseName     = dbDep.ReferencedDatabaseName,
                ChildSchemaName       = dbDep.ReferencedSchemaName,
                ChildObjectName       = dbDep.ReferencedEntityName,
                ChildObjectExists     = false
            };


            _sqlExpressionDependencies.AddRange(res.Union(uwq));

            MyOutput.AppendToLastMessage(EOutputMessageType.INFORMATION, string.Format("Done. Loaded {0:n0} dependencies.", res.Count()), (DateTime.Now - dt).TotalSeconds);
        }
Beispiel #3
0
        private void initSqlObjects(int serverObjectId, int databaseId)
        {
            try
            {
                var serverObjectName = SqlServerObjects.First(so => so.ServerObjectId == serverObjectId).ServerObjectName;
                var databaseName     = SqlDatabases.First(d => d.ServerObjectId == serverObjectId && d.DatabaseId == databaseId).DatabaseName;

                MyOutput.NewMessage(EOutputMessageType.PROGRESS, string.Format("Loading objects from server object '{0}', database '{1}'... ", serverObjectName, databaseName));

                int      counter = 0;
                DateTime dt      = DateTime.Now;

                string query = QueryReader.Read("objects.sql");

                ConnectionPool.GetConnection(serverObjectId).ChangeDatabase(databaseName);
                SqlCommand cmd = (SqlCommand)ConnectionPool.GetConnection(serverObjectId).CreateCommand();
                cmd.CommandText    = query;
                cmd.CommandTimeout = UserSettings.Default.CommandTimeout;
                cmd.CommandType    = CommandType.Text;

                SqlDataReader rdr = cmd.ExecuteReader();
                while (rdr.Read())
                {
                    counter++;
                    _sqlObjects.Add(new MySqlObject
                    {
                        ServerObjectId     = serverObjectId,
                        ServerObjectName   = serverObjectName,
                        DatabaseId         = Convert.ToInt32(rdr["db_id"]),
                        DatabaseName       = (string)rdr["db_name"],
                        SchemaId           = Convert.ToInt32(rdr["schema_id"]),
                        SchemaName         = (string)rdr["schema_name"],
                        ObjectId           = (int)(rdr["object_id"]),
                        ObjectName         = (string)rdr["object_name"],
                        ObjectTypeId       = (string)rdr["type"],
                        ObjectType         = (string)rdr["type_desc"],
                        ObjectNameFull     = (string)rdr["object_name_full"],
                        BaseObjectNameFull = (rdr["base_object_name_full"] != DBNull.Value) ? rdr["base_object_name_full"].ToString() : null
                    });
                }
                rdr.Close();


                // Row counts
                query = QueryReader.Read("table_sizes.sql");

                cmd                = (SqlCommand)ConnectionPool.GetConnection(serverObjectId).CreateCommand();
                cmd.CommandText    = query;
                cmd.CommandTimeout = UserSettings.Default.CommandTimeout;
                cmd.CommandType    = CommandType.Text;

                rdr = cmd.ExecuteReader();
                while (rdr.Read())
                {
                    var obj = _sqlObjects.FirstOrDefault(o => o.ObjectType.Equals("USER_TABLE") &&
                                                         o.SchemaName.Equals((string)rdr["schema_name"], StringComparison.InvariantCultureIgnoreCase) &&
                                                         o.ObjectName.Equals((string)rdr["table_name"], StringComparison.InvariantCultureIgnoreCase));

                    if (obj != null)
                    {
                        obj.RowCount         = (long)rdr["row_counts"];
                        obj.TotalSpaceUsedKB = (long)rdr["total_space_kb"];
                    }
                }
                rdr.Close();

                // Object definitions
                query = QueryReader.Read("object_definitions.sql");

                cmd                = (SqlCommand)ConnectionPool.GetConnection(serverObjectId).CreateCommand();
                cmd.CommandText    = query;
                cmd.CommandTimeout = UserSettings.Default.CommandTimeout;
                cmd.CommandType    = CommandType.Text;

                rdr = cmd.ExecuteReader();
                while (rdr.Read())
                {
                    var obj = _sqlObjects.FirstOrDefault(o => o.ObjectId == (int)rdr["object_id"]);

                    if (obj != null)
                    {
                        obj.ObjectDefinition = (string)rdr["definition"];
                    }
                }
                rdr.Close();

                MyOutput.AppendToLastMessage(EOutputMessageType.INFORMATION, string.Format("Done. Loaded {0:n0} objects.", counter), (DateTime.Now - dt).TotalSeconds);
            }
            catch (Exception ex)
            {
                MyOutput.NewMessage(EOutputMessageType.ERROR, string.Format("Error loading database objects: {0}", ex.Message));
                var exForm = new ExceptionForm(ex);
                exForm.ShowDialog();
            }
        }
Beispiel #4
0
        private void initSqlServerObjects()
        {
            DateTime dt = DateTime.Now;

            _sqlServerObjects.Clear();

            try
            {
                MyOutput.NewMessage(EOutputMessageType.PROGRESS, string.Format("Loading server objects for server ({0}) '{1}'...", 0, getDataSourceName(0)));

                // Load Server data
                SqlCommand cmd = (SqlCommand)ConnectionPool.GetConnection(0).CreateCommand();
                cmd.CommandText    = QueryReader.Read("server_objects.sql");
                cmd.CommandTimeout = UserSettings.Default.CommandTimeout;
                cmd.CommandType    = CommandType.Text;

                SqlDataReader rdr = cmd.ExecuteReader();
                while (rdr.Read())
                {
                    // Create new server object
                    var so = new MySqlServerObject
                    {
                        ServerObjectId   = (int)(rdr["server_id"]),
                        ServerObjectName = (string)rdr["data_source"],
                        IsLinked         = (bool)rdr["is_linked"],
                        Product          = (string)rdr["product"],
                        Provider         = (string)rdr["provider"]
                    };

                    // Add new server objects
                    _sqlServerObjects.Add(so);

                    // Add new connections in the pool
                    if (so.ServerObjectId > 0)
                    {
                        SqlConnectionStringBuilder scsb = new SqlConnectionStringBuilder(ConnectionPool.GetConnection(0).ConnectionString);
                        scsb.DataSource = so.ServerObjectName;

                        ConnectionPool.AddConnection(scsb.ConnectionString, so.ServerObjectId);
                    }
                }
                rdr.Close();

                // Check connections
                ConnectionPool.Connect(_sqlServerObjects.Select(x => x.ServerObjectId).ToArray());
                List <int> soid = new List <int>();
                soid.AddRange(_sqlServerObjects.Select(x => x.ServerObjectId));

                foreach (int id in soid.ToArray())
                {
                    if (!ConnectionPool.ConfirmedServerObjectIds.Contains(id))
                    {
                        _sqlServerObjects.RemoveAll(x => x.ServerObjectId == id);
                    }
                }


                // Server Properties
                foreach (MySqlServerObject so in _sqlServerObjects)
                {
                    cmd                = (SqlCommand)ConnectionPool.GetConnection(so.ServerObjectId).CreateCommand();
                    cmd.CommandText    = QueryReader.Read("server_properties.sql");
                    cmd.CommandTimeout = UserSettings.Default.CommandTimeout;
                    cmd.CommandType    = CommandType.Text;
                    rdr                = cmd.ExecuteReader();
                    while (rdr.Read())
                    {
                        so.ProductVersionCode = (string)rdr["product_version"];
                        so.ProductLevel       = (string)rdr["product_level"];
                        so.Edition            = (string)rdr["edition"];
                        so.ProcessId          = (int)rdr["process_id"];
                        so.EditionID          = (int)rdr["edition_id"];
                        so.LicenseType        = (string)rdr["license_type"];
                    }
                    rdr.Close();
                }
                MyOutput.AppendToLastMessage(EOutputMessageType.INFORMATION, string.Format("Done. Loaded {0:n0} servers.", _sqlServerObjects.Count), (DateTime.Now - dt).TotalSeconds);
            }
            catch (Exception ex)
            {
                MyOutput.NewMessage(EOutputMessageType.ERROR, string.Format("Error loading server objects: {0}", ex.Message));
                var exForm = new ExceptionForm(ex);
                exForm.ShowDialog();
            }
        }