I am modifying a code in c# that retrieves data from a query and displays it in xml. Actually, I am modifying the query, since the rest of the code should not change.
This query returns a column which is sometimes a decimal number (precision 2), and other times an integer. Executing the query itself gives the correct results.
The data from the query are parsed by the code below to be served as xml. However in the xml document what I get are scientific notation values for the entire column.
The code:
database_connection = new SqlConnection(CONNECTION_STRING);
command = new System.Data.SqlClient.SqlCommand(sql_query, database_connection);
command.Connection.Open();
document = new System.Xml.XmlDocument();
document.Load(command.ExecuteXmlReader());
list = document.GetElementsByTagName("time_in_months");
foreach (XmlNode elementToEncrypt in list)
{
elementToEncrypt.InnerXml = elementToEncrypt.InnerXml;
}
Response.ContentType = "text/xml";
Response.ContentEncoding = System.Text.Encoding.UTF8;
document.Save(Response.Output);
command.Connection.Close();
Response.End();
Basically, the query returns for ex. 0.33 for the db value 10 (10 days = 0.33 months) and 3 for the db value 90.
The xml document though returns 3.300000000000000e-001 for 10 days and 3.000000000000000e+000 for 90 days.
I could set a standard that all values be double, but the scientific notation is a problem. Not being a c# programmer, I really am not sure where the conversation of types happens.
PS: I am aware that days of the month vary, but this does not matter in the way we are doing the calculations. We are taking it as if each month has 30 days.
I haven't figured this out completely, so other answers are welcomed, but I did solve the problem.
I cast the values at the query as varchar (with reasonable length) and now the values are displayed properly. The query execution still gives the same results, but the xml data are formatted properly.
Just a wild guess, ma be that when the data are parsed into xml, it detects two different datatypes and converts them to scientific notation to support both datatypes.