丢失纬度，经度和其他元素，带有SqlGeography列的数据表无法正确序列化为xml [英] Datatable with SqlGeography column can't be serialized to xml correctly with loss of Lat,Long and other elements
问题描述
我试图将DataTable对象序列化为xml. DataTable的列类型为'geography'，其中包含类型为
I tried to serialize a DataTable object to xml. The DataTable has a column with type 'geography', which contains instances of type SqlGeography.
以下代码用于将数据表序列化为xml:
The following code is used to serialize the datatable to xml:
var writer = new StringWriter();
dt.WriteXmlSchema(writer); //get schema
//serialize to xml
dt.WriteXml(writer);
Console.WriteLine(writer.ToString());
生成的xml字符串不完整，丢失了所有地理元素Lat，Long，...
The generated xml string is not complete with the loss of all Geo elements Lat,Long,...
它仅包含STSrid元素.
It contains only STSrid element.
以下是生成的xml字符串:
The following is the generated xml string:
<table>
<f1 xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<STSrid>4326</STSrid>
</f1>
<id>1</id>
</table>
<table>
<f1 xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<STSrid>4326</STSrid>
</f1>
<id>2</id>
</table>
这意味着您不能使用xml进行反序列化，并且会丢失SqlGeography数据.
This means that you can't use the xml for deserialization, and you loose the SqlGeography data.
该架构已正确生成:
<xs:schema id="NewDataSet" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
<xs:element name="NewDataSet" msdata:IsDataSet="true" msdata:MainDataTable="table" msdata:UseCurrentLocale="true">
<xs:complexType>
<xs:choice minOccurs="0" maxOccurs="unbounded">
<xs:element name="table">
<xs:complexType>
<xs:sequence>
<xs:element name="f1" msdata:DataType="Microsoft.SqlServer.Types.SqlGeography, Microsoft.SqlServer.Types, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" type="xs:anyType" minOccurs="0" />
<xs:element name="id" type="xs:int" minOccurs="0" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:choice>
</xs:complexType>
</xs:element>
</xs:schema>
您可以在线找到完整的C#程序来显示问题.
You can find the complete C# program online to show the problem.
我的问题:
1)我想为SqlGeography列获取有效的xml序列化而错过了什么?
1) What I missed to get a valid xml serialization for SqlGeography column?
2)DataTable.WriteXml方法中的错误不是处理复杂对象的问题吗?
2) Is't a bug in the DataTable.WriteXml method to handle a complex object?
推荐答案
DataTable.WriteXml()在内部使用 Lat 和 Long 都可以-和 SqlGeography 不会无法实施 IXmlSerializable ，这是行不通的，因为XmlSerializer不会序列化仅获取属性.
DataTable.WriteXml() internally uses XmlSerializer to serialize complex column values. And since Lat and Long are both get-only, and SqlGeography doesn't implement IXmlSerializable, there's no way this is going to work, since XmlSerializer doesn't serialize get-only properties.
相反，您需要用一些适当的数据传输对象替换SqlGeography的实例以便对其进行序列化.但是，该DTO应该包含什么以及应该如何创建?有几种选择:
Instead, you're going to need to replace instances of SqlGeography with some appropriate data transfer object in order to serialize it. But, what should that DTO contain and how should it be created? There are several options:
-
使用
SqlGeography.AsGml()和
Manually convert to and from GML using
SqlGeography.AsGml()andSqlGeography.GeomFromGml().
在这种情况下，您的DTO看起来像这样:
In this scenario, your DTO would look like something this:
public class SqlGeographyDTO
{
const int DefaultSRID = 4326; // TODO: check if this is the correct default.
public int? STSrid { get; set; }
public XElement Geography { get; set; }
public static implicit operator SqlGeographyDTO(SqlGeography geography)
{
if (geography == null || geography.IsNull)
return null;
return new SqlGeographyDTO
{
STSrid = geography.STSrid.IsNull ? (int?)null : geography.STSrid.Value,
Geography = geography.AsGml().ToXElement(),
};
}
public static implicit operator SqlGeography(SqlGeographyDTO dto)
{
if (dto == null)
return SqlGeography.Null;
var sqlXml = dto.Geography.ToSqlXml();
var geography = SqlGeography.GeomFromGml(sqlXml, dto.STSrid.GetValueOrDefault(DefaultSRID));
return geography;
}
public override string ToString()
{
return Geography == null ? "" : Geography.ToString(SaveOptions.DisableFormatting);
}
}
public static class XNodeExtensions
{
public static SqlXml ToSqlXml(this XNode node)
{
if (node == null)
return SqlXml.Null;
using (var reader = node.CreateReader())
{
return new SqlXml(reader);
}
}
}
public static class SqlXmlExtensions
{
public static XElement ToXElement(this SqlXml sql)
{
if (sql == null || sql.IsNull)
return null;
using (var reader = sql.CreateReader())
return XElement.Load(reader);
}
}
由于GML是基于XML的格式，因此任何XML解析器都可以解析结果.但是请注意，据记载，从GML到GML的转换并不精确.
Since GML is an XML-based format the result will be parsable by any XML parser. Note, however, that conversion from and to GML is documented not to be precise.
正在使用 .Net小提琴.
(顺便说一句:AsGml()返回类型为 SqlXml.WriteXml() 似乎有一个错误:即使将XML声明为外部容器元素的嵌套子元素，也始终包含XML声明，因此，生成的序列化XML将是不一致的，并且会被破坏.不需要的声明.)
(As an aside: AsGml() returns an object of type SqlXml which implements IXmlSerializable, so it would seem possible to include the returned SqlXml directly in the DTO. Unfortunately, testing reveals that either AsGml() or SqlXml.WriteXml() seem to have a bug: an XML declaration is always included, even when the XML is being written as a nested child element of an outer container element. Thus the resulting, serialized XML will be nonconformant and broken. Parsing to an intermediate XElement avoids this bug by stripping the unwanted declaration.)
Manually convert to and from Open Geospatial Consortium (OGC) Well-Known Text (WKT) representation augmented with any Z (elevation) and M (measure) values by using ToString() with SqlGeography.STGeomFromText().
在此实现中，您的DTO类似于:
In this implementation your DTO would look something like:
public class SqlGeographyDTO
{
const int DefaultSRID = 4326; // TODO: check if this is the correct default.
public int? STSrid { get; set; }
public string Geography { get; set; }
public static implicit operator SqlGeographyDTO(SqlGeography geography)
{
if (geography == null || geography.IsNull)
return null;
return new SqlGeographyDTO
{
STSrid = geography.STSrid.IsNull ? (int?)null : geography.STSrid.Value,
Geography = geography.ToString(),
};
}
public static implicit operator SqlGeography(SqlGeographyDTO dto)
{
if (dto == null)
return SqlGeography.Null;
var geography = SqlGeography.STGeomFromText(new SqlChars(dto.Geography), dto.STSrid.GetValueOrDefault(DefaultSRID));
return geography;
}
}
同样，此实现可能会失去精度，但具有可与许多格式和序列化器(包括JSON和Json.NET)一起使用的优点.这似乎是
Again this implementation might lose precision, but has the advantage of being usable with many formats and serializers including JSON and Json.NET. This seems to be the approach used by ServiceStack.OrmLite.SqlServer.Converters/SqlServerGeographyTypeConverter, for instance.
正在工作 .net小提琴由@ M.Hassan提供.
Working .Net fiddle courtesy of @M.Hassan.
使用 SqlGeography.Read (BinaryReader) .
在此实现中，您的DTO类将包含public byte [] Geography { get; set; }.
In this implementation your DTO class would contain public byte [] Geography { get; set; }.
已记录这些方法不会失去精度，但是由于二进制文件是不透明的，因此您将无法记录交换格式.这似乎是 Dapper.EntityFramework.DbGeographyHandler .但是请注意，Dapper正在调用 STAsBinary() ，根据文档，它返回的值将不包含实例携带的任何Z或M值.
These methods are documented not to lose precision, but since the binary is opaque you won't be able to document your interchange format. This seems to be the approach used by Dapper.EntityFramework.DbGeographyHandler. Note however that Dapper is calling STAsBinary(), which, according to the documentation, returns a value that will not contain any Z or M values carried by the instance.
拥有DTO后，您可以使用以下方法之一来替换当前的DataTable中的DTO，例如:如何更改数据类型数据表中的数据列?.
Once you have your DTO, you could replace it in your current DataTable using one of the answers from, e.g., How To Change DataType of a DataColumn in a DataTable?.
注意-上面的DTO尚未经过全面测试.
Note - above DTOs are not fully tested.
这篇关于丢失纬度，经度和其他元素，带有SqlGeography列的数据表无法正确序列化为xml的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
