Generating Columns in GridView ( C#, LINQ )

Question

The situation is, that i need to create table in grid view looking like this:

----------| ID---|---Name--|--1/2002--|--2/2002--|--1/2003--|........| 2/2009 |
Cust1--|
Cust2--|
:
:
I have two tables in db - Customers and orders, throught LINQ to SQL DataContext
ID and Name of the customers i´m getting 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 some clue, how to generate that columns in format t/year where t indicates the first or second half of the year, and assign to that generated columns each Customer´s orders in that session of the year ( displaying only costs )

[edit]

As far as now, i´m attempting to 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 Cost getting only the summed costs in that year session for each customer )

and then i think about iterating throuhgh the years and sessions and getting somehow the query results to 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 generated columns that i want and i have orders in Dictionary where Key is column name and Value are orders in that column, but i need some help binding it to that columns

Answer 1

The way I've seen it done is to create a class that has the properties that 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 the System.Windows.Forms.BindingSource, call it say CustOrdsBindingSource and set its DataSource to a list of your new class.

List<CustOrders> myListOfCustOrders =  new List<CustOrders>();
/* Code to populate myListOfCustOrders */
CustOrdsBindingSource.DataSource = myListOfCustOrders;

In this case you will have to write the code to convert each result of your query results to an instance of CustOrders and store it in myListOfCustOrders.

Finally, the grid view's data source will also have to be set:

gridView1.DataSource = CustOrdsBindingSource;

The big problem I see with this approach is that you will have to change the CustOrders class every year unless there is some voodoo some can suggest to insert properties into the class at run time.

Either way, I hope this gives you a start.

Answer 2

As long as there will be no updating/adding/deleting of rows, I think I would just generate that grid manually.

Fetch the list of customers and the count of how many sales in what year/session. And then in the form take that list and create the needed columns.