try
{
using (SqlConnection conn = new SqlConnection(conStr))
{
var year = req.sysmonth!=null?Convert.ToInt32(req.sysmonth.Substring(0, 4)) :DateTime.Now.Year;
var lyear = year - 1;
var smonth = "";
var sql = "";
int month = 0;
decimal total = 0;
decimal total1 = 0;
IncomeContrastivebyYear listy = new IncomeContrastivebyYear();
listy.syear = year;
IncomeContrastivebyYear listly = new IncomeContrastivebyYear();
listly.syear = lyear;
// 遍历月份
while (month < 12)
{
month++;
smonth = CommonUtil.RightStr(("00" + month.ToString()), 2);
if (req.classid == "123") {
sql = $@"select total = isnull(SUM(day),0) from rep_hotel_all where charindex(rtrim(hotelid), '{ hotelids }')>0
and class in (012000,030000,040000,050000,060000,070000,080000,090000) and syear='{ year }' and smonth='{ smonth }';
select total = isnull(SUM(day),0) from rep_hotel_all where charindex(rtrim(hotelid), '{ hotelids }')>0
and class in (012000,030000,040000,050000,060000,070000,080000,090000) and syear='{ lyear }' and smonth='{ smonth }';";
}
else
{
sql = $@"select total = isnull(SUM(day),0) from rep_hotel_all where charindex(rtrim(hotelid), '{ hotelids }')>0
and class='{ req.classid }' and syear='{ year }' and smonth='{ smonth }';
select total = isnull(SUM(day),0) from rep_hotel_all where charindex(rtrim(hotelid), '{ hotelids }')>0
and class='{ req.classid }' and syear='{ lyear }' and smonth='{ smonth }';";
}
using (var results = conn.QueryMultiple(sql))
{
var j = 0;
// 遍历结果集,走两遍,第一遍返回total,第二遍返回total1
while(!results.IsConsumed)
{
j++;
// 读取当前结果集
var resb = results.Read<TotalTotal1Res>().ToList().FirstOrDefault();
if (resb != null)
{
if (j == 1)
{
total = resb.total;
}
else
{
total1 = resb.total;
}
}
else
{
if (j == 1)
{
total = 0;
}
else
{
total1 = 0;
}
}
}
}
if (month == 01)
{
listy.jan = total;
listly.jan = total1;
}
if (month == 02)
{
listy.feb = total;
listly.feb = total1;
}
if (month == 03)
{
listy.mar = total;
listly.mar = total1;
}
if (month == 04)
{
listy.apr = total;
listly.apr = total1;
}
if (month == 05)
{
listy.may = total;
listly.may = total1;
}
if (month == 06)
{
listy.jun = total;
listly.jun = total1;
}
if (month == 07)
{
listy.jul = total;
listly.jul = total1;
}
if (month == 08)
{
listy.aug = total;
listly.aug = total1;
}
if (month == 09)
{
listy.sep = total;
listly.sep = total1;
}
if (month == 10)
{
listy.oct = total;
listly.oct = total1;
}
if (month == 11)
{
listy.nov = total;
listly.nov = total1;
}
if (month == 12)
{
listy.dec = total;
listly.dec = total1;
}
}
list.Add(listy);
list.Add(listly);
result.Result = list;
result.TotalRecords = list.Count();
return Success(result);
}
}
catch (Exception ex)
{
return Failed(ex.Message);
}
备注:仅做多条同时查询的示例,鉴于效率因素,实际查询仍采用其他优化方法。