"Filter" data from XML datafile into datagrid

[Thomas A]

Hi,

I fill a datgrid with data from a xml document, it works fine
But....
Now I will to filter the data to the grid so only the data shows from
the criteria that I set.

My code now is very simple to fill the grid





Dim strProj As String

Dim MyA As String()
Dim dsCodes As New DataSet
Dim fsReadXml As New System.IO.FileStream(XMLFile,
System.IO.FileMode.Open)

Dim XmlReader As New XmlTextReader(fsReadXml)

dsCodes.ReadXml(XmlReader)

XmlReader.Close()


'fill the grid
DataGrid1.DataSource = dsCodes.Tables(XMLTable)


I wont to show all customerdata for my customer in my grid there only
Proj No = strProj

My XML file looks like this:



<?xml version='1.0' encoding='utf-8' ?>
<!--Code Information-->
<xml>
<projectinfo>
<Cust No='2298' Name='Company1' Adress1='Adress1' Adress2
='Box 999'
Adress3='161 02 BROMMA' Phone='08-999 91 90' Fax=''
Trakt='0' />
<Proj No='445640' Name='Ongoing' />
</projectinfo>
<projectinfo>
<Cust No='2324' Name='Company2' Adress1='xxxxx' Adress2='Box
230'
Adress3='127 24 HOLMEN' Phone='08-999 99 00' Fax=''
Trakt='0' />
<Proj No='232401' Name='Ongoing' />
<Proj No='232402' Name='Test' />
<Proj No='232403' Name='Startup' />

</projectinfo>
</xml>


I have search for this but I couldnt find anything about it, maybe
someone has got an solution or a tips there I could find it.

//Regards Thomas

 

[copyco]

You have to use a DataView because only a DataView has the capability to
filter data or sort it. So, you'll need to do something like this:

dvCodes = New DataView(dsCodes.Tables("Codes"))
dvCodes.Sort = "Name" ' * only if you wanted it sorted
dvCodes.RowFilter = "Proj No = '" & strProj & "'"
DataGrid1.DataSource = dvCodes

and just assign the DataView to the datagrid instead of the DataSet.

 

[Cor]

Hi Copyco,

The problem is that the used XML file is not a dataset but a XML file
(wellformed).
(It has 3 tables but I cannot find a way to make a relation with it in a
dataset way).
As far as I see it now, is making a nice datatable from it the best, but I
am waiting if we see a better answer also.

When I see the code than I asume Thomas is not a real expirienced one with
dataset, datatables etc. and therefore I do not know what is the best
answer.

(Maybe it is better if he starts to make a dataset XML file instead of a XML
file)


Cor

 

[Jay B. Harlow [MVP - Outlook]]

Cor,

When I see the code than I asume Thomas is not a real expirienced one with
dataset, datatables etc. and therefore I do not know what is the best
answer.

Huh?

I hope you realize that using the DataColumn.ColumnMapping &
DataRelation.Nested properties a DataSet can create the XML that Thomas
showed!

If any thing Thomas is using the DataSet in a very advanced method, which in
some cases is preferred.

(It has 3 tables but I cannot find a way to make a relation with it in a
dataset way).

Remember when the DataSet reads the XML it will automatically define (infer)
the relationship.

Try the following code after Thomas's code to see the above settings in
action.

For Each table As DataTable In ds.Tables
Debug.WriteLine(table.TableName, "table")
Debug.Indent()
For Each column As DataColumn In table.Columns
Debug.WriteLine(column.ColumnName, "column")
Debug.Indent()
Debug.WriteLine(column.ColumnMapping, "mapping")
Debug.Unindent()
Next
Debug.Unindent()
Next
For Each relation As DataRelation In ds.Relations
Debug.WriteLine(relation.RelationName, "relation")
Debug.Indent()
Debug.WriteLine(relation.Nested, "nested")
Debug.WriteLine(relation.ParentTable, "parent")
Debug.Indent()
For Each column As DataColumn In relation.ParentColumns
Debug.WriteLine(column.ColumnName, "column")
Next
Debug.Unindent()
Debug.WriteLine(relation.ChildTable, "child")
Debug.Indent()
For Each column As DataColumn In relation.ChildColumns
Debug.WriteLine(column.ColumnName, "column")
Next
Debug.Unindent()
Debug.Unindent()
Next


