コード例 #1
0
ファイル: InsertUser.cs プロジェクト: churulik/SoftUni
        public static void Insert()
        {
            var context = new ProductsShopEntities();

            var loadUsersXml = XDocument.Load("../../../users.xml");

            var getAttributesValue = from u in loadUsersXml.Descendants("users").Elements("user")
                                     where u.Attribute("age") != null
                                     select new
            {
                FirstName = u.Attribute("first-name") != null?u.Attribute("first-name").Value : null,
                LastName  = u.Attribute("last-name").Value,
                Age       = u.Attribute("age").Value
            };

            foreach (var getAttributeValue in getAttributesValue)
            {
                var newUser = new User()
                {
                    FirstName = getAttributeValue.FirstName,
                    LastName  = getAttributeValue.LastName,
                    Age       = int.Parse(getAttributeValue.Age)
                };

                context.Users.Add(newUser);
            }
            context.SaveChanges();
        }
コード例 #2
0
        internal static void fourthQuery()
        {
            var context = new ProductsShopEntities();

            var users = context.Users
                .Where(u => u.SoldProducts.Count > 0)
                .OrderByDescending(u => u.SoldProducts.Count)
                .ThenBy(u => u.LastName)
                .Select(u => new
                {
                    u.FirstName,
                    u.LastName,
                    u.Age,
                    ProductsSold = u.SoldProducts.Select(sp => new
                    {
                        sp.Price,
                        sp.Name
                    })
                });

            XmlDocument userDoc = new XmlDocument();
            XmlDeclaration xmlDeclaration = userDoc.CreateXmlDeclaration("1.0", "UTF-8", null);

            XmlElement rootNode = userDoc.CreateElement("users");
            rootNode.SetAttribute("count", users.Count().ToString());
            userDoc.InsertBefore(xmlDeclaration, userDoc.DocumentElement);
            userDoc.AppendChild(rootNode);

            foreach (var user in users)
            {
                XmlElement userNode = userDoc.CreateElement("user");
                if (user.FirstName != null)
                {
                    userNode.SetAttribute("first-name", user.FirstName);
                }

                userNode.SetAttribute("last-name", user.LastName);

                if (user.Age != null)
                {
                    userNode.SetAttribute("age", user.Age.ToString());
                }

                XmlElement productsNode = userDoc.CreateElement("sold-products");
                productsNode.SetAttribute("count", user.ProductsSold.Count().ToString());

                foreach (var products in user.ProductsSold)
                {
                    XmlElement proNode = userDoc.CreateElement("product");
                    proNode.SetAttribute("name", products.Name);
                    proNode.SetAttribute("price", products.Price.ToString("F"));
                    productsNode.AppendChild(proNode);
                }
                userNode.AppendChild(productsNode);
                rootNode.AppendChild(userNode);
            }

            userDoc.Save(@"..\..\fourthQuery.xml");
        }
コード例 #3
0
        public static void thirdQuery()
        {
            var context = new ProductsShopEntities();

            var categories = context.Categories
                .OrderBy(c => c.Products.Count)
                .Select(c => new
                {
                    category = c.Name,
                    productsCount = c.Products.Count,
                    averagePrice = c.Products.Average(p => p.Price),
                    totalRevenue = c.Products.Sum(p => p.Price)
                });

            var serCategories = JsonConvert.SerializeObject(categories, Formatting.Indented);
            System.IO.File.WriteAllText(@"..\..\thirdQueryExport.json", serCategories);
        }
コード例 #4
0
        public static void firstQuery()
        {
            var context = new ProductsShopEntities();

            var products = context.Products
                .Where(p => p.Price >= 500 &&
                            p.Price <= 1000 &&
                            p.BuyerId == null)
                .OrderBy(p => p.Price)
                .Select(p => new
                {
                    p.Name,
                    p.Price,
                    Seller = (p.Seller.FirstName + " " + p.Seller.LastName).TrimStart()
                });

            var serProducts = JsonConvert.SerializeObject(products, Formatting.Indented);
            System.IO.File.WriteAllText(@"..\..\firstQueryExport.json", serProducts);
        }
