如何从Npgsql和存储过程返回自定义表类型？ [英] How to return custom table types from Npgsql and stored procedures?

问题描述

我试图基于隐式表类型返回自定义（复合）类型。

I'm trying to return a custom (composite) type based on an implicit table type.

我有此表定义：

CREATE TABLE app_user (id CHAR(36) PRIMARY KEY, name TEXT);

已映射到此类定义：

public class ApplicationUser
{
    public string Id { get; set; }
    public string Name { get; set; }
}

通过以下方式映射：

NpgsqlConnection.MapCompositeGlobally<ApplicationUser>("app_user");

我正在尝试使用此存储过程返回记录：

And I'm trying to return a record with this stored procedure:

CREATE FUNCTION find_by_id(user_id app_user.id%TYPE) RETURNS app_user AS $$
DECLARE
    found_user app_user;
BEGIN
    SELECT *
    FROM app_user
    WHERE id = user_id
    INTO found_user;

    RETURN found_user;
END
$$ LANGUAGE plpgsql STABLE SECURITY DEFINER;

我从C＃打电话时是这样的：

Which I'm calling from C# like this:

ApplicationUser user;
using (NpgsqlConnection db = new NpgsqlConnection(this.connectionString))
{
    db.Open();
    using (NpgsqlCommand cmd = new NpgsqlCommand("find_by_id", db))
    {
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("user_id", userId);
        object result = cmd.ExecuteScalar();
        user = result == DBNull.Value ? null : (ApplicationUser)result;
    }
}

但是在投射<$ c $时出现异常c>结果到 ApplicationUser ：

InvalidCastException: Unable to cast object of type 'System.String' to type 'MyApp.ApplicationUser'.

这显然是因为结果只是字符串 Id ，但为什么没有 result 我的复合 app_user 对象？

This is clearly because result is just the string Id, but why isn't result my composite app_user object?

我也尝试使用out参数返回对象，但遇到了完全相同的异常。我想使用Npgsql做什么，还是必须在C＃中从各个列中手动重建 ApplicationUser 对象？

I've also tried to return the object with an out parameter, but ran into the exact same exception. Is what I'm trying to do possible with Npgsql, or do I have to re-build the ApplicationUser object manually in C# from the individual columns?

我正在使用Npgsql v3.2.0和PostgreSQL v9.6。

I'm using Npgsql v3.2.0 and PostgreSQL v9.6.

推荐答案

我想到了。原来比我想象的要容易。我需要更改的只是从C＃调用存储过程的方式。

Looks like I figured it out. Turned out to be easier than I thought. All I needed to change was the way the stored procedure was called from C#.

ApplicationUser user;
using (NpgsqlConnection db = new NpgsqlConnection(this.connectionString))
{
    db.Open();
    using (NpgsqlCommand cmd = new NpgsqlCommand("SELECT find_by_id(@user_id);", db))
    {
        cmd.Parameters.AddWithValue("user_id", userId);
        object result = cmd.ExecuteScalar();
        user = result == DBNull.Value ? null : (ApplicationUser)result;
    }
}

我更喜欢另一种调用存储过程的方式，但这至少行得通！

I preferred the other way of invoking the stored procedure, but at least this works!

这篇关于如何从Npgsql和存储过程返回自定义表类型？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！