写一个通用的多条件查询方法(带分页的)？
要求如下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)");
dso.DBDataParameterList["P_YEAR"].Value = int.Parse(strYear); // 生成年度
SELECT k.rn, k.* from (SELECT t.* , row_number() over ( order by 。。。。。
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;
}
}
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
FounderName = b.UserName
});
[解决办法]
参考：http://bbs.csdn.net/topics/390360310
[解决办法]
分页就这么几种方式
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，还要通用。
俺是想不出来，还有啥办法了。
[解决办法]
要通用
表和字段又不固定
要用参数化
不用存储过程
不能拼接
请问楼主你这是为哪般？
[解决办法]
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);
[解决办法]
[解决办法]
EF 不行么？
[解决办法]
只要拼接的条件都是由你设定好的，拼接参数化的sql就不会带来注入问题
