c# - 从sql返回的C#序列化数据表结果返回XML格式

我正在使用.net core（2.1）Web应用程序。我正在实现Web API，它将连接到SQLite DB并获取数据并返回查询结果。这是我使用的代码。

 using (var connection = new SQLiteConnection("Data Source=D:/SQL-Lite/xxxxx/test.db;Version=3;")) {
                connection.Open();

                using (var transaction = connection.BeginTransaction())
                {
                    var selectCommand = connection.CreateCommand();
                    selectCommand.Transaction = transaction;
                    selectCommand.CommandText = "SELECT * FROM Employee";
                    var ds = new DataSet();
                    var da = new SQLiteDataAdapter(selectCommand);
                    da.Fill(ds);

                    if (ds.Tables != null && ds.Tables.Count > 0)
                        response = JsonConvert.SerializeObject(ds.Tables[0]);
                }
            }

以前，我曾使用过SQL Server和ASP.NET，在序列化期间我将以行和列的格式获取JSON。如下



现在，我还获得了XML中的一些元数据以及实际数据。我将在下面附加序列化数据。

{"DataTable.RemotingVersion":{"Major":2,"Minor":0,"Build":-1,"Revision":-1,"MajorRevision":-1,"MinorRevision":-1},"XmlSchema":"<?xml version=\"1.0\" encoding=\"utf-16\"?>\r\n<xs:schema xmlns=\"\" xmlns:xs=\"http://www.w3.org/2001/XMLSchema\" xmlns:msdata=\"urn:schemas-microsoft-com:xml-msdata\">\r\n  <xs:element name=\"Table\">\r\n    <xs:complexType>\r\n      <xs:sequence>\r\n        <xs:element name=\"Id\" type=\"xs:long\" msdata:targetNamespace=\"\" minOccurs=\"0\" />\r\n        <xs:element name=\"FirstName\" type=\"xs:string\" msdata:targetNamespace=\"\" minOccurs=\"0\" />\r\n        <xs:element name=\"Salary\" type=\"xs:double\" msdata:targetNamespace=\"\" minOccurs=\"0\" />\r\n        <xs:element name=\"Department\" type=\"xs:string\" msdata:targetNamespace=\"\" minOccurs=\"0\" />\r\n      </xs:sequence>\r\n    </xs:complexType>\r\n  </xs:element>\r\n  <xs:element name=\"tmpDataSet\" msdata:IsDataSet=\"true\" msdata:MainDataTable=\"Table\" msdata:UseCurrentLocale=\"true\">\r\n    <xs:complexType>\r\n      <xs:choice minOccurs=\"0\" maxOccurs=\"unbounded\" />\r\n    </xs:complexType>\r\n  </xs:element>\r\n</xs:schema>","XmlDiffGram":"<diffgr:diffgram xmlns:msdata=\"urn:schemas-microsoft-com:xml-msdata\" xmlns:diffgr=\"urn:schemas-microsoft-com:xml-diffgram-v1\">\r\n  <tmpDataSet>\r\n    <Table diffgr:id=\"Table1\" msdata:rowOrder=\"0\">\r\n      <Id>11</Id>\r\n      <FirstName>nithin</FirstName>\r\n      <Salary>5000</Salary>\r\n      <Department>CSE</Department>\r\n    </Table>\r\n  </tmpDataSet>\r\n</diffgr:diffgram>"}

如何避免这些其他数据，而仅在序列化期间获取实际的查询结果（行和列）？

最佳答案

这应该可以解决问题：

解析类：

public class Response
{
    [JsonProperty("DataTable.RemotingVersion")]
    public object RemotingVersion { get; set; }

    public string XmlSchema { get; set; }

    public string XmlDiffGram { get; set; }
}

public class Table
{
    public int Id { get; set; }
    public string FirstName { get; set; }
    public int Salary { get; set; }
    public string Department { get; set; }
}

和用法：

var res = JsonConvert.DeserializeObject<Response>(response);
var doc = XDocument.Parse(res.XmlDiffGram);
var table = doc.Root.XPathSelectElement("tmpDataSet/Table");
var data = new XmlSerializer(typeof(Table)).Deserialize(table.CreateReader());