//01 Main Json private static void ImportData(ProductsShopDbContext dbContext) { ImportUsers(dbContext); ImportProducts(dbContext); ImportCategories(dbContext); SetCategories(dbContext); }
private static void SuccessfullySoldProducts() { using (var context = new ProductsShopDbContext()) { var users = context.Users .Where(u => u.ProductsSold.Any(p => p.BuyerId != null)) .OrderBy(u => u.LastName) .ThenBy(u => u.FirstName) .Select(u => new { u.FirstName, u.LastName, SoldProducts = u.ProductsSold .Select(p => new { p.Name, p.Price, BuyerFirstName = p.Buyer.FirstName, BuyerLastName = p.Buyer.LastName }) }) .ToArray(); var result = JsonConvert.SerializeObject(users, Formatting.Indented, new JsonSerializerSettings { DefaultValueHandling = DefaultValueHandling.Ignore }); File.WriteAllText("ExportJson/SuccessffulySoldProducts.json", result); } }
private static string ImportCategoriesFromXml() { string path = "Files/categories.xml"; var xmlString = File.ReadAllText(path); var xmlDoc = XDocument.Parse(xmlString); var elements = xmlDoc.Root.Elements(); var result = new List <Category>(); foreach (var element in elements) { string name = element.Element("name").Value; var category = new Category(name); result.Add(category); } using (var context = new ProductsShopDbContext()) { context.Categories.AddRange(result); context.SaveChanges(); } return($"{result.Count} categories were imported from file: {path}"); }
//Query 2 Json private static void SuccessfullySoldProducts(ProductsShopDbContext db) { var users = db.Users.Where(u => u.ProductsSold.Any(p => p.BuyerId != null)) .OrderBy(u => u.LastName) .ThenBy(u => u.FirstName) .Select(u => new { firstName = u.FirstName, lastName = u.LastName, soldProducts = u.ProductsSold.Select(ps => new { name = ps.Name, price = ps.Price, buyerFirstName = ps.Buyer.FirstName, buyerLasName = ps.Buyer.LastName }) }) .ToArray(); var jsonString = JsonConvert.SerializeObject(users, Formatting.Indented, new JsonSerializerSettings { DefaultValueHandling = DefaultValueHandling.Ignore, // optional NullValueHandling = NullValueHandling.Ignore // optional }); File.WriteAllText("JsonExport/successfullysoldproducts.json", jsonString); }
private static void SetCategories() { using (var context = new ProductsShopDbContext()) { var productIds = context.Products .Select(p => p.ProductId) .ToArray(); var categoryIds = context.Categories .Select(c => c.CategoryId) .ToArray(); var random = new Random(); int categoryCount = categoryIds.Length; var categoryProductsList = new List <CategoryProduct>(); foreach (var id in productIds) { for (int i = 0; i < 3; i++) { int index = random.Next(0, categoryCount); while (categoryProductsList.Any(cp => cp.ProductId == id && cp.CategoryId == categoryIds[index])) { index = random.Next(0, categoryCount); } var categoryProducts = new CategoryProduct(id, categoryIds[index]); categoryProductsList.Add(categoryProducts); } } context.CategoryProducts.AddRange(categoryProductsList); context.SaveChanges(); } }
private static void CategoriesByProductsCountXml() { using (var context = new ProductsShopDbContext()) { var categories = context.Categories .OrderBy(c => c.CategoryProducts.Count) .Select(c => new { Category = c.Name, ProductsCount = c.CategoryProducts.Count, AveragePrice = c.CategoryProducts.Average(p => p.Product.Price), TotalRevenue = c.CategoryProducts.Sum(p => p.Product.Price) }) .ToArray(); var doc = new XElement("categories"); foreach (var category in categories) { doc.Add(new XElement("category", new XAttribute("name", category.Category)), new XElement("products-count", category.ProductsCount), new XElement("average-price", category.AveragePrice), new XElement("total-revenue", category.TotalRevenue)); } doc.Save("ExportXml/CategoriesByProductsCount.xml"); } }
private static void ExtractProductsData(ProductsShopDbContext context) { XmlSerializer serializer = new XmlSerializer(typeof(ProductDTO[]), new XmlRootAttribute("products")); string xmlProducts = File.ReadAllText(@"XMLs\products.xml"); ProductDTO[] productsDto = (ProductDTO[])serializer.Deserialize(new StringReader(xmlProducts)); Product[] products = productsDto .Where(x => IsValid(x)) .Select(x => Mapper.Map <Product>(x)) .ToArray(); Random randomizer = new Random(); foreach (Product product in products) { product.SellerId = randomizer.Next(1, 57); bool productHasNoBuyer = randomizer.Next(1, 5) == 1; if (productHasNoBuyer) { continue; } product.BuyerId = randomizer.Next(1, 57); } context.Products.AddRange(products); context.SaveChanges(); }
private static void SoldProducts() { using (var context = new ProductsShopDbContext()) { var users = context.Users .OrderBy(u => u.LastName) .ThenBy(u => u.FirstName) .Select(u => new { FirstName = u.FirstName ?? "firstName doesn't exist", LastName = u.LastName, SoldProducts = u.ProductsSold .Select(p => new { Name = p.Name, Price = p.Price }) }) .ToArray(); var doc = new XElement("users"); foreach (var user in users) { doc.Add(new XElement("user", new XAttribute("first-name", user.FirstName), new XAttribute("last-name", user.LastName)), new XElement("sold-products", new XElement("product", new XElement("name", user.SoldProducts), new XElement("price", user.SoldProducts)))); } doc.Save("ExportXml/SoldProducts.xml"); } }
// <--- XML ---> private static string ImportUserFromXml() { string path = "Files/users.xml"; string xmlString = File.ReadAllText(path); var xmlDoc = XDocument.Parse(xmlString); var elements = xmlDoc.Root.Elements(); var result = new List <User>(); foreach (var element in elements) { string firstName = element.Attribute("firstName")?.Value; string lastName = element.Attribute("lastName").Value; byte?age = null; if (element.Attribute("age") != null) { age = byte.Parse(element.Attribute("age").Value); } var user = new User(firstName, lastName, age); result.Add(user); } using (var context = new ProductsShopDbContext()) { context.Users.AddRange(result); context.SaveChanges(); } return($"{result.Count} users were imported from file: {path}"); }
private static void ProductsInRange() { using (var context = new ProductsShopDbContext()) { var products = context.Products .Where(p => (p.Price >= 1000 && p.Price <= 2000)) .OrderBy(p => p.Price) .Select(p => new { Name = p.Name, Price = p.Price, Buyer = $"{p.Buyer.FirstName} {p.Buyer.LastName}" }) .ToArray(); var doc = new XElement("products"); foreach (var product in products) { doc.Add(new XElement("product", new XAttribute("name", product.Name), new XAttribute("price", product.Price), new XAttribute("buyer", product.Buyer))); } doc.Save("ExportXml/ProductsInRange.xml"); } }
public static void ExportSoldProducts(ProductsShopDbContext context) { TwoUserDTO[] users = context.Users .Where(x => x.ProductsBought.Count >= 1) .Select(x => new TwoUserDTO { FirstName = x.FirstName, LastName = x.LastName, SoldProducts = x.ProductsBought.Select(p => new TwoProductDTO { Name = p.Name, Price = p.Price }).ToArray() }) .OrderBy(x => x.LastName) .ThenBy(x => x.FirstName) .ToArray(); XmlSerializer serializer = new XmlSerializer(typeof(TwoUserDTO[]), new XmlRootAttribute("users")); using (var writer = new StreamWriter("users-sold-products.xml")) { serializer.Serialize(writer, users); } }
private static void ExportUsersAndProducts(ProductsShopDbContext context) { var result = context.Users.Select(x => new { usersCount = context.Users.Count(), users = context .Users .Where(u => u.ProductsSold.Any(p => p.BuyerId != null)) .OrderByDescending(u => u.ProductsSold.Count) .ThenBy(u => u.LastName).Select(u => new { firstName = u.FirstName, lastName = u.LastName, age = u.Age, soldProducts = new { count = u.ProductsSold.Count, products = u.ProductsSold.Select(p => new { name = p.Name, price = p.Price }) } }) }); string jsonStr = JsonConvert.SerializeObject(result, Formatting.Indented); File.WriteAllText("JSONs/users-and-products.json", jsonStr); }
//Query 01 XML private static void ProductsInRangeXML(ProductsShopDbContext db) { var products = db.Products .Where(p => p.Price > 1000 && p.Price <= 2000 && p.Buyer != null) .OrderBy(p => p.Price) .Select(p => new { name = p.Name, price = p.Price, buyer = p.Buyer.FirstName + " " + p.Buyer.LastName }) .ToArray(); var document = new XDocument(); var root = new XElement("products"); document.Add(root); foreach (var p in products) { document.Root.Add( new XElement("product", new XAttribute("name", $"{p.name}"), new XAttribute("price", $"{p.price}"), new XAttribute("buyer", $"{p.buyer}"))); } document.Save("XMLExport/ProductsInRangeXML.xml"); }
//Query 03 XML private static void CategoriesByProductsCountXML(ProductsShopDbContext db) { var categories = db.Categories .Select(c => new { name = c.Name, numberOfproducts = c.Products.Count, avaragePrice = c.Products.Average(p => p.Product.Price), totalSum = c.Products.Sum(p => p.Product.Price) }) .OrderBy(c => c.numberOfproducts) .ToArray(); XDocument document = new XDocument(); document.Add(new XElement("categories")); foreach (var c in categories) { document.Root.Add( new XElement("category", new XAttribute("name", c.name), new XElement("products-count", $"{c.numberOfproducts}"), new XElement("average-price", $"{c.avaragePrice}"), new XElement("total-revenue", $"{c.totalSum}") )); } document.Save("XMLExport/CategoriesByProductsCountXML.xml"); }
//01 Main XML private static void ImportDataFromXML(ProductsShopDbContext db) { ImportUsersFromXml(db); ImportProductsFromXml(db); ImportCategoriesFromXml(db); SetCategories(db); }
private static void ExportSuccessullySoldProducts(ProductsShopDbContext context) { var result = context .Users .Where(u => u.ProductsSold.Any(p => p.BuyerId != null)) .Select(u => new { firstName = u.FirstName, lastName = u.LastName, soldProducts = u.ProductsSold .Where(p => p.BuyerId != null) .Select(s => new { name = s.Name, price = s.Price, buyerFirstName = s.Buyer.FirstName, buyerLastName = s.Buyer.LastName }).ToArray() }) .OrderBy(p => p.lastName) .ThenBy(p => p.firstName) .ToArray(); string jsonStr = JsonConvert.SerializeObject(result, Formatting.Indented); File.WriteAllText("JSONs/users-sold-products.json", jsonStr); }
//01 XML private static void ImportUsersFromXml(ProductsShopDbContext db) { string xmlString = File.ReadAllText("Files/users.xml"); XDocument xml = XDocument.Parse(xmlString); var root = xml.Root.Elements(); List <User> users = new List <User>(); foreach (var e in root) { var firstName = e.Attribute("firstName")?.Value; var lastName = e.Attribute("lastName").Value; int?age = null; if (e.Attribute("age") != null) { age = int.Parse(e.Attribute("age").Value); } var user = new User { FirstName = firstName, LastName = lastName, Age = age }; users.Add(user); } db.Users.AddRange(users); db.SaveChanges(); }
//01 Json private static void SetCategories(ProductsShopDbContext db) { var productIds = db.Products.Select(p => p.ProductId).ToArray(); var categoryIds = db.Categories.Select(c => c.CategoryId).ToArray(); Random rnd = new Random(); List <CategoryProduct> categoryProducts = new List <CategoryProduct>(); foreach (var p in productIds) { for (int i = 0; i < 3; i++) { int index = rnd.Next(0, categoryIds.Length); while (categoryProducts.Any(c => c.ProductId == p && c.CategoryId == categoryIds[index])) { index = rnd.Next(0, categoryIds.Length); } CategoryProduct cp = new CategoryProduct { ProductId = p, CategoryId = categoryIds[index] }; categoryProducts.Add(cp); } } db.CategoryProducts.AddRange(categoryProducts); db.SaveChanges(); }
private static void ExtractUsersData(ProductsShopDbContext context) { string jsonStr = File.ReadAllText("JSONs/users.json"); List <User> users = JsonConvert.DeserializeObject <List <User> >(jsonStr); context.Users.AddRange(users); context.SaveChanges(); }
private static void ExtractCategoriesData(ProductsShopDbContext context) { string jsonStr = File.ReadAllText("JSONs/categories.json"); List <Category> categories = JsonConvert.DeserializeObject <List <Category> >(jsonStr); context.Categories.AddRange(categories); context.SaveChanges(); }
//01 Json private static void ImportCategories(ProductsShopDbContext db) { string jsonToString = File.ReadAllText("Files/categories.json"); Category[] categories = JsonConvert.DeserializeObject <Category[]>(jsonToString); db.Categories.AddRange(categories); db.SaveChanges(); }
public static void Main(string[] args) { var db = new ProductsShopDbContext(); using (db) { //ResetDb(db); //UsersandProductsXML(db); } }
public static void Main() { ProductsShopDbContext context = new ProductsShopDbContext(); ExportProductsInRange(context); ExportSoldProducts(context); ExportCategoriesByProductsCount(context); ExportUsersWithProducts(context); }
public static void Main() { ProductsShopDbContext context = new ProductsShopDbContext(); ExportProductsInRange(context); ExportSuccessullySoldProducts(context); ExportCategoriesByProductsCoutn(context); ExportUsersAndProducts(context); }
//01 XML private static void ImportProductsFromXml(ProductsShopDbContext db) { string xmlString = File.ReadAllText("Files/products.xml"); XDocument xmDocument = XDocument.Parse(xmlString); var root = xmDocument.Root.Elements(); Random rnd = new Random(); var userIds = db.Users.Select(u => u.UserId).ToArray(); List <Product> products = new List <Product>(); foreach (var p in root) { string name = p.Element("name").Value; decimal price = decimal.Parse(p.Element("price").Value); int index = rnd.Next(0, userIds.Length); int sellerId = userIds[index]; int?bayerId = sellerId; while (bayerId == sellerId) { int bayerIndex = rnd.Next(0, userIds.Length); bayerId = userIds[bayerIndex]; } if (bayerId - sellerId < 5 && bayerId - sellerId > 0) { bayerId = null; } var product = new Product { Name = name, Price = price, SellerId = sellerId, BuyerId = bayerId, }; products.Add(product); } db.Products.AddRange(products); db.SaveChanges(); }
//Query 04 XML private static void UsersandProductsXML(ProductsShopDbContext db) { var users = db.Users .Where(u => u.ProductsSold.Any(p => p.BuyerId != null)) .Select(u => new { FirstName = u.FirstName, LastName = u.LastName, Age = u.Age, AllProducts = u.ProductsSold.Select(ps => new { ProductName = ps.Name, ProductPrice = ps.Price, }) }) .OrderByDescending(u => u.AllProducts.Count()) .ThenBy(u => u.LastName) .ToArray(); XDocument document = new XDocument(); document.Add(new XElement("users", new XAttribute("count", $"{users.Length}"))); // root foreach (var u in users) { document.Root.Add( new XElement("user", new XAttribute("first-name", $"{u.FirstName}"), new XAttribute("last-name", $"{u.LastName}"), new XAttribute("age", $"{u.Age}"), new XElement("sold-products", new XAttribute("count", $"{u.AllProducts.Count()}")) )); var products = document.Root.Elements() .SingleOrDefault(p => p.Name == "user" && p.Attribute("first-name").Value == $"{u.FirstName}" && p.Attribute("last-name").Value == $"{u.LastName}") .Element("sold-products"); foreach (var prod in u.AllProducts) { products.Add( new XElement("product", new XAttribute("name", $"{prod.ProductName}"), new XAttribute("price", $"{prod.ProductPrice}"))); } } document.Save("XMLExport/UsersandProductsXML.xml"); }
private static string ImportUsersFromJson() { string path = "Files/users.json"; var users = ImportJson <User>(path); using (var context = new ProductsShopDbContext()) { context.Users.AddRange(users); context.SaveChanges(); } return($"{users.Length} users were imported from file: {path}"); }
private static string ImportCategoriesFromJson() { string path = "Files/categories.json"; var categories = ImportJson <Category>(path); using (var context = new ProductsShopDbContext()) { context.Categories.AddRange(categories); context.SaveChanges(); } return($"{categories.Length} categories were imported from file: {path}"); }
private static void ExtractUsersData(ProductsShopDbContext context) { XmlSerializer serializer = new XmlSerializer(typeof(UserDTO[]), new XmlRootAttribute("users")); string xmlUsers = File.ReadAllText(@"XMLs\users.xml"); UserDTO[] usersDto = (UserDTO[])serializer.Deserialize(new StringReader(xmlUsers)); User[] users = usersDto .Where(x => IsValid(x)).Select(x => Mapper.Map <User>(x)) .ToArray(); context.Users.AddRange(users); context.SaveChanges(); }
//01 Json private static void ImportUsers(ProductsShopDbContext dbContext) { string path = "Files/users.json"; string jsonString = File.ReadAllText(path); User[] users = JsonConvert.DeserializeObject <User[]>(jsonString); dbContext.Users.AddRange(users); dbContext.SaveChanges(); }