private string FormatSQLFormula(string sqlFormula, ref SqlCommandTypes sqlCommandType, ref string connectionStringKeyName, ref string errorMessage) { bool invalidFormula = false; if ((!sqlFormula.Contains("(")) || (!sqlFormula.Contains("(")) || (!sqlFormula.Contains("|"))) { errorMessage = string.Format("Error while parasing data, SQL query {0} is invalid. Valid SQL Queries are inside =SQL() or =STOREPROC()", sqlFormula); return(sqlFormula); } int start = sqlFormula.IndexOf("("); int end = sqlFormula.LastIndexOf(")"); if ((end < start) || ((end - start) < 5)) { invalidFormula = true; } string formula = sqlFormula.Substring(0, start); if (formula.Equals("=SQL", StringComparison.OrdinalIgnoreCase)) { sqlCommandType = SqlCommandTypes.SqlCommand; } else if (!formula.Equals("=STOREPROC", StringComparison.OrdinalIgnoreCase)) { sqlCommandType = SqlCommandTypes.StoreProcedure; } else { invalidFormula = true; } if (invalidFormula) { errorMessage = string.Format("Error while parasing data, SQL query {0} is invalid. Valid SQL Queries are inside =SQL() or =STOREPROC()", sqlFormula); return(sqlFormula); } string SQLQueryWithConnectionString = sqlFormula.Substring(start + 1, end - start - 1); connectionStringKeyName = SQLQueryWithConnectionString.Split("|".ToCharArray())[0]; string SQLQuery = SQLQueryWithConnectionString.Split("|".ToCharArray())[1]; return(SQLQuery); }
/// <summary> /// Parses attributes /// </summary> /// <param name="attributes">Master attribute list</param> /// <param name="column">A column(Attributes) to be parsed</param> /// <param name="isSystemRow">true if processing system row</param> /// <param name="rowPosition">Row position, used only in error information, to identify row. 0(Zero) in case of system attributes.</param> /// <param name="doNotWriteErrorInTraceFile">To avoid confusion, if this is true, it wont write sql query formatting errors into trace file. /// (in case of first attempt, values may not be ready to replace in queries, which is absolutely a valid scenario)</param> internal void Parse(List <IdpeAttribute> attributes, Column column, bool isSystemRow, int rowPosition, bool doNotWriteErrorInTraceFile) { try { List <SreType> sqlQueryTypes = new List <SreType>(); for (int a = 0; a < attributes.Count; a++) { string value = string.Empty; if (column[attributes[a].Name].IsNull == true) { value = "NULL"; column[a].Error = new IdpeMessage(IdpeMessageCodes.IDPE_SUCCESS); } else { value = column[attributes[a].Name].Value; } //if (attributes[a].Name == "AssetId") // Debugger.Break(); SreType SREType = SreTypeFactory.GetInstance(attributes[a].Name, value, attributes[a].Type, attributes[a].Formula, attributes[a].Minimum, attributes[a].Maximum, isSystemRow, rowPosition, this._SQLClientManager, this._DataSourceKeys); column[a].Type = SREType; if (column[attributes[a].Name].IgnoreParsing) { column[a].Error = new IdpeMessage(IdpeMessageCodes.IDPE_SUCCESS); continue; } if ((SREType.Type != AttributeTypes.Referenced) && (SREType.Type != AttributeTypes.NotReferenced) && (SREType.Type != AttributeTypes.Generated)) { //parse rightway...We dont have SQL Queries to fire //we can override errors for all except 'IsValid'...because if rule fails, we manually override its error msgs //and if IsValid.err msg already populated, we will lose that... if (attributes[a].Name == "IsValid") { IdpeMessage errMsg = SREType.Parse(false); if (column[a].Error == null) { column[a].Error = errMsg; } else { column[a].Error.Message += column[a].Error.Message; } } else { IdpeMessage thisResult = SREType.Parse(false); if (column[a].Error == null) { column[a].Error = thisResult; } else { if (thisResult.Code != IdpeMessageCodes.IDPE_SUCCESS) { string oldErrorMsg = column[a].Error.Message; column[a].Error = thisResult; column[a].Error.Message = string.Format("{0},{1}", column[a].Error.Message, oldErrorMsg); } } } //if codeset type, store enum value and code as well if (SREType.Type == AttributeTypes.Codeset) { SreCodeset sreCodeset = SREType as SreCodeset; //if (string.IsNullOrEmpty(column[attributes[a].Name].ValueEnumValue)) // column[attributes[a].Name].ValueEnumValue = column[attributes[a].Name].Value; column[attributes[a].Name].ValueEnumCode = sreCodeset.ValueEnumCode; column[attributes[a].Name].ValueEnumValue = sreCodeset.ValueEnumValue; column[attributes[a].Name].ValueEnumReferenceKey = sreCodeset.ReferenceKey; } else { column[a].Value = SREType.Value; //in case anything updated after parse(at this moment, formatted datetime) } } else { //to be parsed once all others are parsed. sqlQueryTypes.Add(SREType); } column[a].IsNull = SREType.IsNull; } //we have parsed all other values except with sql queries, lets parse those. foreach (SreType item in sqlQueryTypes) { //if (item.ColumnName == "OldInvoiceNumber") // Debugger.Break(); Attribute currentAttribute = column[item.ColumnName]; //efficient, instead of calling string indxr multiple times. if ((item.Type == AttributeTypes.Generated) && (!item.IsHavingSqlQuery)) { if (string.IsNullOrEmpty(currentAttribute.Value)) { currentAttribute.Value = GetFormulaResult(item.Formula); } IdpeMessage thisResult = item.Parse(false); if (currentAttribute.Error == null) { currentAttribute.Error = thisResult; } else { if (thisResult.Code != IdpeMessageCodes.IDPE_SUCCESS) { string oldErrorMsg = currentAttribute.Error.Message; currentAttribute.Error = thisResult; currentAttribute.Error.Message = string.Format("{0},{1}", currentAttribute.Error.Message, oldErrorMsg); } } } else if ((item.Type == AttributeTypes.NotReferenced) && (string.IsNullOrEmpty(currentAttribute.Value))) { //NotReferenced can not be empty at least. currentAttribute.Error = new IdpeMessage(IdpeMessageCodes.IDPE_REFERENCED_TYPE_DATA_CAN_NOT_BE_NULL); currentAttribute.Error.Message = string.Format(currentAttribute.Error.Message, PrintRowColPosition(item.RecordPosition, item.ColumnName, isSystemRow), currentAttribute.Value, item.ColumnName); continue; } else { string errorMessage = string.Empty; string value = currentAttribute.Value; SqlCommandTypes sqlCommandTypes = SqlCommandTypes.Unknown; string connectionStringKeyName = string.Empty; string SQLQuery = FormatSQLFormula(item.Formula, ref sqlCommandTypes, ref connectionStringKeyName, ref errorMessage); string FormattedSQLQuery = string.Empty; if (errorMessage == string.Empty) { if (connectionStringKeyName != Constants.DefaultConnectionStringKeyName) { item.ConnectionStringKeyName = connectionStringKeyName; //string k_n_type = _ApplicationKeys.GetKeyValue(connectionStringKeyName); IdpeKey key = _DataSourceKeys.GetKey(connectionStringKeyName); item.ConnectionString = key.Value; item.DatabaseType = (IdpeKeyTypes)key.Type; } if (sqlCommandTypes == SqlCommandTypes.SqlCommand) { FormattedSQLQuery = FormatSQLParameters(SQLQuery, value, column, ref errorMessage); } else if (sqlCommandTypes == SqlCommandTypes.StoreProcedure) { //todo //FormattedSQLQuery = } } if (errorMessage != string.Empty) { //Prepare return error message (and not the technical exception) IdpeMessage returnMessage = new IdpeMessage(IdpeMessageCodes.IDPE_TYPE_DATA_VALIDATION_FAILED_GENERIC); //Write the error into trace with mapId. Guid detailesMapId = Guid.NewGuid(); //Do not write in trace, as it is actually not an error, tried first attempt if (!doNotWriteErrorInTraceFile) { ExtensionMethods.TraceError(string.Format("{0}:{1}", detailesMapId.ToString(), errorMessage)); } //set mapId in client error msg returnMessage.Message = string.Format("{0}. Map Id:{1}", returnMessage.Message, detailesMapId.ToString()); if (currentAttribute.Error == null) { //send the generic error with mapId currentAttribute.Error = returnMessage; } else { if (currentAttribute.Error.Code != IdpeMessageCodes.IDPE_SUCCESS) { string oldErrorMsg = currentAttribute.Error.Message; currentAttribute.Error = returnMessage; currentAttribute.Error.Message = string.Format("{0},{1}", currentAttribute.Error.Message, oldErrorMsg); } } } else { item.OverrideFormula(FormattedSQLQuery); currentAttribute.Error = item.Parse(false); //SQL Query results are always overridden. currentAttribute.Value = item.Value; } } } //Parsing done, as we have more data (SQL Parameters) now, lets just format all queries one more time, there might be few more parameters in context. FormatAllSQLQueries(column); } catch (Exception ex) { Trace.Write(string.Format("Attribute parsing error, row position = {0}{1}{2}", rowPosition, Environment.NewLine, ex.ToString())); } }