/// <summary>
/// 添加数据到数据库
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public int Insert(Model.SearchKeyword 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.SearchKeyword GetModel(string numberId)
{
Model.SearchKeyword 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.SearchKeyword();
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.SearchKeyword 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.SearchKeyword> 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.SearchKeyword> list = null;
using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.SqlProviderConnString, CommandType.Text, cmdText, commandParameters))
{
if (reader != null && reader.HasRows)
{
list = new List <Model.SearchKeyword>();
while (reader.Read())
{
Model.SearchKeyword model = new Model.SearchKeyword();
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.SearchKeyword 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.SearchKeyword model = new Model.SearchKeyword();
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.SearchKeyword model)
{
return(dal.Update(model));
}
/// <summary>
/// 添加数据到数据库
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public int Insert(Model.SearchKeyword model)
{
return(dal.Insert(model));
}
