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 SSL Certificate Extensions: PEM vs. CER vs. CRT

Understanding SSL Certificate Extensions: PEM vs. CER vs. CRT In the realm of SSL certificates, file extensions like PEM, CER, and CRT play crucial roles in how cryptographic information is stored and shared. While often used interchangeably, each extension carries its own conventions and encoding formats. In this blog post, we'll unravel the differences between PEM, CER, and CRT to shed light on their individual purposes. PEM (Privacy Enhanced Mail) Format: PEM is a versatile format widely employed for storing cryptographic objects. It utilizes base64-encoded ASCII, often adorned with headers like "-----BEGIN CERTIFICATE-----" and "-----END CERTIFICATE-----." Extension: Files with the PEM extension are multipurpose, housing certificates, private keys, and other encoded data. Use Case: PEM's flexibility makes it suitable for a variety of cryptographic data, from certificates to private keys and certificate signing requests (CSRs). CER (Certificate) Format...
Post a Comment
Read more