【1】{按时间查询条件无效,无法排除不在时间范围内的记录},sql判定条件中逻辑出错,出错代码:
(CloseStoreStartTime>=@startTime and CloseStoreStartTime<=@endTime) or (CloseStoreEndTime>=@startTime and CloseStoreEndTime <=@endTime)
更正后:
declare @sql varchar(4000)=''
set @sql=' SELECT
C.Id as Id,
CT.id CityId,
S.city CityName,
S.id LocationId,
abbr_name StoreName,
CloseStoreStartTime ,
CloseStoreEndTime,
StoreStatus,
OprNo,
fname OprName,
OprDateTime
FROM EcCloseStore C (nolock)
LEFT JOIN car_storehouse S(NOLOCK) ON C.LocationId=S.id
LEFT JOIN city_new CT(NOLOCK) ON CT.city = S.city
LEFT JOIN user_list U(NOLOCK)ON C.OprNo = U.user_no
WHERE 1=1'
if @cityId>0
begin
set @sql=@sql+' and CT.id='''+cast(@cityId as varchar)+''''
end
if @storeId>0
begin
set @sql=@sql+' and C.LocationId='''+cast(@storeId as varchar)+''''
end
if @startTime>'1900-1-1' and @endTime>'1900-1-1'
begin
set @sql=@sql+' and ((CloseStoreStartTime<='''+cast(@endTime as varchar)+''') AND (CloseStoreEndTime>='''+cast(@startTime as varchar)+'''))'
end
exec (@sql)
时间判断这段语句为where条件中的其中之一,应用括号包裹成一体。
【2】非空验证、信息提示等
【3】复制前一个页面控制器代码时粗心,犯了类似抄作业把别人名字也抄上去的错误。
【4非bug】
var cityId = Convert.ToInt32(Request["cityId"]);
var storeId = Convert.ToInt32(Request["storeId"]);
var startDate = string.IsNullOrEmpty(Request["startTime"]) ? DateTime.Parse("1900-01-01") : DateTime.Parse(Request["startTime"]);
var endDate = string.IsNullOrEmpty(Request["endTime"]) ? DateTime.Parse("1900-01-01") : DateTime.Parse(Request["endTime"]);
以后直接在方法中定义参数,避免使用Request
更改后
public JsonResult Search(int cityId,int storeId,DateTime startTime, DateTime endTime)
{
var contactList = mFactoryClose.Search(cityId, storeId, startTime, endTime);
if (contactList.Count == 0)
{
return Json(new { success = false, message = "暂无数据" });
}
return Json(new { CityIdList = contactList },JsonRequestBehavior.AllowGet);
}