Challenge of Day

Friday, November 19, 2010

DataSet.GetXML Does Not Return Null Values

XML is the defacto of data exchange and is used heavily as a means for presenting and transfering the data.
.NET framework provides a lot of facilities for manipulaitng this data type but when it comes to representing a data set with XML, .NET framework simply does not includes fields by null values. In other word, null fields are omitted in the output XML.
This is a known issue and you can read about it in this knowledgebase article by Microsoft.
There are two possible solutions to this problem:
1 - Either modify your SELECT statement and replace NULL values in the result set with some other values (e.g. Blank or Zero)
2 - You have to write your own GetXML method like this: This code snippet retrieves result set from a stored procedure and makes the XML string.

Dim conn As New OracleConnection
conn.ConnectionString = "data source=oracle;user id=baridsoft;pwd=barid"
Dim ds As New DataSet
Dim da As New OracleDataAdapter
Dim cmd As New OracleCommand
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = "sp_stored_procedure"
Dim p1 As OracleParameter
p1 = cmd.Parameters.Add("grs", OracleType.Cursor)
p1.Direction = ParameterDirection.Output
cmd.Connection = conn
da.SelectCommand = cmd
da.Fill(ds, "mytbl")
If ds.Tables.Contains("mytbl") Then
ds.Tables("mytbl").WriteXml("c:\ali\prr1.xml", XmlWriteMode.IgnoreSchema, True)
Dim strXML As String = ""
strXML = "" + vbCrLf
For Each r As DataRow In ds.Tables("mytbl").Rows
strXML = strXML + "" + vbCrLf
Dim st As String = ""
For i As Integer = 0 To r.ItemArray.Count - 1
st = st + "<" + ds.Tables("mytbl").Columns(i).ColumnName + ">"
If r.IsNull(i) Then
st = st + r(i).ToString
End If
st = st + "" + vbCrLf
strXML = strXML + st + "" + vbCrLf
Next strXML = strXML + "" + vbCrLf
End If

Be a winner in your daily challenges
Ali Khademi

Labels: ,