How do I display a relationship in a datagridview in C#?

Question

0.00/5 (No votes)

See more:

I have two tables Routes and RouteInvoices.
Routes has columns RouteId and RouteName.
RouteInvoices has RouteId and some other route invoice columns.
These two tables are related using RouteID as a foreign key in RouteInvoices.

What I have tried:

I use a datagridview to display RouteInvoices in my application

SQLiteDataAdapter adapter = new SQLiteDataAdapter("SELECT * FROM [route_invoices];", conn);
            DataTable table = new DataTable();
            adapter.Fill(table);

            dataGridView1.DataSource = table;

This shows routeID in the routeinvoices table but I want to display the route name.
How do I do this?

Posted 16-Jan-18 21:25pm

Member 13624040

Updated 17-Sep-20 8:41am

Add a Solution

2 solutions

Solution 2

Use a strongly typed dataset including both the Routes and RouteInvoices tables.
Define the relation between the Route and RouteInvoices tables. VisualStudio will probably call it FK_Routes_RouteInvoices.

I would include code for this but I always do it through the designer.

Then, you can add an expression column to the RouteInvoicesTable

routeDS.RouteInvoices.Columns.Add("RouteName", typeof(string), "Parent(FK_Routes_RouteInvoices).RouteName");

Now, if you load all your routes into the Routes table of the strongly typed DataSet and any invoices you are working with into the RouteInvoices table there will automatically be an 'extra' column in RouteInvoices named RouteName that you can display in the DataGrid and will automatically look up the route name for you when accessed.

I have seen solutions posted elsewhere that skip adding the new column and instead just place "Parent(FK_Routes_RouteInvoices).RouteName" as the DataPropertyName of a new column in the DataGridView. This has not worked when I have tried it however.

You can also leave out the "(FK_Routes_RouteInvoices)" part. That works so long as there is only one relation defined. If there are more than one you need to specify which relation.

Posted 17-Sep-20 8:41am

Leif Burrow

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)

Maciej Los · Accepted Answer · 2018-01-16T21:46:00

Solution 1

All what you need is to use proper query, such as:

SQL

SELECT R.*, RI.*
FROM routes AS R INNER JOIN [route_invoices] AS RI ON R.RouteID = RI.RouteID

Above query will return data from routes table and corresponding data from route_invoices table.

In other words, you have to improve your knowledge about SQL[^].

There's few types of joins. In case you may want to discover them, i'd suggest to read this: Visual Representation of SQL Joins[^]

Posted 16-Jan-18 21:46pm

Maciej Los

Comments

Leif Burrow 17-Sep-20 14:19pm

"...proper query..."?
"In other words, you have to improve your knowledge about SQL."?!?

Really?

How unnecessarily condescending while not even being the best solution!

The problem with this solution is that it flattens the data.
- If the dataset is very large then storing every column of the parent table over and over for every row of the child table is wasteful of memory. If the result set is being sent over a network, which it almost always is for a database query it is wasteful of bandwidth.
- Worse, if a record in the parent table is updated then the update must be propagated across all the children. That kind of duplication of data is where all sorts of nasty bugs come from.

Don't get me wrong, for small datasets that will be quickly thrown away using a join is a simple solution and might be acceptable. It's hardly the best solution for every situation and so certainly doesn't justify the assumption that the asker does not know SQL. Or the implication to everyone else that finds this in a search result that it is always the way to go.