Data.Relation and reflexive relation

dotnet framework


  • 1. BindingSource
    Hi Does BindingSource have any information about column which is being edited (i.e. editing editing data in grid)? Thx Shark
  • 2. Oracle with Enterprise Library
    Hello, I am new to Enterprise Library. I connected with my SQL database, its working fine. When I try to connect with oracle. I got some error like "ORA-01017 Invalid username/password logon denied; How to connect oracle using Enterprise Library. Any body can explain the procedure for this. Thanks in advance. Regards, Raj.
  • 3. Error Not Declaring Scalar Variable
    I get an error on the variable @ObjectName. Can anyone suggest what is wrong? _name = _lvi.Text; _type = "T"; _sql = "INSERT INTO QueryBuilderTableViews(ObjectName,ObjectType) VALUES " + " (@ObjectName, @ObjectType)"; cmd.CommandText = _sql; cmd.Parameters.AddWithValue("@ObjectName", _name); // error on this line. cmd.Parameters.AddWithValue("@ObjectType", _type); cmd.ExecuteNonQuery(); Thanks Bill
  • 4. What thing to Go in my scenario (Recordset/DataTable)?
    Hi SQL Gurus, I have a scenario like following: My DAL is sitting on a BOX with 4 GB RAM and calling the SQL Server BOX via Network (Internal). The records returned are huge in range of say 30000 to 300000. I am having 6 Columns on average in each row. I am generating an XML in a SQL Server 2005 Stored Procedure using 'For XML Path' the result of this Select is a Single row/line returning the entire XML. Now, given the above statistics, I am in doubt what specific feature of ADO.Net to go for: 1. DataTable: which will pull the entire ResultSet at one go (which is effective connection usage n network usage wise, but inefficient on Memory consumption part) OR 2. RecordSet: which will keep connection locked until the entire data is passed or RecordSet is closed. Given the number of records 30000 to 300000, this will again make that many network hops, but is much liter on the Memory Consumption side. I want my application to be Scalable and High Performing, what thing to go for in the scenario like above. I really appreciate any help on this. Thanks, Sachin R. Chavan.

Data.Relation and reflexive relation

Postby Yvonne.Lebhardt » Sat, 10 Sep 2005 21:24:04 GMT


I need to build a treeview in a winforms application. The datasource is
a dataset. I've the following tables

products(prodGUID, name)
categorys(catGUID, name, prodGUID, parentcatGUID)

So a tree might look like this:

       - product 1
                 - category 1
                 - category 2
                            - category 3
                 - category 4

To build a Tree with products and categorys is no problem. But how do I
handle the reflexive relation of the categorys (done with

If I set a reflexive Daterelation I get an error like "same table
cannot be a child-table in two cascaded realtions" (translated from

Do you have any example for me

Thanks Yvonne

Re: Data.Relation and reflexive relation

Postby Ray Booysen » Sat, 10 Sep 2005 21:41:53 GMT

Hi Yvonne

On a side note, shouldn't the products table have a foreign key to 
categorys not the categorys table having the prodGUID?


Re: Data.Relation and reflexive relation

Postby Sahil Malik [MVP] » Sat, 10 Sep 2005 21:50:29 GMT


Just as in a database, add one datarelation for the product->category 
relation. Then add another from category->category. The top parent category 
node FK must be null to enforce such a relationship. It should work then.

- Sahil Malik [MVP]
ADO.NET 2.0 book - 

Re: Data.Relation and reflexive relation

Postby Sahil Malik [MVP] » Sat, 10 Sep 2005 21:54:41 GMT

Good catch.

- Sahil Malik [MVP]
ADO.NET 2.0 book - 

Re: Data.Relation and reflexive relation

Postby Yvonne.Lebhardt » Tue, 13 Sep 2005 16:25:55 GMT

Hi Ray,

no, i don't talk about product-categories in the classic way.

The relation is product-category-question. Each product has serverall
categories with many questions in each category.


Re: Data.Relation and reflexive relation

Postby Cor Ligthert [MVP] » Tue, 13 Sep 2005 16:51:43 GMT


Do you mean that you need the childdatarows of the childdatarows in a 


I hope this helps,


Similar Threads:

1.reflexive relation in dataset

Hi all,

I've got the following problem:

I need to describe a tree structure, the table in the dataset consists 
of the columns structID, parentStructID, description with structID as 
primary key.
the parentStructID of a root treenode is DBNull.
I define a reflexive 1:n relationship from treestructID to 
loading the the database table into my dataset produces an error, 
because the DBNull-value of my root tree node violates my DataSet 
relation (but not the relation defined in my database, anyway).
common solution is: setting the nested-property of the DataRelation in 
my DataSet to true. that works fine, so far so good.
here's the heart of the problem:
setting the nested property to true in VS2003 dataset desginer lets the 
code generator forget my relation. that means if I instanciate my 
dataset the relation collection remains empty.
if I set the nested property to default or false the relation will be 
generated and I have access from code.
accessing and setting the nested property of my DataRelation to true 
produces the result I need and everything works fine.
so what's wrong? the designer's handling of relationships or my 
understanding of the designer? or do I miss to set a further property?

Thorsten Kallweit

2.EF: selecting all the relations of a relation

Hi, I have been using EF since it's release, for the most part, it's saved me
thousands of lines of code. (This is a repost at the request of MS)

I've a scenario I havn't been able to solve with well performing code.

Imagine a model of a phone book, with Companies, Departments and People. A
company has an ID and name, department has an ID and name, and a person has
an ID and name (keeping it simple).

How can I write an entities statement which will return a collections of all
people under a company (regardless of department)?

I considered using something like this:

context.People.Where(p=>p.Department.Company.CompanyId == CompanyIdSearched)

However, my understanding is this goes back to the database to pull the data?

I need to query based on the in-memory data, so I can include data which has
changed but not yet been saved (eg. to check if I've already added a Person
in an import).

What's the most efficient way to query the in-memory context, to get all
People who work for a Company?

Thanks in advance :-) 


