Invoke Postgres Stored Procedure with User Defined Type list parameter using Dapper and Npgsql 7.0.0+

Question

I want to call a stored procedure in a Postgres DB by passing a user defined type parameter and I want to use Dapper and npgsql latest stable version which is 7.0.

I was able to implement this using npgsql version 6.0.0 with this code:

// Register the composite type mapper
NpgsqlConnection.GlobalTypeMapper.MapComposite<CustomerType>("customer_type");

var connectionString = "User ID=postgres;Password=test;Host=localhost;Port=5432;Database=testDB;Pooling=true;";

using var con = new NpgsqlConnection(connectionString);

con.Open();

// Call the insert_customer_list stored procedure
var customers = new[]
                {
                    new CustomerType {Name = "John Doe", Email = "[email protected]", Phone = "555-1234"},
                    new CustomerType {Name = "Jane Smith", Email = "[email protected]", Phone = "555-5678"}
                };

var parameters = new DynamicParameters();

parameters.Add("cust_list", customers);

con.Execute("CALL insert_customer_list(@cust_list)", parameters);

Console.WriteLine("Stored procedure executed successfully.");

Even though I am able to make this code work with the upgraded version of npgsql (v7.0.0), the GlobalTypeMapper.MapComposite method is obsolete in this version.

The documentation mentions that

NpgsqlDataSource was introduced in Npgsql 7.0, and is the recommended way to manage type mapping

However I couldn't find a proper documentation to make NpgsqlDataSource to use with Dapper.

One disadvantage of using npgsql version 6.0.0 is that I need to call a procedure and provide its parameter as shown in the above code (CALL insert_customer_list(@cust_list)), can this be avoided using npgsql version 7?

I had tried to make this work for version 7 with below code changes but its throwing error System.NotSupportedException: 'The CLR type DapperNpgsqlExample.CustomerType isn't natively supported by Npgsql or your PostgreSQL. To use it with a PostgreSQL composite you need to specify DataTypeName or to map it, please refer to the documentation.'

Code Modified

// Register the composite type mapper
                ////NpgsqlConnection.GlobalTypeMapper.MapComposite<CustomerType>("customer_type");

                var connectionString = "User ID=postgres;Password=test;Host=localhost;Port=5432;Database=testDB;Pooling=true;";
                var dataSourceBuilder = new NpgsqlDataSourceBuilder(connectionString);
                dataSourceBuilder.MapComposite<CustomerType>("customer_type");
                using var dataSource = dataSourceBuilder.Build();
 

                using var con = new NpgsqlConnection(connectionString);
                con.Open();

 

                // Call the insert_customer_list stored procedure
                var customers = new[]
                {
                    new CustomerType {Name = "John Doe", Email = "[email protected]", Phone = "555-1234"},
                    new CustomerType {Name = "Jane Smith", Email = "[email protected]", Phone = "555-5678"}
                };
                var parameters = new DynamicParameters();
                parameters.Add("cust_list", customers);
                con.Execute("insert_customer_list", parameters, commandType: CommandType.StoredProcedure);

 

                Console.WriteLine("Stored procedure executed successfully.");

Answer 1

Got this resolved with the help of this following post https://github.com/dotnet/runtime/issues/64812

Checkout the modified code as per this post.

var connectionString = "User ID=postgres;Password=test;Host=localhost;Port=5432;Database=testDB;Pooling=true;";
var dataSourceBuilder = new NpgsqlDataSourceBuilder(connectionString);
dataSourceBuilder.MapComposite<CustomerType>("customer_type");

using var dataSource = dataSourceBuilder.Build();
    
using (var connection = dataSource.OpenConnection())
{
    // Use the connection as usual
    // Call the insert_customer_list stored procedure
    var customers = new[]
    {
        new CustomerType {Name = "John Doe", Email = "[email protected]", Phone = "555-1234"},
        new CustomerType {Name = "Jane Smith", Email = "[email protected]", Phone = "555-5678"}
    };
    var parameters = new DynamicParameters();
    parameters.Add("cust_list", customers);
    connection.Execute("insert_customer_list", parameters, commandType: CommandType.StoredProcedure);
    
    Console.WriteLine("Stored procedure executed successfully.");
}