asp.net实现的MVC跨数据库多表联合动态条件查询功能示例
程序员文章站
2022-06-29 17:41:44
本文实例讲述了asp.net实现的mvc跨数据库多表联合动态条件查询功能。分享给大家供大家参考,具体如下:
一、控制器中方法
[httpget]
public...
本文实例讲述了asp.net实现的mvc跨数据库多表联合动态条件查询功能。分享给大家供大家参考,具体如下:
一、控制器中方法
[httpget] public actionresult search() { viewbag.headtitle = "搜索"; viewbag.metakey = "\"123\""; viewbag.metades = "\"456\""; string wheretext = ""; if (security.htmlhelper.getquerystring("first", true) != string.empty) { wheretext += " and a.parentid='" + stringfilter("first", true)+"'"; } if (security.htmlhelper.getquerystring("second", true) != string.empty) wheretext += " and a.categoryid='" + stringfilter("second",true)+"'"; string valuestr = ""; if (security.htmlhelper.getquerystring("theme", true) != string.empty) valuestr += stringfilter("theme", true) + ","; if (security.htmlhelper.getquerystring("size", true) != string.empty) valuestr += stringfilter("size", true) + ","; if (security.htmlhelper.getquerystring("font", true) != string.empty) valuestr += stringfilter("font", true) + ","; if (security.htmlhelper.getquerystring("shape", true) != string.empty) valuestr += stringfilter("shape", true) + ","; if (security.htmlhelper.getquerystring("technique", true) != string.empty) valuestr += stringfilter("technique", true) + ","; if (security.htmlhelper.getquerystring("category", true) != string.empty) valuestr += stringfilter("category", true) + ","; if (security.htmlhelper.getquerystring("place", true) != string.empty) valuestr += stringfilter("place", true) + ","; if (security.htmlhelper.getquerystring("price", true) != string.empty) valuestr += stringfilter("price", true) + ","; if (valuestr != "") { valuestr=valuestr.substring(0, valuestr.length - 1); wheretext += " and f.valueid in("+valuestr+")"; } if (security.htmlhelper.getquerystring("searchkeys", true) != string.empty) wheretext += " and a.saletitle like '%'" + stringfilter("searchkes", true) + "'%' or a.saledes like '%'" + stringfilter("searchkes", true) + "'%' or a.saleauthor like '%'" + stringfilter("searchkes", true) + "'%' or a.keywords like '%'" + stringfilter("searchkes", true) + "'%' or g.valueproperty like '%'" + stringfilter("searchkes", true) + "'%'"; int pagesize = 50; int pageindex = httpcontext.request.querystring["pageindex"].toint(1); list<string> searchinfo = search(pageindex, pagesize, wheretext, 1); if (security.htmlhelper.getquerystring("sort", true) != string.empty) { string sort = stringfilter("sort", true); switch (sort) { case "1": //综合即默认按照上架时间降序排列即按照id降序 searchinfo = search(pageindex, pagesize, wheretext, 1); break; case"2": //销量 searchinfo = search(pageindex, pagesize, wheretext,0, "saletotal"); break; case "3": //收藏 searchinfo = search(pageindex, pagesize, wheretext,0, "favoritestotal"); break; case "4": //价格升序 searchinfo = search(pageindex, pagesize, wheretext,1); break; case "5": //价格降序 searchinfo = search(pageindex, pagesize, wheretext,2); break; } } string jsonstr = searchinfo[0]; viewdata["jsondata"] = jsonstr; int allcount = utility.toint(searchinfo[1], 0); viewbag.allcount = allcount; viewbag.maxpages = allcount % pagesize == 0 ? allcount / pagesize : (allcount / pagesize + 1).toint(1); return view(); } [nonaction] public list<string> search(int pageindex, int pagesize, string wheretext, int orderbyprice, string orderby = "saleid") { bll.products searchinfobll = new bll.products(); list<string> searchinfo = searchinfobll.getsearchinfo(pageindex, pagesize, wheretext, orderbyprice,orderby); return searchinfo; }
注:security.htmlhelper.getquerystring(),stringfilter()为自己封装的方法,用于过滤参数值
二、bll层方法
using system; using system.web; using system.web.caching; using system.collections; using system.collections.generic; using system.linq; using system.text; using system.data; using system.data.common; using system.web.script.serialization; using fotosaymall.model; using fotosaymall.common; using system.text.regularexpressions; using system.io; using newtonsoft.json; using newtonsoft.json.converters; using fotosaymall.mvc.models; namespace fotosaymall.bll { public class products { private readonly dal.products dal = new dal.products(); /// <summary> /// 分页查询,检索页数据 /// </summary> /// <param name="pageindex"></param> /// <param name="pagesize"></param> /// <param name="orderbyprice">价格排序:0默认,1升序,2降序</param> /// <returns></returns> public list<string> getsearchinfo(int pageindex, int pagesize, string wheretext, int orderbyprice, string orderby = "saleid") { dataset searchinfotables = dal.getsearchinfo(pageindex, pagesize, wheretext); //总记录数 int allcount = utility.toint(searchinfotables.tables[1].rows[0]["rowstotal"], 0); var searchinfo = from list in searchinfotables.tables[0].asenumerable().orderbydescending(x => x.table.columns[orderby]) select new searchmodel { url = "/home/products?saleid=" + list.field<int>("saleid"), author = list.field<string>("saleauthor"), photofilename = list.field<string>("photofilename"), photofilepathflag = list.field<int>("photofilepathflag"), province = list.field<string>("place").split(' ').first(), saleprice = list.field<decimal>("saleprice"), usingprice = list.field<decimal>("usingprice"), title = list.field<string>("saletitle").length > 30 ? list.field<string>("saletitle").substring(0, 30) : list.field<string>("saletitle"), year = list.field<datetime>("buildtime").tostring("yyyy") == "1900" ? "" : list.field<datetime>("buildtime").tostring("yyyy年") }; if (orderbyprice==2) searchinfo = searchinfo.orderbydescending(x => x.price); else if (orderbyprice == 1) searchinfo = searchinfo.orderby(x => x.price); string jsonstr = jsonconvert.serializeobject(searchinfo); list<string> datalist = new list<string>(); datalist.add(jsonstr); datalist.add(allcount.tostring()); return datalist; } } }
注:注意观察由datatable转换为可枚举的可用于linq查询的方法方式。
dal
/// <summary> /// 获取检索页数据 /// </summary> /// <param name="pageindex"></param> /// <param name="pagesize"></param> /// <returns></returns> public dataset getsearchinfo(int pageindex, int pagesize, string wheretext) { stringbuilder sqltext = new stringbuilder(); sqltext.append("select * from ("); sqltext.append("select a.saleid,a.photoid,saletitle,saleauthor,a.status,a.categoryid,c.userid,c.username,b.photofilepathflag,b.photofilename,coalesce(e.buildtime,0) buildtime,c.place,coalesce(d.usingprice,0) usingprice,coalesce(e.saleprice,0) saleprice,h.saletotal,h.favoritestotal,row_number() over(order by a.saleid) rowsnum "); sqltext.append("from fotosay..photo_sale a join fotosay..photo_basic b on a.photoid = b.photoid "); sqltext.append("join fotosay..system_accountsdescription c on b.userid = c.userid "); sqltext.append("left join fotosay..photo_sale_picture d on a.saleid = d.saleid "); sqltext.append("left join fotosay..photo_sale_tangible e on a.saleid = e.saleid "); sqltext.append("join fotosaymall..fotomall_product_relation f on f.saleid = a.saleid "); sqltext.append("join fotosaymall..fotomall_product_propertyvalue g on g.categoryid = a.categoryid and g.valueid = f.valueid and g.propertyid = f.propertyid "); sqltext.append("join fotosay..photo_sale_property h on a.saleid = h.saleid "); sqltext.append("where a.status=1 " + wheretext + " "); sqltext.append("group by a.saleid,a.photoid,saletitle,saleauthor,a.status,a.categoryid,c.userid,c.username,b.photofilepathflag,b.photofilename,e.buildtime,c.place,usingprice,saleprice,h.saletotal,h.favoritestotal "); sqltext.append(") t where rowsnum between @pagesize*(@pageindex-1)+1 and @pagesize*@pageindex;"); sqltext.append("select count(distinct a.saleid) rowstotal from fotosay..photo_sale a join (select b1.photofilepathflag,b1.photofilename,b1.userid,b1.photoid from fotosay..photo_basic b1 union select b2.photofilepathflag,b2.photofilename,b2.userid,b2.photoid from fotosay..photo_basic_history b2 ) b on a.photoid = b.photoid join fotosay..system_accountsdescription c on b.userid = c.userid left join fotosay..photo_sale_picture d on a.saleid = d.saleid left join fotosay..photo_sale_tangible e on a.saleid = e.saleid join fotosaymall..fotomall_product_relation f on f.saleid = a.saleid join fotosaymall..fotomall_product_propertyvalue g on g.categoryid = a.categoryid and g.valueid = f.valueid and g.propertyid = f.propertyid join fotosay..photo_sale_property h on a.saleid = h.saleid where a.status=1 " + wheretext + ";"); dbparameter[] parameters = { fotosay.createindbparameter("@pageindex", dbtype.int32,pageindex), fotosay.createindbparameter("@pagesize", dbtype.int32,pagesize) }; dataset searchinfolist = fotosay.executequery(commandtype.text, sqltext.tostring(), parameters); //记录条数不够一整页,则查历史库 if (searchinfolist.tables[0].rows.count < pagesize) { string sql = "select top(1) a.saleid from fotosay..photo_sale a join fotosay..photo_basic_history b on a.photoid = b.photoid join fotosay..system_accountsdescription c on b.userid = c.userid left join fotosay..photo_sale_picture d on a.saleid = d.saleid left join fotosay..photo_sale_tangible e on a.saleid = e.saleid join fotosaymall..fotomall_product_relation f on f.saleid = a.saleid join fotosaymall..fotomall_product_propertyvalue g on g.categoryid = a.categoryid and g.valueid = f.valueid and g.propertyid = f.propertyid join fotosay..photo_sale_property h on a.saleid = h.saleid where a.status=1 " + wheretext + ";"; dataset ds = fotosay.executequery(commandtype.text, sql.tostring(), parameters); if (ds != null && ds.tables[0].rows.count > 0) { stringbuilder sqltextmore = new stringbuilder(); sqltextmore.append("select * from ("); sqltextmore.append("select a.saleid,a.photoid,saletitle,saleauthor,a.status,a.categoryid,c.userid,c.username,b.photofilepathflag,b.photofilename,coalesce(e.buildtime,0) buildtime,c.place,coalesce(d.usingprice,0) usingprice,coalesce(e.saleprice,0) saleprice,h.saletotal,h.favoritestotal,row_number() over(order by a.saleid) rowsnum "); sqltextmore.append("from fotosay..photo_sale a "); sqltextmore.append("join (select b1.photofilepathflag,b1.photofilename,b1.userid,b1.photoid from fotosay..photo_basic b1 union select b2.photofilepathflag,b2.photofilename,b2.userid,b2.photoid from fotosay..photo_basic_history b2 ) b on a.photoid = b.photoid join fotosay..system_accountsdescription c on b.userid = c.userid "); sqltextmore.append("left join fotosay..photo_sale_picture d on a.saleid = d.saleid "); sqltextmore.append("left join fotosay..photo_sale_tangible e on a.saleid = e.saleid "); sqltextmore.append("join fotosaymall..fotomall_product_relation f on f.saleid = a.saleid "); sqltextmore.append("join fotosaymall..fotomall_product_propertyvalue g on g.categoryid = a.categoryid and g.valueid = f.valueid and g.propertyid = f.propertyid "); sqltextmore.append("join fotosay..photo_sale_property h on a.saleid = h.saleid "); sqltextmore.append("where a.status=1 " + wheretext + " "); sqltextmore.append("group by a.saleid,a.photoid,saletitle,saleauthor,a.status,a.categoryid,c.userid,c.username,b.photofilepathflag,b.photofilename,e.buildtime,c.place,usingprice,saleprice,h.saletotal,h.favoritestotal"); sqltextmore.append(") t where rowsnum between @pagesize*(@pageindex-1)+1 and @pagesize*@pageindex;"); sqltextmore.append("select count(distinct a.saleid) rowstotal from fotosay..photo_sale a join (select b1.photofilepathflag,b1.photofilename,b1.userid,b1.photoid from fotosay..photo_basic b1 union select b2.photofilepathflag,b2.photofilename,b2.userid,b2.photoid from fotosay..photo_basic_history b2 ) b on a.photoid = b.photoid join fotosay..system_accountsdescription c on b.userid = c.userid left join fotosay..photo_sale_picture d on a.saleid = d.saleid left join fotosay..photo_sale_tangible e on a.saleid = e.saleid join fotosaymall..fotomall_product_relation f on f.saleid = a.saleid join fotosaymall..fotomall_product_propertyvalue g on g.categoryid = a.categoryid and g.valueid = f.valueid and g.propertyid = f.propertyid join fotosay..photo_sale_property h on a.saleid = h.saleid where a.status=1 " + wheretext + ";"); searchinfolist = fotosay.executequery(commandtype.text, sqltextmore.tostring(), parameters); } } return searchinfolist; }
注:注意其中使用的跨数据库查询的方式和union的一种使用方式
model
using system; using system.collections.generic; using system.configuration; using system.linq; using system.web; namespace fotosaymall.mvc.models { public class searchmodel { /// <summary> /// 原始图片文件夹(用于url地址) /// </summary> private const string originimagesurlfolder = "userimages/photos_origin"; /// <summary> /// 购买页链接 /// </summary> public string url { get; set; } /// <summary> /// 所属域名(1为fotosay,2为img,3为img1) /// </summary> public int photofilepathflag { get; set; } /// <summary> /// 图片名称 /// </summary> public string photofilename { get; set; } /// <summary> /// 商品名称 /// </summary> public string title { get; set; } /// <summary> /// 作者所在省份 /// </summary> public string province { get; set; } /// <summary> /// 作者 /// </summary> public string author { get; set; } /// <summary> /// 创作年份 /// </summary> public string year { get; set; } /// <summary> /// 图片:单次价格 /// </summary> public decimal usingprice { get; set; } /// <summary> /// 实物:定价 /// </summary> public decimal saleprice { get; set; } /// <summary> /// 售价 /// </summary> public string price { get { if (this.usingprice > 0) return this.usingprice.tostring(); else if (this.saleprice > 0) return this.saleprice.tostring(); else return "议价"; } } /// <summary> /// /// </summary> private string mastersite { get { return configurationmanager.appsettings["mastersite"].tostring(); } } /// <summary> /// 图片完整路径 /// </summary> public string img { get { return mastersite + "/" + originimagesurlfolder + this.photofilename + "b.jpg"; } } } }
更多关于asp.net相关内容感兴趣的读者可查看本站专题:《asp.net优化技巧总结》、《asp.net字符串操作技巧汇总》、《asp.net操作xml技巧总结》、《asp.net文件操作技巧汇总》、《asp.net ajax技巧总结专题》及《asp.net缓存操作技巧总结》。
希望本文所述对大家asp.net程序设计有所帮助。
上一篇: mvc重定向方式详解