XmlDocument, reading from a DB with Nulls 1

[AndyH1]

Ive been given the job of debugging someones code (who has left the company). Been struggling with a Cheshire cat problem of it losing data input by the user which has taken ages to pinpoint. I think I might have found the reason.

He (or she - I'm not sure) has some code similar to:

XmlDocument customers = dbConn.Query("SELECT * FROM customer WHERE ID=" + "15"

which I believe is suppose to pull the data from the db (MS SQL) and create xml node. Some of the values in the database are Nulls, however and I think this is causing a problem. Outputting the data ie using customers.OuterXml I get a lot of 'Unable to cast object of type System.DBNull to System.String' warnings within the output text, whereever the value is null in the db, such as

<row><id>15</id>
<name>Customer</name>
<website></website><warning>Exception: Unable to cast object of type 'System.DBNull' to type 'System.String'.</warning>
<address1>22 Somewhere</address>...

I assume this would cause a problem in the xml(and its not a problem in printing out the node to a string)

Does anyone know the best way to solve this? I assume I can do a cast within the sql itself to convert dbnulls to an empty string, but this would require adding every field name explicitly rather than, as they are doing, using an * for all fields. is thera way I can convert the whole lot so I don't need to do this.

Dont know if this is the cause of the error, but Ive spent 3 days trying to find whats causing the loss and getting desperate. If anyone has suggestions they'd be gratefully recieved
Thanks
AndyH1

 

[jmeckley]

are there any unit/integration tests? if not I would recommend adding this to your development cycle.

I would also be weary of "select * ..." when the database changes this will effect the code base. you may, or may not, want all fields selected.

I haven't come across an ADO.Net command object which automatically converts records to an xml hierarchy. so either Query() is custom code, or the sql statement is more than what you provided.

i would also review the table structure and data residing in the table to determine what fields allow nulls and what fields actually contain nulls. what you will need is logic to convert the nulls to default values (null object pattern). then you can easily convert the data to xml.

last observation. I would separate the functionality in the workflow into distinct, testable units. this will make it easier to maintain in the long run. For more information on how I would research the S.O.L.I.D. design concepts. Specifically Single Responsibility. The other 4 are just as important and interconnected, but this is the pain point I see above.

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190
faq732-7259

 

[AndyH1]

jmeckley.

Query() is custom code. Its in a dll called AKResources, which I don't have the source code for. Ive searched the web for AKResources and can't find anything so assume it may be something they developed.


>haven't come across an ADO.Net command object which automatically converts records to an xml hierarchy. so either Query() is custom code, or the sql statement is more than what you provided.

Not sure I really understand. My idea would be to include statements in the SQL to convert using ISNULL(field,''), does that sound sensible? The other idea, probably less elegant, would be to parse out the <warning>...</warning> tags in the result since it does seem to create the empty tag too. ie
<website></website><warning>Exception: Unable to cast object of type 'System.DBNull' to type 'System.String'.</warning>

which it does for an empty website

becomes <website></website>

using a regular expression, but I'm not knowledgable enough on regular expressions to do this. ISNull is probably the best but I'd need to ensure I have every field explicitly in the select statement.
>what you will need is logic to convert the nulls to default values (null object pattern). then you can easily convert the data to xml.

Thanks
AndyH1

 

[AndyH1]

The problem is that the select statement is actually more complicated than the one I gave as an example (which I simplified - it actually consists of a number of joins on 3 tables and returns 90+ fields) which is why it would be easier to use select *. I don't know how I can get the names of the columns returned by the select statement automatically which means I'd need to type them manually and with 90+ field names it would be easy for me to make a mistake. If theres a way of getting all the column names returned automatically it would help, I know how to do this on a table

SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.Columns where TABLE_NAME = 'tablename'

but not on a select statement. The alternative is finding a way to apply ISNULL(field,'') to all fields * but I'm not aware of one.

AndyH1

AndyH1

 

[jmeckley]

if AKResources was developed in house, then you should have the source code somewhere (archived to disc, source control repository, etc).

What you are encountering is friction. This is usually a sign that the design of the code should be re-evaluated. In this case regex to the warning tags would work, but I think it's a hack. the problem is not the xml document. the problem is how the xml document is loaded.

I find sql difficult to test so I favor logic in my code rather than the sql statement. The lighter the sql statement the better.

'*' is a shortcut character which means return all fields. if you do not want all the fields, or you need to preform custom logic on fields then you cannot use the *.

There are actually a number of approaches to fixing this issue. You have already mentioned 2 of them
1. remove the warning nodes after the xml document is created
2. adjust the sql statement to convert nulls to empty strings.
there are other options which may or may not be suitable.
3. adjust the database schema not to allow null values when imputing data.
4. find the source to AKResources and add an extension point to inject logic when processing rows/fields. this would allow you to check for the presence of DbNull. Value and convert the value.

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190
faq732-7259

 

[AndyH1]

Thanks jmeckley,

Unfortunately I can't get the source code for AKResources. It may have been developed in house it wasn't with us; it was with another company from which the programmer was made redundant and who passed the code over to us - needless to say they don't have the source for the dll.

I actually did option 2 you mentioned and this has removed the warnings. It turned out that removing the warnings was unecessary as the programmer only used calls to tags in the xml ie //website and not any hierarchical navigation so the warning tags were just being ignored anyhow but I feel its better. Agree with you on the regex, felt like a hack to me too and not something I'd have been happy with.

Turned out the error I was hoping it would fix wasn't caussed by this though but in the web services so still looking - sigh...

Wish we could reevaluate but timescales are looming and it needs to be operational in two weeks...

Thanks anyhow
Andy