コード例 #5
0
ファイル: InsertCategory.cs プロジェクト: churulik/SoftUni
        public static void Insert()
        {
            var context = new ProductsShopEntities();

            var json = File.ReadAllText("../../../categories.json");

            var getCategories = JsonConvert.DeserializeObject <List <GetCategories> >(json);

            foreach (var category in getCategories)
            {
                var newCategory = new Category()
                {
                    CategoryName = category.Name
                };
                context.Categories.Add(newCategory);
            }

            context.SaveChanges();
        }
コード例 #6
0
ファイル: Query3.cs プロジェクト: churulik/SoftUni
        public static void CategoriesByProductsCount()
        {
            var context = new ProductsShopEntities();

            var categories = context.Categories;

            var getAllCategories = from c in categories
                                   orderby c.Products.Count
                                   select new
            {
                CategoryName  = c.CategoryName,
                ProductsCount = c.Products.Count,
                AveragePrice  = c.Products.Average(a => a.Price),
                TotalRevenue  = c.Products.Sum(s => s.Price)
            };

            var serializeCategories = JsonConvert.SerializeObject(getAllCategories, Formatting.Indented);

            File.WriteAllText(@"../../QueryAndExportData/JSON/categories-by-products.json", serializeCategories);

            Console.WriteLine(serializeCategories);
        }
コード例 #7
0
        public static void Insert()
        {
            var context = new ProductsShopEntities();

            var productsToList   = context.Products.ToList();
            var categoriesToList = context.Categories.ToList();

            var rand = new Random();

            var collectCategories = new Collection <Category>();

            for (int i = 0; i < productsToList.Count; i++)
            {
                var index = rand.Next(0, categoriesToList.Count);

                collectCategories.Add(categoriesToList[index]);

                productsToList[i].Categories.Add(collectCategories[i]);
            }

            context.SaveChanges();
        }
コード例 #8
0
ファイル: Query2.cs プロジェクト: churulik/SoftUni
        public static void SuccessfullySoldProducts()
        {
            var context = new ProductsShopEntities();

            var users = context.Users;

            var products = context.Products;

            var getAllUsers = from u in users
                              join p in products on u.Id equals p.BuyerId
                              where p.BuyerId != null
                              orderby u.LastName, u.FirstName
            group p by new
            {
                LastName  = p.Seller.LastName,
                FirstName = p.Seller.FirstName ?? "Unknown"
            }
            into g
                select new
            {
                FirstName    = g.Key.FirstName,
                LastName     = g.Key.LastName,
                SoldProducts = from pr in g
                               select new
                {
                    ProductName    = pr.ProductName,
                    Price          = pr.Price,
                    BuyerFirstName = pr.Buyer.FirstName ?? "Unknown",
                    BuyerLastNaem  = pr.Buyer.LastName
                }
            };

            var serializeUsers = JsonConvert.SerializeObject(getAllUsers, Formatting.Indented);

            File.WriteAllText(@"../../QueryAndExportData/JSON/successfully-sold-products.json", serializeUsers);

            Console.WriteLine(serializeUsers);
        }
コード例 #9
0
        public static void ProductsInRange()
        {
            var context = new ProductsShopEntities();

            var products = context.Products;

            var getAllProducts = from p in products
                                 where p.Price >= 500 && p.Price <= 1000 &&
                                 p.BuyerId == null
                                 orderby p.Price
                                 select new
            {
                ProductName = p.ProductName,
                Price       = p.Price,
                SellerName  = p.Seller.FirstName + " " + p.Seller.LastName
            };

            var serializeProducts = JsonConvert.SerializeObject(getAllProducts, Formatting.Indented);

            File.WriteAllText(@"../../QueryAndExportData/JSON/product-in-range.json", serializeProducts);

            Console.WriteLine(serializeProducts);
        }
