如何将DataTable导出为Xml与ALL列作为属性? [英] How to export a DataTable to Xml with ALL columns as attributes?
问题描述
到目前为止,它的工作正常。
但是我想要把所有的数据都放在属性中,这样也很好。
但现在我的问题是,如果在一列中,所有行都为NULL,则不会写入任何空的属性。
所以如果我把XML读回到一个DataTable,它缺少这个列...
如何强制写入所有的列,即使它们是空的?
(DataType不必要的字符串)
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;
//将数据移动到属性
foreach(ds.Tables中的DataTable dt){
foreach(dt.Columns中的DataColumn dc){
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\\\
;
// vbCr& vbLf
//写入UTF-8缩进
使用(System.Xml.XmlWriter w = System.Xml.XmlWriter.Create(System.IO.Path.Combine(strDirectory,strTable +.xml),设置)){
//剥离时区
foreach(ds.Tables中的DataTable dt){
foreach(DataColumn dc在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)
使用ds作为新的System.Data.DataSet()
Dim strTable As String = dtt.TableName
ds .Tables.Add(dtt)
ds.DataSetName = strTable
'将数据移动到属性
对于每个dt作为DataTable在ds.Tables
对于每个dc作为DataColumn在dt.Columns
dc.ColumnMapping = MappingType.Attribute
下一个dc
下一个dt
Dim设置为新系统。 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
'以UTF-8写入缩进
使用w As System.Xml.XmlWriter = System.Xml.XmlWriter.Create(System.IO.Path.Combine(strDirectory,strTable& amp ;.xml),设置)
'剥离时区
对于每个dt作为DataTable在ds.Tables
对于每个dc作为DataColumn在dt。列
如果dc.DataType是GetType(DateTime)然后
dc.DateTimeMode = DataSetDateTime.Unspecified
如果
下一个dc
下一个dt
ds.Tables(0).WriteXml(w,XmlWriteMode.IgnoreSchema)
w.Flush()
w.Close()
结束使用'w
结束使用'ds
End Sub'ExportTable
每个XML属性都必须被分配一个包含在一对单引号或双引号中的值。在纯文本中没有等同的值来表示NULL值。一个没有值的引号用于表示空字符串与NULL值不同。因此,表示NULL属性的唯一方法是省略属性。
这意味着您需要设置 AllowDBNull
为false,并在DataColumn上分配一个合适的 DefaultValue
,或者包含模式。
此外,请参阅处理空值(ADO.NET),特别是本节中解释行为:
此外,以下规则适用于
DataRow的实例。[columnName]空分配:
1.默认默认值为DbNull.Value,除了强类型的空列之外,它是适当的强类型
空值。 p>
2.在序列化期间,空值不会在XML文件中写出(如xsi:nil)。
3.所有非空值(包括默认值)始终在serializi中写出ng到XML。这与XSD / XML语义不同,其中
空值(xsi:nil)是显式的,默认值是隐式的(如果
不存在于XML中,则验证解析器可以从相关联的
XSD模式)。对于DataTable,相反的是:一个空值为
隐式,默认值为显式。
4.从XML读取的行的所有缺少的列值输入被分配NULL。使用NewRow或类似方法创建的行将分配
DataColumn的默认值。
5.DnNull方法对DbNull.Value和INullable.Null都返回true。
Question: I'm exporting a System.Data.DataTable to XML. So far it works fine. But I want to have all the data in attributes, which works fine as well. But my problem now, if in one column, all rows are NULL, no empty attributes are written. So if I read the XML back to a DataTable, it lacks this column...
How can I force write all columns even when they are empty ?
(DataType not necessarely string)
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 original:
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
Every XML attribute must be assigned a value that is enclosed in a pair of single or double quotation marks. There is no equivalent in plain text to denote a NULL value. A pair of quotation marks with no value to represent an empty string is not the same as a NULL value. Therefore, the only way to represent a NULL attribute is to omit the attribute.
This means that you will need to either set AllowDBNull
to false and assign a suitable DefaultValue
on the DataColumn, or include the schema.
Also, see Handling Null Values (ADO.NET)., particularly this section which explains the behavior:
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.
这篇关于如何将DataTable导出为Xml与ALL列作为属性?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!