コード例 #1
0
        public async Task EnumerateObjectsInDatabases(
            SqlConnectionViewModel connection,
            IEnumerable <DatabaseViewModel> databases,
            string nameSearchString,
            string schemaSearchString,
            string definitionSearchString,
            IEventAggregator eventAggregator)
        {
            Contract.Requires(databases != null);
            //int databaseCount = databases.Count();

            var log = LogManager.GetLog(typeof(SchemaDBContext));

            var tasks = new List <Task>();

            eventAggregator.PublishOnUIThread(new ObjectEnumerationStartingMessage());

            foreach (var db in databases)
            {
                var t = Task.Factory.StartNew(() =>
                {
                    try
                    {
                        log.Info("Sch DB Ctx begin foreach DB {0}", db.db_name);
                        var connectionString           = ConnectionViewModel.ConnectionString();
                        var cnStringBuilder            = new SqlConnectionStringBuilder(connectionString);
                        cnStringBuilder.InitialCatalog = db.db_name;
                        using (var cn = new SqlConnection(cnStringBuilder.ConnectionString))
                        {
                            cn.Open();
                            var objectNameSearchParam       = new SqlParameter("@objectNameSearchParam______", (nameSearchString ?? "").Trim());
                            var objectSchemaSearchParam     = new SqlParameter("@objectSchemaSearchParam______", (schemaSearchString ?? "").Trim());
                            var objectDefinitionSearchParam = new SqlParameter("@objectDefinitionSearchParam______", (definitionSearchString ?? "").Trim());
                            var cmd            = cn.CreateCommand();
                            cmd.CommandTimeout = 0;
                            cmd.CommandText    = objectsQuery;
                            cmd.Parameters.Add(objectNameSearchParam);
                            cmd.Parameters.Add(objectSchemaSearchParam);
                            cmd.Parameters.Add(objectDefinitionSearchParam);
                            using (var result = cmd.ExecuteReader())
                            {
                                var objects = SysObject.MapFrom(result).ToArray();

                                var message = new EnumerateObjectsInDatabaseMessage(ConnectionViewModel, objects, db.db_name, connection.ServerAndInstance);
                                eventAggregator.PublishOnUIThread(message);
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        connection.ErrorMessage = ex.ToString();
                        eventAggregator.PublishOnUIThread(ex);
                    }
                }, TaskCreationOptions.LongRunning);
                tasks.Add(t);
            }
            await Task.Factory.StartNew(() => Task.WaitAll(tasks.ToArray()));

            return;
        }
コード例 #2
0
ファイル: SysObject.cs プロジェクト: dawnadvent/SqlTools
 public static IEnumerable <SysObject> MapFrom(IDataReader rdr)
 {
     while (rdr.Read())
     {
         var rv = new SysObject
         {
             db_name                   = rdr["db_name"].ToString(),
             create_date               = Convert.ToDateTime(rdr["create_date"]),
             is_encrypted              = Convert.ToBoolean(rdr["is_encrypted"]),
             modify_date               = Convert.ToDateTime(rdr["modify_date"]),
             object_id                 = Convert.ToInt32(rdr["object_id"]),
             object_name               = Convert.ToString(rdr["object_name"]),
             parent_object_name        = Convert.ToString(rdr["parent_object_name"]),
             parent_object_schema_name = Convert.ToString(rdr["parent_object_schema_name"]),
             schema_name               = Convert.ToString(rdr["schema_name"]),
             server_name               = Convert.ToString(rdr["server_name"]),
             type_desc                 = Convert.ToString(rdr["type_desc"])
                                         //,
                                         //Urn = Convert.ToString(rdr["Urn"])
         };
         yield return(rv);
     }
 }
コード例 #3
0
        public async Task <string> GetObjectDefinition(SysObject so)
        {
            using (var cn = new SqlConnection(ConnectionViewModel.ConnectionString()))
            {
                var srvConnect = new ServerConnection(cn);
                var srv        = new smo.Server(srvConnect);
                srv.Refresh();
                SetDefaultInitFields(srv);
                smo.Database  database = null;
                StringBuilder sb       = null;
                await Task.Factory.StartNew(() =>
                {
                    database = srv.Databases[so.db_name];

                    // SMO keeps different top level collections for each type of object. You can't go to the DB and say,
                    // "Give me the object with this URI," even though each object DOES have a URI property. In some cases,
                    // you have to supply a parent object (e.g. Triggers). So the switch block below figures out how to
                    // grab each object from the database based on its type.
                    //
                    // Worse yet, even though each of them does contain a method called Script() that takes a single parameter
                    // of type ScriptOptions, there's no base object or interface that defines these objects as something
                    // that can script itself. To get make this easier, we cheat and just use a dynamic type then call the
                    // Script() method on that dynamic object. This is pretty hokey, but this only gets called once each
                    // time the user requests that an object get scripted out, i.e. it's not running in a loop thousands of times.
                    dynamic scriptableObject = null;
                    switch (so.type_desc)
                    {
                    case "CHECK_CONSTRAINT":
                        scriptableObject = database.Tables[so.parent_object_name, so.parent_object_schema_name].Checks[so.object_name];
                        break;

                    case "DEFAULT_CONSTRAINT":
                        // We basically ignore default constraints - they get scripted out as part of scripting a table... I think that's why I did this...
                        //ob = db.Tables[so.parent_object_name, so.parent_object_schema_name]
                        break;

                    case "SQL_STORED_PROCEDURE":
                        scriptableObject = database.StoredProcedures[so.object_name, so.schema_name];
                        break;

                    case "USER_TABLE":
                        scriptableObject = database.Tables[so.object_name, so.schema_name];
                        break;

                    case "SQL_INLINE_TABLE_VALUED_FUNCTION":
                    case "SQL_SCALAR_FUNCTION":
                    case "SQL_TABLE_VALUED_FUNCTION":
                        scriptableObject = database.UserDefinedFunctions[so.object_name, so.schema_name];
                        break;

                    case "SQL_TRIGGER":
                        scriptableObject = database.Tables[so.parent_object_name, so.parent_object_schema_name].Triggers[so.object_name];
                        break;

                    case "VIEW":
                        scriptableObject = database.Views[so.object_name, so.schema_name];
                        break;

                    default:
                        throw new InvalidOperationException(String.Format("Unknown DB object type of «{0}» was encountered. " +
                                                                          "I don't know what to do with objects of that type.", so.type_desc));
                    }

                    sb = new StringBuilder();
                    if (scriptableObject == null)
                    {
                        sb.Append("The specified object was not found on the server.");
                    }
                    else
                    {
                        sb.AppendFormat(@"-- {0} :: {1}.{2}.{3}{4}", so.server_name, so.db_name, so.schema_name, so.object_name, Environment.NewLine);
                        sb.AppendFormat(@"-- {0}{1}", so.type_desc, Environment.NewLine);
                        sb.AppendFormat(@"-- Script Created: {0:yyyy-MM-dd HH\:mm\:ss zz}{1}", DateTimeOffset.Now, Environment.NewLine);
                        sb.AppendFormat(@"-- Object Created: {0:yyyy-MM-dd HH\:mm\:ss}{1}", so.create_date, Environment.NewLine);
                        sb.AppendFormat(@"-- Object LastMod: {0:yyyy-MM-dd HH\:mm\:ss}{1}", so.modify_date, Environment.NewLine);

                        sb.AppendFormat("use {0}{1}go{1}", QuoteName(so.db_name), Environment.NewLine);
                        // Script the DROP surrounded by multiline comments
                        ProcessScriptResults(scriptableObject.Script(scriptDropOptions), sb, "/*", "*/");
                        // now script the actual body of the object
                        ProcessScriptResults(scriptableObject.Script(scriptCreateOptions), sb);
                    }
                });

                return(sb.ToString());
            }
        }