public ActionResult GetAllProducts()
{
var conn = new ProductsDbContext();
var productsList = conn.GetProducts();
return(Ok(productsList));
}
/*Query 3 - Categories by Product Count*/
internal static void GetCategoriesByProductCount()
{
using (var context = new ProductsDbContext())
{
var categories = context.Categories
.Include(c => c.CategoryProducts)
.OrderBy(c => c.CategoryProducts.Count)
.Select(c => new
{
name = c.Name,
numberOfPoducts = c.CategoryProducts.Count,
avgPriceInCategory = c.CategoryProducts.Average(cp => cp.Product.Price),
totalRevenue = c.CategoryProducts.Sum(cp => cp.Product.Price)
})
.ToArray();
var doc = new XDocument(new XElement("categories"));
foreach (var cat in categories)
{
doc.Root
.Add(new XElement("category", new XAttribute("name", cat.name),
new XElement("products-count", cat.numberOfPoducts),
new XElement("average-price", cat.avgPriceInCategory),
new XElement("total-revenue", cat.totalRevenue)));
}
doc.Save("CompletedTasksXMLs/Q2.3_CategoriesByProdCount.xml");
}
}
/*======================== Pr.02 ========================*/
/*Query 1 - Products In Range*/
internal static void GetProductsInRange()
{
using (var context = new ProductsDbContext())
{
var products = context.Products
.Where(p => p.Buyer != null && (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 XDocument();
doc.Add(new XElement("products"));
foreach (var p in products)
{
doc.Element("products")
.Add(new XElement("product",
new XAttribute("name", p.name),
new XAttribute("price", p.price),
new XAttribute("buyer", p.buyer)));
}
doc.Save("CompletedTasksXMLs/Q2.1_ProductsInRange.xml");
}
}
public async Task GetProductReturnsProductUsingInvalidId()
{
var options = new DbContextOptionsBuilder <ProductsDbContext>()
.EnableSensitiveDataLogging()
.UseInMemoryDatabase(nameof(GetProductReturnsProductUsingInvalidId))
.Options;
var context = new ProductsDbContext(options);
CreateProducts(context);
var productProfile = new ProductProfile();
var configuration = new MapperConfiguration(config =>
config.AddProfile(productProfile));
var mapper = new Mapper(configuration);
var provider = new ProductsProvider(context, null, mapper);
var(isSuccess, product, errorMessage) = await provider.GetProductAsync(-1);
Assert.False(isSuccess);
Assert.Null(product);
Assert.NotNull(errorMessage);
}
// This method gets called by the runtime. Use this method to configure the HTTP request pipeline.
public void Configure(
IApplicationBuilder app,
IWebHostEnvironment env,
ProductsDbContext productsDbContext)
{
if (env.IsDevelopment())
{
app.UseDeveloperExceptionPage();
app.UseSwagger();
app.UseSwaggerUI(c => c
.SwaggerEndpoint("/swagger/v1/swagger.json", "WebApi v1")
);
}
app.UseHttpsRedirection();
app.UseRouting();
app.UseAuthorization();
app.UseEndpoints(endpoints =>
{
endpoints.MapControllers();
});
productsDbContext.Database.EnsureCreated();
//app.UseMigrationsEndPoint();
//app.UseMvc();
}
public async Task GetProductsReturnsAllProducts()
{
//var context = new ProductsDbContext();
//var productsProvider = new ProductsProvider();
var options = new DbContextOptionsBuilder <ProductsDbContext>()
.EnableSensitiveDataLogging()
.UseInMemoryDatabase(nameof(GetProductsReturnsAllProducts))
.Options;
var context = new ProductsDbContext(options);
CreateProducts(context);
var productProfile = new ProductProfile();
var configuration = new MapperConfiguration(config =>
config.AddProfile(productProfile));
var mapper = new Mapper(configuration);
var provider = new ProductsProvider(context, null, mapper);
var(isSuccess, products, errorMessage) = await provider.GetProductsAsync();
Assert.True(isSuccess);
Assert.True(products.Any());
Assert.Null(errorMessage);
}
public static DataTable GetContentsOf(string tableName)
{
using (var dbContext = new ProductsDbContext())
using (var connection = new SqlCeConnection(dbContext.Database.Connection.ConnectionString))
using (var command = connection.CreateCommand())
{
var sql = "SELECT* FROM " + tableName;
command.CommandText = sql;
var adapter = new SqlCeDataAdapter(command);
var dataSet = new DataSet();
adapter.Fill(dataSet);
return dataSet.Tables[0];
}
//var connection = new SqlConnection(ConnectionString);
//using (connection)
//{
// connection.Open();
// var command = new SqlCommand("SELECT * FROM " + tableName, connection);
// var adapter = new SqlDataAdapter(command);
// var dataSet = new DataSet();
// adapter.Fill(dataSet);
// return dataSet.Tables[0];
//}
}
// This method gets called by the runtime. Use this method to configure the HTTP request pipeline.
public void Configure(IApplicationBuilder app, IWebHostEnvironment env, ProductsDbContext context)
{
if (env.IsDevelopment())
{
app.UseDeveloperExceptionPage();
}
app.UseHttpsRedirection();
app.UseRouting();
app.UseAuthorization();
app.UseAuthorization();
app.UseEndpoints(endpoints =>
{
endpoints.MapControllers();
});
// Activate SSL
app.UseRewriter(new RewriteOptions().AddRedirectToHttpsPermanent());
// Seed products to database on startup
DbProductsSeed.Initialize(context);
}
public async void GetProductReturnsProductUsingValidId()
{
//arrange
var options = new DbContextOptionsBuilder <ProductsDbContext>()
.UseInMemoryDatabase(nameof(GetProductReturnsProductUsingValidId))
.Options;
var dbContext = new ProductsDbContext(options);
CreateProducts(dbContext);
var productProfile = new ProductProfile();
var configuration = new MapperConfiguration(cfg => cfg.AddProfile(productProfile));
var mapper = new Mapper(configuration);
var productsProvider = new ProductsProvider(dbContext, null, mapper);
//act
var result = await productsProvider.GetProductAsync(1);
//assert
Assert.True(result.IsSuccess);
Assert.NotNull(result.Product);
Assert.True(result.Product.Id == 1);
Assert.Null(result.ErrorMessage);
}
public void Seed()
{
if (Seeded)
{
return;
}
var builder = new ConfigurationBuilder()
.SetBasePath(Directory.GetCurrentDirectory())
.AddJsonFile("appsettings.json");
var configuration = builder.Build();
var dbContext = new DbContextOptionsBuilder <ProductsDbContext> ()
.UseSqlite(configuration.GetConnectionString("DefaultConnection"))
.Options;
using (var context = new ProductsDbContext(dbContext)) {
//
context.Database.EnsureDeleted();
context.Database.EnsureCreated();
// Add a new product
using (StreamReader reader = new StreamReader("data.json")) {
var json = reader.ReadToEnd();
var products = JsonConvert.DeserializeObject <List <Product> > (json);
context.Products.AddRange(products);
context.SaveChanges();
context.Database.CloseConnection();
}
}
Seeded = true;
}
/*Query 3 - Categories By Products Count*/
internal static void GetCategoriesByProductCount()
{
using (var context = new ProductsDbContext())
{
var result = context.Categories
.OrderBy(c => c.Name)
.Include(c => c.CategoryProducts)
.Select(c => new
{
category = c.Name,
productsCount = c.CategoryProducts
.Where(cp => cp.CategoryId == c.Id)
.Count(),
averagePrice = c.CategoryProducts
.Where(cp => cp.CategoryId == c.Id)
.Average(cp => cp.Product.Price),
totalRevenue = c.CategoryProducts
.Where(cp => cp.CategoryId == c.Id)
.Sum(cp => cp.Product.Price)
})
.ToArray();
string path = "CompletedTasksJSONs/Q2.3_CategoriesByProdCount.json";
DeserializeResult(result, path);
}
}
public async Task GetProduct_ReturnProduct_UsingInValidId()
{
//Arrange
var options = new DbContextOptionsBuilder <ProductsDbContext>()
.UseInMemoryDatabase(nameof(GetProduct_ReturnProduct_UsingInValidId))
.Options;
var dbContext = new ProductsDbContext(options);
SeedData(dbContext);
var productProfile = new ProductProfile();
var config = new MapperConfiguration(cfg => cfg.AddProfile(productProfile));
var mapper = new Mapper(config);
var productProvider = new ProductsProvider(dbContext, null, mapper);
//Act
var result = await productProvider.GetProductAsync(-1);
//Assert
Assert.False(result.IsSuccess);
Assert.Null(result.product);
Assert.NotNull(result.ErrorMessage);
}
/// <summary>
/// Initializes a new instance of <seealso cref="ProductsProvider"/>
/// </summary>
/// <param name="dbContext"></param>
/// <param name="logger"></param>
/// <param name="mapper"></param>
public ProductsProvider(Db.ProductsDbContext dbContext, ILogger <ProductsProvider> logger, IMapper mapper)
{
_dbContext = dbContext;
_logger = logger;
_mapper = mapper;
SeedData();
}
public ProductsController(
ProductsDbContext dbContext,
ILogger <ProductsController> logger)
{
_dbContext = dbContext;
_logger = logger;
}
private void Seed()
{
_products = new List <Product>()
{
new Product()
{
ProductName = "Mouse", Price = 10.00M
},
new Product()
{
ProductName = "Keyboard", Price = 15.00M
},
new Product()
{
ProductName = "Gamepad", Price = 25.00M
},
};
using (var context = new ProductsDbContext(_options))
{
context.Database.EnsureDeleted();
context.Products.AddRange(_products);
context.SaveChanges();
}
}
public static void Seed(this ProductsDbContext dbContext)
{
if (dbContext.Items.Any() ||
dbContext.Products.Any() ||
dbContext.Stores.Any())
{
return;
}
dbContext.Stores.AddRange(new List <Store> {
new Store {
Name = "Los Angeles - Pasadena", StoreId = new Guid("8048e9ec-80fe-4bad-bc2a-e4f4a75c834e")
},
new Store {
Name = "Los Angeles - Beverly Hills", StoreId = new Guid("8d618778-85d7-411e-878b-846a8eef30c0")
}
});
var productsTxt = File.ReadAllText("products.json");
var products = JsonConvert.DeserializeObject <List <Product> >(productsTxt);
dbContext.Products.AddRange(products);
dbContext.SaveChanges();
}
static void Main(string[] args)
{
CultureInfo.CurrentCulture = new CultureInfo("en-US");
var csvConfiguration = new CsvHelper.Configuration.CsvConfiguration(CultureInfo.CurrentCulture)
{
Delimiter = ",",
PrepareHeaderForMatch = (s, i) => s.TrimStart().TrimEnd(),
HasHeaderRecord = true,
SanitizeForInjection = true,
MissingFieldFound = null
};
var manifestConfiguration = new ManifestConfiguration
{
Format = "{delimiter}{resource}{delimiter}{extension}",
Delimiter = ".",
Extension = "csv",
ExtensionFieldName = "{extension}",
ResourceFieldName = "{resource}",
DelimiterFieldName = "{delimiter}"
};
SeederConfiguration.ResetConfiguration(csvConfiguration, manifestConfiguration, assembly: typeof(Program).GetTypeInfo().Assembly);
using (var context = new ProductsDbContext())
{
//context.Products.SeedDbSetIfEmpty(nameof(context.Products));
context.Suppliers.SeedFromResource(nameof(context.Suppliers));
context.SaveChanges();
}
}
/*Query 2 - Successfully Sold Products*/
internal static void GetSuccessfullySoldProducts()
{
using (var context = new ProductsDbContext())
{
var result = context.Users
.Where(u => u.ProductsSold.Count > 0)
.Include(u => u.ProductsSold)
.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,
buyerLastName = ps.Buyer.LastName
})
})
.ToArray();
string path = "CompletedTasksJSONs/Q2.2_SuccessfullySoldProducts.json";
DeserializeResult(result, path);
}
}
public async Task GetProductsReturnsProductUsingInValidId()
{
var options = new DbContextOptionsBuilder <ProductsDbContext>()
.UseInMemoryDatabase(nameof(GetProductsReturnsProductUsingInValidId))
.Options;
var dbContext = new ProductsDbContext(options);
CreateProducts(dbContext);
var productprofile = new ProductProfile();
var configuration = new MapperConfiguration(cfg => cfg.AddProfile(productprofile));
var mapper = new Mapper(configuration);
var productsProvider = new ProductsProvider(dbContext, null, mapper);
var product = await productsProvider.GetProductByIdAsync(-1);
Assert.False(product.IsSuccess);
Assert.Null(product.Product);
Assert.NotNull(product.ErrorMessage);
}
public ProductsRepository(ProductsDbContext productsDbContext, ILogger <ProductsRepository> logger, IMapper mapper)
{
_productsDbContext = productsDbContext;
_logger = logger;
_mapper = mapper;
SeedData();
}
// This method gets called by the runtime. Use this method to configure the HTTP request pipeline.
public void Configure(IApplicationBuilder app, IWebHostEnvironment env, ProductsDbContext dbContext)
{
if (env.IsDevelopment())
{
app.UseDeveloperExceptionPage();
}
//Ensure that DB exists
dbContext.Database.EnsureCreated();
//Enable support for Graph QL playground at this endpoint and use the schema defined
app.UseGraphiQl("/graphql");
app.UseGraphQL <ISchema>();
//Below lines can be commented if REST support is not needed
app.UseHttpsRedirection();
app.UseRouting();
app.UseAuthorization();
app.UseEndpoints(endpoints =>
{
endpoints.MapControllers();
});
}
public ProductsProvider(ProductsDbContext dbContext, ILogger <ProductsProvider> logger, IMapper mapper)
{
this.dbContext = dbContext;
this.logger = logger;
this.mapper = mapper;
SeedData();
}
// This method gets called by the runtime. Use this method to configure the HTTP request pipeline.
public void Configure(IApplicationBuilder app, ProductsDbContext productsDbContext)
{
if (HostingEnvironment.IsDevelopment())
{
app.UseDeveloperExceptionPage();
}
app.UseMvc();
app.UseSwagger();
app.UseSwaggerUI(c =>
{
c.SwaggerEndpoint("/swagger/v1/swagger.json", "My API V1");
});
using (var serviceScope = app
.ApplicationServices
.GetService <IServiceScopeFactory>()
.CreateScope())
{
var context = serviceScope.ServiceProvider.GetRequiredService <ProductsDbContext>();
context.Database.EnsureCreated();
}
productsDbContext.Seed();
}
public void Handle(ProductsDbContext context, ProductDto dto)
{
var product = context.Products.Find(dto.Id);
if (product == null)
{
return;
}
if (dto.Qty > product.Qty)
{
_stockShortagePublisher.Publish(new StockShortageEvent
{
OrderId = dto.OrderId,
ProductId = product.Id
});
return;
}
product.Qty -= dto.Qty;
context.SaveChanges();
_stockConfirmedPublisher.Publish(new StockConfirmedEvent
{
OrderId = product.Id,
ProductId = dto.OrderId
});
}
public ProductsProvider(ProductsDbContext context, ILogger <ProductsProvider> logger, IMapper mapper)
{
this.DataContext = context;
Mapper = mapper;
this.Logger = logger;
SeedData();
}
public static void Initialize(ProductsDbContext context)
{
context.Database.EnsureCreated();
// Look for any flowers.
if (context.Products.Any())
{
return; // DB has been seeded
}
context.Products.AddRange(
new Product
{
Name = "telefon",
Description = "White, Yellow",
Category = "a",
Price = 5
},
new Product
{
Name = "telefon1",
Description = "White, Yellow",
Category = "a",
Price = 5
}
);
context.SaveChanges();
}
public static async Task <List <SelectListItem> > GenerateCategories(ProductsDbContext db, [Optional] Category category)
{
List <SelectListItem> data = new List <SelectListItem>();
if (category == null)
{
data = await db.Categories.Select(cat => new SelectListItem()
{
Value = cat.Id.ToString(),
Text = cat.Name
}).ToListAsync();
return(data);
}
data = await db.Categories.Select(cat => new SelectListItem()
{
Value = cat.Id.ToString(),
Text = cat.Name
}).ToListAsync();
SelectListItem currentCategory = new SelectListItem()
{
Value = category.Id.ToString(), Text = category.Name, Selected = true
};
data.Add(currentCategory);
return(data);
}
public static async Task <List <SelectListItem> > GenerateDropdownSupplaiers(ProductsDbContext db, [Optional] Supplier supplier)
{
List <SelectListItem> data = new List <SelectListItem>();
if (supplier == null)
{
data = await db.Suppliers.Select(sup => new SelectListItem()
{
Value = sup.Id.ToString(),
Text = sup.Name
}).ToListAsync();
return(data);
}
data = await db.Suppliers.Select(sup => new SelectListItem()
{
Value = sup.Id.ToString(),
Text = sup.Name
}).ToListAsync();
SelectListItem currentSupplier = new SelectListItem()
{
Value = supplier.Id.ToString(), Text = supplier.Name, Selected = true
};
data.Add(currentSupplier);
return(data);
}
public ProductService(ProductsDbContext dbContext, ILogger <ProductService> logger, IMapper mapper)
{
_dbContext = dbContext;
_logger = logger;
_mapper = mapper;
SeedData();
}
public ProductsServiceTest()
{
var contextOption = new DbContextOptionsBuilder <ProductsDbContext>()
.UseInMemoryDatabase(nameof(GetProductsReturnAllProductsAsync));
dbContext = new ProductsDbContext(contextOption.Options);
CreateProducts(dbContext);
}
public void CanDefineDateTimeAndTimestampWithIdentity()
{
ReInitDb();
if (Version < new Version(5, 6)) return;
using (var db = new ProductsDbContext())
{
//db.Database.CreateIfNotExists();
db.Database.Initialize(true);
Product product = new Product
{
//Omitting Identity Columns
DateTimeWithPrecision = DateTime.Now,
TimeStampWithPrecision = DateTime.Now
};
db.Products.Add(product);
db.SaveChanges();
var updateProduct = db.Products.First();
updateProduct.DateTimeWithPrecision = new DateTime(2012, 3, 18, 23, 9, 7, 6);
db.SaveChanges();
Assert.AreNotEqual(null, db.Products.First().Timestamp);
Assert.AreNotEqual(null, db.Products.First().DateCreated);
Assert.AreEqual(new DateTime(2012, 3, 18, 23, 9, 7, 6), db.Products.First().DateTimeWithPrecision);
Assert.AreEqual(1, db.Products.Count());
db.Database.Delete();
}
}
public void CanDefineDatesWithPrecisionFor56()
{
if (Version < new Version(5, 6)) return;
using (var db = new ProductsDbContext())
{
db.Database.CreateIfNotExists();
using (MySqlConnection conn = new MySqlConnection(db.Database.Connection.ConnectionString))
{
conn.Open();
MySqlCommand query = new MySqlCommand("Select Column_name, Is_Nullable, Data_Type, DateTime_Precision from information_schema.Columns where table_schema ='" + conn.Database + "' and table_name = 'Products' and column_name ='DateTimeWithPrecision'", conn);
query.Connection = conn;
MySqlDataReader reader = query.ExecuteReader();
while (reader.Read())
{
Assert.AreEqual("DateTimeWithPrecision", reader[0].ToString());
Assert.AreEqual("NO", reader[1].ToString());
Assert.AreEqual("datetime", reader[2].ToString());
Assert.AreEqual("3", reader[3].ToString());
}
reader.Close();
query = new MySqlCommand("Select Column_name, Is_Nullable, Data_Type, DateTime_Precision from information_schema.Columns where table_schema ='" + conn.Database + "' and table_name = 'Products' and column_name ='TimeStampWithPrecision'", conn);
query.Connection = conn;
reader = query.ExecuteReader();
while (reader.Read())
{
Assert.AreEqual("TimeStampWithPrecision", reader[0].ToString());
Assert.AreEqual("NO", reader[1].ToString());
Assert.AreEqual("timestamp", reader[2].ToString());
Assert.AreEqual("3", reader[3].ToString());
}
reader.Close();
}
db.Database.Delete();
}
}
