public int Update(BulletinInfo oParam)
{
//因为只有一条记录，所以update就不指定了。
string sql = @"UPDATE Bulletin SET
Message=@Message,
Status=@Status";
SqlCommand cmd = new SqlCommand(sql);
SqlParameter paramMessage = new SqlParameter("@Message", SqlDbType.NVarChar, 2000);
SqlParameter paramStatus = new SqlParameter("@Status", SqlDbType.Int, 4);
if (oParam.Message != AppConst.StringNull)
{
paramMessage.Value = oParam.Message;
}
else
{
paramMessage.Value = System.DBNull.Value;
}
if (oParam.Status != AppConst.IntNull)
{
paramStatus.Value = oParam.Status;
}
else
{
paramStatus.Value = System.DBNull.Value;
}
cmd.Parameters.Add(paramMessage);
cmd.Parameters.Add(paramStatus);
return(SqlHelper.ExecuteNonQuery(cmd));
}
public void Import()
{
if (!AppConfig.IsImportable)
{
throw new BizException("Is Importable is false");
}
/* do not use the following code after Data Pour in */
string sql = " select top 1 * from bulletin ";
DataSet ds = SqlHelper.ExecuteDataSet(sql);
if (Util.HasMoreRow(ds))
{
throw new BizException("the table bulletin is not empty");
}
TransactionOptions options = new TransactionOptions();
options.IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted;
options.Timeout = TransactionManager.DefaultTimeout;
using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, options))
{
string sql1 = @"select 1 as sysno, message, status from ipp2003..messagebar";
DataSet ds1 = SqlHelper.ExecuteDataSet(sql1);
foreach (DataRow dr1 in ds1.Tables[0].Rows)
{
BulletinInfo item = new BulletinInfo();
map(item, dr1);
new BulletinDac().Insert(item);
}
scope.Complete();
}
}
public BulletinInfo Load()
{
string sql = "select top 1 * from bulletin";
DataSet ds = SqlHelper.ExecuteDataSet(sql);
if (!Util.HasMoreRow(ds))
{
return(null);
}
BulletinInfo oInfo = new BulletinInfo();
map(oInfo, ds.Tables[0].Rows[0]);
return(oInfo);
}
public int Insert(BulletinInfo oParam)
{
string sql = @"INSERT INTO Bulletin
(
Message, Status
)
VALUES (
@Message, @Status
);set @SysNo = SCOPE_IDENTITY();";
SqlCommand cmd = new SqlCommand(sql);
SqlParameter paramSysNo = new SqlParameter("@SysNo", SqlDbType.Int, 4);
SqlParameter paramMessage = new SqlParameter("@Message", SqlDbType.NVarChar, 2000);
SqlParameter paramStatus = new SqlParameter("@Status", SqlDbType.Int, 4);
paramSysNo.Direction = ParameterDirection.Output;
if (oParam.Message != AppConst.StringNull)
{
paramMessage.Value = oParam.Message;
}
else
{
paramMessage.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(paramMessage);
cmd.Parameters.Add(paramStatus);
return(SqlHelper.ExecuteNonQuery(cmd, out oParam.SysNo));
}
public void Update(BulletinInfo oParam)
{
new BulletinDac().Update(oParam);
}
private void Insert(BulletinInfo oParam)
{
new BulletinDac().Insert(oParam);
}
private void map(BulletinInfo oParam, DataRow tempdr)
{
oParam.SysNo = Util.TrimIntNull(tempdr["SysNo"]);
oParam.Message = Util.TrimNull(tempdr["Message"]);
oParam.Status = Util.TrimIntNull(tempdr["Status"]);
}
