动态查询 及 多条件判断优化方法

CREATE proc  GetQsqAdminLogbyWhere
@where varchar(5000)=''
as  
if(@where = '')
    set @where = '1=1'
exec('select * from (
select forums_Users.UserName,QsqAdminLog.*  from forums_Users,QsqAdminLog
 where forums_Users.UserId=QsqAdminLog.UserId ) a where '+@where)

GO

这里的where可以随时传递不同的条件，很适合条件常变动，组合条件。前台即可非常灵活，三种条件模糊着来查都没问题，灵活多变。

void Bind()

        { 
            //根据时间来查询用户登录日志
            string userName = this.txtKey.Text;
            string begin = beginDateID.Value;
            string end = beginDateID18.Value;
            StringBuilder sb=new StringBuilder(" 1=1 "); 
            if(userName.Length > 0)
            {
                sb.Append(" and [UserName] like '%"+userName+"%'");
            }
            if(begin.Length >0)
            {
                sb.Append(" and LoginTime>'"+begin+"' ");
            }
            if(end.Length>0)
            {
                sb.Append("and LoginTime<'"+end+"'");

            }          

    Dg.DataSource = dp.GetQsqAdminLogbyWhere(sb.ToString()).Items;

            Dg.DataBind();           

        }

多种情况下优化if else 条件查询

private void Button1_Click(object sender, System.EventArgs e)
        {
            string begin = beginDateID.Value;
            string end = beginDateID18.Value;
            string name = Utils.inject(txtName.Text);
            //理财帖子数
            DataTable dt1 = dp.SelectSubjectPager(1,1000000000,"","suCreateDate",1,out count,0);
            DataView dv1 = dt1.DefaultView;
            DataTable dt2 = dp.SelectSubjectPager(1,1000000000,"","suCreateDate",2,out count,0);
            DataView dv2 = dt2.DefaultView;
            if(name!="")
            {
                /*
                if(begin==""&&end!="")
                {
                    dv1.RowFilter = "NickName='"+name+"'  and suCreateDate<='"+Convert.ToDateTime(end).AddDays(1)+"'";    
                    dv2.RowFilter = "NickName='"+name+"'  and suCreateDate<='"+Convert.ToDateTime(end).AddDays(1)+"'";                    
                }
                else if(end==""&&begin!="")
                {
                    dv1.RowFilter = "NickName='"+name+"' and suCreateDate>='"+begin+"'";    
                    dv2.RowFilter = "NickName='"+name+"' and suCreateDate>='"+begin+"'";                    
                }
                else if(begin==""&&end=="")
                {
                    Components.Tools.Utils.MessageBox("起止日期不能都为空！");
                    return;
                }
                else
                {
                    dv1.RowFilter = "NickName='"+name+"' and suCreateDate>='"+begin+"' and suCreateDate<='"+Convert.ToDateTime(end).AddDays(1)+"'";    
                    dv2.RowFilter = "NickName='"+name+"' and suCreateDate>='"+begin+"' and suCreateDate<='"+Convert.ToDateTime(end).AddDays(1)+"'";    
            
                }    
                */            

                string filter = "1=1 and 2=2";
                if(begin!="")
                    filter = filter.Replace("1=1","suCreateDate>='"+begin+"'");
                if(end != "")
                    filter = filter.Replace("2=2","suCreateDate<='"+Convert.ToDateTime(end).AddDays(1)+"'");


                dv1.RowFilter = "NickName='"+name+"' and " + filter;
                dv2.RowFilter = dv1.RowFilter;

                int Num1 = dv1.Count;        
                int Num2 = dv2.Count;
                int num = Num2+Num1;
                this.LabMessage.Text="该用户理财贴吧与随便贴吧发帖共"+num+"条";
            }            
            else
            {
                Components.Tools.Utils.MessageBox("用户名不能为空！");
            }

            DgBind();
        }