How to provide a SQL command line interface (like osql) to a MSSQL database via webpage in .NET MVC (c#)

Question

I'd like to provide a command line interface to my db that allows the user to enter MULTIPLE database commands or queries (separated by line breaks in the textarea)

For each line, if its a query must return the results and if its a command, whether or not it was successful - thus allowing the user to paste a script into the text area and click 'GO' to have the batch executed.

I have been using a DataContext to interface with my database in the application but havent a CLUE where to start. Any assistance would be greatly appreciated

Answer 1

1. Think about the security issues that you are bringing into your Website.

2. Think again about the security. How can a clever user (more clever as you/me) hack into the database using this page.
   Maybe/probably using some misformed SQL, that you do not think about in this stage.

3. Use a direct SqlConnection and SqlCommand when the database you are using is SQL server. Use the oracle or other provider counterparts when you need to use these. A SqlCommand can return more as 1 result, this is handy in the case of multiple commands in one query. See the NextResult method for more information.

Answer 2

As the previous answer points out, please don't do this if it's a publicly accessible site!

If you must do it, the following code is close to what you're after - with a bit of modification you'll 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;
    }
}