public SQLBrokerError(string message, Exception innerException = null,
ConnectionParams connection = null, SQLResult sqlResult = null, BOResult boResult = null) : base(message, innerException)
{
config = SAPB1.brokerConf;
this.boResult = boResult;
this.sqlResult = sqlResult;
this.connection = new NoPwdConnectionParams(connection);
if (this.connection == null && boResult?.connection != null)
{
this.connection = boResult?.connection;
}
if (this.connection == null && sqlResult?.connection != null)
{
this.connection = sqlResult?.connection;
}
}
static string getUserQuerySQL(SqlConnection t, SQLQuery q, ConnectionParams cp)
{
using (SqlCommand uq = new SqlCommand {
Connection = t
}) {
uq.CommandText = getUQCommandText(q.userQuery, q.uqCategory + UQCATEGORY_EXTENSION);
string sqlString = uq.ExecuteScalar()?.ToString();
if (!string.IsNullOrEmpty(sqlString)) // We have found an customized/extended version, which has priority
{
q.uqCategory += UQCATEGORY_EXTENSION;
}
else
{
uq.CommandText = getUQCommandText(q.userQuery, q.uqCategory);
}
using (SqlDataReader rs = uq.ExecuteReader(System.Data.CommandBehavior.Default)) {
if (rs.Read()) //found
{
string SQL = rs.GetString(rs.GetOrdinal("QString"));
string catName = rs.GetString(rs.GetOrdinal("CatName"));
if (!string.IsNullOrEmpty(brokerConf.exposedUQCategories))
{
if (!brokerConf.exposedUQCategories.ToLower().Contains(q.userQuery.ToLower()))
{
throw new Exception($"Category {q.userQuery} is not exposed in {brokerConf.exposedUQCategories}");
}
}
//Substitute parameters
for (int i = 0; i < q.parameters.Length; i++)
{
SQL = SQL.Replace($"[%{i}]", q.parameters[i]);
}
if (!string.IsNullOrEmpty(q.lang))
{
SQL = SQL.Replace($"[lang]", q.lang);
}
return(SQL);
}
else
{
throw new Exception($"User Query {q.userQuery} not found");
}
}
}
}
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);
}
}
}
}
/// <summary>
/// Returns connection parameter values from Web.config
/// <configuration>
/// <configSections>
/// <section name = "SQLBrokerDefault" type="System.Configuration.SingleTagSectionHandler"/>
/// </configSections>
/// <SQLBrokerDefault CompanyDB = "SBODemoUS" Server="MIKISURFACE" LicenseServer="MIKISURFACE:30000"
/// SLDServer="MIKISURFACE:40000" DbUserName="******" DbPassword="******" UserName="******" Password="******"
/// DbServerType="MSSQL2016"/>
/// ....
/// </configuration>
/// </summary>
/// <param name="profile"></param>
/// <returns></returns>
public static ConnectionParams GetConnectionProfile(string profile)
{
if (string.IsNullOrEmpty(profile))
{
throw new Exception("No conf was found for " + profile);
}
var conf = (System.Collections.Hashtable)System.Configuration.ConfigurationManager.GetSection(profile);
if (conf != null)
{
var cp = new ConnectionParams {
Profile = profile,
CompanyDB = conf["CompanyDB"]?.ToString(),
Server = conf["Server"]?.ToString(),
LicenseServer = conf["LicenseServer"]?.ToString(),
SLDServer = conf["SLDServer"]?.ToString(),
DbUserName = conf["DbUserName"]?.ToString(),
DbPassword = conf["DbPassword"]?.ToString(),
UserName = conf["UserName"]?.ToString(),
Password = conf["Password"]?.ToString(),
DbServerType = conf["DbServerType"]?.ToString(),
AdoNetUser = conf["AdoNetUser"]?.ToString(),
AdoNetPassword = conf["AdoNetPassword"]?.ToString(),
};
if (string.IsNullOrEmpty(cp.AdoNetUser)) //If no dedicated separate user defined for ADO.NET, just use the default db user
{
cp.AdoNetUser = cp.DbUserName;
cp.AdoNetPassword = cp.DbPassword;
}
cp.UseTrusted = string.IsNullOrEmpty(cp.AdoNetUser); //If no user defined trusted connection via Windows Authentication is used
return(cp);
}
else
{
throw new Exception("No conf was found for " + profile);
}
}
public static IConnRef startTransaction(ConnectionParams cp)
{
Me.Connect(cp);
return(new ConnRef(Me));
}