Quickly Execute Database Queries in an ASP.NET Website

Very often we find ourselves dealing with a bug or investigating a problem in a scenario where we don’t have direct access to the database to execute a query. Whether it’s because the application is in a production environment and the database is not accessible remotely, or because we need to quickly collect some piece of data through a query but can’t afford to waste time requesting temporary access through a some bureaucratic hierarchy in your company.

Whatever the case may be, one solution could be to create a temporary page in our website that executes the query we’re interested in and displays it in a tabular fashion, much like the query analyzer would.

Of course there is a security dimension to this approach which needs to be carefully considered. The database user that the application authenticates with should NOT be allowed to execute CRUD operations on critical tables. In some cases the website database user shouldn’t even query tables directly, in that case the example provided in this post needs a little tweaking, maybe I’ll implemented it later.  Also this code snippet should not be publicly accessible, on the contrary it should be stored behind a password protected folder at least, and should not be permanently kept in a production project. Just use it to diagnose and resolve the problem you’re facing and immediately delete it.

Ok so that little word of caution being said, I here present you the snippet.  It’s split into two parts, the markup and code-behind, but for ease of use I would recommend to create a single .aspx file with code embedded. Additionally there is a sample file available for download.

protected void Page_Load(object sender, EventArgs e)
    {
        if (Ctrl_Query.Text == "") return;
        
        string connStr = ConfigurationManager.ConnectionStrings["SqlConnection"].ConnectionString;
        DbConnection conn = new SqlConnection(connStr);
        DbCommand cmd = new SqlCommand();

        cmd.Connection = conn;
        cmd.CommandType = CommandType.Text;
        cmd.CommandText = Ctrl_Query.Text;

        try
        {
            conn.Open();
            DataTable dt = new DataTable();
            DbDataAdapter adapter = new SqlDataAdapter(cmd as SqlCommand);
            
            adapter.Fill(dt);
            Ctrl_Table.DataSource = dt;
            this.DataBind();
        }
        catch
        {
            throw;
        }
        finally
        {
            conn.Close();
            conn.Dispose();
        }   
    }