public List <MaterialQuotePrice> LoadMaterialQuotePrices()
{
string sql = @"SELECT [MaterialID]
, [Price]
FROM [BE_MaterialQuotePrice]" ;
SqlCommand cmd = new SqlCommand(sql, this.conn, this.trans);
List <MaterialQuotePrice> ret = new List <MaterialQuotePrice>();
SqlDataReader dr = cmd.ExecuteReader();
try
{
while (dr.Read())
{
MaterialQuotePrice iret = new MaterialQuotePrice();
if (!Convert.IsDBNull(dr["MaterialID"]))
{
iret.MaterialID = (Guid)dr["MaterialID"];
}
if (!Convert.IsDBNull(dr["Price"]))
{
iret.Price = (decimal)dr["Price"];
}
ret.Add(iret);
}
}
finally
{
dr.Close();
}
return(ret);
}
public int UpdateMaterialQuotePriceByMaterialID(MaterialQuotePrice obj)
{
string sql = @"UPDATE [BE_MaterialQuotePrice] SET [Price]=@Price
WHERE [MaterialID]=@MaterialID" ;
SqlCommand cmd = new SqlCommand(sql, this.conn, this.trans);
SqlParameter pPrice = new SqlParameter("Price", Convert2DBnull(obj.Price));
pPrice.SqlDbType = SqlDbType.Decimal;
cmd.Parameters.Add(pPrice);
SqlParameter pMaterialID = new SqlParameter("MaterialID", Convert2DBnull(obj.MaterialID));
pMaterialID.SqlDbType = SqlDbType.UniqueIdentifier;
cmd.Parameters.Add(pMaterialID);
return(cmd.ExecuteNonQuery());
}
public int InsertMaterialQuotePrice(MaterialQuotePrice obj)
{
string sql = @"INSERT INTO[BE_MaterialQuotePrice]([MaterialID]
, [Price]
) VALUES(@MaterialID
, @Price
)" ;
SqlCommand cmd = new SqlCommand(sql, this.conn, this.trans);
SqlParameter pMaterialID = new SqlParameter("MaterialID", Convert2DBnull(obj.MaterialID));
pMaterialID.SqlDbType = SqlDbType.UniqueIdentifier;
cmd.Parameters.Add(pMaterialID);
SqlParameter pPrice = new SqlParameter("Price", Convert2DBnull(obj.Price));
pPrice.SqlDbType = SqlDbType.Decimal;
cmd.Parameters.Add(pPrice);
return(cmd.ExecuteNonQuery());
}
public List <MaterialQuotePrice> LoadMaterialQuotePricesByPrice(decimal price)
{
string sql = @"SELECT [MaterialID]
, [Price]
FROM [BE_MaterialQuotePrice] WHERE [Price]=@Price" ;
SqlCommand cmd = new SqlCommand(sql, this.conn, this.trans);
SqlParameter pPrice = new SqlParameter("Price", price);
pPrice.SqlDbType = SqlDbType.Decimal;
cmd.Parameters.Add(pPrice);
List <MaterialQuotePrice> ret = new List <MaterialQuotePrice>();
SqlDataReader dr = cmd.ExecuteReader();
try
{
while (dr.Read())
{
MaterialQuotePrice iret = new MaterialQuotePrice();
if (!Convert.IsDBNull(dr["MaterialID"]))
{
iret.MaterialID = (Guid)dr["MaterialID"];
}
if (!Convert.IsDBNull(dr["Price"]))
{
iret.Price = (decimal)dr["Price"];
}
ret.Add(iret);
}
}
finally
{
dr.Close();
}
return(ret);
}
public int LoadMaterialQuotePriceByMaterialID(MaterialQuotePrice obj)
{
string sql = @"SELECT [MaterialID]
, [Price]
FROM [BE_MaterialQuotePrice] WITH(NOLOCK) WHERE [MaterialID]=@MaterialID" ;
SqlCommand cmd = new SqlCommand(sql, this.conn, this.trans);
SqlParameter pMaterialID = new SqlParameter("MaterialID", Convert2DBnull(obj.MaterialID));
pMaterialID.SqlDbType = SqlDbType.UniqueIdentifier;
cmd.Parameters.Add(pMaterialID);
int ret = 0;
SqlDataReader dr = cmd.ExecuteReader();
try
{
while (dr.Read())
{
if (!Convert.IsDBNull(dr["MaterialID"]))
{
obj.MaterialID = (Guid)dr["MaterialID"];
}
if (!Convert.IsDBNull(dr["Price"]))
{
obj.Price = (decimal)dr["Price"];
}
ret += 1;
}
}
finally
{
dr.Close();
}
return(ret);
}