public int GetPrjMasterNewOrderNum(PrjMasterInfo oParam)
{
string sql = "select isnull(max(OrderNum),0)+1 as newOrderNum from prj_master where slnsysno=" + oParam.SlnSysNo;
DataSet ds = SqlHelper.ExecuteDataSet(sql);
if (Util.HasMoreRow(ds))
{
return Int32.Parse(ds.Tables[0].Rows[0][0].ToString());
}
else
{
return 1;
}
}
public int Update(PrjMasterInfo oParam)
{
string sql = @"UPDATE Prj_Master SET
SlnSysNo=@SlnSysNo, PrjTypeSysNo=@PrjTypeSysNo,
ID=@ID, Name=@Name,
Title=@Title, Description=@Description,
SysUserSysNo=@SysUserSysNo, DateStamp=@DateStamp,
OrderNum=@OrderNum, Status=@Status
WHERE SysNo=@SysNo";
SqlCommand cmd = new SqlCommand(sql);
SqlParameter paramSysNo = new SqlParameter("@SysNo", SqlDbType.Int, 4);
SqlParameter paramSlnSysNo = new SqlParameter("@SlnSysNo", SqlDbType.Int, 4);
SqlParameter paramPrjTypeSysNo = new SqlParameter("@PrjTypeSysNo", SqlDbType.Int, 4);
SqlParameter paramID = new SqlParameter("@ID", SqlDbType.NVarChar, 50);
SqlParameter paramName = new SqlParameter("@Name", SqlDbType.NVarChar, 500);
SqlParameter paramTitle = new SqlParameter("@Title", SqlDbType.NVarChar, 500);
SqlParameter paramDescription = new SqlParameter("@Description", SqlDbType.Text, 0);
SqlParameter paramSysUserSysNo = new SqlParameter("@SysUserSysNo", SqlDbType.Int, 4);
SqlParameter paramDateStamp = new SqlParameter("@DateStamp", SqlDbType.DateTime);
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 = AppConst.IntNull;
if (oParam.SlnSysNo != AppConst.IntNull)
paramSlnSysNo.Value = oParam.SlnSysNo;
else
paramSlnSysNo.Value = System.DBNull.Value;
if (oParam.PrjTypeSysNo != AppConst.IntNull)
paramPrjTypeSysNo.Value = oParam.PrjTypeSysNo;
else
paramPrjTypeSysNo.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.Title != AppConst.StringNull)
paramTitle.Value = oParam.Title;
else
paramTitle.Value = System.DBNull.Value;
if (oParam.Description != AppConst.StringNull)
paramDescription.Value = oParam.Description;
else
paramDescription.Value = System.DBNull.Value;
if (oParam.SysUserSysNo != AppConst.IntNull)
paramSysUserSysNo.Value = oParam.SysUserSysNo;
else
paramSysUserSysNo.Value = System.DBNull.Value;
if (oParam.DateStamp != AppConst.DateTimeNull)
paramDateStamp.Value = oParam.DateStamp;
else
paramDateStamp.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(paramSlnSysNo);
cmd.Parameters.Add(paramPrjTypeSysNo);
cmd.Parameters.Add(paramID);
cmd.Parameters.Add(paramName);
cmd.Parameters.Add(paramTitle);
cmd.Parameters.Add(paramDescription);
cmd.Parameters.Add(paramSysUserSysNo);
cmd.Parameters.Add(paramDateStamp);
cmd.Parameters.Add(paramOrderNum);
cmd.Parameters.Add(paramStatus);
return SqlHelper.ExecuteNonQuery(cmd);
}
public int SetOrderNum(PrjMasterInfo oParam)
{
string sql = "update prj_master set ordernum = " + oParam.OrderNum + " where sysno = " + oParam.SysNo;
SqlCommand cmd = new SqlCommand(sql);
return SqlHelper.ExecuteNonQuery(cmd);
}
public int Insert(PrjMasterInfo oParam)
{
string sql = @"INSERT INTO Prj_Master
(
SlnSysNo, PrjTypeSysNo, ID, Name,
Title, Description, SysUserSysNo, DateStamp,
OrderNum, Status
)
VALUES (
@SlnSysNo, @PrjTypeSysNo, @ID, @Name,
@Title, @Description, @SysUserSysNo, @DateStamp,
@OrderNum, @Status
);set @SysNo = SCOPE_IDENTITY();";
SqlCommand cmd = new SqlCommand(sql);
SqlParameter paramSysNo = new SqlParameter("@SysNo", SqlDbType.Int, 4);
SqlParameter paramSlnSysNo = new SqlParameter("@SlnSysNo", SqlDbType.Int, 4);
SqlParameter paramPrjTypeSysNo = new SqlParameter("@PrjTypeSysNo", SqlDbType.Int, 4);
SqlParameter paramID = new SqlParameter("@ID", SqlDbType.NVarChar, 50);
SqlParameter paramName = new SqlParameter("@Name", SqlDbType.NVarChar, 500);
SqlParameter paramTitle = new SqlParameter("@Title", SqlDbType.NVarChar, 500);
SqlParameter paramDescription = new SqlParameter("@Description", SqlDbType.Text, 0);
SqlParameter paramSysUserSysNo = new SqlParameter("@SysUserSysNo", SqlDbType.Int, 4);
SqlParameter paramDateStamp = new SqlParameter("@DateStamp", SqlDbType.DateTime);
SqlParameter paramOrderNum = new SqlParameter("@OrderNum", SqlDbType.Int, 4);
SqlParameter paramStatus = new SqlParameter("@Status", SqlDbType.Int, 4);
paramSysNo.Direction = ParameterDirection.Output;
if (oParam.SlnSysNo != AppConst.IntNull)
paramSlnSysNo.Value = oParam.SlnSysNo;
else
paramSlnSysNo.Value = System.DBNull.Value;
if (oParam.PrjTypeSysNo != AppConst.IntNull)
paramPrjTypeSysNo.Value = oParam.PrjTypeSysNo;
else
paramPrjTypeSysNo.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.Title != AppConst.StringNull)
paramTitle.Value = oParam.Title;
else
paramTitle.Value = System.DBNull.Value;
if (oParam.Description != AppConst.StringNull)
paramDescription.Value = oParam.Description;
else
paramDescription.Value = System.DBNull.Value;
if (oParam.SysUserSysNo != AppConst.IntNull)
paramSysUserSysNo.Value = oParam.SysUserSysNo;
else
paramSysUserSysNo.Value = System.DBNull.Value;
if (oParam.DateStamp != AppConst.DateTimeNull)
paramDateStamp.Value = oParam.DateStamp;
else
paramDateStamp.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(paramSlnSysNo);
cmd.Parameters.Add(paramPrjTypeSysNo);
cmd.Parameters.Add(paramID);
cmd.Parameters.Add(paramName);
cmd.Parameters.Add(paramTitle);
cmd.Parameters.Add(paramDescription);
cmd.Parameters.Add(paramSysUserSysNo);
cmd.Parameters.Add(paramDateStamp);
cmd.Parameters.Add(paramOrderNum);
cmd.Parameters.Add(paramStatus);
return SqlHelper.ExecuteNonQuery(cmd, out oParam.SysNo);
}
public PrjMasterInfo LoadPrjMaster(int SlnSysNo, int OrderNum)
{
string sql = @"select * from prj_master where slnsysno=@slnsysno and ordernum=@ordernum";
sql = sql.Replace("@slnsysno", SlnSysNo.ToString()).Replace("@ordernum", OrderNum.ToString());
DataSet ds = SqlHelper.ExecuteDataSet(sql);
PrjMasterInfo oParam = new PrjMasterInfo();
if (Util.HasMoreRow(ds))
mapPrjMaster(oParam, ds.Tables[0].Rows[0]);
else
oParam = null;
return oParam;
}
public void MoveDown(PrjMasterInfo oParam)
{
SortedList sl = GetPrjMasterList(oParam.SlnSysNo);
if (sl == null)
{
throw new BizException("no items");
}
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))
{
PrjDac o = new PrjDac();
foreach (PrjMasterInfo item in sl.Keys)
{
if (item.OrderNum == oParam.OrderNum + 1)
{
item.OrderNum -= 1;
o.SetOrderNum(item);
}
}
oParam.OrderNum += 1;
o.SetOrderNum(oParam);
scope.Complete();
}
}
public int InsertPrjMaster(PrjMasterInfo oParam)
{
string sql = "select * from prj_master where slnsysno = " + oParam.SlnSysNo + " and ID=" + Util.ToSqlString(oParam.ID);
DataSet ds = SqlHelper.ExecuteDataSet(sql);
if (Util.HasMoreRow(ds))
throw new BizException("the same ID exists already");
return new PrjDac().Insert(oParam);
}
public PrjMasterInfo LoadPrjMaster(int SysNo)
{
string sql = "select * from prj_master where sysno=" + SysNo;
DataSet ds = SqlHelper.ExecuteDataSet(sql);
PrjMasterInfo oParam = new PrjMasterInfo();
if (Util.HasMoreRow(ds))
mapPrjMaster(oParam, ds.Tables[0].Rows[0]);
else
oParam = null;
return oParam;
}
public int GetPrjMasterNewOrderNum(PrjMasterInfo oParam)
{
return new PrjDac().GetPrjMasterNewOrderNum(oParam);
}
public SortedList GetPrjMasterList(int SlnSysNo)
{
string sql = @"select * from prj_master where slnsysno = @slnsysno order by ordernum";
sql = sql.Replace("@slnsysno", SlnSysNo.ToString());
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)
{
PrjMasterInfo item = new PrjMasterInfo();
mapPrjMaster(item, dr);
sl.Add(item, null);
}
return sl;
}
private void mapPrjMaster(PrjMasterInfo oParam, DataRow tempdr)
{
oParam.SysNo = Util.TrimIntNull(tempdr["SysNo"]);
oParam.SlnSysNo = Util.TrimIntNull(tempdr["SlnSysNo"]);
oParam.PrjTypeSysNo = Util.TrimIntNull(tempdr["PrjTypeSysNo"]);
oParam.ID = Util.TrimNull(tempdr["ID"]);
oParam.Name = Util.TrimNull(tempdr["Name"]);
oParam.Title = Util.TrimNull(tempdr["Title"]);
oParam.Description = Util.TrimNull(tempdr["Description"]);
oParam.SysUserSysNo = Util.TrimIntNull(tempdr["SysUserSysNo"]);
oParam.DateStamp = Util.TrimDateNull(tempdr["DateStamp"]);
oParam.OrderNum = Util.TrimIntNull(tempdr["OrderNum"]);
oParam.Status = Util.TrimIntNull(tempdr["Status"]);
}
public void UpdatePrjMaster(PrjMasterInfo oParam)
{
TransactionOptions options = new TransactionOptions();
options.IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted;
options.Timeout = TransactionManager.DefaultTimeout;
using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, options))
{
new PrjDac().Update(oParam);
scope.Complete();
}
}
public void MoveTop(PrjMasterInfo oParam)
{
if (oParam.OrderNum == 1)
{
throw new BizException("it's the top one already");
}
SortedList sl = GetPrjMasterList(oParam.SlnSysNo);
if (sl == null)
{
throw new BizException("no item for this solution");
}
TransactionOptions options = new TransactionOptions();
options.IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted;
options.Timeout = TransactionManager.DefaultTimeout;
using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, options))
{
PrjDac o = new PrjDac();
foreach (PrjMasterInfo 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();
}
}