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 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;
}
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"]);
}
private void Insert(BulletinInfo oParam)
{
new BulletinDac().Insert(oParam);
}
public void Update(BulletinInfo oParam)
{
new BulletinDac().Update(oParam);
}
