Exemplo n.º 1
0
        public static int AddProductSupplier(suppliers prosup)
        {
            int prosupID = 0;

            // create connection
            SqlConnection connection = TravelExpertsDB.GetConnection();

            // create INSERT command
            // ProductSupplierID is IDENTITY so no value provided
            string insertStatement =
                "INSERT INTO Products_Suppliers(ProductId, SupplierId) OUTPUT inserted.ProductSupplierId VALUES(@ProductId, @SupplierId)";
            SqlCommand cmd = new SqlCommand(insertStatement, connection);

            cmd.Parameters.AddWithValue("@ProductId", prosup.ProductId);
            cmd.Parameters.AddWithValue("@SupplierId", prosup.SupplierId);
            try
            {
                connection.Open();

                // execute insert command and get inserted ID
                prosupID = (int)cmd.ExecuteScalar();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                connection.Close();
            }

            return(prosupID);
        }
Exemplo n.º 2
0
        public static suppliers GetProductSupplierId(int prosupID)
        {
            suppliers prosup = null;

            // create connection
            SqlConnection connection = TravelExpertsDB.GetConnection();

            // create SELECT command
            string query = " SELECT * FROM Products_Suppliers WHERE ProductSupplierId = @ProductSupplierId";
            // string query =
            //"SELECT p.ProductId, p.ProdName, s.SupplierId, s.SupName, ps.ProductSupplierId FROM Products as p JOIN Products_Suppliers as ps on p.ProductId = ps.ProductId join Suppliers s on ps.SupplierId = s.SupplierId WHERE ProductSupplierId = @ProductSupplierId";
            SqlCommand cmd = new SqlCommand(query, connection);

            // supply parameter value
            cmd.Parameters.AddWithValue("@ProductSupplierId", prosupID);

            // run the SELECT query
            try
            {
                connection.Open();
                SqlDataReader reader = cmd.ExecuteReader();

                // build customer object to return
                if (reader.Read()) // if there is a customer with this ID
                {
                    prosup = new suppliers
                    {
                        ProductSupplierId = (int)reader["ProductSupplierId"],
                        ProductId         = (int)reader["ProductId"],
                        SupplierId        = (int)reader["SupplierId"]
                                            //ProdName = (string)reader["ProdName"],
                                            //SupName = (string)reader["SupName"]
                    };
                }
                reader.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                connection.Close();
            }

            return(prosup);
        }
Exemplo n.º 3
0
        //public static class Products_suppliersDB
        //{
        public static List <suppliers> GetSuppliers()
        {
            List <suppliers> productSuppliers = new List <suppliers>(); //empty list
            suppliers        pro;                                       //just for reading
            //create the connection
            SqlConnection connection = TravelExpertsDB.GetConnection();

            //create the command  for SELECT query to get the product suppliers
            string query = "SELECT * FROM Products_Suppliers ";

            SqlCommand selectCmd = new SqlCommand(query, connection);

            try
            {
                //open the connection
                connection.Open();
                //run the command
                SqlDataReader reader = selectCmd.ExecuteReader(); //built-in

                //each state data returned, make state object and add to the list
                while (reader.Read())     //while there still is data to read
                {
                    pro = new suppliers
                    {
                        ProductSupplierId = (int)reader["ProductSupplierId"],
                        ProductId         = (int)reader["ProductId"],
                        SupplierId        = (int)reader["SupplierId"]
                    };

                    productSuppliers.Add(pro);
                }
                reader.Close();
            }
            catch (Exception ex)  //error
            {
                throw ex;
            }
            finally  //executes always
            {
                connection.Close();
            }
            //return the list of product
            return(productSuppliers);
        }
