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

GvS · Accepted Answer · 2009-03-25 16:19:43Z

1

Think about the security issues that you are bringing into your Website.
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.
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.

edited Mar 25, 2009 at 16:19

answered Mar 25, 2009 at 8:28

GvS

52.5k16 gold badges104 silver badges139 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Paul Suart · Accepted Answer · 2009-03-25 09:07:22Z

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

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related