How to provide SQL command line interface (e.g. osql) to an MSSQL database via a web page in .NET MVC (C #)

Question

I would like to provide a command line interface for my db that allows the user to enter commands or MULTIPLE database queries (separated by line breaks in the text box)

For each line, if its request should return results, and if it is his command, whether it was successful, which allows the user to paste the script into the text area and press "GO" to have the package executed.

I use a DataContext to interact with my database in the application, but do not have a CLUE where to start. Any help would be greatly appreciated.

Answer 1

• Think about the security issues that you introduce to your site.

• Think again about security. How can a smart user (smarter as you / I) hack into a database using this page.
  Perhaps, possibly using incorrect SQL, which you are not thinking about at this stage.

• Use direct SqlConnection and SqlCommand when the database you are using is an SQL server. Use the oracle or other vendor partners when you need to use them. SqlCommand can return more as 1 result, it is convenient in case of several commands in one request. See NextResult for more information.

Answer 2

As follows from the previous answer, please do not do this if it is a public site!

If you must do this, the following code is close to what you need - with a little change, you will get exactly what you want.

public static bool ExecuteSql(string sqlScript) { bool success = true; using (SqlConnection cn = new SqlConnection([YourConnectionString])) { SqlCommand cmd = null; try { cn.Open(); string[] commands = sqlScript.Split(new string[] { "GO\r\n", "GO ", "GO\t" }, StringSplitOptions.RemoveEmptyEntries); foreach (string c in commands) { cmd = new SqlCommand(c, cn); cmd.CommandType = CommandType.Text; cmd.ExecuteNonQuery(); } } catch (Exception ex) { success = false; throw new Exception("Failed to execute sql.", ex); } finally { cn.Close(); } return success; } }