コード例 #10
0
        public static void secondQuery()
        {
            var context = new ProductsShopEntities();

            var users = context.Users
                .Where(u => u.SoldProducts.Count > 0 && u.SoldProducts.All(sp => sp.BuyerId != null))
                .OrderBy(u => u.LastName)
                .ThenBy(u => u.FirstName)
                .Select(u => new
                {
                    firstName = u.FirstName,
                    lastName = u.LastName,
                    soldProducts = u.SoldProducts
                    .Select(sp => new
                    {
                        name = sp.Name,
                        price = sp.Price,
                        buyerFirstName = sp.Buyer.FirstName,
                        buyerLastName = sp.Buyer.LastName
                    })
                });
            var serUsers = JsonConvert.SerializeObject(users, Formatting.Indented);
            System.IO.File.WriteAllText(@"..\..\secondQueryExport.json", serUsers);
        }
コード例 #11
0
        public static void UsersAndProducts()
        {
            var context = new ProductsShopEntities();

            var users = context.Users;

            var products = context.Products;

            var getAllUsers = from u in users
                              join p in products on u.Id equals p.BuyerId
                              where p.BuyerId != null && u.FirstName != null
                              orderby u.LastName
                              group p by new
            {
                p.Buyer.FirstName,
                p.Buyer.LastName,
                p.Buyer.Age
            }
            into g
                select new
            {
                g.Key.FirstName,
                g.Key.LastName,
                g.Key.Age,
                Product = from pr in g
                          select new
                {
                    pr.ProductName,
                    pr.Price
                }
            };
            var count       = 0;
            var totalCaount = 0;

            var xmlUsers = new XElement("users");

            foreach (var user in getAllUsers)
            {
                var xmlSingleUser = new XElement("user");
                xmlSingleUser.Add(new XAttribute("first-name", user.FirstName));
                xmlSingleUser.Add(new XAttribute("last-name", user.LastName));
                xmlSingleUser.Add(new XAttribute("age", user.Age));

                var xmlSoldProducts = new XElement("sold-products");

                foreach (var product in user.Product)
                {
                    var xmlProduct = new XElement("product");
                    xmlProduct.Add(new XAttribute("name", product.ProductName));
                    xmlProduct.Add(new XAttribute("price", product.Price));
                    xmlSoldProducts.Add(xmlProduct);
                    count++;
                    totalCaount++;
                }

                xmlSoldProducts.Add(new XAttribute("count", count));

                xmlSingleUser.Add(xmlSoldProducts);

                xmlUsers.Add(xmlSingleUser);

                count = 0;
            }
            xmlUsers.Add(new XAttribute("count", totalCaount));

            Console.WriteLine(xmlUsers);

            var xmlDoc = new XDocument(xmlUsers);

            xmlDoc.Save("../../QueryAndExportData/XML/users-and-products.xml");
        }
コード例 #12
0
        public static void Insert()
        {
            var context = new ProductsShopEntities();

            var json = File.ReadAllText("../../../products.json");

            var getProducts = JsonConvert.DeserializeObject <List <GetProducts> >(json);

            //Get the first and last Users Id

            var firstId = context.Users.First().Id;
            var lastId  = context.Users.OrderByDescending(i => i.Id).First().Id;


            //Insert, randomly, into array the BuyerId value

            var rand = new Random();

            var nullIndex = 1;

            var buyerArray = new int?[getProducts.Count];

            for (var i = 0; i < getProducts.Count; i++)
            {
                buyerArray[i] = rand.Next(firstId, lastId);

                nullIndex++;

                //Every 10th row will have a null value

                if (nullIndex != 10)
                {
                    continue;
                }
                buyerArray[i] = null;
                nullIndex     = 1;
            }

            //Insert, randomly, into array the SellerId value

            var sellerArray = new int[getProducts.Count];

            for (var i = 0; i < getProducts.Count; i++)
            {
                sellerArray[i] = rand.Next(firstId, lastId);
            }

            //Insert into Products

            var index = 0;

            foreach (var product in getProducts)
            {
                var newProduct = new Product()
                {
                    ProductName = product.Name,
                    Price       = product.Price,
                    SellerId    = sellerArray[index],
                    BuyerId     = buyerArray[index]
                };
                context.Products.Add(newProduct);
                index++;
            }

            context.SaveChanges();
        }
