public void InsertROVoucherList(Hashtable ht)
{
TransactionOptions options = new TransactionOptions();
options.IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted;
options.Timeout = TransactionManager.DefaultTimeout;
using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, options))
{
foreach (ROVoucherInfo oParam in ht.Keys)
{
ROVoucherInfo newInfo = LoadROVoucher(oParam);
if (newInfo == null)
{
InsertROVoucher(oParam);
}
else
{
newInfo.VoucherID = oParam.VoucherID;
UpdateROVoucher(newInfo);
}
}
scope.Complete();
}
}
private void map(ROVoucherInfo oParam, DataRow tempdr)
{
oParam.SysNo = Util.TrimIntNull(tempdr["SysNo"]);
oParam.ROSysNo = Util.TrimIntNull(tempdr["ROSysNo"]);
oParam.VoucherID = Util.TrimNull(tempdr["VoucherID"]);
oParam.Status = Util.TrimIntNull(tempdr["Status"]);
}
public int Update(ROVoucherInfo oParam)
{
string sql = @"UPDATE RO_Voucher SET
ROSysNo=@ROSysNo, VoucherID=@VoucherID,
Status=@Status
WHERE SysNo=@SysNo";
SqlCommand cmd = new SqlCommand(sql);
SqlParameter paramSysNo = new SqlParameter("@SysNo", SqlDbType.Int, 4);
SqlParameter paramROSysNo = new SqlParameter("@ROSysNo", SqlDbType.Int, 4);
SqlParameter paramVoucherID = new SqlParameter("@VoucherID", SqlDbType.NVarChar, 50);
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.ROSysNo != AppConst.IntNull)
{
paramROSysNo.Value = oParam.ROSysNo;
}
else
{
paramROSysNo.Value = System.DBNull.Value;
}
if (oParam.VoucherID != AppConst.StringNull)
{
paramVoucherID.Value = oParam.VoucherID;
}
else
{
paramVoucherID.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(paramROSysNo);
cmd.Parameters.Add(paramVoucherID);
cmd.Parameters.Add(paramStatus);
return(SqlHelper.ExecuteNonQuery(cmd));
}
public int Insert(ROVoucherInfo oParam)
{
string sql = @"INSERT INTO RO_Voucher
(
ROSysNo, VoucherID, Status
)
VALUES (
@ROSysNo, @VoucherID, @Status
);set @SysNo = SCOPE_IDENTITY();";
SqlCommand cmd = new SqlCommand(sql);
SqlParameter paramSysNo = new SqlParameter("@SysNo", SqlDbType.Int, 4);
SqlParameter paramROSysNo = new SqlParameter("@ROSysNo", SqlDbType.Int, 4);
SqlParameter paramVoucherID = new SqlParameter("@VoucherID", SqlDbType.NVarChar, 50);
SqlParameter paramStatus = new SqlParameter("@Status", SqlDbType.Int, 4);
paramSysNo.Direction = ParameterDirection.Output;
if (oParam.ROSysNo != AppConst.IntNull)
{
paramROSysNo.Value = oParam.ROSysNo;
}
else
{
paramROSysNo.Value = System.DBNull.Value;
}
if (oParam.VoucherID != AppConst.StringNull)
{
paramVoucherID.Value = oParam.VoucherID;
}
else
{
paramVoucherID.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(paramROSysNo);
cmd.Parameters.Add(paramVoucherID);
cmd.Parameters.Add(paramStatus);
return(SqlHelper.ExecuteNonQuery(cmd, out oParam.SysNo));
}
private ROVoucherInfo LoadROVoucher(ROVoucherInfo oParam)
{
string sql = "select * from ro_voucher where ROsysno=" + oParam.ROSysNo;
DataSet ds = SqlHelper.ExecuteDataSet(sql);
if (Util.HasMoreRow(ds))
{
ROVoucherInfo newInfo = new ROVoucherInfo();
map(newInfo, ds.Tables[0].Rows[0]);
return(newInfo);
}
else
{
return(null);
}
}
public Hashtable LoadROVoucherList(string VoucherID)
{
string sql = "select * from ro_voucher where voucherid = " + Util.ToSqlString(VoucherID);
DataSet ds = SqlHelper.ExecuteDataSet(sql);
if (Util.HasMoreRow(ds))
{
Hashtable ht = new Hashtable();
foreach (DataRow dr in ds.Tables[0].Rows)
{
ROVoucherInfo oInfo = new ROVoucherInfo();
map(oInfo, dr);
ht.Add(oInfo, null);
}
return(ht);
}
else
{
return(null);
}
}
public int UpdateROVoucher(ROVoucherInfo oParam)
{
return(new ROVoucherDac().Update(oParam));
}
public int InsertROVoucher(ROVoucherInfo oParam)
{
return(new ROVoucherDac().Insert(oParam));
}
