ADO.NET: How do I bind correctly an 1:n relation between two tables on a control

Question

0.00/5 (No votes)

See more:

Hi,

based on a small project I'm working myself into ADO.NET and SQLite.
My-used database system: SQLite via the SQLite provider

edit: further information
I use #develop and so i have no design-time support for this. It has to be done all in code.

It's an windows forms application.

I have linked a table "Games" and a table "genre".
Every game record can have a "genre" assigned.

I would like to output the data of table "games" and of course, instead of the "ID" of the table "genre" its "name" field, that is "Genre"."Name".

Problem
All my tries to implement it, didn't work and google or msdn couldn't help.

I proceed as follows

So I create the DataAdapter

private void create adapter ()
{
rating SQLiteDataAdapter adapter = new ();

/ / A table mapping names the DataTable.
RatingsAdapter.TableMappings.Add ("Table", rating table);
RatingsAdapter.TableMappings.Add ("Table1", table games);
RatingsAdapter.TableMappings.Add ("Table2", test table);
RatingsAdapter.TableMappings.Add ("Table3" genre table);

/ / Create the SelectCommand.
DbCommand SelectCommand = GRData.DataProvider.CreateCommand ();
SelectCommand.Connection = GRData.DataConnection;
SelectCommand.CommandText =
"SELECT * FROM" + table + rating "," +
"SELECT * FROM" + table + games "," +
"SELECT * FROM" + table + tester "," +
"SELECT * FROM" + table + genre ",";
RatingsAdapter.SelectCommand = SelectCommand;

/ / Creates all other commands
rating GRData.DataProvider.CreateCommandBuilder commands = ();
RatingsCommands.DataAdapter rating = adapter;
}

Here I fill the dataset and set the relations between the tables

private void select ratings ()
{
/ / Fill Dataset
RatingsAdapter.Fill (RatingsDataset);

DataRelation relation;

...

/ / Specifiy relation between games and Genres
DataColumn col_GenresID = RatingsDataset.Tables [genre table] Columns ["id"];.
DataColumn col_GamesGenreID = RatingsDataset.Tables [games table] Columns ["genre"],.
Relation = new DataRelation ("rel_GamesGenre" col_GenresID, col_GamesGenreID);
RatingsDataset.Relations.Add (relation);

...

}

Here is the binding to individual controls

Description: GenreText is a Windows.Forms.Label.

private void BindControls ()
{
...
GenreText.DataBindings.Add ("Text", RatingsDataset, String.Concat (games table, ". Rel_GamesGenre ";));
...
}

Error Message
System.ArgumentException: Can not bind to the property or column rel_GamesGenre on the data source.

What am I doing wrong?

Thanks and Greetings
Antonio

Posted 3-Jan-11 9:27am

Antonio Cambule

Updated 4-Jan-11 0:26am

v4

Add a Solution

2 solutions

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)

Espen Harlinn · Answer 1 · 2011-01-03T09:56:00

Solution 1

Here is a few Master/Detail tutorials[^]

Another, and IMHO better solution, is to forget about DataSet. Create your own business objects that works directly with the low level components of the provider (descendants of DbDataReader, DbCommand, and so on). Expose you details collections as System.ComponentModel.BindingList<T>.

Use BindingSource to set up bindings for your forms application.

This approach tends to work well with both Forms and asp.net

I hope this will prove helpfull.

Regards
Espen Harlinn

Posted 3-Jan-11 9:56am

Espen Harlinn

Updated 3-Jan-11 22:23pm

v2

Comments

Dalek Dave 3-Jan-11 18:58pm

Good link.

Antonio Cambule 4-Jan-11 1:43am

Thank you, but that is all about ASP.NET and I'm working on a Windows Forms Application with #Develop so i even haven't got a design-time support for database things. I've added this information also in my question to clarify.

Antonio Cambule 5-Jan-11 5:37am

That would be like having a car with an open window but you can not find the lifter, and instead of looking how to close the window, better construct your own car, there you know how to open and close the window. Don't understand me wrong, maybe that is an better approach but it is also very time consuming, reinventing the wheel and with a lot of rework and so on. I have found a solution, with just two lines of code. Take a look at the my answer.

Espen Harlinn 5-Jan-11 5:49am

It's great that you have found a solution. Good for you!

Antonio Cambule · Answer 2 · 2011-01-04T23:59:00

I know that there are a lot of possible solutions to set up a working mechanism to get working databases and windows forms and all the problems we find between it, like relations, bindings and so on.

I have chosen just one possiblity to learn a bit more how i can go on with c# in .Net and databases. As my project is a very small one i needed a very fast and easy possiblity, that is why i thought hey, try using standard ADO.NET with an simple database provider.

And i went fast and good with it, as there are many tutorials and samples out there. But when I came to the point of relations the only thing developer seem to do is to bind them with an dataGridView or dataView. The most use Visual Studio that makes it rather simple with its design-time GUI.

I'm working on an simple app without an Grid, but an TreeView and with detailed views, one to show, one to edit. So this detail views are populated with labels to show the data and textboxes to edit the data.

So, binding a simple column from a table in dataset to an textbox is easy and works fine. But what if the column is a relation to an other table, so you will find only the id in it, how do I get the name or an other field of the related table?

I have done the relation why doesn't it show up the field i like?

Here is the solution after a lot of days and nerves...and it is so simple that i can't understand to have found it in any other tutorial else where.

// Bind Genres to Games
MasterSource = new BindingSource(RatingsDataset, GenresTable);
DetailsSource = new BindingSource(MasterSource, "rel_GamesGenre");

Here I set an binding between the Games and Genres Data.
What happens is, when record of Games changes the Genres will point
to the record of the foreignKey relation that i set before.
Thas is all. The binding to a field of Genres for the label will look like this:

GenreText.DataBindings.Add("Text", RatingsDataset, GenresTable + ".name");

Without the BindingSource the Genres would point to the first record in the table.

Thanks to Espen who tried to help me out.

Regards