/// <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.");
}
}
}
}
//);
}
