Search code examples
c#winformst-sqllinq-to-sql

Presenting multiple sql tables

Sorry if this question have been asked before but I can't find what I'm looking for anywhere.

Setup: I have a MS SQL database with 3 tables

Table - FoodInfo
Column - FoodId(PK), DanName

Table - CompName
Column - CmpId(PK), CmpName

Table - Nutrients(junction table)
Column - FoodId(FK), CmpId(fk), BestLoc

For every DanName row there is associated about 8 CmpName and for every CmpName there is associated 1 BestLoc

That leads to DanName getting repeated 8 times when I show the data in my winform.

The problem is, I want to present DanName in one line with the associated CmpName's and their values.

Something like: DanName - CmpName1 - BestLoc value - CmpName2 - BestLoc value - CmpName3 - BestLoc value.. and so on.

I am very flexible about the solution, but I'm not sure what path to follow. should I try to create a table in the DB which look as I want it to and put it in a DataGridView, or should I try to solve it with Linq-To-SQL and put it in a listview?

As said, I'm pretty flexible about the solution, I just don't want the data in at textbox. I would also like it to be possible to use the data I retrieve and use it elsewhere in my program.

If anyone have a solution I would love to see an example of it.

Edit: To be a little more detailed, and with some code samples.

My DAL class with linq

void bw_DoWork(object sender, DoWorkEventArgs e)
{
    Table<FoodInfo> FoodInfo = db.GetTable<FoodInfo>();
    Table<CompName> CompName = db.GetTable<CompName>();
    Table<Nutrient> Nutrients = db.GetTable<Nutrient>();

    var foods =
        from compname in CompName
        join nutrients in Nutrients on compname.CompId equals nutrients.CompId
        join foodinfo in FoodInfo on nutrients.FoodId equals foodinfo.FoodId
        where foodinfo.DanName.StartsWith(searchWord) && (compname.CompId >= 0
                                                      && compname.CompId < 8)
        select new { foodinfo.DanName, compname.CmpNamDK, nutrients.BestLoc };

    foreach (var food in foods)
    {
        DanName = food.DanName;
        Compname = food.CmpNamDK;
        BestLoc = food.BestLoc;

        OnSearchResultArgs OSR = new OnSearchResultArgs(DanName, Compname, BestLoc);
        onResult(this, OSR);
    }
}

which triggers an eventhandler in my form and send over the results and execute this code:

listView1.FullRowSelect = true;
listView1.Columns.Add("DanName", 100);
listView1.Columns.Add("CompName", 150);
listView1.Columns.Add("BestLoc", 50);

private void UpdateControls(object sender, OnSearchResultArgs e)
{
    var item = new ListViewItem();
    item.Text = e.DanName;
    item.SubItems.Add(e.CompName);
    item.SubItems.Add(e.BestLoc);
    listView1.Items.Add(item);
}

The UpdateControl is getting invoked, but I don't think that is necessary to put in here.

and by this I get the following:

I want to turn those 8 rows into 1 row. like:

Solution

  • Just an update if anyone runs into the same question as i had.

    First i tried the solution from @JonPayne, with the T-SQL.. it solved my problem, but worked very slow for me. Maby i was doing i wrong. i dont know.

    I ended up with making a pivot method from a guide i found here: http://www.codeproject.com/Articles/22008/C-Pivot-Table

    Very simple, I query my unordered data from the SQL-Server, put it in a datatable and format the datatable with the pivot method, that returns a ordered datatable.