/// <summary> /// 添加数据到数据库 /// </summary> /// <param name="model"></param> /// <returns></returns> public int Insert(Model.SearchKeywordInfo model) { if (model == null) { return(-1); } //判断当前记录是否存在,如果存在则返回; if (IsExist(model.SearchName, "")) { return(110); } string cmdText = "insert into [SearchKeyword] (NumberID,SearchName,TotalCount,LastUpdatedDate,DataCount) values (@NumberID,@SearchName,@TotalCount,@LastUpdatedDate,@DataCount)"; //创建查询命令参数集 SqlParameter[] parms = { new SqlParameter("@NumberID", SqlDbType.UniqueIdentifier), new SqlParameter("@SearchName", SqlDbType.NVarChar, 256), new SqlParameter("@TotalCount", SqlDbType.Int), new SqlParameter("@LastUpdatedDate", SqlDbType.DateTime), new SqlParameter("@DataCount", SqlDbType.Int) }; parms[0].Value = Guid.NewGuid(); parms[1].Value = model.SearchName; parms[2].Value = model.TotalCount; parms[3].Value = model.LastUpdatedDate; parms[4].Value = model.DataCount; //执行数据库操作 return(SqlHelper.ExecuteNonQuery(SqlHelper.SqlProviderConnString, CommandType.Text, cmdText, parms)); }
/// <summary> /// 获取对应的数据 /// </summary> /// <param name="numberId"></param> /// <returns></returns> public Model.SearchKeywordInfo GetModel(string numberId) { Model.SearchKeywordInfo model = null; string cmdText = @"select top 1 NumberID,SearchName,TotalCount,LastUpdatedDate,DataCount from [SearchKeyword] where NumberID = @NumberID order by LastUpdatedDate desc "; SqlParameter parm = new SqlParameter("@NumberID", SqlDbType.VarChar, 50); parm.Value = numberId; using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.SqlProviderConnString, CommandType.Text, cmdText, parm)) { if (reader != null) { while (reader.Read()) { model = new Model.SearchKeywordInfo(); model.NumberID = reader["NumberID"].ToString(); model.SearchName = reader["SearchName"].ToString(); model.TotalCount = int.Parse(reader["TotalCount"].ToString()); model.LastUpdatedDate = DateTime.Parse(reader["LastUpdatedDate"].ToString()); model.DataCount = int.Parse(reader["DataCount"].ToString()); } } } return(model); }
private void Bind() { if (!string.IsNullOrEmpty(nId)) { if (bll == null) { bll = new BLL.SearchKeyword(); } Model.SearchKeywordInfo model = bll.GetModel(nId); if (model != null) { txtSearchName.Value = model.SearchName; txtTotalCount.Value = model.TotalCount.ToString(); txtDataCount.Value = model.DataCount.ToString(); } } }
/// <summary> /// 获取数据分页列表,并返回所有记录数 /// </summary> /// <param name="pageIndex"></param> /// <param name="pageSize"></param> /// <param name="totalCount"></param> /// <param name="sqlWhere"></param> /// <param name="commandParameters"></param> /// <returns></returns> public IList <Model.SearchKeywordInfo> GetList(int pageIndex, int pageSize, out int totalCount, string sqlWhere, params SqlParameter[] commandParameters) { //获取数据集总数 string cmdText = "select count(*) from [SearchKeyword] t1 "; if (!string.IsNullOrEmpty(sqlWhere)) { cmdText += "where 1=1 " + sqlWhere; } totalCount = (int)SqlHelper.ExecuteScalar(SqlHelper.SqlProviderConnString, CommandType.Text, cmdText, commandParameters); //返回分页数据 int startIndex = (pageIndex - 1) * pageSize + 1; int endIndex = pageIndex * pageSize; cmdText = @"select * from(select row_number() over(order by t1.TotalCount desc,t1.DataCount desc) as RowNumber,t1.NumberID,t1.SearchName,t1.TotalCount,t1.LastUpdatedDate,t1.DataCount from [SearchKeyword] t1 "; if (!string.IsNullOrEmpty(sqlWhere)) { cmdText += "where 1=1 " + sqlWhere; } cmdText += ")as objTable where RowNumber between " + startIndex + " and " + endIndex + " "; IList <Model.SearchKeywordInfo> list = null; using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.SqlProviderConnString, CommandType.Text, cmdText, commandParameters)) { if (reader != null && reader.HasRows) { list = new List <Model.SearchKeywordInfo>(); while (reader.Read()) { Model.SearchKeywordInfo model = new Model.SearchKeywordInfo(); model.NumberID = reader["NumberID"].ToString(); model.SearchName = reader["SearchName"].ToString(); model.TotalCount = int.Parse(reader["TotalCount"].ToString()); model.LastUpdatedDate = DateTime.Parse(reader["LastUpdatedDate"].ToString()); model.DataCount = int.Parse(reader["DataCount"].ToString()); list.Add(model); } } } return(list); }
/// <summary> /// 修改数据 /// </summary> /// <param name="model"></param> /// <returns></returns> public int Update(Model.SearchKeywordInfo model) { if (model == null) { return(-1); } Guid gId; if (!Guid.TryParse(model.NumberID.ToString(), out gId)) { return(-1); } if (IsExist(model.SearchName, model.NumberID.ToString())) { return(110); } //定义查询命令 string cmdText = @"update [SearchKeyword] set SearchName = @SearchName,LoweredSearchName = @LoweredSearchName,TotalCount = @TotalCount,LastUpdatedDate = @LastUpdatedDate,DataCount = @DataCount where NumberID = @NumberID"; //创建查询命令参数集 SqlParameter[] parms = { new SqlParameter("@NumberID", SqlDbType.UniqueIdentifier), new SqlParameter("@SearchName", SqlDbType.NVarChar, 256), new SqlParameter("@LoweredSearchName", SqlDbType.NVarChar, 256), new SqlParameter("@TotalCount", SqlDbType.Int), new SqlParameter("@LastUpdatedDate", SqlDbType.DateTime), new SqlParameter("@DataCount", SqlDbType.Int) }; parms[0].Value = gId; parms[1].Value = model.SearchName; parms[2].Value = model.SearchName.ToLower(); parms[3].Value = model.TotalCount; parms[4].Value = model.LastUpdatedDate; parms[5].Value = model.DataCount; return(SqlHelper.ExecuteNonQuery(SqlHelper.SqlProviderConnString, CommandType.Text, cmdText, parms)); }
/// <summary> /// 保存数据 /// </summary> private void OnSave() { #region 获取输入并验证 string sSearchName = txtSearchName.Value.Trim(); string sTotalCount = txtTotalCount.Value.Trim(); string sDataCount = txtDataCount.Value.Trim(); if (string.IsNullOrEmpty(sSearchName)) { WebHelper.MessageBox.Messager(this.Page, lbtnPostBack, "关键字名称为必填项,请检查", "操作错误", "error"); return; } int totalCount = 0; if (!string.IsNullOrEmpty(sTotalCount)) { if (!int.TryParse(sTotalCount, out totalCount)) { WebHelper.MessageBox.Messager(this.Page, lbtnPostBack, "累计次数正确格式为整数,请检查", "操作错误", "error"); return; } } int dataCount = 0; if (!string.IsNullOrEmpty(sDataCount)) { if (!int.TryParse(sDataCount, out dataCount)) { WebHelper.MessageBox.Messager(this.Page, lbtnPostBack, "数据个数正确格式为整数,请检查", "操作错误", "error"); return; } } #endregion if (bll == null) { bll = new BLL.SearchKeyword(); } if (!string.IsNullOrEmpty(nId)) { Model.SearchKeywordInfo model = new Model.SearchKeywordInfo(); model.NumberID = nId; model.SearchName = sSearchName; model.TotalCount = totalCount; model.LastUpdatedDate = DateTime.Now; model.DataCount = dataCount; if (bll.Update(model) > 0) { WebHelper.MessageBox.MessagerShow(this.Page, lbtnPostBack, "操作成功!"); return; } else { WebHelper.MessageBox.Messager(this.Page, lbtnPostBack, "操作失败,请检查", "系统提示"); return; } } else { if (bll.CreateKeyword(sSearchName, dataCount) > -1) { WebHelper.MessageBox.MessagerShow(this.Page, lbtnPostBack, "操作成功!"); return; } else { WebHelper.MessageBox.Messager(this.Page, lbtnPostBack, "操作失败,请检查", "系统提示"); return; } } }
/// <summary> /// 修改数据 /// </summary> /// <param name="model"></param> /// <returns></returns> public int Update(Model.SearchKeywordInfo model) { return(dal.Update(model)); }
/// <summary> /// 添加数据到数据库 /// </summary> /// <param name="model"></param> /// <returns></returns> public int Insert(Model.SearchKeywordInfo model) { return(dal.Insert(model)); }