public static int InsertProdSupplier(ProdSuppliers newps)
{
int InsertPS = 0;
SqlConnection con = DBConnection.GetConnection();
string sql = "INSERT Products_Suppliers (ProductId,SupplierId) " +
"VALUES (@npsPid, @npsSid)";
SqlCommand cmdInsert = new SqlCommand(sql, con); //get connection
cmdInsert.Parameters.AddWithValue("@npsPid", newps.ProdId); //assign value with parameter
cmdInsert.Parameters.AddWithValue("@npsSid", newps.SupplierId); //assign value with parameter
try
{
con.Open(); //connection open
InsertPS = cmdInsert.ExecuteNonQuery(); //execute query
}
catch (SqlException ex)
{
MessageBox.Show("The error is " + ex.Message, ex.GetType().ToString());
}
finally
{
con.Close();//close connection
}
return(InsertPS);
}
public static int DeleteProdSupplier(ProdSuppliers delps)
{
int DeletePS = 0;
SqlConnection con = DBConnection.GetConnection();
string sql = "DELETE FROM Products_Suppliers " +
"WHERE ProductId = @delPId " +
"AND SupplierId = @delSId";
SqlCommand cmdDelete = new SqlCommand(sql, con); //get connection
cmdDelete.Parameters.AddWithValue("@delPId", delps.ProdId); //assign value with parameter
cmdDelete.Parameters.AddWithValue("@delSId", delps.SupplierId); //assign value with parameter
try
{
con.Open(); //connection open
DeletePS = cmdDelete.ExecuteNonQuery(); //execute query
}
catch (SqlException ex)
{
MessageBox.Show("The error is " + ex.Message, ex.GetType().ToString());//throw exception
}
finally
{
con.Close();//close connection
}
return(DeletePS);
}
public static ProdSuppliers GetProductSupplierById(int pId, int sId)
{
ProdSuppliers ps = new ProdSuppliers();
SqlConnection con = DBConnection.GetConnection();
string sql =
"SELECT ProductSupplierId, ProductId, SupplierId " +
"FROM Products_Suppliers " +
"WHERE ProductId = @pId and SupplierId =@sId";
SqlCommand cmdSelect = new SqlCommand(sql, con);
cmdSelect.Parameters.AddWithValue("@pId", pId);
cmdSelect.Parameters.AddWithValue("@sId", sId);
try
{
con.Open();
SqlDataReader Reader = cmdSelect.ExecuteReader();
while (Reader.Read())
{
ps.ProductSupplierId = Convert.ToInt32(Reader["ProductSupplierId"]);
ps.ProdId = Convert.ToInt32(Reader["ProductId"]);
ps.SupplierId = Convert.ToInt32(Reader["SupplierId"]);
}
}
catch (SqlException ex)
{
MessageBox.Show("The error is " + ex.Message, ex.GetType().ToString());
}
finally
{
con.Close();
}
return(ps);
}