setting BindingSource data types from an XML data source

[michael sorens]

I have successfully bound an XmlDocument to a DataGridView but all fields
seem to be strings. I want to retrofit appropriate datatypes on some of
the fields. Let me take this in 2 parts.

Part I:
I found the ValueType field, leading me to believe that I could say

dataGridView.Co lumns["mean"].ValueType = typeof(Double);
dataGridView.Co lumns["sum"].ValueType = typeof(Integer) ;
etc.

I have not tried this but I suspect it would then, for example, sort a
column numerically rather than lexically when I click on the column
heading. Is that true?

Part II:
I want to dynamically adjust the bindingSource.F ilter property to display
different subsets of the data in the DataGridView. So, for example, one
could say

bindingSource.F ilter = "sum 0"

This actually works now, but I just realized that it is mere coincidence,
as they are really treated as strings.
When I tried this, however:

bindingSource.F ilter = "value 5 * mean"

I received an EvaluationExcep tion saying that "*" could not be applied to
an Integer and a String. Which is due, I assume, to the fact that I set
the ValueType on the dataGridView, rather than on the bindingSource. So
how could I get this to work? I am guessing it is not as simple as just
setting the type but I need to attach a schema for the XML in some way....?

[Jeffrey Tan[MSFT]]

RE: setting BindingSource data types from an XML data source

Hi Michael,

The ValueType property of DataGridViewCol umn can not be used to convert all
the values in that column from string to numeric type. Based on my
research, it is used as formatting purpose, which is leveraged by
DataGridViewCel l.ValueType.

Can you tell me what effect you want to achieve by converting the data type
of a column? I think the correct solution to translate the data type is
creating an additional DataColumn(yes, I assume you have read the
XmlDocument to DataTable, and then bind the DataTable to the DataGridView)
with type of Integer or Double, then you have to read all the values in the
cell one by one and parse them into Integer/Double and assign to the cells
in the new column.

Regarding bindingSource.F ilter property, it internally leverages underlying
DataView.RowFil ter to implement the filtering work. While the syntax of
DataView.RowFil ter is the same as DataColumn.Expr ession property. So you
should consult the MSDN link below for the correct syntax:

http://msdn2.microsoft.com/en-us/library/system.data.datacolumn.expression.a

spx

For the specific formatting string below, I am not sure what "value" and
"mean" mean in your code context. I assume they are both DataColumn names:
bindingSource.F ilter = "value 5 * mean"

Then, since mean DataColumn is still of type string, so it can not multiply
the integer of 5. So the exception will generate. By converting all the
values in the DataColumn from string to integer value, I think the
expression will be OK.

Hope this helps.

Best regards,
Jeffrey Tan
Microsoft Online Community Support
=============== =============== =============== =====
Get notification to my posts through email? Please refer to

http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif

ications.

Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscripti...t/default.aspx.
=============== =============== =============== =====
This posting is provided "AS IS" with no warranties, and confers no rights.

[michael sorens]

Re: setting BindingSource data types from an XML data source

Sorry for the delay in replying, Jeffrey :-)
I am not quite sure how to apply what you have stated to my situation, so
let me add some additional information that may state my need better. Here
is the essential code I am using (thanks in part to another post on this
forum) to go from my own generated XML into a DataGridView:

XmlDocument myDoc = GenerateAnalysi sXML();
DataSet ds = new DataSet();
MemoryStream ms = new MemoryStream();
myDoc.Save(ms);
ms.Seek(0, 0);
ds.ReadXml(ms, XmlReadMode.Aut o);
bindingSource.D ataSource = ds;
bindingSource.D ataMember = "device";
myDataGridView. DataSource = bindingSource;

Within the XML I have generated are columns "value" and "mean". As I
stated, if I use something like this:

bindingSource.F ilter = "value 0"

....then it dynamically filters the DataGridView as desired, whereas this:

bindingSource.F ilter = "value 5 * mean"

....causes an EvaluationExcep tion. So clearly in principle, as you state,I
need value and mean to be numerical types (integer or double). How do I go
about doing that, based on the above code?

[Jeffrey Tan[MSFT]]

Re: setting BindingSource data types from an XML data source

Hi Henin,

Thanks for your feedback.

Oh, this lies with how you save the memory xml data into the disk, that is,
how you implement GenerateAnalysi sXML() method.

There are 2 types of data you should save to disk xml files: 1. Xml data,
2. Xml Schema data.

We normally save the Xml data into the disk without any problem, however,
without the Xml schema information serialized to disk, the deserilizer does
not know how to parse the data in the xml file, so it always treats it as
string type.

