/// <summary> /// Processes all of the fields on a given form, page-by-page, except for the fields on the base table. /// </summary> /// <param name="sourceView">The source form</param> /// <param name="destinationView">The destination form</param> /// <param name="destinationGUIDList">The list of GUIDs that exist in the destination</param> private void ProcessPages(View sourceView, View destinationView, List<string> destinationGUIDList) { for (int i = 0; i < sourceView.Pages.Count; i++) { sourceView.LoadFirstRecord(); OnAddStatusMessage(string.Format(ImportExportSharedStrings.PROCESSING_PAGE, (i + 1).ToString(), sourceView.Pages.Count.ToString())); int recordsInserted = 0; int recordsUpdated = 0; Page sourcePage = sourceView.Pages[i]; Page destinationPage = destinationView.Pages[i]; try { List<string> fieldsToSkip = new List<string>(); foreach (Field sourceField in sourceView.Fields) { bool found = false; foreach (Field destinationField in destinationView.Fields) { if (destinationField.Name.ToLower().Equals(sourceField.Name.ToLower())) { found = true; } } if (!found) { fieldsToSkip.Add(sourceField.Name); } } if (ColumnsToNull != null && ColumnsToNull.ContainsKey(sourceView.Name)) { List<string> toNull = ColumnsToNull[sourceView.Name]; foreach (string s in toNull) { if (!fieldsToSkip.Contains(s)) { fieldsToSkip.Add(s); } } } IDataReader sourceReader = sourceProjectDataDriver.GetTableDataReader(sourcePage.TableName); while (sourceReader.Read()) { //if (importWorker.CancellationPending) //{ // this.BeginInvoke(new SetStatusDelegate(AddStatusMessage), "Import cancelled."); // return; //} if (OnCheckForCancellation()) { OnAddStatusMessage(ImportExportSharedStrings.IMPORT_CANCELLED); sourceReader.Close(); sourceReader.Dispose(); return; } WordBuilder fieldNames = new WordBuilder(StringLiterals.COMMA); WordBuilder fieldValues = new WordBuilder(StringLiterals.COMMA); List<QueryParameter> fieldValueParams = new List<QueryParameter>(); string GUID = sourceReader["GlobalRecordId"].ToString(); if (sourceGUIDs != null && !sourceGUIDs.Contains(GUID)) { continue; } if (destinationGUIDList.Contains(GUID) && update) { // UPDATE matching records string updateHeader = string.Empty; string whereClause = string.Empty; fieldValueParams = new List<QueryParameter>(); StringBuilder sb = new StringBuilder(); int columnIndex = 0; // Build the Update statement which will be reused sb.Append(SqlKeyWords.UPDATE); sb.Append(StringLiterals.SPACE); sb.Append(destinationProjectDataDriver.InsertInEscape(destinationPage.TableName)); sb.Append(StringLiterals.SPACE); sb.Append(SqlKeyWords.SET); sb.Append(StringLiterals.SPACE); updateHeader = sb.ToString(); sb.Remove(0, sb.ToString().Length); // Build the WHERE caluse which will be reused sb.Append(SqlKeyWords.WHERE); sb.Append(StringLiterals.SPACE); sb.Append(destinationProjectDataDriver.InsertInEscape(ColumnNames.GLOBAL_RECORD_ID)); sb.Append(StringLiterals.EQUAL); sb.Append("'"); sb.Append(GUID); sb.Append("'"); whereClause = sb.ToString(); sb.Remove(0, sb.ToString().Length); int fieldsInQuery = 0; // Now build the field update statements in 100 field chunks foreach (RenderableField renderableField in sourcePage.Fields) { if (renderableField is GridField || renderableField is GroupField || renderableField is ImageField || fieldsToSkip.Contains(renderableField.Name)) // TODO: Someday, allow image fields { continue; } else if (renderableField is IDataField) { IDataField dataField = (IDataField)renderableField; if (dataField.FieldType != MetaFieldType.UniqueKey && dataField is RenderableField) { columnIndex += 1; //if (dataField.CurrentRecordValueObject == null) if (sourceReader[renderableField.Name] == DBNull.Value || string.IsNullOrEmpty(sourceReader[renderableField.Name].ToString())) { //sb.Append(SqlKeyWords.NULL); } else { switch (dataField.FieldType) { case MetaFieldType.Date: case MetaFieldType.DateTime: case MetaFieldType.Time: fieldValueParams.Add(new QueryParameter("@" + renderableField.Name, DbType.DateTime, Convert.ToDateTime(sourceReader[renderableField.Name]))); break; case MetaFieldType.Checkbox: fieldValueParams.Add(new QueryParameter("@" + renderableField.Name, DbType.Boolean, Convert.ToBoolean(sourceReader[renderableField.Name]))); break; case MetaFieldType.CommentLegal: case MetaFieldType.LegalValues: case MetaFieldType.Codes: case MetaFieldType.Text: case MetaFieldType.TextUppercase: case MetaFieldType.PhoneNumber: case MetaFieldType.UniqueRowId: case MetaFieldType.ForeignKey: case MetaFieldType.GlobalRecordId: case MetaFieldType.Multiline: fieldValueParams.Add(new QueryParameter("@" + renderableField.Name, DbType.String, sourceReader[renderableField.Name])); break; case MetaFieldType.Number: case MetaFieldType.RecStatus: case MetaFieldType.YesNo: fieldValueParams.Add(new QueryParameter("@" + renderableField.Name, DbType.Single, sourceReader[renderableField.Name])); break; case MetaFieldType.GUID: fieldValueParams.Add(new QueryParameter("@" + renderableField.Name, DbType.Guid, sourceReader[renderableField.Name])); break; case MetaFieldType.Option: if (optionFieldsAsStrings.Contains(renderableField.Name)) { fieldValueParams.Add(new QueryParameter("@" + renderableField.Name, DbType.String, sourceReader[renderableField.Name])); } else { fieldValueParams.Add(new QueryParameter("@" + renderableField.Name, DbType.Int16, sourceReader[renderableField.Name])); } break; case MetaFieldType.Image: OnAddStatusMessage(string.Format(ImportExportSharedStrings.WARNING_FIELD_NOT_IMPORTED, renderableField.Name)); continue; default: throw new ApplicationException(ImportExportSharedStrings.UNRECOGNIZED_FIELD_TYPE); } sb.Append(StringLiterals.LEFT_SQUARE_BRACKET); sb.Append(((Epi.INamedObject)dataField).Name); sb.Append(StringLiterals.RIGHT_SQUARE_BRACKET); sb.Append(StringLiterals.EQUAL); sb.Append(StringLiterals.COMMERCIAL_AT); sb.Append(((Epi.INamedObject)dataField).Name); sb.Append(StringLiterals.COMMA); } } if ((columnIndex % 100) == 0 && columnIndex > 0) { if (sb.ToString().LastIndexOf(StringLiterals.COMMA).Equals(sb.ToString().Length - 1)) { sb.Remove(sb.ToString().LastIndexOf(StringLiterals.COMMA), 1); } Query updateQuery = destinationProjectDataDriver.CreateQuery(updateHeader + StringLiterals.SPACE + sb.ToString() + StringLiterals.SPACE + whereClause); updateQuery.Parameters = fieldValueParams; destinationProjectDataDriver.ExecuteNonQuery(updateQuery); columnIndex = 0; sb.Remove(0, sb.ToString().Length); fieldValueParams.Clear(); } } fieldsInQuery++; } if (fieldsInQuery == 0) { continue; } if (sb.Length > 0) { if (sb.ToString().LastIndexOf(StringLiterals.COMMA).Equals(sb.ToString().Length - 1)) { int startIndex = sb.ToString().LastIndexOf(StringLiterals.COMMA); if (startIndex >= 0) { sb.Remove(startIndex, 1); } } Query updateQuery = destinationProjectDataDriver.CreateQuery(updateHeader + StringLiterals.SPACE + sb.ToString() + StringLiterals.SPACE + whereClause); updateQuery.Parameters = fieldValueParams; destinationProjectDataDriver.ExecuteNonQuery(updateQuery); columnIndex = 0; sb.Remove(0, sb.ToString().Length); fieldValueParams.Clear(); } recordsUpdated++; } else if (!destinationGUIDList.Contains(GUID) && append) { fieldNames.Append("GlobalRecordId"); fieldValues.Append("@GlobalRecordId"); fieldValueParams.Add(new QueryParameter("@GlobalRecordId", DbType.String, GUID)); int fieldsInQuery = 0; // INSERT unmatched records foreach (RenderableField renderableField in sourcePage.Fields) { if (renderableField is GridField || renderableField is GroupField || fieldsToSkip.Contains(renderableField.Name)) { continue; } else if (renderableField is IDataField) { IDataField dataField = (IDataField)renderableField; if (dataField is UniqueKeyField) { continue; } else { if (sourceReader[renderableField.Name] == DBNull.Value || string.IsNullOrEmpty(sourceReader[renderableField.Name].ToString())) //if (dataField.CurrentRecordValueObject == null) { //fieldValues.Append(" null "); // TODO: Check to make sure we shouldn't be using this } else { String fieldName = ((Epi.INamedObject)dataField).Name; //fieldValueParams.Add(dataField.CurrentRecordValueAsQueryParameter); switch (dataField.FieldType) { case MetaFieldType.Date: case MetaFieldType.DateTime: case MetaFieldType.Time: fieldValueParams.Add(new QueryParameter("@" + fieldName, DbType.DateTime, Convert.ToDateTime(sourceReader[fieldName]))); break; case MetaFieldType.Checkbox: fieldValueParams.Add(new QueryParameter("@" + fieldName, DbType.Boolean, Convert.ToBoolean(sourceReader[fieldName]))); break; case MetaFieldType.CommentLegal: case MetaFieldType.LegalValues: case MetaFieldType.Codes: case MetaFieldType.Text: case MetaFieldType.TextUppercase: case MetaFieldType.PhoneNumber: case MetaFieldType.UniqueRowId: case MetaFieldType.ForeignKey: case MetaFieldType.GlobalRecordId: case MetaFieldType.Multiline: fieldValueParams.Add(new QueryParameter("@" + fieldName, DbType.String, sourceReader[fieldName])); break; case MetaFieldType.Number: case MetaFieldType.YesNo: case MetaFieldType.RecStatus: fieldValueParams.Add(new QueryParameter("@" + fieldName, DbType.Single, sourceReader[fieldName])); break; case MetaFieldType.GUID: fieldValueParams.Add(new QueryParameter("@" + fieldName, DbType.Guid, sourceReader[fieldName])); break; case MetaFieldType.Option: if (optionFieldsAsStrings.Contains(renderableField.Name)) { fieldValueParams.Add(new QueryParameter("@" + renderableField.Name, DbType.String, sourceReader[fieldName])); } else { fieldValueParams.Add(new QueryParameter("@" + renderableField.Name, DbType.Int16, sourceReader[fieldName])); } break; case MetaFieldType.Image: OnAddStatusMessage(string.Format(ImportExportSharedStrings.WARNING_FIELD_NOT_IMPORTED, renderableField.Name)); continue; default: throw new ApplicationException(ImportExportSharedStrings.UNRECOGNIZED_FIELD_TYPE); } fieldNames.Append(destinationProjectDataDriver.InsertInEscape(((Epi.INamedObject)dataField).Name)); fieldValues.Append("@" + fieldName); } } } fieldsInQuery++; } if (fieldsInQuery == 0) { continue; } // Concatenate the query clauses into one SQL statement. StringBuilder sb = new StringBuilder(); sb.Append(" insert into "); sb.Append(destinationProjectDataDriver.InsertInEscape(destinationPage.TableName)); sb.Append(StringLiterals.SPACE); sb.Append(Util.InsertInParantheses(fieldNames.ToString())); sb.Append(" values ("); sb.Append(fieldValues.ToString()); sb.Append(") "); Query insertQuery = destinationProjectDataDriver.CreateQuery(sb.ToString()); insertQuery.Parameters = fieldValueParams; destinationProjectDataDriver.ExecuteNonQuery(insertQuery); recordsInserted++; } //this.BeginInvoke(new SetProgressBarDelegate(IncrementProgressBarValue), 1); OnSetProgress(1); } sourceReader.Close(); sourceReader.Dispose(); } catch (Exception ex) { OnAddStatusMessage(string.Format(ImportExportSharedStrings.ERROR_WITH_MESSAGE, ex.Message)); } finally { } if (update && append) { OnAddStatusMessage(string.Format(ImportExportSharedStrings.IMPORT_PAGE_UPDATED_AND_APPENDED, destinationPage.Name, recordsInserted.ToString(), recordsUpdated.ToString())); } else if (update && !append) { OnAddStatusMessage(string.Format(ImportExportSharedStrings.IMPORT_PAGE_UPDATED, destinationPage.Name, recordsUpdated.ToString())); } else if (!update && append) { OnAddStatusMessage(string.Format(ImportExportSharedStrings.IMPORT_PAGE_APPENDED, destinationPage.Name, recordsInserted.ToString())); } } }
/// <summary> /// Processes a form's base table /// </summary> /// <param name="sourceView">The source form</param> /// <param name="destinationView">The destination form</param> /// <param name="destinationGUIDList">The list of GUIDs that exist in the destination</param> private void ProcessBaseTable(View sourceView, View destinationView, List<string> destinationGUIDList) { sourceView.LoadFirstRecord(); OnAddStatusMessage(ImportExportSharedStrings.PROCESSING_BASE_TABLE); int recordsInserted = 0; int recordsUpdated = 0; string sourceTable = sourceView.TableName; string destinationTable = destinationView.TableName; optionFieldsAsStrings = new List<string>(); // Check for string-based option fields. foreach (Field f in destinationView.Fields) { if (f is OptionField) { OptionField optionField = f as OptionField; if (optionField != null) { DataTable dt = destinationProjectDataDriver.GetTopTwoTable(optionField.Page.TableName); if (dt.Columns[optionField.Name].DataType.ToString().Equals("System.String", StringComparison.OrdinalIgnoreCase)) { optionFieldsAsStrings.Add(f.Name); } } } } try { List<string> newGUIDList = new List<string>(); IDataReader sourceReader = sourceProjectDataDriver.GetTableDataReader(sourceView.TableName); while (sourceReader.Read()) { object recordStatus = sourceReader["RECSTATUS"]; QueryParameter paramRecordStatus = new QueryParameter("@RECSTATUS", DbType.Int32, recordStatus); //if (importWorker.CancellationPending) //{ // this.BeginInvoke(new SetStatusDelegate(AddStatusMessage), "Import cancelled."); // return; //} if (OnCheckForCancellation()) { OnAddStatusMessage(ImportExportSharedStrings.IMPORT_CANCELLED); sourceReader.Close(); sourceReader.Dispose(); sourceReader = null; return; } WordBuilder fieldNames = new WordBuilder(StringLiterals.COMMA); WordBuilder fieldValues = new WordBuilder(StringLiterals.COMMA); List<QueryParameter> fieldValueParams = new List<QueryParameter>(); string GUID = sourceReader["GlobalRecordId"].ToString(); if (sourceGUIDs != null && !sourceGUIDs.Contains(GUID)) { continue; } fieldNames.Append("GlobalRecordId"); fieldValues.Append("@GlobalRecordId"); string FKEY = sourceReader["FKEY"].ToString(); QueryParameter paramFkey = new QueryParameter("@FKEY", DbType.String, FKEY); // don't add this yet QueryParameter paramGUID = new QueryParameter("@GlobalRecordId", DbType.String, GUID); fieldValueParams.Add(paramGUID); if (destinationGUIDList.Contains(GUID)) { if (update) { // UPDATE matching records string updateHeader = string.Empty; string whereClause = string.Empty; fieldValueParams = new List<QueryParameter>(); StringBuilder sb = new StringBuilder(); // Build the Update statement which will be reused sb.Append(SqlKeyWords.UPDATE); sb.Append(StringLiterals.SPACE); sb.Append(destinationProjectDataDriver.InsertInEscape(destinationTable)); sb.Append(StringLiterals.SPACE); sb.Append(SqlKeyWords.SET); sb.Append(StringLiterals.SPACE); updateHeader = sb.ToString(); sb.Remove(0, sb.ToString().Length); // Build the WHERE caluse which will be reused sb.Append(SqlKeyWords.WHERE); sb.Append(StringLiterals.SPACE); sb.Append(destinationProjectDataDriver.InsertInEscape(ColumnNames.GLOBAL_RECORD_ID)); sb.Append(StringLiterals.EQUAL); sb.Append("'"); sb.Append(GUID); sb.Append("'"); whereClause = sb.ToString(); sb.Remove(0, sb.ToString().Length); //if (sourceView.ForeignKeyFieldExists) if (!string.IsNullOrEmpty(FKEY)) { sb.Append(StringLiterals.LEFT_SQUARE_BRACKET); sb.Append("FKEY"); sb.Append(StringLiterals.RIGHT_SQUARE_BRACKET); sb.Append(StringLiterals.EQUAL); sb.Append(StringLiterals.COMMERCIAL_AT); sb.Append("FKEY"); fieldValueParams.Add(paramFkey); Query updateQuery = destinationProjectDataDriver.CreateQuery(updateHeader + StringLiterals.SPACE + sb.ToString() + StringLiterals.SPACE + whereClause); updateQuery.Parameters = fieldValueParams; destinationProjectDataDriver.ExecuteNonQuery(updateQuery); sb.Remove(0, sb.ToString().Length); fieldValueParams.Clear(); recordsUpdated++; } } } else { if (append) { if (!string.IsNullOrEmpty(FKEY)) { fieldNames.Append("FKEY"); fieldValues.Append("@FKEY"); fieldValueParams.Add(paramFkey); } fieldNames.Append("RECSTATUS"); fieldValues.Append("@RECSTATUS"); fieldValueParams.Add(paramRecordStatus); // Concatenate the query clauses into one SQL statement. StringBuilder sb = new StringBuilder(); sb.Append(" insert into "); sb.Append(destinationProjectDataDriver.InsertInEscape(destinationTable)); sb.Append(StringLiterals.SPACE); sb.Append(Util.InsertInParantheses(fieldNames.ToString())); sb.Append(" values ("); sb.Append(fieldValues.ToString()); sb.Append(") "); Query insertQuery = destinationProjectDataDriver.CreateQuery(sb.ToString()); insertQuery.Parameters = fieldValueParams; destinationProjectDataDriver.ExecuteNonQuery(insertQuery); recordsInserted++; } } OnSetProgress(1); } sourceReader.Close(); sourceReader.Dispose(); sourceReader = null; } catch (Exception ex) { OnAddStatusMessage(string.Format(ImportExportSharedStrings.ERROR_WITH_MESSAGE, ex.Message)); } finally { } if (update && append) { OnAddStatusMessage(string.Format(ImportExportSharedStrings.IMPORT_TABLE_UPDATED_AND_APPENDED, destinationTable, recordsInserted.ToString(), recordsUpdated.ToString())); } else if (update && !append) { OnAddStatusMessage(string.Format(ImportExportSharedStrings.IMPORT_TABLE_UPDATED, destinationTable, recordsUpdated.ToString())); } else if (!update && append) { OnAddStatusMessage(string.Format(ImportExportSharedStrings.IMPORT_TABLE_APPENDED, destinationTable, recordsInserted.ToString())); } }
/// <summary> /// Processes a form's base table /// </summary> /// <param name="sourceView">The source form</param> /// <param name="destinationView">The destination form</param> /// <param name="destinationGUIDList">The list of GUIDs that exist in the destination</param> private void ProcessBaseTable(View sourceView, View destinationView, List<string> destinationGUIDList) { sourceView.LoadFirstRecord(); this.BeginInvoke(new SetStatusDelegate(SetStatusMessage), "Processing records on base table..."); int recordsInserted = 0; int recordsUpdated = 0; string sourceTable = sourceView.TableName; string destinationTable = destinationView.TableName; try { IDataReader sourceReader = sourceProjectDataDriver.GetTableDataReader(sourceView.TableName); while (sourceReader.Read()) { object recordStatus = sourceReader["RECSTATUS"]; QueryParameter paramRecordStatus = new QueryParameter("@RECSTATUS", DbType.Int32, recordStatus); if (importWorker.CancellationPending) { this.BeginInvoke(new SetStatusDelegate(AddStatusMessage), "Import cancelled."); return; } WordBuilder fieldNames = new WordBuilder(StringLiterals.COMMA); WordBuilder fieldValues = new WordBuilder(StringLiterals.COMMA); List<QueryParameter> fieldValueParams = new List<QueryParameter>(); fieldNames.Append("GlobalRecordId"); fieldValues.Append("@GlobalRecordId"); string GUID = sourceReader["GlobalRecordId"].ToString(); string FKEY = sourceReader["FKEY"].ToString(); QueryParameter paramFkey = new QueryParameter("@FKEY", DbType.String, FKEY); // don't add this yet QueryParameter paramGUID = new QueryParameter("@GlobalRecordId", DbType.String, GUID); fieldValueParams.Add(paramGUID); if (destinationGUIDList.Contains(GUID)) { if (update) { // UPDATE matching records string updateHeader = string.Empty; string whereClause = string.Empty; fieldValueParams = new List<QueryParameter>(); StringBuilder sb = new StringBuilder(); // Build the Update statement which will be reused sb.Append(SqlKeyWords.UPDATE); sb.Append(StringLiterals.SPACE); sb.Append(destinationProjectDataDriver.InsertInEscape(destinationTable)); sb.Append(StringLiterals.SPACE); sb.Append(SqlKeyWords.SET); sb.Append(StringLiterals.SPACE); updateHeader = sb.ToString(); sb.Remove(0, sb.ToString().Length); // Build the WHERE caluse which will be reused sb.Append(SqlKeyWords.WHERE); sb.Append(StringLiterals.SPACE); sb.Append(destinationProjectDataDriver.InsertInEscape(ColumnNames.GLOBAL_RECORD_ID)); sb.Append(StringLiterals.EQUAL); sb.Append("'"); sb.Append(GUID); sb.Append("'"); whereClause = sb.ToString(); sb.Remove(0, sb.ToString().Length); //if (sourceView.ForeignKeyFieldExists) if (!string.IsNullOrEmpty(FKEY)) { sb.Append(StringLiterals.LEFT_SQUARE_BRACKET); sb.Append("FKEY"); sb.Append(StringLiterals.RIGHT_SQUARE_BRACKET); sb.Append(StringLiterals.EQUAL); sb.Append(StringLiterals.COMMERCIAL_AT); sb.Append("FKEY"); fieldValueParams.Add(paramFkey); Query updateQuery = destinationProjectDataDriver.CreateQuery(updateHeader + StringLiterals.SPACE + sb.ToString() + StringLiterals.SPACE + whereClause); updateQuery.Parameters = fieldValueParams; destinationProjectDataDriver.ExecuteNonQuery(updateQuery); sb.Remove(0, sb.ToString().Length); fieldValueParams.Clear(); recordsUpdated++; } } } else { if (append) { if (!string.IsNullOrEmpty(FKEY)) { fieldNames.Append("FKEY"); fieldValues.Append("@FKEY"); fieldValueParams.Add(paramFkey); } fieldNames.Append("RECSTATUS"); fieldValues.Append("@RECSTATUS"); fieldValueParams.Add(paramRecordStatus); // Concatenate the query clauses into one SQL statement. StringBuilder sb = new StringBuilder(); sb.Append(" insert into "); sb.Append(destinationProjectDataDriver.InsertInEscape(destinationTable)); sb.Append(StringLiterals.SPACE); sb.Append(Util.InsertInParantheses(fieldNames.ToString())); sb.Append(" values ("); sb.Append(fieldValues.ToString()); sb.Append(") "); Query insertQuery = destinationProjectDataDriver.CreateQuery(sb.ToString()); insertQuery.Parameters = fieldValueParams; destinationProjectDataDriver.ExecuteNonQuery(insertQuery); recordsInserted++; } } this.BeginInvoke(new SetProgressBarDelegate(IncrementProgressBarValue), 1); } sourceReader.Close(); sourceReader.Dispose(); } catch (Exception ex) { this.BeginInvoke(new SetStatusDelegate(AddErrorStatusMessage), ex.Message); } finally { } this.BeginInvoke(new SetStatusDelegate(AddStatusMessage), "On page '" + destinationTable + "', " + recordsInserted.ToString() + " record(s) inserted and " + recordsUpdated.ToString() + " record(s) updated."); }