Setting and using relationship between XML and SQL

[Dave]

Hi all,

I am having a problem with my relationships (Not personal
ones though )

I have an XML datasource and a SQL datasource. I have put
them both into the same dataset.

Neither table suits being parent or child. I have had to
put the constraint to false in order to get the page to
at least come up.

My XML table has a list of numbers. My SQL table has some
data with matching numbers, though it may have some data
without matching numbers.

e.g.

XML
111
112
113
115
117

SQL
111
112
118
119

I need to drop the XML data and the SQL data where there
are no match between them, hence in this case, leaving
behind 111 and 112.

The only way I can think of is to me, wasteful and time
consuming. That is to create a loop with the XML data and
feed this in an "IN" statement into SQL Query.

Is there a prettier and faster way to do it.

Thanks.

Dave Colliver.
http://www.SheffieldFOCUS.com

 

[Sahil Malik]

Just use DataTable.Select

Probably not fast, but very straightforward. But no slower than a loop even.

- Sahil Malik
http://dotnetjunkies.com/weblog/sahilmalik

 

[Dave]

Hi,

Thanks for replying.

I have looked to see how to do this but am coming up
against a brick wall. :-(

Can you show me how?

Currently, I am not using a datatable. I can't seem to
find out how to put two tables into a datatable, to set a
relationship, in order to select from that relationship.

I have something like...

DataSet SchoolData = new DataSet();
SchoolData.ReadXml(MapData);

SqlDataAdapter SqlDA = new SqlDataAdapter();
SqlDA.SelectCommand = cmd; // cmd has already been set
up, and works OK.
SqlDA.Fill(SchoolData, "SchoolsList");

DataRelation XML2SQLRelation = new DataRelation
("MyRelation", SchoolData.Tables["SchoolsList"].Columns
["DFES Number"], SchoolData.Tables[0].Columns["DFES"],
false);
SchoolData.Relations.Add(XML2SQLRelation);

SchoolsListGrid.DataSource = SchoolData.Tables
["SchoolsList"];
SchoolsListGrid.DataBind();

I can't have constraints as there are items from both
tables that are not related to items in the connected
tables. However, I must show the data where there is
a "working" relationship (i.e. DFES = DFE Number).

I am really struggling with this and any help is
appreciated.

Thanks.
Dave Colliver.
http://www.AshfordFOCUS.com

 

[Sahil Malik]

Sorry I hadn't seen this message before. On an average day I browse the
newsgroups with atleast 4 computers so at times I miss a few messages .

Anyway, here's how you'd do it.

So you have 2 tables, XML and letssay NonXML, each with FirstColumn.

What you wanna do is "Select FirstColumn from NonXml where FirstColumn In
( Select FirstColumn from XML)"

Now you cant do something like that in ADO.NET, so waht you do is, you
create a Select expression that looks like this. "FirstColumn in
('111','112','113'...)". How you create that column could be a simple loop
OR it could be an XSLT after you convert the dataset/table to XML.

Anyway, assuming that you've come to the Select string, what you do next is,
you do ..

NonXmlDataTable.Select("TheAboveSelectString").

This would give you back an array of exactly what you need. But these are
still DataRow[] <-- datatype. If you indeed need a datatable, you could
create a brandnew datatable, and use ImportRow to populate the rows
contained inside the DataRow[] array to the brand new DataTable.

That should do it !!!


If you need to do the above repeatedly in a loop, you might want to setup a
third temporary datatable even for performance reasons.
..
Create another datatable, with 3 columns

NonXmlFirstColumn -- Value of NonXmlFirstColumn
XMLFirstColumn -- value of XmlFirstColumn
IsCommon -- Set to true of the mapping shown above exists in both tables.

Populate this table as per the logic shown before this.
Set relations between BrandNewThirdTable and XmlTable and NonXmLTable.
Then you can simply find the ones with IsCommon = true.

HTH

- Sahil Malik
http://www.dotnetjunkies.com/weblog/sahilmalik

Hi,

Thanks for replying.

I have looked to see how to do this but am coming up
against a brick wall. :-(

Can you show me how?

Currently, I am not using a datatable. I can't seem to
find out how to put two tables into a datatable, to set a
relationship, in order to select from that relationship.

I have something like...

DataSet SchoolData = new DataSet();
SchoolData.ReadXml(MapData);

SqlDataAdapter SqlDA = new SqlDataAdapter();
SqlDA.SelectCommand = cmd; // cmd has already been set
up, and works OK.
SqlDA.Fill(SchoolData, "SchoolsList");

DataRelation XML2SQLRelation = new DataRelation
("MyRelation", SchoolData.Tables["SchoolsList"].Columns
["DFES Number"], SchoolData.Tables[0].Columns["DFES"],
false);
SchoolData.Relations.Add(XML2SQLRelation);

SchoolsListGrid.DataSource = SchoolData.Tables
["SchoolsList"];
SchoolsListGrid.DataBind();

I can't have constraints as there are items from both
tables that are not related to items in the connected
tables. However, I must show the data where there is
a "working" relationship (i.e. DFES = DFE Number).

I am really struggling with this and any help is
appreciated.

Thanks.
Dave Colliver.
http://www.AshfordFOCUS.com

-----Original Message-----
Just use DataTable.Select

Probably not fast, but very straightforward. But no slower than a loop even.

- Sahil Malik
http://dotnetjunkies.com/weblog/sahilmalik





.