Custom datatable output

Question

I am quite new to windows forms(.net 3.5) and are trying to present some bound data in a good way.

I have a table that looks like this:

date        -      unit     -      message
2012-01-01         200             some text
2012-01-01         300             some text
2012-01-01         400             some text
2012-01-02         100             some text
2012-01-02         300             some text
2012-01-02         700             some text

I would like to output this to:

units       -    2012-01-01 - 2012-01-02   - count
200              some text                   1
100                           some text      1
300              some text    some text      2

and so on...is this possible? And where should I start? I not aware of all functions and controls available and maybe you guys have some clever ideas.

Thanks in advance!

Code:

public DataTable ReadExcel()
{
        String excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=test.xlsx;Extended Properties=\"Excel 12.0;IMEX=1;HDR=YES;TypeGuessRows=0;ImportMixedTypes=Text\"";
        OleDbConnection excelCon = new OleDbConnection(excelConnectionString);
        OleDbCommand excelSelectCmd = new OleDbCommand("SELECT * FROM [stock$]", excelCon);
        OleDbDataAdapter excelAdapter = new OleDbDataAdapter();
        excelAdapter.SelectCommand = excelSelectCmd;
        DataSet excelDS = new DataSet();
        DataTable dt = new DataTable();
        excelAdapter.Fill(excelDS);
        excelCon.Close();

         return dt;
}

So basically i am only populating a datatable from an excel file and returning it, then i am binding it to a DataGridView, the data is shown as expected in the grid.

Now I am just looking for a way to present the data in another way.

        DBLayer test = new DBLayer();
        dataGridView1.DataSource = test.ReadExcel();

Answer 1

Hope it will help.

EDIT5 I have added the binding to dataGridView

public static class GroupedDtoWorker
{
    public static List<GroupedDto> GetIt()
    {
        DataTable dataTable = new DataTable();
        dataTable.Columns.Add("date").DataType = typeof(DateTime);
        dataTable.Columns.Add("unit").DataType = typeof(Int32);
        dataTable.Columns.Add("message").DataType = typeof(String);
        dataTable.Rows.Add("27.5.1989", "200", "someText");
        dataTable.Rows.Add("27.6.1989", "300", "someText");
        dataTable.Rows.Add("27.7.1989", "400", "someText");
        dataTable.Rows.Add("27.8.1989", "100", "someText");
        dataTable.Rows.Add("27.9.1989", "300", "someText");
        dataTable.Rows.Add("27.10.1989", "700", "someText");

        var result = (from rows in dataTable.AsEnumerable()
                      select new Dto
                      {
                          DateTime = rows.Field<DateTime>("date"),
                          Unit = rows.Field<Int32>("unit"),
                          Message = rows.Field<String>("message")

                      }).GroupBy(e => e.Unit)
                            .Select(
                                e =>
                                new GroupedDto()
                                {
                                    DateMessage = GetDictionary(e),
                                    Unit = e.AsQueryable().First().Unit,
                                    Count = e.Count()
                                }).ToList();
        return result;
    }

    public static Dictionary<String, String> GetDictionary(IGrouping<int, Dto> input)
    {
        Dictionary<String, String> result = new Dictionary<string, string>();
        input.ToList().ForEach(e => result.Add(e.DateTime.Date.ToString("yyyy/MM/dd"), e.Message));
        return result;
    }

}

public class GroupedDto
{
    public Int32 Unit { get; set; }
    public Dictionary<String, String> DateMessage { get; set; }
    public Int32 Count { get; set; }
}

public class Dto
{
    public DateTime DateTime { get; set; }
    public Int32 Unit { get; set; }
    public String Message { get; set; }
}

And the DataGridView bindings:

    public Form1()
    {
        InitializeComponent();
        var dto = GroupedDtoWorker.GetIt();
        dataGridView.Columns.Add("units", "units");
        foreach (GroupedDto groupedDto in dto)
        {
            foreach (KeyValuePair<string, string> keyValuePair in groupedDto.DateMessage)
            {
                if (!dataGridView.Columns.Contains(keyValuePair.Key))
                {
                    dataGridView.Columns.Add(keyValuePair.Key, keyValuePair.Key);
                }
            }
        }
        dataGridView.Columns.Add("count", "count");

        foreach (GroupedDto groupedDto in dto)
        {
            String[] row = new string[dataGridView.Columns.Count];
            row[0] = groupedDto.Unit.ToString();
            row[dataGridView.Columns.Count - 1] = groupedDto.Count.ToString();
            foreach (KeyValuePair<string, string> keyValuePair in groupedDto.DateMessage)
            {
                Int32 index = dataGridView.Columns[keyValuePair.Key].Index;
                row[index] = keyValuePair.Value;
            }
            dataGridView.Rows.Add(row);
        }
    }

Answer 2

If you wish to query a Datatable with LINQ then you will be best doing the Following :-

public class Dto
{
    public DateTime dateTime { get; set; }
    public Int32 Unit { get; set; }
    public String Message { get; set; }
}


public List<Dto> GetEnumerableObject()
{

       //Populate your Datatable from Excel Spreadsheet here as your already doing


       Var Result = (from rows in DataTable.AsEnumerable()

                     select new Dto
                     {
                        dateTime = rows.Field<DateTime>("date"),
                        Unit = rows.Field<Int32>("unit"),
                        Message = rows.Field<string>("message")

                     }).ToList();

       return Result;

}

In your maincode you can do something like this :-

List<Dto> myList = GetEnumerableObject(); 

Then you can learn the pleasures of LINQ.