c# - SQL SMO 生成插入语句不正确

Question

我正在开发一个 C# 应用程序来生成脚本。我在下面有以下设置，但正在为其他模式生成表/插入语句。我只想要一个特定的架构和其中的所有架构/数据。

我尝试设置 options.WithDependencies = false;，但它不会像编写数据脚本那样为我提供所需的一切。

我还缺少什么其他设置？

        private void GeneratingMainSQLScript(string schema)
        {
            Server srv = new Server(@".\sql2017");
            Database dbs = srv.Databases["SOURCE_T"];
            ScriptingOptions options = new ScriptingOptions();
            options.TargetServerVersion = SqlServerVersion.Version140;
            options.EnforceScriptingOptions = true;
            options.ScriptData = true;
            options.ScriptDrops = false;
            options.FileName = GenerateScriptFileName(schema, false);
            options.EnforceScriptingOptions = true;
            options.ScriptSchema = true;
            options.IncludeHeaders = true;
            options.AppendToFile = true;
            options.Indexes = true;
            options.WithDependencies = true;

            var tableNames = GetTableNames(schema);

            int progressCounter = 1;
            foreach (var tbl in tableNames)
            {
                if (tbl.SchemaName == "dbo" && tbl.TableName == "FileDatas")
                {
                    options.ScriptData = false;
                }

                dbs.Tables[tbl.TableName, tbl.SchemaName].EnumScript(options);

                DisplayDebug(string.Format("Running migration schema's ({2}) main genreated script | Total: {0} | Processing #: {1}", tableNames.Count, progressCounter, schema));

                progressCounter++;
            }
        }

更新：

在与我的领导合作后，我做出了这些改变，我们已经很接近了。现在的问题：它是在创建表后生成 FKS，而不是像直接通过 SSMS 那样在文件的末尾生成 FKS？

Server srv = new Server(@".\sql2017");
            Database dbs = srv.Databases["SOURCE_T"];
            ScriptingOptions options = new ScriptingOptions();
            options.FileName = GenerateScriptFileName(schema, isSchemaOnly);
            options.EnforceScriptingOptions = true;
      
            // start
            options.AnsiPadding = false;
            options.AppendToFile = true;
            options.ContinueScriptingOnError = false;
            options.ConvertUserDefinedDataTypesToBaseType = false;
            options.WithDependencies = false;
            options.IncludeHeaders = true;
            options.IncludeScriptingParametersHeader = false;
            options.SchemaQualify = true;
            options.Bindings = false;
            options.NoCollation = true;
            options.Default = true;
            options.ScriptDrops = false;
            options.ScriptSchema = isSchemaOnly;
            options.ExtendedProperties = true;
            options.TargetServerVersion = SqlServerVersion.Version140;
            options.TargetDatabaseEngineType = Microsoft.SqlServer.Management.Common.DatabaseEngineType.Standalone;
            options.LoginSid = false;
            options.Statistics = false;
            options.ScriptData = !isSchemaOnly;
            options.ChangeTracking = false;
            options.DriAllConstraints = true;
            options.ScriptDataCompression = false;
            options.DriForeignKeys = true;
            options.FullTextIndexes = false;
            options.Indexes = false;
            options.DriPrimaryKey = true;
            options.Triggers = false;
            options.DriUniqueKeys = true;
            options.DriAll = true;

标签： c#sql-serversmo

Answer 1

这是我正在使用的解决方案。这里的关键是我设置的所有选项，还有我如何提取表名。GetTablesNames将按照需要制作的顺序获取表格，这允许所有内容与 FK 一起正确处理。

1. 正确获取表格
2. 将此变量设置为 false： options.WithDependencies = false;

