public List <QuoteItem> LoadQuoteItemsBySubCagegoryCode(string subCagegoryCode)
{
string sql = @"SELECT [ItemID]
, [CategoryCode]
, [SubCagegoryCode]
, [ItemCode]
, [ItemName]
, [Style]
, [Model]
, [Unit]
, [Price]
FROM [BE_QuoteItem] WHERE [SubCagegoryCode]=@SubCagegoryCode" ;
SqlCommand cmd = new SqlCommand(sql, this.conn, this.trans);
SqlParameter pSubCagegoryCode = new SqlParameter("SubCagegoryCode", subCagegoryCode);
pSubCagegoryCode.SqlDbType = SqlDbType.NVarChar;
cmd.Parameters.Add(pSubCagegoryCode);
List <QuoteItem> ret = new List <QuoteItem>();
SqlDataReader dr = cmd.ExecuteReader();
try
{
while (dr.Read())
{
QuoteItem iret = new QuoteItem();
if (!Convert.IsDBNull(dr["ItemID"]))
{
iret.ItemID = (Guid)dr["ItemID"];
}
iret.CategoryCode = dr["CategoryCode"].ToString();
iret.SubCagegoryCode = dr["SubCagegoryCode"].ToString();
iret.ItemCode = dr["ItemCode"].ToString();
iret.ItemName = dr["ItemName"].ToString();
iret.Style = dr["Style"].ToString();
iret.Model = dr["Model"].ToString();
iret.Unit = dr["Unit"].ToString();
if (!Convert.IsDBNull(dr["Price"]))
{
iret.Price = (decimal)dr["Price"];
}
ret.Add(iret);
}
}
finally
{
dr.Close();
}
return(ret);
}
public int LoadQuoteItemByItemID(QuoteItem obj)
{
string sql = @"SELECT [ItemID]
, [CategoryCode]
, [SubCagegoryCode]
, [ItemCode]
, [ItemName]
, [Style]
, [Model]
, [Unit]
, [Price]
FROM [BE_QuoteItem] WITH(NOLOCK) WHERE [ItemID]=@ItemID" ;
SqlCommand cmd = new SqlCommand(sql, this.conn, this.trans);
SqlParameter pItemID = new SqlParameter("ItemID", Convert2DBnull(obj.ItemID));
pItemID.SqlDbType = SqlDbType.UniqueIdentifier;
cmd.Parameters.Add(pItemID);
int ret = 0;
SqlDataReader dr = cmd.ExecuteReader();
try
{
while (dr.Read())
{
if (!Convert.IsDBNull(dr["ItemID"]))
{
obj.ItemID = (Guid)dr["ItemID"];
}
obj.CategoryCode = dr["CategoryCode"].ToString();
obj.SubCagegoryCode = dr["SubCagegoryCode"].ToString();
obj.ItemCode = dr["ItemCode"].ToString();
obj.ItemName = dr["ItemName"].ToString();
obj.Style = dr["Style"].ToString();
obj.Model = dr["Model"].ToString();
obj.Unit = dr["Unit"].ToString();
if (!Convert.IsDBNull(dr["Price"]))
{
obj.Price = (decimal)dr["Price"];
}
ret += 1;
}
}
finally
{
dr.Close();
}
return(ret);
}
public int InsertQuoteItem(QuoteItem obj)
{
string sql = @"INSERT INTO[BE_QuoteItem]([ItemID]
, [CategoryCode]
, [SubCagegoryCode]
, [ItemCode]
, [ItemName]
, [Style]
, [Model]
, [Unit]
, [Price]
) VALUES(@ItemID
, @CategoryCode
, @SubCagegoryCode
, @ItemCode
, @ItemName
, @Style
, @Model
, @Unit
, @Price
)" ;
SqlCommand cmd = new SqlCommand(sql, this.conn, this.trans);
SqlParameter pItemID = new SqlParameter("ItemID", Convert2DBnull(obj.ItemID));
pItemID.SqlDbType = SqlDbType.UniqueIdentifier;
cmd.Parameters.Add(pItemID);
SqlParameter pCategoryCode = new SqlParameter("CategoryCode", Convert2DBnull(obj.CategoryCode));
pCategoryCode.SqlDbType = SqlDbType.NVarChar;
cmd.Parameters.Add(pCategoryCode);
SqlParameter pSubCagegoryCode = new SqlParameter("SubCagegoryCode", Convert2DBnull(obj.SubCagegoryCode));
pSubCagegoryCode.SqlDbType = SqlDbType.NVarChar;
cmd.Parameters.Add(pSubCagegoryCode);
SqlParameter pItemCode = new SqlParameter("ItemCode", Convert2DBnull(obj.ItemCode));
pItemCode.SqlDbType = SqlDbType.NVarChar;
cmd.Parameters.Add(pItemCode);
SqlParameter pItemName = new SqlParameter("ItemName", Convert2DBnull(obj.ItemName));
pItemName.SqlDbType = SqlDbType.NVarChar;
cmd.Parameters.Add(pItemName);
SqlParameter pStyle = new SqlParameter("Style", Convert2DBnull(obj.Style));
pStyle.SqlDbType = SqlDbType.NVarChar;
cmd.Parameters.Add(pStyle);
SqlParameter pModel = new SqlParameter("Model", Convert2DBnull(obj.Model));
pModel.SqlDbType = SqlDbType.NVarChar;
cmd.Parameters.Add(pModel);
SqlParameter pUnit = new SqlParameter("Unit", Convert2DBnull(obj.Unit));
pUnit.SqlDbType = SqlDbType.NVarChar;
cmd.Parameters.Add(pUnit);
SqlParameter pPrice = new SqlParameter("Price", Convert2DBnull(obj.Price));
pPrice.SqlDbType = SqlDbType.Decimal;
cmd.Parameters.Add(pPrice);
return(cmd.ExecuteNonQuery());
}