Hope this helps
Jay

 

[Thomas A]

Thank you all,

It works fine now.

I think also that I should make two xml files instead of one
One with customer data and one with project info.

is it possible to link 2 xml files with data to one datagrid
I thininking about a query to show projectnumber, projectname from
project.xml and customername from customer.xml linked by customercode
from both files.

//Thomas

 

[Jay B. Harlow [MVP - Outlook]]

Thomas,
You can use the Merge method to merge two DataSets into a single Dataset.

After you loaded each XML file into their own DataSet.

Then you might be able to use the JoinView sample custom DataView class for
VB.NET to join the project data with the customer data.

See:
http://support.microsoft.com/default.aspx?scid=kb;en-us;325682

Basically you create a new JoinView object, set the properties for your
join, then use this JoinView object as the DataSource on your DataGrid.

Hope this helps
Jay

 

[Cor]

Hi Jay B.

Are you sure of that all.

I hope that you know that it is much easier to test this using the VS.net
tools.

Just paste the XML file from Thomas on an empty item XML file that you have
opened in the IDE.

Than just right click and say create schema.
An XSD file is formed. And right away you have an error on your XML file,
because the schema correspondent not with the XML file.

When you look at the schema, than you will see that there is no relation at
all between the project information and the customer information.

However, I think that when the XML reader (not the dataset) is used to
create a new datatable, the new datatable can be used to represent that in
the datagrid with selecting. (Without updating of course to the XML file
back).

Because of the last fact I think it is better when Thomas creates a real
dataset with posible relations between the customer and the projectinfo.

As I said in my earlier post maybe is it possible to create a relation to
the parent projectinfo from the Proj and than try to get the first childrow
from the related Cust table, but I think that it will be a lot of work to
find out how. In addition, this will than only work because the logic exist
in this situation that there is only one customer row in a projectinfo. For
this I think it will also not possible to update.

I hope this helps?

Cor

 

[Cor]

Hi Thomas,

If you make one or two XML files is not that important I think.
(I think it is not a bad idea).

But the main thing is that you have situated your customer info in your
projectinfo and that is bad design.

You have customers and for those you have projects.

A project is related to a customer and has therefore the id from the
customer has to be in the data of a project (Before you write it, no that
would not be necessary with XML file but it is for a dataset).

And also make from all what is now an attribute an element, that is what the
dataset also does.

Cor

 

[Jay B. Harlow [MVP - Outlook]]

Cor,

Are you sure of that all.

Of course I'm sure!

You should realize by now that rarely do I say anything that I am not sure
about. Or that I am not able or willing to back up.

I hope that you know that it is much easier to test this using the VS.net
tools.

I hope that you realize that the VS.NET tools are just that, they are tools,
designed to give you X, while the DataSet gives you X & Y.

All you have succeeded in doing is prove that the VS.NET tools do not fully
support Y at the level that the DataSet does. HOWEVER!

Than just right click and say create schema.
An XSD file is formed. And right away you have an error on your XML file,
because the schema correspondent not with the XML file.

I'm not seeing an error (in VS.NET 2003).

As I said in my earlier post maybe is it possible to create a relation to

Obviously you missed the part in my post that stated that the DataSet
already does!

Hope this helps
Jay

 

[Cor]

Hi Jayb B,

You should realize by now that rarely do I say anything that I am not sure
about. Or that I am not able or willing to back up.

You should realize that with me is the same and that I do not like this kind
of statements to my adres. (But maybe you have a MVP status that gives you
the right to do that and I do not have that).

Obviously you missed the part in my post that stated that the DataSet
already does!

Than show it, because I will really like to see how you can make from two
almost nonrelated datatables on a equal a level a relation. Your debugging
using a XML file read is absolutly not importang in this. XML has a lot of
ways to use and they are not all mixable.

