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.Columns["mean"].ValueType = typeof(Double);
dataGridView.Columns["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.Filter property to display
different subsets of the data in the DataGridView. So, for example, one
could say

bindingSource.Filter = "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.Filter = "value > 5 * mean"

I received an EvaluationException 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]]

Hi Michael,

The ValueType property of DataGridViewColumn 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
DataGridViewCell.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.Filter property, it internally leverages underlying
DataView.RowFilter to implement the filtering work. While the syntax of
DataView.RowFilter is the same as DataColumn.Expression 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.Filter = "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/subscriptions/support/default.aspx.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[michael sorens]

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 = GenerateAnalysisXML();
DataSet ds = new DataSet();
MemoryStream ms = new MemoryStream();
myDoc.Save(ms);
ms.Seek(0, 0);
ds.ReadXml(ms, XmlReadMode.Auto);
bindingSource.DataSource = ds;
bindingSource.DataMember = "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.Filter = "value > 0"

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

bindingSource.Filter = "value > 5 * mean"

....causes an EvaluationException. 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]]

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 GenerateAnalysisXML() 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(object sender, System.EventArgs e)
{
DataTable dt=new DataTable();
dt.Columns.Add(new DataColumn("column1", typeof(int)));
dt.Columns.Add(new DataColumn("column2", typeof(string)));
for(int i=0;i<5;i++)
{
DataRow dr=dt.NewRow();
dr["column1"]=i;
dr["column2"]="item"+i.ToString();
dt.Rows.Add(dr);
}
DataSet ds=new DataSet();
ds.Tables.Add(dt);

MessageBox.Show(ds.Tables[0].Columns[0].DataType.ToString());
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(object sender, System.EventArgs e)
{
DataSet ds=new DataSet();
ds.ReadXml("F:\\test.xml");
MessageBox.Show(ds.Tables[0].Columns[0].DataType.ToString());
}

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

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

private void button2_Click(object sender, System.EventArgs e)
{
DataSet ds=new DataSet();
ds.ReadXmlSchema("F:\\testSchema.xml");
ds.ReadXml("F:\\test.xml");
MessageBox.Show(ds.Tables[0].Columns[0].DataType.ToString());
}
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(object sender, System.EventArgs e)
{
DataTable dt=new DataTable();
dt.Columns.Add(new DataColumn("column1", typeof(int)));
dt.Columns.Add(new DataColumn("column2", typeof(string)));
for(int i=0;i<5;i++)
{
DataRow dr=dt.NewRow();
dr["column1"]=i;
dr["column2"]="item"+i.ToString();
dt.Rows.Add(dr);
}
DataSet ds=new DataSet();
ds.Tables.Add(dt);

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

}

private void button2_Click(object sender, System.EventArgs e)
{
DataSet ds=new DataSet();
//construct the schema types manually with code
DataTable dt=new DataTable();
dt.Columns.Add(new DataColumn("column1", typeof(int)));
dt.Columns.Add(new DataColumn("column2", typeof(string)));
ds.Tables.Add(dt);

ds.ReadXml("F:\\test.xml");
MessageBox.Show(ds.Tables[0].Columns[0].DataType.ToString());
}
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/subscriptions/support/default.aspx.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[michael sorens]

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!