SqlContainer - pengdows/pengdows.crud GitHub Wiki
The SqlContainer class in pengdows.crud wraps and simplifies direct SQL execution. It handles connections, parameters, and logging in a consistent, safe, and database-agnostic way.
SqlContainer is responsible for:
- Executing raw or generated SQL
- Managing parameters in a portable way
- Handling command lifecycle and cleanup
- Enforcing connection mode rules (read-only, write-only)
- Safely invoking stored procedures across supported databases
Instances of SqlContainer are constructed internally by DatabaseContext or TransactionContext, both implement IDatabaseContext.
var sc = context.CreateSqlContainer();
You may also optionally pass a pre-existing query string.
A StringBuilder used to build the SQL command text.
- This allows in-place construction of SQL fragments
- SQL can be inspected or logged before execution
Used to bind parameters to the command.
- Automatically supports @name, :name, or positional ?
- Parameters are created using DbProviderFactory
var p = context.CreateDbParameter("email", DbType.String, email);
sc.AppendParameter(p);
sc.Query.Append(context.MakeParameterName(p));
- ExecuteReaderAsync() — runs the query and returns a DbDataReader
- ExecuteNonQueryAsync() — returns affected row count
- ExecuteScalarAsync() — returns a single coerced value of type T
All commands are prepared with proper:
- Parameter limit enforcement
- CommandType (Text or StoredProcedure)
- Statement preparation (if supported)
- Connection open behavior (auto-opened if closed)
ADO.NET supports three values for the CommandType enumeration:
-
CommandType.Text– The default; executes the provided SQL string as-is. -
CommandType.StoredProcedure– Intended to call a stored procedure. -
CommandType.TableDirect– Used to select all rows from a table without SQL (not supported in this library).
While ADO.NET allows CommandType.StoredProcedure, it does not automatically wrap the command in the correct syntax for the underlying database.
This library addresses that limitation as follows:
- It accepts
CommandType.StoredProcedure. - Internally, it rewrites the command into a valid SQL string using the appropriate syntax for the target database.
- It then sets the command back to
CommandType.Textbefore executing it.
The wrapping logic is based on the ProcWrappingStyle for the current DatabaseContext:
| Database | Syntax Used |
|---|---|
| SQL Server |
EXEC procName
|
| Oracle |
BEGIN procName; END;
|
| PostgreSQL |
CALL procName() or SELECT * FROM procName()
|
| MySQL / MariaDB |
CALL procName()
|
| Firebird |
EXECUTE PROCEDURE procName
|
CommandType.TableDirect is not supported, as it bypasses SQL entirely and is of limited value in cross-database scenarios.
- If in TransactionContext or SingleConnection mode, connection stays open
- Otherwise, CommandBehavior.CloseConnection is used to auto-close
- SQL is logged through ILogger at Information level
- Parameter values are NOT logged unless the consumer does so explicitly
- Dispose() clears parameters and query buffer
- Finalizer calls Dispose(false) to ensure unmanaged cleanup
- Cleanup() handles connection and command cleanup based on execution mode
Wraps table or column names using the database's quote character. This will split and reassemble a value as well.
var name = sc.WrapObjectName("MyTable");
//returns "MyTable" or [MyTable] or `MyTable` or similarly appropriate value
var schemaAndName = sc.WrapObjectName("dbo.mytable");
//returns "dbo"."mytable" or [dbo].[mytable] or `dbo`.`mytable` or similarly appropriate value
var sc = context.CreateSqlContainer();
var p = context.CreateDbParameter("userId", DbType.Int32, 42);
sc.AppendParameter(p);
sc.Query.Append("SELECT * FROM Users WHERE Id = ")
.Append(context.MakeParameterName(p));
await using var reader = await sc.ExecuteReaderAsync();