public int Insert(OnlineListInfo oParam) { string sql = @"INSERT INTO OnlineList ( ListArea, ProductSysNo, CreateUserSysNo, CreateTime, ListOrder ) VALUES ( @ListArea, @ProductSysNo, @CreateUserSysNo, @CreateTime, @ListOrder );set @SysNo = SCOPE_IDENTITY();"; SqlCommand cmd = new SqlCommand(sql); SqlParameter paramSysNo = new SqlParameter("@SysNo", SqlDbType.Int,4); SqlParameter paramListArea = new SqlParameter("@ListArea", SqlDbType.Int,4); SqlParameter paramProductSysNo = new SqlParameter("@ProductSysNo", SqlDbType.Int,4); SqlParameter paramCreateUserSysNo = new SqlParameter("@CreateUserSysNo", SqlDbType.Int,4); SqlParameter paramCreateTime = new SqlParameter("@CreateTime", SqlDbType.DateTime); SqlParameter paramListOrder = new SqlParameter("@ListOrder", SqlDbType.NVarChar,20); paramSysNo.Direction = ParameterDirection.Output; if ( oParam.ListArea != AppConst.IntNull) paramListArea.Value = oParam.ListArea; else paramListArea.Value = System.DBNull.Value; if ( oParam.ProductSysNo != AppConst.IntNull) paramProductSysNo.Value = oParam.ProductSysNo; else paramProductSysNo.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.ListOrder != AppConst.StringNull) paramListOrder.Value = oParam.ListOrder; else paramListOrder.Value = System.DBNull.Value; cmd.Parameters.Add(paramSysNo); cmd.Parameters.Add(paramListArea); cmd.Parameters.Add(paramProductSysNo); cmd.Parameters.Add(paramCreateUserSysNo); cmd.Parameters.Add(paramCreateTime); cmd.Parameters.Add(paramListOrder); return SqlHelper.ExecuteNonQuery(cmd, out oParam.SysNo); }
public void Insert(OnlineListInfo oParam) { string sql = "select top 1 sysno from onlinelist where listarea=" + oParam.ListArea + " and productsysno=" + oParam.ProductSysNo; DataSet ds = SqlHelper.ExecuteDataSet(sql); if (Util.HasMoreRow(ds)) throw new BizException("there is the same product in the list area you selected"); new OnlineListDac().Insert(oParam); }
private void map(OnlineListInfo oParam, DataRow tempdr) { oParam.SysNo = Util.TrimIntNull(tempdr["SysNo"]); oParam.ListArea = Util.TrimIntNull(tempdr["ListArea"]); oParam.ProductSysNo = Util.TrimIntNull(tempdr["ProductSysNo"]); oParam.CreateUserSysNo = Util.TrimIntNull(tempdr["CreateUserSysNo"]); oParam.CreateTime = Util.TrimDateNull(tempdr["CreateTime"]); oParam.ListOrder = Util.TrimNull(tempdr["ListOrder"]); }
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 onlinelist "; DataSet ds = SqlHelper.ExecuteDataSet(sql); if (Util.HasMoreRow(ds)) throw new BizException("the table online list 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, listarea, 'zzz' as listorder, c.newsysno as productsysno, b.newsysno as createusersysno, createtime from ipp2003..web_daily_list as a, ippconvert..sys_user as b, ippconvert..productbasic as c where a.createusersysno = b.oldsysno and a.productsysno = c.oldsysno"; DataSet ds1 = SqlHelper.ExecuteDataSet(sql1); foreach (DataRow dr1 in ds1.Tables[0].Rows) { OnlineListInfo oInfo = new OnlineListInfo(); map(oInfo, dr1); switch (oInfo.ListArea) { case 0: oInfo.ListArea = (int)AppEnum.OnlineListArea.DefaultUp; break; case 1: oInfo.ListArea = (int)AppEnum.OnlineListArea.DefaultDown; break; case 2: oInfo.ListArea = (int)AppEnum.OnlineListArea.FeturedProduct; break; case 3: oInfo.ListArea = (int)AppEnum.OnlineListArea.DefaultTop2; break; default: break; } new OnlineListDac().Insert(oInfo); } //����sysno����ԭ���������Ϣ string sql2 = @"select categorysysno as sysno, listarea, 'zzz' as listorder, c.newsysno as productsysno, b.newsysno as createusersysno, createtime from ipp2003..web_index_list as a, ippconvert..sys_user as b, ippconvert..productbasic as c where a.createusersysno = b.oldsysno and a.productsysno = c.oldsysno"; DataSet ds2 = SqlHelper.ExecuteDataSet(sql2); foreach (DataRow dr2 in ds2.Tables[0].Rows) { OnlineListInfo oInfo = new OnlineListInfo(); map(oInfo, dr2); if (oInfo.SysNo == 1) { if (oInfo.ListArea == 0) oInfo.ListArea = (int)AppEnum.OnlineListArea.Hardware; else if (oInfo.ListArea == 1) oInfo.ListArea = (int)AppEnum.OnlineListArea.HardwareTopSale; else throw new BizException("error1"); } else if (oInfo.SysNo == 2) { if (oInfo.ListArea == 0) oInfo.ListArea = (int)AppEnum.OnlineListArea.Digital; else if (oInfo.ListArea == 1) oInfo.ListArea = (int)AppEnum.OnlineListArea.DigitalTopSale; else throw new BizException("error"); } else if (oInfo.SysNo == 4) { if (oInfo.ListArea == 0) oInfo.ListArea = (int)AppEnum.OnlineListArea.Accessory; else if (oInfo.ListArea == 1) oInfo.ListArea = (int)AppEnum.OnlineListArea.AccessoryTopSale; else throw new BizException("error"); } else throw new BizException("error"); new OnlineListDac().Insert(oInfo); } string sql3 = @"select 1 as sysno, 0 as listarea, 'zzz' as listorder, c.newsysno as productsysno, b.newsysno as createusersysno, createtime from ipp2003..web_new_list as a, ippconvert..sys_user as b, ippconvert..productbasic as c where a.createusersysno = b.oldsysno and a.productsysno = c.oldsysno "; DataSet ds3 = SqlHelper.ExecuteDataSet(sql3); foreach (DataRow dr3 in ds3.Tables[0].Rows) { OnlineListInfo oInfo = new OnlineListInfo(); map(oInfo, dr3); oInfo.ListArea = (int)AppEnum.OnlineListArea.Newcome; new OnlineListDac().Insert(oInfo); } string sql4 = @"select 1 as sysno, 0 as ListArea, 'zzz' as listorder, c.newsysno as productsysno, b.newsysno as createusersysno, createtime from ipp2003..web_subindex_list as a, ippconvert..sys_user as b, ippconvert..productbasic as c where a.createusersysno = b.oldsysno and a.productsysno = c.oldsysno"; DataSet ds4 = SqlHelper.ExecuteDataSet(sql4); foreach (DataRow dr4 in ds4.Tables[0].Rows) { OnlineListInfo oInfo = new OnlineListInfo(); map(oInfo, dr4); oInfo.ListArea = (int)AppEnum.OnlineListArea.Audio; new OnlineListDac().Insert(oInfo); } string sql5 = @"select 1 as sysno, ListArea, 'zzz' as listorder, c.newsysno as productsysno, b.newsysno as createusersysno, createtime from ipp2003..BrandZone_List as a, ippconvert..sys_user as b, ippconvert..productbasic as c where a.createusersysno = b.oldsysno and a.productsysno = c.oldsysno"; DataSet ds5 = SqlHelper.ExecuteDataSet(sql5); foreach (DataRow dr5 in ds5.Tables[0].Rows) { OnlineListInfo oInfo = new OnlineListInfo(); map(oInfo, dr5); if (Util.TrimIntNull(dr5["ListArea"]) == 0) //recommend oInfo.ListArea = (int)AppEnum.OnlineListArea.AOpenTop1; else//new product oInfo.ListArea = (int)AppEnum.OnlineListArea.AOpenNew; new OnlineListDac().Insert(oInfo); } scope.Complete(); } }