setting dataset datarelation from database

[stv_fortner]

Hi,

I want to set my dataset's datarelations based on the relationships
from a database. I've read about people doing this, and since the
dataset.readxmlschema() is just looking for a xmlreader, I provide that
with sqlcommand.executexmlreader(). I also tried casting it to
(xmltextreader) since that's what the method really returns, to no
avail. I'm hoping someone can tell me what's wrong. I had a query I
initially used:

select top 1 Brands.*, Models.*, ModelTypes.*, Types.*, Audio.*,
Video.*, IRCommands.*, IRTime.*, IRFreqRepeat.*, Groups.*, FileNames.*
from Brands (nolock) join Models (nolock) on (Brands.ID = Models.Brand)
join ModelTypes (nolock) on (Models.ID = ModelTypes.Model) join Types
(nolock) on (ModelTypes.Type = Types.ID)
join Audio (nolock) on (Models.Audio = Audio.ID) join Video (nolock) on
(Models.Video = Video.ID) join IRCommands (nolock) on (Models.ID =
IRCommands.Model) join IRTime (nolock) on (IRCommands.Time =
IRTime.Time) join IRFreqRepeat on (IRCommands.FreqRepeat =
IRFreqRepeat.ID)
join Groups (nolock) on (IRCommands.[Group] = Groups.ID) join FileNames
on (IRCommands.FileName = FileNames.ID)
for xml auto, xmldata

It returned the first four relationships correctly, but I read online
that it uses the order of the tables in the query, not the join
relationships. The relationships were plugged into the dataset, but
the 5th relationship was between Types and Audio, the 6th was between
Audio and Video, and so forth, which was incorrect. Okay, fine. So, I
switched to using 'for xml explicit, xmldata' but now the relationships
are gone. If I run the query in sql query analyzer, I see the schema
at the top of the xml that's returned, along with the data, but there
aren't any relationships in the schema, nor are there any in the
dataset. I'm wondering if I need to switch to using xml view with
annotated xdr or xsd schema? Here's the code I'm using (I've also
tried creating a SQLXML 3.0 SP 3 sqlxmlcommand, and using that, but the
datarelations are still missing):

SqlConnection _conn = new SqlConnection(connstr);
_conn.Open();
SqlCommand comm = new SqlCommand(spGetIRCommandSchema, _conn);
// sql xml 3.0 SP 3 didn't return datarelations either, tried no root
tag and tried "root"
//SqlXmlCommand comm = new SqlXmlCommand(xmlConnStr);
//comm.RootTag = "root";
//comm.CommandType = SqlXmlCommandType.Sql;
//comm.CommandText = "exec dbo.spGetIRCommandSchema";
comm.CommandType = CommandType.StoredProcedure;
DataSet ds = new DataSet();
XmlReader rdr;

// sql xml 3.0 SP 3 didn't return datarelations either, with both
//SqlXmlCommand comm = database.CreateSQLXMLCommand("root",
SqlXmlCommandType.Sql, "exec dbo.spGetIRCommandSchema");
SqlCommand comm = database.CreateCommand("spGetIRCommandSchema", null);
rdr = comm.ExecuteXmlReader();
ds.ReadXmlSchema(rdr);
rdr.Close();
// also tried ReadXmlSchema() followed by ReadXml() that I read was
required to get the data, but that made no difference either...
//rdr = (XmlTextReader)comm.ExecuteXmlReader();
//ds.ReadXml(rdr, XmlReadMode.ReadSchema);
//rdr.Close();

Here's an excerpt of the new query using for xml explicit, xmldata:

CREATE PROCEDURE spGetIRCommandSchema
AS

