/// <summary>
/// 添加数据到数据库
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public int Insert(Model.SystemProfileInfo model)
{
if (model == null)
{
return(-1);
}
//判断当前记录是否存在，如果存在则返回;
if (IsExist(model.Title, ""))
{
return(110);
}
string cmdText = "insert into [SystemProfile] (NumberID,Title,ContentText,LastUpdatedDate) values (@NumberID,@Title,@ContentText,@LastUpdatedDate)";
//创建查询命令参数集
SqlParameter[] parms =
{
new SqlParameter("@NumberID", SqlDbType.VarChar, 40),
new SqlParameter("@Title", SqlDbType.NVarChar, 50),
new SqlParameter("@ContentText", SqlDbType.NText),
new SqlParameter("@LastUpdatedDate", SqlDbType.DateTime)
};
parms[0].Value = NumberID.GetNumberID();
parms[1].Value = model.Title;
parms[2].Value = model.ContentText;
parms[3].Value = model.LastUpdatedDate;
//执行数据库操作
return(SqlHelper.ExecuteNonQuery(SqlHelper.SqlProviderConnString, CommandType.Text, cmdText, parms));
}
/// <summary>
/// 修改数据
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public int Update(Model.SystemProfileInfo model)
{
if (model == null)
{
return(-1);
}
if (IsExist(model.Title, model.NumberID))
{
return(110);
}
//定义查询命令
string cmdText = @"update [SystemProfile] set Title = @Title,ContentText = @ContentText,LastUpdatedDate = @LastUpdatedDate where NumberID = @NumberID";
//创建查询命令参数集
SqlParameter[] parms =
{
new SqlParameter("@NumberID", SqlDbType.VarChar, 40),
new SqlParameter("@Title", SqlDbType.NVarChar, 50),
new SqlParameter("@ContentText", SqlDbType.NText),
new SqlParameter("@LastUpdatedDate", SqlDbType.DateTime)
};
parms[0].Value = model.NumberID;
parms[1].Value = model.Title;
parms[2].Value = model.ContentText;
parms[3].Value = model.LastUpdatedDate;
return(SqlHelper.ExecuteNonQuery(SqlHelper.SqlProviderConnString, CommandType.Text, cmdText, parms));
}
/// <summary>
/// 获取对应的数据
/// </summary>
/// <param name="numberId"></param>
/// <returns></returns>
public Model.SystemProfileInfo GetModel(string numberId)
{
Model.SystemProfileInfo model = null;
string cmdText = @"select top 1 NumberID,Title,ContentText,LastUpdatedDate from [SystemProfile] 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.SystemProfileInfo();
model.NumberID = reader["NumberID"].ToString();
model.Title = reader["Title"].ToString();
model.ContentText = reader["ContentText"].ToString();
model.LastUpdatedDate = DateTime.Parse(reader["LastUpdatedDate"].ToString());
}
}
}
return(model);
}
private void Bind()
{
if (!string.IsNullOrEmpty(nId))
{
if (bll == null)
{
bll = new BLL.SystemProfile();
}
Model.SystemProfileInfo model = bll.GetModel(nId);
if (model != null)
{
txtTitle.Value = model.Title;
hEditor1.Value = model.ContentText;
}
}
}
/// <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.SystemProfileInfo> GetList(int pageIndex, int pageSize, out int totalCount, string sqlWhere, params SqlParameter[] commandParameters)
{
//获取数据集总数
string cmdText = "select count(*) from [SystemProfile] ";
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 LastUpdatedDate desc) as RowNumber,NumberID,Title,ContentText,LastUpdatedDate from [SystemProfile] ";
if (!string.IsNullOrEmpty(sqlWhere))
{
cmdText += "where 1=1 " + sqlWhere;
}
cmdText += ")as objTable where RowNumber between " + startIndex + " and " + endIndex + " ";
IList <Model.SystemProfileInfo> list = null;
using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.SqlProviderConnString, CommandType.Text, cmdText, commandParameters))
{
if (reader != null && reader.HasRows)
{
list = new List <Model.SystemProfileInfo>();
while (reader.Read())
{
Model.SystemProfileInfo model = new Model.SystemProfileInfo();
model.NumberID = reader["NumberID"].ToString();
model.Title = reader["Title"].ToString();
model.ContentText = reader["ContentText"].ToString();
model.LastUpdatedDate = DateTime.Parse(reader["LastUpdatedDate"].ToString());
list.Add(model);
}
}
}
return(list);
}
/// <summary>
/// 保存数据
/// </summary>
private void OnSave()
{
#region 获取输入并验证
string sTitle = txtTitle.Value.Trim();
string sContentText = hEditor1.Value;
#endregion
if (bll == null)
{
bll = new BLL.SystemProfile();
}
Model.SystemProfileInfo model = new Model.SystemProfileInfo();
model.Title = sTitle;
model.ContentText = sContentText;
model.LastUpdatedDate = DateTime.Now;
int result = -1;
if (!string.IsNullOrEmpty(nId))
{
model.NumberID = nId;
result = bll.Update(model);
}
else
{
result = bll.Insert(model);
}
if (result == 110)
{
WebHelper.MessageBox.Messager(this.Page, lbtnSave, "已存在相同记录！");
return;
}
if (result > 0)
{
WebHelper.MessageBox.MessagerShow(this.Page, lbtnSave, "提交成功！");
}
else
{
WebHelper.MessageBox.Messager(this.Page, lbtnSave, "提交失败,系统异常！", "系统提示");
}
}
/// <summary>
/// 获取系统预设，并绑定到页面显示
/// </summary>
/// <param name="productId"></param>
private void BindSystemProfile(Literal ltr, string title)
{
if (!string.IsNullOrEmpty(productId))
{
if (list == null)
{
if (syspBll == null)
{
syspBll = new BLL.SystemProfile();
}
list = syspBll.GetModelInTitle("'关于本站','产品服务','常见问题'");
}
if (list != null)
{
Model.SystemProfileInfo model = list.Find(delegate(Model.SystemProfileInfo m) { return(m.Title == title); });
if (model != null)
{
ltr.Text = HttpUtility.UrlDecode(model.ContentText);
}
}
}
}
/// <summary>
/// 获取当前标题对应的数据
/// </summary>
/// <param name="titleAppend"></param>
/// <returns></returns>
public List <Model.SystemProfileInfo> GetModelInTitle(string titleAppend)
{
List <Model.SystemProfileInfo> list = null;
string cmdText = "select Title,ContentText from SystemProfile where Title in(" + titleAppend + ")";
using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.SqlProviderConnString, CommandType.Text, cmdText))
{
if (reader != null)
{
list = new List <Model.SystemProfileInfo>();
while (reader.Read())
{
Model.SystemProfileInfo model = new Model.SystemProfileInfo();
model.Title = reader.GetString(0);
model.ContentText = reader.GetString(1);
list.Add(model);
}
}
}
return(list);
}
/// <summary>
/// 修改数据
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public int Update(Model.SystemProfileInfo model)
{
return(dal.Update(model));
}
/// <summary>
/// 添加数据到数据库
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public int Insert(Model.SystemProfileInfo model)
{
return(dal.Insert(model));
}