c#创建后端接口
c#创建后端接口的框架如下图结构所示:
![](https://img2020.cnblogs.com/blog/1487352/202012/1487352-20201222172127793-2122030832.png)
共分为四个步骤:
-
先写好数据库查询语句
-
在控制器(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;