static void SQLDIQuery(SQLQuery q, SQLResult result, ConnectionParams cp)
{
using (var t = DIConnection.startTransaction(cp)) { //Must be used with using !!!
SAPbobsCOM.Recordset rs = t.company.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset);
rs.DoQuery(q.SQL);
result.statusCode = System.Net.HttpStatusCode.OK;
//These mustn't be called since we get a transaction error
//result.errorCode = t.company.GetLastErrorCode();
//result.errorText = t.company.GetLastErrorDescription();
string xmlText = rs.GetAsXML();
if (q.rawXml)
{
result.rawXml = xmlText;
}
System.Xml.XmlDocument xmlDoc = new System.Xml.XmlDocument();
xmlDoc.LoadXml(xmlText);
string jsonText = Newtonsoft.Json.JsonConvert.SerializeXmlNode(xmlDoc, Newtonsoft.Json.Formatting.Indented, false);
result.data = Newtonsoft.Json.Linq.JToken.Parse(jsonText);
if (q.columnInfo)
{
int cc = rs.Fields.Count;
SAPbobsCOM.Fields fields = rs.Fields;
for (int i = 0; i < cc; i++)
{
SAPbobsCOM.Field f = fields.Item(i);
SQLResult.Column column = new SQLResult.Column();
column.name = f.Name;
column.dataType = f.Type.ToString();
column.subType = f.SubType.ToString();
//column.description = f.Description;
SAPbobsCOM.ValidValues vvs = f.ValidValues;
int vvc = vvs.Count;
for (int k = 0; k < vvc; k++)
{
SAPbobsCOM.ValidValue v = vvs.Item(k);
column.validValues.Add(new SQLResult.ValidValue {
value = v.Value, description = v.Description
});
}
result.columns.Add(column);
}
}
}
}
