private IEnumerable <object> GetValues(IEnumerable <CommenceValue> values) { foreach (CommenceValue cv in values) { if (!cv.ColumnDefinition.IsConnection) { yield return(CommenceValueConverter.ToAdoNet(cv.DirectFieldValue, cv.ColumnDefinition.CommenceFieldDefinition.Type) ?? DBNull.Value); } else { if (cv.ConnectedFieldValues != null && cv.ConnectedFieldValues.Length > 0) { // notice the trimming, EPPlus doesn't do it for you yield return(cv.ConnectedFieldValues[0].Left(MaxExcelCellSize)); // we turned off splitting, so all values are in first element } else { yield return(string.Empty); // always return something, otherwise column order gets thrown off } } } }
internal void ProcessRow() { // magic happens here // process direct fields // A cursor can contain only related columns! // in that case all columns are IsConnection // There will be a primary table, but it will only contain id's, not name field values. DataTable dt = null; dt = _ds.Tables[0]; // assume primary table is always first table! DataRow dr = dt.NewRow(); dr["id"] = _pk; foreach (CommenceValue cv in _vpList) { if (!cv.ColumnDefinition.IsConnection) // we have a direct field { string value = cv.DirectFieldValue; try { dr[cv.ColumnDefinition.FieldName] = CommenceValueConverter.ToAdoNet(value, cv.ColumnDefinition.CommenceFieldDefinition.Type); } catch (Exception e) { Console.WriteLine(e.Message); } } } dt.Rows.Add(dr); // process related columns // this is way more tricky... // we need to open datatables based on columns, but columns can use the same data table List <string> connectedDataTableNames = new List <string>(); foreach (CommenceValue cv in _vpList) // loop all row elements { if (cv.ColumnDefinition.IsConnection) { // compile a list of related table names connectedDataTableNames.Add(cv.ColumnDefinition.AdoTableName); } } // connectedDataTableNames now contains all table names for connected items // these can be duplicate, so make them unique connectedDataTableNames = connectedDataTableNames.Distinct().ToList(); // now we have a list of unique table names // we now need to create a row in each table with the values Commence gave us for that particular connection foreach (string connectedTableName in connectedDataTableNames) { // Create a second list of connected value/column pairs, // then pass that on to ParseConnectedRows method with the associated DataTable List <CommenceValue> cvpList = new List <CommenceValue>(); // new list for just the connected values foreach (CommenceValue cv in _vpList) { if (cv.ColumnDefinition.AdoTableName.Equals(connectedTableName)) { cvpList.Add(cv); } } dt = _ds.Tables[connectedTableName]; // now pass on the list of connected values this.ProcessConnectedValuesForItem(cvpList, dt, _pk); } }
private void ProcessConnectedValuesForItem(List <CommenceValue> values, DataTable dt, int fk) { // values contains arrays of connected values // we need a new row for every array element // all arrays will have same number of elements *if populated* // ideally we should compare the number of elements against the number of actual connected items // but I do not see a reliable way to do that (not quickly, anyway). // create a number of new rows equal to the number of connected items int numrows = 0; DataRow[] newrowbuffer = null; // find the number of connected items // this assumes the number is correct, which in case of a large text field may not be true. IEnumerable <CommenceValue> x = values.Where(o => o.ConnectedFieldValues != null); if (x.Count() == 0) // x is the collection CommenceValue objects that have connected values { return; // there are no connected values } else { // get the highest number of connected items. // They *should* all be the same, except when they were split incorrectly, in which case we end up with more rows than needed. numrows = x.Select(o => o.ConnectedFieldValues.Length).ToArray <int>().Max(); } for (int i = 0; i < numrows; i++) { if (newrowbuffer == null) { newrowbuffer = new DataRow[numrows]; } newrowbuffer[i] = dt.NewRow(); newrowbuffer[i]["fkid"] = fk; } foreach (CommenceValue v in values) { if (v.ConnectedFieldValues != null) // check to see if we have connected values { for (int i = 0; i < v.ConnectedFieldValues.Length; i++) // process connected items { string value = v.ConnectedFieldValues[i]; try { newrowbuffer[i][v.ColumnDefinition.FieldName] = CommenceValueConverter.ToAdoNet(value, v.ColumnDefinition.CommenceFieldDefinition.Type); } catch (Exception e) { Console.WriteLine(e.Message); } } // foreach } // if } // foreach // commit rows to datatable foreach (DataRow dr in newrowbuffer) { dt.Rows.Add(dr); } } // method