Skip to main content

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

 

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




In modern web applications, there are often scenarios where users need the flexibility to run dynamic queries against a database directly from a web interface. This is especially useful for admin tools, reporting dashboards, or troubleshooting interfaces where access to the database needs to be quick and easy. Today, we'll walk through building an ASP.NET Web Forms page that allows users to run queries against either SQL Server or Oracle databases with a flexible, user-friendly interface.

In this blog post, we'll cover the following:

  1. Why a Dynamic Query Executor is Useful
  2. Building the ASP.NET Web Forms Page
  3. Supporting Different Types of Queries (SELECT, INSERT, UPDATE, DELETE, Procedures, Views)
  4. Handling SQL Server and Oracle Connections
  5. Securing the Application with Parameterized Queries
  6. Testing and Running the Application

Let’s dive into the solution!

Why a Dynamic Query Executor is Useful

A query execution interface that supports multiple databases and types of queries can be incredibly beneficial for:

  • Administrators: Quickly run maintenance queries without needing to open a full-fledged database client.
  • Developers: Test queries or stored procedures directly from the application.
  • Reporting and Analysis: Enable dynamic reporting by executing queries on-demand.
  • Troubleshooting: Diagnose issues with the database directly from the web application.

For our solution, we’ll build a C# ASP.NET Web Forms page where users can input the connection details, choose the type of database (SQL Server or Oracle), and execute any SQL query they like.

Building the ASP.NET Web Forms Page

First, we’ll create an input form that asks users for the following information:

  • Database Type: Either SQL Server or Oracle.
  • Server Name: The address of the database server.
  • Database Name (or SID for Oracle): The database name or the SID for Oracle.
  • Username and Password: For connecting to the database.
  • Port: An optional field (especially useful for Oracle).
  • SQL Query: A multiline input box where users can enter their queries.

Here’s what the UI looks like in Default.aspx:

html
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="QueryExecutorApp._Default" %>

<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>SQL/Oracle Query Executor</title>
</head>
<body>
    <form id="form1" runat="server">
        <div>
            <h2>Execute SQL/Oracle Query</h2>
            <table>
                <tr>
                    <td>Select Database:</td>
                    <td>
                        <asp:RadioButtonList ID="rblDatabaseType" runat="server">
                            <asp:ListItem Text="SQL Server" Value="SQLServer"></asp:ListItem>
                            <asp:ListItem Text="Oracle" Value="Oracle"></asp:ListItem>
                        </asp:RadioButtonList>
                    </td>
                </tr>
                <tr>
                    <td>Server Name:</td>
                    <td><asp:TextBox ID="txtServer" runat="server" Width="400px"></asp:TextBox></td>
                </tr>
                <tr>
                    <td>Database Name (or SID for Oracle):</td>
                    <td><asp:TextBox ID="txtDatabase" runat="server" Width="400px"></asp:TextBox></td>
                </tr>
                <tr>
                    <td>User Name:</td>
                    <td><asp:TextBox ID="txtUsername" runat="server" Width="400px"></asp:TextBox></td>
                </tr>
                <tr>
                    <td>Password:</td>
                    <td><asp:TextBox ID="txtPassword" runat="server" TextMode="Password" Width="400px"></asp:TextBox></td>
                </tr>
                <tr>
                    <td>Port:</td>
                    <td><asp:TextBox ID="txtPort" runat="server" Width="400px"></asp:TextBox></td>
                </tr>
                <tr>
                    <td>Query:</td>
                    <td><asp:TextBox ID="txtQuery" runat="server" TextMode="MultiLine" Rows="5" Width="400px"></asp:TextBox></td>
                </tr>
                <tr>
                    <td></td>
                    <td><asp:Button ID="btnExecute" runat="server" Text="Execute Query" OnClick="btnExecute_Click" /></td>
                </tr>
            </table>

            <asp:Label ID="lblMessage" runat="server" ForeColor="Red" />
            <br />
            <asp:GridView ID="gvResults" runat="server" AutoGenerateColumns="true" Visible="false"></asp:GridView>
        </div>
    </form>
</body>
</html>

This layout allows the user to select the database type, enter connection details, and execute a query. If the query is a SELECT statement, the results are displayed in a GridView; for other queries (like INSERT, UPDATE, DELETE), a message showing the affected rows is displayed.

Supporting Different Types of Queries (SELECT, INSERT, UPDATE, DELETE, Procedures, Views)

To support multiple types of queries, the backend must be dynamic enough to handle:

  • SELECT Queries: Display results in a grid.
  • INSERT/UPDATE/DELETE Queries: Show the number of rows affected.
  • Stored Procedures: Execute them by recognizing the command type.

We also allow execution of Oracle-specific queries by providing fields for Oracle connection strings (like SID and port). We’ll handle the logic based on the selected database type.

Handling SQL Server and Oracle Connections

In the Default.aspx.cs code-behind file, we build connection strings dynamically based on user input. We use Oracle.ManagedDataAccess for Oracle and System.Data.SqlClient for SQL Server.

Here’s a snippet to build connection strings dynamically:

csharp
// Build Oracle connection string
private string BuildOracleConnectionString(string server, string sid, string username, string password, string port)
{
    return $"Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST={server})(PORT={port})))(CONNECT_DATA=(SID={sid})));User Id={username};Password={password};";
}

// Build SQL Server connection string
private string BuildSqlServerConnectionString(string server, string database, string username, string password)
{
    return $"Server={server};Database={database};User Id={username};Password={password};";
}

Securing the Application with Parameterized Queries

Running dynamic queries can expose the system to SQL injection attacks, especially when accepting queries from user input. To secure the application, it’s crucial to use parameterized queries wherever possible.

For example, for an UPDATE query:

csharp
SqlCommand cmd = new SqlCommand("UPDATE Products SET Price = @Price WHERE ProductID = @ProductID", conn);
cmd.Parameters.AddWithValue("@Price", price);
cmd.Parameters.AddWithValue("@ProductID", productId);

This ensures that user input is treated as a parameter, not as part of the SQL statement, making the application more secure.

Error Handling and Testing the Application

We use try-catch blocks to catch any exceptions and display user-friendly error messages. The application should handle issues like incorrect connection details, invalid queries, or database timeouts gracefully.

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

Running the Application

Once the application is set up, users can access the page and:

  1. Select the database type (SQL Server or Oracle).
  2. Enter connection details (server name, database name or SID, username, password, port).
  3. Enter the query they want to execute.
  4. Click "Execute Query" and see the results.

For SELECT queries, results are displayed in a grid view. For INSERT, UPDATE, and DELETE operations, the number of rows affected is shown.

Conclusion

We’ve built a flexible, dynamic query execution tool in ASP.NET Web Forms that works with both SQL Server and Oracle databases. It can handle different types of queries, such as SELECT, INSERT, UPDATE, and even stored procedures. Additionally, the tool uses best practices like parameterized queries to ensure security and prevent SQL injection.

This dynamic query executor can be expanded further by adding features like saved query history, result export, and more complex user roles for query permissions.

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