public IList <AdBaseInfo> GetList(int pageIndex, int pageSize, out int totalRecords, string sqlWhere, params SqlParameter[] cmdParms) { StringBuilder sb = new StringBuilder(250); sb.Append(@"select count(*) from AdBase "); if (!string.IsNullOrEmpty(sqlWhere)) { sb.AppendFormat(" where 1=1 {0} ", sqlWhere); } totalRecords = (int)SqlHelper.ExecuteScalar(SqlHelper.SqlProviderConnString, CommandType.Text, sb.ToString(), cmdParms); if (totalRecords == 0) { return(new List <AdBaseInfo>()); } sb.Clear(); int startIndex = (pageIndex - 1) * pageSize + 1; int endIndex = pageIndex * pageSize; sb.Append(@"select * from(select row_number() over(order by LastUpdatedDate desc) as RowNumber, Id,Title,SiteFunId,LayoutPositionId,Timeout,Sort,StartTime,EndTime,VirtualViewCount,ViewCount,IsDisable,LastUpdatedDate from AdBase " ); if (!string.IsNullOrEmpty(sqlWhere)) { sb.AppendFormat(" where 1=1 {0} ", sqlWhere); } sb.AppendFormat(@")as objTable where RowNumber between {0} and {1} ", startIndex, endIndex); IList <AdBaseInfo> list = new List <AdBaseInfo>(); using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.SqlProviderConnString, CommandType.Text, sb.ToString(), cmdParms)) { if (reader != null && reader.HasRows) { while (reader.Read()) { AdBaseInfo model = new AdBaseInfo(); model.Id = reader.GetGuid(1); model.Title = reader.GetString(2); model.SiteFunId = reader.GetGuid(3); model.LayoutPositionId = reader.GetGuid(4); model.Timeout = reader.GetInt32(5); model.Sort = reader.GetInt32(6); model.StartTime = reader.GetDateTime(7); model.EndTime = reader.GetDateTime(8); model.VirtualViewCount = reader.GetInt32(9); model.ViewCount = reader.GetInt32(10); model.IsDisable = reader.GetBoolean(11); model.LastUpdatedDate = reader.GetDateTime(12); list.Add(model); } } } return(list); }
public AdBaseInfo GetModelByJoin(object Id) { AdBaseInfo model = null; string cmdText = @"select top 1 ad.Id,ad.Title,ad.SiteFunId,ad.LayoutPositionId,ad.Timeout,ad.Sort,ad.StartTime,ad.EndTime,ad.VirtualViewCount,ad.ViewCount,ad.IsDisable,ad.LastUpdatedDate, at.TypeCode as SiteFunName,at1.TypeCode as LayoutPositionName,adi.Descr,adi.ContentText from AdBase ad left join ContentType at on at.Id = ad.SiteFunId left join ContentType at1 on at1.Id = ad.LayoutPositionId left join AdvertisementItem adi on adi.AdvertisementId = ad.Id where ad.Id = @Id " ; SqlParameter parm = new SqlParameter("@Id", SqlDbType.UniqueIdentifier); parm.Value = Guid.Parse(Id.ToString()); using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.SqlProviderConnString, CommandType.Text, cmdText, parm)) { if (reader != null) { while (reader.Read()) { model = new AdBaseInfo(); model.Id = reader.GetGuid(0); model.Title = reader.GetString(1); model.SiteFunId = reader.GetGuid(2); model.LayoutPositionId = reader.GetGuid(3); model.Timeout = reader.GetInt32(4); model.Sort = reader.GetInt32(5); model.StartTime = reader.GetDateTime(6); model.EndTime = reader.GetDateTime(7); model.VirtualViewCount = reader.GetInt32(8); model.ViewCount = reader.GetInt32(9); model.IsDisable = reader.GetBoolean(10); model.LastUpdatedDate = reader.GetDateTime(11); model.SiteFunName = reader.IsDBNull(12) ? "" : reader.GetString(12); model.LayoutPositionName = reader.IsDBNull(13) ? "" : reader.GetString(13); } } } return(model); }
public IList <AdBaseInfo> GetList(string sqlWhere, params SqlParameter[] cmdParms) { StringBuilder sb = new StringBuilder(250); sb.Append(@"select Id,Title,SiteFunId,LayoutPositionId,Timeout,Sort,StartTime,EndTime,VirtualViewCount,ViewCount,IsDisable,LastUpdatedDate from AdBase "); if (!string.IsNullOrEmpty(sqlWhere)) { sb.AppendFormat(" where 1=1 {0} ", sqlWhere); } IList <AdBaseInfo> list = new List <AdBaseInfo>(); using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.SqlProviderConnString, CommandType.Text, sb.ToString(), cmdParms)) { if (reader != null && reader.HasRows) { while (reader.Read()) { AdBaseInfo model = new AdBaseInfo(); model.Id = reader.GetGuid(0); model.Title = reader.GetString(1); model.SiteFunId = reader.GetGuid(2); model.LayoutPositionId = reader.GetGuid(3); model.Timeout = reader.GetInt32(4); model.Sort = reader.GetInt32(5); model.StartTime = reader.GetDateTime(6); model.EndTime = reader.GetDateTime(7); model.VirtualViewCount = reader.GetInt32(8); model.ViewCount = reader.GetInt32(9); model.IsDisable = reader.GetBoolean(10); model.LastUpdatedDate = reader.GetDateTime(11); list.Add(model); } } } return(list); }
public int InsertByOutput(AdBaseInfo model) { StringBuilder sb = new StringBuilder(250); sb.Append(@"insert into AdBase (Id,Title,SiteFunId,LayoutPositionId,Timeout,Sort,StartTime,EndTime,VirtualViewCount,ViewCount,IsDisable,LastUpdatedDate) values (@Id,@Title,@SiteFunId,@LayoutPositionId,@Timeout,@Sort,@StartTime,@EndTime,@VirtualViewCount,@ViewCount,@IsDisable,@LastUpdatedDate) " ); SqlParameter[] parms = { new SqlParameter("@Id", SqlDbType.UniqueIdentifier), new SqlParameter("@Title", SqlDbType.NVarChar, 100), new SqlParameter("@SiteFunId", SqlDbType.UniqueIdentifier), new SqlParameter("@LayoutPositionId", SqlDbType.UniqueIdentifier), new SqlParameter("@Timeout", SqlDbType.Int), new SqlParameter("@Sort", SqlDbType.Int), new SqlParameter("@StartTime", SqlDbType.DateTime), new SqlParameter("@EndTime", SqlDbType.DateTime), new SqlParameter("@VirtualViewCount", SqlDbType.Int), new SqlParameter("@ViewCount", SqlDbType.Int), new SqlParameter("@IsDisable", SqlDbType.Bit), new SqlParameter("@LastUpdatedDate", SqlDbType.DateTime) }; parms[0].Value = Guid.Parse(model.Id.ToString()); parms[1].Value = model.Title; parms[2].Value = model.SiteFunId; parms[3].Value = model.LayoutPositionId; parms[4].Value = model.Timeout; parms[5].Value = model.Sort; parms[6].Value = model.StartTime; parms[7].Value = model.EndTime; parms[8].Value = model.VirtualViewCount; parms[9].Value = model.ViewCount; parms[10].Value = model.IsDisable; parms[11].Value = model.LastUpdatedDate; return(SqlHelper.ExecuteNonQuery(SqlHelper.SqlProviderConnString, CommandType.Text, sb.ToString(), parms)); }
public AdBaseInfo GetModel(object Id) { AdBaseInfo model = null; StringBuilder sb = new StringBuilder(300); sb.Append(@"select top 1 Id,Title,SiteFunId,LayoutPositionId,Timeout,Sort,StartTime,EndTime,VirtualViewCount,ViewCount,IsDisable,LastUpdatedDate from AdBase where Id = @Id " ); SqlParameter parm = new SqlParameter("@Id", SqlDbType.UniqueIdentifier); parm.Value = Guid.Parse(Id.ToString()); using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.SqlProviderConnString, CommandType.Text, sb.ToString(), parm)) { if (reader != null) { if (reader.Read()) { model = new AdBaseInfo(); model.Id = reader.GetGuid(0); model.Title = reader.GetString(1); model.SiteFunId = reader.GetGuid(2); model.LayoutPositionId = reader.GetGuid(3); model.Timeout = reader.GetInt32(4); model.Sort = reader.GetInt32(5); model.StartTime = reader.GetDateTime(6); model.EndTime = reader.GetDateTime(7); model.VirtualViewCount = reader.GetInt32(8); model.ViewCount = reader.GetInt32(9); model.IsDisable = reader.GetBoolean(10); model.LastUpdatedDate = reader.GetDateTime(11); } } } return(model); }
public IList <AdBaseInfo> GetList() { StringBuilder sb = new StringBuilder(250); sb.Append(@"select Id,Title,SiteFunId,LayoutPositionId,Timeout,Sort,StartTime,EndTime,VirtualViewCount,ViewCount,IsDisable,LastUpdatedDate from AdBase order by LastUpdatedDate desc " ); IList <AdBaseInfo> list = new List <AdBaseInfo>(); using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.SqlProviderConnString, CommandType.Text, sb.ToString())) { if (reader != null && reader.HasRows) { while (reader.Read()) { AdBaseInfo model = new AdBaseInfo(); model.Id = reader.GetGuid(0); model.Title = reader.GetString(1); model.SiteFunId = reader.GetGuid(2); model.LayoutPositionId = reader.GetGuid(3); model.Timeout = reader.GetInt32(4); model.Sort = reader.GetInt32(5); model.StartTime = reader.GetDateTime(6); model.EndTime = reader.GetDateTime(7); model.VirtualViewCount = reader.GetInt32(8); model.ViewCount = reader.GetInt32(9); model.IsDisable = reader.GetBoolean(10); model.LastUpdatedDate = reader.GetDateTime(11); list.Add(model); } } } return(list); }
/// <summary> /// 广告基本信息提交 /// </summary> /// <param name="context"></param> private void SaveAdBase(HttpContext context) { try { string sAdId = context.Request.Form["ctl00$cphMain$hAdId"].Trim(); string title = context.Request.Form["ctl00$cphMain$txtTitle"].Trim(); string sSiteFunId = context.Request.Form["siteFunId"].Trim(); string sLayoutPositionId = context.Request.Form["layoutPositionId"].Trim(); string sTimeout = context.Request.Form["ctl00$cphMain$txtTimeout"].Trim(); string sSort = context.Request.Form["ctl00$cphMain$txtSort"].Trim(); string sStartTime = context.Request.Form["ctl00$cphMain$txtStartTime"].Trim(); string sEndTime = context.Request.Form["ctl00$cphMain$txtEndTime"].Trim(); string sVirtualViewCount = context.Request.Form["ctl00$cphMain$txtVirtualViewCount"].Trim(); string sIsDisable = context.Request.Form["isDisable"].Trim(); int timeout = 0; if (!string.IsNullOrWhiteSpace(sTimeout)) { int.TryParse(sTimeout, out timeout); } Guid siteFunId = Guid.Empty; Guid.TryParse(sSiteFunId, out siteFunId); Guid layoutPositionId = Guid.Empty; Guid.TryParse(sLayoutPositionId, out layoutPositionId); int sort = 0; if (!string.IsNullOrWhiteSpace(sSort)) { int.TryParse(sSort, out sort); } int virtualViewCount = 0; if (!string.IsNullOrWhiteSpace(sVirtualViewCount)) { int.TryParse(sVirtualViewCount, out virtualViewCount); } DateTime startTime = DateTime.MinValue; if (!string.IsNullOrWhiteSpace(sStartTime)) { DateTime.TryParse(sStartTime, out startTime); } DateTime endTime = DateTime.MinValue; if (!string.IsNullOrWhiteSpace(sEndTime)) { DateTime.TryParse(sEndTime, out endTime); } Guid adId = Guid.Empty; if (!string.IsNullOrWhiteSpace(sAdId)) { Guid.TryParse(sAdId, out adId); } AdBaseInfo model = new AdBaseInfo(); model.Id = adId; model.LastUpdatedDate = DateTime.Now; model.Title = title; model.Timeout = timeout; model.Sort = sort; model.StartTime = startTime; model.EndTime = endTime; model.VirtualViewCount = virtualViewCount; model.ViewCount = 0; model.SiteFunId = siteFunId; model.LayoutPositionId = layoutPositionId; model.IsDisable = sIsDisable == "1" ? true : false; AdBase bll = new AdBase(); object adIdOutput = Guid.Empty; int effect = -1; using (TransactionScope scope = new TransactionScope()) { if (!adId.Equals(Guid.Empty)) { var oldModel = bll.GetModel(adId); if (oldModel == null) { context.Response.Write("{\"success\": false,\"message\": \"" + MessageContent.GetString(MessageContent.Request_NotExist, "广告ID【" + adId + "】") + "\"}"); return; } model.ViewCount = oldModel.ViewCount; effect = bll.Update(model); } else { model.Id = Guid.NewGuid(); effect = bll.InsertByOutput(model); } adIdOutput = model.Id; scope.Complete(); } if (effect == 110) { context.Response.Write("{\"success\": false,\"message\": \"" + MessageContent.Submit_Exist + "\"}"); return; } if (effect != 1) { context.Response.Write("{\"success\": false,\"message\": \"操作失败,原因:请正确输入并重试,如果再出现此问题请联系管理员\"}"); return; } context.Response.Write("{\"success\": true,\"message\": \"" + MessageContent.Submit_Success + "\",\"data\": \"" + adIdOutput + "\"}"); } catch (Exception ex) { context.Response.Write("{\"success\": false,\"message\": \"" + MessageContent.AlertTitle_Ex_Error + ":" + ex.Message + "\"}"); } }
public int InsertByOutput(AdBaseInfo model) { return(dal.InsertByOutput(model)); }
/// <summary> /// 修改数据 /// </summary> /// <param name="model"></param> /// <returns></returns> public int Update(AdBaseInfo model) { return(dal.Update(model)); }