c#创建后端接口

c#创建后端接口的框架如下图结构所示：

共分为四个步骤：

• 先写好数据库查询语句

• 在控制器（Controllers）中建立后端接口函数

/// <summary>
/// 获取performance bySales页面数据
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
 
[HttpPost]
public IActionResult GetPerformanceBySales(QueryDescriptor model)
{
   var list = service.GetPerformanceBySales(model);
   // return Ok(JsonConvert.SerializeObject(list));
   return MstResult.Success(list);
}

• 在 Interface 中为接口函数配置接口规范

object GetPerformanceBySales(QueryDescriptor model);

• 在服务（Service）中为接口函数配置服务：数据库查询语句、逻辑处理等皆在此文件中进行

/// <summary>
        /// 获取performance bySales页面数据
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public object GetPerformanceBySales(QueryDescriptor model)
        {
            //方法一：利用c#中的SqlSugar方法将数据库的查询语句转换成该方法的函数形式
            //table
            //sql语句： SELECT business.iotype AS TYPE,business.sales AS SalesName,business.customerid AS CustID,business.customername AS CustName, COUNT(business.id) AS Shipment, SUM(business.volume) Volume,SUM(business.receivable) AS SalesAmount, SUM(business.profit) AS GrossProfit department.name AS Company FROM business LEFT JOIN department ON business.departmentid = department.code GROUP BY TYPE ,SalesName,CustID,CustName, Company
            var listTable = dal.SqlSugarClient().Queryable<Business, Department>
                ((s1, s2) => new Object[] { JoinType.Left, s1.Departmentid == s2.Ccode })
                .GroupBy((s1, s2) => new { s1.Iotype, s1.Sales, s1.Rccode, s1.Rccodename, s2.Name })
                .Where( s1 =>  s1.BusinessOwner == "SZX" )
                .Select((s1, s2) => new { Type = s1.Iotype, SalesName = s1.Sales, CustID = s1.Rccode, CustName = s1.Rccodename, Shipment = SqlFunc.AggregateCount(s1.Id), Volume = SqlFunc.AggregateSum(s1.Volume), SalesAmount = SqlFunc.AggregateSum(s1.Receivable), GrossProfit = SqlFunc.AggregateSum(s1.Profit) })
                .MergeTable()
                .ToPageList(1, 50);
 
            //Type = s1.Iotype  等号前的变量（Type）为数据库字段的别名
 
 
            //方法二：直接使用 $ 符号将数据库查询语句包含起来
            //string strsql4 = $"SELECT rccodename,SUM(profit) AS grossprofit FROM business WHERE {sqlWhere} GROUP BY rccodename ORDER BY grossprofit DESC";
            //DataTable listTable4 = ((SugarRepository)dal).DbContext.Ado.GetDataTable(strsql4);
            //var obj = new { shipmentTotal = list1, shipment = listTable1, volumeTotal = list2, volume = listTable2, salesAmountToatl = list3, salesAmount = listTable3, grossProfitTotal = list4, grossProfit = listTable4 };
            //return obj;
 
            return listTable;
 
        }
 

补充：在页面中传入参数去后端查询出指定的数据

/// <summary>
        /// 获取performance bySales页面数据
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public object GetPerformanceBySales(QueryDescriptor model)
        {
 
            //在页面中传入参数去后端查询出指定的数据
            string BusinessType = string.Empty;  //考虑到性能问题，所以初始化字段时对它赋值为empty
            //遍历model.Conditions中的数据，根据前端传入的条件获取相对应的数据
            model.Conditions.ForEach(s =>
            {
                if (s.Key == "business_owner")
                {
                    BusinessType = s.Value.ToString();
                }
            });
            //table
            //SELECT business.iotype AS TYPE,business.sales AS SalesName,business.customerid AS CustID,business.customername AS CustName, COUNT(business.id) AS Shipment, SUM(business.volume) Volume,SUM(business.receivable) AS SalesAmount, SUM(business.profit) AS GrossProfit department.name AS Company FROM business LEFT JOIN department ON business.departmentid = department.code GROUP BY TYPE ,SalesName,CustID,CustName, Company
            var listTable = dal.SqlSugarClient().Queryable<Business, Department>
                ((s1, s2) => new Object[] { JoinType.Left, s1.Departmentid == s2.Ccode })
                .GroupBy((s1, s2) => new { s1.Iotype, s1.Sales, s1.Rccode, s1.Rccodename, s2.Name })
                .Where( s1 =>  s1.BusinessOwner == BusinessType ) //页面中传入参数去后端查询出指定的数据:BusinessOwner为数据库字段
                .Select((s1, s2) => new { Type = s1.Iotype, SalesName = s1.Sales, CustID = s1.Rccode, CustName = s1.Rccodename, Shipment = SqlFunc.AggregateCount(s1.Id), Volume = SqlFunc.AggregateSum(s1.Volume), SalesAmount = SqlFunc.AggregateSum(s1.Receivable), GrossProfit = SqlFunc.AggregateSum(s1.Profit) })
                .MergeTable()
                .ToPageList(1, 50);
 
            return listTable;
 
        }

页面中传参形式为：

{
  "pageSize": 0,   //后端自定义的行数
  "pageIndex": 0,  //后端自定义的页码
    //排序
  "orderBys": [
    {
      "sort": "string",
      "order": 0
    }
  ],
    //条件
  "conditions": [
    {
      "key": "business_owner",  //数据库对应字段
      "value":"SZX",            //数据库对应字段的值
      "operator": 0,
      "character": 0
    }
  ]
}

c#中创建一个输出结果为json的对象。

JObject data = new JObject {  { "Alleen", 43024 }, { "Lydia", 38024 }, { "Lrina", 35224 }, { "Yanni", 34321 }, { "Sasaki", 32324 }, { "Morita", 31992 }, { "Kijyo", 29238 }, { "Kobayashi", 28324 }, { "William", 27345 }, { "Lina", 89313 }  };
var obj = new { data };
return obj;
 
 
//输出结果：
//{"data":{"Alleen":43024,"Lydia":38024,"Lrina":35224,"Yanni":34321,"Sasaki":32324,"Morita":31992,"Kijyo":29238,"Kobayashi":28324,"William":27345,"Lina":89313}}
 

创建对象并插入五条数据。

JObject item = new JObject { { "type","AEX"}, { "company","KWESZX" }, { "department","Sales" },{ "team","Div1." },{ "salesname","Allen"},{ "custid","302174961" },{ "custname","IFLIGHT TECHNOLGY CO,LTD" },{ "shipment","35" },{ "volume","72.36" },{ "salesamount","468962.23" },{"grossprofit","98005.6" } };
            tableData.Add(item);
            tableData.Add(item);
            tableData.Add(item);
            tableData.Add(item);
            tableData.Add(item);
             var obj = new { tableData };
            return obj;