select distinct 1 as Tag,
NULL as Parent,
Brands.ID as [Brands!1!ID!id],
Brands.[Name] as [Brands!1!Name],
Brands.Manufacturer as [Brands!1!Manufacturer],
Brands.WebSite as [Brands!1!WebSite],
Brands.Address1 as [Brands!1!Address1],
Brands.Address2 as [Brands!1!Address2],
Brands.City as [Brands!1!City],
Brands.State as [Brands!1!State],
Brands.PostalCode as [Brands!1!PostalCode],
Brands.Country as [Brands!1!Country],
NULL as [Models!2!ID],
NULL as [Models!2!Name],
NULL as [Models!2!Brand!idref],
NULL as [Models!2!Audio],
NULL as [Models!2!Video],
NULL as [Models!2!IsEditable],
NULL as [Audio!3!ID],
NULL as [Audio!3!Name],
NULL as [Video!4!ID],
NULL as [Video!4!Name],
NULL as [IRCommands!5!Name],
NULL as [IRCommands!5!Model],
NULL as [IRCommands!5!Content],
NULL as [IRCommands!5!Time],
NULL as [IRCommands!5!FreqRepeat],
NULL as [IRCommands!5!Group],
NULL as [IRCommands!5!FileName],
NULL as [IRCommands!5!IsEditable],
NULL as [IRTime!6!ID],
NULL as [IRTime!6!Time],
NULL as [IRTime!6!OnTime],
NULL as [IRTime!6!OffTime],
NULL as [IRFreqRepeat!7!ID],
NULL as [IRFreqRepeat!7!CarrierNumber],
NULL as [IRFreqRepeat!7!RepeatPoint],
NULL as [Groups!8!ID],
NULL as [Groups!8!Name],
NULL as [FileNames!9!ID],
NULL as [FileNames!9!Name],
NULL as [ModelTypes!10!Model],
NULL as [ModelTypes!10!Type],
NULL as [Types!11!ID],
NULL as [Types!11!Name]

From Brands

UNION ALL
select distinct 2 as Tag,
1 as Parent,
Brands.ID,
NULL,
NULL,
..
..
..
Types.ID,
Types.Name
from Brands join Models on (Brands.ID = Models.Brand) join Audio on
(Models.Audio = Audio.ID)
join Video on (Models.Video = Video.ID) join IRCommands on (Models.ID =
IRCommands.Model)
join IRTime on (IRCommands.Time = IRTime.Time) join IRFreqRepeat on
(IRCommands.FreqRepeat = IRFreqRepeat.ID)
join Groups on (IRCommands.[Group] = Groups.ID) join FileNames on
(IRCommands.FileName = FileNames.ID)
join ModelTypes on (Models.ID = ModelTypes.Model) join Types on
(ModelTypes.Type = Types.ID)
ORDER BY [Brands!1!ID!id], [Models!2!ID], [Audio!3!ID], [Video!4!ID],
[IRCommands!5!Model], [IRCommands!5!Name],
[IRCommands!5!Time], [IRTime!6!Time], [IRCommands!5!FreqRepeat],
[IRFreqRepeat!7!ID], [IRCommands!5!Group],
[Groups!8!ID], [IRCommands!5!FileName], [FileNames!9!ID],
[ModelTypes!10!Model], [ModelTypes!10!Type], [Types!11!ID]
FOR XML EXPLICIT, XMLDATA

Here's an excerpt of the schema the query produces (from query
analyzer):

<Schema name="Schema1" xmlns="urn:schemas-microsoft-com:xml-data"
xmlns:dt="urn:schemas-microsoft-com:datatypes">
<ElementType name="Brands" content="mixed" model="open">
<AttributeType name="ID" dt:type="id"/>
<AttributeType name="Name" dt:type="string"/>
<AttributeType name="Manufacturer" dt:type="string"/>
<AttributeType name="WebSite" dt:type="string"/>
<AttributeType name="Address1" dt:type="string"/>
<AttributeType name="Address2" dt:type="string"/>
<AttributeType name="City" dt:type="string"/>
<AttributeType name="State" dt:type="string"/>
<AttributeType name="PostalCode" dt:type="string"/>
<AttributeType name="Country" dt:type="string"/>
<attribute type="ID"/>
<attribute type="Name"/>
<attribute type="Manufacturer"/>
<attribute type="WebSite"/>
<attribute type="Address1"/>
<attribute type="Address2"/>
<attribute type="City"/>
<attribute type="State"/>
<attribute type="PostalCode"/>
<attribute type="Country"/>
</ElementType>
<ElementType name="Models" content="mixed" model="open">
<AttributeType name="ID" dt:type="i4"/>
<AttributeType name="Name" dt:type="string"/>
<AttributeType name="Brand" dt:type="idref"/>
<AttributeType name="Audio" dt:type="ui1"/>
<AttributeType name="Video" dt:type="ui1"/>
<AttributeType name="IsEditable" dt:type="boolean"/>
<attribute type="ID"/>
<attribute type="Name"/>
<attribute type="Brand"/>
<attribute type="Audio"/>
<attribute type="Video"/>
<attribute type="IsEditable"/>
</ElementType>
..
..
..

