asp.net实现的MVC跨数据库多表联合动态条件查询功能示例

本文实例讲述了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程序设计有所帮助。