postgexecute.net is lightweight wrapper around NpgsqlConnection object to facilitate simple PostgreSQL execution and data retrieval in .NET Core.
This is NOT an ORM
I repeat:
This is NOT an ORM
There is not data conversion whatsoever, and so there is no impedance mismatch issue.
All read operations will serialize rows directly and only to
IDictionary<string, object>>
or, even much, much better:
- All read operations version with lambda callback for each row
This opens up new flexibility options. Now you can:
Serialize directly to structure of your choice. No need for extra transformation step. For example dictionary of instances that has key same as your database key.
Write directly to stream, json, etc ...
Developer notes
If someone wishes to build micro-ORM Dapper style based on this code base - he or she may freely do so as long as they give proper credits/mentions and link to this repository. And if I may suggest a name - NoORM would be just perfect.
Current version works only with PostgreSQL and there are no plans for now to expand to other databases.
Usage
Install
PostgExecute.Netor clone this project repo.Two ways to use this API:
1. PostgreSQL connection extensions
Extensions on NpgsqlConnection object (like Dapper).
For example:
using (var connection = new NpgsqlConnection("<connection string>"))
{
// Execute can be chained. Two chained executes under same connection and/or transaction
connection.Execute("<pgpsql command 1>").Execute("<pgpsql command 2>");
// ALL methods not returning any results can be chained
// Execute another command and read some data and return results
var myResults1 = connection.Execute("<pgpsql command 3>").Read("<pgpsql read 1>");
// myResults1 is enumerable of row dictionaries
// Read into dictionary
var myResults2 = new List<IDictionary<string, object>>();
connection.Read("<pgpsql read 2>", result => myResults2.Add(result));
// Read into custom class
var myResults3 = List<MyResults>();
connection.Read("<pgpsql read 3>", r => myResults3.Add(new MyResults{ Field1 = result["field1"] }));
// etc..
}
2. Static methods
Each extension have same exact version as static method which takes first parameter connection string.
For example:
Postg.Execute("<connection string>", "pgpsql command 1");
Postg.Execute("<connection string>", "pgpsql command 2");
// ...
Notes:
No chaining available because connection is used once and disposed immediatel.
When using static methods
PostgreSQLnew connection will be created and disposed.
NpgsqlConnectionwill recycle connection made from same thread (they'll have same PID), but, any pending transaction will be lost and rolled back.
API
Full list of entire available API's and their overloads can be found on this interface definition.
They fall in following three categories:
ExecuteandExecuteAsync- Execute PGPSQL command onPostgreSQLdatabaseSingleandSingleAsync- Fetch single row fromPostgreSQLdatabaseReadandReadAsync- Read multiple rows fromPostgreSQLdatabase
Each version have it async version that ends with Async suffix and they process parameters in same way:
Working with parameters
By convention, parameters on query must start with letter
@
Positional parameters
Example:
var result = connection.Single(
@"select * from (
select 1 as first, 'foo' as bar, '1977-05-19'::date as day, null as null
) as sub
where first = @1 and bar = @2 and day = @3
",
1, "foo", new DateTime(1977, 5, 19));
Note:
Positional parameters are assigned in order of appearance in query, name is completely irrelevant (but, they must have one).
Named parameters
Unlike positional parameters, when using named parameters - position is irrelevant and every parameter must have unique name.
To accept named parameters interface exposes parameters collection (type NpgsqlParameterCollection) - as lambda parameter:
var result = connection.Single(
@"select * from (
select 1 as first, 'foo' as bar, '1977-05-19'::date as day, null as null
) as sub
where first = @1 and bar = @2 and day = @3
", p => {
p.AddWithValue("3", new DateTime(1977, 5, 19));
p.AddWithValue("2", "foo");
p.AddWithValue("1", 1);
});
This API also defines extensions for parameter collection type - that allows chaining. This allows muc mroe elegant syntax:
var result = connection.Single(
@"select * from (
select 1 as first, 'foo' as bar, '1977-05-19'::date as day, null as null
) as sub
where first = @1 and bar = @2 and day = @3
", p => p.Add("3", new DateTime(1977, 5, 19)).Add("2", "foo").Add("1", 1));
There is also shorter alias named @P:
var result = connection.Single(
@"select * from (
select 1 as first, 'foo' as bar, '1977-05-19'::date as day, null as null
) as sub
where first = @1 and bar = @2 and day = @3
", p => p.@P("3", new DateTime(1977, 5, 19)).@P("2", "foo").@P("1", 1));
API can also take async version of this lambda:
var result = connection.Single(
@"select * from (
select 1 as first, 'foo' as bar, '1977-05-19'::date as day, null as null
) as sub
where first = @1 and bar = @2 and day = @3
", async p => {
await Task.Delay(0); // some async operation...
p.@P("3", new DateTime(1977, 5, 19)).@P("2", "foo").@P("1", 1);
});
Fetching the data
Default result set for this API is IDictionary<string, object> for each row:
var result = connection.Single("select 1, 'foo' as bar, '1977-05-19'::date as day, null as null");
// result is `IDictionary<string, object>` that represent result row.
Empty result set will yield empty dictionary.
Note:
There is no data conversion whatsoever (this is not ORM).
nullvalues will NOT haveC#null- butDBNull.Valueinstead.
Reason for this is because your null and database null are not same thing.
Same logic applies for reading multiple rows. Read will return IEnumerable<IDictionary<string, object>>:
var result = connection.Read(
@"select * from (
values
(1, 'foo1', '1977-05-19'::date),
(2, 'foo2', '1978-05-19'::date),
(3, 'foo3', '1979-05-19'::date)
) t(first, bar, day)");
// result is `IEnumerable<IDictionary<string, object>>`
Read callback lambda
Each read method has version that accepts lambda callback that is executed for each row:
var results = new List<IDictionary<string, object>>();
await connection.Read(
@"select * from (
values
(1, 'foo1', '1977-05-19'::date),
(2, 'foo2', '1978-05-19'::date),
(3, 'foo3', '1979-05-19'::date)
) t(first, bar, day)",
result => results.Add(result));
Each version also has lambda overload that have bool as return value.
In that case you can return false to break from iteration immediately and safely:
var results = new List<IDictionary<string, object>>();
connection.Read(
@"select * from (
values
(1, 'foo1', '1977-05-19'::date),
(2, 'foo2', '1978-05-19'::date),
(3, 'foo3', '1979-05-19'::date)
) t(first, bar, day)",
result =>
{
if ((int)result["first"] == 2)
{
return false;
}
results.Add(r);
return true;
});
// breaks on second row, third is never executed, result will have only one entry
Of course, there is async overload for each API version:
await connection.ReadAsync(@"
select * from (
values
(1, 'foo1', '1977-05-19'::date),
(2, 'foo2', '1978-05-19'::date),
(3, 'foo3', '1979-05-19'::date)
) t(first, bar, day)",
async result =>
{
await Task.Delay(0); // some async operation...
results.Add(result);
});
Tests
Test coverage is 100% and it can be found here
Future plans
When C# 8.0 finally comes out - implement those fancy, ultra fast async streams for read operations supported by C# 8.0 (e.g. async return yield)
Top comments (0)