preface
In the previous section, we added one-to-many relationships using transactions in Dapper. This section mainly records how to use Dapper to realize the query display of multiple tables.
A problem
When the query displayed in the MVC controller returns the Json format as JsonResult to the foreground. The inevitable date format is not the date format we want. It’s something like /Date(1565664248000)/ Date.
The solution
- Option 1 uses the IsoDateTimeConverter class in Newtonsoft.Json to format the DATE in ISO format
// To use the IsoDateTimeConverter class, you must refer to two methods using newtonsoft.json; using Newtonsoft.Json.Linq; Public string Load(){sring IsoDateTimeConverter timeFormat = new IsoDateTimeConverter(); timeFormat.DateTimeFormat = "yyyy-MM-dd HH:mm:ss"; return JsonConvert.SerializeObject(result, Formatting.Indented, timeFormat); }Copy the codeWhere result is the string returned to the foreground. Note, however, that with method 1, the foreground must be converted to a Json object.
- Scheme 2 still uses the JsonResult method to return Json. However, we need to extend the JsonResult class.
- In the DbOption folder, create the my.jsonMVC class.
namespace my.JsonMvc { public static class MvcExtendsion { public static JsonResult MyJson(object data, JsonRequestBehavior behavior, string format) { return new ToJsonResult { Data = data, JsonRequestBehavior = behavior, FormatString = format }; } public class ToJsonResult: JsonResult {const string error = "This request has been blocked because sensitive information was disclosed to a third party website, which is used in a GET request. To AllowGet requests, set JsonRequestBehavior AllowGet. ; /// </summary> public string FormatString {get; set; } public override void ExecuteResult(ControllerContext context) { if (context == null) { throw new ArgumentNullException("context"); } if (JsonRequestBehavior == JsonRequestBehavior.DenyGet && String.Equals(context.HttpContext.Request.HttpMethod, "GET", StringComparison.OrdinalIgnoreCase)) { throw new InvalidOperationException(error); } HttpResponseBase response = context.HttpContext.Response; if (! String.IsNullOrEmpty(ContentType)) { response.ContentType = ContentType; } else { response.ContentType = "application/json"; } if (ContentEncoding ! = null) { response.ContentEncoding = ContentEncoding; } if (Data ! = null) { JavaScriptSerializer serializer = new JavaScriptSerializer(); string jsonstring = serializer.Serialize(Data); string p = @"\\/Date\(\d+\)\\/"; MatchEvaluator matchEvaluator = new MatchEvaluator(ConvertJsonDateToDateString); Regex reg = new Regex(p); jsonstring = reg.Replace(jsonstring, matchEvaluator); response.Write(jsonstring); }} /// <summary> /// serialize Json time from /Date(1294499956278+0800) to a string /// </summary> private string ConvertJsonDateToDateString(Match m) { string result = string.Empty; string p = @"\d"; var cArray = m.Value.ToCharArray(); StringBuilder sb = new StringBuilder(); Regex reg = new Regex(p); for (int i = 0; i < cArray.Length; i++) { if (reg.IsMatch(cArray[i].ToString())) { sb.Append(cArray[i]); } } DateTime dt = new DateTime(1970, 1, 1); dt = dt.AddMilliseconds(long.Parse(sb.ToString())); dt = dt.ToLocalTime(); result = dt.ToString(FormatString); return result; }}}}Copy the code- The extended class is then referenced in the controller.
using my.JsonMvc; Public JsonResult Load() {Json return MvcExtendsion.MyJson(result, JsonRequestBehavior.AllowGet, "yyyy-MM-dd HH:mm:ss"); }Copy the codeFinally, I chose option 2 to solve the problem of formatting dates.
Below is the complete multi-table query code.
- Create the Index view in the BorrowBookController controller
/// </summary> /// </returns> </returns> public ActionResult Add() {return View(); }Copy the code- Create the Load_Borrow_Data method
//</summary> //</ returns></ /splitOn read Reader from right to left public JsonResult Load_Borrow_Data() { using (var conn = ConnectionFactory.MysqlConn()) { string sql_query = "select a.book_Id,a.book_Num,a.book_Name,a.book_Desc,a.borow_Time, "; sql_query += "b.book_Type_id,b.book_Type_Name,c.user_name"; sql_query += " from sys_Borrow_Book a join sys_Book_Type b on a.book_Type_id=b.book_Type_id left join sys_Borrow_User c "; sql_query += " on a.user_Id=c.user_Id "; var result = conn.Query<sys_Borrow_Book, sys_Book_Type, sys_Borrow_User, sys_Borrow_Book>(sql_query, (books, bkys, users) => { books.borrow_types = bkys; books.borrow_users = users; return books; }, splitOn: "book_Type_Name,user_name").ToList(); return MvcExtendsion.MyJson(result, JsonRequestBehavior.AllowGet, "yyyy-MM-dd HH:mm:ss"); }}Copy the code- Create the Index View in the View View
<div class="layui-container"> <div class="layui-row"> <h2 style="margin-top:10px;" <h >< hr class="layui-bg-green"> <button class="layui-btn layui-btn-sm" id="btnAdd">< I class="layui-icon"> </ I > New </ div class="layui-col-md12"> <table class="layui-table" id="bind_tb"> <thead> <tr> <th> Type books < th > < / th > < th > book number < / th > < th > book title < / th > < th > the borrower < / th > < th > borrow time < / th > < th > action < / th > < / tr > < thead > < / table > < / div > < / div > </div>Copy the code- Manipulate the DOM in the Index view
<script type="text/javascript"> Load(); Function Load() {$.ajax({url: ".. /BorrowBook/Load_Borrow_Data", type: "Get", }).done(function (msg) { var str_tb = ""; $("#bind_tb tr:gt(0)").remove(); debugger; if ($.isEmptyObject(msg) == false) { $.each(msg, function (i, obj) { str_tb += "<tr>"; str_tb += "<td>" + (i + 1) + "</td>"; str_tb += "<td>" + obj.borrow_types.book_Type_Name + "</td>"; str_tb += "<td>" + obj.book_Num + "</td>"; str_tb += "<td>" + obj.book_Name + "</td>"; str_tb += "<td>" + obj.borrow_users.user_name + "</td>"; str_tb += "<td>" + obj.borow_Time + "</td>"; str_tb += "<td><button class='layui-btn layui-btn-normal layui-btn-xs btnedit' id=" + obj.book_Type_id + "><i class='layui-icon'> < / I > edit < / button > "; str_tb += "<button class='layui-btn layui-btn-danger layui-btn-xs btndel' id=" + obj.book_Id + "><i class='layui-icon'> Delete the < / I > < / button > < / td > "; str_tb += "</tr>"; }); $("#bind_tb").append(str_tb); } else { //$("#bind_tb tr:gt(0)").remove(); var str_error = "<tr><td colspan='7' style='text-align:center; '> </td></tr>"; $("#bind_tb").append(str_error); } }).fail(function (e) { }); } </script>Copy the codeEnd result:
Postscript: today the last day of work, here to everyone to worship an early years, I hope you in the year of the Ox, bull, good luck. Hee hee ~
To be continued…