3.Adding new records when databinding and using data relation win forms / data form wizard


I have a dataset with two tables and a relation between them.
I have controls (textboxes, check boxes etc) on a windows form bound to
those data tables. Editing existing data and navigating
between records works fine. However when I try to add a new record, only the
master table is updated to the dataset (and eventually to the database).

I struggled with this problem for a while and then decided to check what
VS.NET data form wizard would generate in a similar situation.

So I did the following actions:
1) added dataform to windows app .Net project
2) connected to Northwind database (SQL server)
3) created a new dataset
4) selected Orders and Order Details tables
5) created a datarelation between Orders and Order Details -tables ( Orders
being the parent and Order Details the child).
6) chose all columns to be displayed from both tables
7) selected 'single record in individual controls'

The generated form works fine when editing existing data or navigating.
However when trying to add a new record,
the master portion of the order (ie. individual text boxes) has to be saved
prior to saving any Order Details rows. If the master portion isn't
saved,  "ForeignKeyConstraint 'x' requires the child key values (eg. 11088)
to exist in the parent table. Do you want to correct the value?" message
is displayed. ['x'= name of the datarelation in step 5]. So it's not
possible to fill in all the necessary fields and then just hit 'update' to
save the new order.
Interestingly the OrderID is populated correctly in the datagrid with or
without saving the master portion?!

So how should the wizard generated code be amended to allow users to save an
order by hitting update button just once?

Any thoughts and/or pointers would be appreciated.

Thanking in advance!

4.Need help with data relations

I need to establish a relationship that looks like this:

One Payment can have many PaymentLine
One PaymentLine can have one Payer
One PaymentLine can have many PaymentLine Details

The relationships that I create between Payment and 
PaymentLine work, and the one between PaymentLine and 
PaymentLine detail work.  Whenever, I bind the dataset to 
a grid, it will never pick up the auto-identity for a 
payment and payer on a payment line.  How can I make this 
work?  Any help or insight would greatly be appreciated.

Here is some of the code that I built into a winform 
testing application:

Imports System.Data.SqlClient

Public Class Form1
    Inherits System.Windows.Forms.Form

#Region " Windows Form Designer generated code "

    Public Sub New()

        'This call is required by the Windows Form 

        'Add any initialization after the 
InitializeComponent() call

    End Sub

    'Form overrides dispose to clean up the component 
    Protected Overloads Overrides Sub Dispose(ByVal 
disposing As Boolean)
        If disposing Then
            If Not (components Is Nothing) Then
            End If
        End If
    End Sub

    'Required by the Windows Form Designer
    Private components As System.ComponentModel.IContainer

    'NOTE: The following procedure is required by the 
