System.Data.SqlClient.SqlCommand( with more sqlTextcomands ...)

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

hello,

is it possible to create a sqlTextcomands with more sqlcmds.
I want to run somthing what i have tested in the queryanalyser ...


--first command
Create view v5tempA as .....

GO
-- command 2
Delete from mytable

--command 3
INSERT INTO mytable ....

--command 4
DROP VIEW v5tempA

now i could execute this in 4 steps
Is it possible to have a sqltext with this 4 statements where the commands
are separated with a separator?

what could be the syntax for sqlText with the 4 commands

vobjCmd = New System.Data.SqlClient.SqlCommand(sqlText)
vobjCmd.ExecuteNonQuery()
.....


thanks
 
Hi Xavier,

I must say I haven't tried it myself but why not combine the different
statements in one, seperated by a semicolon

for example:
string sQuery = "create view v5tempA as select * from tblTemp;delete from
mytable; insert into mytable (a, b) values (1, 2);drop view v5tempA";

Hope this works

Cheers
Christiaan
 
Hi Christiaan ,

i tryed it - but did not work.

For example if i have in the command 3 create view statements - in the
query analyzer i put between this commands a GO.

If i replace the now the GO with ; and create a sqltextcmd - i get in from
ado.net the exception message

? ex.Message
"Line 12: Incorrect syntax near ';'.
'CREATE VIEW' must be the first statement in a query batch.
'CREATE VIEW' must be the first statement in a query batch."

any othe idea?
thanks
Xavier
 
the commands are created dynamically from the database.
because the statements runs only ~1 time every 3 Month
also i did not want to save the commands as Stored Proc - because this
statements runs on different server - and if something is changed - better
change it at once in the database.

Have you a example l .... for using Batched Command ... - i am not the
expert in SQL ...

thanks
Xavier
 
but this did not work, i get the ado.net exception message

? ex.Message
"Line 12: Incorrect syntax near ';'.
'CREATE VIEW' must be the first statement in a query batch.
'CREATE VIEW' must be the first statement in a query batch."

any idea how to solve this?
thanks
Xavier
 
Back
Top