The SQL statement does not recognize the go solution (the trigger that dynamically creates the table)

Source of the problem

The SQL text is opened directly with the server, but the error is always unsuccessful when executing the cmdtext command text with the SqlCommand class.

Cause analysis and solution

Executing SQL statements directly with a database is no problem, and you can even use go for batching, but there are some limitations when you use the link class to access the database and execute the SQL statement as follows:

System.Data.SqlClient.SqlCommand cmd;Try{     using(System.Data.SqlClient.SqlConnection Newconn =NewSystem.Data.SqlClient.SqlConnection ("Data Source=.;i Nitial catalog=rfa_lpp3;integrated security=true") ) {cmd=NewSystem.Data.SqlClient.SqlCommand ("Create trigger triggername on dbo.tablename for insert as BEGIN declare @ID int;   Select @ID =id from inserted; Print @ID End", conn); Cmd.      ExecuteNonQuery (); Cmd.   Dispose (); }}cath (Exception ex) {Throw;//will throw an exception}

There will be a syntax error near go "' CREATE TRIGGER ' must be the first statement in the query batch" such as Error

First look at the use of Go:

The SQL Server utility interprets GO as a signal that the current Transact-SQL batch statement should be sent to SQL Server. SQL Server applications can send multiple Transact-SQL statements to SQL Server for execution as a batch. The statements in this batch are compiled into an execution plan. The programmer executes a specific statement in the SQL Server utility, or generates a Transact-SQL statement script that runs in the SQL Server utility, using GO to identify the end of the batch process

Note: 1.GO commands and Transact-SQL statements are not available on the same line . However, you can include comments on the GO command line. 2.CREATE DEFAULT, create PROCEDURE, create RULE, create TRIGGER, and create VIEW statements cannot be combined with other statements in a batch. The batch must start with the CREATE statement . All other statements that follow the batch will be interpreted as part of the first CREATE statement definition

Take a look at the explanation of the SqlCommand class: Represents a Transact-SQL statement or stored procedure to be executed against a SQL Server database. so the text content to be executed with SqlCommand can be multiple SQL statements, a stored procedure, trigger creation, etc., but cannot be used in combination with trigger creation, and cannot use the Go batch end command.

But what if I want to do it together in a mysql.sql text?

Three ideas are available:

1. Create the SQL for the trigger when the string is placed inside the variable, then exec executes the variable.

2. Place the SQL that created the trigger in the stored procedure and then exec the stored procedure execution.

3. Execute the SQL that created the trigger separately. Each created SQL is individually fetched to execute.

Finally put the code.

1. Method 1 Executable SQL

Use [LPP12]  declare @sql nvarchar (max); set @sql ='Create trigger triggername  on  dbo.tablename for    insert     as Begin    declare @ID int;    Select @ID =id from inserted;   Print @ID   end'; exec (@sql);

2. Method 2 of SQL

CREATE PROCEDURE Proc_name  as begindeclare @sql nvarchar (max); set @sql ='Create trigger triggername  on  dbo.tablename for    insert     as Begin    declare @ID int;    Select @ID =id from inserted;   Print @ID   end'; exec (@sql); end

3. Method 3: Store SQL in XML and execute once for each fetch.

//XML Content<?xml version="1.0"encoding="Utf-8"?><tr> <add name="Trigger_insert"Value="Trigger_familycontent_insert"value="Create Trigger Trigger_nameOn dbo.tablename forInsert asBegindeclare @IDint;Select@ID =id frominserted; end"></add><Add Name="Trigger_insert"value="Create Trigger Trigger_nameOn dbo.tablename forInsert asBegindeclare @IDint;Select@ID =id frominserted; end"></add></TR>//C # codeSystem.Data.SqlClient.SqlCommand cmdnew; Try                        {                            string_xmlpath = Path.getdirectoryname (assembly.getexecutingassembly (). Location) +@"\sqlcreatetrggers.xml"; XmlDocument Doc=NewXmlDocument (); Doc.                            Load (_xmlpath); XmlNodeList nodes= Doc. selectSingleNode ("TR").                                                        ChildNodes; using(System.Data.SqlClient.SqlConnection Newconn =NewSystem.Data.SqlClient.SqlConnection ("Data Source=.;i Nitial catalog=rfa_lpp3;integrated security=true") {newconn.                                Open (); Cmdnew=NewSystem.Data.SqlClient.SqlCommand (); foreach(XmlElement nodeinchnodes) {                                    stringValue = node. attributes["value"].                                    Value; Cmdnew=NewSystem.Data.SqlClient.SqlCommand (value. Replace ("\ r","  "). Replace ("\ n","  "). Replace ("\ t","  "), newconn); intCount =cmdnew.                                ExecuteNonQuery (); } cmdnew.                                Dispose (); Newconn.                            Close (); }                        }                        Catch(Exception ex) {Console.WriteLine (ex).                            Message);                        Console.readkey (); }

It is a mistake to welcome correct.