有几层分类,如何得到中间一层下面所有子类的商品表?
分类数据表字段: ID,NAME,parentID(父ID,)
商品表字段:ID,NAME,typeID(关联分类表ID)
比如有三层:
层一:A,B,C
层二:A下的a1,a2,
层三:a1下的a1a,a1b.
a1a下有商品:x,y,z;a1b下有商品:xb,yb,zb
现在我点a1a可得到表:x,y,z
但我点a1,如何得到:x,y,z,xb,yb,zb?
Access数据库
用了一个晚上解决这个个问题,但不知道效率方面如何,先贴出来,大家探讨下:
/**/
/// <summary>
/// 数组,用于存储类别ID集
/// </summary>
ArrayList alTypeId = new ArrayList();
/**/ /// <summary>
/// 获取最底层类别ID,以便得到商品表
/// </summary>
/// <param name="_TypeId">所选类别ID</param>
/// <returns>所选类别下的最底层ID集,数组</returns>
private ArrayList GetTypeId( int _TypeId)
... {
try
...{
string sqlstr = "select t.type_id from WareType t where t.type_parentId in (" + _TypeId + ")";
DALHelp connobj = DALHelp.CreateInstance();
DataSet ds = connobj.runSQLDataSet(sqlstr);
DataTable dt = ds.Tables[0];
if (dt != null && dt.Rows.Count > 0)
...{
foreach (DataRow dr in dt.Rows)
...{
_TypeId = Convert.ToInt32(dr[0]);
GetTypeId(_TypeId);
}
}
else
alTypeId.Add(_TypeId);
return alTypeId;
}
catch (Exception ex)
...{
throw new Exception(ex.Message);
}
}
/**/ /// <summary>
/// 读取商品表by 类别ID
/// </summary>
/// <param name="_TypeID">类别ID</param>
/// <returns>所选类别商品表</returns>
public DataTable GetWaresByTypeID( int _TypeID)
... {
try
...{
if (_TypeID <= 0) return null;
alTypeId = new ArrayList();
alTypeId = GetTypeId(_TypeID);
string typeIds = "";
foreach (Object obj in alTypeId)
typeIds += obj + ",";
DALHelp cnnobj = DALHelp.CreateInstance();
DataSet ds = cnnobj.runSQLDataSet("SELECT w.ware_id, w.ware_name, w.ware_typeId, p.provider_id, p.provider_name FROM (WareInfo w Left JOIN ProviderInfo p ON w.providerId = p.provider_id) Where w.ware_typeId in (" + typeIds + ") Order by w.createDate DESC");
if (ds == null)
return null;
else
return ds.Tables[0];
}
catch (Exception ex)
...{
throw new Exception(ex.Message);
}
}
/// 数组,用于存储类别ID集
/// </summary>
ArrayList alTypeId = new ArrayList();
/**/ /// <summary>
/// 获取最底层类别ID,以便得到商品表
/// </summary>
/// <param name="_TypeId">所选类别ID</param>
/// <returns>所选类别下的最底层ID集,数组</returns>
private ArrayList GetTypeId( int _TypeId)
... {
try
...{
string sqlstr = "select t.type_id from WareType t where t.type_parentId in (" + _TypeId + ")";
DALHelp connobj = DALHelp.CreateInstance();
DataSet ds = connobj.runSQLDataSet(sqlstr);
DataTable dt = ds.Tables[0];
if (dt != null && dt.Rows.Count > 0)
...{
foreach (DataRow dr in dt.Rows)
...{
_TypeId = Convert.ToInt32(dr[0]);
GetTypeId(_TypeId);
}
}
else
alTypeId.Add(_TypeId);
return alTypeId;
}
catch (Exception ex)
...{
throw new Exception(ex.Message);
}
}
/**/ /// <summary>
/// 读取商品表by 类别ID
/// </summary>
/// <param name="_TypeID">类别ID</param>
/// <returns>所选类别商品表</returns>
public DataTable GetWaresByTypeID( int _TypeID)
... {
try
...{
if (_TypeID <= 0) return null;
alTypeId = new ArrayList();
alTypeId = GetTypeId(_TypeID);
string typeIds = "";
foreach (Object obj in alTypeId)
typeIds += obj + ",";
DALHelp cnnobj = DALHelp.CreateInstance();
DataSet ds = cnnobj.runSQLDataSet("SELECT w.ware_id, w.ware_name, w.ware_typeId, p.provider_id, p.provider_name FROM (WareInfo w Left JOIN ProviderInfo p ON w.providerId = p.provider_id) Where w.ware_typeId in (" + typeIds + ") Order by w.createDate DESC");
if (ds == null)
return null;
else
return ds.Tables[0];
}
catch (Exception ex)
...{
throw new Exception(ex.Message);
}
}