datatable dateset 载体传递数据、存储过程
第一部分:数据库通过存储过程读取数据，通过datatable接受，前台通过asp：repeater.DataSource()和binding()绑定数据
1 /// <summary> 2 /// 分店详情及点评信息 3 /// </summary> 4 /// <param name="cityName"></param> 5 /// <param name="startDate"></param> 6 /// <param name="endDate"></param> 7 /// <returns></returns> 8 public override DataTable BranchListSearchAndReview(string cityName, string startDate, string endDate) 9 { 10 DataTable dt = new DataTable("BranchListSearchAndReview"); 11 DataColumn ID = new DataColumn("ID", typeof(int)); 12 DataColumn Name = new DataColumn("Name", typeof(string)); 13 DataColumn CityName = new DataColumn("CityName", typeof(string)); 14 DataColumn Address = new DataColumn("Address", typeof(string)); 15 DataColumn Content = new DataColumn("Content", typeof(string)); 16 DataColumn adjustmentprice = new DataColumn("adjustmentprice", typeof(string)); 17 DataColumn Regional = new DataColumn("Regional", typeof(string)); 18 DataColumn SumBranchReview = new DataColumn("SumBranchReview", typeof(string)); 19 DataColumn ScoreAvg = new DataColumn("ScoreAvg", typeof(string)); 20 DataColumn Cover = new DataColumn("Cover", typeof(string)); 21 DataColumn Longitude = new DataColumn("Longitude", typeof(string)); 22 DataColumn Latitude = new DataColumn("Latitude", typeof(string)); 23 DataColumn PriceSection = new DataColumn("PriceSection",typeof(string)); 24 DataColumn Phone = new DataColumn("Phone",typeof(string)); 25 DataColumn Mobile = new DataColumn("Mobile",typeof(string)); 26 dt.Columns.Add(ID); 27 dt.Columns.Add(Name); 28 dt.Columns.Add(CityName); 29 dt.Columns.Add(Address); 30 dt.Columns.Add(Content); 31 dt.Columns.Add(adjustmentprice); 32 dt.Columns.Add(Regional); 33 dt.Columns.Add(SumBranchReview); 34 dt.Columns.Add(ScoreAvg); 35 dt.Columns.Add(Cover); 36 dt.Columns.Add(Longitude); 37 dt.Columns.Add(Latitude); 38 dt.Columns.Add(PriceSection); 39 dt.Columns.Add(Phone); 40 dt.Columns.Add(Mobile); 41 using (SqlConnection conn = SqlHelper.getConnection()) 42 { 43 SqlCommand cmd = conn.CreateCommand(); 44 cmd.CommandType = CommandType.StoredProcedure; 45 cmd.CommandText = "sb_branch_search"; 46 cmd.Parameters.Add("@CityName", SqlDbType.NVarChar, 50).Value = cityName; 47 cmd.Parameters.Add("@StartDate", SqlDbType.NVarChar, 50).Value = startDate; 48 cmd.Parameters.Add("@EndDate", SqlDbType.NVarChar, 50).Value = endDate; 49 conn.Open(); 50 SqlDataReader sdr = cmd.ExecuteReader(); 51 while (sdr.Read()) 52 { 53 DataRow dr = dt.NewRow(); 54 dr["ID"] = Convert.ToInt32(sdr["ID"]); 55 dr["Name"] = sdr["Name"].ToString(); 56 dr["CityName"] = sdr["CityName"].ToString(); 57 dr["Address"] = sdr["Address"].ToString(); 58 if (!string.IsNullOrEmpty(sdr["content"].ToString())) 59 { 60 dr["Content"] = sdr["Content"].ToString().Length > 13 ? sdr["Content"].ToString().Substring(0, 13)+"..." : sdr["Content"].ToString(); 61 } 62 else 63 { 64 dr["Content"] = "试过才知道真好！"; 65 } 66 dr["adjustmentprice"] = sdr["adjustmentprice"].ToString(); 67 dr["Regional"] = sdr["Regional"].ToString(); 68 if (!string.IsNullOrEmpty(sdr["SumBranchReview"].ToString())) 69 { 70 dr["SumBranchReview"] = sdr["SumBranchReview"].ToString(); 71 } 72 else 73 { 74 dr["SumBranchReview"] = "0"; 75 } 76 77 if (sdr["ScoreAvg"] != DBNull.Value) 78 { 79 dr["ScoreAvg"] = sdr["ScoreAvg"].ToString(); 80 } 81 else 82 { 83 dr["ScoreAvg"] = "0"; 84 } 85 86 //对价格分区域判断 0-300：A ;300-450:B;450-600:C;600+:D 如果有变化要在这里改 87 //赵坤 20160318 88 Convert.ToInt32(dr["adjustmentprice"].ToString()); 89 if (0 < Convert.ToInt32(dr["adjustmentprice"].ToString()) && Convert.ToInt32(dr["adjustmentprice"].ToString()) <= 300) 90 { 91 dr["PriceSection"] = "A"; 92 } 93 else if (300 < Convert.ToInt32(dr["adjustmentprice"].ToString()) && Convert.ToInt32(dr["adjustmentprice"].ToString()) <= 450) 94 { 95 dr["PriceSection"] = "B"; 96 } 97 else if (450 < Convert.ToInt32(dr["adjustmentprice"].ToString()) && Convert.ToInt32(dr["adjustmentprice"].ToString()) <= 600) 98 { 99 dr["PriceSection"] = "C"; 100 } 101 else 102 { 103 dr["PriceSection"] = "D"; 104 } 105 dr["Cover"] = sdr["Cover"].ToString(); 106 dr["Longitude"] = sdr["Longitude"].ToString(); 107 dr["Latitude"] = sdr["Latitude"].ToString(); 108 dr["Mobile"] = sdr["Mobile"].ToString(); 109 dr["Phone"] = sdr["Phone"].ToString(); 110 dt.Rows.Add(dr); 111 } 112 } 113 return dt; 114 }
1 /// <summary> 2 /// 分店信息 3 /// </summary> 4 private void LoadingBranchList() 5 { 6 ToolManager.GetParam<string>("cityName", ref cityName); 7 ToolManager.GetParam<string>("startDate", ref startDate); 8 ToolManager.GetParam<string>("endDate", ref endDate); 9 if (ToolManager.CheckParam("StartDate")) 10 { 11 if (Convert.ToDateTime(ToolManager.GetParam("StartDate")) >= Convert.ToDateTime(today)) 12 { 13 startDate = Convert.ToDateTime(ToolManager.GetParam("StartDate")).ToString("yyyy-MM-dd"); 14 } 15 else 16 { 17 18 } 19 } 20 if (ToolManager.CheckParam("EndDate")) 21 { 22 if (Convert.ToDateTime(startDate) >= Convert.ToDateTime(ToolManager.GetParam("EndDate"))) 23 { 24 endDate =Convert.ToDateTime( startDate).AddDays(1).ToString(); 25 } 26 else 27 { 28 endDate = Convert.ToDateTime(ToolManager.GetParam("EndDate")).ToString("yyyy-MM-dd"); 29 } 30 } 31 string cityNameTrue = cityName; 32 if (cityName == "输入城市名字") 33 { 34 cityNameTrue = ""; 35 } 36 // List<CBranch> branchs= BranchManager.BranchListSearch(cityNameTrue, startDate, endDate); 37 DataTable dt = BranchManager.BranchListSearchAndReview(cityNameTrue, startDate, endDate); 38 rptBranch.DataSource = dt; 39 rptBranch.DataBind(); 40 }
第二部分：数据库通过存储过程读取数据，通过dataset接受，前天通过datetable处理后，binding()
1 private void LoadBranch() 2 { 3 //店名、地址信息、价格（最低价格）、图片信息、房间概述（分店介绍、路线图）、 4 ToolManager.GetParam<int>("branchID", ref branchID); 5 ToolManager.GetParam<string>("startDate", ref startDate); 6 ToolManager.GetParam<string>("endDate", ref endDate); 7 cb = BranchManager.GetBranch(branchID); 8 if (cb.ID == 0) 9 { 10 //如果没有传递参数或者参数为总部，则调回首页 11 Response.Redirect("/default.aspx"); 12 } 13 else 14 { 15 DataTable dt = new DataTable(); 16 List<CRoomPicture> pis = new List<CRoomPicture>(); 17 DataSet ds = BranchManager.GetBranchDetailInfo(branchID); 18 if (ds.Tables.Count > 0) 19 { 20 dt = ds.Tables[0]; 21 if (dt==null||dt.Rows.Count==0) 22 { 23 Response.Redirect("/default.aspx"); 24 } 25 else 26 { 27 BranchName = dt.Rows[0]["BranchName"].ToString(); 28 BranchNameShort = dt.Rows[0]["BranchNameShort"].ToString(); 29 Address = dt.Rows[0]["Address"].ToString(); 30 Introduction = dt.Rows[0]["Introduction"].ToString().Trim(); 31 Traffic = dt.Rows[0]["Traffic"].ToString().Trim(); 32 MapAddress = dt.Rows[0]["MapAddress"].ToString(); 33 string[] trafficc = Traffic.Split('|');//交通 34 for (int i = 0; i < trafficc.Length; i++) 35 { 36 strTrafficcMap = strTrafficcMap + "<li>" + trafficc[i] + "</li>"; 37 } 38 adjustmentprice = dt.Rows[0]["adjustmentprice"].ToString(); 39 ScoreAvg = dt.Rows[0]["ScoreAvg"].ToString(); 40 Latitude = dt.Rows[0]["Latitude"].ToString(); 41 Longitude = dt.Rows[0]["Longitude"].ToString(); 42 CityName = dt.Rows[0]["CityName"].ToString(); 43 Phone = dt.Rows[0]["Phone"].ToString(); 44 //pics = ds.Tables[1]; 45 DataTable dt1 = new DataTable(); 46 dt1 = ds.Tables[1]; 47 //for (int i = 0; i < ds.Tables[1].Rows.Count; i++) 48 //{ 49 // //数据库中必须有八个 50 // if (i>8) 51 // { 52 // break; 53 // } 54 // //CRoomPicture cs = new CRoomPicture(); 55 // //cs.ID = (int)dt1.Rows[i]["ID"]; 56 // //cs.RoomID = (int)dt1.Rows[i]["RoomID"]; 57 // //cs.MasterPath = dt1.Rows[i]["MasterPath"].ToString().Replace("~", ""); 58 // //cs.ThumbnailsUrl = dt1.Rows[i]["ThumbnailsUrl"].ToString(); 59 // //cs.AttachmentUrl = dt1.Rows[i]["AttachmentUrl"].ToString(); 60 // //cs.Description = dt1.Rows[i]["Description"].ToString(); 61 // //pis.Add(cs); 62 //} 63 pis = (List<CRoomPicture>)ConvertToModel(dt1); 64 } 65 66 67 } 68 ImgRepeater.DataSource = pis; 69 ImgRepeater.DataBind(); 70 } 71 }
1 //获取detail页面详情 2 public override DataSet GetBranchDetailInfo(int branchId) 3 { 4 DataSet ds=new DataSet (); 5 List<CRoomPicture> pics = new List<CRoomPicture>(); 6 using (SqlConnection conn=SqlHelper.getConnection()) 7 { 8 SqlParameter param = new SqlParameter("@BranchID", branchId); 9 ds = SqlHelper.ExecuteDataset(conn, CommandType.StoredProcedure, "sb_branch_img_Booking_review", param); 10 conn.Close(); 11 } 12 return ds; 13 }
存储过程
存储过程一：实用技术包括inner join 、cast round sum count float 组合取保留两位小数的平均数
1 USE [ttrj] 2 GO 3 /****** Object: StoredProcedure [dbo].[sb_branch_search] Script Date: 04/18/2016 09:57:07 ******/ 4 SET ANSI_NULLS ON 5 GO 6 SET QUOTED_IDENTIFIER ON 7 GO 8 ALTER proc [dbo].[sb_branch_search] 9 @CityName nvarchar(50), 10 @StartDate nvarchar(50), 11 @EndDate nvarchar(50) 12 as 13 begin 14 set nocount on 15 declare @where nvarchar(1000) 16 declare @sql nvarchar(2000) 17 set @where='where 1=1 and h.isvalid=1' 18 if @CityName <> '' 19 set @where=@where+' and c.Name='''+@CityName+'''' 20 if @StartDate <> '' 21 set @where=@where+' and e.date>='''+@StartDate+'''' 22 if @EndDate <> '' 23 set @where=@where+' and e.date<'''+@EndDate+'''' 24 set @sql='select h.id,max(b.id) as branchID,avg(h.MemberPrice+e.adjustmentprice) as adjustmentprice from branch b left join dbo.HouseUnit h on b.id=h.branchID left join T_city c on b.cityID=c.id left join Ebooking e 25 on b.id=e.branchID '+@where+' and b.id<>0 group by b.id,h.id order by b.id' 26 declare @TempIds Table 27 ( 28 Id int, 29 branchID int, 30 adjustmentprice float, 31 Pos int identity(1,1) 32 ) 33 insert into @TempIds 34 exec(@sql) 35 if @CityName <> '' 36 begin 37 insert into @TempIds 38 select isnull(h.id,0),b.ID,isnull(h.memberPrice,0) from Branch b left join HouseUnit h on b.id=h.branchID left join T_city c on b.cityID=c.id where b.isApproved=1 and b.ID<>0 and h.isvalid=1 and c.Name=convert(nvarchar(50),@CityName) and isnull(h.id,0) not in (select id from @TempIds) 39 end 40 else 41 begin 42 insert into @TempIds 43 select isnull(h.id,0),b.ID,isnull(h.memberPrice,0) from Branch b left join HouseUnit h on b.id=h.branchID where b.isApproved=1 and b.ID<>0 and h.isvalid=1 and isnull(h.id,0) not in (select id from @TempIds) 44 end 45 --开始 对返回结果重新编辑，减少b表中返回数据，增加了一个试图的（分店点评总数、均分、最后评价），赵坤，20160315 46 select b.*, 47 isnull(a.adjustmentprice,0) as adjustmentprice,c.[Name] as cityName,r.Content,r.SumBranchReview,r.ScoreAvg 48 from branch b inner join T_City c 49 on b.cityID=c.ID inner join dbo.Vi_GetBranch_ReviewInfo r on b.ID=r.BranchID,(select branchID,min(adjustmentprice) adjustmentprice from @TempIds group by branchID) a 50 where b.id=a.branchid and b.isapproved=1 and b.foregroundShow=0 order by b.SysOrder desc 51 --结束 对返回结果重新编辑 52 end 53 54
1 SELECT br.ReviewID, br.BranchID, br.ScoreAvg, br.SumBranchReview, tr.[Content] 2 FROM dbo.T_Review AS tr INNER JOIN 3 (SELECT MAX(r.ID) AS ReviewID, b.BranchID, COUNT(r.ID) AS SumBranchReview, 4 ISNULL(CAST(ROUND((SUM(r.CleanScore + r.ComfortScore + r.ServiceScore + r.SleepScore + r.InternetScore + r.InAndOutScore + r.EquipmentScore + r.ChannelServiceScore) 5 * 1.0) / (COUNT(r.ID) * 8), 2) AS float), 0) AS ScoreAvg 6 FROM dbo.T_Review AS r INNER JOIN 7 dbo.T_Bill AS b ON r.BillID = b.ID 8 WHERE (r.ReviewType = '0') OR 9 (r.ReviewType = '3') 10 GROUP BY b.BranchID) AS br ON tr.ID = br.ReviewID
存储过程二：inner join
1 USE [ttrj] 2 GO 3 /****** Object: StoredProcedure [dbo].[sb_branch_img_Booking_review] Script Date: 04/18/2016 10:01:12 ******/ 4 SET ANSI_NULLS ON 5 GO 6 SET QUOTED_IDENTIFIER ON 7 GO 8 -- ============================================= 9 -- Author: <赵坤,,Name> 10 -- Create date: <2016-03-21,,> 11 -- Description: <查询detail页面中详细信息、图片信息、预定信息、> 12 -- ============================================= 13 ALTER PROCEDURE [dbo].[sb_branch_img_Booking_review] 14 @BranchID int 15 AS 16 BEGIN 17 --详细信息 18 select brr.*,brs.ScoreAvg from (SELECT d.ID,d.CityName,d.BranchName,d.Phone,d.BranchNameShort,d.MapAddress,d.Address,d.Introduction,d.Traffic,f.adjustmentprice,d.Longitude,d.Latitude from 19 (select br.ID,ci.name as CityName,ci.name+'天天如家'+br.Name as BranchName,br.Name as BranchNameShort,br.Phone,br.MapAddress,br.Address,br.Introduction,br.Traffic,br.Longitude,br.Latitude 20 from branch br inner join T_city ci on br.cityid=ci.id where br.id=@BranchID ) d 21 ,( select top 1 avg(h.MemberPrice+e.adjustmentprice) as adjustmentprice,max(h.branchID) as branchID 22 from dbo.HouseUnit h left join Ebooking e on h.branchID=e.branchID where h.branchID=@BranchID and h.isvalid=1 group by h.id order by adjustmentprice asc) 23 f where d.ID=f.BranchID) brr ,( SELECT b.BranchID,ISNULL(CAST(ROUND(SUM(r.CompositeScore) * 1.0 / COUNT(r.ID), 2) AS float), 0) AS ScoreAvg 24 FROM dbo.T_Review AS r LEFT OUTER JOIN dbo.T_Bill AS b ON r.BillID = b.ID 25 WHERE (r.ReviewType = '0') OR(r.ReviewType = '3') 26 GROUP BY b.BranchID) brs where brs.BranchId=brr.ID 27 --图片 28 select top 8 r.ID,r.RoomID,MasterPath=replace(r.MasterPath,'~',''),r.ThumbnailsUrl,r.AttachmentUrl,r.Description from T_RoomPicture r inner join HouseUnit h on r.RoomID=h.id where h.BranchID=@BranchID 29 30 END
