Пример #1
0
      public static ProductSupplier GetProductSupplierById(int productSupplierID)
      {
          int tempSupId;
          int tempProdId;

          SqlConnection connection      = TravelExpertsDB.GetConnection();
          string        selectStatement =
              "SELECT ProductSupplierId, ProductID,SupplierId " +
              "FROM Products_Suppliers " +
              "WHERE ProductSupplierId = @ProductSupplierId";
          SqlCommand selectCommand = new SqlCommand(selectStatement, connection);

          selectCommand.Parameters.AddWithValue("@ProductSupplierId", productSupplierID);
          try
          {
              connection.Open();
              SqlDataReader Reader = selectCommand.ExecuteReader
                                         (CommandBehavior.SingleRow); // selecting by PK value
              if (Reader.Read())
              {                                                       // we have  a customer
                  ProductSupplier productSupplier = new ProductSupplier();
                  productSupplier.ProductSupplierId = (int)Reader["ProductSupplierId"];
                  tempSupId  = (int)Reader["SupplierId"];
                  tempProdId = (int)Reader["ProductId"];

                  connection.Close();
                  productSupplier.Supplier = SupplierDB.GetSupplierByID(tempSupId);
                  productSupplier.Product  = ProductDB.GetEntityById(tempSupId);

                  return(productSupplier);
              }
              else   // no customer
              {
                  return(null);
              }
          }
          catch (SqlException ex)
          {
              throw ex;
          }
          finally
          {
              connection.Close();
          }
      }
Пример #2
0
      public static List <ProductSupplier> GetProductSuppliers()
      {
          List <ProductSupplier> ProdSupList = new List <ProductSupplier>();
          string        connectionString     = "Data Source=localhost\\SAIT;Initial Catalog=TravelExperts;Integrated Security=True";
          SqlConnection connection           = new SqlConnection(connectionString);
          string        selectStatement      = "SELECT ProductSupplierId ,ps.ProductId as ProductId, ProdName, ps.SupplierId as SupplierId, SupName " +
                                               "FROM Products_Suppliers as ps " +
                                               "INNER JOIN Products as p ON p.ProductId = ps.ProductId " +
                                               "INNER JOIN Suppliers as s on s.SupplierId = ps.SupplierId " +
                                               "GROUP BY ProductSupplierId, ps.SupplierId, ps.ProductId, p.ProdName, s.SupName";
          SqlCommand selectCommand = new SqlCommand(selectStatement, connection);

          try
          {
              connection.Open();
              SqlDataReader reader = selectCommand.ExecuteReader();
              while (reader.Read())
              {
                  ProductSupplier productSupplier = new ProductSupplier();
                  Product         product         = new Product();
                  Supplier        supplier        = new Supplier();
                  productSupplier.ProductSupplierId = (int)reader["ProductSupplierId"];
                  product.ProductId        = (int)reader["ProductId"];
                  product.Name             = (string)reader["ProdName"];
                  supplier.SupplierId      = (int)reader["SupplierId"];
                  supplier.Name            = (string)reader["SupName"];
                  productSupplier.Product  = product;
                  productSupplier.Supplier = supplier;

                  ProdSupList.Add(productSupplier);
              }
              connection.Close();
              foreach (ProductSupplier ProdSup in ProdSupList)
              {
                  ProdSup.Product  = ProductDB.GetEntityById(ProdSup.Product.ProductId);
                  ProdSup.Supplier = SupplierDB.GetSupplierByID(ProdSup.Supplier.SupplierId);
              }
          }
          catch (Exception)
          {
              throw;
          }
          return(ProdSupList);
      }