コード例 #13
0
        static void Main(string[] args)
        {
            var migrationStrategy = new MigrateDatabaseToLatestVersion <ProductsShopEntities, Configuration>();

            Database.SetInitializer(migrationStrategy);

            var context = new ProductsShopEntities();

            //Query 1 - Products In Range

            //var products = context.Products
            //    .Where(p => p.Buyer == null && p.Price >= 500m && p.Price <= 1000m)
            //    .OrderBy(p => p.Price)
            //    .Select(p => new
            //                {
            //                    p.Name,
            //                    p.Price,
            //                    Seller = (p.Seller.FirstName != null ? p.Seller.FirstName + " " : "") + p.Seller.LastName
            //                });

            //var json = JsonConvert.SerializeObject(products, Formatting.Indented);

            //Console.WriteLine(json);
            //Console.ReadLine();

            //Query 2 - Successfully Sold Products

            //var sellers = context.Users
            //    .Where(u => u.ProductsSold.Any(p => p.Buyer != null))
            //    .OrderBy(u => u.LastName)
            //    .ThenBy(u => u.FirstName)
            //    .Select(u => new
            //            {
            //                u.FirstName,
            //                u.LastName,
            //                ProductsSold = u.ProductsSold
            //                .Where(p => p.Buyer != null)
            //                .Select(p => new
            //                            {
            //                                p.Name,
            //                                p.Price,
            //                                BuyerFirstName = p.Buyer.FirstName != null ? p.Buyer.FirstName : "",
            //                                BuyerLastName = p.Buyer.LastName
            //                            })
            //    });

            //var json = JsonConvert.SerializeObject(sellers, Formatting.Indented);

            //Console.WriteLine(json);
            //Console.ReadLine();

            //Categories By Products Count

            //var categories = context.Categories
            //    .OrderByDescending(c => c.Products.Count())
            //    .Select(c => new
            //                {
            //                    c.Name,
            //                    ProductsCount = c.Products.Count(),
            //                    AveragePrice = c.Products.Average(p => p.Price),
            //                    TotalRevenye = c.Products.Sum(p => p.Price)
            //                });

            //var json = JsonConvert.SerializeObject(categories, Formatting.Indented);

            //Console.WriteLine(json);
            //Console.ReadLine();

            //Query 4 - Users and Products
            //Unfinished

            var sellers = context.Users
                          .Where(u => u.ProductsSold.Count() >= 1)
                          .OrderByDescending(u => u.ProductsSold.Count())
                          .ThenBy(u => u.LastName)
                          .Select(u => new
            {
                u.FirstName,
                u.LastName,
                u.Age,
                ProductsSold = u.ProductsSold.Select(p => new
                {
                    p.Name,
                    p.Price
                })
            });

            JArray sellersArray = new JArray();

            foreach (var user in sellers)
            {
                JObject userObject = new JObject();

                if (user.FirstName != null)
                {
                    userObject.Add(new JProperty("@first-name", user.FirstName));
                }

                userObject.Add(new JProperty("@last-name", user.LastName));

                if (user.Age != null)
                {
                    userObject.Add(new JProperty("@age", user.Age));
                }

                JObject soldProducts = new JObject();
                soldProducts.Add(new JProperty("@count", user.ProductsSold.Count()));

                JArray products = new JArray();

                foreach (var product in user.ProductsSold)
                {
                    JObject productObject = new JObject();
                    productObject.Add(new JProperty("@price", product.Price));
                    productObject.Add(new JProperty("@name", product.Name));
                    products.Add(productObject);
                }

                soldProducts.Add(new JProperty("product", products));

                userObject.Add(new JProperty("sold-products", soldProducts));

                sellersArray.Add(userObject);
            }

            JObject users = new JObject();

            users.Add(new JProperty("@count", sellers.Count()));
            users.Add(new JProperty("user", sellersArray));

            JObject main = new JObject();

            main.Add(new JProperty("?xml",
                                   new JObject(new JProperty("@version", "1.0"), new JProperty("@encoding", "utf-8"))));
            main.Add(new JProperty("users", users));

            var json = JsonConvert.SerializeObject(main, Formatting.Indented);

            XDocument xmlFromJson = JsonConvert.DeserializeXNode(json);

            xmlFromJson.Save("../../../../../sellers.xml");



            //Console.WriteLine(main);
        }