前言:對於經常忙於服務端開發的小伙伴來說,與DB層打交道是在正常不過的事了,但是每次頁面的查詢條件新增往往意味著後端代碼參數化同比增長,當然你可以不使用sqlhelper自帶的參數化條件查詢,可以直接傳遞參數,這樣一來,可能你寫的代碼就變少了,但是存在一個隱藏的問題就是sql註入,對於sql註入我想 ...
前言:對於經常忙於服務端開發的小伙伴來說,與DB層打交道是在正常不過的事了,但是每次頁面的查詢條件新增往往意味著後端代碼參數化同比增長,當然你可以不使用sqlhelper自帶的參數化條件查詢,可以直接傳遞參數,這樣一來,可能你寫的代碼就變少了,但是存在一個隱藏的問題就是sql註入,對於sql註入我想大家都並不陌生,相關資源和預防措施網上千篇一律,有興趣可以自己去瞭解,常用的註入工具是sqlmapper,有興趣的可以一併去瞭解。
那麼你寫代碼既要保持代碼的優雅,語句條數的少量,有想要保證代碼的安全性能,不被輕易註入,有沒有一種2種都能相容的方式呢,在對於一般習慣拘泥於原有代碼思維的人來看,可能並沒有,但是習慣於從不同角度思考問題的人來說,條條大路通羅馬,比如我下麵說的這種就是基於.NET 自帶的stirngfromatter原理來實現的一種自動化參數化查詢~~
由於口頭表述不是很好,我直接貼圖來說明瞭,請看
這是原先的參數化查詢
1 public PayOrderEntity GetPayOrderInfoById(int id) 2 { 3 PayOrderEntity parorderModel = new PayOrderEntity(); 4 List<SqlParameter> paramList = new List<SqlParameter>(); 5 string sql = @"select p.Id as pid 6 ,c.Id 7 ,c.UserId 8 ,p.OrderId 9 ,p.TransactionId 10 ,p.PayType 11 ,c.TotalPrice as orderprice 12 ,p.TotalPrice as payprice 13 ,c.[Status] as orderstatus 14 ,p.[Status] as paystatus 15 ,c.CreateTime 16 ,p.PayTime 17 ,c.Remark as orderremark 18 ,p.Remark as payremark 19 from t_ContentOrder(nolock) c 20 left join t_Payment(nolock) p 21 on c.Id=p.OrderId where c.IsDeleted=0 and p.IsDeleted=0 and p.Id= {0}"; 22 paramList.Add(new SqlParameter("@id", id)); 23 try 24 { 25 var dt = SqlHelper.ExecuteDataset(write_connstring, CommandType.Text, sql, paramList.ToArray()); 26 27 //使用重寫後的DB驅動查詢方法調用 by:xuja 2017-05-27 10:34:19 28 //var dt = SqlQuery(sql, id); 29 30 31 parorderModel = DbTableConvertor<PayOrderEntity>.ConvertToEntity(dt.Tables[0]); 32 } 33 catch (Exception ex) 34 { 35 Core.Log.TraceLogger.Error(ex); 36 } 37 return parorderModel; 38 }View Code
步驟一 先實例化一個sqlparams實體
步驟二 將滿足條件的參數傳入定義好的參數實體並賦值(可能需要條件驗證)
步驟三 將填充完成後的參數對象傳入調用的sqlhelper查詢介面中
這樣四步即可實現整個查詢流程
看著流程也並不複雜,但是大家想過一個問題沒,如果一個頁面有6個以上的查詢條件,是不是定義起來會很吃力,這種感覺我相信大家都會有,我也不列外 囧 ,接下來利用formatter原理我們在看看重構後的參數化查詢代碼
1 /// <summary> 2 /// sql參數自動化拼接方法 3 /// 創建人:xujiangan 4 /// 2017-05-27 10:35:40 5 /// </summary> 6 /// <param name="sqlCommand">需要查詢的sql</param> 7 /// <param name="param">需要拼接的參數列表</param> 8 /// <returns></returns> 9 public Tuple<string, SqlParameter[]> ProcessSqlCommand(string sqlCommand, params object[] param) 10 { 11 var tempKVDic = param.Select((item, i) => new KeyValuePair<string, object>("@an" + i, item)).ToDictionary(k => k.Key, v => v.Value); 12 13 var tempSqlCommand = string.Format(sqlCommand, tempKVDic.Keys.ToArray()); 14 15 var tempParams = tempKVDic.Select(t => new SqlParameter(t.Key, t.Value)).ToArray(); 16 17 return Tuple.Create(tempSqlCommand, tempParams); 18 }View Code
方法的返回值因為不確定到具體類型,所以用了元組來定義了,元組的能力類似於dynamic T類型,這裡就不多介紹了,參數的條件只有2個,1.要執行查詢的sql語句 2.需要傳遞的參數化條件
代碼邏輯:1.將參數列表利用鍵值對的方法一一對應組合,參數可以自動增長,組成參數列表字典;2.使用format方法將sql語句和參數字典拼接;3.將拼接好的查詢字元串返回給調用方
原先介面並沒有這次參數條件的重載 接下來,我們還學要重寫一下sqlhelper執行查詢的介面,如下:
1 /// <summary> 2 /// 重寫ExcuteQuery方法,便於自動話添加參數 3 /// 創建人:xujiangan 4 /// 2017-05-27 10:35:07 5 /// </summary> 6 /// <param name="sqlCommand">執行sql語句</param> 7 /// <param name="param">需要新增的查詢參數</param> 8 /// <returns></returns> 9 public DataSet SqlQuery(string sqlCommand, params object[] param) 10 { 11 var dt = new DataSet(); 12 if (param == null || param.Length == 0) 13 { 14 dt = SqlHelper.ExecuteDataset(write_connstring, sqlCommand, CommandType.Text); 15 } 16 17 var temp = ProcessSqlCommand(sqlCommand, param); 18 19 dt = SqlHelper.ExecuteDataset(write_connstring, CommandType.Text, temp.Item1, temp.Item2); 20 21 //object entity = DbTableConvertor<object>.ConvertToEntity(dt.Tables[0]); 22 23 return dt; 24 }View Code
代碼大家都能看懂,無非就是有參和無參兩種情況做了區分,我就不解釋了...
俗話說,欲善其事,必先利其器,有了上面的準備之後,我們就可以調用我們剛纔重寫的介面啦
調用請看下麵:
1 public PayOrderEntity GetPayOrderInfoById(int id) 2 { 3 PayOrderEntity parorderModel = new PayOrderEntity(); 4 string sql = @"select p.Id as pid 5 ,c.Id 6 ,c.UserId 7 ,p.OrderId 8 ,p.TransactionId 9 ,p.PayType 10 ,c.TotalPrice as orderprice 11 ,p.TotalPrice as payprice 12 ,c.[Status] as orderstatus 13 ,p.[Status] as paystatus 14 ,c.CreateTime 15 ,p.PayTime 16 ,c.Remark as orderremark 17 ,p.Remark as payremark 18 from t_ContentOrder(nolock) c 19 left join t_Payment(nolock) p 20 on c.Id=p.OrderId where c.IsDeleted=0 and p.IsDeleted=0 and p.Id= {0}"; 21 try 22 { 23 //使用重寫後的DB驅動查詢方法調用 by:xuja 2017-05-27 10:34:19 24 var dt = SqlQuery(sql, id); 25 parorderModel = DbTableConvertor<PayOrderEntity>.ConvertToEntity(dt.Tables[0]); 26 } 27 catch (Exception ex) 28 { 29 Core.Log.TraceLogger.Error(ex); 30 } 31 return parorderModel; 32 }View Code
是不是發現少了什麼?沒錯,我們此時不再需要定義繁雜的sqlparams對象拉,直接傳遞參數即可,有多少傳多少,如果太多可以定義數組或者參數實體傳遞哦~
PS:註意sql代碼語句中參數位置改為占位符!!!
到此為止,整個自動化參數化查詢基本上就結束了,有問題可以給我留言指出,共勉
項目目前已經應用到 admin.kk.net 支付訂單詳情頁面查詢,經多番測試,並沒有發現什麼問題,看來是可以用的。
建議sql代碼大小寫一致,保持只解析一次。
高山仰止,景行行止
by:Jsonxu