如何将参数传递给 postgre 函数并使用 ExecuteReader 获取数据? [英] How to pass the parameter to a postgre function and get data using ExecuteReader?

问题描述

我正在尝试使用 C# 应用程序中的 ExecuteReader 从表中检索所有列.Db 是 postgre.为了测试，我按照教程创建了一个控制台应用程序，该教程确实展示了如何使用函数而不是传递参数进行查询.用于测试的控制台应用程序功能

I am trying to retrieve all the columns from a table using ExecuteReader in C# application.Db is postgre.To test i created a console application following a tutorial which did showed how to query using a Function but not with passing parameters.The console application function for test

    static void Main(string[] args)
    {
        // Connect to a PostgreSQL database
        NpgsqlConnection conn = new NpgsqlConnection("Server=localhost;User Id=postgres; " +
            "Password=pes;Database=pmc;");
        conn.Open();

        // Define a query
        NpgsqlCommand command = new NpgsqlCommand("SELECT * from audit.exception_gl_accounts()", conn);

        // Execute the query and obtain a result set
        NpgsqlDataReader dr = command.ExecuteReader();

        // Output rows
        while (dr.Read())
            Console.Write("{0}\t{1} \n", dr[0], dr[1]);

        conn.Close();
    }
}

在 NpgsqlCommand 中，我将不带参数的查询发送到函数 audit.exception_gl_accounts 并且运行良好.现在我如何将参数传递给这样的函数

Here in NpgsqlCommand i sent the query without parameter to the function audit.exception_gl_accounts and it worked well.Now How do i pass a parameter to a function like this

"SELECT * FROM sms.get_accounts_info(@AccountNumber);

"SELECT * FROM sms.get_accounts_info(@AccountNumber);

我正在尝试使用此函数检索所有 5 列并获取这些对象

I am trying to retrieve all 5 columns using this function and get those objects

    public static string GetAccountInfo(string accountNumber)
    {
        NpgsqlConnection conn = new NpgsqlConnection("Server=localhost;User 
                            Id=postgres; " + "Password=pes;Database=pmc;");
        conn.Open();
        NpgsqlCommand command = new NpgsqlCommand("SELECT * FROM 
                           sms.get_accounts_info(@AccountNumber); ", conn);
        command.Parameters.AddWithValue("@AccountNumber", accountNumber);
        NpgsqlDataReader dr = command.ExecuteReader();
        while (dr.Read())
            Console.Write("{0}\t{1} \n", dr[0], dr[1]);
            return dr.ToString();
    }

使用第二个示例代码会出现此错误:

Using the second sample code gives this error :

{"accountNumber": "Npgsql.ForwardsOnlyDataReader",余额":空，利率":0，账户名":空，帐户类型":空}

{ "accountNumber": "Npgsql.ForwardsOnlyDataReader", "balance": null, "interestRate": 0, "accountName": null, "accountType": null }

感谢任何帮助.

细节更新

控制器

[HttpPost]
[ActionName("info")]
public IHttpActionResult GetAccountInfo([FromBody]AccountInfo 
accountinfo)
 {
accountinfo.accountNumber = BusinessLayer.Api.AccountHolderApi.GetAccountInfo
          (accountinfo.accountNumber);
            return Ok(accountinfo);
 }

帐户信息类

public class AccountInfo
    {
      public string accountNumber { get; set; }
      public string balance { get; set; }
      public int interestRate { get; set; }
      public string accountName { get; set; }
      public string accountType { get; set; }
    }

URI

http://localhost:8080/v1/accounts/info

获取账户信息

CREATE OR REPLACE FUNCTION sms.get_accounts_info(IN account_number_ character varying)
  RETURNS TABLE(account_number character varying, account_name text, product character varying, interest_rate numeric, balance money) AS
$BODY$
BEGIN
    RETURN QUERY(
        SELECT a.account_number,
        c.customer_name,
            p.deposit_product_name, 
            a.interest_rate::numeric, deposit.get_balance(account_number_)
        FROM deposit.account_holders a 
        JOIN core.customers_view  c ON a.customer_id = c.customer_id
        JOIN core.deposit_products p ON a.deposit_product_id = p.deposit_product_id
        WHERE a.account_number = $1
    );
END
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;
ALTER FUNCTION sms.get_accounts_info(character varying)
  OWNER TO postgres;

推荐答案

如果没有实体框架，您需要编写从 datareader 读取值到您的 AccountInfo 类的实例的代码:

Without entity framework, you need to write the code that reads the values from the datareader into an instance of your AccountInfo class:

public static AccountInfo GetAccountInfo(string accountNumber)
{
    AccountInfo result = null;
    using(var conn = new NpgsqlConnection("..."))
    {
        conn.Open();
        using(var command = new NpgsqlCommand("SELECT * FROM sms.get_accounts_info(@AccountNumber); ", conn))
        {
            command.Parameters.AddWithValue("@AccountNumber", accountNumber);
            using(var dr = command.ExecuteReader())
            {
                if(dr.HasRows && dr.Read())
                {
                    result = new AccountInfo { 
                        accountNumber = dr["accountNumber"].ToString(),
                        balance = dr["balance"].ToString(),
                        interestRate = Convert.ToInt32(dr["interestRate"]),
                        accountName = dr["accountName"].ToString()
                    };
                }
            }
        }
    }
    return result;
}

请注意，函数的返回类型已更改为AccountInfo，以前是字符串.此外，它仅限于读取一条记录，如果对 sms.get_accounts_info 的一次调用可以返回多条记录，那就是另一回事了.我只是假设 account_number 是 account_holders 表中的主键.

Note that the return type of the function has been changed to AccountInfo, previously string. Also, it is limited to reading just one record, If one call to sms.get_accounts_info could return more than one record, it is a different story. I just assumed that account_number is a primary key in the account_holders table.

有些细节需要注意，例如balance在数据库中是money，在class中是string.另外我不知道product(数据库)和accountType(类)是否以及如何对应，所以我省略了它.

Some details need your attention, for example balance is money in database, but string in the class. Also I did not know if and how product (database) and accountType (class) would correspond, so I omitted it.

数据库连接、命令和数据读取器是 IDisposable 并且应该包装在 using 块中.

Database connections, commands, and datareaders are IDisposable and should be wrapped in using blocks.

这篇关于如何将参数传递给 postgre 函数并使用 ExecuteReader 获取数据?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！