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.Net
or 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
PostgreSQL
new connection will be created and disposed.
NpgsqlConnection
will 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:
Execute
andExecuteAsync
- Execute PGPSQL command onPostgreSQL
databaseSingle
andSingleAsync
- Fetch single row fromPostgreSQL
databaseRead
andReadAsync
- Read multiple rows fromPostgreSQL
database
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).
null
values will NOT haveC#
null
- butDBNull.Value
instead.
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)