第一部分:数据库通过存储过程读取数据，通过datatable接受，前台通过asp：repeater.DataSource()和binding()绑定数据
/// <summary>
/// 分店详情及点评信息
/// </summary>
/// <param name="cityName"></param>
/// <param name="startDate"></param>
/// <param name="endDate"></param>
/// <returns></returns>
public override DataTable BranchListSearchAndReview(string cityName, string startDate, string endDate)
{
DataTable dt = new DataTable("BranchListSearchAndReview");
DataColumn ID = new DataColumn("ID", typeof(int));
DataColumn Name = new DataColumn("Name", typeof(string));
DataColumn CityName = new DataColumn("CityName", typeof(string));
DataColumn Address = new DataColumn("Address", typeof(string));
DataColumn Content = new DataColumn("Content", typeof(string));
DataColumn adjustmentprice = new DataColumn("adjustmentprice", typeof(string));
DataColumn Regional = new DataColumn("Regional", typeof(string));
DataColumn SumBranchReview = new DataColumn("SumBranchReview", typeof(string));
DataColumn ScoreAvg = new DataColumn("ScoreAvg", typeof(string));
DataColumn Cover = new DataColumn("Cover", typeof(string));
DataColumn Longitude = new DataColumn("Longitude", typeof(string));
DataColumn Latitude = new DataColumn("Latitude", typeof(string));
DataColumn PriceSection = new DataColumn("PriceSection",typeof(string));
DataColumn Phone = new DataColumn("Phone",typeof(string));
DataColumn Mobile = new DataColumn("Mobile",typeof(string));
dt.Columns.Add(ID);
dt.Columns.Add(Name);
dt.Columns.Add(CityName);
dt.Columns.Add(Address);
dt.Columns.Add(Content);
dt.Columns.Add(adjustmentprice);
dt.Columns.Add(Regional);
dt.Columns.Add(SumBranchReview);
dt.Columns.Add(ScoreAvg);
dt.Columns.Add(Cover);
dt.Columns.Add(Longitude);
dt.Columns.Add(Latitude);
dt.Columns.Add(PriceSection);
dt.Columns.Add(Phone);
dt.Columns.Add(Mobile);
using (SqlConnection conn = SqlHelper.getConnection())
{
SqlCommand cmd = conn.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "sb_branch_search";
cmd.Parameters.Add("@CityName", SqlDbType.NVarChar, ).Value = cityName;
cmd.Parameters.Add("@StartDate", SqlDbType.NVarChar, ).Value = startDate;
cmd.Parameters.Add("@EndDate", SqlDbType.NVarChar, ).Value = endDate;
conn.Open();
SqlDataReader sdr = cmd.ExecuteReader();
while (sdr.Read())
{
DataRow dr = dt.NewRow();
dr["ID"] = Convert.ToInt32(sdr["ID"]);
dr["Name"] = sdr["Name"].ToString();
dr["CityName"] = sdr["CityName"].ToString();
dr["Address"] = sdr["Address"].ToString();
if (!string.IsNullOrEmpty(sdr["content"].ToString()))
{
dr["Content"] = sdr["Content"].ToString().Length > ? sdr["Content"].ToString().Substring(, )+"..." : sdr["Content"].ToString();
}
else
{
dr["Content"] = "试过才知道真好！";
}
dr["adjustmentprice"] = sdr["adjustmentprice"].ToString();
dr["Regional"] = sdr["Regional"].ToString();
if (!string.IsNullOrEmpty(sdr["SumBranchReview"].ToString()))
{
dr["SumBranchReview"] = sdr["SumBranchReview"].ToString();
}
else
{
dr["SumBranchReview"] = "";
} if (sdr["ScoreAvg"] != DBNull.Value)
{
dr["ScoreAvg"] = sdr["ScoreAvg"].ToString();
}
else
{
dr["ScoreAvg"] = "";
} //对价格分区域判断 0-300：A ;300-450:B;450-600:C;600+:D 如果有变化要在这里改
//赵坤 20160318
Convert.ToInt32(dr["adjustmentprice"].ToString());
if ( < Convert.ToInt32(dr["adjustmentprice"].ToString()) && Convert.ToInt32(dr["adjustmentprice"].ToString()) <= )
{
dr["PriceSection"] = "A";
}
else if ( < Convert.ToInt32(dr["adjustmentprice"].ToString()) && Convert.ToInt32(dr["adjustmentprice"].ToString()) <= )
{
dr["PriceSection"] = "B";
}
else if ( < Convert.ToInt32(dr["adjustmentprice"].ToString()) && Convert.ToInt32(dr["adjustmentprice"].ToString()) <= )
{
dr["PriceSection"] = "C";
}
else
{
dr["PriceSection"] = "D";
}
dr["Cover"] = sdr["Cover"].ToString();
dr["Longitude"] = sdr["Longitude"].ToString();
dr["Latitude"] = sdr["Latitude"].ToString();
dr["Mobile"] = sdr["Mobile"].ToString();
dr["Phone"] = sdr["Phone"].ToString();
dt.Rows.Add(dr);
}
}
return dt;
}
Dal
/// <summary>
/// 分店信息
/// </summary>
private void LoadingBranchList()
{
ToolManager.GetParam<string>("cityName", ref cityName);
ToolManager.GetParam<string>("startDate", ref startDate);
ToolManager.GetParam<string>("endDate", ref endDate);
if (ToolManager.CheckParam("StartDate"))
{
if (Convert.ToDateTime(ToolManager.GetParam("StartDate")) >= Convert.ToDateTime(today))
{
startDate = Convert.ToDateTime(ToolManager.GetParam("StartDate")).ToString("yyyy-MM-dd");
}
else
{ }
}
if (ToolManager.CheckParam("EndDate"))
{
if (Convert.ToDateTime(startDate) >= Convert.ToDateTime(ToolManager.GetParam("EndDate")))
{
endDate =Convert.ToDateTime( startDate).AddDays().ToString();
}
else
{
endDate = Convert.ToDateTime(ToolManager.GetParam("EndDate")).ToString("yyyy-MM-dd");
}
}
string cityNameTrue = cityName;
if (cityName == "输入城市名字")
{
cityNameTrue = "";
}
// List<CBranch> branchs= BranchManager.BranchListSearch(cityNameTrue, startDate, endDate);
DataTable dt = BranchManager.BranchListSearchAndReview(cityNameTrue, startDate, endDate);
rptBranch.DataSource = dt;
rptBranch.DataBind();
}
web
第二部分：数据库通过存储过程读取数据，通过dataset接受，前天通过datetable处理后，binding()
private void LoadBranch()
{
//店名、地址信息、价格（最低价格）、图片信息、房间概述（分店介绍、路线图）、
ToolManager.GetParam<int>("branchID", ref branchID);
ToolManager.GetParam<string>("startDate", ref startDate);
ToolManager.GetParam<string>("endDate", ref endDate);
cb = BranchManager.GetBranch(branchID);
if (cb.ID == )
{
//如果没有传递参数或者参数为总部，则调回首页
Response.Redirect("/default.aspx");
}
else
{
DataTable dt = new DataTable();
List<CRoomPicture> pis = new List<CRoomPicture>();
DataSet ds = BranchManager.GetBranchDetailInfo(branchID);
if (ds.Tables.Count > )
{
dt = ds.Tables[];
if (dt==null||dt.Rows.Count==)
{
Response.Redirect("/default.aspx");
}
else
{
BranchName = dt.Rows[]["BranchName"].ToString();
BranchNameShort = dt.Rows[]["BranchNameShort"].ToString();
Address = dt.Rows[]["Address"].ToString();
Introduction = dt.Rows[]["Introduction"].ToString().Trim();
Traffic = dt.Rows[]["Traffic"].ToString().Trim();
MapAddress = dt.Rows[]["MapAddress"].ToString();
string[] trafficc = Traffic.Split('|');//交通
for (int i = ; i < trafficc.Length; i++)
{
strTrafficcMap = strTrafficcMap + "<li>" + trafficc[i] + "</li>";
}
adjustmentprice = dt.Rows[]["adjustmentprice"].ToString();
ScoreAvg = dt.Rows[]["ScoreAvg"].ToString();
Latitude = dt.Rows[]["Latitude"].ToString();
Longitude = dt.Rows[]["Longitude"].ToString();
CityName = dt.Rows[]["CityName"].ToString();
Phone = dt.Rows[]["Phone"].ToString();
//pics = ds.Tables[1];
DataTable dt1 = new DataTable();
dt1 = ds.Tables[];
//for (int i = 0; i < ds.Tables[1].Rows.Count; i++)
//{
// //数据库中必须有八个
// if (i>8)
// {
// break;
// }
// //CRoomPicture cs = new CRoomPicture();
// //cs.ID = (int)dt1.Rows[i]["ID"];
// //cs.RoomID = (int)dt1.Rows[i]["RoomID"];
// //cs.MasterPath = dt1.Rows[i]["MasterPath"].ToString().Replace("~", "");
// //cs.ThumbnailsUrl = dt1.Rows[i]["ThumbnailsUrl"].ToString();
// //cs.AttachmentUrl = dt1.Rows[i]["AttachmentUrl"].ToString();
// //cs.Description = dt1.Rows[i]["Description"].ToString();
// //pis.Add(cs);
//}
pis = (List<CRoomPicture>)ConvertToModel(dt1);
} }
ImgRepeater.DataSource = pis;
ImgRepeater.DataBind();
}
}
web
//获取detail页面详情
public override DataSet GetBranchDetailInfo(int branchId)
{
DataSet ds=new DataSet ();
List<CRoomPicture> pics = new List<CRoomPicture>();
using (SqlConnection conn=SqlHelper.getConnection())
{
SqlParameter param = new SqlParameter("@BranchID", branchId);
ds = SqlHelper.ExecuteDataset(conn, CommandType.StoredProcedure, "sb_branch_img_Booking_review", param);
conn.Close();
}
return ds;
}
Dal
存储过程
存储过程一：实用技术包括inner join 、cast round sum count float 组合取保留两位小数的平均数
USE [ttrj]
GO
/****** Object: StoredProcedure [dbo].[sb_branch_search] Script Date: 04/18/2016 09:57:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[sb_branch_search]
@CityName nvarchar(),
@StartDate nvarchar(),
@EndDate nvarchar()
as
begin
set nocount on
declare @where nvarchar()
declare @sql nvarchar()
set @where='where 1=1 and h.isvalid=1'
if @CityName <> ''
set @where=@where+' and c.Name='''+@CityName+''''
if @StartDate <> ''
set @where=@where+' and e.date>='''+@StartDate+''''
if @EndDate <> ''
set @where=@where+' and e.date<'''+@EndDate+''''
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
on b.id=e.branchID '+@where+' and b.id<> group by b.id,h.id order by b.id'
declare @TempIds Table
(
Id int,
branchID int,
adjustmentprice float,
Pos int identity(,)
)
insert into @TempIds
exec(@sql)
if @CityName <> ''
begin
insert into @TempIds
select isnull(h.id,),b.ID,isnull(h.memberPrice,) 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= and b.ID<> and h.isvalid= and c.Name=convert(nvarchar(),@CityName) and isnull(h.id,) not in (select id from @TempIds)
end
else
begin
insert into @TempIds
select isnull(h.id,),b.ID,isnull(h.memberPrice,) from Branch b left join HouseUnit h on b.id=h.branchID where b.isApproved= and b.ID<> and h.isvalid= and isnull(h.id,) not in (select id from @TempIds)
end
--开始 对返回结果重新编辑，减少b表中返回数据，增加了一个试图的（分店点评总数、均分、最后评价），赵坤，
select b.*,
isnull(a.adjustmentprice,) as adjustmentprice,c.[Name] as cityName,r.Content,r.SumBranchReview,r.ScoreAvg
from branch b inner join T_City c
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
where b.id=a.branchid and b.isapproved= and b.foregroundShow= order by b.SysOrder desc
--结束 对返回结果重新编辑
end
存储过程（一）
SELECT br.ReviewID, br.BranchID, br.ScoreAvg, br.SumBranchReview, tr.[Content]
FROM dbo.T_Review AS tr INNER JOIN
(SELECT MAX(r.ID) AS ReviewID, b.BranchID, COUNT(r.ID) AS SumBranchReview,
ISNULL(CAST(ROUND((SUM(r.CleanScore + r.ComfortScore + r.ServiceScore + r.SleepScore + r.InternetScore + r.InAndOutScore + r.EquipmentScore + r.ChannelServiceScore)
* 1.0) / (COUNT(r.ID) * ), ) AS float), ) AS ScoreAvg
FROM dbo.T_Review AS r INNER JOIN
dbo.T_Bill AS b ON r.BillID = b.ID
WHERE (r.ReviewType = '') OR
(r.ReviewType = '')
GROUP BY b.BranchID) AS br ON tr.ID = br.ReviewID
试图（一）
存储过程二：inner join
USE [ttrj]
GO
/****** Object: StoredProcedure [dbo].[sb_branch_img_Booking_review] Script Date: 04/18/2016 10:01:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <赵坤,,Name>
-- Create date: <--,,>
-- Description: <查询detail页面中详细信息、图片信息、预定信息、>
-- =============================================
ALTER PROCEDURE [dbo].[sb_branch_img_Booking_review]
@BranchID int
AS
BEGIN
--详细信息
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
(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
from branch br inner join T_city ci on br.cityid=ci.id where br.id=@BranchID ) d
,( select top avg(h.MemberPrice+e.adjustmentprice) as adjustmentprice,max(h.branchID) as branchID
from dbo.HouseUnit h left join Ebooking e on h.branchID=e.branchID where h.branchID=@BranchID and h.isvalid= group by h.id order by adjustmentprice asc)
f where d.ID=f.BranchID) brr ,( SELECT b.BranchID,ISNULL(CAST(ROUND(SUM(r.CompositeScore) * 1.0 / COUNT(r.ID), ) AS float), ) AS ScoreAvg
FROM dbo.T_Review AS r LEFT OUTER JOIN dbo.T_Bill AS b ON r.BillID = b.ID
WHERE (r.ReviewType = '') OR(r.ReviewType = '')
GROUP BY b.BranchID) brs where brs.BranchId=brr.ID
--图片
select top 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 END
存储过程（二）