For example, the below code snippet only saves the original DataSet data
into the disk:

private void button1_Click(o bject sender, System.EventArg s e)
{
DataTable dt=new DataTable();
dt.Columns.Add( new DataColumn("col umn1", typeof(int)));
dt.Columns.Add( new DataColumn("col umn2", typeof(string)) );
for(int i=0;i<5;i++)
{
DataRow dr=dt.NewRow();
dr["column1"]=i;
dr["column2"]="item"+i.ToStr ing();
dt.Rows.Add(dr) ;
}
DataSet ds=new DataSet();
ds.Tables.Add(d t);

MessageBox.Show (ds.Tables[0].Columns[0].DataType.ToStr ing());
ds.WriteXml("F: \\test.xml");
}

When reading the xml file, DataSet.ReadXml method does not know how to
parse the integer data in the column1, so it will always parse it as a
string. So after reading it in DataSet, we will find the column1's datatype
is string:
private void button2_Click(o bject sender, System.EventArg s e)
{
DataSet ds=new DataSet();
ds.ReadXml("F:\ \test.xml");
MessageBox.Show (ds.Tables[0].Columns[0].DataType.ToStr ing());
}

To get the correct type, you should serialize the schema information and
read the schema before reading data, like this:
private void button1_Click(o bject sender, System.EventArg s e)
{
DataTable dt=new DataTable();
dt.Columns.Add( new DataColumn("col umn1", typeof(int)));
dt.Columns.Add( new DataColumn("col umn2", typeof(string)) );
for(int i=0;i<5;i++)
{
DataRow dr=dt.NewRow();
dr["column1"]=i;
dr["column2"]="item"+i.ToStr ing();
dt.Rows.Add(dr) ;
}
DataSet ds=new DataSet();
ds.Tables.Add(d t);

MessageBox.Show (ds.Tables[0].Columns[0].DataType.ToStr ing());
ds.WriteXml("F: \\test.xml");
ds.WriteXmlSche ma("F:\\testSch ema.xml");
}

private void button2_Click(o bject sender, System.EventArg s e)
{
DataSet ds=new DataSet();
ds.ReadXmlSchem a("F:\\testSche ma.xml");
ds.ReadXml("F:\ \test.xml");
MessageBox.Show (ds.Tables[0].Columns[0].DataType.ToStr ing());
}
As you will see that, the column1's datatype will be correctly parse as
integer now.

So the key point is that you have to correctly generate the schema xml file
for the xml data.

If you failed to generate the xml schema inforamtion but still want to get
the correct data type in the reading, your reading code must know of the
schema information advance, so that you can manually construct the
DataTable/DataColumn schema type information by code before reading data,
like this:
private void button1_Click(o bject sender, System.EventArg s e)
{
DataTable dt=new DataTable();
dt.Columns.Add( new DataColumn("col umn1", typeof(int)));
dt.Columns.Add( new DataColumn("col umn2", typeof(string)) );
for(int i=0;i<5;i++)
{
DataRow dr=dt.NewRow();
dr["column1"]=i;
dr["column2"]="item"+i.ToStr ing();
dt.Rows.Add(dr) ;
}
DataSet ds=new DataSet();
ds.Tables.Add(d t);

MessageBox.Show (ds.Tables[0].Columns[0].DataType.ToStr ing());
ds.WriteXml("F: \\test.xml");

}

private void button2_Click(o bject sender, System.EventArg s e)
{
DataSet ds=new DataSet();
//construct the schema types manually with code
DataTable dt=new DataTable();
dt.Columns.Add( new DataColumn("col umn1", typeof(int)));
dt.Columns.Add( new DataColumn("col umn2", typeof(string)) );
ds.Tables.Add(d t);

ds.ReadXml("F:\ \test.xml");
MessageBox.Show (ds.Tables[0].Columns[0].DataType.ToStr ing());
}
Once you construct the columns datatype before ReadXml, the data in xml
file will be parsed correctly to integer type.

If you still have anything unclear, or need any help, please feel free to
tell me, thanks.

Best regards,
Jeffrey Tan
Microsoft Online Community Support
=============== =============== =============== =====
Get notification to my posts through email? Please refer to

http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif

ications.

Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscripti...t/default.aspx.
=============== =============== =============== =====
This posting is provided "AS IS" with no warranties, and confers no rights.

[michael sorens]

Re: setting BindingSource data types from an XML data source

Wow--your explanatation and discussion of alternatives have provided me
with a wealth of great information both for my current project and future
work. Thank you!