Building a Dynamic Query Executor for SQL Server and Oracle in ASP.NET Web Forms - II

Building a Dynamic Query Executor for SQL Server and Oracle in ASP.NET Web Forms - II

 To complete the ExecuteSqlServerQuery method as well as the ExecuteOracleQuery method, we need to handle different types of SQL operations (e.g., SELECT, INSERT, UPDATE, DELETE) and manage the execution of the queries appropriately. Here’s how you can structure these methods:

ExecuteSqlServerQuery Method Definition

This method will handle SQL Server queries, detecting whether the query is a SELECT or a data-modifying operation like INSERT, UPDATE, or DELETE. For a SELECT query, it will display the results in a GridView, while for the others, it will return the number of rows affected.

csharp
private void ExecuteSqlServerQuery(string connectionString, string query)
{
    using (SqlConnection conn = new SqlConnection(connectionString))
    {
        try
        {
            conn.Open();

            SqlCommand cmd = new SqlCommand(query, conn);
            SqlDataAdapter adapter = new SqlDataAdapter(cmd);

            // Detect whether the query is a SELECT statement
            if (query.Trim().StartsWith("SELECT", StringComparison.OrdinalIgnoreCase))
            {
                DataTable dt = new DataTable();
                adapter.Fill(dt);

                // Check if the result has rows
                if (dt.Rows.Count > 0)
                {
                    gvResults.DataSource = dt;
                    gvResults.DataBind();
                    gvResults.Visible = true;
                    lblMessage.Text = ""; // Clear any previous messages
                }
                else
                {
                    gvResults.Visible = false;
                    lblMessage.Text = "No records found.";
                }
            }
            else
            {
                // Handle non-SELECT queries like INSERT, UPDATE, DELETE
                int affectedRows = cmd.ExecuteNonQuery();
                lblMessage.Text = $"{affectedRows} row(s) affected.";
                gvResults.Visible = false;
            }
        }
        catch (Exception ex)
        {
            lblMessage.Text = "SQL Server Error: " + ex.Message;
            gvResults.Visible = false;
        }
    }
}

ExecuteOracleQuery Method Definition

Similarly, for Oracle, we’ll handle different query types. We'll use the Oracle.ManagedDataAccess.Client library to work with Oracle connections.

csharp
private void ExecuteOracleQuery(string connectionString, string query)
{
    using (OracleConnection conn = new OracleConnection(connectionString))
    {
        try
        {
            conn.Open();

            OracleCommand cmd = new OracleCommand(query, conn);
            OracleDataAdapter adapter = new OracleDataAdapter(cmd);

            // Detect if it's a SELECT statement
            if (query.Trim().StartsWith("SELECT", StringComparison.OrdinalIgnoreCase))
            {
                DataTable dt = new DataTable();
                adapter.Fill(dt);

                // Check if the result has rows
                if (dt.Rows.Count > 0)
                {
                    gvResults.DataSource = dt;
                    gvResults.DataBind();
                    gvResults.Visible = true;
                    lblMessage.Text = ""; // Clear any previous messages
                }
                else
                {
                    gvResults.Visible = false;
                    lblMessage.Text = "No records found.";
                }
            }
            else
            {
                // Handle non-SELECT queries like INSERT, UPDATE, DELETE
                int affectedRows = cmd.ExecuteNonQuery();
                lblMessage.Text = $"{affectedRows} row(s) affected.";
                gvResults.Visible = false;
            }
        }
        catch (Exception ex)
        {
            lblMessage.Text = "Oracle Error: " + ex.Message;
            gvResults.Visible = false;
        }
    }
}

Explanation of the Code

1. Opening the Connection:

   • We first open a connection to the specified SQL Server or Oracle database using the connection string.

2. Handling SELECT Queries:

   • If the query starts with SELECT, we assume it’s a query for retrieving data.
   • We use SqlDataAdapter (for SQL Server) or OracleDataAdapter (for Oracle) to fill a DataTable with the results.
   • If the DataTable has rows, the results are bound to the GridView for display.

