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 currency "; DataSet ds = SqlHelper.ExecuteDataSet(sql); if ( Util.HasMoreRow(ds) ) throw new BizException("the table currency 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)) { try { string sql1 = @"select a.*, isnull(b.exchangerate,0) as exchangerate from ipp2003..currency a, ipp2003..exchange_rate b where a.sysno *=b.currencysysno"; DataSet ds1 = SqlHelper.ExecuteDataSet(sql1); foreach(DataRow dr1 in ds1.Tables[0].Rows ) { CurrencyInfo item = new CurrencyInfo(); item.CurrencyID = Util.TrimNull(dr1["CurrencyID"]); item.CurrencyName = Util.TrimNull(dr1["CurrencyName"]); item.CurrencySymbol = Util.TrimNull(dr1["CurrencySymbol"]); if ( dr1["IfBasic"] == System.DBNull.Value ) item.IsLocal = (int)AppEnum.YNStatus.No; else if ( Convert.ToBoolean(dr1["IfBasic"])==true) item.IsLocal = (int)AppEnum.YNStatus.Yes; else item.IsLocal = (int)AppEnum.YNStatus.No; item.ExchangeRate = Util.TrimDecimalNull(dr1["ExchangeRate"]); item.ListOrder = Util.TrimNull(dr1["CurrencyID"]); item.Status = Util.TrimIntNull(dr1["Status"]); this.Insert(item); //insert into convert table ImportInfo oConvert = new ImportInfo(); oConvert.OldSysNo = Util.TrimIntNull(dr1["SysNo"]); oConvert.NewSysNo = item.SysNo; new ImportDac().Insert(oConvert, "Currency"); } } catch(Exception ex) { currencyHash.Clear(); throw ex; } scope.Complete(); } }
private void map(CurrencyInfo oParam, DataRow tempdr) { oParam.SysNo = Util.TrimIntNull(tempdr["SysNo"]); oParam.CurrencyID = Util.TrimNull(tempdr["CurrencyID"]); oParam.CurrencyName = Util.TrimNull(tempdr["CurrencyName"]); oParam.CurrencySymbol = Util.TrimNull(tempdr["CurrencySymbol"]); oParam.IsLocal = Util.TrimIntNull(tempdr["IsLocal"]); oParam.ExchangeRate = Util.TrimDecimalNull(tempdr["ExchangeRate"]); oParam.ListOrder = Util.TrimNull(tempdr["ListOrder"]); oParam.Status = Util.TrimIntNull(tempdr["Status"]); }
public void Update(CurrencyInfo oParam) { TransactionOptions options = new TransactionOptions(); options.IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted; options.Timeout = TransactionManager.DefaultTimeout; using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, options)) { new CurrencyDac().Update(oParam); SyncManager.GetInstance().SetDbLastVersion( (int)AppEnum.Sync.Currency ); currencyHash.Remove(oParam.SysNo); currencyHash.Add(oParam.SysNo,oParam); scope.Complete(); } }
public void Insert(CurrencyInfo oParam) { foreach(CurrencyInfo item in currencyHash.Values) { if ( item.CurrencyID == oParam.CurrencyID) throw new BizException("the same id exists"); } TransactionOptions options = new TransactionOptions(); options.IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted; options.Timeout = TransactionManager.DefaultTimeout; using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, options)) { string sql = "select isnull(max(sysno),0) as maxsysno from currency"; DataSet ds = SqlHelper.ExecuteDataSet(sql); int newSysNo = 0; if ( !Util.HasMoreRow(ds)) newSysNo = 0; else newSysNo = Util.TrimIntNull(ds.Tables[0].Rows[0]["maxsysno"]); oParam.SysNo = newSysNo+1; new CurrencyDac().Insert(oParam); SyncManager.GetInstance().SetDbLastVersion( (int)AppEnum.Sync.Currency ); currencyHash.Add(oParam.SysNo, oParam); scope.Complete(); } }
public void Init() { lock ( currencyLocker ) { currencyHash.Clear(); string sql = @" select * from currency "; DataSet ds = SqlHelper.ExecuteDataSet(sql); if ( !Util.HasMoreRow(ds)) return; foreach( DataRow dr in ds.Tables[0].Rows ) { CurrencyInfo item = new CurrencyInfo(); map(item, dr); currencyHash.Add(item.SysNo, item); } } }
public int Update(CurrencyInfo oParam) { string sql = @"UPDATE Currency SET CurrencyID=@CurrencyID, CurrencyName=@CurrencyName, CurrencySymbol=@CurrencySymbol, IsLocal=@IsLocal, ExchangeRate=@ExchangeRate, ListOrder=@ListOrder, Status=@Status WHERE SysNo=@SysNo"; SqlCommand cmd = new SqlCommand(sql); SqlParameter paramSysNo = new SqlParameter("@SysNo", SqlDbType.Int,4); SqlParameter paramCurrencyID = new SqlParameter("@CurrencyID", SqlDbType.NVarChar,20); SqlParameter paramCurrencyName = new SqlParameter("@CurrencyName", SqlDbType.NVarChar,50); SqlParameter paramCurrencySymbol = new SqlParameter("@CurrencySymbol", SqlDbType.NVarChar,20); SqlParameter paramIsLocal = new SqlParameter("@IsLocal", SqlDbType.Int,4); SqlParameter paramExchangeRate = new SqlParameter("@ExchangeRate", SqlDbType.Decimal,9); SqlParameter paramListOrder = new SqlParameter("@ListOrder", SqlDbType.NVarChar,10); 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.CurrencyID != AppConst.StringNull) paramCurrencyID.Value = oParam.CurrencyID; else paramCurrencyID.Value = System.DBNull.Value; if ( oParam.CurrencyName != AppConst.StringNull) paramCurrencyName.Value = oParam.CurrencyName; else paramCurrencyName.Value = System.DBNull.Value; if ( oParam.CurrencySymbol != AppConst.StringNull) paramCurrencySymbol.Value = oParam.CurrencySymbol; else paramCurrencySymbol.Value = System.DBNull.Value; if ( oParam.IsLocal != AppConst.IntNull) paramIsLocal.Value = oParam.IsLocal; else paramIsLocal.Value = System.DBNull.Value; if ( oParam.ExchangeRate != AppConst.DecimalNull) paramExchangeRate.Value = oParam.ExchangeRate; else paramExchangeRate.Value = System.DBNull.Value; if ( oParam.ListOrder != AppConst.StringNull) paramListOrder.Value = oParam.ListOrder; else paramListOrder.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(paramCurrencyID); cmd.Parameters.Add(paramCurrencyName); cmd.Parameters.Add(paramCurrencySymbol); cmd.Parameters.Add(paramIsLocal); cmd.Parameters.Add(paramExchangeRate); cmd.Parameters.Add(paramListOrder); cmd.Parameters.Add(paramStatus); return SqlHelper.ExecuteNonQuery(cmd); }