SqlCommand + T-SQL Statements

[christof]

I'm trying to run this T-SQL code using SqlCommand:

USE master;
ALTER DATABASE AdventureWorks
SET SINGLE_USER;
GO

I did it like that:

public static int ObtainExclusiveAccess(Server srv, string database)
{
string cmd = @"USE master; ";
cmd += "ALTER DATABASE " + database;
cmd += @" SET SINGLE_USER ";
//cmd += "GO"; //if i add this got SqlException:
// Incorrect syntax near 'GO'

SqlConnection sqlConn =
new SqlConnection(srv.ConnectionContext.ConnectionString);

sqlConn.Open(); //it's opened for sure, i've checked it
SqlCommand cmdExclusiveAccess = new SqlCommand(cmd,sqlConn);

int result = cmdExclusiveAccess.ExecuteNonQuery();
//result is -1
//string response = (string)cmdExclusiveAccess.ExecuteScalar();
//tried to get: Changed database context to 'master'. but was null
sqlConn.Close();

return result;

}

What am I doing wrong, is SqlCommand good way to run this query
ps i'am logging as user "sa" with membership db_owner, so it is not this

Thanks in advance

 

[Marina Levit [MVP]]

ExecuteNonQuery is not something you can just call on an arbitrary SQL
script. You can't use GO statements.

If you need to run some arbitrary sql statements like you were using query
analyzer, you need to use SQLDMO.

 

[christof]

ExecuteNonQuery is not something you can just call on an arbitrary SQL
script. You can't use GO statements.

If you need to run some arbitrary sql statements like you were using query
analyzer, you need to use SQLDMO.

So I can use Smo (Microsoft.SqlServer.Management.Smo) i understand that,
but is there any possibility do make remote administration with only
queries (simple textbox or textarea and Run button), is there no chance
to run T-SQL queries, but the only solution is using Server, Database...
(Smo) classes? Because in that case each query should be exchanged for
Smo methods properties...

Thanks

 

[Nick Malik [Microsoft]]

I haven't looked at SMO. I used to use SQLDMO and I know that there was an
interface for submitting simple queries. I imagine that this interface is
still in SMO. If you dig around, you may get lucky. Sorry I can't be more
helpful.

--
--- Nick Malik [Microsoft]
MCSD, CFPS, Certified Scrummaster
http://blogs.msdn.com/nickmalik

Disclaimer: Opinions expressed in this forum are my own, and not
representative of my employer.
I do not answer questions on behalf of my employer. I'm just a
programmer helping programmers.
--

 

[Marina Levit [MVP]]

If it is just going to be simple queries (no GO statements), then you can
use ADO.NET. If a user enters a query in that has a GO statement though,
ADO.NET is going to throw an exception.