and here's an excerpt of one part of the data:

<Brands xmlns="x-schema:#Schema1" ID="1" Name="Elan">
<Models ID="2" Name="SomeModel" Brand="1" Audio="0" Video="0"
IsEditable="0">
<Audio ID="0" Name="No"/>
<Video ID="0" Name="No"/>
<IRCommands Name="*, 0" Model="2" Content="00:00:01" Time="3"
FreqRepeat="4" Group="1" FileName="15" IsEditable="0">
<IRTime ID="1" Time="3" OnTime="115" OffTime="1123"/>
<IRTime ID="2" Time="3" OnTime="115" OffTime="1234"/>
<IRTime ID="3" Time="3" OnTime="115" OffTime="15234"/>
<IRFreqRepeat ID="4" CarrierNumber="46" RepeatPoint="0"/>
<Groups ID="1" Name="Normal"/>
<FileNames ID="15" Name="SomeModelFile.irf"/>
</IRCommands>
<ModelTypes Model="2" Type="4"/>
<Types ID="4" Name="System Controller"/>
<ModelTypes Model="2" Type="5"/>
<Types ID="5" Name="Controller"/>
<ModelTypes Model="2" Type="13"/>
<Types ID="13" Name="Video Controller"/>
..
..
..

Thanks in advance for any help,

 

[Steve Fortner]

I've also tried using an XmlValidatingReader, but that didn't help
either. For reference, I've also used ReadXml(rdr,
XmlReadMode.fragment), since that's what the stored procedure produces,
also to no avail. Here's the code for the XmlValidatingReader I tried:

XmlValidatingReader rdr;
SqlCommand comm = database.CreateCommand("spGetIRCommandSchema", null);
rdr = new XmlValidatingReader(comm.ExecuteXmlReader());
rdr.ValidationType = ValidationType.XDR;

 

[Cor Ligthert [MVP]]

Steve,

AFAIK has a dataset no Attributes only Elements.

I would if I was you try to make a dataset using the designer for that or by
code.

For reading and writing that is the
ds.writeXML(path)
ds.readXML(path)

Cor

 

[Steve Fortner]

When I used 'for xml auto, xmldata' it returned both the DataTables and
the DataRelations, it just that after the fourth relation it was wrong.
Also, I know you can convert the XDR schema at the top of the xml
returned from a query using 'for xml explicit, xmldata' with the Visual
Studio's xsd.exe tool, and create an XSD file. You can then read that
file, pass it into the dataset with the readxmlschema() method, and set
the DataTables and DataRelations that way. The dataset supports it,
but it's more painful that way and is not dynamic. If we can store the
schema in a file, why can't we take the generated schema from the
database and use that to initialize the dataset's DataRelations???

--Steve

 

[Steve Fortner]

One other interesting find.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sql25xmlbp.asp
says the following:

'Note that the FOR XML clause has an option called XMLData which, if
specified, includes an auto-generated XDR schema inline with the XML
view, but it's not a mapping schema.'

It looks like Microsoft is officially saying here that since the XDR
schema is not a mapping schema what I'm trying to do won't work. The
same article recommends using XSD mapping schemas, which looks like an
XSD file that you can read into the DataSet using the ReadXmlSchema
method. Maybe that's the best I can do going down that route with SQL
Server 2000?

Maybe I should just give up on my noble quest and go for using one
DataSet, multiple data adapters for each database table, and set up my
DataSet's DataRelations manually? I have to say, I'm disappointed. So
close, yet so far. I'm sure Microsoft would recommend upgrading to SQL
Server 2005, but that's probably reserved for next year...

--Steve