大佬教程收集整理的这篇文章主要介绍了如何映射 postresql 中存储过程的结果集，其中我使用 Entity Framework Core 返回多个游标，大佬教程大佬觉得挺不错的，现在分享给大家，也给大家做个参考。
我正在研究存储过程以从数据库中获取一些统计信息，但我不想在代码端执行此操作，因为有大量数据要处理，并且在执行后我需要调用一些表，很多将使用资源并且可以通过数据库查询廉价制作的东西。所以我想出了看起来像这样的存储过程：
CREATE OR replaCE FUNCTION PUBliC.get_statistics_info(severityref refcursor,eventsref refcursor)
RETURNS SetoF refcursor
LANGUAGE 'plpgsql'
AS $BODY$
BEGIN
OPEN severityref FOR
SELECT
si."VarID",si."Date",si."SystemID",si."Systemname",SUM(ss."InfoCounter") AS info,SUM(ss."WarningCounter") AS warn,SUM(ss."NonFatalCounter") AS nfatal,SUM(ss."FatalCounter") AS fatal,SUM(ssm."InfoCounter") AS minfo,SUM(ssm."WarningCounter") AS mwarn,SUM(ssm."NonFatalCounter") AS nfatal,SUM(ssm."FatalCounter") AS mfatal
FROM "StatisticsInfo" AS si
left JOIN "StatistiCSSeverity" AS ss ON si."ID" = ss."StatisticsInfoID"
left JOIN "StatistiCSSeveritymaintenance" AS ssm ON si."ID" = ssm."StatisticsInfoID"
GROUP BY si."VarID",si."Systemname";
RETURN NEXT severityref;
OPEN eventsref for
SELECT
si."VarID",se."EventID" AS eventID,se."Counter" AS eventcounter,sed."Symbol" AS eventsymbol,sem."EventID" AS mseventID,sem."Counter" AS mseventcounter,msed."Symbol" AS meventsymbol
FROM "StatisticsInfo" AS si
left JOIN "StatisticsEvent" AS se ON si."ID" = se."StatisticsInfoID"
left JOIN "EventDeFinitions" AS sed ON se."EventID" = sed."decimal"
left JOIN "StatisticsEventMaintenance" AS sem ON si."ID" = sem."StatisticsInfoID"
left JOIN "EventDeFinitions" AS msed ON sem."EventID" = msed."decimal";
RETURN NEXT eventsref;
END
$BODY$;
它使用 2 个游标汇总统计信息，因此我应该得到 2 组数据。但现在我有一个问题如何接收它。我的数据库操作使用实体框架核心，所以我做了这样的事情。
服务电话：
public async ValueTask<IEnumerable<object>> GetCombinedStatisticsInfo()
{
try
{
using var unitOfWork = UnitOfWorkFactory.CreateGeneralContext();
//I MADE THIS MAPPing AS TEST TO checK what WILL BE RETURNED
var map = new Func<DbDataReader,object>(reader => new
{
first = reader[0]
});
const String procedureCall = "BEGIN; SELECT get_statistics_info('severity','events'); FETCH ALL IN \"severity\"; FETCH ALL IN \"events\"; COMMIT;";
var result = await unitOfWork.GeneralRepository.ExecuteSqquery(procedureCall,map);
unitOfWork.dispose();
return result;
}
catch (Exception eX)
{
logger.Error(eX);
throw;
}
}
存储库：
public async ValueTask<IEnumerable<TResponse>> ExecutesqlProcedure<TResponse>(String procedureCall,Func<DbDataReader,TResponse> map)
{
var transaction = await this.Context.Database.begintransactionAsync();
var result = await Executesql(procedureCall,map,CommandType.StoredProcedurE);
await transaction.CommitAsync();
return result;
}
private async ValueTask<IEnumerable<TResponse>> Executesql<TResponse>(String query,TResponse> map,CommandType commandTypE)
{
await using var command = this.Context.Database.GetDbConnection().CreateCommand();
command.CommandText = query;
command.Commandtype = commandType;
await this.Context.Database.openConnectionAsync();
await using var result = await command.ExecuteReaderAsync();
var entitIEs = new List<TResponse>();
while (await result.ReadAsync())
{
entitIEs.Add(map(result));
}
return entitIEs;
}
基本上它执行过程，但结果我得到的游标名称如下：{first: "severity";首先：“事件”}，所以我检查了我的数据库上的执行情况，它工作正常，我使用该事务和 fetch 获得了 2 组数据。所以我的问题是我在这里做错了什么？
所以我想出了这个我需要在单独执行存储过程并映射结果后在事务中添加每个获取，例如：
public async ValueTask<Dictionary<String,List<object>>> ExecuteGetStatisticsProcedure()
{
var transaction = await this.Context.Database.begintransactionAsync();
var entities = new Dictionary<String,List<object>>
{
{SeverityKey,new List<object>() },{EventsKey,new List<object>() }
};
try
{
async Task GetSeverity(DbCommand severityCommand)
{
const String fetchSeveritycursor = "FETCH ALL IN \"severity\";";
severityCommand.CommandText = fetchSeveritycursor;
await using var result = await severityCommand.ExecuteReaderAsync();
while (await result.ReadAsync())
{
entities[SeverityKey].Add(
new
{
//Map results
});
}
}
async Task GetEvents(DbCommand eventsCommand)
{
const String fetchEventcursor = "FETCH ALL IN \"events\";";
eventsCommand.CommandText = fetchEventcursor;
await eventsCommand.ExecuteReaderAsync();
await using var eventsResult = await eventsCommand.ExecuteReaderAsync();
while (await eventsResult.ReadAsync())
{
entities[EventsKey].Add(
new
{
//Map results
});
}
}
await using var command = this.Context.Database.GetDbConnection().CreateCommand();
command.Commandtype = commandType.Text;
await this.Context.Database.openConnectionAsync();
const String procedureCallProcedure = "SELECT * From get_statistics_info(\'severity\',\'events\');";
await this.Context.Database.ExecuteSqlRawAsync(procedureCallProcedurE);
await GetSeverity(command);
await GetEvents(command);
await transaction.CommitAsync();
}
catch (Exception eX)
{
Logger.Error(eX);
await transaction.RollBACkAsync();
}
return entities;
}
以上是大佬教程为你收集整理的如何映射 postresql 中存储过程的结果集，其中我使用 Entity Framework Core 返回多个游标全部内容，希望文章能够帮你解决如何映射 postresql 中存储过程的结果集，其中我使用 Entity Framework Core 返回多个游标所遇到的程序开发问题。
如果觉得大佬教程网站内容还不错，欢迎将大佬教程推荐给程序员好友。
本图文内容来源于网友网络收集整理提供，作为学习参考使用，版权属于原作者。
如您有任何意见或建议可联系处理。小编QQ：384754419，请注明来意。