C# 和 PostgreSQL

任何人都可以向我展示使用从 PLSQL 返回的游标到 C# 代码的工作示例吗？

我找到了许多示例，展示了如何用返回的数据填充 dataSet ，但我找不到如何使用该游标和 DataReader ，因此我有 {unnamed portal} 。

NpgsqlTransaction tr = (NpgsqlTransaction) Connection.BeginTransaction();
NpgsqlCommand cursCmd = new NpgsqlCommand("someStoredProcedure(:inRadius)", (NpgsqlConnection) Connection);
cursCmd.Transaction = tr;
NpgsqlParameter rf = new NpgsqlParameter("ref", NpgsqlTypes.NpgsqlDbType.Refcursor);
rf.Direction = ParameterDirection.InputOutput;
cursCmd.Parameters.Add(rf);

我必须添加这个才能正确使用 NpgsqlDataReader myReader;:

tr.Commit();

当我在 sql 命令之后编写 fetch 时，它​​可以工作但不合适。

最佳答案

供你引用:

/// <summary>
/// Get data from the returning refcursor of postgresql function
/// </summary>
/// <param name="FunctionName">Function name of postgresql</param>
/// <param name="Parameters">parameters to pass to the postgresql function</param>
/// <param name="ErrorOccured">out bool parameter to check if it occured error</param>
/// <returns></returns>
public List<DataTable> GetRefCursorData(string FunctionName, List<object> Parameters, out bool ErrorOccured)
{
    string connectstring = ""; //your connectstring here
    List<DataTable >  dtRtn =new List<DataTable>();
    NpgsqlConnection connection = null;
    NpgsqlTransaction transaction = null;
    NpgsqlCommand command = null;
    try
    {
        connection = new NpgsqlConnection(connectstring);
        transaction = connection.BeginTransaction();
        command = new NpgsqlCommand();
        command.Connection = connection;
        command.CommandType = CommandType.StoredProcedure;
        command.CommandText = FunctionName;
        command.Transaction = transaction;
        //
        if (Parameters != null)
        {
            foreach (object item in Parameters)
            {
                NpgsqlParameter parameter = new NpgsqlParameter();
                parameter.Direction = ParameterDirection.Input;
                parameter.Value = item;
                command.Parameters.Add(parameter);
            }
        }
        //
        NpgsqlDataReader dr = command.ExecuteReader();
        while (dr.Read())
        {
            DataTable dt = new DataTable();
            command = new NpgsqlCommand("FETCH ALL IN " + "\"" + dr[0].ToString() + "\"", Connection); //use plpgsql fetch command to get data back
            NpgsqlDataAdapter da = new NpgsqlDataAdapter(command);
            da.Fill(dt);
            dtRtn.Add(dt); //all the data will save in the List<DataTable> ,no matter the connection is closed or returned multiple refcursors
        }
        ErrorOccured = false;
        transaction.Commit();
    }
    catch
    {
        //error handling ...
        ErrorOccured = true;
        if (transaction != null) transaction.Rollback();
    }
    finally
    {
        if (connection != null) connection.Close();
    }
    return dtRtn;
}

关于C# 和 PostgreSQL，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/2037000/