public int UpdateMaterial2SupplierByMaterialID_SupplierID(Material2Supplier obj)
{
string sql = @"UPDATE [BE_Material2Supplier] SET [Price]=@Price
, [MinPurchaseQty]=@MinPurchaseQty
, [MinDelivery]=@MinDelivery
WHERE [MaterialID]=@MaterialID AND [SupplierID]=@SupplierID" ;
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 pMinPurchaseQty = new SqlParameter("MinPurchaseQty", Convert2DBnull(obj.MinPurchaseQty));
pMinPurchaseQty.SqlDbType = SqlDbType.Int;
cmd.Parameters.Add(pMinPurchaseQty);
SqlParameter pMinDelivery = new SqlParameter("MinDelivery", Convert2DBnull(obj.MinDelivery));
pMinDelivery.SqlDbType = SqlDbType.Int;
cmd.Parameters.Add(pMinDelivery);
SqlParameter pMaterialID = new SqlParameter("MaterialID", Convert2DBnull(obj.MaterialID));
pMaterialID.SqlDbType = SqlDbType.UniqueIdentifier;
cmd.Parameters.Add(pMaterialID);
SqlParameter pSupplierID = new SqlParameter("SupplierID", Convert2DBnull(obj.SupplierID));
pSupplierID.SqlDbType = SqlDbType.UniqueIdentifier;
cmd.Parameters.Add(pSupplierID);
return(cmd.ExecuteNonQuery());
}
public int LoadMaterial2SupplierByMaterialID_SupplierID(Material2Supplier obj)
{
string sql = @"SELECT [MaterialID]
, [SupplierID]
, [Price]
, [MinPurchaseQty]
, [MinDelivery]
FROM [BE_Material2Supplier] WITH(NOLOCK) WHERE [MaterialID]=@MaterialID AND [SupplierID]=@SupplierID" ;
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 pSupplierID = new SqlParameter("SupplierID", Convert2DBnull(obj.SupplierID));
pSupplierID.SqlDbType = SqlDbType.UniqueIdentifier;
cmd.Parameters.Add(pSupplierID);
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["SupplierID"]))
{
obj.SupplierID = (Guid)dr["SupplierID"];
}
if (!Convert.IsDBNull(dr["Price"]))
{
obj.Price = (decimal)dr["Price"];
}
if (!Convert.IsDBNull(dr["MinPurchaseQty"]))
{
obj.MinPurchaseQty = (int)dr["MinPurchaseQty"];
}
if (!Convert.IsDBNull(dr["MinDelivery"]))
{
obj.MinDelivery = (int)dr["MinDelivery"];
}
ret += 1;
}
}
finally
{
dr.Close();
}
return(ret);
}
public List <Material2Supplier> LoadMaterial2SuppliersByMinDelivery(int minDelivery)
{
string sql = @"SELECT [MaterialID]
, [SupplierID]
, [Price]
, [MinPurchaseQty]
, [MinDelivery]
FROM [BE_Material2Supplier] WHERE [MinDelivery]=@MinDelivery" ;
SqlCommand cmd = new SqlCommand(sql, this.conn, this.trans);
SqlParameter pMinDelivery = new SqlParameter("MinDelivery", minDelivery);
pMinDelivery.SqlDbType = SqlDbType.Int;
cmd.Parameters.Add(pMinDelivery);
List <Material2Supplier> ret = new List <Material2Supplier>();
SqlDataReader dr = cmd.ExecuteReader();
try
{
while (dr.Read())
{
Material2Supplier iret = new Material2Supplier();
if (!Convert.IsDBNull(dr["MaterialID"]))
{
iret.MaterialID = (Guid)dr["MaterialID"];
}
if (!Convert.IsDBNull(dr["SupplierID"]))
{
iret.SupplierID = (Guid)dr["SupplierID"];
}
if (!Convert.IsDBNull(dr["Price"]))
{
iret.Price = (decimal)dr["Price"];
}
if (!Convert.IsDBNull(dr["MinPurchaseQty"]))
{
iret.MinPurchaseQty = (int)dr["MinPurchaseQty"];
}
if (!Convert.IsDBNull(dr["MinDelivery"]))
{
iret.MinDelivery = (int)dr["MinDelivery"];
}
ret.Add(iret);
}
}
finally
{
dr.Close();
}
return(ret);
}
public int InsertMaterial2Supplier(Material2Supplier obj)
{
string sql = @"INSERT INTO[BE_Material2Supplier]([MaterialID]
, [SupplierID]
, [Price]
, [MinPurchaseQty]
, [MinDelivery]
) VALUES(@MaterialID
, @SupplierID
, @Price
, @MinPurchaseQty
, @MinDelivery
)" ;
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 pSupplierID = new SqlParameter("SupplierID", Convert2DBnull(obj.SupplierID));
pSupplierID.SqlDbType = SqlDbType.UniqueIdentifier;
cmd.Parameters.Add(pSupplierID);
SqlParameter pPrice = new SqlParameter("Price", Convert2DBnull(obj.Price));
pPrice.SqlDbType = SqlDbType.Decimal;
cmd.Parameters.Add(pPrice);
SqlParameter pMinPurchaseQty = new SqlParameter("MinPurchaseQty", Convert2DBnull(obj.MinPurchaseQty));
pMinPurchaseQty.SqlDbType = SqlDbType.Int;
cmd.Parameters.Add(pMinPurchaseQty);
SqlParameter pMinDelivery = new SqlParameter("MinDelivery", Convert2DBnull(obj.MinDelivery));
pMinDelivery.SqlDbType = SqlDbType.Int;
cmd.Parameters.Add(pMinDelivery);
return(cmd.ExecuteNonQuery());
}
