/// <summary> /// Gets the appropriate query parameter for a given grid column. /// </summary> /// <param name="fieldData">The field data to use to generate the parameter.</param> /// <param name="gridField">The grid field that contains this column</param> /// <returns>QueryParameter</returns> protected QueryParameter GetQueryParameterForField(PackageFieldData fieldData, GridField gridField) { if (!string.IsNullOrEmpty(fieldData.FieldValue.ToString())) { GridColumnBase gridColumnBase = null; string fieldName = fieldData.FieldName; foreach (GridColumnBase gc in gridField.Columns) { if (gc.Name.ToLower().Equals(fieldName.ToLower())) { gridColumnBase = gc; } } if (gridColumnBase == null) { return null; } // the column exists in the source but not the destination, so skip it switch (gridColumnBase.GridColumnType) { case MetaFieldType.Date: case MetaFieldType.DateTime: case MetaFieldType.Time: DateTime dt = new DateTime(Convert.ToInt64(fieldData.FieldValue)); return new QueryParameter("@" + fieldName, DbType.DateTime, dt); case MetaFieldType.Checkbox: return new QueryParameter("@" + fieldName, DbType.Boolean, Convert.ToBoolean(fieldData.FieldValue)); 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: return new QueryParameter("@" + fieldName, DbType.String, fieldData.FieldValue); case MetaFieldType.Number: case MetaFieldType.YesNo: return new QueryParameter("@" + fieldName, DbType.Single, fieldData.FieldValue); default: throw new ApplicationException("Not a supported field type"); } } return null; }
/// <summary> /// Begins the process of importing grid data /// </summary> /// <param name="gridField">The grid that will receive the data</param> /// <param name="gridRecords">The data to be imported</param> /// <param name="destinationGuids">A dictionary of GUIDs in the destination project; the key represents the GUID itself and the value (either true or false) represents whether or not to process that record</param> protected virtual void ImportGridRecordsToGridTable(GridField gridField, List<PackageFieldData> gridRecords, Dictionary<string, bool> destinationGuids) { if (Conn.State != ConnectionState.Open) { Conn.Open(); } IDbDriver db = DestinationProject.CollectedData.GetDatabase(); if (ResetProgress != null) { ResetProgress(); } double total = gridRecords.Count; string lastGuid = string.Empty; List<string> fieldsInQuery = new List<string>(); WordBuilder setFieldText = new WordBuilder(StringLiterals.COMMA); List<QueryParameter> fieldValueParams = new List<QueryParameter>(); PackageFieldData lastRecord = new PackageFieldData(); lastRecord.FieldName = "__--LastRecord--__"; lastRecord.RecordGUID = string.Empty; gridRecords.Add(lastRecord); for (int i = 0; i < gridRecords.Count; i++) { PackageFieldData fieldData = gridRecords[i]; if (i % 200 == 0) { if (StatusChanged != null) { StatusChanged(string.Format(UnpackagerStrings.IMPORT_GRID_PROGRESS, (i / total).ToString("P0"))); } if (UpdateProgress != null) { UpdateProgress((i / total) * 100); } } string guid = fieldData.RecordGUID; bool isLast = fieldData.Equals(lastRecord); if (isLast || fieldsInQuery.Contains(fieldData.FieldName)) { // run the update with the fields we currently have... string updateHeader = string.Empty; string whereClause = string.Empty; StringBuilder sb = new StringBuilder(); // Build the Update statement which will be reused sb.Append(SqlKeyWords.UPDATE); sb.Append(StringLiterals.SPACE); sb.Append(db.InsertInEscape(gridField.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(db.InsertInEscape(ColumnNames.UNIQUE_ROW_ID)); sb.Append(StringLiterals.EQUAL); sb.Append("'"); sb.Append(lastGuid); sb.Append("'"); whereClause = sb.ToString(); sb.Remove(0, sb.ToString().Length); sb.Append(StringLiterals.LEFT_SQUARE_BRACKET); sb.Append(fieldData.FieldName); sb.Append(StringLiterals.RIGHT_SQUARE_BRACKET); sb.Append(StringLiterals.EQUAL); sb.Append(StringLiterals.COMMERCIAL_AT); sb.Append(fieldData.FieldName); if (fieldsInQuery.Count > 0 && fieldValueParams.Count > 0) { Query updateQuery = db.CreateQuery(updateHeader + StringLiterals.SPACE + setFieldText.ToString() + StringLiterals.SPACE + whereClause); updateQuery.Parameters = fieldValueParams; if (DestinationProject.CollectedDataDriver.ToLower().Contains("epi.data.office")) { IDbCommand command = GetCommand(updateQuery.SqlStatement, Conn, updateQuery.Parameters); object obj = command.ExecuteNonQuery(); } else { db.ExecuteNonQuery(updateQuery); } } setFieldText = new WordBuilder(StringLiterals.COMMA); fieldValueParams = new List<QueryParameter>(); fieldsInQuery = new List<string>(); if (isLast) { break; } } if (destinationGuids.ContainsKey(guid) && destinationGuids[guid] == true) { fieldsInQuery.Add(fieldData.FieldName); //setFieldText.Append(fieldData.FieldName + " = " + "@" + fieldData.FieldName); QueryParameter parameter = GetQueryParameterForField(fieldData, gridField); if (parameter != null) { setFieldText.Append(fieldData.FieldName + " = " + "@" + fieldData.FieldName); fieldValueParams.Add(parameter); } lastGuid = guid; } } }
/// <summary> /// Gets the appropriate query parameter for a given field. /// </summary> /// <param name="fieldData">The field data to use to generate the parameter.</param> /// <param name="destinationForm">The form on which the field resides.</param> /// <param name="sourcePage">The page on the form in which the field resides.</param> /// <returns>QueryParameter</returns> protected QueryParameter GetQueryParameterForField(PackageFieldData fieldData, View destinationForm, Page sourcePage) { Field dataField = destinationForm.Fields[fieldData.FieldName]; if (!( dataField is GroupField || dataField is RelatedViewField || dataField is UniqueKeyField || dataField is RecStatusField || dataField is GlobalRecordIdField || fieldData.FieldValue == null || string.IsNullOrEmpty(fieldData.FieldValue.ToString() ))) { String fieldName = ((Epi.INamedObject)dataField).Name; switch (dataField.FieldType) { case MetaFieldType.Date: case MetaFieldType.DateTime: case MetaFieldType.Time: DateTime dt = new DateTime(Convert.ToInt64(fieldData.FieldValue)); return new QueryParameter("@" + fieldName, DbType.DateTime, dt); case MetaFieldType.Checkbox: return new QueryParameter("@" + fieldName, DbType.Boolean, Convert.ToBoolean(fieldData.FieldValue)); 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: case MetaFieldType.GUID: return new QueryParameter("@" + fieldName, DbType.String, fieldData.FieldValue); case MetaFieldType.Number: case MetaFieldType.YesNo: case MetaFieldType.RecStatus: return new QueryParameter("@" + fieldName, DbType.Single, fieldData.FieldValue); case MetaFieldType.Image: //throw new ApplicationException("Not a supported field type"); return new QueryParameter("@" + fieldName, DbType.Binary, Convert.FromBase64String(fieldData.FieldValue.ToString())); case MetaFieldType.Option: return new QueryParameter("@" + fieldName, DbType.Single, fieldData.FieldValue); //this.BeginInvoke(new SetStatusDelegate(AddWarningMessage), "The data for " + fieldName + " was not imported. This field type is not supported."); default: throw new ApplicationException("Not a supported field type"); } } return null; }
/// <summary> /// Unpackages the specified XmlDocument and imports the data into the specified Epi Info 7 form (and any descendant forms). /// </summary> public void Unpackage() { ImportInfo = new ImportInfo(); ImportInfo.UserID = System.Security.Principal.WindowsIdentity.GetCurrent().Name.ToString(); ImportInfo.ImportInitiated = DateTime.Now; System.Diagnostics.Stopwatch sw = new System.Diagnostics.Stopwatch(); sw.Start(); using (Conn = DestinationProject.CollectedData.GetDatabase().GetConnection()) { Conn.Open(); CheckForProblems(); foreach (XmlNode node in XmlDataPackage.ChildNodes) { if (node.Name.ToLower().Equals("datapackage")) { PackageName = node.Attributes["Name"].Value; if (StatusChanged != null) { StatusChanged(string.Format(UnpackagerStrings.IMPORT_INITIATED, PackageName, ImportInfo.UserID)); } if (MessageGenerated != null) { MessageGenerated(string.Format(UnpackagerStrings.IMPORT_INITIATED, PackageName, ImportInfo.UserID)); } foreach (XmlNode dpNode in node.ChildNodes) { if (dpNode.Name.ToLower().Equals("form")) { List<PackageFieldData> records = new List<PackageFieldData>(); XmlNode formNode = dpNode; View form = DestinationProject.Views[formNode.Attributes["Name"].Value.ToString()]; //DestinationForm; if (formNode.ChildNodes.Count >= 2) // if less than 2 then the package is incomplete and can't be imported; throw exception if so? { XmlNode metaDataNode = formNode.SelectSingleNode("FieldMetadata"); XmlNode dataNode = formNode.SelectSingleNode("Data"); XmlNode keyNode = formNode.SelectSingleNode("KeyFields"); if (keyNode != null) { foreach (XmlNode key in keyNode.ChildNodes) { string fieldName = key.Attributes["Name"].Value; Field keyField = form.Fields[fieldName]; this.KeyFields.Add(keyField); } } Dictionary<string, Page> pageDictionary = new Dictionary<string, Page>(); foreach (XmlElement fieldMetadataElement in metaDataNode.ChildNodes) { string fieldName = fieldMetadataElement.Attributes["Name"].InnerText; foreach (Page page in form.Pages) { if (page.Fields.Contains(fieldName)) { pageDictionary.Add(fieldName, page); break; } } } foreach (XmlElement recordElement in dataNode.ChildNodes) { if (recordElement.Name.Equals("Record")) { string guid = recordElement.Attributes[0].Value.ToString(); Dictionary<Field, object> customKey = new Dictionary<Field, object>(); #region Custom Match Keys int keysFound = 0; if (IsUsingCustomMatchkeys == true) { foreach (XmlNode fieldNode in recordElement.ChildNodes) { string fieldName = string.Empty; if (fieldNode.Name.Equals("Field")) { fieldName = fieldNode.Attributes["Name"].Value; if (form.Fields.Contains(fieldName)) { Field field = form.Fields[fieldName]; if (KeyFields.Contains(field)) { keysFound++; customKey.Add(field, FormatFieldData(fieldName, fieldNode.InnerText)); } } } if (keysFound == KeyFields.Count) break; // stop looping if we have all the keys } } #endregion // Custom Match Keys foreach (XmlNode fieldNode in recordElement.ChildNodes) { string fieldName = string.Empty; if (fieldNode.Name.Equals("Field")) { fieldName = fieldNode.Attributes[0].Value; if (pageDictionary.ContainsKey(fieldName)) // needed in case a field exists in the package but not on the form { Page destinationPage = pageDictionary[fieldName]; object fieldValue = FormatFieldData(fieldName, fieldNode.InnerText); PackageFieldData fieldData = new PackageFieldData(); fieldData.FieldName = fieldName; fieldData.FieldValue = fieldValue; fieldData.RecordGUID = guid; fieldData.Page = destinationPage; fieldData.KeyValues = customKey; records.Add(fieldData); } } } } else if (recordElement.Name.Equals("Grid")) { #region Grid Records List<PackageFieldData> gridRecords = new List<PackageFieldData>(); XmlNode gridNode = recordElement; if (form.Fields.Contains(gridNode.Attributes["Name"].Value)) { Field field = form.Fields.GridFields[gridNode.Attributes["Name"].Value]; GridField gridField = field as GridField; if (gridNode.ChildNodes.Count == 2) { XmlNode gridMetaDataNode = gridNode.ChildNodes[0]; XmlNode gridDataNode = gridNode.ChildNodes[1]; foreach (XmlElement gridRecordElement in gridDataNode.ChildNodes) { string gridGuid = gridRecordElement.Attributes["UniqueRowId"].Value.ToString(); foreach (XmlNode gridColumnNode in gridRecordElement.ChildNodes) { string columnName = string.Empty; if (gridColumnNode.Name.Equals("GridColumn")) { columnName = gridColumnNode.Attributes[0].Value; object gridColumnValue = gridColumnNode.InnerText; PackageFieldData fieldData = new PackageFieldData(); fieldData.FieldName = columnName; fieldData.FieldValue = gridColumnValue; fieldData.RecordGUID = gridGuid; gridRecords.Add(fieldData); } } } } if (gridRecords.Count > 0) { ImportGridRecords(gridField, gridNode, gridRecords); ImportInfo.GridsProcessed++; } } #endregion // Grid Records } } } ImportInfo.FormsProcessed++; if (StatusChanged != null) { StatusChanged(string.Format(UnpackagerStrings.IMPORT_START_FORM, PackageName, form.Name)); } if (MessageGenerated != null) { MessageGenerated(string.Format(UnpackagerStrings.IMPORT_START_FORM, PackageName, form.Name)); } ImportRecords(form, formNode, records); if (StatusChanged != null) { StatusChanged(string.Format(UnpackagerStrings.IMPORT_END_FORM, PackageName, form.Name, ImportInfo.RecordsUpdated[form].ToString(), ImportInfo.RecordsAppended[form].ToString())); } if (MessageGenerated != null) { MessageGenerated(string.Format(UnpackagerStrings.IMPORT_END_FORM, PackageName, form.Name, ImportInfo.RecordsUpdated[form].ToString(), ImportInfo.RecordsAppended[form].ToString())); } } } } } } if (StatusChanged != null) { StatusChanged(string.Format(UnpackagerStrings.IMPORT_END, PackageName)); } if (MessageGenerated != null) { MessageGenerated(string.Format(UnpackagerStrings.IMPORT_END, PackageName)); } sw.Stop(); ImportInfo.TimeElapsed = sw.Elapsed; ImportInfo.ImportCompleted = DateTime.Now; ImportInfo.Succeeded = true; if (ImportFinished != null) { ImportFinished(this, new EventArgs()); } }
/// <summary> /// Creates a new blank row for a given form's base table and all of its page tables. /// </summary> /// <param name="form">The form where the row should be added.</param> /// <param name="guid">The Guid value to use for the row.</param> /// <param name="keyValues">The key values to use for custom matching</param> /// <param name="fkey">The foreign key for the row.</param> /// <param name="firstSaveId">The user ID of the first person that saved this record.</param> /// <param name="firstSaveTime">The time when the record was first saved.</param> /// <param name="lastSaveId">The user ID of the last person that saved this record.</param> /// <param name="lastSaveTime">The time when the record was last saved.</param> protected virtual void CreateNewBlankRow(View form, string guid, Dictionary<Field, object> keyValues = null, string fkey = "", string firstSaveId = "", string lastSaveId = "", DateTime? firstSaveTime = null, DateTime? lastSaveTime = null) { #region Input Validation if (string.IsNullOrEmpty(guid)) { throw new ArgumentNullException("guid"); } if (form == null) { throw new ArgumentNullException("form"); } #endregion // Input Validation if (Conn.State != ConnectionState.Open) { Conn.Open(); } IDbDriver db = DestinationProject.CollectedData.GetDatabase(); StringBuilder sb = new StringBuilder(); sb.Append(" insert into "); sb.Append(db.InsertInEscape(form.TableName)); sb.Append(StringLiterals.SPACE); sb.Append(StringLiterals.SPACE); WordBuilder fields = new WordBuilder(","); fields.Append("[GlobalRecordId]"); if (!string.IsNullOrEmpty(fkey)) { fields.Append("[FKEY]"); } if (!string.IsNullOrEmpty(firstSaveId)) { fields.Append("[FirstSaveLogonName]"); } if (!string.IsNullOrEmpty(lastSaveId)) { fields.Append("[LastSaveLogonName]"); } if (firstSaveTime.HasValue) { fields.Append("[FirstSaveTime]"); } if (lastSaveTime.HasValue) { fields.Append("[LastSaveTime]"); } sb.Append("(" + fields.ToString() + ")"); sb.Append(" values ("); List<QueryParameter> parameters = new List<QueryParameter>(); WordBuilder values = new WordBuilder(","); values.Append("'" + guid + "'"); if (!string.IsNullOrEmpty(fkey)) { values.Append("@FKEY"); parameters.Add(new QueryParameter("@FKEY", DbType.String, fkey)); } if (!string.IsNullOrEmpty(firstSaveId)) { values.Append("@FirstSaveLogonName"); parameters.Add(new QueryParameter("@FirstSaveLogonName", DbType.String, firstSaveId)); } if (!string.IsNullOrEmpty(lastSaveId)) { values.Append("@LastSaveLogonName"); parameters.Add(new QueryParameter("@LastSaveLogonName", DbType.String, lastSaveId)); } if (firstSaveTime.HasValue) { values.Append("@FirstSaveTime"); parameters.Add(new QueryParameter("@FirstSaveTime", DbType.DateTime, firstSaveTime)); } if (lastSaveTime.HasValue) { values.Append("@LastSaveTime"); parameters.Add(new QueryParameter("@LastSaveTime", DbType.DateTime, lastSaveTime)); } sb.Append(values.ToString()); sb.Append(") "); Epi.Data.Query insertQuery = db.CreateQuery(sb.ToString()); insertQuery.Parameters = parameters; if (DestinationProject.CollectedDataDriver.ToLower().Contains("epi.data.office")) { IDbCommand command = GetCommand(insertQuery.SqlStatement, Conn, insertQuery.Parameters); object obj = command.ExecuteNonQuery(); } else { db.ExecuteNonQuery(insertQuery); } //Parallel.ForEach(form.Pages, page => foreach(Page page in form.Pages) { WordBuilder wbFields = new WordBuilder(","); WordBuilder wbParams = new WordBuilder(","); List<QueryParameter> queryParams = new List<QueryParameter>(); wbFields.Add("[GlobalRecordId]"); wbParams.Add("@GlobalRecordId"); queryParams.Add(new QueryParameter("@GlobalRecordId", DbType.String, guid)); foreach (KeyValuePair<Field, object> kvp in keyValues) { RenderableField field = kvp.Key as RenderableField; PackageFieldData fieldData = new PackageFieldData(); fieldData.FieldValue = kvp.Value; fieldData.FieldName = field.Name; fieldData.Page = field.Page; if (field.Page.TableName.Equals(page.TableName)) { wbFields.Add(db.InsertInEscape(fieldData.FieldName)); wbParams.Add("@" + fieldData.FieldName); QueryParameter parameter = GetQueryParameterForField(fieldData, form, field.Page); queryParams.Add(parameter); } } sb = new StringBuilder(); sb.Append(" insert into "); sb.Append(db.InsertInEscape(page.TableName)); sb.Append(StringLiterals.SPACE); sb.Append(StringLiterals.SPACE); sb.Append("("); sb.Append(wbFields.ToString()); sb.Append(")"); sb.Append(" values ("); sb.Append(wbParams.ToString()); sb.Append(") "); insertQuery = db.CreateQuery(sb.ToString()); foreach (QueryParameter queryParam in queryParams) { insertQuery.Parameters.Add(queryParam); } if (DestinationProject.CollectedDataDriver.ToLower().Contains("epi.data.office")) { IDbCommand command = GetCommand(insertQuery.SqlStatement, Conn, insertQuery.Parameters); object obj = command.ExecuteNonQuery(); } else { db.ExecuteNonQuery(insertQuery); } } //); }
/// <summary> /// Creates a new blank row for a given form's base table and all of its page tables. /// </summary> /// <param name="form">The form where the row should be added.</param> /// <param name="guid">The Guid value to use for the row.</param> /// <param name="keyValues">The key values to use for custom matching</param> /// <param name="fkey">The foreign key for the row.</param> /// <param name="firstSaveId">The user ID of the first person that saved this record.</param> /// <param name="firstSaveTime">The time when the record was first saved.</param> /// <param name="lastSaveId">The user ID of the last person that saved this record.</param> /// <param name="lastSaveTime">The time when the record was last saved.</param> protected virtual void CreateNewBlankRow(View form, string guid, Dictionary <Field, object> keyValues = null, string fkey = "", string firstSaveId = "", string lastSaveId = "", DateTime?firstSaveTime = null, DateTime?lastSaveTime = null) { #region Input Validation if (string.IsNullOrEmpty(guid)) { throw new ArgumentNullException("guid"); } if (form == null) { throw new ArgumentNullException("form"); } #endregion // Input Validation if (Conn.State != ConnectionState.Open) { Conn.Open(); } IDbDriver db = DestinationProject.CollectedData.GetDatabase(); StringBuilder sb = new StringBuilder(); sb.Append(" insert into "); sb.Append(db.InsertInEscape(form.TableName)); sb.Append(StringLiterals.SPACE); sb.Append(StringLiterals.SPACE); WordBuilder fields = new WordBuilder(","); fields.Append("[GlobalRecordId]"); if (!string.IsNullOrEmpty(fkey)) { fields.Append("[FKEY]"); } if (!string.IsNullOrEmpty(firstSaveId)) { fields.Append("[FirstSaveLogonName]"); } if (!string.IsNullOrEmpty(lastSaveId)) { fields.Append("[LastSaveLogonName]"); } if (firstSaveTime.HasValue) { fields.Append("[FirstSaveTime]"); } if (lastSaveTime.HasValue) { fields.Append("[LastSaveTime]"); } sb.Append("(" + fields.ToString() + ")"); sb.Append(" values ("); List <QueryParameter> parameters = new List <QueryParameter>(); WordBuilder values = new WordBuilder(","); values.Append("'" + guid + "'"); if (!string.IsNullOrEmpty(fkey)) { values.Append("@FKEY"); parameters.Add(new QueryParameter("@FKEY", DbType.String, fkey)); } if (!string.IsNullOrEmpty(firstSaveId)) { values.Append("@FirstSaveLogonName"); parameters.Add(new QueryParameter("@FirstSaveLogonName", DbType.String, firstSaveId)); } if (!string.IsNullOrEmpty(lastSaveId)) { values.Append("@LastSaveLogonName"); parameters.Add(new QueryParameter("@LastSaveLogonName", DbType.String, lastSaveId)); } if (firstSaveTime.HasValue) { values.Append("@FirstSaveTime"); parameters.Add(new QueryParameter("@FirstSaveTime", DbType.DateTime, firstSaveTime)); } if (lastSaveTime.HasValue) { values.Append("@LastSaveTime"); parameters.Add(new QueryParameter("@LastSaveTime", DbType.DateTime, lastSaveTime)); } sb.Append(values.ToString()); sb.Append(") "); Epi.Data.Query insertQuery = db.CreateQuery(sb.ToString()); insertQuery.Parameters = parameters; if (DestinationProject.CollectedDataDriver.ToLowerInvariant().Contains("epi.data.office")) { IDbCommand command = GetCommand(insertQuery.SqlStatement, Conn, insertQuery.Parameters); object obj = command.ExecuteNonQuery(); } else { db.ExecuteNonQuery(insertQuery); } //Parallel.ForEach(form.Pages, page => foreach (Page page in form.Pages) { WordBuilder wbFields = new WordBuilder(","); WordBuilder wbParams = new WordBuilder(","); List <QueryParameter> queryParams = new List <QueryParameter>(); wbFields.Add("[GlobalRecordId]"); wbParams.Add("@GlobalRecordId"); queryParams.Add(new QueryParameter("@GlobalRecordId", DbType.String, guid)); foreach (KeyValuePair <Field, object> kvp in keyValues) { RenderableField field = kvp.Key as RenderableField; PackageFieldData fieldData = new PackageFieldData(); fieldData.FieldValue = kvp.Value; fieldData.FieldName = field.Name; fieldData.Page = field.Page; if (field.Page.TableName.Equals(page.TableName)) { wbFields.Add(db.InsertInEscape(fieldData.FieldName)); wbParams.Add("@" + fieldData.FieldName); QueryParameter parameter = GetQueryParameterForField(fieldData, form, field.Page); queryParams.Add(parameter); } } sb = new StringBuilder(); sb.Append(" insert into "); sb.Append(db.InsertInEscape(page.TableName)); sb.Append(StringLiterals.SPACE); sb.Append(StringLiterals.SPACE); sb.Append("("); sb.Append(wbFields.ToString()); sb.Append(")"); sb.Append(" values ("); sb.Append(wbParams.ToString()); sb.Append(") "); insertQuery = db.CreateQuery(sb.ToString()); foreach (QueryParameter queryParam in queryParams) { insertQuery.Parameters.Add(queryParam); } if (DestinationProject.CollectedDataDriver.ToLowerInvariant().Contains("epi.data.office")) { IDbCommand command = GetCommand(insertQuery.SqlStatement, Conn, insertQuery.Parameters); object obj = command.ExecuteNonQuery(); } else { db.ExecuteNonQuery(insertQuery); } } //); }
/// <summary> /// Begins the process of importing records from the data package into the destination form /// </summary> /// <param name="form">The form that will receive the data</param> /// <param name="formNode">The XmlNode representing the form</param> /// <param name="records">The data to be imported</param> protected override void ImportRecords(View form, XmlNode formNode, List <PackageFieldData> records) { if (!IsUsingCustomMatchkeys) // Calling class should instantiate normal data packager if custom keys aren't used { throw new ApplicationException("This class should not be used without custom match keys."); } ImportInfo.RecordsAppended.Add(form, 0); ImportInfo.RecordsUpdated.Add(form, 0); IDbDriver destinationDb = DestinationProject.CollectedData.GetDatabase(); DataTable destinationKeyTable = new DataTable(); destinationKeyTable.Columns.Add(new DataColumn("GlobalRecordId", typeof(string))); destinationKeyTable.Columns.Add(new DataColumn("Update", typeof(bool))); destinationKeyTable.Columns.Add(new DataColumn("KeyDictionary", typeof(Dictionary <Field, object>))); DataColumn [] primaryKey = new DataColumn[1]; primaryKey[0] = destinationKeyTable.Columns["GlobalRecordId"]; destinationKeyTable.PrimaryKey = primaryKey; WordBuilder wb = new WordBuilder(","); wb.Add("t.GlobalRecordId"); foreach (Field field in KeyFields) { wb.Add(field.Name); } Query selectQuery = destinationDb.CreateQuery("SELECT " + wb.ToString() + " " + form.FromViewSQL); using (IDataReader keyTableReader = destinationDb.ExecuteReader(selectQuery)) { while (keyTableReader.Read()) { string guid = keyTableReader["GlobalRecordId"].ToString(); Dictionary <Field, object> keys = new Dictionary <Field, object>(); foreach (Field field in KeyFields) { keys.Add(field, keyTableReader[field.Name]); } destinationKeyTable.Rows.Add(guid, true, keys); } } var query = from record in records group record by record.RecordGUID; IEnumerable <IEnumerable <PackageFieldData> > fieldDataLists = query as IEnumerable <IEnumerable <PackageFieldData> >; foreach (IEnumerable <PackageFieldData> fieldDataList in fieldDataLists) { PackageFieldData fieldData = fieldDataList.First(); bool found = false; foreach (DataRow row in destinationKeyTable.Rows) { Dictionary <Field, object> keyDictionary = row["KeyDictionary"] as Dictionary <Field, object>; if (AreKeyFieldDictionariesEqual(keyDictionary, fieldData.KeyValues)) { found = true; if (!Update) { row["Update"] = false; } else { ImportInfo.TotalRecordsUpdated++; ImportInfo.RecordsUpdated[form]++; } } } if (!found && Append) // no match, this is a new record that must be inserted { CreateNewBlankRow(form, fieldData.RecordGUID, fieldData.KeyValues); ImportInfo.TotalRecordsAppended++; ImportInfo.RecordsAppended[form]++; } } System.Threading.Thread.Sleep(2000); // give time for DB to update destinationKeyTable.Clear(); selectQuery = destinationDb.CreateQuery("SELECT " + wb.ToString() + " " + form.FromViewSQL); using (IDataReader keyTableReader = destinationDb.ExecuteReader(selectQuery)) { while (keyTableReader.Read()) { string guid = keyTableReader["GlobalRecordId"].ToString(); Dictionary <Field, object> keys = new Dictionary <Field, object>(); foreach (Field field in KeyFields) { keys.Add(field, keyTableReader[field.Name]); } destinationKeyTable.Rows.Add(guid, true, keys); } } //Parallel.ForEach(records, rec => // TODO: Make this faster (note that Parallel foreach seems to make it worse) foreach (PackageFieldData rec in records) { bool found = false; string targetGuid = String.Empty; bool shouldUpdate = true; foreach (DataRow row in destinationKeyTable.Rows) { Dictionary <Field, object> keyDictionary = row["KeyDictionary"] as Dictionary <Field, object>; if (AreKeyFieldDictionariesEqual(keyDictionary, rec.KeyValues)) { found = true; targetGuid = row["GlobalRecordId"].ToString(); shouldUpdate = (bool)row["Update"]; break; } } if (shouldUpdate && found && !String.IsNullOrEmpty(targetGuid) && rec.FieldValue != null && !String.IsNullOrEmpty(rec.FieldValue.ToString())) { Query updateQuery = destinationDb.CreateQuery("UPDATE " + rec.Page.TableName + " SET " + "[" + rec.FieldName + "] = @" + rec.FieldName + " WHERE [GlobalRecordId] = @GlobalRecordId"); QueryParameter fieldParam = GetQueryParameterForField(rec, form, rec.Page); if (fieldParam != null) { updateQuery.Parameters.Add(fieldParam); updateQuery.Parameters.Add(new QueryParameter("@GlobalRecordId", DbType.String, targetGuid)); int rowsAffected = destinationDb.ExecuteNonQuery(updateQuery); if (rowsAffected == 0) { throw new ApplicationException("No records affected."); } else if (rowsAffected > 1) { throw new ApplicationException("Too many records affected."); } } } } //); }