/// <summary>
/// 修改数据
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public int Update(Model.LotteryItem model)
{
if (model == null)
{
return(-1);
}
if (IsExist(model.ItemName, model.NumberID))
{
return(110);
}
//定义查询命令
string cmdText = @"update [LotteryItem] set ItemName = @ItemName,ItemCode = @ItemCode,ImageUrl = @ImageUrl,FixRatio = @FixRatio,LastUpdatedDate = @LastUpdatedDate where NumberID = @NumberID";
//创建查询命令参数集
SqlParameter[] parms =
{
new SqlParameter("@NumberID", SqlDbType.UniqueIdentifier),
new SqlParameter("@ItemName", SqlDbType.NVarChar, 50),
new SqlParameter("@ItemCode", SqlDbType.NVarChar, 50),
new SqlParameter("@ImageUrl", SqlDbType.NVarChar, 300),
new SqlParameter("@LastUpdatedDate", SqlDbType.DateTime),
new SqlParameter("@FixRatio", SqlDbType.Decimal)
};
parms[0].Value = Guid.Parse(model.NumberID.ToString());
parms[1].Value = model.ItemName;
parms[2].Value = model.ItemCode;
parms[3].Value = model.ImageUrl;
parms[4].Value = model.LastUpdatedDate;
parms[5].Value = model.FixRatio;
return(SqlHelper.ExecuteNonQuery(SqlHelper.SqlProviderConnString, CommandType.Text, cmdText, parms));
}
/// <summary>
/// 添加数据到数据库
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public int Insert(Model.LotteryItem model)
{
if (model == null)
{
return(-1);
}
//判断当前记录是否存在，如果存在则返回;
if (IsExist(model.ItemName, null))
{
return(110);
}
string cmdText = "insert into [LotteryItem] (ItemName,ItemCode,ImageUrl,FixRatio,LastUpdatedDate) values (@ItemName,@ItemCode,@ImageUrl,@FixRatio,@LastUpdatedDate)";
//创建查询命令参数集
SqlParameter[] parms =
{
new SqlParameter("@ItemName", SqlDbType.NVarChar, 50),
new SqlParameter("@ItemCode", SqlDbType.NVarChar, 50),
new SqlParameter("@ImageUrl", SqlDbType.NVarChar, 300),
new SqlParameter("@LastUpdatedDate", SqlDbType.DateTime),
new SqlParameter("@FixRatio", SqlDbType.Decimal)
};
parms[0].Value = model.ItemName;
parms[1].Value = model.ItemCode;
parms[2].Value = model.ImageUrl;
parms[3].Value = model.LastUpdatedDate;
parms[4].Value = model.FixRatio;
//执行数据库操作
return(SqlHelper.ExecuteNonQuery(SqlHelper.SqlProviderConnString, CommandType.Text, cmdText, parms));
}
/// <summary>
/// 获取对应的数据
/// </summary>
/// <param name="numberId"></param>
/// <returns></returns>
public Model.LotteryItem GetModel(string numberId)
{
Model.LotteryItem model = null;
string cmdText = @"select top 1 NumberID,ItemName,ItemCode,ImageUrl,FixRatio,LastUpdatedDate from [LotteryItem] where NumberID = @NumberID order by LastUpdatedDate desc ";
SqlParameter parm = new SqlParameter("@NumberID", SqlDbType.UniqueIdentifier);
parm.Value = Guid.Parse(numberId);
using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.SqlProviderConnString, CommandType.Text, cmdText, parm))
{
if (reader != null)
{
while (reader.Read())
{
model = new Model.LotteryItem();
model.NumberID = reader["NumberID"].ToString();
model.ItemName = reader["ItemName"].ToString();
model.ItemCode = reader["ItemCode"].ToString();
model.ImageUrl = reader["ImageUrl"].ToString();
model.FixRatio = decimal.Parse(reader["FixRatio"].ToString());
model.LastUpdatedDate = DateTime.Parse(reader["LastUpdatedDate"].ToString());
}
}
}
return(model);
}
private void Bind()
{
if (!string.IsNullOrEmpty(nId))
{
if (bll == null)
{
bll = new BLL.LotteryItem();
}
Model.LotteryItem model = bll.GetModel(nId);
if (model != null)
{
txtName.Value = model.ItemName;
txtCode.Value = model.ItemCode;
txtImageUrl.Value = model.ImageUrl;
}
}
}
/// <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 List <Model.LotteryItem> GetList(int pageIndex, int pageSize, out int totalCount, string sqlWhere, params SqlParameter[] commandParameters)
{
//获取数据集总数
string cmdText = "select count(*) from [LotteryItem] 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.LastUpdatedDate desc) as RowNumber,t1.NumberID,t1.ItemName,t1.ItemCode,t1.ImageUrl,FixRatio,t1.LastUpdatedDate from [LotteryItem] t1 ";
if (!string.IsNullOrEmpty(sqlWhere))
{
cmdText += "where 1=1 " + sqlWhere;
}
cmdText += ")as objTable where RowNumber between " + startIndex + " and " + endIndex + " ";
List <Model.LotteryItem> list = null;
using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.SqlProviderConnString, CommandType.Text, cmdText, commandParameters))
{
if (reader != null && reader.HasRows)
{
list = new List <Model.LotteryItem>();
while (reader.Read())
{
Model.LotteryItem model = new Model.LotteryItem();
model.NumberID = reader["NumberID"].ToString();
model.ItemName = reader["ItemName"].ToString();
model.ItemCode = reader["ItemCode"].ToString();
model.ImageUrl = reader["ImageUrl"].ToString();
model.FixRatio = decimal.Parse(reader["FixRatio"].ToString());
model.LastUpdatedDate = DateTime.Parse(reader["LastUpdatedDate"].ToString());
list.Add(model);
}
}
}
return(list);
}
/// <summary>
/// 修改数据
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public int Update(Model.LotteryItem model)
{
return(dal.Update(model));
}
/// <summary>
/// 添加数据到数据库
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public int Insert(Model.LotteryItem model)
{
return(dal.Insert(model));
}
private void OnSave()
{
string sName = txtName.Value.Trim();
if (string.IsNullOrEmpty(sName))
{
WebHelper.MessageBox.Messager(this.Page, lbtnPostBack, "名称不能为空，请检查", "错误提醒", "error");
return;
}
string sCode = txtCode.Value.Trim();
if (string.IsNullOrEmpty(sCode))
{
WebHelper.MessageBox.Messager(this.Page, lbtnPostBack, "代号不能为空，请检查", "错误提醒", "error");
return;
}
string sRatio = txtRatio.Value.Trim();
if (string.IsNullOrEmpty(sRatio))
{
WebHelper.MessageBox.Messager(this.Page, lbtnPostBack, "赔率不能为空，请检查", "错误提醒", "error");
return;
}
decimal ratio = 0;
if (!decimal.TryParse(sRatio, out ratio))
{
WebHelper.MessageBox.Messager(this.Page, lbtnPostBack, "赔率正确格式为整数，请检查", "错误提醒", "error");
return;
}
string sImageUrl = txtImageUrl.Value.Trim();
if (string.IsNullOrEmpty(sImageUrl))
{
WebHelper.MessageBox.Messager(this.Page, lbtnPostBack, "图片不能为空，请检查", "错误提醒", "error");
return;
}
if (bll == null)
{
bll = new BLL.LotteryItem();
}
Model.LotteryItem model = new Model.LotteryItem();
model.ItemName = sName;
model.ItemCode = sCode;
model.ImageUrl = sImageUrl;
model.FixRatio = ratio;
model.LastUpdatedDate = DateTime.Now;
int effectCount = -1;
if (!string.IsNullOrEmpty(nId))
{
model.NumberID = nId;
effectCount = bll.Update(model);
}
else
{
effectCount = bll.Insert(model);
}
if (effectCount == 110)
{
WebHelper.MessageBox.Messager(this.Page, lbtnPostBack, "已存在相同记录", "温馨提醒", "error");
return;
}
if (effectCount > 0)
{
WebHelper.MessageBox.MessagerShow(this.Page, lbtnPostBack, "操作成功");
}
else
{
WebHelper.MessageBox.Messager(this.Page, lbtnPostBack, "操作失败");
}
}
