c# - 如何动态创建 View ？

Question

背景

我有一个显示客户独有数据的网站。每次添加新客户时，站点都需要创建 View 。每个客户都是独一无二的，并且拥有他们独有的不同识别信息。例如 ID 号和前缀。

每次添加新客户时，都会使用标准 View 集手动创建一组新 View ，每次更改以反射(reflect)客户的独特信息。这通常使用 SQL Server Management Studio (SSMS) 中的查找和替换来完成

到目前为止我有什么？

我创建了一个 Winform 应用程序，它可以捕获唯一信息并将它们放入变量中。然后将这些变量放入用于创建 View 的标准脚本中。

问题

我的脚本包含的 SMSS 语句不是 native SQL 语句，这会导致我的程序出错并中断其向数据库的提交。

有问题的语句是用于通过 SMSS 运行批处理的 GO 关键字。

到目前为止我尝试了什么？

我已经使用 String Literal 封装了整个脚本，并按照另一个问题中的建议在 GO 语句前后插入了一个新行。但它似乎没有用。

我现在在尝试什么？

在每次出现“GO”时使用 REGEX 拆分脚本。这也行不通。

问题

这个问题是否有更好的解决方案或我的解决方案的修复程序？

代码

 string connectionString = fmDbSelect();
            using (SqlConnection connection = new SqlConnection(connectionString))
            {

                using (SqlCommand command = new SqlCommand())
                {
                    command.Connection = connection;
                    connection.Open();
                    var scripts = Regex.Split(sql, @"^\w+GO$", RegexOptions.Multiline);
                    foreach (var splitScript in scripts)
                    {
                        command.CommandText = splitScript;
                        command.ExecuteNonQuery();

                    }


                }
            }

错误信息

{"Incorrect syntax near 'ANSI_NULLS'.\r\nIncorrect syntax near 'QUOTED_IDENTIFIER'.\r\nIncorrect syntax near ')'.\r\nIncorrect syntax near 'ANSI_NULLS'.\r\nIncorrect syntax near 'QUOTED_IDENTIFIER'.\r\nIncorrect syntax near ')'.\r\n'CREATE VIEW' must be the first statement in a query batch.\r\nIncorrect syntax near ')'.\r\nIncorrect syntax near ')'.\r\nIncorrect syntax near ')'.\r\nIncorrect syntax near 'ANSI_NULLS'.\r\nIncorrect syntax near 'ANSI_NULLS'.\r\nIncorrect syntax near the keyword 'AS'.\r\nIncorrect syntax near the keyword 'AS'.\r\nIncorrect syntax near the keyword 'AS'.\r\nIncorrect syntax near the keyword 'AS'.\r\nIncorrect syntax near the keyword 'AS'.\r\nIncorrect syntax near the keyword 'AS'.\r\nIncorrect syntax near 'ANSI_NULLS'.\r\nIncorrect syntax near ')'.\r\nIncorrect syntax near 'ANSI_NULLS'.\r\nIncorrect syntax near 'ANSI_NULLS'.\r\nIncorrect syntax near 'ANSI_NULLS'.\r\nIncorrect syntax near the keyword 'AS'.\r\nIncorrect syntax near the keyword 'AS'.\r\nIncorrect syntax near 'ANSI_NULLS'.\r\nIncorrect syntax near ')'.\r\nIncorrect syntax near 'ANSI_NULLS'.\r\nIncorrect syntax near ')'.\r\nIncorrect syntax near 'ANSI_NULLS'.\r\nIncorrect syntax near 'ANSI_NULLS'.\r\nIncorrect syntax near 'ANSI_NULLS'.\r\nIncorrect syntax near ')'.\r\nIncorrect syntax near 'ANSI_NULLS'.\r\nIncorrect syntax near the keyword 'AS'.\r\nIncorrect syntax near the keyword 'AS'.\r\nIncorrect syntax near 'ANSI_NULLS'.\r\nIncorrect syntax near 'ANSI_NULLS'.\r\nIncorrect syntax near 'ANSI_NULLS'.\r\nIncorrect syntax near 'ANSI_NULLS'.\r\nIncorrect syntax near ')'.\r\nIncorrect syntax near 'ANSI_NULLS'.\r\nIncorrect syntax near 'ANSI_NULLS'.\r\nIncorrect syntax near the keyword 'AS'.\r\nIncorrect syntax near the keyword 'LIKE'.\r\nIncorrect syntax near 'ANSI_NULLS'."}

我的脚本

/****** Object: View [dbo].[TIDEreportEmails] Script Date: 23/02/2015 12:43:36 ******/ SET ANSI_NULLS ON GO

SET QUOTED_IDENTIFIER ON GO

CREATE VIEW [dbo].[TIDEreportEmails] AS SELECT EmailID, EmailContent, EmailSubject, EmailTo, EmailFrom, UserID, ObjectValueID, EmailSent, EmailCreated, EmailRead, EmailFromName, EmailType, EmailFailed, CASE WHEN emailread IS NULL THEN 'Not Read' ELSE 'Read' END AS EmailStatus FROM DEReportingClient2DB.dbo.Emails AS Emails_1 WHERE (UserID IN (SELECT UserID FROM DEReportingClient2DB.dbo.Users WHERE (ClientID = 195)))

GO

/****** Object: View [dbo].[TIDEunreadEmails] Script Date: 23/02/2015 12:43:36 ******/ SET ANSI_NULLS ON GO

SET QUOTED_IDENTIFIER ON GO

CREATE VIEW [dbo].[TIDEunreadEmails] AS SELECT COUNT(*) AS UnreadEmails, UserID FROM dbo.TIDEreportEmails WHERE
(EmailRead IS NULL) GROUP BY UserID

Answer 1

您的 RegEx 无法正确拆分行；您可以使用以下语句之一来拆分脚本。

1. sql.Split(新字符串[] { "GO"}.
2. Regex.Split(sql, @"\bGO\b", RegexOptions.Multiline);

以下是代码片段

      string connectionString = fmDbSelect();
        using (SqlConnection connection = new SqlConnection(connectionString))
        {

            using (SqlCommand command = new SqlCommand())
            {
                command.Connection = connection;
                connection.Open();
                var scripts = Regex.Split(sql, @"\bGO\b", RegexOptions.Multiline);
                //var scripts = sql.Split(new string[] { "GO" }, StringSplitOptions.None);
                foreach (var splitScript in scripts)
                {
                    command.CommandText = splitScript;
                    command.ExecuteNonQuery();

                }


            }
        }