XML Structure and Dataset Designer

[timh]

OK, so I'm struggling here!

I'm trying to create a dataset representation in Visual Basic 2010 Express of my XML schema. The problem I've got is understanding the relationships.

I want my XML to look like this...

<eventdata>
  <createdwith>schema.xsd</createdwith>
  <eventdetails>
    <eventname>some event</eventname>
    <eventdate>2012-05-03</eventdate>
  </eventdetails>
  <entrant>
    <surname>Bloggs</surname>
    <firstname>Joe</firstname>
    ..lots more simple elements..
  </entrant>
</eventdata>

So, I thought I would create 3 datatables, within a dataset called "edx"...

"eventdata" contains the "createdwith" column
"eventdetails" contains the "eventname" and "eventdate" columns
"entrant" contains the "surname", "firstname" etc columns

Trouble is, the XML that produces is...

<edx>
  <eventdata>
    <createdwith>schema.xsd</createdwith>
  </eventdata>
  <eventdetails>
    <eventname>some event</eventname>
    <eventdate>2012-05-03</eventdate>
  </eventdetails>
  <entrant>
    <surname>Bloggs</surname>
    <firstname>Joe</firstname>
    ..lots more simple elements..
  </entrant>
</edx>

...which is not what I want. I suppose it may not matter ultimately, but is it possible to achieve what I ideally want and if so, how?

I thought I could include columns named "eventdetails" and "entrant" within the "eventdata" datatable and somehow tell the designer that those two columns are actually the two nested datatables with the same name, but that doesn't seem to work, no matter what I do with the "relation".

I could rename the dataset "eventdata" which would get me the root element name I want, but then I don't see where the "createdwith" element goes in the dataset design? It needs a datatable to sit in? I suppose I could put it in the "eventdetails" element and be done with it...

Thanks,

Tim

 

[timh]

Ok, problem solved, or rather worked around. Just made a separate data table. After thinking about it, I decided there is no need for any relations between the tables, so my xml just contains 3 separate tables and does what I want.

 

[timh]

Hi,

I'm revisiting this thread with another query under the same heading...

As my application has developed, I've added a couple of new tables into the dataset, so instead of the original 3 tables, it now contains 5.

The original dataset contained the following tables, in the order listed:

1. info
2. eventdetails
3. entrant

The two tables I added are:

• eventcategories
• entryfees

When the dataset is saved as an XML file, the elements appear in the order above, whereas the order I want them in is:

1. info
2. eventdetails
3. eventcategories
4. entryfees
5. entrant

Functionally, it makes no odds what order the tables are in as there are no interdependencies, however I am after neatness in the XML and it makes sense to me that the multiple "entrant" elements should sit after all the other elements which are concerned with event admin. I thought re-positioning the tables in the dataset design view might do something, but it doesn't (I'm using Visual Studio 2012 Express).

I can't find any "index" property for the tables and the only way I can get the output I want is to programmatically copy each table to a temporary table, remove each table from the main dataset, then re-add them in the order I want them.

        'sequence to reorder tables in dataset
        Dim table(5) As DataTable
        With tempeventDS
            table(0) = .Tables("info")
            table(1) = .Tables("eventdetails")
            table(2) = .Tables("eventcategories")
            table(3) = .Tables("entryfees")
            table(4) = .Tables("entrant")
        End With

        With tempeventDS.Tables
            .Remove("info")
            .Remove("eventdetails")
            .Remove("eventcategories")
            .Remove("entryfees")
            .Remove("entrant")
            For i = 0 To 4
                .Add(table(i))
            Next
        End With
        '/reordering

It works, but it seems unnecessary program activity. The alternative would be to delete my dataset and start again from scratch, but I don't fancy doing that if there's an easier way! Plus, as the program continues to develop with use and new functionality is added, I will potentially need to add other tables. It would be nice to get this cracked now.

Thanks.

 

[IanRyder]

Hi,

I have had a look at this and I do not think there is a specific method / function that you can run to achieve this. One of the other forum members may know better than I do?

The best I can offer is a bit of a more streamline way of achieving what you are already doing. Have a look here:-

Public Class Form1
  Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
    Dim sqlConn As New SqlConnection("Data Source=IANVAIO\SQLEXPRESS;Initial Catalog=NORTHWIND;Integrated Security=True")
    Dim myDA As New SqlDataAdapter("Select * From Employees", sqlConn)
    Dim myDA2 As New SqlDataAdapter("Select * From Orders", sqlConn)
    Dim myDA3 As New SqlDataAdapter("Select * From Products", sqlConn)
    Dim myDS As New DataSet
 
    'This is the incorrect order of the tables in the dataset
    myDA.Fill(myDS, "Employees")
    myDA2.Fill(myDS, "Orders")
    myDA3.Fill(myDS, "Products")
 
    'create the datatable order that you want in the array and reassign it back to the dataset
    Dim myOrderedTables() As DataTable = {myDS.Tables(2), myDS.Tables(1), myDS.Tables(0)}
    With myDS
      .Tables.Clear()
      .Tables.AddRange(myOrderedTables)
      .WriteXml("d:\temp\EmpsXML.xml")
    End With
 
    MsgBox("Done!")
  End Sub
End Class

Hope that helps.

Cheers,

Ian