private int InsertValues(IList <CommenceValue> values) { foreach (var v in values) { string paramIdentifier = "@" + columnMap[v.ColumnDefinition.ColumnName]; if (!v.ColumnDefinition.IsConnection) { string value = string.Empty; // remove currency symbol if present or we'll get a type cast error if (v.ColumnDefinition.CommenceFieldDefinition.Type == CommenceFieldType.Number) { value = CommenceValueConverter.RemoveCurrencySymbol(v.DirectFieldValue); } else { value = v.DirectFieldValue; } //// if we are dealing with large text fields, we may have more than allowed number of newlines //if (value.Count(c => c.Equals('\n')) > MaxExcelNewLines) //{ // value = value.Substring(0, value.IndexOfNthChar('\n', 0, MaxExcelNewLines)); //} cmd.Parameters[paramIdentifier].Value = value; } else { string cValue = string.Empty; // there can be many more values in a connection than an Excel cell can hold if (v.ConnectedFieldValues != null) { //// check maximum number of newlines //if (_settings.TextDelimiterConnections == "\n") //{ // cValue = string.Join(_settings.TextDelimiterConnections, v.ConnectedFieldValues.Take(MaxExcelNewLines)); //} //else //{ // cValue = string.Join(_settings.TextDelimiterConnections, v.ConnectedFieldValues); //} cValue = string.Join(_settings.TextDelimiterConnections, v.ConnectedFieldValues); //// if we are dealing with large text fields, we still may have more than allowed number of newlines //if (cValue.Count(c => c.Equals('\n')) > MaxExcelNewLines) //{ // cValue = cValue.Substring(0, cValue.IndexOfNthChar('\n', 0, MaxExcelNewLines)); //} // make sure length doesn't exceed Excel cell limit cValue = cValue.Length > MaxExcelCellSize?cValue.Substring(0, MaxExcelCellSize) : cValue; } int lengte = cValue.Length; cmd.Parameters[paramIdentifier].Value = cValue; } } cmd.CommandText = InsertCommandText; return(cmd.ExecuteNonQuery()); }
private string[] FormatValues(string[] values, ColumnDefinition cd) { string[] retval = values; if (settings.Canonical) { for (int i = 0; i < retval.Length; i++) { if (!string.IsNullOrEmpty(retval[i])) { retval[i] = CommenceValueConverter.ToCanonical(retval[i], cd.CommenceFieldDefinition.Type, settings); } } } return(retval); }
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