写一个通用的多条件查询方法(带分页的)？

要求如下1 要求 通用 ，多条件动态查询，不能拼接SQL（用+ 神马的）,或者

 
            StringBuilder strBuild = new StringBuilder(_SelectStatEffect);
            if (orgID != "")
            {
                strBuild.AppendFormat(" and C1.OrgFullCode like '{0}%' {1}", orgID, strFilter);
            }
            else
            {
                strBuild.AppendFormat(" {0}", strFilter);
            }
            strBuild.AppendFormat(" group by ORGFULLCODE,ORGNAME,paytype,DATEEFFECTED,currentstep)");

2 要求所有的都必须参数化，如：

dso.DBDataParameterList["P_YEAR"].Value = int.Parse(strYear);    // 生成年度       
      

3 带分页的，分页是SQL实现，不用存储过程，如：

SELECT k.rn, k.* from (SELECT t.* ,  row_number() over ( order by 。。。。。

目前没有好的思路，大家给给意见

25 个解决方案

#1

通用很重要，目前有关参数化问题我解决不了 

#2

坐等高手..

#3

1.where 1=1,后面的必须补上sql语句
2.参数写到@里面
3.用sqlserver2012，的off偏移语句，易于理解，效率也高

#4

OFFSET 然后提取命令

#5

做看高手回答

#6

引用

1.where 1=1,后面的必须补上sql语句
2.参数写到@里面
3.用sqlserver2012，的off偏移语句，易于理解，效率也高 

是ORACLE数据库 

#7

public IList<UserInfo> GetAllContent(int pageSize, int pageIndex, string logName)
        {
            try
            {
                IQueryable<UserInfo> query = DbContext.UserInfo;
                if (!string.IsNullOrEmpty(logName))
                {
                    query = query.Where(i => i.LoginName.Contains(logName));
                }
                var conList = query.AsEnumerable().Skip(pageSize * (pageIndex - 1)).Take(pageSize).ToList<UserInfo>();
                return conList;
            }
            catch (Exception e)
            {

                return null;
            }
        }

#8

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[sp_PageQuery]
     @pageSize int=20,
     @SortFile varchar(50)='',
     @SortType varchar(20)='asc',
     @QuerySQL varchar(8000),
     @PageIndex int=1,
     @Total int output
AS
BEGIN
     declare @SQL nvarchar(4000),@SQLTotal nvarchar(4000)

     set @SQLTotal='select @Total=count(*) from ('+@QuerySQL +') as totaltable'
     exec sp_executesql @SQLTotal,N'@Total int OUTPUT',@Total OUTPUT

     set @SQL='SELECT TOP '+CAST(@pageSize as varchar)+' * '
	          +'FROM '
		  +'( '
		       +' SELECT ROW_NUMBER() OVER ('
      if @SortFile<>''
            set @SQL=@SQL+' ORDER BY '+@SortFile+' '+@SortType
      set @SQL=@SQL +' ) as RowNO,*' 
		+' FROM ( '+@QuerySQL+') as DataTable'
		+' ) as PageTable '
		+'WHERE ROWNO>'+CAST( (@PageIndex-1)*@pageSize as nvarchar)
      exec (@SQL) 
END

#9

引用

public IList<UserInfo> GetAllContent(int pageSize, int pageIndex, string logName)         {             try            {                 IQueryable<UserInfo> query = DbContext.UserInfo;                 if (!string.IsNullOrEmpty(logName))                 {                     query = query.Where(i => i.LoginName.Contains(logName));                 }                 var conList = query.AsEnumerable().Skip(pageSize * (pageIndex - 1)).Take(pageSize).ToList<UserInfo>();                 return conList;             }             catch (Exception e)             {                   return null;             }         }

多表查询分页情况如何处理 ？？？  表数量  都是不固定的

#10

SET ANSI_NULLS ONGO SET QUOTED_IDENTIFIER ONGO   CREATE PROCEDURE [dbo].[sp_PageQuery]      @pageSize int=20,      @SortFile varchar(50)='',      @SortType varchar(20)='asc',      @QuerySQL varchar(8000),      @PageIndex int=1,      @Total int outputASBEGIN     declare @SQL nvarchar(4000),@SQLTotal nvarchar(4000)        set @SQLTotal='select @Total=count(*) from ('+@QuerySQL +') as totaltable'     exec sp_executesql @SQLTotal,N'@Total int OUTPUT',@Total OUTPUT       set @SQL='SELECT TOP '+CAST(@pageSize as varchar)+' * '              +'FROM '          +'( '               +' SELECT ROW_NUMBER() OVER ('      if @SortFile<>''            set @SQL=@SQL+' ORDER BY '+@SortFile+' '+@SortType       set @SQL=@SQL +' ) as RowNO,*'         +' FROM ( '+@QuerySQL+') as DataTable'        +' ) as PageTable '        +'WHERE ROWNO>'+CAST( (@PageIndex-1)*@pageSize as nvarchar)       exec (@SQL)  END

不使用存储过程

#11

引用 9 楼 zkvistor 的回复:

引用

public IList<UserInfo> GetAllContent(int pageSize, int pageIndex, string logName)         {             try            {                 IQueryable<UserInfo> query = DbContext.UserInfo;                 if (!string.IsNullOrEmpty(logName))                 {                     query = query.Where(i => i.LoginName.Contains(logName));                 }                 var conList = query.AsEnumerable().Skip(pageSize * (pageIndex - 1)).Take(pageSize).ToList<UserInfo>();                 return conList;             }             catch (Exception e)             {                   return null;             }         }

多表查询分页情况如何处理 ？？？  表数量  都是不固定的

多表用连接查询啊
IQueryable<GroupInfo> query = (from p in DbContext.MicroLetterGroup
                                               join n in DbContext.UserInfo on p.Founder equals n.UserId
                                               into a
                                               from b in a.DefaultIfEmpty()
                                               select new GroupInfo
                                               {
                                                   GroupId = p.GroupId,
                                                   GroupName = p.GroupName,
                                                   Founder = p.Founder,
                                                   CreateTime = p.CreateTime,
                                                   IsDelete = p.IsDelete,
                                                   FounderName = b.UserName
                                               });

#12

基本不可能

我买了一件衣服，洗涤要求是

1、不能水洗
2、不能干洗
3、不能用刷子刷
4、不能打油。
5、不能抽打。

那么这件衣服要怎么洗呢？

#13

参考： http://bbs.csdn.net/topics/390360310

#14

分页就这么几种方式

1、存储过程
2、SQL

1、拼接
2、每个分页都写一个

然后是组合

1、存储过程里面拼接sql
2、每个分页都写一个存储过程

3、拼接SQL语句 （包括参数化SQL）
4、每个分页都写一个SQL语句 （包括参数化SQL）


对了，还有就是用现成的ORM这一类的。



看看lz的要求

1、通用 —— 2和4不通用

2、不能拼接SQL —— 3 不能用了，1也不能用了。

3、不能用存储过程，1有被pass了。

4、还必须参数化。只能是存储过程或者参数化SQL，但是 又不用存储过程，又不能拼接SQL，还要通用。



俺是想不出来，还有啥办法了。

#15

要通用
表和字段又不固定
要用参数化
不用存储过程
不能拼接

请问楼主你这是为哪般？

#16

不能....不能....不能....

程序员不是很能吗？干嘛“不能”？

#17

在相当程度上，你还是尽量“能”吧。不要太过。

#18

ShareDB.DataTable dt= new ShareDB.DataTable();
dt.GetPage(string sConnName, string sTableName, string sWhere, string sSelectColumns, string sKeyIDName, string sSortColumn, string sSortOrder, int iPageStart, int iPageSize, params Parm[] value)；

http://bbs.csdn.net/topics/390528226?page=1 ShareDB.DataTable能达到你的要求，并且非常简单。
sTableName 可以混合多表。

#19

多表用连接查询啊
IQueryable<GroupInfo> query = (from p in DbContext.MicroLetterGroup
                                               join n in DbContext.UserInfo on p.Founder equals n.UserId
                                               into a
                                               from b in a.DefaultIfEmpty()
                                               select new GroupInfo
                                               {
                                                   GroupId = p.GroupId,
                                                   GroupName = p.GroupName,
                                                   Founder = p.Founder,
                                                   CreateTime = p.CreateTime,
                                                   IsDelete = p.IsDelete,
                                                   FounderName = b.UserName
                                               }); 

不同的查询逻辑不一样，SQL也不一样，这个无法通用？？？

#20

       public DataSet GetinfoList(string table, int pageindex, int pagesize, string orderFid, string Conditions, string[] strparam, object[] strval)
       {

           string query = string.Empty;
           if (ConfigurationManager.ConnectionStrings["DbType"].ToString() == "Access")
           {
               if (pageindex <= 1)
               {
                   query = "Select top {0} * from {1} {2} Order by {3}";
                   query = string.Format(query, pagesize, table, Conditions, orderFid, (pageindex - 1) * pagesize);
               }
               else
               {
                   query = "select a.* from ( select top {0} * from {1} {2} Order by {3} ) a left join ( select top {4} * from {1} {2} Order by {3} ) b on a.id=b.id where iif(b.id,'0','1')='1'";
                   query = string.Format(query, pageindex * pagesize, table, Conditions, orderFid, (pageindex - 1) * pagesize);
               }
           }
           else if (ConfigurationManager.ConnectionStrings["DbType"].ToString() == "SQL2000")
           {
               query = "select a.* from (select top {0} * from {1} {2} Order by {3}) a left join ( select top {4} * from {1} {2} Order by {3}) b on a.id=b.id where b.id is null";
               query = string.Format(query, pageindex * pagesize, table, Conditions, orderFid, (pageindex - 1) * pagesize);
           }
           else
           {
               query = "Select top {0} * From "
                + "(Select *,ROW_NUMBER() OVER(ORDER BY {2}) as RowNum From {4} {3}) as newTable "
                + "Where (RowNum > {1})";
               query = string.Format(query, pagesize, (pageindex - 1) * pagesize, orderFid, Conditions, table);
           }
           return db.GetDataSet(query, strparam, strval);

       }
       public int GetRowCount(string table, string cond, string[] strparam, object[] strval)
       {

           string query = string.Format("Select count(1) From {0} {1}", table, cond);
           DataSet ds = db.GetDataSet(query, strparam, strval);
           if (ds != null)
           {
               if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
               {
                   return Galsun.Common.Utils.StrToInt(ds.Tables[0].Rows[0][0].ToString(), 0);
               }
           }
           return 0;

       }
一般是这样的
    private void BindData()
    {
        string _cond = " Where isCheck=1 and sid=@sid ";
        string[] strparam={"@sid"};
        object[] objvalue={sid};
        rp_news.DataSource = GetinfoList("gl_NewsSystem", AspNetPager1.CurrentPageIndex, AspNetPager1.PageSize, "fbdate DESC,ID DESC", _cond, strparam, objvalue);
        rp_news.DataBind();
        AspNetPager1.RecordCount = GetRowCount("gl_NewsSystem", _cond, strparam, objvalue);
    }
当遇到多表关联查询时，我要先把多表关联做成一个视图，然后把视图带进去就可以了
CREATE VIEW [dbo].[v_gl_k_market]
AS
Select qs.*,
sp.f2 as sp_f2,sp.f3 as sp_f3,sp.f5 as sp_f5 ,
ot.classID,ot.PicUrl,ot.PicThumb,ot.Weight,ot.EMS,ot.Explain,ot.Intro,ot.sdateTime,ot.edateTIme,ot.price3,ot.price3UserID,ot.price3Date,ot.Times,ot.hits,
case isnull(ot.Status,0) 
when 99 then 
case datediff(ss,sdateTime,getdate())/abs(datediff(ss,sdateTime,getdate()))
when -1 then
'未开始' 
else
case datediff(ss,edateTIme,getdate())/abs(datediff(ss,edateTIme,getdate())) 
when 1 then 
'已结束'
else
'进行中'
end
end
when 0 then '未设置' 
when 3 then '已开标' 
when 4 then '已结算' 
end as [Status],
case isnull(ot.ItemID,0)
when 0 then '线下用户'
when -1 then '流标标的'
else
ot.WinUserID
end as WinUserID
, ot.price4
,ot.OrderID
From k_qs_bh qs
left outer join k_sp_list sp on qs.f2=sp.f4
left outer join k_Auction ot on qs.id=ot.qs_bh_ID


....
rp_news.DataSource = GetinfoList("[v_gl_k_market]", AspNetPager1.CurrentPageIndex, AspNetPager1.PageSize, "OrderID DESC,ID DESC", _cond, strparam, objvalue);

#21

#22

首先感谢楼上各位的关注，昨天和同事讨论了一下,
思路如下：对于一个传统的SQL分页查询可以拆分为两个部分
1 查询SQL

select A.id,A.name,B.age from A inner join B on A.id=B.id

2 条件

where A.id=1 .......

要解决的问题如下：
1要把查询的字段 和 表名以参数形式传入,并指定字段属于哪个表？
2表与表连接的关系【inner join, left join, right join, out join】,先后顺序也要考虑
3表与表的关联字段
4如何指定 查询条件【where后的部分】和表字段的对应关系
5条件间的关系【and 还是 or ? 默认用and ,遇到or 或者有括号情况如何处理?】
上述每一个问题需要用到怎样的数据结构存储 ?

#23

EF 不行么？

#24

只要拼接的条件都是由你设定好的，拼接 参数化的sql就不会带来注入问题

#25

已实现，只针对where部分,where前面的SQL语句需要作为参数传入【where前面的SQL情况太多，各种连接，子查询，带括号，不带括号】