public int SetOrderNum(QAInfo oParam)
{
string sql = "update qa set ordernum = " + oParam.OrderNum + " where sysno = " + oParam.SysNo;
SqlCommand cmd = new SqlCommand(sql);
return(SqlHelper.ExecuteNonQuery(cmd));
}
private void Map(QAInfo oParam, DataRow tempdr)
{
oParam.SysNo = Util.TrimIntNull(tempdr["SysNo"]);
oParam.Question = Util.TrimNull(tempdr["Question"]);
oParam.Answer = Util.TrimNull(tempdr["Answer"]);
oParam.SearchKey = Util.TrimNull(tempdr["SearchKey"]);
oParam.Type = Util.TrimIntNull(tempdr["Type"]);
oParam.CreateUserSysNo = Util.TrimIntNull(tempdr["CreateUserSysNo"]);
oParam.CreateTime = Util.TrimDateNull(tempdr["CreateTime"]);
oParam.ViewCount = Util.TrimIntNull(tempdr["ViewCount"]);
oParam.OrderNum = Util.TrimIntNull(tempdr["OrderNum"]);
oParam.Status = Util.TrimIntNull(tempdr["Status"]);
}
public int GetQANewOrderNum(QAInfo oParam)
{
string sql = "select isnull(max(OrderNum),0)+1 as newOrderNum from qa where type=" + oParam.Type;
DataSet ds = SqlHelper.ExecuteDataSet(sql);
if (Util.HasMoreRow(ds))
{
return(Int32.Parse(ds.Tables[0].Rows[0][0].ToString()));
}
else
{
return(1);
}
}
public QAInfo LoadQA(int SysNo)
{
string sql = "select * from QA where sysno=" + SysNo;
DataSet ds = SqlHelper.ExecuteDataSet(sql);
if (Util.HasMoreRow(ds))
{
QAInfo oInfo = new QAInfo();
Map(oInfo, ds.Tables[0].Rows[0]);
return(oInfo);
}
else
{
return(null);
}
}
protected void btnSubmit_Click(object sender, EventArgs e)
{
string title = tbName.Text.Trim();
string catName = ddlCat.SelectedValue;
string content = tbContent.Text;
var bll = new TMS.QA();
if (title.IsNullOrEmpty())
{
MessageBox.Show("标题必须填写！");
return;
}
if (content.IsNullOrEmpty())
{
MessageBox.Show("内容必须填写！");
return;
}
if (QAID == 0)
{
//添加
QAInfo model = new QAInfo();
model.QATitle = title;
model.CatName = catName;
model.QAContent = content;
bll.Add(model);
MessageBox.Show("添加成功！", CurrentUrl);
}
else
{
QAInfo model = bll.Select(QAID);
if (model == null)
{
MessageBox.Show("该问答不存在！", true);
return;
}
model.QATitle = title;
model.CatName = catName;
model.QAContent = content;
bll.Update(model);
MessageBox.Show("修改成功！");
}
}
public SortedList GetQAListByType(int Type)
{
string sql = "select * from QA where type=" + Type + " order by ordernum";
DataSet ds = SqlHelper.ExecuteDataSet(sql);
if (Util.HasMoreRow(ds))
{
SortedList sl = new SortedList();
foreach (DataRow dr in ds.Tables[0].Rows)
{
QAInfo oInfo = new QAInfo();
Map(oInfo, dr);
sl.Add(oInfo, null);
}
return(sl);
}
else
{
return(null);
}
}
public void MoveBottom(QAInfo oParam)
{
SortedList sl = GetQAListByType(oParam.Type);
if (sl == null)
{
throw new BizException("no qa");
}
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))
{
QADac o = new QADac();
foreach (QAInfo 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(QAInfo oParam)
{
if (oParam.OrderNum == 1)
{
throw new BizException("it's the top one already");
}
SortedList sl = GetQAListByType(oParam.Type);
if (sl == null)
{
throw new BizException("no QA for this type");
}
TransactionOptions options = new TransactionOptions();
options.IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted;
options.Timeout = TransactionManager.DefaultTimeout;
using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, options))
{
QADac o = new QADac();
foreach (QAInfo 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 Insert(QAInfo oParam)
{
string sql = @"INSERT INTO QA
(
Question, Answer, SearchKey, Type,
CreateUserSysNo, CreateTime, ViewCount, OrderNum,
Status
)
VALUES (
@Question, @Answer, @SearchKey, @Type,
@CreateUserSysNo, @CreateTime, @ViewCount, @OrderNum,
@Status
);set @SysNo = SCOPE_IDENTITY();";
SqlCommand cmd = new SqlCommand(sql);
SqlParameter paramSysNo = new SqlParameter("@SysNo", SqlDbType.Int, 4);
SqlParameter paramQuestion = new SqlParameter("@Question", SqlDbType.NVarChar, 500);
SqlParameter paramAnswer = new SqlParameter("@Answer", SqlDbType.Text, 0);
SqlParameter paramSearchKey = new SqlParameter("@SearchKey", SqlDbType.NVarChar, 500);
SqlParameter paramType = new SqlParameter("@Type", SqlDbType.Int, 4);
SqlParameter paramCreateUserSysNo = new SqlParameter("@CreateUserSysNo", SqlDbType.Int, 4);
SqlParameter paramCreateTime = new SqlParameter("@CreateTime", SqlDbType.DateTime);
SqlParameter paramViewCount = new SqlParameter("@ViewCount", 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.Question != AppConst.StringNull)
{
paramQuestion.Value = oParam.Question;
}
else
{
paramQuestion.Value = System.DBNull.Value;
}
if (oParam.Answer != AppConst.StringNull)
{
paramAnswer.Value = oParam.Answer;
}
else
{
paramAnswer.Value = System.DBNull.Value;
}
if (oParam.SearchKey != AppConst.StringNull)
{
paramSearchKey.Value = oParam.SearchKey;
}
else
{
paramSearchKey.Value = System.DBNull.Value;
}
if (oParam.Type != AppConst.IntNull)
{
paramType.Value = oParam.Type;
}
else
{
paramType.Value = System.DBNull.Value;
}
if (oParam.CreateUserSysNo != AppConst.IntNull)
{
paramCreateUserSysNo.Value = oParam.CreateUserSysNo;
}
else
{
paramCreateUserSysNo.Value = System.DBNull.Value;
}
if (oParam.CreateTime != AppConst.DateTimeNull)
{
paramCreateTime.Value = oParam.CreateTime;
}
else
{
paramCreateTime.Value = System.DBNull.Value;
}
if (oParam.ViewCount != AppConst.IntNull)
{
paramViewCount.Value = oParam.ViewCount;
}
else
{
paramViewCount.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(paramQuestion);
cmd.Parameters.Add(paramAnswer);
cmd.Parameters.Add(paramSearchKey);
cmd.Parameters.Add(paramType);
cmd.Parameters.Add(paramCreateUserSysNo);
cmd.Parameters.Add(paramCreateTime);
cmd.Parameters.Add(paramViewCount);
cmd.Parameters.Add(paramOrderNum);
cmd.Parameters.Add(paramStatus);
return(SqlHelper.ExecuteNonQuery(cmd, out oParam.SysNo));
}
public int Update(QAInfo oParam)
{
string sql = @"UPDATE QA SET
Question=@Question, Answer=@Answer,
SearchKey=@SearchKey, Type=@Type,
CreateUserSysNo=@CreateUserSysNo, CreateTime=@CreateTime,
ViewCount=@ViewCount, OrderNum=@OrderNum,
Status=@Status
WHERE SysNo=@SysNo";
SqlCommand cmd = new SqlCommand(sql);
SqlParameter paramSysNo = new SqlParameter("@SysNo", SqlDbType.Int, 4);
SqlParameter paramQuestion = new SqlParameter("@Question", SqlDbType.NVarChar, 500);
SqlParameter paramAnswer = new SqlParameter("@Answer", SqlDbType.Text, 0);
SqlParameter paramSearchKey = new SqlParameter("@SearchKey", SqlDbType.NVarChar, 500);
SqlParameter paramType = new SqlParameter("@Type", SqlDbType.Int, 4);
SqlParameter paramCreateUserSysNo = new SqlParameter("@CreateUserSysNo", SqlDbType.Int, 4);
SqlParameter paramCreateTime = new SqlParameter("@CreateTime", SqlDbType.DateTime);
SqlParameter paramViewCount = new SqlParameter("@ViewCount", 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.Question != AppConst.StringNull)
{
paramQuestion.Value = oParam.Question;
}
else
{
paramQuestion.Value = System.DBNull.Value;
}
if (oParam.Answer != AppConst.StringNull)
{
paramAnswer.Value = oParam.Answer;
}
else
{
paramAnswer.Value = System.DBNull.Value;
}
if (oParam.SearchKey != AppConst.StringNull)
{
paramSearchKey.Value = oParam.SearchKey;
}
else
{
paramSearchKey.Value = System.DBNull.Value;
}
if (oParam.Type != AppConst.IntNull)
{
paramType.Value = oParam.Type;
}
else
{
paramType.Value = System.DBNull.Value;
}
if (oParam.CreateUserSysNo != AppConst.IntNull)
{
paramCreateUserSysNo.Value = oParam.CreateUserSysNo;
}
else
{
paramCreateUserSysNo.Value = System.DBNull.Value;
}
if (oParam.CreateTime != AppConst.DateTimeNull)
{
paramCreateTime.Value = oParam.CreateTime;
}
else
{
paramCreateTime.Value = System.DBNull.Value;
}
if (oParam.ViewCount != AppConst.IntNull)
{
paramViewCount.Value = oParam.ViewCount;
}
else
{
paramViewCount.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(paramQuestion);
cmd.Parameters.Add(paramAnswer);
cmd.Parameters.Add(paramSearchKey);
cmd.Parameters.Add(paramType);
cmd.Parameters.Add(paramCreateUserSysNo);
cmd.Parameters.Add(paramCreateTime);
cmd.Parameters.Add(paramViewCount);
cmd.Parameters.Add(paramOrderNum);
cmd.Parameters.Add(paramStatus);
return(SqlHelper.ExecuteNonQuery(cmd));
}
public int GetQANewOrderNum(QAInfo oParam)
{
return(new QADac().GetQANewOrderNum(oParam));
}
public int Update(QAInfo oParam)
{
return(new QADac().Update(oParam));
}
public int Insert(QAInfo oParam)
{
return(new QADac().Insert(oParam));
}
