Building a generic page "Query Analyzer" like in Asp.net with SMO

Question

Hello,

I'm build a web page in ASP.net is supposed to work just like Query Analyzer. It has basically a textarea to input the sql command, and an Execute button.

I'm using SMO to execute the command in the database, as follows:

//Create object  SMO
Microsoft.SqlServer.Management.Smo.Server server = new Microsoft.SqlServer.Management.Smo.Server(new Microsoft.SqlServer.Management.Common.ServerConnection(oConn));

//To execute the command
server.ConnectionContext.ExecuteNonQuery(tbx_cmd.Text);
//OR
myDataset = server.ConnectionContext.ExecuteWithResults(tbx_cmd.Text);

The problem is that the textarea command can contain anything, from a stored procedure (with GO's statements) and any select command that return resultsets, just like Query Analyzer.

But, if I have GO statements, I only can perform the query using ExecuteNonQuery method. If I use the ExecuteWithResults method, it raises errors because of the GO statements.

And if I want the resultsets to be available, of course I can only use the ExecuteWithResults method.

Does anybody knows how can I do to execute the same command texts at the same time?

Thank you!

Answer 1

You would have to write your code to split out the commands and be able to recognize where one command ends and another begins. Either way it's not a simple task.

You COULD just make a rule that in your app, all SQL commands end with a semicolon, and then use a string.split() to split it up into multiple strings, and treat each one as a command. That would be dependent on users using this properly, of course.

Speaking of depending on users, I just have to ask, is this something where only you or another trusted person will use it? And something that is not pointing at valuable data? I ask only because when I was a young programmer and didn't know any better, I built such a tool, for a mission-critical database, and now I live in fear that someone is going to do something dumb like

Delete From MyMissionCriticalTable

and then I'll spend who knows how long fixing it...

I have no idea of your experience level, so I don't mean to be insulting when I urge you to consider SHOULD you do it before you worry too much about CAN you do it.

Answer 2

Not sure why you want to do this. Here is an old tool that does this:

http://www.databasejournal.com/features/mssql/article.php/10894_3484936_2/The-SQL-Server-Web-Data-Administrator.htm