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
