以下代码生成的sql语句是曾对sql server 2005以上版本的，希望这些代码对大家有用
public class pagerquery { private int _pageindex; private int _pagesize = 20; private string _pk; private string _fromclause; private string _groupclause; private string _selectclause; private string _sortclause; private stringbuilder _whereclause; public datetime datefilter = datetime.minvalue; protected querybase() { _whereclause = new stringbuilder(); } /**//// /// 主键 /// public string pk { get { return _pk; } set { _pk = value; } } public string selectclause { get { return _selectclause; } set { _selectclause = value; } } public string fromclause { get { return _fromclause; } set { _fromclause = value; } } public stringbuilder whereclause { get { return _whereclause; } set { _whereclause = value; } } public string groupclause { get { return _groupclause; } set { _groupclause = value; } } public string sortclause { get { return _sortclause; } set { _sortclause = value; } } /**//// /// 当前页数 /// public int pageindex { get { return _pageindex; } set { _pageindex = value; } } /**//// /// 分页大小 /// public int pagesize { get { return _pagesize; } set { _pagesize = value; } } /**//// /// 生成缓存key /// /// public override string getcachekey() { const string keyformat = “pager-sc:{0}-fc:{1}-wc:{2}-gc:{3}-sc:{4}”; return string.format(keyformat, selectclause, fromclause, whereclause, groupclause, sortclause); } /**//// /// 生成查询记录总数的sql语句 /// /// public string generatecountsql() { stringbuilder sb = new stringbuilder(); sb.appendformat(” from {0}”, fromclause); if (whereclause.length > 0) sb.appendformat(” where 1=1 {0}”, whereclause); if (!string.isnullorempty(groupclause)) sb.appendformat(” group by {0}”, groupclause); return string.format(“select count(0) {0}”, sb); } /**//// /// 生成分页查询语句，包含记录总数 /// /// public string generatesqlincludetotalrecords() { stringbuilder sb = new stringbuilder(); if (string.isnullorempty(selectclause)) selectclause = “*”; if (string.isnullorempty(sortclause)) sortclause = pk; int start_row_num = (pageindex – 1)*pagesize + 1; sb.appendformat(” from {0}”, fromclause); if (whereclause.length > 0) sb.appendformat(” where 1=1 {0}”, whereclause); if (!string.isnullorempty(groupclause)) sb.appendformat(” group by {0}”, groupclause); string countsql = string.format(“select count(0) {0};”, sb); string temql = string.format( “with t as (select row_number() over(order by {0}) as row_number,{1}{2}) select * from t where row_number between {3} and {4};”, sortclause, selectclause, sb, start_row_num, (start_row_num + pagesize – 1)); return tempsql + countsql; } /**//// /// 生成分页查询语句 /// /// public override string generatesql() { stringbuilder sb = new stringbuilder(); if (string.isnullorempty(selectclause)) selectclause = “*”; if (string.isnullorempty(sortclause)) sortclause = pk; int start_row_num = (pageindex – 1)*pagesize + 1; sb.appendformat(” from {0}”, fromclause); if (whereclause.length > 0) sb.appendformat(” where 1=1 {0}”, whereclause); if (!string.isnullorempty(groupclause)) sb.appendformat(” group by {0}”, groupclause); return string.format( “with t as (select row_number() over(order by {0}) as row_number,{1}{2}) select * from t where row_number between {3} and {4}”, sortclause, selectclause, sb, start_row_num, (start_row_num + pagesize – 1)); } }
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容， 请发送邮件至253000106@qq.com举报，一经查实，本站将立刻删除。
发布者：PHP中文网，转转请注明出处：https://www.chuangxiangniao.com/p/1836864.html
