static void SQLADONETQuery(SQLQuery q, SQLResult result, ConnectionParams cp, bool rollbackOnly)
{
using (SqlConnection t = new SqlConnection(cp.getConnectionString())) //{ t.Open();
using (SqlCommand sqlCommand = new SqlCommand {
Connection = t
}) {
sqlCommand.Connection.Open();
if (!string.IsNullOrEmpty(q.userQuery))
{
if (!string.IsNullOrEmpty(q.SQL))
{
throw new Exception("SQL must be empty for a user query request");
}
q.SQL = getUserQuerySQL(t, q, cp);
checkRollbackOnlyConditions(result.rollbackOnly, q.SQL);
}
result.SQL = q.SQL;
result.userQuery = !string.IsNullOrEmpty(q.userQuery) ? q.uqCategory + "." + q.userQuery : null;
result.extendedUQ = q.uqCategory != null && q.uqCategory.EndsWith(UQCATEGORY_EXTENSION);
sqlCommand.CommandText = q.SQL;
using (SqlDataReader rs = sqlCommand.ExecuteReader(System.Data.CommandBehavior.Default)) {
result.statusCode = System.Net.HttpStatusCode.OK;
var data = new Newtonsoft.Json.Linq.JArray();
do
{
while (rs.Read())
{
var row = new Newtonsoft.Json.Linq.JObject();
for (int i = 0; i < rs.FieldCount; i++)
{
row.Add(rs.GetName(i), rs.GetValue(i).ToString());
}
data.Add(row);
}
result.data = data;
if (q.columnInfo)
{
int cc = rs.FieldCount;
for (int i = 0; i < cc; i++)
{
SQLResult.Column column = new SQLResult.Column();
column.name = rs.GetName(i);
column.dataType = rs.GetDataTypeName(i);
//column.subType = ;//Hmm, What to give here?
result.columns.Add(column);
}
}
} while (rs.NextResult());
}
}
}
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);
}
}
}
}