public bool ImportTextToDataTableUsingKeys(string rawImportText, DataTable destinationTable, char[] rowDelimiters, char[] columnDelimiters, out int badRows, out int missingRows, LookupTables lookupTables, AppSettings appSettings)
{
string replacementTextForEmbeddedNewLines = "<br>";
if (!string.IsNullOrEmpty(appSettings.GetAppSettingValue("GrinGlobalClient_replacementTextForEmbeddedNewLines")))
{
replacementTextForEmbeddedNewLines = appSettings.GetAppSettingValue("GrinGlobalClient_replacementTextForEmbeddedNewLines");
}
string scrubbedRawImportText = "";
// Attempting to remove new lines with no matching carriage return (ex. \n with no leading \r) and vice versa (ex. \r with no trailing \n)...
// First protect the well-formed carriage return line feed (\r\n) by temp. removing it and substituting a placeholder (to aid in weeding out lone \n and \r)...
scrubbedRawImportText = rawImportText.Replace("\r\n", "***well-formed carriage return line feed***").Replace("\n\r", "***well-formed carriage return line feed***");
// Now remove any remaining lone \n or \r that cannot be processed properly...
scrubbedRawImportText = scrubbedRawImportText.Replace("\r", replacementTextForEmbeddedNewLines).Replace("\n", replacementTextForEmbeddedNewLines);
// Next return the well-formed carriage return line feeds back where they belong...
scrubbedRawImportText = scrubbedRawImportText.Replace("***well-formed carriage return line feed***", "\r\n");
//string[] rawImportRows = rawImportText.Split(rowDelimiters, StringSplitOptions.RemoveEmptyEntries);
string[] rawImportRows = scrubbedRawImportText.Split(rowDelimiters, StringSplitOptions.RemoveEmptyEntries);
string[] uniqueKeyColumnNames = null;
bool primaryKeyFound = false;
System.Collections.Generic.List<DataColumn> uniqueKeys = new System.Collections.Generic.List<DataColumn>();
bool processedImportSuccessfully = false;
badRows = 0;
missingRows = 0;
// Make sure there is text to process - if not bail out now...
if (rawImportRows == null || rawImportRows.Length <= 0) return false;
// Begin looking for a row of raw text that contains the column headers for the destination datatable...
// This is a 2 phase approach that first looks for a row that contains all of the primary key column names
// But if that is not found - try again to find a row of raw text that contains all of the column names for the unique compound key
int columnHeaderRowIndex = -1;
// PHASE 1:
// Look for a raw text line that contains the full text name of the primary key columns (they must all be on the same line of raw text)...
if (destinationTable.PrimaryKey.Length > 0)
{
// Look through all of the rows of raw text for a single row that contains all of the primary key column names
for (int i = 0; i < rawImportRows.Length && columnHeaderRowIndex == -1; i++)
{
columnHeaderRowIndex = i; // Start out ASSUMING this is the 'right' row...
foreach (DataColumn pKeyColumn in destinationTable.PrimaryKey)
{
if (!FindText(GetFriendlyFieldName(pKeyColumn, pKeyColumn.ColumnName), rawImportRows[i], false, rowDelimiters, columnDelimiters))
{
// If the column header was not matched using case sensitive - try matching again (case insensitive)...
if (!FindText(GetFriendlyFieldName(pKeyColumn, pKeyColumn.ColumnName), rawImportRows[i], true, rowDelimiters, columnDelimiters))
{
// If the column header was still not matched - try the raw table field name...
if (!FindText(pKeyColumn.ColumnName, rawImportRows[i], true, rowDelimiters, columnDelimiters))
{
// The ASSUMPTION was wrong because the header text for one of the required primary key columns is missing in this raw text row...
columnHeaderRowIndex = -1;
}
}
}
}
}
if (columnHeaderRowIndex != -1) primaryKeyFound = true;
// Check to see if we need to move on to PHASE 2...
if (!primaryKeyFound)
{
// PHASE 2:
// Didn't find the primary key column in any text row in the import data - so try again, but this time looking for the alternate unique key...
if (destinationTable.PrimaryKey[0].ExtendedProperties.Contains("alternate_key_fields") &&
destinationTable.PrimaryKey[0].ExtendedProperties["alternate_key_fields"].ToString().Length > 0)
{
uniqueKeyColumnNames = destinationTable.PrimaryKey[0].ExtendedProperties["alternate_key_fields"].ToString().Split(',');
// Make sure the destination datatable has all of the columns specified in the alternate_key_fields ext. prop...
foreach (string uniqueColumnName in uniqueKeyColumnNames)
{
if (destinationTable.Columns.Contains(uniqueColumnName.Trim().ToLower()))
{
uniqueKeys.Add(destinationTable.Columns[uniqueColumnName.Trim().ToLower()]);
}
}
// The destination datatable does not have all of the columns specified in the compound unique key so bail out now...
if (uniqueKeys.Count != uniqueKeyColumnNames.Length) return false;
// Look through all of the rows of raw text for a single row that contains all of the unique key column names
for (int i = 0; i < rawImportRows.Length && columnHeaderRowIndex == -1; i++)
{
columnHeaderRowIndex = i; // Start out assuming the row has all of the unique key column headers...
foreach (DataColumn uKeyColumn in uniqueKeys)
{
if (!FindText(GetFriendlyFieldName(uKeyColumn, uKeyColumn.ColumnName), rawImportRows[i], false, rowDelimiters, columnDelimiters))
{
// If the column header was not matched using case sensitive - try matching again (case insensitive)...
if (!FindText(GetFriendlyFieldName(uKeyColumn, uKeyColumn.ColumnName), rawImportRows[i], true, rowDelimiters, columnDelimiters))
{
// If the column header was still not matched - try the raw table field name...
if (!FindText(uKeyColumn.ColumnName, rawImportRows[i], true, rowDelimiters, columnDelimiters))
{
// The ASSUMPTION was wrong because the header text for one of the required unique key columns is missing in this raw text row...
columnHeaderRowIndex = -1;
}
}
}
}
}
}
}
// Check to see if a column header was found for the psuedo-primary key of the destinationTable...
if (columnHeaderRowIndex == -1)
{
// Still cannot find an import row with column text that contains a collection of unique key columns - ask the user if they want to bail out now...
string uniqueKeyColumnFriendlyNames = "";
foreach (DataColumn dc in uniqueKeys)
{
uniqueKeyColumnFriendlyNames += GetFriendlyFieldName(dc, dc.ColumnName) + ", ";
}
uniqueKeyColumnFriendlyNames = uniqueKeyColumnFriendlyNames.Trim().TrimEnd(',');
//SharedUtils sharedUtils = new SharedUtils(lookupTables.WebServiceURL, lookupTables.Username, lookupTables.Password_ClearText, true);
SharedUtils sharedUtils = new SharedUtils(_webServices.Url, _webServices.Username, _webServices.Password_ClearText, true, "");
GRINGlobal.Client.Common.GGMessageBox ggMessageBox = new GRINGlobal.Client.Common.GGMessageBox("WARNING!!! You are pasting data in to this dataview without column headers that include: \n 1) The primary key column ({0}) OR \n 2) A combination of all of the columns ({1}) that will uniquely identify a single record in this dataview.\n\nWould you like to paste the data directly to the dataview starting at the selected cell?", "Missing Columns", MessageBoxButtons.YesNo, MessageBoxDefaultButton.Button1);
ggMessageBox.Name = "UserInterfaceUtils_ImportTextToDataTableUsingKeysMessage1";
if (sharedUtils != null && sharedUtils.IsConnected) sharedUtils.UpdateControls(ggMessageBox.Controls, ggMessageBox.Name);
//if (ggMessageBox.MessageText.Contains("{0}") &&
// ggMessageBox.MessageText.Contains("{1}"))
//{
// ggMessageBox.MessageText = string.Format(ggMessageBox.MessageText, GetFriendlyFieldName(destinationTable.PrimaryKey[0], destinationTable.PrimaryKey[0].ColumnName), uniqueKeyColumnFriendlyNames);
//}
//else if (ggMessageBox.MessageText.Contains("{0}"))
//{
// ggMessageBox.MessageText = string.Format(ggMessageBox.MessageText, GetFriendlyFieldName(destinationTable.PrimaryKey[0], destinationTable.PrimaryKey[0].ColumnName));
//}
//else if (ggMessageBox.MessageText.Contains("{1}"))
//{
// ggMessageBox.MessageText = string.Format(ggMessageBox.MessageText, uniqueKeyColumnFriendlyNames);
//}
string[] argsArray = new string[100];
argsArray[0] = GetFriendlyFieldName(destinationTable.PrimaryKey[0], destinationTable.PrimaryKey[0].ColumnName);
argsArray[1] = uniqueKeyColumnFriendlyNames;
ggMessageBox.MessageText = string.Format(ggMessageBox.MessageText, argsArray);
// Ask the user if they wish to perform a 'block paste' of the data...
if (DialogResult.Yes == ggMessageBox.ShowDialog())
{
return false;
}
else
{
return true;
}
}
// Since we made it here, it looks like we found a row in the import text that contains the column names for the destination tables primary/unique key...
string[] importColumnNames = rawImportRows[columnHeaderRowIndex].Split(columnDelimiters, StringSplitOptions.None);
System.Collections.Generic.Dictionary<string, int> columnNameMap = new System.Collections.Generic.Dictionary<string, int>();
// So now we need to build a map of datatable columns in import text columns (because they may not be in the same order)...
for (int i = 0; i < importColumnNames.Length; i++)
{
// Map the friendly field name from the incoming text to the matching column in the datatable (case sensitive)...
foreach (DataColumn dc in destinationTable.Columns)
{
if (GetFriendlyFieldName(dc, dc.ColumnName) == importColumnNames[i])
{
columnNameMap.Add(dc.ColumnName, i);
}
}
// If the column header was not matched - try matching again (case insensitive)...
if (!columnNameMap.ContainsValue(i))
{
// Map the friendly field name from the incoming text to the matching column in the datatable (case insensitive)...
foreach (DataColumn dc in destinationTable.Columns)
{
if (GetFriendlyFieldName(dc, dc.ColumnName).ToLower() == importColumnNames[i].ToLower())
{
columnNameMap.Add(dc.ColumnName, i);
}
}
}
// If the column header was still not matched - try the raw table field name...
if (!columnNameMap.ContainsValue(i))
{
// Map the friendly field name from the incoming text to the matching column in the datatable (case insensitive)...
foreach (DataColumn dc in destinationTable.Columns)
{
if (dc.ColumnName.ToLower() == importColumnNames[i].ToLower())
{
columnNameMap.Add(dc.ColumnName, i);
}
}
}
}
// Now that we have the column map, start processing the rows (starting with the one right after the column header row)...
for (int i = columnHeaderRowIndex + 1; i < rawImportRows.Length; i++)
{
DataRow dr = null;
string[] rawFieldData = rawImportRows[i].Split(columnDelimiters, StringSplitOptions.None);
if (primaryKeyFound)
{
System.Collections.Generic.List<object> rowKeys = new System.Collections.Generic.List<object>();
// Build the primary key to get the row to edit...
foreach (DataColumn pKeyColumn in destinationTable.PrimaryKey)
{
object keyValue;
if (string.IsNullOrEmpty(rawFieldData[columnNameMap[pKeyColumn.ColumnName]].ToString()))
{
keyValue = DBNull.Value;
}
else
{
keyValue = rawFieldData[columnNameMap[pKeyColumn.ColumnName]];
}
rowKeys.Add(keyValue);
}
// Get the row to update (or create a new one for insert if an existing one is not found)...
// First - attempt to find a row in the DataTable that matches the primary key(s)...
try
{
dr = destinationTable.Rows.Find(rowKeys.ToArray());
}
catch
{
// There was an error in the row key data - so bail out and do nothing...
}
if (dr == null)
{
// No row exists in this DataTable for the given primary key(s), so create a new blank row to fill...
dr = destinationTable.NewRow();
// and add it to the DataTable...
destinationTable.Rows.Add(dr);
}
}
else // Find the row using the unique keys...
{
DataRow[] matchingRows = null;
string rowFilter = "";
foreach (DataColumn uKeyColumn in uniqueKeys)
{
if (columnNameMap[uKeyColumn.ColumnName] >= 0 &&
columnNameMap[uKeyColumn.ColumnName] <= (rawFieldData.Length - 1) &&
!string.IsNullOrEmpty(rawFieldData[columnNameMap[uKeyColumn.ColumnName]]))
{
string newValue = "";
// Perform a reverse lookup to get the key if this is a ForeignKey field...
if (lookupTables.IsValidFKField(uKeyColumn))
{
if (!string.IsNullOrEmpty(rawFieldData[columnNameMap[uKeyColumn.ColumnName]].ToString().Trim()))
{
newValue = lookupTables.GetPKeyValueMember(dr, uKeyColumn.ExtendedProperties["foreign_key_dataview_name"].ToString(),
rawFieldData[columnNameMap[uKeyColumn.ColumnName]].ToString().Trim(),
-1).ToString();
// If the lookup attempt returned the default value - indicate to the user that the lookup failed...
if (newValue.Equals("-1"))
{
dr.SetColumnError(uKeyColumn.ColumnName, "\tCould not find lookup value: " + rawFieldData[columnNameMap[uKeyColumn.ColumnName]].ToString());
}
}
}
// Perform a reverse lookup to get the value if this is a Code_Value field...
else if (lookupTables.IsValidCodeValueField(uKeyColumn))
{
if (!string.IsNullOrEmpty(rawFieldData[columnNameMap[uKeyColumn.ColumnName]].ToString().Trim()))
{
newValue = lookupTables.GetCodeValueValueMember(rawFieldData[columnNameMap[uKeyColumn.ColumnName]].ToString(),
uKeyColumn.ExtendedProperties["group_name"].ToString(),
"!Error! - GetValueMember method failed to find display member");
// If the lookup attempt returned the default value - indicate to the user that the lookup failed...
if (newValue.Equals("!Error! - GetValueMember method failed to find display member"))
{
dr.SetColumnError(uKeyColumn.ColumnName, "\tCould not find lookup value: " + rawFieldData[columnNameMap[uKeyColumn.ColumnName]].ToString());
}
}
}
// Doesn't require a lookup...
else
{
newValue = rawFieldData[columnNameMap[uKeyColumn.ColumnName]];
}
if (uKeyColumn.DataType == typeof(string))
{
rowFilter += uKeyColumn.ColumnName + "='" + newValue + "' AND ";
}
else
{
rowFilter += uKeyColumn.ColumnName + "=" + newValue + " AND ";
}
}
else
{
rowFilter += uKeyColumn.ColumnName + " IS NULL AND ";
}
}
rowFilter = rowFilter.Substring(0, rowFilter.LastIndexOf(" AND "));
try
{
matchingRows = destinationTable.Select(rowFilter);
}
catch
{
matchingRows = new DataRow[] { };
}
if (matchingRows.Length > 0)
{
dr = matchingRows[0];
}
else
{
// Could not find a matching row, so set the dr to null (this will effectively ignore this import record)
//dr = null;
// No row exists in this DataTable for the given primary key(s), so create a new blank row to fill...
dr = destinationTable.NewRow();
// and add it to the DataTable...
destinationTable.Rows.Add(dr);
}
}
if (dr != null)
{
populateRowWithImportData(dr, rawFieldData, columnNameMap, lookupTables);
}
else
{
missingRows++;
}
}
}
processedImportSuccessfully = true;
return processedImportSuccessfully;
}
private void populateRowWithImportData(DataRow dr, string[] fieldValues, System.Collections.Generic.Dictionary<string, int> columnNameMap, LookupTables lookupTables)
{
DataTable dt = dr.Table;
foreach (string tableColumnName in columnNameMap.Keys)
{
// Only update the write enabled columns in this row...
if (!dt.Columns[tableColumnName].ReadOnly)
{
string newValue = "";
DataColumn dc = dt.Columns[tableColumnName];
int fieldIndex = columnNameMap[tableColumnName];
if (fieldValues.Length <= fieldIndex) continue;
// Perform a reverse lookup to get the key if this is a ForeignKey field...
if (lookupTables.IsValidFKField(dc))
{
if (!string.IsNullOrEmpty(fieldValues[fieldIndex].ToString().Trim()))
{
newValue = lookupTables.GetPKeyValueMember(dr, dc.ExtendedProperties["foreign_key_dataview_name"].ToString(),
fieldValues[fieldIndex].ToString().Trim(),
-1).ToString();
// If the lookup attempt returned the default value - indicate to the user that the lookup failed...
if (newValue.Equals("-1"))
{
dr.SetColumnError(tableColumnName, "\tCould not find lookup value: " + fieldValues[fieldIndex].ToString());
}
}
}
// Perform a reverse lookup to get the value if this is a Code_Value field...
else if (lookupTables.IsValidCodeValueField(dc))
{
if (!string.IsNullOrEmpty(fieldValues[fieldIndex].ToString().Trim()))
{
newValue = lookupTables.GetCodeValueValueMember(fieldValues[fieldIndex].ToString(),
dc.ExtendedProperties["group_name"].ToString(),
"!Error! - GetValueMember method failed to find display member");
// If the lookup attempt returned the default value - indicate to the user that the lookup failed...
if (newValue.Equals("!Error! - GetValueMember method failed to find display member"))
{
dr.SetColumnError(tableColumnName, "\tCould not find lookup value: " + fieldValues[fieldIndex].ToString());
}
}
}
// Doesn't require a lookup...
else
{
newValue = fieldValues[fieldIndex];
}
// If the newValue is null attempt to retrieve the default value before further processing...
if (string.IsNullOrEmpty(newValue) &&
dt.Columns[tableColumnName].ExtendedProperties.Contains("default_value") &&
dt.Columns[tableColumnName].ExtendedProperties["default_value"].ToString().Length > 0)
{
newValue = dt.Columns[tableColumnName].ExtendedProperties["default_value"].ToString();
}
// Set the newValue to a default value if it is empty or null...
if (string.IsNullOrEmpty(newValue) || newValue == "{DBNull.Value}")
{
if (dt.Columns[tableColumnName].ExtendedProperties.Contains("is_nullable") &&
dt.Columns[tableColumnName].ExtendedProperties["is_nullable"].ToString() == "Y")
{
if (!dr[tableColumnName].Equals(DBNull.Value) && !dr[tableColumnName].Equals(newValue)) dr[tableColumnName] = DBNull.Value;
}
else
{
dr.SetColumnError(tableColumnName, "\tThis value cannot be empty (null)");
}
}
// Convert the newValue string to the datatype for this column...
else if ((dt.Columns[tableColumnName].DataType == typeof(int) ||
dt.Columns[tableColumnName].DataType == typeof(Int16) ||
dt.Columns[tableColumnName].DataType == typeof(Int32) ||
dt.Columns[tableColumnName].DataType == typeof(Int64)) &&
!dr.GetColumnsInError().Contains(dt.Columns[tableColumnName]))
{
int tempValue = 0;
if (Int32.TryParse(newValue, out tempValue))
{
if (!dr[tableColumnName].Equals(tempValue)) dr[tableColumnName] = tempValue;
}
else
{
dr.SetColumnError(tableColumnName, "\tValue '" + newValue + "' cannot be converted to an integer");
}
}
else if (dt.Columns[tableColumnName].DataType == typeof(Decimal) && !dr.GetColumnsInError().Contains(dt.Columns[tableColumnName]))
{
Decimal tempValue = new Decimal();
if (Decimal.TryParse(newValue, out tempValue))
{
if (!dr[tableColumnName].Equals(tempValue)) dr[tableColumnName] = tempValue;
}
else
{
dr.SetColumnError(tableColumnName, "\tValue '" + newValue + "' cannot be converted to a decimal");
}
}
else if (dt.Columns[tableColumnName].DataType == typeof(DateTime) && !dr.GetColumnsInError().Contains(dt.Columns[tableColumnName]))
{
DateTime tempValue = new DateTime();
if (DateTime.TryParse(newValue, out tempValue))
{
if (!dr[tableColumnName].Equals(tempValue)) dr[tableColumnName] = tempValue;
}
else
{
// Basic DateTime conversion failed - look to see if the user provided a hint about how to interpret this date value...
// Look to see if there is a column provided that matches this current column name + "_code"...
if (dt.Columns.Contains(tableColumnName + "_code"))
{
string dateFormat = "MM/dd/yyyy";
dateFormat = lookupTables.GetCodeValueValueMember(fieldValues[columnNameMap[tableColumnName + "_code"]].ToString().Trim(), dr.Table.Columns[tableColumnName + "_code"].ExtendedProperties["group_name"].ToString(), dateFormat);
if (DateTime.TryParseExact(newValue, dateFormat, null, System.Globalization.DateTimeStyles.AssumeLocal, out tempValue))
{
if (!dr[tableColumnName].Equals(tempValue)) dr[tableColumnName] = tempValue;
}
else
{
dr.SetColumnError(tableColumnName, "\tValue '" + newValue + "' cannot be converted to a Date/Time");
}
}
}
}
else if (dt.Columns[tableColumnName].DataType == typeof(string) && !dr.GetColumnsInError().Contains(dt.Columns[tableColumnName]))
{
if (dc.ExtendedProperties.Contains("max_length") && !dr.GetColumnsInError().Contains(dt.Columns[tableColumnName]))
{
int maxLength = 0;
if (Int32.TryParse(dc.ExtendedProperties["max_length"].ToString(), out maxLength))
{
if (newValue.Length <= maxLength ||
maxLength == -1)
{
// Strip off leading and trailing double quotes - typically added by Excel when it embeds a new line character using the ALT-Enter keystroke...
if (newValue.Trim().StartsWith("\"") && newValue.Trim().EndsWith("\""))
{
newValue = newValue.Trim().TrimStart('"').TrimEnd('"');
}
if (!dr[tableColumnName].Equals(newValue)) dr[tableColumnName] = newValue;
}
else
{
dr.SetColumnError(tableColumnName, "\tValue exceeds maximum length - truncated to " + maxLength.ToString() + " characters");
dr[tableColumnName] = newValue.Substring(0, maxLength); // Truncate the value (so the user can see what is legal to be pasted in)
}
}
}
}
else
{
// Not sure what datatype got us here - bailout...
}
}
}
}