欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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程序设计有所帮助。