Skip to main content

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.

Comments

Post a Comment

Provide your valuable feedback, we would love to hear from you!! Follow our WhatsApp Channel at
https://whatsapp.com/channel/0029VaKapP65a23urLOUs40y

Popular posts from this blog

Working with OAuth Tokens in .NET Framework 4.8

  Working with OAuth Tokens in .NET Framework 4.8 OAuth (Open Authorization) is a widely used protocol for token-based authentication and authorization. If you're working with .NET Framework 4.8 and need to integrate OAuth authentication, this guide will walk you through the process of obtaining and using an OAuth token to make secure API requests. Step 1: Understanding OAuth Flow OAuth 2.0 typically follows these steps: The client requests authorization from the OAuth provider. The user grants permission. The client receives an authorization code. The client exchanges the code for an access token. The client uses the token to access protected resources. Depending on your use case, you may be implementing: Authorization Code Flow (for web applications) Client Credentials Flow (for machine-to-machine communication) Step 2: Install Required Packages For handling HTTP requests, install Microsoft.AspNet.WebApi.Client via NuGet: powershell Copy Edit Install-Package Microsoft.AspNet.W...
Post a Comment
Read more

Changing the Default SSH Port on Windows Server 2019: A Step-by-Step Guide

Changing the Default SSH Port on Windows Server 2019: A Step-by-Step Guide By default, SSH uses port 22 for all connections. However, for enhanced security or due to policy requirements, it may be necessary to change this default port. In this guide, we'll walk you through how to change the SSH port on Windows Server 2019 . Changing the default port not only reduces the chances of brute-force attacks but also minimizes exposure to potential vulnerabilities. Let's get started! Why Change the Default SSH Port? Changing the default SSH port can offer several advantages: Security : Automated scripts often target the default SSH port (22). Changing it can prevent many basic attacks. Compliance : Certain compliance regulations or internal policies may require the use of non-standard ports. Segregation : If multiple services are running on the same server, different ports can be used for easier management and separation. Prerequisites Before proceeding, ensure that you: Have administ...
Post a Comment
Read more

Understanding Microservices: What They Are and How They Differ from Traditional Services and APIs

  Understanding Microservices: What They Are and How They Differ from Traditional Services and APIs In recent years, microservices have become one of the most popular architectural styles for building modern applications. But what exactly are they, and how do they differ from traditional services or APIs? In this blog, we’ll break down what microservices are, their key features, and how they differ from the more traditional service-oriented architectures (SOA) or simple APIs. What Are Microservices? In the simplest terms, a microservice is a way of designing software as a collection of small, independent services that each handle a specific task or business function. Imagine you're building an online shopping application. Rather than having a massive, monolithic (one big block of) application that handles everything—user management, product catalog, payment processing, etc.—you can break it down into smaller services. For example: User Service : Manages user accounts, login...
Post a Comment
Read more