C# 序列化从 sqlite 返回的 DataTable 结果正在返回 XML 格式

Question

我正在使用 .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>"}

如何在序列化期间避免这些其他数据并仅获取实际查询结果(行和列)？

Answer 1

这应该可以解决问题:

解析类:

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());