ProcWizard 2.2.2

ProcWizard

A lightweight, high-performance ORM helper for .NET 8.0 that supports executing raw SQL queries and stored procedures, including advanced features like dynamic parameter binding and table-valued parameters for SQL Server or Oracle collections.

Target Framework: <TargetFramework>net8.0</TargetFramework>

📦 Installation

Install via NuGet:

Install-Package ProcWizard2.0

Or using the .NET CLI:

dotnet add package ProcWizard2.0

🚀 Features

• Execute SQL queries and stored procedures asynchronously.
• Supports parameter binding via anonymous objects or DynamicParamBuilder.
• Optimized for performance using cached expressions.
• Full support for structured/table-valued parameters.
• Cross-database support: SQL Server, MySQL, PostgreSQL, Oracle.

⚙️ Basic Usage

Import the namespace

using ProcWizard

Establish a connection

using var connection = new SqlConnection("your-connection-string");

1. Execute async a non-query command

It exposes an asynchronous ExecuteAsync method and a synchronous Execute method to retrieve the items.

await connection.ExecuteAsync(
    "UPDATE Users SET Name = @Name WHERE Id = @Id",
    new { Name = "Alice", Id = 1 }
);

await connection.Execute(
    "UPDATE Users SET Name = @Name WHERE Id = @Id",
    new { Name = "Alice", Id = 1 }
);

2. Retrieve a single record

It exposes an asynchronous SelectSingleAsync method and a synchronous SelectSingleAsync method to retrieve the items.

var user = await connection.SelectSingleAsync<User>(
    "SELECT Id, Name, Email FROM Users WHERE Id = @Id",
    new { Id = 1 }
);
Console.WriteLine(user.Name);

var user = await connection.SelectSingle<User>(
    "SELECT Id, Name, Email FROM Users WHERE Id = @Id",
    new { Id = 1 }
);
Console.WriteLine(user.Name);

3. Retrieve a list of records

It exposes an asynchronous SelectListAsync method and a synchronous SelectList method to retrieve the items.

var users = await connection.SelectListAsync<User>(
    "SELECT * FROM Users WHERE IsActive = @IsActive",
    new { IsActive = true }
);

var users = await connection.SelectList<User>(
    "SELECT * FROM Users WHERE IsActive = @IsActive",
    new { IsActive = true }
);

4. Execute multiple queries with results

It exposes an asynchronous MultiQueryListAsync method and a synchronous MultiQueryList method to retrieve the items.

var commands = new[]
{
    new MultiCommand { Query = "DELETE FROM Logs", ReturnRows = false },
    new MultiCommand { Query = "SELECT * FROM Orders", ReturnRows = true }
};

var orders = await connection.MultiQueryListAsync<Order>(commands);

var commands = new[]
{
    new MultiCommand { Query = "DELETE FROM Logs", ReturnRows = false },
    new MultiCommand { Query = "SELECT * FROM Orders", ReturnRows = true }
};

var orders = await connection.MultiQueryList<Order>(commands);

5. Execute multiple queries returning a single value

It exposes an asynchronous MultiQuerySingleAsync method and a synchronous MultiQuerySingle method to retrieve the items.

var commands = new[]
{
    new MultiCommand { Query = "INSERT INTO Logs(...) VALUES (...)", ReturnRows = false },
    new MultiCommand { Query = "SELECT COUNT(*) FROM Logs", ReturnRows = true }
};

int logCount = await connection.MultiQuerySingleAsync<int>(commands);

🧠 Advanced Parameter Binding

Use DynamicParamBuilder for fine-grained control over parameters:

var paramBuilder = new DynamicParamBuilder();
paramBuilder.Add("Id", 42);
paramBuilder.Add("Name", "Bob", dbType: SqlServerType.NVarChar, size: 50);

// Structured parameter (e.g. for SQL Server table types)
var data = new[] {
    new { Col1 = 1, Col2 = "A" },
    new { Col1 = 2, Col2 = "B" }
};
paramBuilder.Add("Items", data, dbType: SqlServerType.Structured);

var result = await connection.SelectListAsync<User>(
    "MyStoredProcedure",
    paramBuilder,
    commandType: CommandType.StoredProcedure
);

// Using an anonymous object with both inline parameters and a DynamicParamBuilder
var category = _db.SelectListAsync<Category>(
    "spCategory", 
    new { paramBuilder, p_id = 0, p_user = user.id, dynamicParaman }
);

// Or separating the builder and additional parameters explicitly
var category = _db.SelectListAsync<Category>(
    "spCategory", 
    paramBuilder, 
    new { p_id = 0, p_user = user.id, dynamicParaman }, 
    commandType: CommandType.StoredProcedure
);

📖 Stored Procedures

Stored procedures are fully supported via the CommandType.StoredProcedure flag:

var products = await connection.SelectListAsync<Product>(
    "GetActiveProducts",
    new { IsActive = true },
    commandType: CommandType.StoredProcedure
);

💡 Supported Providers

ProcWizard2.0 dynamically supports the following ADO.NET providers:

• System.Data.SqlClient (SQL Server)
• MySql.Data.MySqlClient (MySQL)
• Npgsql (PostgreSQL)
• Oracle.ManagedDataAccess.Client (Oracle)

📄 License

This project is licensed under the MIT License.

For bug reports and recommendations, please contact me at eduardocc1miranda@outlook.com. Don't hesitate to reach out with any issues or suggestions you might have.

Happy coding with ProcWizard!