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