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();
}
}