/// <summary>
/// 数据转商品实体
/// </summary>
public static ItemInfo DataToModel(SqlDataReader rdr)
{
ItemInfo item = new ItemInfo();
if (!rdr.IsDBNull(0)) item.ItemID = rdr.GetInt32(0);
if (!rdr.IsDBNull(1)) item.ItemName = rdr.GetString(1);
if (!rdr.IsDBNull(2)) item.CategoryTypeID = rdr.GetInt32(2);
if (!rdr.IsDBNull(3)) item.ItemPrice = rdr.GetDecimal(3);
if (!rdr.IsDBNull(4)) item.ItemBuyDate = rdr.GetDateTime(4);
if (!rdr.IsDBNull(5)) item.UserID = rdr.GetInt32(5);
if (!rdr.IsDBNull(6)) item.Recommend = rdr.GetByte(6);
if (!rdr.IsDBNull(7)) item.ModifyDate = rdr.GetDateTime(7);
if (!rdr.IsDBNull(8)) item.Synchronize = rdr.GetByte(8);
if (!rdr.IsDBNull(9)) item.ItemAppID = rdr.GetInt32(9);
if (!rdr.IsDBNull(10)) item.RegionID = rdr.GetInt32(10);
if (!rdr.IsDBNull(11)) item.RegionType = rdr.GetString(11);
if (!rdr.IsDBNull(12)) item.ItemType = rdr.GetString(12);
if (!rdr.IsDBNull(13)) item.ZhuanTiID = rdr.GetInt32(13);
if (!rdr.IsDBNull(14)) item.CardID = rdr.GetInt32(14);
return item;
}
/// <summary>
/// 根据商品AppID修改商品
/// </summary>
public bool UpdateItemByItemAppId(ItemInfo item)
{
SqlParameter[] parms = ModelToParms(item);
int result = SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionString, CommandType.StoredProcedure, SQL_UPDATE_ITEM_BY_ITEM_APP_ID, parms);
return result > 0;
}
/// <summary>
/// 插入商品
/// </summary>
public bool InsertItem(ItemInfo item)
{
SqlParameter[] parms = ModelToParms(item);
int result = SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionString, CommandType.StoredProcedure, SQL_INSERT_ITEM, parms);
return result > 0;
}
/// <summary>
/// 根据商品ID取商品
/// </summary>
public ItemInfo GetItemByItemId(int itemId)
{
ItemInfo item = new ItemInfo();
SqlParameter parm = new SqlParameter(PARM_ITEM_ID, SqlDbType.Int);
parm.Value = itemId;
using (SqlDataReader rdr = SqlHelper.ExecuteReader(SqlHelper.ConnectionString, CommandType.Text, SQL_SELECT_ITEM_BY_ITEM_ID, parm))
{
while (rdr.Read())
{
item = DataToModel(rdr);
}
}
return item;
}
/// <summary>
/// 根据商品AppID取商品
/// </summary>
public ItemInfo GetItemByItemAppId(int userId, int itemAppId)
{
ItemInfo item = new ItemInfo();
SqlParameter[] parms = {
new SqlParameter(PARM_USER_ID, SqlDbType.Int),
new SqlParameter(PARM_ITEM_APP_ID, SqlDbType.Int)
};
parms[0].Value = userId;
parms[1].Value = itemAppId;
using (SqlDataReader rdr = SqlHelper.ExecuteReader(SqlHelper.ConnectionString, CommandType.Text, SQL_SELECT_ITEM_BY_ITEM_APP_ID, parms))
{
while (rdr.Read())
{
item = DataToModel(rdr);
}
}
return item;
}
/// <summary>
/// 检查商品是否存在
/// </summary>
public bool CheckItemExists(ItemInfo item)
{
SqlParameter[] parms = {
new SqlParameter(PARM_ITEM_NAME, SqlDbType.NVarChar, 20),
new SqlParameter(PARM_ITEM_BUY_DATE, SqlDbType.Date),
new SqlParameter(PARM_RECOMMEND, SqlDbType.TinyInt),
new SqlParameter(PARM_ZT_ID, SqlDbType.Int),
new SqlParameter(PARM_CD_ID, SqlDbType.Int),
new SqlParameter(PARM_CATEGORY_ID, SqlDbType.Int),
new SqlParameter(PARM_ITEM_PRICE, SqlDbType.Decimal),
new SqlParameter(PARM_ITEM_TYPE, SqlDbType.NVarChar, 10)
};
parms[0].Value = item.ItemName;
parms[1].Value = item.ItemBuyDate.ToString("yyyy-MM-dd");
parms[2].Value = item.Recommend;
parms[3].Value = item.ZhuanTiID;
parms[4].Value = item.CardID;
parms[5].Value = item.CategoryTypeID;
parms[6].Value = item.ItemPrice;
parms[7].Value = item.ItemType;
int result = Convert.ToInt32(SqlHelper.ExecuteScalar(SqlHelper.ConnectionString, CommandType.Text, SQL_CHECK_ITEM_EXISTS, parms));
return result > 0;
}
/// <summary>
/// 商品实体转参数
/// </summary>
public static SqlParameter[] ModelToParms(ItemInfo item)
{
SqlParameter[] parms = {
new SqlParameter("@ItemID", SqlDbType.Int),
new SqlParameter("@ItemName", SqlDbType.NVarChar, 20),
new SqlParameter("@CategoryTypeID", SqlDbType.Int),
new SqlParameter("@ItemPrice", SqlDbType.Decimal),
new SqlParameter("@ItemBuyDate", SqlDbType.DateTime),
new SqlParameter("@UserID", SqlDbType.Int),
new SqlParameter("@Recommend", SqlDbType.TinyInt),
new SqlParameter("@ModifyDate", SqlDbType.DateTime),
new SqlParameter("@Synchronize", SqlDbType.TinyInt),
new SqlParameter("@ItemAppID", SqlDbType.Int),
new SqlParameter("@RegionID", SqlDbType.Int),
new SqlParameter("@RegionType", SqlDbType.NVarChar, 10),
new SqlParameter("@ItemType", SqlDbType.NVarChar, 10),
new SqlParameter("@ZhuanTiID", SqlDbType.Int),
new SqlParameter("@CardID", SqlDbType.Int),
};
parms[0].Value = item.ItemID;
parms[1].Value = item.ItemName;
parms[2].Value = item.CategoryTypeID;
parms[3].Value = item.ItemPrice;
parms[4].Value = item.ItemBuyDate;
parms[5].Value = item.UserID;
parms[6].Value = item.Recommend;
parms[7].Value = item.ModifyDate;
parms[8].Value = item.Synchronize;
parms[9].Value = item.ItemAppID;
parms[10].Value = item.RegionID;
parms[11].Value = item.RegionType;
parms[12].Value = item.ItemType;
parms[13].Value = item.ZhuanTiID;
parms[14].Value = item.CardID;
return parms;
}
