这里有新鲜出炉的精品教程,程序狗速度看过来!
ASP.NET 是. NET FrameWork 的一部分,是一项微软公司的技术,是一种使嵌入网页中的脚本可由因特网服务器执行的服务器端脚本技术,它可以在通过 HTTP 请求文档时再在 Web 服务器上动态创建它们。 指 Active Server Pages(动态服务器页面) ,运行于 IIS(Internet Information Server 服务,是 Windows 开发的 Web 服务器)之中的程序 。
这篇文章主要介绍了 asp.net 实现的 MVC 跨数据库多表联合动态条件查询功能, 结合实例形式较为详细分析了 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 程序设计有所帮助。
来源: http://www.phperz.com/article/17/0814/337996.html