Data.Relation and reflexive relation

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 
parentTreeStructID.
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?

tia,
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 :-) 

+M

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

Hi,

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()
        MyBase.New()

        'This call is required by the Windows Form 
Designer.
        InitializeComponent()

        'Add any initialization after the 
InitializeComponent() call

    End Sub

    'Form overrides dispose to clean up the component 
list.
    Protected Overloads Overrides Sub Dispose(ByVal 
disposing As Boolean)
        If disposing Then
            If Not (components Is Nothing) Then
                components.Dispose()
            End If
        End If
        MyBase.Dispose(disposing)
    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 
System.Windows.Forms.Button
    Friend WithEvents DataGrid1 As 
System.Windows.Forms.DataGrid
    <System.Diagnostics.DebuggerStepThrough()> Private 
Sub InitializeComponent()
        Me.Button1 = New System.Windows.Forms.Button
        Me.DataGrid1 = New System.Windows.Forms.DataGrid
        CType(Me.DataGrid1, 
System.ComponentModel.ISupportInitialize).BeginInit()
        Me.SuspendLayout()
        '
        'Button1
        '
        Me.Button1.Location = New System.Drawing.Point
(16, 16)
        Me.Button1.Name = "Button1"
        Me.Button1.TabIndex = 0
        Me.Button1.Text = "Button1"
        '
        'DataGrid1
        '
        Me.DataGrid1.DataMember = ""
        Me.DataGrid1.HeaderForeColor = 
System.Drawing.SystemColors.ControlText
        Me.DataGrid1.Location = New System.Drawing.Point
(24, 56)
        Me.DataGrid1.Name = "DataGrid1"
        Me.DataGrid1.Size = New System.Drawing.Size(680, 
240)
        Me.DataGrid1.TabIndex = 1
        '
        'Form1
        '
        Me.AutoScaleBaseSize = New System.Drawing.Size(5, 
13)
        Me.ClientSize = New System.Drawing.Size(712, 318)
        Me.Controls.Add(Me.DataGrid1)
        Me.Controls.Add(Me.Button1)
        Me.Name = "Form1"
        Me.Text = "Form1"
        CType(Me.DataGrid1, 
System.ComponentModel.ISupportInitialize).EndInit()
        Me.ResumeLayout(False)

    End Sub

#End Region

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

        myPmtTable.Columns.Add(AddColumn
("Pmt_id", "System.Int32", True))
        'Add table to dataset
        ds.Tables.Add(myPmtTable)

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

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

        'Add table to dataset
        ds.Tables.Add(myPmtLnDetTable)

        Dim myPayerTable As New DataTable("Payer")
        myPayerTable.Columns.Add(AddColumn
("Payer_id", "System.Int32", True))
        myPayerTable.Columns.Add(AddColumn
("Payer_name", "System.String", False))
        ds.Tables.Add(myPayerTable)
        'Create another dataset, just to see what happens
        Dim myDatasetWithRelations As DataSet = 
BuildPaymentDataRelations(ds)
        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
("pmt_id")
        childCol = dataset.Tables("PmtLn").Columns
("pmt_id")
        dataset.Relations.Add("Pmt_PmtLn", parentCol, 
childCol)

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

        parentCol = dataset.Tables("PmtLn").Columns
("pmtln_id")
        childCol = dataset.Tables("PmtLnDet").Columns
("pmtln_id")
        dataset.Relations.Add("PmtLn_PmtLnDet", 
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 ?

TIA

Mark

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