C# 如何使用所有列作为属性将数据表导出到 Xml？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10231621/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to export a DataTable to Xml with ALL columns as attributes?
提问by Stefan Steiger
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...
问题：我正在将 System.Data.DataTable 导出为 XML。到目前为止它工作正常。但我想拥有属性中的所有数据，这也很好用。但是我现在的问题是，如果在一列中，所有行都为 NULL，则不会写入空属性。因此，如果我将 XML 读回 DataTable，它将缺少此列...
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:
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
采纳答案by JamieSee
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.
必须为每个 XML 属性分配一个值，该值用一对单引号或双引号括起来。纯文本中没有等效项来表示 NULL 值。表示空字符串的一对没有值的引号与 NULL 值不同。因此，表示 NULL 属性的唯一方法是省略该属性。
This means that you will need to either set AllowDBNullto false and assign a suitable DefaultValueon the DataColumn, or include the schema.
这意味着您需要设置AllowDBNull为 false 并DefaultValue在 DataColumn 上分配合适的值，或者包含架构。
Also, see Handling Null Values (ADO.NET)., particularly this section which explains the behavior:
另请参阅处理空值 (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.
此外，以下规则适用于 DataRow.["columnName"] 空赋值的实例：
1.默认默认值是 DbNull.Value，除强类型空列之外的所有列都是适当的强类型空值。
2.Null 值在序列化到 XML 文件期间永远不会写出（如“xsi:nil”）。
3.所有非空值，包括默认值，在序列化为 XML 时总是被写出。这与 XSD/XML 语义不同，在 XSD/XML 语义中，空值 (xsi:nil) 是显式的，默认值是隐式的（如果 XML 中不存在，验证解析器可以从关联的 XSD 模式中获取它）。DataTable 的情况正好相反：空值是隐式的，默认值是显式的。
4. 从 XML 输入读取的行的所有缺失列值都被分配为 NULL。使用 NewRow 或类似方法创建的行被分配了 DataColumn 的默认值。
5.IsNull 方法对 DbNull.Value 和 INullable.Null 都返回 true。
回答by Raj Ranjhan
Try setting the column DefaultValueto something valid
尝试将列DefaultValue设置为有效的值
foreach (DataTable dt in ds.Tables) {
foreach (DataColumn dc in dt.Columns) {
dc.ColumnMapping = MappingType.Attribute;
//If type is DataType string
dc.DefaultValue = String.Empty;
}
回答by Jakester26
Two points:
两点：
First: The ExportTable() threw an exception: "DataTable already belongs to another DataSet." When I executed:
首先：ExportTable() 抛出异常：“DataTable 已经属于另一个 DataSet。” 当我执行：
ds.Tables.Add(dtt)
I corrected this by making a local copy of the table:
我通过制作表格的本地副本来纠正这个问题：
Dim dtX As DataTable = dtt.Copy
ds.Tables.Add(dtX)
ds.DataSetName = strTable
This worked well.
这工作得很好。
Second: If you use the XML to create a dynamic SQL statement, there is no need to be concerned about the columns/fields with NULL value being omitted in the XML export. Simply walk through the a attributes that are in the XML record, build the INSERT or UPDATE statement and execute a connection command. This is faster than using a DataSet.
第二：如果您使用XML创建动态SQL语句，则无需担心XML导出中省略了NULL值的列/字段。只需遍历 XML 记录中的属性，构建 INSERT 或 UPDATE 语句并执行连接命令。这比使用 DataSet 更快。
For INSERT it has one drawback. If the primary key is created by incrementing an identity column, an ADO.Net DataSet will return it. Dynamic SQL will require a SELECT statement to retrieve it.
对于 INSERT，它有一个缺点。如果主键是通过增加标识列创建的，则 ADO.Net 数据集将返回它。动态 SQL 将需要一个 SELECT 语句来检索它。
Also, it would be good idea to obfuscate your code.
此外，混淆您的代码是个好主意。
回答by Circus Ranger
It's a bit old thread but, maybe it can help someone:
If you are not writing big xml files frequently (it's ok for exporting settings or something similar) you can use function below, otherwise it's better to use cutom xml schema.
这是一个有点旧的线程，但是，也许它可以帮助某人：
如果您不经常编写大的 xml 文件（可以导出设置或类似的东西），您可以使用下面的函数，否则最好使用 cutom xml 模式。
private static void addEmptyElementsToXML(DataSet dataSet)
{
foreach (DataTable dataTable in dataSet.Tables)
{
foreach (DataRow dataRow in dataTable.Rows)
{
for (int j = 0; j < dataRow.ItemArray.Length; j++)
{
if (dataRow.ItemArray[j] == DBNull.Value)
dataRow.SetField(j, string.Empty);
}
}
}
}
Usage:
用法：
using(DataTable dTable = ..something..)
using(DataSet dS = new DataSet())
using(XmlTextWriter xmlStream = new XmlTextWriter("FILENAME.XML", Encoding.UTF8))
{
//set xml to be formatted so it can be easily red by human
xmlStream.Formatting = Formatting.Indented;
xmlStream.Indentation = 4;
//add table to dataset
dS.Tables.Add(dTable);
//call the mentioned function so it will set all DBNull values in dataset
//to string.Empty
addEmptyElementsToXML(dS);
//write xml to file
xmlStream.WriteStartDocument();
dS.WriteXml(xmlStream);
}
回答by Rajarshi
I have been searching the whole world for a solution of writing null fields to XML using DataSet.WriteXML(). I found that following works in a performance optimized way. I have created a function for your convenience. Change your dataset tables one after the other by calling the following function and replacing the tables.
我一直在全世界寻找使用 DataSet.WriteXML() 将空字段写入 XML 的解决方案。我发现以下以性能优化的方式工作。为了您的方便，我创建了一个函数。通过调用以下函数并替换表一个接一个地更改数据集表。
private DataTable GetNullFilledDataTableForXML(DataTable dtSource)
{
// Create a target table with same structure as source and fields as strings
// We can change the column datatype as long as there is no data loaded
DataTable dtTarget = dtSource.Clone();
foreach (DataColumn col in dtTarget.Columns)
col.DataType = typeof(string);
// Start importing the source into target by ItemArray copying which
// is found to be reasonably fast for null operations. VS 2015 is reporting
// 500-525 milliseconds for loading 100,000 records x 10 columns
// after null conversion in every cell
// The speed may be usable in many circumstances.
// Machine config: i5 2nd Gen, 8 GB RAM, Windows 7 64bit, VS 2015 Update 1
int colCountInTarget = dtTarget.Columns.Count;
foreach (DataRow sourceRow in dtSource.Rows)
{
// Get a new row loaded with data from source row
DataRow targetRow = dtTarget.NewRow();
targetRow.ItemArray = sourceRow.ItemArray;
// Update DBNull.Values to empty string in the new (target) row
// We can safely assign empty string since the target table columns
// are all of string type
for (int ctr = 0; ctr < colCountInTarget; ctr++)
if (targetRow[ctr] == DBNull.Value)
targetRow[ctr] = String.Empty;
// Now add the null filled row to target datatable
dtTarget.Rows.Add(targetRow);
}
// Return the target datatable
return dtTarget;
}
