Awesome

[!IMPORTANT] as of 2024-03-08 I'm not going to maintain this project anymore. I've moved on to SogePoco

AsyncPocoDpy

AsyncPocoDpy is a fork of AsyncPoco. Unless explicitly configured, it is 100% compatible with AsyncPoco. What differentiates AsyncPocoDpy from AsyncPoco is a support for collection based parameters. Without enabling it, AsyncPocoDpy continues to follow logic of upstream being: expand sql parameters that are collections into as many sql parameters as there are in said collection sql parameter. I'm using it in projects utilizing PostgreSQL and SQL Server. Probably it works for other databases but I didn't check it. When database natively supports collections, all that is needed is to set property ShouldExpandSqlParametersToLists of AsyncPoco class instance to false

Examples below utilize dotnet script running within VSCode. It works normally in regular dotnet, I picked those tools just for sake of brevity.

Installation

install AsyncPocoDpy from nuget

Usage in PostgreSQL

Postgresql supports arrays natively so all that is needed is to set boolean mentioned above.

Assuming that following database schema is available...

create table Something(
    id serial not null PRIMARY KEY,
    somenum int not null,
    sometext text
);
insert into Something(sometext, somenum) values ('first', 1);
insert into Something(sometext, somenum) values ('second', 2);
insert into Something(sometext, somenum) values ('third', 3);
select * from Something

...following C# program uses native parameters

(following example is present in repo as VSCode project)

#!/usr/bin/env dotnet-script
#r "nuget: Npgsql, 5.0.3"
#r "nuget: AsyncPocoDpy, 2.0.2.1"

using AsyncPoco;

//POCO classes either written manually or generated by project similar to https://github.com/d-p-y/PocoSchemaCodeGeneration
[ExplicitColumns]
[TableName("something")]
[PrimaryKey("id")]
public class Something {
    [Column] public int id {get; set;}
    [Column] public int somenum {get; set;}
    [Column] public string sometext {get; set;}
}

var rawDbConn = new Npgsql.NpgsqlConnection("Host=localhost;Username=user;Password=pass;Database=dbname");
rawDbConn.Open();

var dbConn = new AsyncPoco.Database(rawDbConn);

dbConn.ShouldExpandSqlParametersToLists = false; //all that is needed to enable support in PostgreSQL

object myCollectionParameter = new [] {"first", "second"};
//use 'object' above to prevent wrong method overload resolution in AsyncPoco (variable params)

var rawRows = 
    await dbConn.FetchAsync<Something>(
        "select * FROM Something WHERE sometext = ANY(@0)", 
        myCollectionParameter
    );

var rows = rawRows.OrderBy(x => x.sometext).ToList();

Console.WriteLine($"Retrieved {rows.Count} rows having somenums: {String.Join(',', rows.Select(x => x.somenum))}");

...and produces given output:

Retrieved 2 rows having somenums: 1,2

Usage in SQL Server

SQL Server doesn't have out-of-the box support for collections in parameters. It requires a little bit of setup to use it. Table-Valued parameter types are the implementation upon feature is built.

Assuming that following database schema is available...

create table Something(
    id int identity(1,1) not null PRIMARY KEY,
    somenum int not null,
    sometext nvarchar(100)
);
insert into Something(sometext, somenum) values ('first', 1);
insert into Something(sometext, somenum) values ('second', 2);
insert into Something(sometext, somenum) values ('third', 3);
select * from Something

and that custom type is defined

CREATE TYPE ArrayOfString AS TABLE(V nvarchar(255) NULL)

...following C# program uses native parameters relying on custom type defined above

(following example is present in repo as VSCode project)

#!/usr/bin/env dotnet-script
#r "nuget: System.Data.SqlClient, 4.6.1"
#r "nuget: AsyncPocoDpy, 2.0.2.1"

using AsyncPoco;

//POCO classes either written manually or generated by project similar to https://github.com/d-p-y/PocoSchemaCodeGeneration
[ExplicitColumns]
[TableName("something")]
[PrimaryKey("id")]
public class Something {
    [Column] public int id {get; set;}
    [Column] public int somenum {get; set;}
    [Column] public string sometext {get; set;}
}

var rawDbConn = new System.Data.SqlClient.SqlConnection(@"Data Source=localhost\SQLEXPRESS;Initial Catalog=databasename;Integrated security=true");
rawDbConn.Open();

var dbConn = new AsyncPoco.Database(rawDbConn);

dbConn.ShouldExpandSqlParametersToLists = false; //in SQL Server we need to opt-in for feature...

//...and guide SQL Server which custom types to use
dbConn.PrepareParameterValue = (paramKind, paramValue) => {
    switch(paramKind) {
        case AsyncPoco.Database.ParameterKind.Collection:
            if (paramValue is string[] arrayOfString) {
                var dt = new System.Data.DataTable();
                dt.Columns.Add(new System.Data.DataColumn("V", typeof(string)));
                foreach (var x in arrayOfString) {
                    dt.Rows.Add(x);
                }

                return new System.Data.SqlClient.SqlParameter(){
                    //ParameterName is supplied later by AsyncPoco
                    SqlDbType = System.Data.SqlDbType.Structured,
                    TypeName = "ArrayOfString",
                    Value = dt
                };
            }
            //if you need more types (such as ArrayOfInt): create type in T-SQL and add support here
            throw new Exception($"unsupported collection type {paramValue.GetType().FullName}");

        default: return paramValue;
    }
};

object myCollectionParameter = new [] {"first", "second"};
//use 'object' above to prevent wrong method overload resolution in AsyncPoco (variable params)

var rawRows = 
    await dbConn.FetchAsync<Something>(
        "select * FROM Something WHERE sometext in (select V from @0)", 
        myCollectionParameter
    );

var rows = rawRows.OrderBy(x => x.sometext).ToList();

Console.WriteLine($"Retrieved {rows.Count} rows having somenums: {String.Join(',', rows.Select(x => x.somenum))}");

...and produces given output:

Retrieved 2 rows having somenums: 1,2