public int Update(ProductRelatedInfo oParam)
{
string sql = @"UPDATE Product_Related SET
MasterProductSysNo=@MasterProductSysNo, RelatedProductSysNo=@RelatedProductSysNo,
CreateUserSysNo=@CreateUserSysNo, CreateTime=@CreateTime,
Status=@Status
WHERE SysNo=@SysNo";
SqlCommand cmd = new SqlCommand(sql);
SqlParameter paramSysNo = new SqlParameter("@SysNo", SqlDbType.Int, 4);
SqlParameter paramMasterProductSysNo = new SqlParameter("@MasterProductSysNo", SqlDbType.Int, 4);
SqlParameter paramRelatedProductSysNo = new SqlParameter("@RelatedProductSysNo", SqlDbType.Int, 4);
SqlParameter paramCreateUserSysNo = new SqlParameter("@CreateUserSysNo", SqlDbType.Int, 4);
SqlParameter paramCreateTime = new SqlParameter("@CreateTime", SqlDbType.DateTime);
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.MasterProductSysNo != AppConst.IntNull)
paramMasterProductSysNo.Value = oParam.MasterProductSysNo;
else
paramMasterProductSysNo.Value = System.DBNull.Value;
if (oParam.RelatedProductSysNo != AppConst.IntNull)
paramRelatedProductSysNo.Value = oParam.RelatedProductSysNo;
else
paramRelatedProductSysNo.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.Status != AppConst.IntNull)
paramStatus.Value = oParam.Status;
else
paramStatus.Value = System.DBNull.Value;
cmd.Parameters.Add(paramSysNo);
cmd.Parameters.Add(paramMasterProductSysNo);
cmd.Parameters.Add(paramRelatedProductSysNo);
cmd.Parameters.Add(paramCreateUserSysNo);
cmd.Parameters.Add(paramCreateTime);
cmd.Parameters.Add(paramStatus);
return SqlHelper.ExecuteNonQuery(cmd);
}
public int Insert(ProductRelatedInfo oParam)
{
string sql = @"INSERT INTO Product_Related
(
MasterProductSysNo, RelatedProductSysNo, CreateUserSysNo, CreateTime,
Status
)
VALUES (
@MasterProductSysNo, @RelatedProductSysNo, @CreateUserSysNo, @CreateTime,
@Status
);set @SysNo = SCOPE_IDENTITY();";
SqlCommand cmd = new SqlCommand(sql);
SqlParameter paramSysNo = new SqlParameter("@SysNo", SqlDbType.Int, 4);
SqlParameter paramMasterProductSysNo = new SqlParameter("@MasterProductSysNo", SqlDbType.Int, 4);
SqlParameter paramRelatedProductSysNo = new SqlParameter("@RelatedProductSysNo", SqlDbType.Int, 4);
SqlParameter paramCreateUserSysNo = new SqlParameter("@CreateUserSysNo", SqlDbType.Int, 4);
SqlParameter paramCreateTime = new SqlParameter("@CreateTime", SqlDbType.DateTime);
SqlParameter paramStatus = new SqlParameter("@Status", SqlDbType.Int, 4);
paramSysNo.Direction = ParameterDirection.Output;
if (oParam.MasterProductSysNo != AppConst.IntNull)
paramMasterProductSysNo.Value = oParam.MasterProductSysNo;
else
paramMasterProductSysNo.Value = System.DBNull.Value;
if (oParam.RelatedProductSysNo != AppConst.IntNull)
paramRelatedProductSysNo.Value = oParam.RelatedProductSysNo;
else
paramRelatedProductSysNo.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.Status != AppConst.IntNull)
paramStatus.Value = oParam.Status;
else
paramStatus.Value = System.DBNull.Value;
cmd.Parameters.Add(paramSysNo);
cmd.Parameters.Add(paramMasterProductSysNo);
cmd.Parameters.Add(paramRelatedProductSysNo);
cmd.Parameters.Add(paramCreateUserSysNo);
cmd.Parameters.Add(paramCreateTime);
cmd.Parameters.Add(paramStatus);
return SqlHelper.ExecuteNonQuery(cmd, out oParam.SysNo);
}
private void map(ProductRelatedInfo oParam, DataRow tempdr)
{
oParam.SysNo = Util.TrimIntNull(tempdr["SysNo"]);
oParam.MasterProductSysNo = Util.TrimIntNull(tempdr["MasterProductSysNo"]);
oParam.RelatedProductSysNo = Util.TrimIntNull(tempdr["RelatedProductSysNo"]);
oParam.CreateUserSysNo = Util.TrimIntNull(tempdr["CreateUserSysNo"]);
oParam.CreateTime = Util.TrimDateNull(tempdr["CreateTime"]);
oParam.Status = Util.TrimIntNull(tempdr["Status"]);
}
private void Insert(ProductRelatedInfo oParam)
{
new ProductRelatedDac().Insert(oParam);
}
public void SetProductRelated(Hashtable paramHash,int CreateUserSysNo)
{
TransactionOptions options = new TransactionOptions();
options.IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted;
options.Timeout = TransactionManager.DefaultTimeout;
using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, options))
{
DataSet ds = ProductRelatedQuery(paramHash);
if (Util.HasMoreRow(ds) && ds.Tables[0].Rows.Count > 0)
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
int invalidstatus = (int)AppEnum.BiStatus.InValid;
string Updatesql = "update Product_Related set status=" + invalidstatus + " where MasterProductSysNo =" + dr["MasterProductSysNo"] + "and RelatedProductSysNo=" + dr["RelatedProductSysNo"];
SqlHelper.ExecuteDataSet(Updatesql);
}
}
string sql = @"select Product.sysno from Product
inner join category3 on category3.sysno=Product.c3sysno
inner join category2 on category3.c2sysno = category2.sysno
inner join category1 on category2.c1sysno = category1.sysno
where 1=1 @MasterProductSysNo @Category @C3 @KeyWords @MasterProductList ";
if (paramHash.Contains("MasterProductSysNo"))
{
sql = sql.Replace("@MasterProductSysNo", " and Product.sysno=" + paramHash["MasterProductSysNo"].ToString());
}
else
{
sql = sql.Replace("@MasterProductSysNo", " ");
}
if (paramHash.ContainsKey("Category"))
{
sql = sql.Replace("@Category", " and " + (string)paramHash["Category"]);
}
else
{
sql = sql.Replace("@Category", "");
}
if (paramHash.ContainsKey("C3"))
{
sql = sql.Replace("@C3", " and Product." + (string)paramHash["C3"]);
}
else
{
sql = sql.Replace("@C3", "");
}
if (paramHash.ContainsKey("KeyWords"))
{
string[] Keys = (Util.TrimNull(paramHash["KeyWords"].ToString())).Split(' ');
if (Keys.Length == 1)
{
sql = sql.Replace("@KeyWords", "and (Product.productid like " + Util.ToSqlLikeString(paramHash["KeyWords"].ToString()) + " or Product.productname like " + Util.ToSqlLikeString(paramHash["KeyWords"].ToString()) + ")");
}
else
{
string t = "";
t += " and (Product.productid like " + Util.ToSqlLikeString(paramHash["KeyWords"].ToString()) + " or ( 1=1 ";
for (int i = 0; i < Keys.Length; i++)
{
t += " and Product.productname like " + Util.ToSqlLikeString(Keys[i]);
}
t += "))";
sql = sql.Replace("@KeyWords", t);
}
}
else
{
sql = sql.Replace("@KeyWords", "");
}
if (paramHash.ContainsKey("MasterProductList"))
{
sql = sql.Replace("@MasterProductList", "and product.sysno in (" + paramHash["MasterProductList"].ToString() + ")");
}
else
{
sql = sql.Replace("@MasterProductList", "");
}
sql += "order by createtime desc";
DataSet MasterSysNods = SqlHelper.ExecuteDataSet(sql);
if (!Util.HasMoreRow(MasterSysNods))
{
throw new BizException("û�д�����������Ʒ��¼");
}
foreach (DataRow dr in MasterSysNods.Tables[0].Rows)
{
ProductRelatedInfo oInfo=new ProductRelatedInfo();
oInfo.MasterProductSysNo = Util.TrimIntNull(dr["sysno"]);
oInfo.RelatedProductSysNo = Util.TrimIntNull(paramHash["RelatedProductSysNo"].ToString());
oInfo.CreateTime = DateTime.Now;
oInfo.CreateUserSysNo = CreateUserSysNo;
oInfo.Status = (int)AppEnum.BiStatus.Valid;
Insert(oInfo);
}
scope.Complete();
}
}
public ProductRelatedInfo Load(int SysNo)
{
string sql = "select * from product_Related where sysno=" + SysNo;
DataSet ds = SqlHelper.ExecuteDataSet(sql);
if (Util.HasMoreRow(ds))
{
ProductRelatedInfo oInfo = new ProductRelatedInfo();
map(oInfo, ds.Tables[0].Rows[0]);
return oInfo;
}
else
return null;
}
public int UpdateProductRelated(ProductRelatedInfo oParam)
{
return new ProductRelatedDac().Update(oParam);
}
public int InsertProductRelated(ProductRelatedInfo oParam)
{
return new ProductRelatedDac().Insert(oParam);
}