Run SQL statements from ASP.net application

Question

I need to run sql statements from the application itself. i.e. the user can go into the asp.net applciation, gets a box and can run sql statements from there

I am already doing something like this http://stackoverflow.com/questions/1858329/can-i-rollback-dynamic-sql-in-mssql-tsql/1860334#1860334

That is running dynamic sql

is there a better way to do this

Answer 1

DO NOT DO THIS. What if the user types in sp_msforeachtable 'truncate table ?'...?

Answer 2

RunSQL.aspx utility might help. See Upload T-SQL and execute at your hosting provider using an ASP.NET page.

Answer 3

Dynamic SQL is certainly the easiest way to do this. The alternative is parameterized SQL, but that would require having your users define and set parameters separately from the T-SQL.

You can simply submit the T-SQL string to SQL Server using the SqlCommand object; there's no real benefit to wrapping it in an EXEC or anything, as in the link you provided. You can do exception handling on the .NET side.

Also, if you want to support command batches, keep in mind that SqlClient and friends don't understand "GO", which also isn't an actual T-SQL command -- you will need to parse the input and break it into batches yourself.

I'm sure you understand that there is a big security risk in doing this, and that's it's generally not recommended. You might consider using a connection string that specifies a user with limited permissions, to help control / limit their access.