public void Insert(Category3ReviewItemInfo oParam)
{
string _reviewID = "";
string _tmpStr = "";
int _tmpInt = -1;
string sql = "select max(id) from Category3_ReviewItem where C3SysNo=" + oParam.C3SysNo.ToString();
_tmpStr = SqlHelper.ExecuteScalar(sql).ToString();
if (_tmpStr.Trim() == "")
{
_tmpStr = "0";
}
if (!Util.IsInteger(_tmpStr))
{
throw new BizException("对不起，生成编号错误，不能添加");
}
_tmpInt = Convert.ToInt32(_tmpStr);
_tmpInt++;
_reviewID = _tmpInt.ToString();
oParam.ID = _reviewID;
new ProductReviewDac().Insert(oParam);
}
public int SetOrderNum(Category3ReviewItemInfo oParam)
{
string sql = "update category3_reviewitem set ordernum = " + oParam.OrderNum + " where sysno = " + oParam.SysNo;
SqlCommand cmd = new SqlCommand(sql);
return(SqlHelper.ExecuteNonQuery(cmd));
}
private void map(Category3ReviewItemInfo oParam, DataRow tempdr)
{
oParam.SysNo = Util.TrimIntNull(tempdr["SysNo"]);
oParam.C3SysNo = Util.TrimIntNull(tempdr["C3SysNo"]);
oParam.ID = Util.TrimNull(tempdr["ID"]);
oParam.Name = Util.TrimNull(tempdr["Name"]);
oParam.Description = Util.TrimNull(tempdr["Description"]);
oParam.Weight = Util.TrimIntNull(tempdr["Weight"]);
oParam.OrderNum = Util.TrimIntNull(tempdr["OrderNum"]);
oParam.Status = Util.TrimIntNull(tempdr["Status"]);
}
public Category3ReviewItemInfo LoadCategory3ReviewItem(int paramSysNo)
{
string sql = "select * from category3_reviewitem where sysno=" + paramSysNo;
DataSet ds = SqlHelper.ExecuteDataSet(sql);
if (!Util.HasMoreRow(ds))
{
return(null);
}
Category3ReviewItemInfo item = new Category3ReviewItemInfo();
map(item, ds.Tables[0].Rows[0]);
return(item);
}
public int GetCatetory3ReviewItemNewOrderNum(Category3ReviewItemInfo oParam)
{
string sql = "select isnull(max(OrderNum),0)+1 as newOrderNum from category3_reviewitem where c3sysno=" + oParam.C3SysNo;
DataSet ds = SqlHelper.ExecuteDataSet(sql);
if (Util.HasMoreRow(ds))
{
return(Int32.Parse(ds.Tables[0].Rows[0][0].ToString()));
}
else
{
return(1);
}
}
public SortedList GetCategory3ReviewItemList(int paramC3SysNo)
{
string sql = " select * from category3_reviewitem where c3sysno = " + paramC3SysNo + " order by ordernum";
DataSet ds = SqlHelper.ExecuteDataSet(sql);
if (!Util.HasMoreRow(ds))
{
return(null);
}
SortedList sl = new SortedList(ds.Tables[0].Rows.Count);
foreach (DataRow dr in ds.Tables[0].Rows)
{
Category3ReviewItemInfo item = new Category3ReviewItemInfo();
map(item, dr);
sl.Add(item, null);
}
return(sl);
}
public void MoveBottom(Category3ReviewItemInfo oParam)
{
SortedList sl = GetCategory3ReviewItemList(oParam.C3SysNo);
if (sl == null)
{
throw new BizException("no attributes");
}
if (oParam.OrderNum == sl.Count)
{
throw new BizException("it's the last one, can't be moved down");
}
TransactionOptions options = new TransactionOptions();
options.IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted;
options.Timeout = TransactionManager.DefaultTimeout;
using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, options))
{
ProductReviewDac o = new ProductReviewDac();
foreach (Category3ReviewItemInfo item in sl.Keys)
{
if (item.OrderNum > oParam.OrderNum)
{
item.OrderNum = item.OrderNum - 1;
o.SetOrderNum(item);
}
}
oParam.OrderNum = sl.Count;
o.SetOrderNum(oParam);
scope.Complete();
}
}
public void MoveTop(Category3ReviewItemInfo oParam)
{
if (oParam.OrderNum == 1)
{
throw new BizException("it's the top one already");
}
SortedList sl = GetCategory3ReviewItemList(oParam.C3SysNo);
if (sl == null)
{
throw new BizException("no item for this third category");
}
TransactionOptions options = new TransactionOptions();
options.IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted;
options.Timeout = TransactionManager.DefaultTimeout;
using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, options))
{
ProductReviewDac o = new ProductReviewDac();
foreach (Category3ReviewItemInfo item in sl.Keys)
{
if (item.OrderNum < oParam.OrderNum)
{
item.OrderNum = item.OrderNum + 1;
o.SetOrderNum(item);
}
}
oParam.OrderNum = 1;
o.SetOrderNum(oParam);
scope.Complete();
}
}
public int GetCatetory3ReviewItemNewOrderNum(Category3ReviewItemInfo oParam)
{
return(new ProductReviewDac().GetCatetory3ReviewItemNewOrderNum(oParam));
}
public void Update(Category3ReviewItemInfo oParam)
{
new ProductReviewDac().Update(oParam);
}
public int Insert(Category3ReviewItemInfo oParam)
{
string sql = @"INSERT INTO Category3_ReviewItem
(
C3SysNo, ID, Name, Description,
Weight, OrderNum, Status
)
VALUES (
@C3SysNo, @ID, @Name, @Description,
@Weight, @OrderNum, @Status
);set @SysNo = SCOPE_IDENTITY();";
SqlCommand cmd = new SqlCommand(sql);
SqlParameter paramSysNo = new SqlParameter("@SysNo", SqlDbType.Int, 4);
SqlParameter paramC3SysNo = new SqlParameter("@C3SysNo", SqlDbType.Int, 4);
SqlParameter paramID = new SqlParameter("@ID", SqlDbType.NVarChar, 10);
SqlParameter paramName = new SqlParameter("@Name", SqlDbType.NVarChar, 100);
SqlParameter paramDescription = new SqlParameter("@Description", SqlDbType.Text, 0);
SqlParameter paramWeight = new SqlParameter("@Weight", SqlDbType.Int, 4);
SqlParameter paramOrderNum = new SqlParameter("@OrderNum", SqlDbType.Int, 4);
SqlParameter paramStatus = new SqlParameter("@Status", SqlDbType.Int, 4);
paramSysNo.Direction = ParameterDirection.Output;
if (oParam.C3SysNo != AppConst.IntNull)
{
paramC3SysNo.Value = oParam.C3SysNo;
}
else
{
paramC3SysNo.Value = System.DBNull.Value;
}
if (oParam.ID != AppConst.StringNull)
{
paramID.Value = oParam.ID;
}
else
{
paramID.Value = System.DBNull.Value;
}
if (oParam.Name != AppConst.StringNull)
{
paramName.Value = oParam.Name;
}
else
{
paramName.Value = System.DBNull.Value;
}
if (oParam.Description != AppConst.StringNull)
{
paramDescription.Value = oParam.Description;
}
else
{
paramDescription.Value = System.DBNull.Value;
}
if (oParam.Weight != AppConst.IntNull)
{
paramWeight.Value = oParam.Weight;
}
else
{
paramWeight.Value = System.DBNull.Value;
}
if (oParam.OrderNum != AppConst.IntNull)
{
paramOrderNum.Value = oParam.OrderNum;
}
else
{
paramOrderNum.Value = System.DBNull.Value;
}
if (oParam.Status != AppConst.IntNull)
{
paramStatus.Value = oParam.Status;
}
else
{
paramStatus.Value = System.DBNull.Value;
}
cmd.Parameters.Add(paramSysNo);
cmd.Parameters.Add(paramC3SysNo);
cmd.Parameters.Add(paramID);
cmd.Parameters.Add(paramName);
cmd.Parameters.Add(paramDescription);
cmd.Parameters.Add(paramWeight);
cmd.Parameters.Add(paramOrderNum);
cmd.Parameters.Add(paramStatus);
return(SqlHelper.ExecuteNonQuery(cmd, out oParam.SysNo));
}
public int Update(Category3ReviewItemInfo oParam)
{
string sql = @"UPDATE Category3_ReviewItem SET
C3SysNo=@C3SysNo, ID=@ID,
Name=@Name, Description=@Description,
Weight=@Weight, OrderNum=@OrderNum,
Status=@Status
WHERE SysNo=@SysNo";
SqlCommand cmd = new SqlCommand(sql);
SqlParameter paramSysNo = new SqlParameter("@SysNo", SqlDbType.Int, 4);
SqlParameter paramC3SysNo = new SqlParameter("@C3SysNo", SqlDbType.Int, 4);
SqlParameter paramID = new SqlParameter("@ID", SqlDbType.NVarChar, 10);
SqlParameter paramName = new SqlParameter("@Name", SqlDbType.NVarChar, 100);
SqlParameter paramDescription = new SqlParameter("@Description", SqlDbType.Text, 0);
SqlParameter paramWeight = new SqlParameter("@Weight", SqlDbType.Int, 4);
SqlParameter paramOrderNum = new SqlParameter("@OrderNum", SqlDbType.Int, 4);
SqlParameter paramStatus = new SqlParameter("@Status", SqlDbType.Int, 4);
if (oParam.SysNo != AppConst.IntNull)
{
paramSysNo.Value = oParam.SysNo;
}
else
{
paramSysNo.Value = System.DBNull.Value;
}
if (oParam.C3SysNo != AppConst.IntNull)
{
paramC3SysNo.Value = oParam.C3SysNo;
}
else
{
paramC3SysNo.Value = System.DBNull.Value;
}
if (oParam.ID != AppConst.StringNull)
{
paramID.Value = oParam.ID;
}
else
{
paramID.Value = System.DBNull.Value;
}
if (oParam.Name != AppConst.StringNull)
{
paramName.Value = oParam.Name;
}
else
{
paramName.Value = System.DBNull.Value;
}
if (oParam.Description != AppConst.StringNull)
{
paramDescription.Value = oParam.Description;
}
else
{
paramDescription.Value = System.DBNull.Value;
}
if (oParam.Weight != AppConst.IntNull)
{
paramWeight.Value = oParam.Weight;
}
else
{
paramWeight.Value = System.DBNull.Value;
}
if (oParam.OrderNum != AppConst.IntNull)
{
paramOrderNum.Value = oParam.OrderNum;
}
else
{
paramOrderNum.Value = System.DBNull.Value;
}
if (oParam.Status != AppConst.IntNull)
{
paramStatus.Value = oParam.Status;
}
else
{
paramStatus.Value = System.DBNull.Value;
}
cmd.Parameters.Add(paramSysNo);
cmd.Parameters.Add(paramC3SysNo);
cmd.Parameters.Add(paramID);
cmd.Parameters.Add(paramName);
cmd.Parameters.Add(paramDescription);
cmd.Parameters.Add(paramWeight);
cmd.Parameters.Add(paramOrderNum);
cmd.Parameters.Add(paramStatus);
return(SqlHelper.ExecuteNonQuery(cmd));
}
