What is the purpose of a DataRelation in a DataSet?

The C # program I am working on will need to export data to one or more data tables. Some of the columns in these tables will be related to each other, so that the ID column of one table can refer to a cell in another table. This looks like a generic example for creating a DataRelation. But I am trying to understand how this relationship can be used later.

I have little experience with relational databases, but not much. I understand SQL queries and things like LEFT join, right join, etc. And I did some work in Access, creating queries both GUI and manually. But I have never created any links in the database itself. I tried this a minute ago in Access. After creating the relationship, I tried to add my two test tables to the query and the connection was automatically created.

However, when I actually looked at the actual SQL, it actually included the JOIN command that was obtained from the relationship. But it also means that the request was not relationship dependent at all. How would relationships be used in a database that doesn't have a drag-and-drop query editor like Access?

Is the only reason I want to create a relationship in a database or a DataRelation in a DataSet so that I can guess what kind of relationship should exist but not necessarily force it?

+2


source to share


4 answers


DataRelations are used for:

  • Ensure referential integrity (you cannot reference a row that does not exist in the parent table)
  • Go through the relationship, for example get the child rows or parent row of the relationship:

    DataRow[] orderRows = customerRow.GetChildRows("Customers_Orders");

  • Create calculated columns with expressions that refer to parent or child rows

    OrderTotalColumn.Expression = "Sum(Child(Orders_OrderDetails).Quantity * Child(Orders_OrderDetails).UnitPrice)";



DataRelations are also used in the Windows Forms binding system

+1


source


In fact, the opposite is true. Setting up relationships between tables helps to enforce them. If you set up a foreign key relationship and try to insert data into the table and don't include the foreign key, the database will stop you.

Many databases have cascading updates and are also deleted when setting up relationships. They will automatically update and / or delete child relationships when the parent is updated / deleted.

DataRelation works in a similar way to helping to enforce relationships at the code level ....

Here's a link to some of the basics of database design: http://www.sqlteam.com/article/database-design-and-modeling-fundamentals . # 4, 5, 6 and 7 talk about connections.



And here's another one for more information: http://www.deeptraining.com/litwin/dbdesign/FundamentalsOfRelationalDatabaseDesign.aspx

Here is a tutorial on using DataRelation to help with the parent / child relationship in code when using a DataSet:

http://www.dotnetjohn.com/articles.aspx?articleid=63

And here's MSDN for DataRelation: http://msdn.microsoft.com/en-us/library/system.data.datarelation(VS.80).aspx

0


source


Sometimes Access in the Query Designer will automatically combine fields from two tables if they have the same name (ID), even if you haven't created a relationship.

0


source


It seems to me that you are confusing referential integrity at the database engine level with a nice UI accessibility feature.

As others have explained, relationships are about data, not the pretty entity relationship diagram you get in the relationship designer and the ease of use in Access's query grid. The relationship limits the values ​​in a field in one table to values ​​taken from a column in another table.

The lines that you get in the query grid when you add two tables with RIs defined in between are the result of enough access available to create a default relationship for you.

The default access also guesses the relationship using "AutoJoin", a parameter that can be controlled in TOOLS | OPTIONS | TABLES / INQUIRIES. Here's what the explanation is:

Select to automatically create an inner join between the two added tables to the computational mesh. Clear if you like to define the relationships between the tables themselves. For AutoJoin, all tables must have a field with the same name and data type, and one of the join fields must be the primary key.

In this case, you can get autoconnect lines even if the relationship is not defined.

Also, note that if you add aliases to the saved QueryDef, you may lose both types of automatic joins. It looks to me like it was something that has changed in recent versions of Access, but I don't have time to check it out.

0


source







All Articles