Beispiel #1
0
        public static int AddProductSupplier(ProductSupplier prodsup)
        {
            SqlConnection con = TravelExpertsDB.GetConnection();
            string insertStatement = "INSERT INTO Products_Suppliers (ProductSupplierID, ProductID, SupplierID) " +
                                     "VALUES(@ProductSupplierID, @ProductID, @SupplierID)";
            SqlCommand cmd = new SqlCommand(insertStatement, con);
            cmd.Parameters.AddWithValue("@ProductSupplierID", prodsup.ProductSupplierID);
            cmd.Parameters.AddWithValue("@ProductID", prodsup.ProductID);
            cmd.Parameters.AddWithValue("@SupplierID", prodsup.SupplierID);

            try
            {
                con.Open();
                cmd.ExecuteNonQuery(); // run the insert command
                                       // get the generated ID - current identity value for  Products_Suppliers table
                string selectQuery = "SELECT IDENT_CURRENT('ProductSuppliers') FROM Products_Suppliers";
                SqlCommand selectCmd = new SqlCommand(selectQuery, con);
                int productsupplierID = Convert.ToInt32(selectCmd.ExecuteScalar()); // single value
                                                                                    // typecase (int) does NOT work!
                return productsupplierID;
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
            }
        }
        /// <summary>
        /// <author>Brian Appleton</author>
        /// Gets a product supplier from a product id and supplier id
        /// </summary>
        /// <returns></returns>
        public static ProductSupplier GetProductSupplier(int productId, int supplierId)
        {
            ProductSupplier productSupplier = null;

            SqlConnection con = TravelExpertsConnection.GetConnection();

            string selectStatement = "SELECT ProductSupplierID, ProductID, SupplierID " +
                                     "FROM Products_Suppliers " +
                                     "WHERE ProductID = @ProductID AND " +
                                     "SupplierID = @SupplierID";


            using (SqlCommand cmd = new SqlCommand(selectStatement, con))
            {
                cmd.Parameters.AddWithValue("@ProductID", productId);
                cmd.Parameters.AddWithValue("@SupplierID", supplierId);

                con.Open();
                SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow);


                if (reader.Read()) // found a customer
                {
                    productSupplier = new ProductSupplier
                    {
                        ProductSupplierID = (int)reader["ProductSupplierID"],
                        ProductID         = (int)reader["ProductID"],
                        SupplierID        = (int)reader["SupplierID"]
                    };
                }
                con.Close();
            }
            return(productSupplier);
        }
Beispiel #3
0
 public static ProductSupplier GetProductSupplier(int productsupplierID)
 {
     ProductSupplier prodsup = null;
     SqlConnection con = TravelExpertsDB.GetConnection();
     string selectStatement = "SELECT ProductSupplierID, ProductID, SupplierID " +
                              "FROM Products_Suppliers " +
                              "WHERE ProductSupplierID = @ProductSupplierID";
     SqlCommand cmd = new SqlCommand(selectStatement, con);
     cmd.Parameters.AddWithValue("@ProductSupplierID", productsupplierID); // value comes from the method's argument
     try
     {
         con.Open();
         SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow);
         if (reader.Read()) // found a customer
         {
             prodsup = new ProductSupplier();
             prodsup.ProductSupplierID = (int)reader["ProductSupplierID"];
             prodsup.ProductID = (int)reader["ProductID"];
             prodsup.SupplierID = (int)reader["SupplierID"];
         }
     }
     catch (SqlException ex)
     {
         throw ex;
     }
     finally
     {
         con.Close();
     }
     return prodsup;
 }
Beispiel #4
0
        public static List<ProductSupplier> GetAllProductSuppliers()
        {
            List<ProductSupplier> productsuppliers = new List<ProductSupplier>();
            ProductSupplier prodsup = null;
            SqlConnection con = TravelExpertsDB.GetConnection();
            string selectStatement = "SELECT ProductSupplierID, ProductID, SupplierID " +
                                     "FROM Products_Suppliers " +
                                     "ORDER BY ProductSupplierID";
            SqlCommand cmd = new SqlCommand(selectStatement, con);

            try
            {
                con.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read()) // while there are customers
                {
                    prodsup = new ProductSupplier();
                    prodsup.ProductSupplierID = (int)reader["ProductSupplierID"];
                    prodsup.ProductID = (int)reader["ProductID"];
                    prodsup.SupplierID = (int)reader["SupplierID"];

                    productsuppliers.Add(prodsup);
                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
            }
            return productsuppliers;
        }
Beispiel #5
0
 public static bool UpdateSupplier(ProductSupplier oldProdSup, ProductSupplier newProdSup)
 {
     SqlConnection con = TravelExpertsDB.GetConnection();
     string updateStatement = "UPDATE Products_Suppliers " +
                              "SET ProductSupplierID = @NewProductSupplierID, " +
                              "    ProductID = @NewProductID, " +
                              "    SupplierID = @NewSupplierID " +
                              "WHERE ProductSupplierID = @OldProductSupplierID " +
                              "AND ProductID = @OldProductID " +
                              "AND SupplierID = @OldSupplierID ";
     SqlCommand cmd = new SqlCommand(updateStatement, con);
     cmd.Parameters.AddWithValue("@NewProductSupplierID", newProdSup.ProductSupplierID);
     cmd.Parameters.AddWithValue("@NewProductID", newProdSup.ProductID);
     cmd.Parameters.AddWithValue("@NewSupplierID", newProdSup.SupplierID);
     cmd.Parameters.AddWithValue("@OldProductSupplierID", oldProdSup.ProductSupplierID);
     cmd.Parameters.AddWithValue("@OldProductID", oldProdSup.ProductID);
     cmd.Parameters.AddWithValue("@OldSupplierID", oldProdSup.SupplierID);
     try
     {
         con.Open();
         int count = cmd.ExecuteNonQuery();
         if (count > 0) return true;
         else return false;
     }
     catch (SqlException ex)
     {
         throw ex;
     }
     finally
     {
         con.Close();
     }
 }
Beispiel #6
0
        public static bool DeleteProductSupplier(ProductSupplier prodsup)
        {
            SqlConnection con = TravelExpertsDB.GetConnection();
            string deleteStatement = "DELETE FROM Products_Suppliers " +
                                     "WHERE ProductSupplierID = @ProductSupplierID " + // to identify the supplier to be  deleted
                                     "AND ProductID = @ProductID " +
                                     "AND SupplierID = @SupplierID "; // remaining conditions - to ensure optimistic concurrency

            SqlCommand cmd = new SqlCommand(deleteStatement, con);
            cmd.Parameters.AddWithValue("@ProductSupplierID", prodsup.ProductSupplierID);
            cmd.Parameters.AddWithValue("@ProductID", prodsup.ProductID);

            cmd.Parameters.AddWithValue("@SupplierID", prodsup.SupplierID);

            try
            {
                con.Open();
                int count = cmd.ExecuteNonQuery();
                if (count > 0) return true;
                else return false;
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
            }
        }
Beispiel #7
0
 public static List<ProductSupplier> GetAllProductSuppliers()
 {
     List<ProductSupplier> productsuppliers = new List<ProductSupplier>();
     ProductSupplier prodsup = null;