Creating Columns in GridView (C #, LINQ)
The situation is that I need to create a grid-like table that looks like this:
---------- | ID --- | --- Name-- | --1 / 2002-- | --2 / 2002-- | --1 / 2003-- | ........ | 2/2009 | Cust1-- |
Cust2-- |
:
:
I have two tables in the db - customers and orders through LINQ to SQL DataContext
ID and customer name, which I get from a simple query
var custInfo = from cust in db.Customers
select new { ID = cust.Id,
FullName = cust.FirstName + " " + cust.LastName }
dataGridOrdersPreview.DataSource = custInfo;
And I need a hint how to generate these columns in the t / year format, where t indicates the first or second half of the year, and assign these generated columns to the orders of each Customer in that session in the year (display only expenses)
[edit]
As far as I am currently trying to do something like this:
var orders = from ord in db.Orders
group ord by ord.Id_cust into grouped
let costs = grouped
.Where( s => s.YearSession == session && s.Year == year)
.Select(a => new { Costs = a.Cost ) } )
select new { ID = grouped.Key,
Name = custInfo
.Where( a => a.ID == grouped.Key)
.Select( j => j.Name).Single(),
Cost = ExtensionLibrary.Sum(costs, "\n")
};
(in a value receiving only the total costs this year for each client)
and then I think about iterating over years and sessions anyway the query result matches the corresponding columns
while (year <= DateTime.Today.Year)
{
year++;
while (session < 2)
{
session++;
dataGridOrdersPreview.Columns.Add(session +"/"+ year);
col.Add((session +"/"+ year),
orders.Select( a => a.Cost ).ToList() );
/* col is Dictionary<string, List<string> > */
}
session = 0;
}
Here I have created the columns I want and I have orders in a dictionary where Key is the name of the column and Value are the orders in that column, but I need some help binding it to those columns
source to share
The way I have seen this is to create the class you want, for example
class CustOrders
{
public string CustName {get; set;}
public int Orders2002-1 {get; set;}
public int Orders2002-2 {get; set;}
...
public int Orders2009-1 {get; set;}
}
Then use System.Windows.Forms.BindingSource, ask it to say CustOrdsBindingSource and set its DataSource to your new class list.
List<CustOrders> myListOfCustOrders = new List<CustOrders>();
/* Code to populate myListOfCustOrders */
CustOrdsBindingSource.DataSource = myListOfCustOrders;
In this case, you will have to write code to convert each result of your query results to a CustOrders instance and store it in myListOfCustOrders.
Finally, the grid view data source must also be specified:
gridView1.DataSource = CustOrdsBindingSource;
The big problem I see with this approach is that every year you will have to change the CustOrders class unless there is any voodoo that might suggest inserting properties into the class at runtime.
Either way, hopefully this gets you started.
source to share