This is my first attempt to re-write an old VB6 application that exported a
CSV file to a .NET application that exports an XML file with the equivelent
data. I have limited understanding of OO programming concepts (am trying to
learn) and XML serialization is all new ground for me. Anyway, here is what
I am trying to accomplish:
Goal
----
We sponsor competitions in ehich various teams compete. An "event" is made
up of a set of "shows". Each show uses a specific set of judges and may be
restricted to specific classes of competititors. Each show has several teams
competing. The objective of my XML code is to generate an output for a
selected event is something like:
<event name:Dublin Invitational>
<show>
<name>Juniors </name>
<team>
<teamname>Red Hats</teamname>
<class>JR</class>
<perftime>11:00 </perftime>
</team>
<team>
<teamname>Artur is</teamname>
<class>JR</class>
<perftime>11:10 </perftime>
</team>
</show>
<show>
<name>AA Prelims</name>
<team>
<teamname>Ace </teamname>
<class>AA</class>
<perftime>1:3 0</perftime>
</team>
<team>
<teamname>Fireb irds</teamname>
<class>JR</class>
<perftime>1:4 5</perftime>
</team>
</show>
</event>
Environment
-----------
The data is stored in two tables in an SQL Server 2000 database (see
structure below). The Shows table lists the name of the show, the judges,
etc for each event/show. The PerfSched table has an entry for each team
entered in a particular event/show. The tables are linked via the event and
show(seqnbr) fields.
My code is VB using VSNET 2003 IDE.
Attemped Approach (not working)
-----------------
I found several XML serialization examples in the on-line help and I am
trying to adapt an Order Entry example to my problem (the main parts of my
code are below). I open two SQL Data Readers. The first one, rdr1, loops
through the rows from the Shows table that match the event being processed,
adds that data to the event output ("evnt"), and then a second reader
("rdr2") is used to loop through all the entries in the PerfSched table for
that event/show and those are added to the event.
The problem is that if I execute the serializer at the point shown as "<=
Try 1" the resulting XML only has the data from the last show and its
participants. If I execute the serializer at the "<= Try 2" point, I get all
shows with all the participants for eah but I get the root element before
each set of show data.
How can I get just one instance of the root and all the show/participant
sets?
Thanks for any suggestions.
=============== == Condensed Code =============== =====
Private Sub CreateXML(ByVal filename As String)
Dim serializer As New XmlSerializer(G etType(Event))
Dim writer As New StreamWriter(fi lename)
Dim evnt As New Event
' Get the list of shows for the selected event
strSQL = "Select * From Shows Where ID = '" & strSelectedID & "'"
Dim cmd1 As New SqlCommand(strS QL, cn)
Dim rdr1 As SqlDataReader
rdr1 = cmd1.ExecuteRea der()
.......
Do While rdr1.Read
' Build the show base data
Dim currshow As New ShowBase
currshow.ShowNa me = rdr1("ShowName" )
currshow.ShowDa te = rdr1("ShowDate" )
currshow.SeqNbr = rdr1("SeqNbr")
...........
evnt.ShowBase = currshow
' Get all the entries for this show
strSQL = "Select * FROM PerfSched Where ID = '" & strSelectedID
& "' " & _
" AND SeqNbr = " & rdr1("SeqNbr")
Dim cmd2 As New SqlCommand(strS QL, cn2)
Dim rdr2 As SqlDataReader
rdr2 = cmd2.ExecuteRea der()
Dim entryslots(50) As Performance
Dim entrycount As Integer = 0
Do While rdr2.Read
' Create a Performance entry
Dim p3 As New Performance
p3.UnitID = rdr2("TeamID")
p3.Class = rdr2("Class")
p3.PerfTime = CDate(rdr2("Per fTime"))
entryslots(entr ycount) = p3
entrycount += 1
Loop
ReDim Preserve entryslots(entr ycount - 1)
evnt.Performanc es = entryslots
rdr2.Close()
serializer.Seri alize(writer, evnt) ' <= Try 2
Loop ' Loop for rdr1 - get next CGShows entry
rdr1.Close()
' Serializes the purchase order, and close the TextWriter.
'serializer.Ser ialize(writer, evnt) ' <= Try 1
writer.Close()
End Sub
<XmlRootAttribu te("EventName" , _
Namespace:="htt p://www.cpandl.com" , IsNullable:=Fal se)> _
Public Class Event
Public NameOfEvent As String
Public ShowBase As ShowBase
Public Performances() As Performance
End Class
Public Class ShowBase
<XmlAttributeAt tribute()> _
Public ID As String
Public ShowName As String
Public SeqNbr As Integer
Public ShowDate As Date
.....
End Class
Public Class Performance
Public TeamID As Integer
Public Class As String
Public PerfTime As DateTime
End Class
=============== Table Structures =============== ======
CREATE TABLE [dbo].[PerfSched] (
[EventID] [varchar] (20) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NOT NULL ,
[SeqNbr] [smallint] NOT NULL ,
[PerfTime] [smalldatetime] NOT NULL ,
[TeamID] [int] NULL ,
[Class] [varchar] (10) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[Shows] (
[EventID] [varchar] (20) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NOT NULL ,
[SeqNbr] [smallint] NOT NULL ,
[ShowName] [varchar] (35) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NOT NULL ,
[ShowDate] [smalldatetime] NOT NULL ,
[Round] [smallint] NOT NULL,
[Type] [char] (2) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NOT NULL ,
[Judge1] [varchar] (20) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NOT NULL ,
[Judge2] [varchar] (20) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
) ON [PRIMARY]
ALTER TABLE [dbo].[PerfSched] WITH NOCHECK ADD
CONSTRAINT [PK_PerfSched] PRIMARY KEY CLUSTERED
(
[EventID],
[SeqNbr],
[PerfTime]
) ON [PRIMARY]
ALTER TABLE [dbo].[Shows] WITH NOCHECK ADD
CONSTRAINT [DF_Shows_Round] DEFAULT (0) FOR [Round],
CONSTRAINT [PK_Shows] PRIMARY KEY CLUSTERED
(
[EventID],
[SeqNbr]
) ON [PRIMARY]