是我解决方案的关键。

        private void GeneratingMainSQLScript(string schema, bool isSchemaOnly)
        {
            Server srv = new Server(sqlServer);
            Database dbs = srv.Databases["SOURCE_T"];
            ScriptingOptions options = new ScriptingOptions();
            options.FileName = GenerateScriptFileName(schema, isSchemaOnly);
            options.EnforceScriptingOptions = true;

            // start
            options.AnsiPadding = false;
            options.AppendToFile = true;
            options.ContinueScriptingOnError = false;
            options.ConvertUserDefinedDataTypesToBaseType = false;
            options.WithDependencies = false;
            options.IncludeHeaders = true;
            options.IncludeScriptingParametersHeader = false;
            options.SchemaQualify = true;
            options.Bindings = false;
            options.NoCollation = true;
            options.Default = true;
            options.ScriptDrops = false;
            options.ScriptSchema = true;
            options.ExtendedProperties = true;
            options.TargetServerVersion = SqlServerVersion.Version140;
            options.TargetDatabaseEngineType = Microsoft.SqlServer.Management.Common.DatabaseEngineType.Standalone;
            options.LoginSid = false;
            options.Statistics = false;
            options.ScriptData = true;
            options.ChangeTracking = false;
            options.DriAllConstraints = true;
            options.ScriptDataCompression = false;
            options.DriForeignKeys = true;
            options.FullTextIndexes = false;
            options.Indexes = false;
            options.DriPrimaryKey = true;
            options.Triggers = false;
            options.DriUniqueKeys = true;


            if (isSchemaOnly == true)
            {
                options.ScriptSchema = true;
                options.DriForeignKeys = true;
                options.SchemaQualify = true;
                options.SchemaQualifyForeignKeysReferences = true;
                options.DriAll = true;
                options.ScriptData = false;
            }
            else
            {
                options.ScriptSchema = false;
                options.ScriptData = true;
                options.DriAll = false;
            }

            var tableNames = GetTablesNames(schema, sourceDefaultConnection, true);

            int progressCounter = 1;
            foreach (var tbl in tableNames)
            {
                if (tbl.SchemaName == "HR" && tbl.TableName == "FileDatas" && isSchemaOnly == false)
                {
                    continue;
                }
                else
                {
                    dbs.Tables[tbl.TableName, tbl.SchemaName].EnumScript(options);
                }

                DisplayDebug(string.Format("Running migration schema's ({2}) main generated script | Total: {0} | Processing #: {1}", tableNames.Count, progressCounter, schema));

                progressCounter++;
            }
        }

        private IList<DatabaseTableDTO> GetTablesNames(string schema, string connectionName, bool isAscending)
        {
            string sql = string.Format(@"WITH cte (lvl, object_id, name, schema_Name) AS
                                          (SELECT 1, object_id, sys.tables.name, sys.schemas.name as schema_Name
                                           FROM sys.tables Inner Join sys.schemas on sys.tables.schema_id = sys.schemas.schema_id
                                           WHERE type_desc = 'USER_TABLE'
                                             AND is_ms_shipped = 0
                                           UNION ALL SELECT cte.lvl + 1, t.object_id, t.name, S.name as schema_Name
                                           FROM cte
                                           JOIN sys.tables AS t ON EXISTS
                                             (SELECT NULL FROM sys.foreign_keys AS fk
                                              WHERE fk.parent_object_id = t.object_id
                                                AND fk.referenced_object_id = cte.object_id )
                                           JOIN sys.schemas as S on t.schema_id = S.schema_id
                                           AND t.object_id <> cte.object_id
                                           AND cte.lvl < 30
                                           WHERE t.type_desc = 'USER_TABLE'
                                             AND t.is_ms_shipped = 0 )
                                        SELECT schema_Name, name, MAX (lvl) AS dependency_level
                                        FROM cte
                                        where schema_Name = '{0}'
                                        GROUP BY schema_Name, name
                                        ORDER BY dependency_level {1},schema_Name, name;", schema, isAscending == true ? "ASC" : "DESC");

            var connectionString = ConfigurationManager.ConnectionStrings[connectionName];

            var listOfTables = new List<DatabaseTableDTO>();

            using (SqlConnection conn = new SqlConnection(connectionString.ConnectionString))
            {
                conn.Open();
                using (var command = new SqlCommand(sql, conn))
                {
                    using (var reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            listOfTables.Add(new DatabaseTableDTO { SchemaName = schema, TableName = reader.GetString(1) });
                        }
                    }
                }
            }

            return listOfTables;
        }

        public class DatabaseTableDTO
        {
            public string SchemaName { get; set; }

            public string TableName { get; set; }
        }