/// <summary> /// vraci hodnotu sloupce, pripadne hodnotu null (referenci) /// </summary> /// <param name="data"></param> /// <param name="row"></param> /// <param name="ColumnName"></param> /// <returns></returns> internal static object ValByName(this SelectStatementResultRow row, DevExpress.Xpo.DB.SelectedData data, string ColumnName) { string colName, colType, stringValue; int colIndex = Int32.MaxValue; for (int i = 0; i < data.ResultSet[0].Rows.Count(); i++) { DevExpress.Xpo.DB.SelectStatementResultRow rowColDefine = data.ResultSet[0].Rows[i]; colName = (string)rowColDefine.Values[0]; colType = (string)rowColDefine.Values[2]; //Decimal, String ... if (String.Equals(colName, ColumnName, StringComparison.InvariantCultureIgnoreCase)) { colIndex = i; break; } } if (colIndex <= row.Values.Count()) { return(row.Values[colIndex]); } else { throw new IndexOutOfRangeException(String.Format("column \"{0}\" v kolekci DataSetu neexistuje", ColumnName)); } }
public static string NextLineVal(Session unitOfWork) { #region EndCode //int sequId = 0; //var sequNameParm = new DevExpress.Xpo.DB.SprocParameter("@SequenceName", UI_LABEL_ID_SEQU); //var outputParm = new DevExpress.Xpo.DB.SprocParameter() //{ // ParameterName = "@NewID", // Direction = DevExpress.Xpo.DB.SprocParameterDirection.Output, // DbType = DevExpress.Xpo.DB.DBColumnType.Int32 //}; //var data = unitOfWork.ExecuteSprocParametrized(SP_NEXT_TASK_LINE, sequNameParm, outputParm); //SelectStatementResultRow row = data.ResultSet[1].Rows[0]; //sequId = (int)row.Values[1];// data.ResultSet[1].Rows[1].Values[1]; #endregion long returnIndex = 0; var sequIdParm = new DevExpress.Xpo.DB.SprocParameter("@SequenceId", SysSequence.UI_LABEL_SEQU); var sequNameParm = new DevExpress.Xpo.DB.SprocParameter("@SequenceName", Enum.GetName(typeof(SysSequence), SysSequence.UI_LABEL_SEQU)); var startWithParm = new DevExpress.Xpo.DB.SprocParameter("@StartWith", 100 * 1000); var outputParm = new DevExpress.Xpo.DB.SprocParameter() { ParameterName = "@NewID", Direction = DevExpress.Xpo.DB.SprocParameterDirection.Output, DbType = DevExpress.Xpo.DB.DBColumnType.Int32 }; var data = unitOfWork.ExecuteSprocParametrized(SP_NEXT_TASK_LINE, sequIdParm, sequNameParm, startWithParm, outputParm); SelectStatementResultRow row = data.ResultSet[1].Rows[0]; returnIndex = (int)row.Values[1];// data.ResultSet[1].Rows[1].Values[1]; return("@" + returnIndex.ToString()); }
internal static string[] GetColNames(this DevExpress.Xpo.DB.SelectedData data) { string[] colNames = new string[data.ResultSet[0].Rows.Count()]; for (int i = 0; i < data.ResultSet[0].Rows.Count(); i++) { SelectStatementResultRow rowColDefine = data.ResultSet[0].Rows[i]; colNames[i] += (string)rowColDefine.Values[0]; } return(colNames); }
public static int NextLineVal(Session unitOfWork) { int returnIndex = 0; var outputParm = new DevExpress.Xpo.DB.SprocParameter() { ParameterName = "@NewID", Direction = DevExpress.Xpo.DB.SprocParameterDirection.Output, DbType = DevExpress.Xpo.DB.DBColumnType.Int32 }; var data = unitOfWork.ExecuteSprocParametrized(SP_NEXT_TASK_LINE, outputParm); SelectStatementResultRow row = data.ResultSet[1].Rows[0]; returnIndex = (int)row.Values[1];// data.ResultSet[1].Rows[1].Values[1]; return(returnIndex); }
internal static Dictionary <string, Type> GetColumnsInfo(this DevExpress.Xpo.DB.SelectedData data) { Dictionary <string, Type> colInfo = new Dictionary <string, Type>(); for (int i = 0; i < data.ResultSet[0].Rows.Count(); i++) { SelectStatementResultRow rowColDefine = data.ResultSet[0].Rows[i]; Type typ = DBColumn.GetType((DBColumnType)Enum.Parse(typeof(DBColumnType), (string)rowColDefine.Values[2])); try { colInfo.Add((string)rowColDefine.Values[0], typ); } catch (Exception exc) { throw new ArgumentException(exc.Message + "\n key: " + (string)rowColDefine.Values[0]); } } return(colInfo); }
public static long NextVal(Session unitOfWork, MyEnums.SysSequence sysSequenceName, long startWith = 0) { long returnIndex = 0; var sequIdParm = new DevExpress.Xpo.DB.SprocParameter("@SequenceId", sysSequenceName); var sequNameParm = new DevExpress.Xpo.DB.SprocParameter("@SequenceName", Enum.GetName(typeof(MyEnums.SysSequence), sysSequenceName)); var startWithParm = new DevExpress.Xpo.DB.SprocParameter("@StartWith", startWith); var outputParm = new DevExpress.Xpo.DB.SprocParameter() { ParameterName = "@NewID", Direction = DevExpress.Xpo.DB.SprocParameterDirection.Output, DbType = DevExpress.Xpo.DB.DBColumnType.Int32 }; var data = unitOfWork.ExecuteSprocParametrized(SP_NEXT_TASK_LINE, sequIdParm, sequNameParm, startWithParm, outputParm); SelectStatementResultRow row = data.ResultSet[1].Rows[0]; returnIndex = (int)row.Values[1];// data.ResultSet[1].Rows[1].Values[1]; return(returnIndex); }
private SelectStatementResult SelectData(Query query, CriteriaOperatorCollection targets) { if (query.ConstantValues != null && query.OperandIndexes != null && query.ConstantValues.Count > 0) { CriteriaOperatorCollection customTargets = new CriteriaOperatorCollection(); if (query.OperandIndexes.Count == 0) { customTargets.Add(new OperandValue(1)); } else { CriteriaOperator[] trgts = new CriteriaOperator[query.OperandIndexes.Count]; for (int i = 0; i < targets.Count; i++) { if (query.OperandIndexes.ContainsKey(i)) { trgts[query.OperandIndexes[i]] = targets[i]; } } customTargets.AddRange(trgts); } SelectStatementResult queryResult = SelectDataSimple(query, customTargets, false)[0]; SelectStatementResultRow[] rows = new SelectStatementResultRow[queryResult.Rows.Length]; for (int ri = 0; ri < rows.Length; ri++) { object[] values = new object[targets.Count]; for (int i = 0; i < targets.Count; i++) { if (query.OperandIndexes.ContainsKey(i)) { values[i] = queryResult.Rows[ri].Values[query.OperandIndexes[i]]; } else { values[i] = query.ConstantValues[i].Value; } } rows[ri] = new SelectStatementResultRow(values); } return new SelectStatementResult(rows); } return SelectDataSimple(query, targets, false)[0]; }
private SelectStatementResult GetDataForTables(ICollection tables, TablesFilter filter, string queryText) { QueryParameterCollection parameters = new QueryParameterCollection(); List<SelectStatementResult> resultList = new List<SelectStatementResult>(); int paramIndex = 0; int pos = 0; int count = tables.Count; int currentSize = 0; StringCollection inGroup = null; foreach (DBTable table in tables) { if (currentSize == 0) { if (inGroup == null) { inGroup = new StringCollection(); } else { if (inGroup.Count == 0) { resultList.Add(new SelectStatementResult()); } resultList.Add(SelectData(new Query(string.Format(CultureInfo.InvariantCulture, queryText, StringListHelper.DelimitedText(inGroup, ",")), parameters, inGroup))); inGroup.Clear(); parameters.Clear(); } paramIndex = 0; currentSize = (pos < count) ? (count - pos < 15 ? count - pos : 15) : 0; } if (filter == null || filter(table)) { parameters.Add(new OperandValue(ComposeSafeTableName(table.Name))); inGroup.Add(":p" + paramIndex.ToString(CultureInfo.InvariantCulture)); ++paramIndex; --currentSize; } ++pos; } if (inGroup != null && inGroup.Count > 0) { resultList.Add(SelectData(new Query(string.Format(CultureInfo.InvariantCulture, queryText, StringListHelper.DelimitedText(inGroup, ",")), parameters, inGroup))); } if (resultList.Count == 0) return new SelectStatementResult(); if (resultList.Count == 1) return resultList[0]; int fullResultSize = 0; for (int i = 0; i < resultList.Count; i++) { fullResultSize += resultList[i].Rows.Length; } if (fullResultSize == 0) return new SelectStatementResult(); SelectStatementResultRow[] fullResult = new SelectStatementResultRow[fullResultSize]; int copyPos = 0; for (int i = 0; i < resultList.Count; i++) { Array.Copy(resultList[i].Rows, 0, fullResult, copyPos, resultList[i].Rows.Length); copyPos += resultList[i].Rows.Length; } return new SelectStatementResult(fullResult); }
public void UpdateSql() { try { bool useImageOverlay = Convert.ToBoolean(GlobalFramework.Settings["useImageOverlay"]); if (!useImageOverlay) { _fileBaseButtonOverlay = null; } //When update always set page 1, start page _currentPage = 1; //Store Total Childs bool _hasChilds = false; //Local Vars String executeSql; TouchButtonBase buttonCurrent = null; //Reset CurrentButtonOid, Or Assign it to initialActiveButtonOid if Defined in TablePad Constructor _selectedButtonOid = (_initialActiveButtonOid != Guid.Empty) ? _initialActiveButtonOid : Guid.Empty; //Prepare executeSql for first time if (_filter != string.Empty) { executeSql = _sql + _filter; } else { executeSql = _sql; }; if (_order != string.Empty) { executeSql += _order; } ; executeSql = string.Format("{0};", FrameworkUtils.RemoveCarriageReturnAndExtraWhiteSpaces(executeSql)); //_log.Debug(string.Format("TablePad(): executeSql: [{0}]", executeSql)); //Always clear listItems if (_listButtons.Count > 0) { _listButtons.Clear(); } //Debug SelectedData xpoSelectedData = GlobalFramework.SessionXpo.ExecuteQueryWithMetadata(executeSql); SelectStatementResultRow[] selectStatementResultMeta = xpoSelectedData.ResultSet[0].Rows; SelectStatementResultRow[] selectStatementResultData = xpoSelectedData.ResultSet[1].Rows; // foreach (SelectStatementResultRow row in selectStatementResultMeta) // { // _log.Debug(string.Format("UpdateSql(): {0}\t{1}\t{2}", row.Values[0], row.Values[1], row.Values[2])); // } // Detect Encrypted Model if (GlobalFramework.PluginSoftwareVendor != null && executeSql.ToLower().Contains(nameof(sys_userdetail).ToLower())) { // Inject nonPropertyFields that are outside of attributes Scope and are required to exists to be decrypted string[] nonPropertyFields = { "label" }; // Unencrypt selectStatementResultData encrypted properties selectStatementResultData = XPGuidObject.DecryptSelectStatementResults(typeof(sys_userdetail), selectStatementResultMeta, selectStatementResultData, nonPropertyFields); } //Create a FieldIndex to Get Values From FieldNames int i = 0; _fieldIndex = new Dictionary <string, int>(); foreach (SelectStatementResultRow field in selectStatementResultMeta) { _fieldIndex.Add(field.Values[0].ToString(), i++); } if (selectStatementResultData != null && selectStatementResultData.LongLength > 0) { //check existance of required fields if (selectStatementResultMeta[_fieldIndex["id"]].Values[0].ToString() == "id" && selectStatementResultMeta[_fieldIndex["name"]].Values[0].ToString() == "name" && selectStatementResultMeta[_fieldIndex["label"]].Values[0].ToString() == "label" && selectStatementResultMeta[_fieldIndex["image"]].Values[0].ToString() == "image") { foreach (SelectStatementResultRow row in selectStatementResultData) { //Create the protected reference to current row _resultRow = row; //Assign first id to TablePad if (_selectedButtonOid == Guid.Empty) { _selectedButtonOid = new Guid(_resultRow.Values[_fieldIndex["id"]].ToString()); //If not defined _initialActiveButtonOid, by default the _initialActiveButtonOid will be the first button if (_initialActiveButtonOid == Guid.Empty) { _initialActiveButtonOid = _selectedButtonOid; } } ; _strButtonName = string.Format("{0}_{1}", _buttonNamePrefix, _resultRow.Values[_fieldIndex["id"]].ToString()); _strButtonLabel = (_resultRow.Values[_fieldIndex["label"]] != null && _resultRow.Values[_fieldIndex["label"]].ToString() != string.Empty) ? _resultRow.Values[_fieldIndex["label"]].ToString() : _resultRow.Values[_fieldIndex["name"]].ToString(); _strButtonImage = (_resultRow.Values[_fieldIndex["image"]] != null && _resultRow.Values[_fieldIndex["image"]].ToString() != string.Empty) ? FrameworkUtils.OSSlash(_resultRow.Values[_fieldIndex["image"]].ToString()) : ""; if (_strButtonLabel.Length > _posBaseButtonMaxCharsPerLabel) { _strButtonLabel = _strButtonLabel.Substring(0, _posBaseButtonMaxCharsPerLabel) + "."; } ; //Initialize Button buttonCurrent = InitializeButton(); //Add Current Button to List _listButtons.Add(buttonCurrent); buttonCurrent.CurrentButtonOid = new Guid(_resultRow.Values[_fieldIndex["id"]].ToString()); //Disable Current Active Button, and turn it the selected if (new Guid(_resultRow.Values[_fieldIndex["id"]].ToString()) == _initialActiveButtonOid) { if (_toggleMode) { buttonCurrent.Sensitive = false; } //Always assign selected reference to SelectedButton _selectedButton = buttonCurrent; } //Childs if (_fieldIndex.ContainsKey("childs")) { _hasChilds = (Convert.ToInt16(_resultRow.Values[_fieldIndex["childs"]]) > 0) ? true : false; } else { _hasChilds = true; }; if (_hasChilds) { //Assign to public Event, to be Exposed to initialized object buttonCurrent.Clicked += TablePadChildButton_Clicked; //Add Childs to Button Label //TODO: WorkInProgress //_log.Debug(string.Format("UpdateSql(): _reader.FieldCount [{0}]", _reader.FieldCount)); //if(_reader.FieldCount >= 5 && !_reader.IsDBNull(4)) buttonCurrent.Label += string.Format(" ({0})", _reader.GetInt16("childs")); } else { //Disable Button buttonCurrent.Sensitive = false; }; } ; //prepare pagging _totalItems = _listButtons.Count; _itemsPerPage = Convert.ToInt16(_rows * _columns); _totalPages = (int)Math.Ceiling((float)_totalItems / (float)_itemsPerPage); //Debug //_log.Debug(string.Format("UpdateSql(): totalItems: [{0}], itemsPerPage: [{1}], totalPages: [{2}]", _totalItems, _itemsPerPage, _totalPages)); Update(); } else { Utils.ShowMessageTouch(GlobalApp.WindowPos, DialogFlags.Modal, MessageType.Error, ButtonsType.Ok, resources.CustomResources.GetCustomResources(GlobalFramework.Settings["customCultureResourceDefinition"], "global_error"), "TablePad: Cant create TablePad, invalid query! You must supply mandatory fields name in Sql (id, name, label and image)!"); }; } else { //Always update buttons, even if result in empty query Update(); } } catch (Exception ex) { _log.Error(ex.Message, ex); } }
/*======================================================================*/ internal static void copyToObject <T>(this SelectStatementResultRow row, DevExpress.Xpo.DB.SelectedData data, T target) { SelectStatementResultRow[] columns = data.ResultSet[0].Rows; SelectStatementResultRow[] values = data.ResultSet[1].Rows; Dictionary <string, Type> columnsInfo = data.GetColumnsInfo(); BindingFlags bFlags = BindingFlags.Public | BindingFlags.NonPublic | BindingFlags.Instance; System.Reflection.PropertyInfo targetInfo = null; foreach (var column in columns) { string colName = (string)column.Values[0]; object value = ValByName(row, data, colName); string targetPropName = Utils.copy.GetPropertNameFromPersistAttr(target, colName); if (targetPropName == string.Empty) { targetInfo = typeof(T).GetProperty(colName, bFlags); } else { targetInfo = typeof(T).GetProperty(targetPropName); } if ((!(targetInfo == null)) && (targetInfo.CanWrite)) { bool dataZapsana = false; if (targetInfo.PropertyType.IsAssignableFrom(columnsInfo[colName])) { targetInfo.SetValue(target, value, null); dataZapsana = true; } else { HashSet <Type> IntTypes = new HashSet <Type> { typeof(int), typeof(Int16), typeof(Int32), typeof(Int64), typeof(short), typeof(uint), typeof(UInt16), typeof(UInt32), typeof(UInt64) }; HashSet <Type> nullIntTypes = new HashSet <Type> { typeof(int?), typeof(Int16?), typeof(Int32?), typeof(Int64?), typeof(short?), typeof(uint?), typeof(UInt16?), typeof(UInt32?), typeof(UInt64?) }; if ((IntTypes.Contains(targetInfo.PropertyType)) && (columnsInfo[colName] == typeof(System.Decimal)) && (((decimal)value % 1) == 0) ) { targetInfo.SetValue(target, (int)(decimal)value, null); dataZapsana = true; } if ((nullIntTypes.Contains(targetInfo.PropertyType)) && (columnsInfo[colName] == typeof(System.Decimal))) { if (value == null) { targetInfo.SetValue(target, value, null); dataZapsana = true; } else { if (((decimal)(value ?? 0) % 1) == 0) { targetInfo.SetValue(target, (int)(decimal)value, null); dataZapsana = true; } } } } if (!dataZapsana) { throw new Exception(String.Format("data sloupce \" {0} \" nelze zkopirovat", colName)); } } } }