How to Dynamically Create Views?

Question

Background

I have a website that displays data unique to a client. The site required views to be created ever time a new client is added. Each client is unique and has a different identifying information unique to them. For example an ID number and a prefix.

Everytime a new client is added a new set of views is manually created using a standard view set, which is just changed each time to reflect the clients unique information. This is usually done using a Find and Replace in SQL Server Management Studio (SSMS)

What I have so far?

I have created a Winform app that captures the unique information and puts them into variables. These variables are then put into the the standard script that is used to create the views.

Problem

My script contains SMSS statements are not native SQL statements, this causes my program to error and break in its submission to the database.

The statement in question is the GOkey word used to run batches by SMSS.

What I have tried so far?

I have encapsulated the whole script using String Literal and have inserted a new line before and after the GO statements as suggested in another question. but it didn't seem to work.

What I am trying now?

Using REGEX to split the script up at every 'GO' occurrence. This isn't working either.

Question

Is there a better solution to this problem or a fix for my solution?

Code

 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();

                    }


                }
            }

Error Message

{"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'."}

My Script

/****** 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

Your RegEx is not able to split lines correctly; You can use one of following statements to split your script.

1. sql.Split(new string[] { "GO" }.
2. Regex.Split(sql, @"\bGO\b", RegexOptions.Multiline);

Following is a code-snippet

      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();

                }


            }
        }