Exemplo n.º 4
0
        public static bool DeleteProductSupplier(suppliers prosup)
        {
            bool success = false;

            // create connection
            SqlConnection connection = TravelExpertsDB.GetConnection();

            // create DELETE command
            string deleteStatement =
                "DELETE FROM Products_Suppliers " +
                "WHERE ProductSupplierId = @ProductSupplierId " + // needed for identification
                "AND ProductId  = @ProductId " +                  // the rest - for optimistic concurrency
                "AND SupplierId = @SupplierId ";
            SqlCommand cmd = new SqlCommand(deleteStatement, connection);

            cmd.Parameters.AddWithValue("@ProductSupplierId", prosup.ProductSupplierId);
            cmd.Parameters.AddWithValue("@ProductId", prosup.ProductId);
            cmd.Parameters.AddWithValue("@SupplierId", prosup.SupplierId);

            try
            {
                connection.Open();

                // execute the command
                int count = cmd.ExecuteNonQuery();
                // check if successful
                if (count > 0)
                {
                    success = true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                connection.Close();
            }

            return(success);
        }
Exemplo n.º 5
0
        public static int AddProductSupplier(suppliers prosup)
        {
            int prosupID = 0;

            // create connection
            SqlConnection connection = TravelExpertsDB.GetConnection();

            // create INSERT command
            // ProductSupplierID is IDENTITY so no value provided
            string insertStatement =
                "INSERT INTO Products_Suppliers(ProductId, SupplierId) OUTPUT inserted.ProductSupplierId VALUES(@ProductId, @SupplierId)";
            SqlCommand cmd = new SqlCommand(insertStatement, connection);

            cmd.Parameters.AddWithValue("@ProductId", prosup.ProductId);
            cmd.Parameters.AddWithValue("@SupplierId", prosup.SupplierId);
            try
            {
                connection.Open();

                // execute insert command and get inserted ID
                prosupID = (int)cmd.ExecuteScalar();
                //cmd.ExecuteNonQuery();

                // retrieve generate customer ID to return
                //string selectStatement =
                //    "SELECT IDENT_CURRENT('Customers')";
                //SqlCommand selectCmd = new SqlCommand(selectStatement, connection);
                //custID = Convert.ToInt32(selectCmd.ExecuteScalar()); // returns single value
                //         // (int) does not work in this case
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                connection.Close();
            }

            return(prosupID);
        }
Exemplo n.º 6
0
        public static bool UpdateProductSupplier(suppliers oldprosup, suppliers newprosup)
        {
            bool success = false;     // did not update

            // connection
            SqlConnection connection = TravelExpertsDB.GetConnection();
            // update command
            string updateStatement =
                "UPDATE Products_Suppliers SET ProductId = @NewProductId, SupplierId = @NewSupplierId " +
                "WHERE ProductSupplierId = @OldProductSupplierId " +
                "AND ProductId = @OldProductId " +
                "AND SupplierId = @OldSupplierId";

            SqlCommand cmd = new SqlCommand(updateStatement, connection);

            cmd.Parameters.AddWithValue("@NewProductId ", newprosup.ProductId);
            cmd.Parameters.AddWithValue("@NewSupplierId", newprosup.SupplierId);
            cmd.Parameters.AddWithValue("@OldProductSupplierId", oldprosup.ProductSupplierId);
            cmd.Parameters.AddWithValue("@OldProductId", oldprosup.ProductId);
            cmd.Parameters.AddWithValue("@OldSupplierId", oldprosup.SupplierId);

            try
            {
                connection.Open();
                int count = cmd.ExecuteNonQuery();
                if (count > 0)
                {
                    success = true;     // updated
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                connection.Close();
            }
            return(success);
        }
Exemplo n.º 7
0
        public static suppliers GetSuppliers(string Id, string Id1)
        {
            suppliers productSuppliers;

            // create connection
            SqlConnection connection = TravelExpertsDB.GetConnection();

            // create SELECT command
            string     query = "SELECT ProductId, SupplierId FROM Products_Suppliers WHERE ProductId  = @ProductId AND SupplierId = @SupplierId ";
            SqlCommand cmd   = new SqlCommand(query, connection);

            productSuppliers = new suppliers();
            cmd.Parameters.AddWithValue("@ProductId", int.Parse(Id));
            cmd.Parameters.AddWithValue("@SupplierId", int.Parse(Id1));

            try
            {
                connection.Open();

                // execute the command
                SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow);
                // check if successful
                if (reader.Read())
                {
                    productSuppliers.ProductId  = (int)reader["ProductId"];
                    productSuppliers.SupplierId = (int)reader["SupplierId"];
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                connection.Close();
            }

            return(productSuppliers);
        }
Exemplo n.º 8
0
 public static bool UpdateSupplier(Suppliers supplier, suppliers newSupplier)
 {
     throw new NotImplementedException();
 }