public void MoveBottom(WebBulletinInfo oParam)
{
SortedList sl = GetWebBulletinList();
if (sl == null)
{
throw new BizException("no items");
}
TransactionOptions options = new TransactionOptions();
options.IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted;
options.Timeout = TransactionManager.DefaultTimeout;
using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, options))
{
WebBulletinDac o = new WebBulletinDac();
foreach (WebBulletinInfo 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 int SetOrderNum(WebBulletinInfo oParam)
{
string sql = "update WebBulletin set ordernum = " + oParam.OrderNum + " where sysno = " + oParam.SysNo;
SqlCommand cmd = new SqlCommand(sql);
return(SqlHelper.ExecuteNonQuery(cmd));
}
private void map(WebBulletinInfo oParam, DataRow tempdr)
{
oParam.SysNo = Util.TrimIntNull(tempdr["SysNo"]);
oParam.Title = Util.TrimNull(tempdr["Title"]);
oParam.Content = Util.TrimNull(tempdr["Content"]);
oParam.CreateDate = Util.TrimDateNull(tempdr["CreateDate"]);
oParam.OrderNum = Util.TrimIntNull(tempdr["OrderNum"]);
oParam.Status = Util.TrimIntNull(tempdr["Status"]);
}
public WebBulletinInfo LoadWebBulletin(int SysNo)
{
string sql = @"select * from WebBulletin where sysno=" + SysNo;
DataSet ds = SqlHelper.ExecuteDataSet(sql);
if (!Util.HasMoreRow(ds))
{
return(null);
}
WebBulletinInfo o = new WebBulletinInfo();
map(o, ds.Tables[0].Rows[0]);
return(o);
}
public SortedList GetWebBulletinList(int Top)
{
string sql = @"select top " + Top + " * from WebBulletin 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)
{
WebBulletinInfo item = new WebBulletinInfo();
map(item, dr);
sl.Add(item, null);
}
return(sl);
}
public void Update(WebBulletinInfo oParam)
{
new WebBulletinDac().Update(oParam);
}
public void Insert(WebBulletinInfo oParam)
{
new WebBulletinDac().Insert(oParam);
}
public int Update(WebBulletinInfo oParam)
{
string sql = @"UPDATE WebBulletin SET
Title=@Title, Content=@Content,
CreateDate=@CreateDate, OrderNum=@OrderNum,
Status=@Status
WHERE SysNo=@SysNo";
SqlCommand cmd = new SqlCommand(sql);
SqlParameter paramSysNo = new SqlParameter("@SysNo", SqlDbType.Int, 4);
SqlParameter paramTitle = new SqlParameter("@Title", SqlDbType.NVarChar, 500);
SqlParameter paramContent = new SqlParameter("@Content", SqlDbType.Text, 0);
SqlParameter paramCreateDate = new SqlParameter("@CreateDate", 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 = System.DBNull.Value;
}
if (oParam.Title != AppConst.StringNull)
{
paramTitle.Value = oParam.Title;
}
else
{
paramTitle.Value = System.DBNull.Value;
}
if (oParam.Content != AppConst.StringNull)
{
paramContent.Value = oParam.Content;
}
else
{
paramContent.Value = System.DBNull.Value;
}
if (oParam.CreateDate != AppConst.DateTimeNull)
{
paramCreateDate.Value = oParam.CreateDate;
}
else
{
paramCreateDate.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(paramTitle);
cmd.Parameters.Add(paramContent);
cmd.Parameters.Add(paramCreateDate);
cmd.Parameters.Add(paramOrderNum);
cmd.Parameters.Add(paramStatus);
return(SqlHelper.ExecuteNonQuery(cmd));
}
public int Insert(WebBulletinInfo oParam)
{
string sql = @"INSERT INTO WebBulletin
(
Title, Content, CreateDate, OrderNum,
Status
)
VALUES (
@Title, @Content, @CreateDate, @OrderNum,
@Status
);set @SysNo = SCOPE_IDENTITY();";
SqlCommand cmd = new SqlCommand(sql);
SqlParameter paramSysNo = new SqlParameter("@SysNo", SqlDbType.Int, 4);
SqlParameter paramTitle = new SqlParameter("@Title", SqlDbType.NVarChar, 500);
SqlParameter paramContent = new SqlParameter("@Content", SqlDbType.Text, 0);
SqlParameter paramCreateDate = new SqlParameter("@CreateDate", SqlDbType.DateTime);
SqlParameter paramOrderNum = new SqlParameter("@OrderNum", SqlDbType.Int, 4);
SqlParameter paramStatus = new SqlParameter("@Status", SqlDbType.Int, 4);
paramSysNo.Direction = ParameterDirection.Output;
if (oParam.Title != AppConst.StringNull)
{
paramTitle.Value = oParam.Title;
}
else
{
paramTitle.Value = System.DBNull.Value;
}
if (oParam.Content != AppConst.StringNull)
{
paramContent.Value = oParam.Content;
}
else
{
paramContent.Value = System.DBNull.Value;
}
if (oParam.CreateDate != AppConst.DateTimeNull)
{
paramCreateDate.Value = oParam.CreateDate;
}
else
{
paramCreateDate.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(paramTitle);
cmd.Parameters.Add(paramContent);
cmd.Parameters.Add(paramCreateDate);
cmd.Parameters.Add(paramOrderNum);
cmd.Parameters.Add(paramStatus);
return(SqlHelper.ExecuteNonQuery(cmd, out oParam.SysNo));
}
