.NET MVC+ EF+调用存储过程 多表联查以及VIEW列表显示

直接上干活，至于网上的一大堆处理方式不予评论，做好自己的就是最好的，供大家不走弯路

1、view页面

<link href="~/Content/bootstrap.css" rel="stylesheet" /><div class="well"><table class="table"><tr><th>用户名</th><th>地址</th><th>订单编号</th><th>城市代号</th><th>时间</th><th>订单状态</th></tr>@foreach (var item in ViewBag.dyObject){<tr><td>@item.UserName </td><td>@item.LocalAddress </td><td>@item.BargainOrderCode </td><td>@item.CityCode </td><td>@item.UpdateTime </td><td>@item.OrderStatus </td></tr>}</table></div>

 2、Controller 控制器代码

 public async Task<ActionResult> UserOrder(){#region 使用存储过程实现多表联查VIEW显示int lastID = 0;int pageSize = 10;SqlParameter[] Param ={new SqlParameter("@lastID", System.Data.SqlDbType.Int),new SqlParameter("@pageSize", System.Data.SqlDbType.Int)};if (lastID< 0){Param[0].Value = DBNull.Value;}else{Param[0].Value = lastID;}if (pageSize< 0){Param[1].Value = DBNull.Value;}else{Param[1].Value = pageSize;}var data = await _DbContext.Exec_SpAsync("SP_GetUserOrderList", Param);//foreach (Dictionary<string, object> item in data)//{//    string UserName = item["UserName"].ToString();//    string LocalAddress = item["LocalAddress"].ToString();//    string BargainOrderCode = item["BargainOrderCode"].ToString();//    string CityCode = item["CityCode"].ToString();//    DateTime UpdateTime = Convert.ToDateTime(item["UpdateTime"]);//    int OrderStatus = Convert.ToInt32(item["OrderStatus"]);//}List<dynamic> userList = new List<dynamic>();foreach (Dictionary<string, object> item in data){//userList.Add(new//{//    UserName = item["UserName"].ToString(),//    LocalAddress = item["LocalAddress"].ToString(),//    BargainOrderCode = item["BargainOrderCode"].ToString(),//    CityCode = item["CityCode"].ToString(),//    UpdateTime =Convert.ToDateTime(item["UpdateTime"]),//    OrderStatus =Convert.ToInt32(item["OrderStatus"])//  });      dynamic dyObject = new ExpandoObject();dyObject.UserName = item["UserName"].ToString();dyObject.LocalAddress = item["LocalAddress"].ToString();dyObject.BargainOrderCode = item["BargainOrderCode"].ToString();dyObject.CityCode = item["CityCode"].ToString();dyObject.UpdateTime = Convert.ToDateTime(item["UpdateTime"]);dyObject.OrderStatus = Convert.ToInt32(item["OrderStatus"]);userList.Add(dyObject);}ViewBag.dyObject = userList;return View();#endregion}

存储过程怎么用。 3、EF中多表查询操作的存储过程通用调用方法

/// <summary>/// 存储共用/// </summary>public static class StorageCommon{/// <summary>/// 带有参数的存储过程公共方法  获取信息集合  以及返回空值处理/// </summary>/// <param name="db"></param>/// <param name="sql"></param>/// <param name="sqlParams"></param>/// <returns></returns>public async static Task<ArrayList> Exec_SpAsync(this DefaultDbContext db, string sql, SqlParameter[] sqlParams){using (var cmd = db.Database.Connection.CreateCommand()){await db.Database.Connection.OpenAsync();cmd.CommandText = sql;cmd.CommandType = System.Data.CommandType.StoredProcedure;cmd.Parameters.AddRange(sqlParams);var dr = await cmd.ExecuteReaderAsync();var columnSchema = dr.GetColumnSchema();var data = new ArrayList();while (await dr.ReadAsync()){var item = new Dictionary<string, object>();foreach (var kv in columnSchema){if (kv.ColumnOrdinal.HasValue){var itemVal = dr.GetValue(kv.ColumnOrdinal.Value);item.Add(kv.ColumnName, itemVal.GetType() != typeof(DBNull) ? itemVal : "");}}data.Add(item);}dr.Dispose();return data;}}
完善后代码：

 public async static Task<ArrayList> Exec_SpAsync(this DefaultDbContext db, string sql, SqlParameter[] sqlParams){var data = new ArrayList();DbDataReader dr=null ;try{  using (var cmd = db.Database.Connection.CreateCommand()){await db.Database.Connection.OpenAsync();cmd.CommandText = sql;cmd.CommandType = System.Data.CommandType.StoredProcedure;cmd.Parameters.AddRange(sqlParams);//var dr = await cmd.ExecuteReaderAsync();dr = await cmd.ExecuteReaderAsync();var columnSchema = dr.GetColumnSchema();while (await dr.ReadAsync()){var item = new Dictionary<string, object>();foreach (var kv in columnSchema){if (kv.ColumnOrdinal.HasValue){var itemVal = dr.GetValue(kv.ColumnOrdinal.Value);item.Add(kv.ColumnName, itemVal.GetType() != typeof(DBNull) ? itemVal : "");}}data.Add(item);}}}catch (Exception ex){_Logger.Error("查询数据" + ex.Message);//throw new Exception("查询失败." + ex.Message);
            }finally{dr.Dispose();}return data;}

 

       /// <summary>/// 异步执行带有参数的存储过程公共方法  增删改操作以及返回带有输出的参数/// </summary>/// <param name="db"></param>/// <param name="sql"></param>/// <param name="sqlParams"></param>/// <returns></returns>public async static Task<int> ExecuteNonQueryAsync(this DefaultDbContext db, string sql, SqlParameter[] sqlParams){int numint;using (var cmd = db.Database.Connection.CreateCommand()){await db.Database.Connection.OpenAsync();cmd.CommandText = sql;cmd.CommandType = System.Data.CommandType.StoredProcedure;cmd.Parameters.AddRange(sqlParams);numint = await cmd.ExecuteNonQueryAsync();cmd.Connection.Close();}return numint;}

完善后代码：public async static Task<int> ExecuteNonQueryAsync999(this DefaultDbContext db, string sql, SqlParameter[] sqlParams){int numint=0;using (var cmd = db.Database.Connection.CreateCommand()){try{await db.Database.Connection.OpenAsync();cmd.CommandText = sql;cmd.CommandType = System.Data.CommandType.StoredProcedure;cmd.Parameters.AddRange(sqlParams);numint = await cmd.ExecuteNonQueryAsync();cmd.Connection.Close();}catch (Exception ex){                   _Logger.Error("执行数据" + ex.Message);//throw new Exception("提交失败." + ex.Message);
                }finally{cmd.Connection.Dispose();}return numint;}}

 

public class RetCode{public const int SUCCESS = 0;public const int ERROR = -1;}public class AsResult{private const int STATUS_CODE = 100;private const string CONTENT_TYPE = "application/json;charset=utf-8";private readonly static Dictionary<int, string> message = new Dictionary<int, string>(){{ RetCode.SUCCESS, "success" },{ RetCode.ERROR, "" },};public static ContentResult Success(Object data = null){var content = new{RetCode = RetCode.SUCCESS,Message = message[RetCode.SUCCESS],Data = data ?? new { }};return new ContentResult{//StatusCode = STATUS_CODE,ContentType = CONTENT_TYPE,Content = JsonConvert.SerializeObject(content).ToString()};}public static ContentResult Error(int code, string moreMsg = ""){string msg = "";if (message.ContainsKey(code)){msg = message[code];}msg = String.IsNullOrEmpty(msg) ? moreMsg : msg + ", " + moreMsg;var content = new{RetCode = code,Message = msg,Data = new { }};return new ContentResult{//StatusCode = STATUS_CODE,ContentType = CONTENT_TYPE,Content = JsonConvert.SerializeObject(content).ToString()};}}}

 4、存储过程

CREATE PROCEDURE [dbo].[SP_GetUserOrderList] 
( @lastID int=0,  --当前页数@pageSize int=10 --每页显示记录数
   )AS--declare  @rt_code int;  --声明变量BEGINbegin transactionbegin tryBEGINSELECT  DISTINCT top (@pageSize)  -- CONVERT(varchar(100), order.UpdateTime, 20) AS UpdateTime* FROM    UserInfo_test usertestLEFT JOIN  TRA_BargainOrder_Test  ordertest ON usertest.Id=ordertest.UserID where (( @lastID > 0 AND usertest.Id < @lastID) OR @lastID=0 )  --  and CONVERT(varchar(100), MB.AddTime, 23)>CONVERT(varchar(100), @StartTime, 23) --AND CONVERT(varchar(100), MB.AddTime, 23)<CONVERT(varchar(100), @EndTime, 23) ORDER BY ordertest.UpdateTime DESC; 
ENDcommit transactionend try ----------------------------------------------------------------------------------------------------------------------------begin catchprint '执行存储异常'rollback transactionend catchENDGO

oracle多表联查，