//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();
}
