private IEnumerable <ConnectedItem> GetConnectedItems(ICommenceCursor cur) { if (this.CategoryDefinition.Clarified) { foreach (List <string> row in cur.ReadAllRows()) { yield return(new ConnectedItem() { DisplayName = row[0] + " " + this.CategoryDefinition.ClarifySeparator + " " + row[1], ItemName = row[0], ClarifyValue = row[1], ClarifySeparator = this.CategoryDefinition.ClarifySeparator }); } } else { foreach (List <string> row in cur.ReadAllRows()) { yield return(new ConnectedItem() { DisplayName = row[0], ItemName = row[0], }); } } }
/// <summary> /// This method is called by all exports methods, it takes care of all the actual exports. /// Other export methods are really just preparation routines for calling this method. /// </summary> /// <param name="cur">ICommenceCursor</param> /// <param name="fileName">file name</param> /// <param name="settings">IExportSettings</param> internal void ExportCursor(ICommenceCursor cur, string fileName, IExportSettings settings) { // exporting may take a long time, and the system may go into power saving mode // this is annoying, so we tell the system not to go into sleep/hibernation // this may or may not be a good idea... PowerSavings ps = new PowerSavings(); try { ps.EnableConstantDisplayAndPower(true, "Performing time-consuming Commence export"); cur.MaxFieldSize = this.Settings.MaxFieldSize; // remember setting this size greatly impacts memory usage! using (_writer = this.GetExportWriter(cur, settings)) { SubscribeToWriterEvents(_writer); _writer.WriteOut(fileName); } } finally { ps.EnableConstantDisplayAndPower(false); // CODE SMELL, may overwrite things UnsubscribeToWriterEvents(_writer); _writer?.Dispose(); } }
/// <inheritdoc /> public void ExportCategory(string categoryName, IEnumerable <ICursorFilter> filters, string fileName, IExportSettings settings = null) { if (settings != null) { this.Settings = settings; } // use custom settings if supplied CmcOptionFlags flags = this.Settings.UseThids ? CmcOptionFlags.UseThids : CmcOptionFlags.Default | CmcOptionFlags.IgnoreSyncCondition; using (var db = new CommenceDatabase()) { if (this.Settings.SkipConnectedItems && this.Settings.HeaderMode != HeaderMode.CustomLabel) { using (ICommenceCursor cur = GetCategoryCursorFieldsOnly(db, categoryName, flags)) { ApplyFilters(cur, filters); this.Settings.MaxFieldSize = (int)Math.Pow(2, 15); // 32.768, the built-in Commence max fieldlength (large text) is 30.000 ExportCursor(cur, fileName, this.Settings); } } else { using (ICommenceCursor cur = GetCategoryCursorAllFieldsAndConnections(db, categoryName, flags)) { ApplyFilters(cur, filters); ExportCursor(cur, fileName, this.Settings); } } } }
public ExcelWriterUsingOpenXml(ICommenceCursor cursor, IExportSettings settings) : base(cursor, settings) { columnDefinitions = new List <ColumnDefinition>(_settings.UseThids ? base.ColumnDefinitions.Skip(1) : base.ColumnDefinitions); _sheetName = string.IsNullOrEmpty(settings.CustomRootNode) ? Utils.EscapeString(_dataSourceName, "_").Left(MaxSheetNameLength) : settings.CustomRootNode; settings.ISO8601Format = true; // override custom setting(s) settings.SplitConnectedItems = false; // override custom setting(s) }
private ICommenceCursor GetCategoryCursorFieldsOnly(ICommenceDatabase db, string categoryName, CmcOptionFlags flags) { ICommenceCursor cur = db.GetCursor(categoryName, CmcCursorType.Category, flags); string[] fieldNames = db.GetFieldNames(categoryName).ToArray(); cur.Columns.AddDirectColumns(fieldNames); cur.Columns.Apply(); return(cur); }
public OriginalCursorProperties(ICommenceCursor cursor) { Name = string.IsNullOrEmpty(cursor.View) ? cursor.Category : cursor.View; Category = cursor.Category; Type = string.IsNullOrEmpty(cursor.View) ? CmcCursorType.Category.ToString() : CmcCursorType.View.ToString(); }
private IEnumerable <Field> GetValuesFromCursor(ICommenceCursor cur) { IEnumerable <Field> retval = new List <Field>(); // a cursor cannot have no fields, so we should be safe. ColumnParser cp = new ColumnParser(cur); IList <ColumnDefinition> columnDefinitions = cp.ParseColumns(); _db.ClarifyItemNames("true"); _itemName = _db.GetActiveItemName(); // also marks the item for us IList <Field> fields = new List <Field> { new Field { Name = _nameField, Value = _itemName, Label = columnDefinitions .Where(w => !w.IsConnection) .SingleOrDefault(s => s.FieldName.Equals(_nameField))?.ColumnLabel } }; // if the name field isn't in the view the view label will be empty, // in that casechange it to fieldname instead // note that this will actually return more information than is showing in Commence if (string.IsNullOrEmpty(fields[0].Label)) { fields[0].Label = _nameField; } // get a list of all the direct fields except the name field IEnumerable <ColumnDefinition> directFields = columnDefinitions .Where(w => !w.IsConnection && w.FieldName != _nameField) .ToArray(); // get the direct field values using DDE IEnumerable <Field> directFieldValues = GetDirectFieldValues(directFields); retval = fields.Concat(directFieldValues); // get the indirect values IEnumerable <RelatedColumn> relatedColumns = columnDefinitions .Where(w => w.IsConnection) .Select(s => new RelatedColumn(s.Connection, s.Category, s.FieldName, RelatedColumnType.ConnectedField, s.QualifiedConnection + ' ' + s.FieldName)) // very dirty trick!!!!!! .ToArray(); IEnumerable <Field> connectedFieldValues = GetConnectedFieldValues(relatedColumns); retval = retval.Concat(connectedFieldValues); _db.ClarifyItemNames(clarifyStatus); // restore original setting return(retval); } // method
/// <summary> /// Constructor used when exporting cursor. /// </summary> /// <param name="cursor">Database.ICommenceCursor.</param> /// <param name="settings">ExportSettings object.</param> /// <exception cref="ArgumentOutOfRangeException"></exception> /// <exception cref="ArgumentException"></exception> protected internal BaseWriter(ICommenceCursor cursor, IExportSettings settings) { _cursor = cursor; _settings = settings; // this check is very important if (_settings.HeaderMode == HeaderMode.CustomLabel) { if (ValidCustomHeaders(_settings.CustomHeaders.Select(x => x.ToString()).ToArray())) { _customColumnHeaders = _settings.CustomHeaders.Select(x => x.ToString()).ToArray(); _customColumnHeaders = Utils.RenameDuplicates(_customColumnHeaders.ToList()).ToArray(); } } _dataSourceName = (string.IsNullOrEmpty(_cursor.View)) ? _cursor.Category : _cursor.View; _cursor.SeekRow(CmcCursorBookmark.Beginning, 0); // put rowpointer on first item }
internal DataReader(ICommenceCursor cursor, IExportSettings settings, List <ColumnDefinition> columndefinitions) { this.cursor = (CommenceCursor)cursor; totalRows = cursor.RowCount; this.settings = settings; numRows = (int)Math.Pow(2, BalanceNumRowsAndFieldSize(settings)); if (settings.NumRows < numRows) { numRows = settings.NumRows; } this.columnDefinitions = columndefinitions; regex = new Regex(pattern); if (this.cursor.Flags.HasFlag(CmcOptionFlags.UseThids)) { useThids = true; } }
private ICommenceCursor GetCategoryCursorAllFieldsAndConnections(ICommenceDatabase db, string categoryName, CmcOptionFlags flags) { ICommenceCursor cur = db.GetCursor(categoryName, CmcCursorType.Category, flags); string[] fieldNames = db.GetFieldNames(categoryName).ToArray(); cur.Columns.AddDirectColumns(fieldNames); var cons = db.GetConnectionNames(cur.Category); foreach (var c in cons) { //string nameField = db.GetNameField(c.ToCategory); //cur.Columns.AddRelatedColumn(c.Name, c.ToCategory, nameField); // this is bad. a related column loses the THID flag cur.Columns.AddDirectColumn(c.Name + ' ' + c.ToCategory); // will respect UseThids flag } cur.Columns.Apply(); return(cur); }
/// <inheritdoc /> public void ExportView(string viewName, string fileName, IExportSettings settings = null) { if (settings != null) { this.Settings = settings; } // use user-supplied settings using (var db = new CommenceDatabase()) { string _viewName = string.IsNullOrEmpty(viewName) ? GetActiveViewName(db) : viewName; using (ICommenceCursor cur = db.GetCursor(_viewName, CmcCursorType.View, this.Settings.UseThids ? CmcOptionFlags.UseThids : CmcOptionFlags.Default)) { ExportCursor(cur, fileName, this.Settings); } } }
/// <summary> /// Factory method for creating the required export writer object for a cursor export. /// </summary> /// <param name="cursor">Database.ICommenceCursor .</param> /// <param name="settings">Settings object.</param> /// <returns>Derived BaseDataWriter object.</returns> /// <remarks>Defaults to XML.</remarks> internal BaseWriter GetExportWriter(ICommenceCursor cursor, IExportSettings settings) { if (!settings.PreserveAllConnections && settings.NestConnectedItems && settings.ExportFormat == ExportFormat.Json) { return(new AdoNetWriter(cursor, settings)); // i think this can be taken out entirely } if (settings.PreserveAllConnections) { return(new Complex.SQLiteWriter(cursor, settings)); } switch (settings.ExportFormat) { case ExportFormat.Text: return(new TextWriter(cursor, settings)); case ExportFormat.Html: return(new HtmlWriter(cursor, settings)); case ExportFormat.Xml: return(new XmlWriter(cursor, settings)); case ExportFormat.Json: return(new JsonWriter(cursor, settings)); case ExportFormat.Excel: //_writer = new ExcelWriterUsingXml(cursor, settings); //return new ExcelWriterUsingOleDb(cursor, settings); //return new ExcelWriterUsingOpenXml(cursor, settings); return(new ExcelWriterUsingEPPlus(cursor, settings)); case ExportFormat.Event: return(new EventWriter(cursor, settings)); case ExportFormat.GoogleSheets: // will probably always be too slow throw new NotImplementedException("Exportformat not yet implemented."); default: return(new XmlWriter(cursor, settings)); } }
internal ExcelWriterUsingEPPlus(ICommenceCursor cursor, IExportSettings settings) : base(cursor, settings) { string s = string.IsNullOrEmpty(settings.CustomRootNode) ? _dataSourceName : settings.CustomRootNode; _dataTable = PrepareDataTable(s, base.ColumnDefinitions); _sheetName = Utils.EscapeString(s, "_").Left(MaxSheetNameLength); settings.Canonical = true; // override custom setting settings.SplitConnectedItems = false; // override custom setting // When dealing with very large cursors, // the default number of rows read may lead to memory issues when dumping to a datatable // we may have to cap the number of items being read // This superceeds the check already performed in the datareader. // I am undecided on this. // What I do know is that writing 1000 rows of 250 columns of size 30.000 // (i.e. 250 large text fields, fully populated) will fail. // Maybe we should have some mechanism that collects the size of the fields // then caps the NumRows count accordingly. // However, it would not solve issues with EPPlus running out of memory when dealing with huge workbooks. }
/// <inheritdoc /> public void ExportCategory(string categoryName, string fileName, IExportSettings settings = null) { if (settings != null) { this.Settings = settings; } // use custom settings if supplied CmcOptionFlags flags = this.Settings.UseThids ? CmcOptionFlags.UseThids : CmcOptionFlags.Default | CmcOptionFlags.IgnoreSyncCondition; // User requested we skip connections. // A default cursor on a category contains all fields *and* connections. // The data receiving routines will ignore them, but they will be read unless we do not include them in our cursor // We optimize here by only including direct fields in the cursor using (var db = new CommenceDatabase()) { if (this.Settings.SkipConnectedItems && this.Settings.HeaderMode != HeaderMode.CustomLabel) { using (ICommenceCursor cur = GetCategoryCursorFieldsOnly(db, categoryName, flags)) { // we can limit MAX_FIELD_SIZE in this case this.Settings.MaxFieldSize = (int)Math.Pow(2, 15); // 32.768, the built-in Commence max fieldlength (large text) is 30.000 ExportCursor(cur, fileName, this.Settings); } } else { using (ICommenceCursor cur = GetCategoryCursorAllFieldsAndConnections(db, categoryName, flags)) { // You can create a cursor with all fields including connections by just // supplying CmcOptionFlags.All. // However, when the cursor is read, connected items are returned as comma-delimited strng, // which, because Commence does not supply text-qualifiers, makes it impossible to split them. // We therefore explicitly set the connections which deteriorates performance // but gains us (more) reliability. ExportCursor(cur, fileName, this.Settings); } } } }
internal ICommenceCursor Create(CursorDescriptor cursorParameters) { ICommenceCursor retval = null; if (db == null) { throw new InvalidOperationException($"Underlying database was closed."); } retval = db.GetCursor(cursorParameters.CategoryOrView, cursorParameters.CursorType, CmcOptionFlags.UseThids | CmcOptionFlags.IgnoreSyncCondition); retval.SetColumns(cursorParameters.Fields.ToArray()); retval.Columns.Apply(); for (int i = 0; i < cursorParameters.Filters.Count(); i++) { ICursorFilterTypeCTCF f = retval.Filters.Create(i + 1, FilterType.ConnectionToCategoryField); // we already have the filter object defined, copy them foreach (PropertyInfo property in typeof(ICursorFilterTypeCTCF).GetProperties().Where(p => p.CanWrite)) { property.SetValue(f, property.GetValue(cursorParameters.Filters[i], null), null); } } retval.Filters.Apply(); retval.MaxFieldSize = cursorParameters.MaxFieldSize; return(retval); }
private ICursorFilter CreateFilter(string categoryName, FilterType filterType) { using (ICommenceDatabase db = new CommenceDatabase()) { using (ICommenceCursor cur = db.GetCursor(categoryName)) { switch (filterType) { case FilterType.Field: return((CursorFilterTypeF)cur.Filters.Add(_model.ClauseNumber, filterType)); case FilterType.ConnectionToItem: return((CursorFilterTypeCTI)cur.Filters.Add(_model.ClauseNumber, filterType)); case FilterType.ConnectionToCategoryField: return((CursorFilterTypeCTCF)cur.Filters.Add(_model.ClauseNumber, filterType)); case FilterType.ConnectionToCategoryToItem: return((CursorFilterTypeCTCTI)cur.Filters.Add(_model.ClauseNumber, filterType)); } } } return(null); }
internal ExcelWriterUsingOleDb(ICommenceCursor cursor, IExportSettings settings) : base(cursor, settings) { columnDefinitions = new List <ColumnDefinition>(_settings.UseThids ? base.ColumnDefinitions.Skip(1) : base.ColumnDefinitions); }
private void ApplyFilters(ICommenceCursor cur, IEnumerable <ICursorFilter> filters) { ((CursorFilters)cur.Filters).AddRange(filters); // AddRange is not exposed by interface cur.Filters.Apply(); }
private IList <ConnectedItem> PopulateConnectedItemNamesList(string searchString) { IList <ConnectedItem> retval = new List <ConnectedItem>(); if (string.IsNullOrEmpty(this.SelectedConnectedCategory)) { return(retval); } using (ICommenceDatabase db = new CommenceDatabase()) { using (ICommenceCursor cur = db.GetCursor(this.SelectedConnectedCategory)) { string nameField = db.GetNameField(this.SelectedConnectedCategory); var columns = this.CategoryDefinition.Clarified ? new[] { nameField, this.CategoryDefinition.ClarifyField } : new[] { nameField }; if (!cur.SetColumns(columns)) { return(retval); } // something went wrong bad var number = cur.RowCount; if (string.IsNullOrEmpty(searchString)) { if (number == 0) { retval.Add(new ConnectedItem("(No items to display)", null, null, null)); return(retval); } else if (number < 1000) { return(GetConnectedItems(cur).ToList()); } else { retval.Add(new ConnectedItem("(Too many items to display.)", null, null, null)); return(retval); } } else { CursorFilterTypeF f = cur.Filters.Create(1, FilterType.Field); f.FieldValue = this.ConnectedItemSearchString; f.FieldName = nameField; f.Qualifier = FilterQualifier.Contains; int count = cur.Filters.Apply(); if (count > 1000) { retval.Add(new ConnectedItem("(Too many items to display)", null, null, null)); return(retval); } else if (count == 0) { retval.Add(new ConnectedItem($"(No items contain '{ this.ConnectedItemSearchString }')", null, null, null)); return(retval); } else { return(GetConnectedItems(cur).ToList()); } } } } }