I made som updates to the form code to emphesize how to make selections with LINQ (this is NOT SQL, even if the syntax is similar).
I added some LINQ code to filter, I I put that code in the transfer of data fråm the "database"-objects to the "report"-objects.
I added the method
LoadDataFromDatabase()
that simulates the database request. But i real code you should really make the major selection by having a very tight SQL query. The information from the database that you dont need, should NEVER be fetched at all.
This is not optimized at all, but only a way of showing how some of the functionality works with LINQ and how to build a Pivot table out of live data, in a relatively simple way.
all data is also hard coded, but this is only to make this sample code simple to understand, for a novice developer. In real life you whould NEVER write code this way.
make sure you learn how to use Entity framework, for accessing the database, and retriving data from it. Entity Framework utilizes LINQ.
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Windows.Forms;
namespace SalesReportApp
{
public partial class Form1 : Form
{
private List<Product> databaseProducts;
private List<ReportPeriod> databaseReportPeriods;
private List<SalesReport> databaseSalesReports;
private List<Product> products;
private List<ReportPeriod> reportPeriods;
private List<SalesReport> salesReports;
private List<ReportDataRow> reportdataSource;
public Form1()
{
InitializeComponent();
}
private void DefaultInitializers()
{
databaseProducts = new List<Product>();
databaseReportPeriods = new List<ReportPeriod>();
databaseSalesReports = new List<SalesReport>();
products = new List<Product>();
reportPeriods = new List<ReportPeriod>();
salesReports = new List<SalesReport>();
reportdataSource = new List<ReportDataRow>();
}
private void LoadDataFromDatabase()
{
databaseProducts.Clear();
databaseProducts.Add(new Product(productName: "Item1"));
databaseProducts.Add(new Product(productName: "Item2"));
databaseProducts.Add(new Product(productName: "Item3"));
databaseProducts.Add(new Product(productName: "Item4"));
databaseReportPeriods.Clear();
databaseReportPeriods.Add(new ReportPeriod(Month_field: "Jul 17", from: "2017-07-01", to: "2017-07-31"));
databaseReportPeriods.Add(new ReportPeriod(Month_field: "Aug 17", from: "2017-08-01", to: "2017-08-31"));
databaseReportPeriods.Add(new ReportPeriod(Month_field: "Sep 17", from: "2017-09-01", to: "2017-09-30"));
databaseReportPeriods.Add(new ReportPeriod(Month_field: "Oct 17", from: "2017-10-01", to: "2017-10-31"));
databaseReportPeriods.Add(new ReportPeriod(Month_field: "Nov 17", from: "2017-11-01", to: "2017-11-30"));
databaseReportPeriods.Add(new ReportPeriod(Month_field: "Dec 17", from: "2017-12-01", to: "2017-12-31"));
databaseReportPeriods.Add(new ReportPeriod(Month_field: "Jan 18", from: "2018-01-01", to: "2018-01-31"));
databaseReportPeriods.Add(new ReportPeriod(Month_field: "Feb 18", from: "2018-02-01", to: "2018-02-28"));
databaseSalesReports.Clear();
databaseSalesReports.Add(new SalesReport(Item: "Item1", Month_field: "Jul 17", SalesCount: 5));
databaseSalesReports.Add(new SalesReport(Item: "Item2", Month_field: "Jul 17", SalesCount: 6));
databaseSalesReports.Add(new SalesReport(Item: "Item3", Month_field: "Jul 17", SalesCount: 9));
databaseSalesReports.Add(new SalesReport(Item: "Item4", Month_field: "Jul 17", SalesCount: 12));
databaseSalesReports.Add(new SalesReport(Item: "Item1", Month_field: "Aug 17", SalesCount: 3));
databaseSalesReports.Add(new SalesReport(Item: "Item2", Month_field: "Aug 17", SalesCount: 2));
databaseSalesReports.Add(new SalesReport(Item: "Item3", Month_field: "Aug 17", SalesCount: 13));
databaseSalesReports.Add(new SalesReport(Item: "Item4", Month_field: "Aug 17", SalesCount: 14));
databaseSalesReports.Add(new SalesReport(Item: "Item1", Month_field: "Sep 17", SalesCount: 6));
databaseSalesReports.Add(new SalesReport(Item: "Item2", Month_field: "Sep 17", SalesCount: 7));
databaseSalesReports.Add(new SalesReport(Item: "Item3", Month_field: "Sep 17", SalesCount: 15));
databaseSalesReports.Add(new SalesReport(Item: "Item4", Month_field: "Sep 17", SalesCount: 16));
databaseSalesReports.Add(new SalesReport(Item: "Item1", Month_field: "Oct 17", SalesCount: 3));
databaseSalesReports.Add(new SalesReport(Item: "Item2", Month_field: "Oct 17", SalesCount: 2));
databaseSalesReports.Add(new SalesReport(Item: "Item3", Month_field: "Oct 17", SalesCount: 17));
databaseSalesReports.Add(new SalesReport(Item: "Item4", Month_field: "Oct 17", SalesCount: 18));
databaseSalesReports.Add(new SalesReport(Item: "Item1", Month_field: "Nov 17", SalesCount: 5));
databaseSalesReports.Add(new SalesReport(Item: "Item2", Month_field: "Nov 17", SalesCount: 6));
databaseSalesReports.Add(new SalesReport(Item: "Item1", Month_field: "Dec 17", SalesCount: 3));
databaseSalesReports.Add(new SalesReport(Item: "Item2", Month_field: "Dec 17", SalesCount: 2));
databaseSalesReports.Add(new SalesReport(Item: "Item1", Month_field: "Jan 18", SalesCount: 6));
databaseSalesReports.Add(new SalesReport(Item: "Item2", Month_field: "Jan 18", SalesCount: 7));
databaseSalesReports.Add(new SalesReport(Item: "Item1", Month_field: "Feb 18", SalesCount: 3));
databaseSalesReports.Add(new SalesReport(Item: "Item2", Month_field: "Feb 18", SalesCount: 2));
}
private void Form1_Load(object sender, EventArgs e)
{
DefaultInitializers();
LoadDataFromDatabase();
products.Clear();
products.AddRange(databaseProducts);
reportPeriods.Clear();
reportPeriods.AddRange(databaseReportPeriods.Where(p => p.from>=DateTime.Parse("2017-11-01") && p.to <= DateTime.Parse("2018-02-28") ));
salesReports.Clear();
foreach (var period in reportPeriods)
{
var sr3 = databaseSalesReports.Where(r => r.Month_field == period.Month_field);
foreach (SalesReport sr in sr3)
{
salesReports.Add(sr);
}
}
reportdataSource.Clear();
foreach (Product prod in products)
{
List<SalesReport> salesReportSubList = new List<SalesReport>();
foreach (var salesItem in salesReports.Where(i => i.Item == prod.productName))
{
salesReportSubList.Add((SalesReport)salesItem);
}
ReportDataRow reportDataRow = new ReportDataRow();
for (int columnIndex = 0; columnIndex < reportPeriods.Count; columnIndex++)
{
var sr = salesReportSubList.SingleOrDefault(r => r.Item == prod.productName && r.Month_field == reportPeriods[columnIndex].Month_field);
if (sr != null)
{
if (columnIndex == 0) reportDataRow.Month1 = sr.SalesCount;
if (columnIndex == 1) reportDataRow.Month2 = sr.SalesCount;
if (columnIndex == 2) reportDataRow.Month3 = sr.SalesCount;
if (columnIndex == 3) reportDataRow.Month4 = sr.SalesCount;
}
}
reportDataRow.Product = prod.productName;
reportdataSource.Add(reportDataRow);
}
dataGridView1.DataSource = reportdataSource;
for (int columnIndex=0; columnIndex< reportPeriods.Count; columnIndex++)
{
dataGridView1.Columns[columnIndex+1].HeaderText = reportPeriods[columnIndex].Month_field;
}
}
}
}