0

I'm trying to return the results of a SQL query to a RESTFUL endpoint. I'm unsure of how to convert the results of the query into a JSON object. In SQL 2016 there is a 'USING JSON' feature to do the conversion for you. However in some cases we don't have SQL 2016. What is the most efficient way of converting to JSON? I've done some research and it looks like JavaScriptSerializer is recommended. However I'm unfamiliar with the distinctions between my object type structure of a Recordset vs a Datatable.

using ADODB;       

Object SQL = new Object();
ADODB.Recordset rs = new Recordset();

SQL.ExecRecordSet("Select * from table", ref rs);

while (rs.EOF == false)
{
  foreach (ADODB.Field field in rs.Fields)
  {

    if (field.Value != null)
    {
       Debug.Print(field.Name.ToString());
       Debug.Print(field.Value.ToString());
    }


  }
  rs.MoveNext();
}
node943034835
  • 53
  • 7
  • 1
    Why are you using ADODB? If you are coming from vba/access then you should know there are modern alternatives. Regarding json, look at Newtonsoft. – Crowcoder Mar 11 '20 at 22:24
  • You're asking a great question. I don't make the constraints unfortunately it's a legacy system I'm inheriting. – node943034835 Mar 11 '20 at 22:52
  • [tag:json.net] is better and more flexible than `JavaScriptSerializer` -- which is in fact deprecated in favor of Json.NET. – dbc Mar 11 '20 at 22:54
  • 2
    I'm not familiar with `Recordset` but It seems like it may be possible to convert one to a `DataTable`, as explained in [Working with ADODB recordset](https://stackoverflow.com/q/17387930). If so you can serialize it to JSON with [tag:json.net] as data sets and data tables are supported out of the box, see https://www.newtonsoft.com/json/help/html/SerializeDataSet.htm. – dbc Mar 11 '20 at 23:01
  • 1
    A `Recordset` is really for record-by-record access to a set of records. It is not serializable. As @dbc mentions you can fill a `DataTable` or `DataSet` with a `Recordset`. Those types are serializable. – Heretic Monkey Mar 11 '20 at 23:21

0 Answers0