how do I show data from multiple tables in datagridview C# winforms

Question

4.00/5 (1 vote)

See more:

, +

Hi everyone,
I'm developing an automation of manual system for a fuel pump. Up till now I've accomplished my tasks but now I'm stuck on something related to retrieve data from multiple related tables into one data grid view. I've already 'Google' my problem but failed to find the right way to move on. Here is my scenario Explanation:

- I've table named 'Department' which have 'Vehicles' table; (1-*) relation.
- then 'Vehicles' table have 'Months' table to keep monthly billing details; (1-*) relation.
- and 'Months' table have tow tables 'Fuel_Bill' and 'Lubi_Bill' to keep the top level information of bills.(Both have 1-1 relation with 'Months' table).

I've build this using entity-framework designer first model on SQL LocalDB.

So, now I'm stuck on showing:
-Department's monthly abstract view which have the details related to 'Vehicles' and the 'Fuel _bill" details of selected 'Month' in one data grid view.

It's a long question but to be clear I give these details, Please help me out, my whole project will fail if I can't accomplish this.

Any help will be appreciated.
Thanks in advance.

I'm adding links to pictures for better understanding

Entity Framework Model
Entity Framework Model design

What I'm trying to achive
My Goal discription

Posted 9-Mar-14 12:26pm

paingiver

Updated 10-Mar-14 8:47am

v2

Add a Solution

3 solutions

Solution 1

Hi,

Now You have Table : Vehicles, Months, Fuel_Bill, Lubi_Bill

I have one best idea to do this.

Now these tables are related let me consider Vehicles Table have the Collection of various Vehicles. and each vehicle will have a unique id which is the primary key.
secondly ill give u an example

vehicle table--Columns: VehicleID, Name, Model, Year....etc
Months--- MonthID, Month int(10), year int (10)
fuel_bill-- FuelID, VehicleID, MOnthID, ....etc

now what you are going to do is..

SQL

select * from fuel_bill FB 
left outer join Vehicles V on V.VehicleID = FB.VehicleID
left outer join months m oh m.monthid = fb.monthid
--to filter specific month and year
where m.month = '1' and m.year = '2014'
--now ull get all the record for that specific period.

this is just an example modify it with ur database
Now get this datasource and bind it to the datagridview.

Happy Programming.

Posted 9-Mar-14 17:40pm

AndrewCharlz

Updated 9-Mar-14 17:41pm

v2

Comments

paingiver 10-Mar-14 14:27pm

thanks AndrewCharlz for your solution I'm going to implement it...

paingiver 10-Mar-14 15:11pm

I've tried your solution but didn't get it to work, please check my edited question. I'm Trying to work on events please give me example through c# coding thanks

Solution 2

The simple way is:

To create a view then Binding that to your gridview.

Also See:
How to use SQL Server Views with the Entity Framework[^]

Posted 9-Mar-14 19:27pm

Meysam Toluie

Updated 15-Mar-14 20:14pm

v2

Comments

paingiver 10-Mar-14 14:29pm

Thanks for your suggestion Meysam Tolouee, Can you please guide me on how to work with views using Entity-framework I've no idea of views

Meysam Toluie 16-Mar-14 2:14am

See the answer update please.

paingiver 17-Mar-14 13:38pm

Thanks Meysam Tolouee it helped me a lot thanks for you time

Meysam Toluie 18-Mar-14 3:32am

So are you going to accept it formally or not?

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

paingiver · Accepted Answer · 2014-03-10T12:53:00

C#

var viewModels = context.Months
    .Where(m => m.Month_Name == selectedMonthName &&
        m.Vehical.Dept_Id == selectedDepartmentId)
    .Select(m => new
    {
        Vehc_Number = m.Vehical.Vehc_Number,
        FB_Id = m.Fule_Bill.FB_Id,
        LB_GTAmount = m.Fule_Bill.LB_GTAmount,
        LB_GTQuantity = m.Fule_Bill.LB_GTQuantity
    }
    .ToList();

so here is the solution what I want thanks to "Slauma."