62,046
社区成员
发帖
与我相关
我的任务
分享
/// <summary>
/// 拼接查询条件
/// </summary>
/// <param name="strSql">查询sql主体</param>
/// <param name="entity">条件实体</param>
/// <returns></returns>
public static String CreateSearchSql(String strSql,Object entity)
{
PropertyInfo[] propertys = entity.GetType().GetProperties();
foreach (PropertyInfo p in propertys)
{
if (p.PropertyType == typeof(System.String) && p.GetValue(entity, null) != null)
{
if (p.GetValue(entity, null).ToString() != String.Empty)
strSql += " AND " + p.Name + " LIKE '%" + p.GetValue(entity, null).ToString() + "%'";
}
else if (p.PropertyType == typeof(System.Int32))
{
if (Convert.ToInt32(p.GetValue(entity, null)) != 999)
strSql += " AND " + p.Name + " = " + Convert.ToInt32(p.GetValue(entity, null));
}
else if (p.PropertyType == typeof(System.Int64))
{
if (Convert.ToInt64(p.GetValue(entity, null)) != 999)
strSql += " AND " + p.Name + " = " + Convert.ToInt64(p.GetValue(entity, null));
}
//else if (p.PropertyType == typeof(System.DateTime))
//{
// if(p.GetValue(entity,null).ToString()!=String.Empty)
// strSql+="AND"+p.Name+">"
//}
}
return strSql;
}
// 查询按钮事件
protected void btnSearch_Click(object sender, EventArgs e)
{
try
{
StringBuilder strWhere = new StringBuilder();
List<NameValue> lstWhereInfo = new List<NameValue>();
StringBuilder strAnd = new StringBuilder();
if (txtYearMonthSearch.Text != "")//年度月份
{
CommTool.DynamicParameter.AddModel2List(ref lstWhereInfo, "BackApply.YearMonth", txtYearMonthSearch.Text);
strWhere.Append(strAnd + " YearMonth=@YearMonth");
if (strAnd.Length > 0) { strAnd.Remove(0, strAnd.Length); } strAnd.Append(" And");
}
if (txtBackApplyDateSearchBeg.Text != "")//申请时间起始
{
CommTool.DynamicParameter.Append2WhereEqualBig(ref strWhere, ref lstWhereInfo, "BackApply.BackApplyDate", "BackApplyDateBeg", txtBackApplyDateSearchBeg.Text, ref strAnd);
}
if (txtBackApplyDateSearchEnd.Text != "")//申请时间截止
{
CommTool.DynamicParameter.Append2WhereEqualLit(ref strWhere, ref lstWhereInfo, "BackApply.BackApplyDate", "BackApplyDateEnd", txtBackApplyDateSearchEnd.Text, ref strAnd);
}
//if (txtBackApplyDateSearch.Text != "") { CommTool.DynamicParameter.AddModel2List(ref lstWhereInfo, "BackApply.BackApplyDate", txtBackApplyDateSearch.Text); strWhere.Append(strAnd + " BackApplyDate=@BackApplyDate"); if (strAnd.Length > 0) { strAnd.Remove(0, strAnd.Length); } strAnd.Append(" And"); }//申请时间
if (ddlSupplierIDSearch.SelectedIndex != 0)//供应商
{
CommTool.DynamicParameter.AddModel2List(ref lstWhereInfo, "SupplierID", ddlSupplierIDSearch.SelectedValue);
strWhere.Append(strAnd + " BackApply.SupplierID=@SupplierID");
if (strAnd.Length > 0) { strAnd.Remove(0, strAnd.Length); } strAnd.Append(" And");
}
DataTable dt = dal.GetDataSet(strWhere.ToString(), lstWhereInfo);
rptList.DataSource = dt;
rptList.DataBind();
}
catch (Exception err)
{
JscriptMsg(CommApp.BLL.CommonToolBLL.GetErrMsg(err, Request.Path), "Error", 2);
}
}
条件可按需求修改的
数据访问类的代码是这样的
/// <summary>
/// 获得查询数据
/// </summary>
public DataTable GetDataTable(string strWhere, List<NameValue> lstWhereInfo)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select * from BackApply");
if(strWhere.Trim() != "")
{
strSql.Append(" where " + strWhere);
}
SqlParameter[] parameters = CommTool.DynamicParameter.SqlDynamicParameter(lstWhereInfo);
return dbt.QueryDataTable(strSql.ToString(), parameters);
}