Beispiel #1
0
        public void buildReadOnlyDataGridView(DataGridView dataGridView, DataTable dataTable, LookupTables lookupTables)
        {
            DataTable newDataTable = new DataTable(dataTable.TableName);
            System.Collections.Generic.List<DataColumn> pKeys = new System.Collections.Generic.List<DataColumn>();

            // Clear the DGV columns and set the autogenerate columns property...
            dataGridView.Columns.Clear();
            dataGridView.AutoGenerateColumns = true;

            // First build the columns of the new table...
            foreach (DataColumn dataColumn in dataTable.Columns)
            {
                string newColumnName = dataColumn.ColumnName;
                newDataTable.Columns.Add(dataColumn.ColumnName);
                // Set the column header...
                newDataTable.Columns[newColumnName].Caption = GetFriendlyFieldName(dataColumn, dataColumn.ColumnName);

                // Add the extended properties from the source table...
                foreach (string key in dataColumn.ExtendedProperties.Keys)
                {
                    newDataTable.Columns[newColumnName].ExtendedProperties.Add(key, dataColumn.ExtendedProperties[key]);
                }
                // Add this column to the primary keys list if extended properties indicate it is a primary key column
                if (newDataTable.Columns[newColumnName].ExtendedProperties.Contains("is_primary_key") &&
                    newDataTable.Columns[newColumnName].ExtendedProperties["is_primary_key"].ToString() == "Y")
                {
                    pKeys.Add(newDataTable.Columns[newColumnName]);
                }

                if (dataColumn.ExtendedProperties.Contains("gui_hint"))
                {
                    switch (dataColumn.ExtendedProperties["gui_hint"].ToString())
                    {
                        case "LARGE_SINGLE_SELECT_CONTROL":
                            break;
                        case "SMALL_SINGLE_SELECT_CONTROL":
                            break;
                        default:
                            // This column is not a Code/Value or FK lookup column, so set the datatype for the column (to facilitate sorting)...
                            newDataTable.Columns[newColumnName].DataType = dataColumn.DataType;
                            if(dataColumn.DataType == typeof(DateTime))
                            {
                                newDataTable.Columns[newColumnName].DateTimeMode = DataSetDateTime.Unspecified;
                            }
                            break;
                    }
                }
                // Set the readonly property...
                newDataTable.Columns[newColumnName].ReadOnly = dataColumn.ReadOnly;
            }

            // Set the datatable's primary key...
            newDataTable.PrimaryKey = pKeys.ToArray();

            // Before populating the rows in the table, pre-load the FKey LU dictionaries with all needed values...
            foreach (DataColumn dc in dataTable.Columns)
            {
                if (lookupTables.IsValidFKField(dc))
                {
                    string lookupTable = dc.ExtendedProperties["foreign_key_dataview_name"].ToString();
                    int[] fkeys = new int[dataTable.Rows.Count];
                    // Build the list of FKeys that need to be resolved in the dicitonary...
                    for(int i=0; i<dataTable.Rows.Count; i++)
                    {
                        //fkeys[i] = (int)dataTable.Rows[i][dc];
                        int fkey = 0;
                        if(int.TryParse(dataTable.Rows[i][dc].ToString(), out fkey))
                        {
                            fkeys[i] = fkey;
                        }
                    }
                    lookupTables.PreLoadPKeyLUTDictionary(lookupTable, fkeys);
                }
            }

            // Now populate the rows of the new table...
            foreach (DataRow dr in dataTable.Rows)
            {
                if (dr.RowState != DataRowState.Deleted &&
                    dr.RowState != DataRowState.Detached)
                {
                    DataRow newDataRow = newDataTable.NewRow();
                    foreach (DataColumn dc in newDataTable.Columns)
                    {
                        if (lookupTables.IsValidFKField(dc))
                        {
                            string lookupTable = dc.ExtendedProperties["foreign_key_dataview_name"].ToString();
                            if (dr[dc.ColumnName] != DBNull.Value)
                            {
                                //newDataRow[dc.ColumnName] = lookupTables.GetDisplayMember(lookupTable, dr[dc.ColumnName].ToString(), "", dr[dc.ColumnName].ToString());
                                newDataRow[dc.ColumnName] = lookupTables.GetPKeyDisplayMember(lookupTable, (int)dr[dc.ColumnName], dr[dc.ColumnName].ToString());
                            }
                        }
                        else if (lookupTables.IsValidCodeValueField(dc))
                        {
                            if (dr[dc.ColumnName] != DBNull.Value)
                            {
                                //newDataRow[dc.ColumnName] = lookupTables.GetDisplayMember("code_value_lookup", dr[dc.ColumnName].ToString(), "group_name='" + dc.ExtendedProperties["group_name"].ToString() + "'", dr[dc.ColumnName].ToString());
                                newDataRow[dc.ColumnName] = lookupTables.GetCodeValueDisplayMember(dc.ExtendedProperties["group_name"].ToString() + dr[dc.ColumnName].ToString(), dr[dc.ColumnName].ToString());
                            }
                        }
                        else
                        {
                            if (!string.IsNullOrEmpty(dr[dc.ColumnName].ToString()))
                            {
                                newDataRow[dc.ColumnName] = dr[dc.ColumnName].ToString();
                                if (dc.DataType == typeof(DateTime))
                                {
                                    if ("created_date modified_date owned_date".Contains(dc.ColumnName))
                                    {
                                        // If the date field is created_date, modified_date, or owned_date it is stored in the database
                                        // as a UTC datetime by the middle tier.  When the datetime field is sent to the CT it has a DateTimeMode property of
                                        // "DataSetDateTime.Unspecified".  So convert the datetime to LocalTime using the ToLocalTime() method call...
                                        newDataRow[dc.ColumnName] = ((DateTime)dr[dc.ColumnName]).ToLocalTime();
            //DateTime raw = (DateTime)dr[dc.ColumnName];
            //DateTime local = ((DateTime)dr[dc.ColumnName]).ToLocalTime();
            //DateTime utc = ((DateTime)dr[dc.ColumnName]).ToUniversalTime();
                                    }
                                }
                            }
                        }
                    }
                    // Add the row to the new data table...
                    newDataTable.Rows.Add(newDataRow.ItemArray);
                }
            }

            // Set the sort and filter properties in the new datatable...
            newDataTable.DefaultView.Sort = dataTable.DefaultView.Sort;
            newDataTable.DefaultView.RowFilter = dataTable.DefaultView.RowFilter;

            newDataTable.DefaultView.AllowDelete = false;
            newDataTable.DefaultView.AllowEdit = false;
            newDataTable.DefaultView.AllowNew = false;

            // Bind the DataGridView to the datasource passed into this procedure...
            newDataTable.AcceptChanges();
            // NOTE: Sometimes the datagridview's datasource is actually a bindingsource (to wire up the binding navigator)
            // so binding must be handled differently...
            if (dataGridView != null &&
                dataGridView.DataSource != null &&
                dataGridView.DataSource.GetType() == typeof(BindingSource))
            {
                // Bind the new table to the default binding source (the bindingNavigator and mainDGV are both bound to this bindingSource)...
                ((BindingSource)dataGridView.DataSource).DataSource = newDataTable;
            }
            else
            {
                dataGridView.DataSource = newDataTable;
            }

            foreach (DataGridViewColumn dgvc in dataGridView.Columns)
            {
                // Turn on wrap mode for all columns set as varchar(max) and set the gridview column text...
                if (newDataTable.Columns[dgvc.Name].ExtendedProperties.Contains("max_length") &&
                    newDataTable.Columns[dgvc.Name].ExtendedProperties["max_length"].ToString().Trim() == "-1")
                {
                    dgvc.DefaultCellStyle.WrapMode = DataGridViewTriState.True;
                }
                // Get the text for the column header...
                dgvc.HeaderText = GetFriendlyFieldName(newDataTable.Columns[dgvc.Name], newDataTable.Columns[dgvc.Name].ColumnName);
                // Get the text for the column description...
                if (newDataTable.Columns[dgvc.Name].ExtendedProperties.Contains("description") &&
                    !string.IsNullOrEmpty(newDataTable.Columns[dgvc.Name].ExtendedProperties["description"].ToString()))
                {
                    dgvc.ToolTipText = newDataTable.Columns[dgvc.Name].ExtendedProperties["description"].ToString();
                }
            }
        }
Beispiel #2
0
        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;
        }
Beispiel #3
0
        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...
                    }
                }
            }
        }