Load relation to typed dataset from database  
Author Message
frank chen





PostPosted: .NET Framework Data Access and Storage, Load relation to typed dataset from database Top

Hi,

I create a typed dataset which has 2 tables Order and OrderDetail, I also build relation between Order and OrderDetail as nest which means one order row include multiple orderdetail rows. But when I load data from database to this typed dataset, it looks like Fill() method cannot load data like what I define in typed dataset. When I drill down order row, it doesn't include orderdetail rows. I list typed dataset xsd and the code I load from database.

< xml version="1.0" encoding="utf-8" >
<xs:schema id="TestDS" targetNamespace=" http://www.hide-link.com/ " elementFormDefault="qualified"
attributeFormDefault="qualified" xmlns=" http://www.hide-link.com/ " xmlns:mstns=" http://www.hide-link.com/ "
xmlns:xs=" http://www.hide-link.com/ " xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
<xs:element name="TestDS" msdata:IsDataSet="true">
<xs:complexType>
<xs:choice maxOccurs="unbounded">
<xs:element name="Order" >
<xs:complexType>
<xs:sequence>
<xs:element name="OrderId" type="xs:int" minOccurs="0" />
<xs:element name="OrderName" type="xs:string" minOccurs="0" />
<xs:element name="OrderDetail">
<xs:complexType>
<xs:sequence>
<xs:element name="OrderDetailId" type="xs:int" minOccurs="0" />
<xs:element name="OrderDetailName" type="xs:string" minOccurs="0" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:choice>
</xs:complexType>
<xs:key name="TestDS_Order_Key">
<xs:selector xpath=".//mstns:Order" />
<xs:field xpath="mstns:OrderId" />
</xs:key>
<xs:key name="TestDS_OrderDetail_Key">
<xs:selector xpath=".//mstns:OrderDetail" />
<xs:field xpath="mstns:OrderDetailId" />
</xs:key>
</xs:element>
</xs:schema>

Dim dsn As String = "data source=(local);initial catalog=TestDB;User id=sa;password=1234"
Dim conn As SqlConnection
Dim da As SqlDataAdapter
Dim ds As New TestDS 'DataSet
Dim sql As String
Try
conn = New SqlConnection(dsn)

sql = "select * from [order]"
da = New SqlDataAdapter(sql, conn)
da.Fill(ds, "order")

sql = "select * from orderdetail"
da = New SqlDataAdapter(sql, conn)
da.Fill(ds, "orderdetail")

Catch ex As Exception
MessageBox.Show(ex.Message)
Finally
If Not conn Is Nothing Then
conn.Close()
End If
End Try



.NET Development32  
 
 
Luis D. Rojas





PostPosted: .NET Framework Data Access and Storage, Load relation to typed dataset from database Top

Hi Frank,

I think you should create your typed datasets visually from the Designer. Just select Data->Add new Datasource, and you typed dataset will be created and will have all the code necesary for filling and updating your data in the database and in the typed dataset.

best regards



 
 
frank chen





PostPosted: .NET Framework Data Access and Storage, Load relation to typed dataset from database Top

Hi,

Thanks for your notes.

You are talking about vs.net 2005, right I am sorry for not mentioning I am using vs.net 2003 still. Any idea about vs.net 2003

Thanks


 
 
Luis D. Rojas





PostPosted: .NET Framework Data Access and Storage, Load relation to typed dataset from database Top

Hi,

you can do this in VS2003 too

check this out this link for doing that.

Best Regards



 
 
frank chen





PostPosted: .NET Framework Data Access and Storage, Load relation to typed dataset from database Top

Thanks for your note,

What I mentioned is after I define nested dataset, I want data load from database is automatically has parent-child relation. I can create new datatable, and load data from database, but if I have 2 datatable, and both of them has relation to refer each other. For example, I have Order and orderdetail datatable in typed dataset, I can load data from Order table to Order datatable, and OrderDetail table to OderDetail datatable, but what I want is to get real data from ds.OrderDetail.Item(0).OrderRow property after I load. The reality is ds.OrderDetail.Item(0).OrderRow is null after I load.