我想通过传递一个用户定义的类型参数来调用Postgres DB中的存储过程,我想使用Dapper和npgsql最新的稳定版本7。0.
我可以使用npgsql版本6来实现这一点。0.0,代码如下:


// 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 = "john.doe@example.com", Phone = "555-1234"},
                    new CustomerType {Name = "Jane Smith", Email = "jane.smith@example.com", 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.");

即使我能够使这段代码与升级版本的npgsql(v7)一起工作。0.0),GlobalTypeMapper.MapComposite方法在此版本中已过时。
文件中提到
NpgsqlDataSource在Npgsql 7中引入。0,并且是管理类型Map的推荐方法
然而,我找不到一个合适的文档来使NpgsqlDataSource与Dapper一起使用。
使用npgsql版本6的一个缺点。0.0的问题是我需要调用一个过程并提供它的参数,如上面的代码所示(CALL insert_customer_list(@cust_list)),使用npgsql版本7可以避免这一点吗?
我曾试图使这一工作的版本7与以下代码的变化,但其抛出错误系统。NotSupportedException:’CLR类型DapperNpgsqlExample。Npgsql或PostgreSQL不支持CustomerType。要将其与PostgreSQL组合一起使用,您需要指定DataTypeName或Map它,请参阅文档。’
代码修改

// 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 = "john.doe@example.com", Phone = "555-1234"},
                    new CustomerType {Name = "Jane Smith", Email = "jane.smith@example.com", 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.");

postgresql
来源:https://stackoverflow.com/questions/76086800/invoke-postgres-stored-procedur

文档更新时间: 2023-06-19 18:32   作者:admin