C# Serializing DataTable result returned from sqlite is returing XML format

Question

I'm using .net core(2.1) web application. I'm implementing the web API where it will connect to SQLite DB and get the data and return the query result. Here is the code that I have used.

 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]);
                }
            }

previously I have worked with SQL server and ASP.NET where I will get the JSON during serialization in the format of rows and columns. As below

Now I'm also getting some metadata in XML along with the actual data. I'm attaching the serialized data below.

{"DataTable.RemotingVersion":{"Major":2,"Minor":0,"Build":-1,"Revision":-1,"MajorRevision":-1,"MinorRevision":-1},"XmlSchema":"

  
    
      
        
        
        
        
      
    
  
  
    
      
    
  
","XmlDiffGram":"
  
    11nithin5000CSE
      
      
      
      
    
  
"}

How can I avoid these other data and only get the actual query result (rows & columns) during serialization?

user_5520131 · Answer

This should do the trick:

Parsing classes:

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; }
}

And usage:

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

C# Serializing DataTable result returned from sqlite is returing XML format

1 Answers