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



Answers
  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.

By : GvS


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;
    }
}


I think you should just use RadioButtons instead of RadioButtonLists.

Here's an article that presents a solution to resolve the radiobutton naming bug.

By : Cerebrus


This video can help you solving your question :)
By: admin