通过存储过程，查询数据，结果转换为集合 传给前段

存储过程的优点是：可以传参，执行效率高，当遇到条件比较多，加载速度比较慢的功能，可以采用，使用存储过程查询数据，再将查询结果转换为集合，进行接口传值

 1 　　　　　[HttpPost]
 2         public ActionResult List(View_Hos_UserPostSearch search)
 3         {
 4             try
 5             {
 6                 var account = MobileAccount.GetAccount;
 7                 var PostNum = string.Empty;
 8                 var PostNum1 = string.Empty;
 9                 if (account.RoleType == RoleType.MR.GetHashCode())
10                 {
11                     PostNum = account.PostNum;
12                 }
13                 if (account.RoleType == RoleType.DM.GetHashCode())
14                 {
15                     PostNum1 = account.PostNum;
16                 }
17 
18                 var where = string.Empty;
19 
20                 //关键字
21                 if (!string.IsNullOrWhiteSpace(search.KeyWords))
22                 {
23                     search.KeyWords = Character.ReplaceSqlKey(search.KeyWords, int.MaxValue);
24                     if (search.KeyWords.Length > 10)
25                     {
26                         search.KeyWords = search.KeyWords.Substring(0, 10);
27                     }
28                     where += $" AND (Name LIKE '%{search.KeyWords}%' OR Province LIKE '%{search.KeyWords}%' OR City LIKE '%{search.KeyWords}%' OR Code LIKE '%{search.KeyWords}%') ";
29                 }
30 
31                 var totalUserCount = 0;
32                 var sql = string.Empty;
33                 if (string.IsNullOrWhiteSpace(where))
34                 {
35                     sql = $"EXEC GetHosUserPost_All '{PostNum}', '{PostNum1}', {search.PageIndex}, {search.PageSize},  '','ID'";
36                 }
37                 else
38                 {
39                     sql = $"EXEC GetHosUserPost_All '{PostNum}', '{PostNum1}', {search.PageIndex}, {search.PageSize},  \"{where}\",'ID'";
40                 }
41 
42                 var list = service.GetObjects<View_Hos_UserPost>(sql);
43 
44                 var pageList = new EntityPagedList<View_Hos_UserPost>(list, search.PageIndex, search.PageSize, totalUserCount);
45 
46                 return SuccessMsg(pageList);
47             }
48             catch (Exception ex)
49             {
50                 var msg = ex.Message;
51                 return ErrorMsg(msg);
52                 throw;
53             }
54         }

 

posted @ 2023-11-07 16:18  小易儿  阅读(22)  评论(0)  编辑  收藏  举报