3. Handling Non-SELECT Queries:

   • For INSERT, UPDATE, DELETE, or any other type of query, we use ExecuteNonQuery() to execute the command.
   • The number of rows affected by the query is displayed in the lblMessage label.

4. Error Handling:

   • Any exceptions thrown during execution are caught and displayed as an error message, ensuring the UI doesn’t crash.

---

Full Code Example in C# for the ASP.NET Web Forms Application

Here’s the complete implementation of the methods to handle SQL Server and Oracle queries:

csharp
protected void btnExecute_Click(object sender, EventArgs e)
{
    string dbType = rblDatabaseType.SelectedValue;
    string server = txtServer.Text;
    string database = txtDatabase.Text;
    string username = txtUsername.Text;
    string password = txtPassword.Text;
    string port = txtPort.Text;
    string query = txtQuery.Text;

    try
    {
        if (dbType == "Oracle")
        {
            string oracleConnectionString = BuildOracleConnectionString(server, database, username, password, port);
            ExecuteOracleQuery(oracleConnectionString, query);
        }
        else
        {
            string sqlServerConnectionString = BuildSqlServerConnectionString(server, database, username, password);
            ExecuteSqlServerQuery(sqlServerConnectionString, query);
        }
    }
    catch (Exception ex)
    {
        lblMessage.Text = "Error: " + ex.Message;
    }
}

private void ExecuteSqlServerQuery(string connectionString, string query)
{
    using (SqlConnection conn = new SqlConnection(connectionString))
    {
        try
        {
            conn.Open();

            SqlCommand cmd = new SqlCommand(query, conn);
            SqlDataAdapter adapter = new SqlDataAdapter(cmd);

            if (query.Trim().StartsWith("SELECT", StringComparison.OrdinalIgnoreCase))
            {
                DataTable dt = new DataTable();
                adapter.Fill(dt);

                if (dt.Rows.Count > 0)
                {
                    gvResults.DataSource = dt;
                    gvResults.DataBind();
                    gvResults.Visible = true;
                    lblMessage.Text = "";
                }
                else
                {
                    gvResults.Visible = false;
                    lblMessage.Text = "No records found.";
                }
            }
            else
            {
                int affectedRows = cmd.ExecuteNonQuery();
                lblMessage.Text = $"{affectedRows} row(s) affected.";
                gvResults.Visible = false;
            }
        }
        catch (Exception ex)
        {
            lblMessage.Text = "SQL Server Error: " + ex.Message;
            gvResults.Visible = false;
        }
    }
}

private void ExecuteOracleQuery(string connectionString, string query)
{
    using (OracleConnection conn = new OracleConnection(connectionString))
    {
        try
        {
            conn.Open();

            OracleCommand cmd = new OracleCommand(query, conn);
            OracleDataAdapter adapter = new OracleDataAdapter(cmd);

            if (query.Trim().StartsWith("SELECT", StringComparison.OrdinalIgnoreCase))
            {
                DataTable dt = new DataTable();
                adapter.Fill(dt);

                if (dt.Rows.Count > 0)
                {
                    gvResults.DataSource = dt;
                    gvResults.DataBind();
                    gvResults.Visible = true;
                    lblMessage.Text = "";
                }
                else
                {
                    gvResults.Visible = false;
                    lblMessage.Text = "No records found.";
                }
            }
            else
            {
                int affectedRows = cmd.ExecuteNonQuery();
                lblMessage.Text = $"{affectedRows} row(s) affected.";
                gvResults.Visible = false;
            }
        }
        catch (Exception ex)
        {
            lblMessage.Text = "Oracle Error: " + ex.Message;
            gvResults.Visible = false;
        }
    }
}

Conclusion

This approach allows users to execute any SQL query against either SQL Server or Oracle databases via a web interface. It handles all types of queries (SELECT, INSERT, UPDATE, DELETE) and safely displays the results. For non-SELECT operations, it shows how many rows were affected. With input validation, secure connections, and user-friendly error handling, this is a powerful tool for administrators or developers to quickly interact with databases without needing direct access.