How to dynamically create views?

Question

Background

I have a website that displays data unique to the client. The site required submissions to be created with each new client. Each client is unique and has unique identification information unique to them. For example, identification number and prefix.

Each time a new client is added, a new set of views is created manually using a standard set of views, which is simply changed each time to reflect unique customer information. This is usually done by searching and replacing in SQL Server Management Studio (SSMS)

What am I still?

I created a Winform application that captures unique information and puts them in variables. Then these variables are placed in a standard script that is used to create views.

Problem

My script contains SMSS statements that are not native SQL statements, this leads to an error of my program and interruption of its submission to the database.

The statement in question is the GO keyword used to launch packages using SMSS.

What have i tried so far?

I encapsulated the entire script using String Literal and inserted a new line before and after the GO statements, as suggested in another question. but it does not seem to work.

What am I trying to do now?

Using REGEX to split a script into each "GO". This does not work either.

Question

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

the 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

{"Invalid syntax near 'ANSI_NULLS'. \ R \ nSelect the correct syntax near 'QUOTED_IDENTIFIER'. \ R \ nInvalid syntax near ')'. \ R \ nInvalid syntax near 'ANSI_NULLS'. \ R \ nInvalid syntax near ' QUOTED_IDENTIFIER '. \ R \ nInvalid syntax next to') '. \ R \ n'CREATE VIEW' must be the first statement in the query package. \ R \ nInstructive syntax near ')'. \ R \ nIncorrect syntax next to ')' . \ r \ n Incorrect syntax near ')'. \ r \ n Incorrect syntax near 'ANSI_NULLS'. \ r \ n Incorrect syntax near 'ANSI_NULLS'. \ R \ n Incorrect syntax near the keyword 'AS'. \ R \ nIncorrect syntax next to the keyword "AS". \ r \ nInvalid syntax next to the keyword 'AS'. \ R \ nInvalid syntax next to the keyword 'AS'. \ R \ nIncorrect syntax next to the keyword 'AS'. \ r \ nInvalid syntax next to the keyword 'AS'. \ R \ nInvalid syntax near 'ANSI_NULLS'. \ R \ nInvalid syntax near ')'. \ r \ nInvalid syntax near 'ANSI_NULLS. \ r \ nInvalid syntax next to 'ANSI_NULLS'. \ R \ nThe wrong syntax next to 'ANSI_NULLS'. \ R \ nThe wrong syntax next to the keyword 'AS'. \ r \ nThe wrong syntax next to keyword 'AS'. \ R \ n Incorrect syntax next to 'ANSI_NULLS'. \ R \ n Incorrect syntax near ')'. \ r \ n Incorrect syntax next to 'ANSI_NULLS'. \ r \ n Incorrect syntax near ')'. \ r \ nInvalid syntax near 'ANSI_NULLS'. \ r \ nInvalid syntax near 'ANSI_NULLS'. \ R \ nInvalid syntax near 'ANSI_NULLS'. \ R \ nInvalid syntax near ')'. \ r \ nIncorrect syntax near 'ANSI_ . \ r \ nThe syntax is incorrect next to the keyword "AS". \ r \ nThe incorrect syntax is next to the keyword 'AS'. \ R \ nThe incorrect syntax is next to 'ANSI_NULLS'. \ R \ nThe incorrect syntax is next to 'AN SI_NULLS '. \ R \ n Incorrect syntax next to' ANSI_NULLS '. \ R \ n Incorrect syntax near' ANSI_NULLS '. \ R \ n Incorrect syntax near') '. \ R \ n Incorrect syntax near' ANSI_NULLS '. \ R \ n syntax next to 'ANSI_NULLS'. \ R \ nInvalid syntax next to the keyword 'AS. \ R \ nInvalid syntax next to the keyword' LIKE. \ r \ nSelect the correct syntax next to 'ANSI_NULLS'. " }

My script

/ ****** Object: View [dbo]. [TIDEreportEmails] script Date: 02/23/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 'Unreadable' ELSE 'Read' ENDOM EmailDatentus FR EmailDatmentus FR .dbo.Emails AS Emails_1 WHERE (UserID IN (SELECT UserID FROM DEReportingClient2DB.dbo.Users WHERE (ClientID = 195)))

GO

/ ****** Object: View [dbo]. [TIDEunreadEmails] script Date: 02/23/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 cannot split the lines correctly; You can use one of the following statements to split your script.

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

Below is a snippet of 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, @"\bGO\b", RegexOptions.Multiline); //var scripts = sql.Split(new string[] { "GO" }, StringSplitOptions.None); foreach (var splitScript in scripts) { command.CommandText = splitScript; command.ExecuteNonQuery(); } } } 

Answer 2

Delete GO instructions.

GO is used by SSMS to send individual "packages" to SQL Server ... it's nothing more than a delimiter. Most of the GOs you don't need.

If something should be the first in the package (for example, the CREATE VIEW statement), just send everything in front of it in one batch, and then send the full CREATE VIEW statement in a separate instruction as a separate step in your code.

You can and should use the same connection object, just send one command, then another, one for each batch. And there is no need to separate the "SET" statements with GO. You can put all SET statements together ... and you can only do them once in a connection. So ... use one command to send SET statements. And then one command to send each of the CREATE VIEW statements on its own.

Answer 3

Separation by GO will work if done correctly. You just crack the wrong way. Use a debugger to verify what you are trying to accomplish. This will be clearly violated.

Make sure you are not sending GO to the server.

The line break in your script seems to be messed up. Perhaps this is just an artifact in order to get the script here.