c# - 如何将所有列作为属性的 DataTable 导出到 Xml？

Question

问题:我正在将 System.Data.DataTable 导出到 XML。到目前为止它工作正常。但我想将所有数据都放在属性中，这也能正常工作。但是我现在的问题是，如果在一列中，所有行都是 NULL，则不会写入任何空属性。因此，如果我将 XML 读回 DataTable，它会缺少此列...

如何强制写入所有列，即使它们是空的？
(数据类型不一定是字符串)

public void ExportTable(string strDirectory, DataTable dtt)
{
    using (System.Data.DataSet ds = new System.Data.DataSet()) {
        string strTable = dtt.TableName;

        ds.Tables.Add(dtt);
        ds.DataSetName = strTable;

        // Move data to attributes 
        foreach (DataTable dt in ds.Tables) {

            foreach (DataColumn dc in dt.Columns) {
                dc.ColumnMapping = MappingType.Attribute;
            }

        }

        System.Xml.XmlWriterSettings settings = new System.Xml.XmlWriterSettings();
        settings.Indent = true;
        //settings.Encoding = System.Text.Encoding.GetEncoding("ISO-8859-1") 
        settings.Encoding = System.Text.Encoding.UTF8;
        settings.CloseOutput = true;
        settings.CheckCharacters = true;
        settings.NewLineChars = "\r\n";
        // vbCr & vbLf 

        // Write as UTF-8 with indentation 
        using (System.Xml.XmlWriter w = System.Xml.XmlWriter.Create(System.IO.Path.Combine(strDirectory, strTable + ".xml"), settings)) {

            // Strip out timezone 
            foreach (DataTable dt in ds.Tables) {

                foreach (DataColumn dc in dt.Columns) {

                    if (object.ReferenceEquals(dc.DataType, typeof(DateTime))) {
                        dc.DateTimeMode = DataSetDateTime.Unspecified;
                    }

                }

            }

            ds.Tables[0].WriteXml(w, XmlWriteMode.IgnoreSchema);
            w.Flush();
            w.Close();
        }
        // w 

    }
    // ds 

}
// ExportTable 

VB.NET 原文:

 Public Sub ExportTable(strDirectory As String, dtt As DataTable)
        Using ds As New System.Data.DataSet()
            Dim strTable As String = dtt.TableName

            ds.Tables.Add(dtt)
            ds.DataSetName = strTable

            ' Move data to attributes
            For Each dt As DataTable In ds.Tables

                For Each dc As DataColumn In dt.Columns
                    dc.ColumnMapping = MappingType.Attribute
                Next dc

            Next dt

            Dim settings As New System.Xml.XmlWriterSettings()
            settings.Indent = True
            'settings.Encoding = System.Text.Encoding.GetEncoding("ISO-8859-1")
            settings.Encoding = System.Text.Encoding.UTF8
            settings.CloseOutput = True
            settings.CheckCharacters = True
            settings.NewLineChars = vbCrLf ' vbCr & vbLf

            ' Write as UTF-8 with indentation
            Using w As System.Xml.XmlWriter = System.Xml.XmlWriter.Create(System.IO.Path.Combine(strDirectory, strTable & ".xml"), settings)

                ' Strip out timezone
                For Each dt As DataTable In ds.Tables

                    For Each dc As DataColumn In dt.Columns

                        If dc.DataType Is GetType(DateTime) Then
                            dc.DateTimeMode = DataSetDateTime.Unspecified
                        End If

                    Next dc

                Next dt

                ds.Tables(0).WriteXml(w, XmlWriteMode.IgnoreSchema)
                w.Flush()
                w.Close()
            End Using ' w

        End Using ' ds

    End Sub ' ExportTable

Answer 1

必须为每个 XML 属性分配一个值，该值用一对单引号或双引号括起来。纯文本中没有等效项来表示 NULL 值。一对没有值的引号表示空字符串与 NULL 值不同。因此，表示 NULL 属性的唯一方法是省略该属性。

这意味着您需要将 AllowDBNull 设置为 false 并在 DataColumn 上分配合适的 DefaultValue，或者包括架构。

另请参阅 Handling Null Values (ADO.NET). ，特别是解释行为的这一部分:

In addition, the following rules apply for an instance of DataRow.["columnName"] null assignments:

1.The default default value is DbNull.Value for all except the strongly typed null columns where it is the appropriate strongly typed null value.

2.Null values are never written out during serialization to XML files (as in "xsi:nil").

3.All non-null values, including defaults, are always written out while serializing to XML. This is unlike XSD/XML semantics where a null value (xsi:nil) is explicit and the default value is implicit (if not present in XML, a validating parser can get it from an associated XSD schema). The opposite is true for a DataTable: a null value is implicit and the default value is explicit.

4.All missing column values for rows read from XML input are assigned NULL. Rows created using NewRow or similar methods are assigned the DataColumn's default value.

5.The IsNull method returns true for both DbNull.Value and INullable.Null.