Windows Form Designer
    'It can be modified using the Windows Form Designer.  
    'Do not modify it using the code editor.
    Friend WithEvents Button1 As 
    Friend WithEvents DataGrid1 As 
    <System.Diagnostics.DebuggerStepThrough()> Private 
Sub InitializeComponent()
        Me.Button1 = New System.Windows.Forms.Button
        Me.DataGrid1 = New System.Windows.Forms.DataGrid
        Me.Button1.Location = New System.Drawing.Point
(16, 16)
        Me.Button1.Name = "Button1"
        Me.Button1.TabIndex = 0
        Me.Button1.Text = "Button1"
        Me.DataGrid1.DataMember = ""
        Me.DataGrid1.HeaderForeColor = 
        Me.DataGrid1.Location = New System.Drawing.Point
(24, 56)
        Me.DataGrid1.Name = "DataGrid1"
        Me.DataGrid1.Size = New System.Drawing.Size(680, 
        Me.DataGrid1.TabIndex = 1
        Me.AutoScaleBaseSize = New System.Drawing.Size(5, 
        Me.ClientSize = New System.Drawing.Size(712, 318)
        Me.Name = "Form1"
        Me.Text = "Form1"

    End Sub

#End Region

    Private Sub Button1_Click(ByVal sender As 
System.Object, ByVal e As System.EventArgs) Handles 
        Dim myPmtTable As New DataTable("Pmt")
        Dim myColumn As New DataColumn
        Dim ds As New DataSet

("Pmt_id", "System.Int32", True))
        'Add table to dataset

        Dim myPmtLnTable As New DataTable("PmtLn")
("PmtLn_id", "System.Int32", True))
("Pmt_id", "System.Int32", False))
("Payer_id", "System.Int32", False))
        'Add table to dataset

        Dim myPmtLnDetTable As New DataTable("PmtLnDet")
("PmtLnDet_id", "System.Int32", True))
("PmtLn_id", "System.Int32", False))
("PmtLnDet_amount", "System.Double", False))

        'Add table to dataset

        Dim myPayerTable As New DataTable("Payer")
("Payer_id", "System.Int32", True))
("Payer_name", "System.String", False))
        'Create another dataset, just to see what happens
        Dim myDatasetWithRelations As DataSet = 
        DataGrid1.DataSource = myDatasetWithRelations
    End Sub
    Public Function AddColumn(ByVal ColumnName As String, 
ByVal DataType As String, ByVal autoIncrement As Boolean) 
As DataColumn
        Dim myColumn As New DataColumn
        myColumn.ColumnName = ColumnName
        myColumn.DataType = Type.GetType(DataType)
        myColumn.AutoIncrement = autoIncrement
        If autoIncrement Then
            myColumn.AutoIncrementSeed = -1
        End If
        Return myColumn
    End Function

    Private Function BuildPaymentDataRelations(ByVal 
dataset As DataSet) As DataSet
        'blnRelationsCreated = True
        Dim parentCol, childCol As DataColumn

        parentCol = dataset.Tables("Pmt").Columns
        childCol = dataset.Tables("PmtLn").Columns
        dataset.Relations.Add("Pmt_PmtLn", parentCol, 

        parentCol = dataset.Tables("Payer").Columns
        childCol = dataset.Tables("PmtLn").Columns
        dataset.Relations.Add("Payer_PmtLn", parentCol, 

        parentCol = dataset.Tables("PmtLn").Columns
        childCol = dataset.Tables("PmtLnDet").Columns
parentCol, childCol)

        Return dataset
    End Function
End Class

5.Slef Referencing Data Relation Problem..

I've got a strongly typed dataset, one of the tables in the dataset has a
UID column ( integer, primary key ) and a TIER_ID column ( integer,
nullable ). TIER_ID references UID, but when I add a data relation to do
this the fill method hangs..
The top level record has a TIER_ID of NULL it seems that this is causing the
problem, if I update TIER_ID on this row so its not null it solves the
problem, but causes alot of junk in the dataset as somewhere along the line
you have to have a row with the same UID and TIER_ID, which means you end up
withodd rows in the dataset.

Has anyone ever come across this ? is there a solution ?



6. Add child rows to a datatable that have a data relation

7. filtering with data relations

8. HowTo: Updating Data in tables with an 1:n Relation

Return to dotnet framework


Who is online

Users browsing this forum: No registered users and 60 guest