对查询的条件进行判断 ##
string sql = @”select a.MemberId,b.Name as CategoryName,a.Name as MemberName,a.Comments,a.CreationTime,c.BindCardCount
from member a left join member_category b on a.CategoryId=b.CategoryId left join
member_wallet_io e on a.MemberId=e.MemberId
inner join
(
select count(d.CardID) as BindCardCount,c.MemberId from card d
inner join member_card c on d.CardId=c.CardId “;
这里进行判断SQL是否满足
if (!string.IsNullOrEmpty(this.txtCardNumber.Text))
sql += “where d.CodeNumber like ‘%” + this.txtCardNumber.Text + “%’”;
sql += @” group by c.MemberId
) as c on c.MemberId = a.MemberId where 1=1”;
if (!string.IsNullOrEmpty(this.txtMemberName.Text))
sql += ” and a.name like ‘%” + this.txtMemberName.Text + “%’”;
if (!string.IsNullOrEmpty(this.TxtMemberWalletIO.Text))
sql += ” and e.SourceJson like ‘%” + this.TxtMemberWalletIO.Text + “%’”;
if (!string.IsNullOrEmpty(this.dtCreationStartDate.SelectedDate) && !string.IsNullOrEmpty(this.dtCreationEndDate.SelectedDate))
sql += “and a.creationtime >= ‘” + Convert.ToDateTime(this.dtCreationStartDate.SelectedDate).ToString(“yyyy-MM-dd”) + “‘and a.creationtime<=’” + Convert.ToDateTime(this.dtCreationEndDate.SelectedDate).ToString(“yyyy-MM-dd 23:59:59”) + “’”;
sql += @” group by a.MemberId,b.Name,a.Name,a.Comments,a.CreationTime,c.BindCardCount
order by a.CreationTime”;
DataTable dt = dbAccess.ExecuteTable(sql);
this.dgvMember.TableDefinition = new TableDefinitionXmlFile(Server.MapPath(“MemberListDefinition.xml”));
this.dgvMember.DynamicColumnBind();
this.dgvMember.SetDataSource(dt);