As I already stated this whole thread is in my opinion the XML file from
Thomas no dataset.

I will gladly see a dataset made with complex elements and in that
attributes.

Cor

 

[Jay B. Harlow [MVP - Outlook]]

Cor,

Than show it, because I will really like to see how you can make from two
almost nonrelated datatables on a equal a level a relation.

Run the previously posted code (copied here for you convenience):

Dim strProj As String

Dim MyA As String()
Dim dsCodes As New DataSet
Dim fsReadXml As New System.IO.FileStream(XMLFile,
System.IO.FileMode.Open)

Dim XmlReader As New XmlTextReader(fsReadXml)

dsCodes.ReadXml(XmlReader)

XmlReader.Close()

For Each table As DataTable In ds.Tables
Debug.WriteLine(table.TableName, "table")
Debug.Indent()
For Each column As DataColumn In table.Columns
Debug.WriteLine(column.ColumnName, "column")
Debug.Indent()
Debug.WriteLine(column.ColumnMapping, "mapping")
Debug.Unindent()
Next
Debug.Unindent()
Next
For Each relation As DataRelation In ds.Relations
Debug.WriteLine(relation.RelationName, "relation")
Debug.Indent()
Debug.WriteLine(relation.Nested, "nested")
Debug.WriteLine(relation.ParentTable, "parent")
Debug.Indent()
For Each column As DataColumn In relation.ParentColumns
Debug.WriteLine(column.ColumnName, "column")
Next
Debug.Unindent()
Debug.WriteLine(relation.ChildTable, "child")
Debug.Indent()
For Each column As DataColumn In relation.ChildColumns
Debug.WriteLine(column.ColumnName, "column")
Next
Debug.Unindent()
Debug.Unindent()
Next

'fill the grid
DataGrid1.DataSource = dsCodes.Tables(XMLTable)

Or are you asking how to create said DataSet, either from a XSD or via code?

Your debugging
using a XML file read is absolutly not importang in this. XML has a lot of
ways to use and they are not all mixable.

The Debug.Writelines are showing you how the DataColumn.ColumnMapping &
DataRelation.Nested work to give you what Thomas is asking for, how are they
not important?

In other words: why state "then show it" then immediately dismiss the
example where I do show it???

Note: If you want a response to the other "static" send me a private email.

Hope this helps
Jay

 

[Cor]

Hi JaybB,

Thank you for your message.

I send you a seperate mail for the other situation.

This is what I all the time try to tell to you and also have described in my
last message.

This is the printed relation from the given XML file with your sample (only
the last part the first is for this not important in my eyes, from that I
have an XSD schema).
relation: projectinfo_Cust
nested: True
parent: projectinfo
column: projectinfo_Id
child: Cust
column: projectinfo_Id
relation: projectinfo_Proj
nested: True
parent: projectinfo
column: projectinfo_Id
child: Proj
column: projectinfo_Id
I have extended your code with this.
\\\
For Each table As DataTable In ds.Tables
Debug.WriteLine(table.TableName, "table")
Debug.Indent()
For Each row As DataRow In table.Rows
Debug.WriteLine(row("projectinfo_Id"), "id")
Next
Debug.Unindent()
Next
///
Than I get this.
table: projectinfo
id: 0
id: 1
table: Cust
id: 0
id: 1
table: Proj
id: 0
id: 1
id: 1
id: 1

Exactly as I said some messages before in this thread
-----------------------------
As I said in my earlier post maybe is it possible to create a relation to
the parent projectinfo from the Proj and than try to get the first childrow
from the related Cust table, but I think that it will be a lot of work to
find out how. In addition, this will than only work because the logic exist
in this situation that there is only one customer row in a projectinfo. For
this I think it will also not possible to update.
------------------------------
By the way, do you know that in the code from Thomas only this is needed.

Dim ds As New DataSet
ds.ReadXml("c:\testx.xml")
DataGrid1.DataSource = ds.Tables(0)

Cor