Dynamic SQL Execution Web API

A flexible, high-performance ASP.NET Core Web API engine designed to execute batch SQL statements dynamically. It supports custom authentication, conditional statement execution (Data Retrieval vs. Command Execution), configurable query timeouts, custom XOR encryption wrappers for connection strings, and local transactional support with rollback capabilities.

---

🚀 Features

• Dynamic Batch Execution: Accepts an array of distinct raw SQL commands and executes them sequentially in a single API call.

• Dual-Mode Processing: Detects and routes queries automatically:

• SELECT statements are loaded into memory and returned as serialized JSON payloads or scalar values.

• INSERT, UPDATE, and DELETE commands are processed via ExecuteNonQuery to return rows-affected tallies.

• Transaction Controls: Optional atomic state configuration (rollbackcommit = "1") that manages database transactions and applies rollbacks if an execution unit fails mid-batch.

• Custom Parameter Tuning: Exposes granular per-request controls for individual execution query timeouts.

• Encrypted Connections: Implements a localized cyclic XOR string transformation block to secure over-the-wire connection configurations when requested.

• Unified Error Diagnostics: Fully catches exceptions down to precise line numbers, returning actionable debugging metrics alongside output data blocks.

---

🛠️ API Reference

Execute Dynamic CRUD Operations

POST /api/Data/CRUD_API

Request Payload Structure (InputsValue)

Field	Type	Description
SQLStatements	string[]	Array of raw SQL queries to run in sequential order.
SQLReturntype	string[]	Formats output for corresponding queries ("1" = single scalar text value, "0" = full JSON dataset array).
DBDetails	string	The target database connection string (raw or XOR encrypted).
DBProfile	string	Configuration profile reference identifier.
sqltimeout	string	Query command execution timeout limit in seconds.
multiuserflag	string	Flag denoting multi-tenant operational routing configurations.
securitykey	string	Header metadata key descriptor.
securityvalue	string	Secret matching token evaluated against server configuration keys.
rollbackcommit	string	Transaction behavior switch ("1" = wrap batch inside rollback/commit boundaries, "0" = independent execution).
encrypt	boolean	Flag indicating if the connection string should process through the local decryption module first.

---

📋 JSON Example Payload

Request Body (POST)

{
  "SQLStatements": [
    "SELECT TOP 2 * FROM Employees;",
    "UPDATE Employees SET Department = 'Engineering' WHERE ID = 101;"
  ],
  "SQLReturntype": ["0", "0"],
  "DBDetails": "Server=YOUR_SERVER;Database=YOUR_DB;User Id=YOUR_USER;Password=YOUR_PASSWORD;TrustServerCertificate=True;",
  "DBProfile": "Production_Core",
  "sqltimeout": "30",
  "multiuserflag": "0",
  "securitykey": "YOUR_SECRET_AUTHENTICATION_KEY_HERE",
  "securityvalue": "YOUR_SECRET_AUTHENTICATION_VALUE_HERE",
  "rollbackcommit": "1",
  "encrypt": false
}

Response Body (200 OK)

[
  {
    "noOfRecordsAffected": [
      "[{\"ID\":101,\"Name\":\"Alice\",\"Department\":\"IT\"},{\"ID\":102,\"Name\":\"Bob\",\"Department\":\"HR\"}]",
      "1"
    ],
    "errorMessage": [
      "Successful",
      "Successful"
    ],
    "overAllError": [
      "1",
      "1"
    ]
  }
]

---

⚙️ Configuration (appsettings.json)

Ensure your application configuration defines the correct security keys for authorization checks:

{
  "AppSettings": {
    "AuthenticationKey": "YOUR_SECRET_AUTHENTICATION_KEY_HERE"
  }
}

---

🛡️ Architecture & Security Warning

⚠️ Security Notice: This system is built to act as a generic dynamic execution gateway. Executing raw unparameterized query text sent directly from clients introduces significant risks of SQL Injection. Ensure this endpoint is exclusively exposed over highly secured, trusted internal networks or used within specialized administration dashboard context controls.