使用Dapper参数化查询(一)
封装查询实体类
public class QueryParameter { public QueryParameter() { listWhere = new List<string>(); dynamicParameter = new DynamicParameters(); } public List<string> listWhere { get; set; } public DynamicParameters dynamicParameter { get; set; } public string strWhere { get { if (listWhere != null && listWhere.Count > 0) return string.Join(" AND ", listWhere); else return ""; } } }
使用时候
qp = new QueryParameter(); if (!string.IsNullOrWhiteSpace(txtcname.Text)) { _qp.listWhere.Add(" cname LIKE @cname "); _qp.dynamicParameter.Add("@cname", "%" + txtcname.Text.Trim() + "%"); } if (!string.IsNullOrWhiteSpace(txtcaccount.Text)) { _qp.listWhere.Add(" caccount LIKE @caccount "); _qp.dynamicParameter.Add("@caccount", "%" + txtcaccount.Text.Trim() + "%"); } if (!string.IsNullOrWhiteSpace(txtcinput.Text)) { _qp.listWhere.Add(" cinput=@cinput "); _qp.dynamicParameter.Add("@cinput", txtcinput.Text.Trim()); }
/// <summary> /// 获得数据列表 /// </summary> public List<op_company> GetList(QueryParameter qp) { StringBuilder strSql = new StringBuilder(); strSql.Append("SELECT * FROM op_company "); if (!string.IsNullOrEmpty(qp.strWhere)) { strSql.Append(" WHERE " + qp.strWhere); } return DBHelperFactory.RemoteWrite.DbConnection.Query<op_company>(strSql.ToString(), qp.dynamicParameter).ToList(); }