Controtex.SqlUtility2 2.0.1

Controtex.SqlUtility2

Controtex.SqlUtility2 is a C# library that simplifies querying SQL Server databases and always returns results in structured JSON format.

It supports executing:

• Stored procedures (with or without parameters)
• SQL queries returning tables or scalar values
• Arbitrary SQL commands (e.g. INSERT/UPDATE/DELETE)
• Automatic conversion of all results to JArray / JObject (via Newtonsoft.Json)

🔧 Features

• ✅ JSON output for everything: JArray / JObject
• ✅ Stored procedure support, including multiple result sets
• ✅ Auto-discovery of procedure parameter names via sys.parameters
• ✅ Parameter binding by name or position
• ✅ Unified API for querying and executing
• ✅ Lightweight, dependency-free (aside from Newtonsoft.Json and Microsoft.Data.SqlClient)

📦 Installation

dotnet add package Controtex.SqlUtility2

🚀 Basic Usage

var db = new SqlUtility("your-connection-string");

// Get rows from a table
JArray users = db.Table("Users");

// Run a non-query SQL command
int rows = db.ExecuteCommand("DELETE FROM Logs WHERE Level = @level", "level", "Debug");

// Get a single scalar value
object count = db.ExecuteScalar("SELECT COUNT(*) FROM Users");

🔄 Calling Stored Procedures

🧪 Example 1: No parameters

JArray? result = db.Call("GetAllUsers");

🧪 Example 2: One parameter

JArray? result = db.Call("GetUsersByRole", "role", "Admin");

Equivalent to:

EXEC GetUsersByRole @role = 'Admin'

🧪 Example 3: Multiple parameters

var result = db.Call("GetUsersByStatus",
    new[] { "status", "country" },
    "Active", "PL");

🧪 Example 4: All result sets (multi-table)

JObject results = db.CallAllResults("GetDashboardStats", new object[] { 2024 });

// Access results by name
JArray stats = (JArray)results["Stats"];
JArray errors = (JArray)results["Errors"];

🧪 Example 5: Get single object

JObject? user = db.CallOne("GetUserById", 123);
string? name = user?["Name"]?.Value<string>();

📊 Table Queries

Basic table data

JArray? orders = db.Table("Orders", "UserId = @id", "id", 42);

One row from a table

JObject? latestOrder = db.TableOne("Orders", "UserId = @id", "Date DESC", "id", 42);

Top N results with order

JArray? topItems = db.TableTop("Products", 5, "Category = @cat", "Price DESC", new[] { "cat" }, "Books");

🔁 Execute SQL Commands

Non-query

int affected = db.ExecuteCommand("UPDATE Users SET Active = 0 WHERE LastLogin < @date", "date", DateTime.Now.AddMonths(-6));

Scalar result

object result = db.ExecuteScalar("SELECT COUNT(*) FROM Users WHERE Active = 1");

📄 License

MIT License

✉️ Support

Issues, suggestions, or feedback? Please post them on the NuGet package page.