/// <summary>
/// Creates a relationship between a field and a child view
/// </summary>
/// <param name="fieldId">Id of the field</param>
/// <param name="relatedViewId">Id of the view the field is related to</param>
/// <param name="relateCondition">Conditions to show the related view</param>
/// <param name="shouldReturnToParent">Whether or not the related view returns to parent</param>
public void RelateFieldToView(Guid uniqueId, int relatedViewId, string relateCondition, bool shouldReturnToParent)
{
try
{
Query fieldQuery = db.CreateQuery("update metaFields set [RelatedViewId] = @RelatedViewId, [RelateCondition] = @RelateCondition, [ShouldReturnToparent] = @ShouldReturnToParent where UniqueId = @UniqueId");
fieldQuery.Parameters.Add(new QueryParameter("@RelatedViewId", DbType.Int32, relatedViewId));
fieldQuery.Parameters.Add(new QueryParameter("@RelateCondition", DbType.String, relateCondition));
fieldQuery.Parameters.Add(new QueryParameter("@ShouldReturnToParent", DbType.Boolean, shouldReturnToParent));
fieldQuery.Parameters.Add(new QueryParameter("@UniqueId", DbType.Guid, uniqueId));
Query viewQuery = db.CreateQuery("update metaViews set [IsRelatedView] = @IsRelatedView where [ViewId] = @ViewId");
viewQuery.Parameters.Add(new QueryParameter("@IsRelatedView", DbType.Boolean, true));
viewQuery.Parameters.Add(new QueryParameter("@ViewId", DbType.Int32, relatedViewId));
db.ExecuteNonQuery(fieldQuery);
db.ExecuteNonQuery(viewQuery);
if (relatedViewId != 0)
{
Epi.View view = this.GetViewById(relatedViewId);
ForeignKeyField foreignKeyField;
if (view.IsRelatedView)
{
try
{
foreignKeyField = (ForeignKeyField) view.Fields["FKEY"];
}
catch (System.Exception ex)
{
if (ex != null) // this line is to avoid compiler warning;
{
foreignKeyField = new ForeignKeyField(view);
foreignKeyField.SaveToDb();
}
}
}
}
}
catch (Exception ex)
{
throw new GeneralException("Could not create new relate info", ex);
}
}
/// <summary>
/// Insert a ForeignKeyField record into the metaFields table.
/// </summary>
/// <param name="field">Foreign Key field of a Unique Key.</param>
/// <returns>Returns the Id of the last ForeignKeyField added.</returns>
public int CreateField(ForeignKeyField field)
{
try
{
#region InputValidation
if (field == null)
{
throw new ArgumentNullException("ForeignKeyField");
}
#endregion
Query insertQuery = db.CreateQuery("insert into metaFields([DataTableName], [ViewId], [UniqueId], [FieldTypeId], [Name]) " +
"values (@DataTableName, @ViewId, @UniqueId, @FieldTypeId, @Name)");
insertQuery.Parameters.Add(new QueryParameter("@DataTableName", DbType.String, field.TableName));
insertQuery.Parameters.Add(new QueryParameter("@ViewId", DbType.Int32, field.GetView().Id));
insertQuery.Parameters.Add(new QueryParameter("@UniqueId", DbType.Guid, field.UniqueId));
insertQuery.Parameters.Add(new QueryParameter("@FieldTypeId", DbType.Int32, (int)field.FieldType));
insertQuery.Parameters.Add(new QueryParameter("@Name", DbType.String, field.Name));
db.ExecuteNonQuery(insertQuery);
return GetMaxFieldId(field.GetView().Id);
}
catch (Exception ex)
{
throw new GeneralException("Could not create ForeignKey field in the database", ex);
}
finally
{
}
}
/// <summary>
/// Gets all the fields in a view
/// </summary>
/// <param name="view">A view object</param>
/// <returns>A collection of fields</returns>
public virtual FieldCollectionMaster GetFields(View view)
{
FieldCollectionMaster fields = new FieldCollectionMaster();
try
{
DataTable table = GetFieldsAsDataTable(view);
foreach (DataRow row in table.Rows)
{
MetaFieldType fieldTypeId = (MetaFieldType)row[ColumnNames.FIELD_TYPE_ID];
Field field = null;
switch (fieldTypeId)
{
case MetaFieldType.Text:
field = new SingleLineTextField(view);
field.LoadFromRow(row);
break;
case MetaFieldType.LabelTitle:
field = new LabelField(view);
field.LoadFromRow(row);
break;
case MetaFieldType.TextUppercase:
field = new UpperCaseTextField(view);
field.LoadFromRow(row);
break;
case MetaFieldType.Multiline:
field = new MultilineTextField(view);
field.LoadFromRow(row);
break;
case MetaFieldType.Number:
field = new NumberField(view);
field.LoadFromRow(row);
break;
case MetaFieldType.PhoneNumber:
field = new PhoneNumberField(view);
field.LoadFromRow(row);
break;
case MetaFieldType.Date:
field = new DateField(view);
field.LoadFromRow(row);
break;
case MetaFieldType.Time:
field = new TimeField(view);
field.LoadFromRow(row);
break;
case MetaFieldType.DateTime:
field = new DateTimeField(view);
field.LoadFromRow(row);
break;
case MetaFieldType.Checkbox:
field = new CheckBoxField(view);
field.LoadFromRow(row);
break;
case MetaFieldType.YesNo:
field = new YesNoField(view);
field.LoadFromRow(row);
break;
case MetaFieldType.Option:
field = new OptionField(view);
field.LoadFromRow(row);
break;
case MetaFieldType.CommandButton:
field = new CommandButtonField(view);
field.LoadFromRow(row);
break;
case MetaFieldType.Image:
field = new ImageField(view);
field.LoadFromRow(row);
break;
case MetaFieldType.Mirror:
field = new MirrorField(view);
field.LoadFromRow(row);
break;
case MetaFieldType.Grid:
field = new GridField(view);
field.LoadFromRow(row);
break;
case MetaFieldType.Group:
field = new GroupField(view);
field.LoadFromRow(row);
break;
case MetaFieldType.GUID:
field = new GUIDField(view);
field.LoadFromRow(row);
break;
case MetaFieldType.LegalValues:
field = new DDLFieldOfLegalValues(view);
field.LoadFromRow(row);
break;
case MetaFieldType.Codes:
field = new DDLFieldOfCodes(view);
((DDLFieldOfCodes)field).LoadFromRow(row); //zack
break;
case MetaFieldType.List:
field = new DDListField(view);
((DDListField)field).LoadFromRow(row);
break;
case MetaFieldType.CommentLegal:
field = new DDLFieldOfCommentLegal(view);
field.LoadFromRow(row);
break;
case MetaFieldType.Relate:
field = new RelatedViewField(view);
field.LoadFromRow(row);
break;
case MetaFieldType.RecStatus:
field = new RecStatusField(view);
field.LoadFromRow(row);
break;
case MetaFieldType.UniqueKey:
field = new UniqueKeyField(view);
field.LoadFromRow(row);
break;
case MetaFieldType.ForeignKey:
field = new ForeignKeyField(view);
field.LoadFromRow(row);
break;
case MetaFieldType.GlobalRecordId:
field = new GlobalRecordIdField(view);
field.LoadFromRow(row);
break;
default:
throw new ApplicationException("Invalid Field Type");
}
// Note: This check ideally shouldn't be necessary, but Epi 3.5.1 and previous versions actually do allow duplicate field names for group fields.
if (fields.Contains(field))
{
Logger.Log(DateTime.Now + ": " + string.Format("The {0} field with name \"{1}\" already exists in {2}. This field has not been imported.", field.GetType().ToString(), field.Name, view.Name));
}
else
{
fields.Add(field);
}
}
}
catch (Exception ex)
{
}
return (fields);
}
/// <summary>
/// Creates a view in a specified project
/// </summary>
/// <old-param name="isrelatedview">Whether or not this view is a related (child) view</old-param>
/// <old-param name="viewname">Name of the view</old-param>
public void InsertView(View view)
{
#region Input Validation
if (view == null)
{
throw new ArgumentNullException("view");
}
#endregion Input Validation
try
{
Query insertQuery = db.CreateQuery("insert into metaViews([Name], [IsRelatedView], [CheckCode], [Width], [Height], [Orientation], [LabelAlign] ) values (@Name, @IsRelatedView, @CheckCode, @Width, @Height, @Orientation, @LabelAlign)");
insertQuery.Parameters.Add(new QueryParameter("@Name", DbType.String, view.Name));
insertQuery.Parameters.Add(new QueryParameter("@IsRelatedView", DbType.Boolean, view.IsRelatedView));
insertQuery.Parameters.Add(new QueryParameter("@CheckCode", DbType.String, view.CheckCode));
insertQuery.Parameters.Add(new QueryParameter("@Width", DbType.Int32, view.PageWidth));
insertQuery.Parameters.Add(new QueryParameter("@Height", DbType.Int32, view.PageHeight));
insertQuery.Parameters.Add(new QueryParameter("@Orientation", DbType.String, view.PageOrientation));
insertQuery.Parameters.Add(new QueryParameter("@LabelAlign", DbType.String, view.PageLabelAlign));
db.ExecuteNonQuery(insertQuery);
view.Id = this.GetMaxViewId();
RecStatusField recStatusField = new RecStatusField(view);
UniqueKeyField uniqueKeyField = new UniqueKeyField(view);
GlobalRecordIdField globalRecordIdField = new GlobalRecordIdField(view);
uniqueKeyField.SaveToDb();
recStatusField.SaveToDb();
globalRecordIdField.SaveToDb();
if (view.IsRelatedView)
{
ForeignKeyField foreignKeyField = new ForeignKeyField(view);
foreignKeyField.SaveToDb();
}
}
catch (Exception ex)
{
throw new GeneralException("Could not create view in the database", ex);
}
}
/// <summary>
/// Retrieves data for foreign key field from xml metadata.
/// </summary>
/// <param name="field">A foreign key field.</param>
/// <param name="fieldNode">XML node for a foreign field.</param>
public void GetFieldData(ForeignKeyField field, XmlNode fieldNode)
{
}
/// <summary>
/// Creates a new view in XML metadata
/// </summary>
/// <param name="view">View to be inserted in metadata</param>
public void InsertView(View view)
{
XmlDocument xmlDoc = GetXmlDocument();
XmlNode viewsNode = GetViewsNode();
XmlElement viewElement = xmlDoc.CreateElement("View");
viewsNode.AppendChild(viewElement);
view.ViewElement = viewElement;
XmlAttribute viewId = xmlDoc.CreateAttribute("ViewId");
viewId.Value = GetMaxViewId().ToString();
view.Id = int.Parse(viewId.Value);
viewElement.Attributes.Append(viewId);
XmlAttribute viewNameAttribute = xmlDoc.CreateAttribute("Name");
viewNameAttribute.Value = view.Name;
viewElement.Attributes.Append(viewNameAttribute);
XmlAttribute isRelatedView = xmlDoc.CreateAttribute("IsRelatedView");
isRelatedView.Value = view.IsRelatedView.ToString();
viewElement.Attributes.Append(isRelatedView);
viewElement.AppendChild(xmlDoc.CreateElement("CheckCodeBefore"));
viewElement.AppendChild(xmlDoc.CreateElement("CheckCodeAfter"));
viewElement.AppendChild(xmlDoc.CreateElement("RecordCheckCodeBefore"));
viewElement.AppendChild(xmlDoc.CreateElement("RecordCheckCodeAfter"));
viewElement.AppendChild(xmlDoc.CreateElement("CheckCodeVariableDefinitions"));
viewElement.AppendChild(xmlDoc.CreateElement("Pages"));
viewElement.AppendChild(xmlDoc.CreateElement("Fields"));
//Insert system fields .. RecStatus and UniqueKey
RecStatusField recStatusField = new RecStatusField(view, viewElement);
UniqueKeyField uniqueKeyField = new UniqueKeyField(view, viewElement);
recStatusField.SaveToDb();
uniqueKeyField.SaveToDb();
if (view.IsRelatedView)
{
ForeignKeyField foreignKeyField = new ForeignKeyField(view, viewElement);
foreignKeyField.SaveToDb();
}
Save();
}
/// <summary>
/// Gets all the fields in a view
/// </summary>
/// <param name="view">the view object</param>
/// <returns>A collection of fields</returns>
public virtual FieldCollectionMaster GetFields(View view)
{
try
{
FieldCollectionMaster fields = new FieldCollectionMaster();
//DataTable table = GetFieldsAsDataTable(view);
//foreach (DataRow row in table.Rows)
//{
// MetaFieldType fieldTypeId = (MetaFieldType)row[ColumnNames.FIELD_TYPE_ID];
// Field field = null;
XmlNode fieldsNode = view.ViewElement.SelectSingleNode("Fields");
foreach (XmlNode fieldNode in fieldsNode.ChildNodes)
{
//MetaFieldType fieldTypeId = (MetaFieldType)(int.Parse(fieldNode.Attributes["FieldTypeId"].Value.ToString()));
MetaFieldType fieldTypeId = (MetaFieldType)Enum.Parse(typeof(MetaFieldType), fieldNode.Attributes["FieldTypeId"].Value.ToString());
Field field = null;
switch (fieldTypeId)
{
case MetaFieldType.Text:
field = new SingleLineTextField(view, fieldNode);
break;
case MetaFieldType.LabelTitle:
field = new LabelField(view, fieldNode);
break;
case MetaFieldType.TextUppercase:
field = new UpperCaseTextField(view, fieldNode);
break;
case MetaFieldType.Multiline:
field = new MultilineTextField(view, fieldNode);
break;
case MetaFieldType.Number:
field = new NumberField(view, fieldNode);
break;
case MetaFieldType.PhoneNumber:
field = new PhoneNumberField(view, fieldNode);
break;
case MetaFieldType.Date:
field = new DateField(view, fieldNode);
break;
case MetaFieldType.Time:
field = new TimeField(view, fieldNode);
break;
case MetaFieldType.DateTime:
field = new DateTimeField(view, fieldNode);
break;
case MetaFieldType.Checkbox:
field = new CheckBoxField(view, fieldNode);
break;
case MetaFieldType.YesNo:
field = new YesNoField(view, fieldNode);
break;
case MetaFieldType.Option:
field = new OptionField(view, fieldNode);
break;
case MetaFieldType.CommandButton:
field = new CommandButtonField(view, fieldNode);
break;
case MetaFieldType.Image:
field = new ImageField(view, fieldNode);
break;
case MetaFieldType.Mirror:
field = new MirrorField(view, fieldNode);
break;
case MetaFieldType.Grid:
field = new GridField(view, fieldNode);
break;
case MetaFieldType.LegalValues:
field = new DDLFieldOfLegalValues(view, fieldNode);
break;
case MetaFieldType.Codes:
field = new DDLFieldOfCodes(view, fieldNode);
break;
case MetaFieldType.List:
field = new DDListField(view, fieldNode);
break;
case MetaFieldType.CommentLegal:
field = new DDLFieldOfCommentLegal(view, fieldNode);
break;
case MetaFieldType.Relate:
field = new RelatedViewField(view, fieldNode);
break;
case MetaFieldType.RecStatus:
field = new RecStatusField(view);
break;
case MetaFieldType.UniqueKey:
field = new UniqueKeyField(view);
break;
case MetaFieldType.ForeignKey:
field = new ForeignKeyField(view);
break;
default:
throw new GeneralException("Invalid Field Type");
}
//field.LoadFromRow(row);
fields.Add(field);
}
return (fields);
}
finally
{
}
}
/// <summary>
/// Create Foreign Key field.
/// </summary>
/// <param name="field">Foreign Key field to create.</param>
/// <returns>Id of the newly created ForeignKey field.</returns>
public int CreateField(ForeignKeyField field)
{
try
{
#region InputValidation
if (field == null)
{
throw new ArgumentNullException("ForeignKeyField");
}
#endregion
XmlDocument xmlDoc = GetXmlDocument();
XmlNode fieldsNode = GetFieldsNode(field.ViewElement);
View view = field.GetView();
XmlElement fieldElement = xmlDoc.CreateElement("Field");
XmlAttribute fieldIdAttribute = xmlDoc.CreateAttribute("FieldId");
fieldIdAttribute.Value = view.GetFieldId(field.ViewElement).ToString();
fieldElement.Attributes.Append(fieldIdAttribute);
field.Id = Int32.Parse(fieldIdAttribute.Value);
XmlAttribute fieldTypeId = xmlDoc.CreateAttribute("FieldTypeId");
fieldTypeId.Value = field.FieldType.ToString();
fieldElement.Attributes.Append(fieldTypeId);
XmlAttribute fieldNameAttribute = xmlDoc.CreateAttribute("Name");
fieldNameAttribute.Value = field.Name;
fieldElement.Attributes.Append(fieldNameAttribute);
XmlAttribute fieldViewId = xmlDoc.CreateAttribute("ViewId");
fieldViewId.Value = view.Id.ToString();
fieldElement.Attributes.Append(fieldViewId);
XmlAttribute dataTableName = xmlDoc.CreateAttribute("DataTableName");
dataTableName.Value = field.TableName;
fieldElement.Attributes.Append(dataTableName);
fieldsNode.AppendChild(fieldElement);
view.Project.Save();
return field.Id;
}
finally
{
}
}
/// <summary>
/// Transfers the collected data into the new database
/// </summary>
/// <param name="view">A view object</param>
private void CopyCollectedData(View view)
{
//System.Console.WriteLine("Started Copy Data {0}", System.DateTime.Now);
Epi.Data.Services.CollectedDataProvider collectedDataProvider = destinationProject.CollectedData;
Epi.Data.IDbDriver db = collectedDataProvider.GetDatabase();
System.Collections.Generic.List<System.Threading.Thread> ThreadList = new System.Collections.Generic.List<System.Threading.Thread>();
string ConnectionString = db.ConnectionString;
try
{
RaiseEventImportStatus(view.TableName + StringLiterals.ELLIPSIS);
if (collectedDataProvider.TableExists(view.TableName))
collectedDataProvider.DeleteTable(view.TableName);
foreach (Page page in view.Pages)
{
if (collectedDataProvider.TableExists(page.TableName))
collectedDataProvider.DeleteTable(page.TableName);
}
if (!collectedDataProvider.TableExists(view.TableName))
collectedDataProvider.CreateDataTableForView(view, 1);
if (sourceProject.CollectedData.TableExists(view.TableName))
{
// If the view has related views, we need to copy the old UniqueKey values
// so we can do a proper relationship later on. These will get stored in OldUniqueKey.
string viewName = view.Name;
if (view.Name.StartsWith("view") == false)
{
viewName = "view" + viewName;
}
//List<string> relatedViews = sourceProject.Metadata.GetRelatedViewNames(viewName);
//if (relatedViews.Count > 0)
//{
//hasRelatedViews = true;
// TODO: Find some way of changing "NUMERIC" to db-agnostic value; using this
// simple method now just to get this working
//Query alterQuery = db.CreateQuery(
// "alter table " + view.TableName + " add OldUniqueKey " + "NUMERIC");
TableColumn column = new TableColumn("OldUniqueKey", GenericDbColumnType.Int32, true);
db.AddColumn(view.TableName, column);
foreach(Page page in view.Pages)
{
db.AddColumn(page.TableName, column);
}
//}
if (view.IsRelatedView)
{
//Query alterQuery = db.CreateQuery(
// "alter table " + view.TableName + " add OldFKEY " + "NUMERIC");
//db.ExecuteNonQuery(alterQuery);
column = new TableColumn("OldFKEY", GenericDbColumnType.Int32, true);
db.AddColumn(view.TableName, column);
// Create FKEY in MetaFields
ForeignKeyField foreignKeyField;
try
{
foreignKeyField = (ForeignKeyField)view.Fields["FKEY"];
}
catch (System.Exception ex)
{
if (ex != null) // this line is to avoid compiler warning;
{
foreignKeyField = new ForeignKeyField(view);
foreignKeyField.SaveToDb();
}
}
}
int recordCount = 0;
//////////////////////////////////////////////////
if (!string.IsNullOrEmpty(view.TableName))
{
IDataReader reader = sourceProject.CollectedData.GetTableDataReader(view.TableName);
StringBuilder InsertSQL = new StringBuilder();
InsertSQL.Append("Insert Into [");
InsertSQL.Append(view.TableName);
InsertSQL.Append("] (");
view.MustRefreshFieldCollection = true;
foreach (Field field in view.Fields.TableColumnFields)
{
if (field is UniqueKeyField)
{
InsertSQL.Append("[OldUniqueKey],");
break;
}
}
if (view.IsRelatedView)
{
foreach (Field field in view.Fields.TableColumnFields)
{
if (field is ForeignKeyField)
{
InsertSQL.Append("[OldFKEY],");
break;
}
}
}
InsertSQL.Append("[GlobalRecordId]");
//InsertSQL.Remove(InsertSQL.Length - 1, 1);
InsertSQL.Append(")\n");
StringBuilder fieldValues = new StringBuilder();
Epi.Data.Query query = db.CreateQuery("");
while (reader.Read())
{
recordCount++;
if (recordCount % 1000 == 0)
{
Epi.Data.DBReadExecute.ExecuteSQL(ConnectionString, InsertSQL.ToString() + query.GetInsertValue(fieldValues.ToString()), 0);
fieldValues.Length = 0;
RaiseEventImportStatus(string.Format(SharedStrings.IMPORT_PROCESSED_RECORDS_BASE_TABLE, recordCount, view.Name)); // TODO: Make sure to modify this so that it doesn't appear in the log.
RaiseEventProgressBarStep();
}
foreach (Field field in view.Fields.TableColumnFields)
{
if (field is UniqueKeyField)
{
Object fieldValue = reader[field.Name];
string fieldValueString = string.Empty;
fieldValueString = fieldValue.ToString();
fieldValues.Append(fieldValueString);
fieldValues.Append(",");
break;
}
}
foreach (Field field in view.Fields.TableColumnFields)
{
if (field.Name.ToLower().Equals("fkey"))
{
Object fieldValue = reader[field.Name];
string fieldValueString = string.Empty;
fieldValueString = fieldValue.ToString();
fieldValues.Append(fieldValueString);
fieldValues.Append(",");
break;
}
}
foreach (Field field in view.Fields.TableColumnFields)
{
if (field is GlobalRecordIdField)
{
string fieldValueString = Util.InsertInSingleQuotes(System.Guid.NewGuid().ToString());
fieldValues.Append(fieldValueString);
fieldValues.Append(",");
break;
}
}
fieldValues.Remove(fieldValues.Length - 1, 1);
fieldValues.Append(";");
}
// execute the remaining statments
if (string.IsNullOrEmpty(fieldValues.ToString()) == false)
{
Epi.Data.DBReadExecute.ExecuteSQL(ConnectionString, InsertSQL.ToString() + query.GetInsertValue(fieldValues.ToString()), 0);
}
}
//////////////////////////////////////////////////
recordCount = 0;
Epi.Epi2000.View sourceView = null;
foreach (Epi.Epi2000.View epi2000View in sourceProject.Views)
{
if (epi2000View.NameWithoutPrefix.ToLower().Equals(view.Name.ToLower()))
{
sourceView = epi2000View;
break;
}
}
foreach (Page page in view.Pages)
{
recordCount = 0;
if (sourceView == null)
{
Logger.Log(DateTime.Now + ": " + SharedStrings.IMPORT_ERROR_SOURCE_VIEW_NULL);
throw new ApplicationException(SharedStrings.IMPORT_ERROR_SOURCE_VIEW_NULL);
}
IDataReader reader = sourceProject.CollectedData.GetTableDataReader(view.TableName);
string dataTableNames = string.Empty;
string joinClause = "WHERE ";
string fromClause = string.Empty;
if (sourceView.IsWideTableView)
{
foreach (string s in sourceView.TableNames)
{
dataTableNames = dataTableNames + s + ",";
joinClause = joinClause + sourceView.TableNames[0] + ".UniqueKey = " + s + ".UniqueKey AND ";
}
dataTableNames = dataTableNames.TrimEnd(',');
joinClause = joinClause.Remove(joinClause.Length - 4, 4);
fromClause = " FROM " + dataTableNames + " " + joinClause;
string fieldNames = sourceView.TableNames[0] + ".UniqueKey AS UniqueKey,";
foreach (Field field in page.Fields)
{
if (field is IDataField && !(field is GlobalRecordIdField) )
{
fieldNames = fieldNames + "[" + field.Name + "],";
}
}
fieldNames = fieldNames.TrimEnd(',');
string selectCommand = "SELECT " + fieldNames + " " + fromClause;
Query selectQuery = db.CreateQuery(selectCommand);
reader = sourceProject.CollectedData.GetDatabase().ExecuteReader(selectQuery);
}
StringBuilder InsertSQL = new StringBuilder();
InsertSQL.Append("Insert Into [");
InsertSQL.Append(/*view.TableName*/ page.TableName);
InsertSQL.Append("] (");
// Insert Into [TableName] ( [f1], ... [fn])
view.MustRefreshFieldCollection = true;
InsertSQL.Append("[OldUniqueKey],[GlobalRecordId],");
foreach (Field field in page.Fields /*view.Fields.TableColumnFields*/)
{
// Eliminate UniqueKeyFields. They are not inserted explicitly.
if (!(field is UniqueKeyField))
{
if (field is InputFieldWithoutSeparatePrompt || field is InputFieldWithSeparatePrompt)
{
InsertSQL.Append("[");
InsertSQL.Append(field.Name);
InsertSQL.Append("],");
}
}
//else if (/*hasRelatedViews &&*/ field is UniqueKeyField)
//{
// InsertSQL.Append("[OldUniqueKey],");
//}
}
InsertSQL.Remove(InsertSQL.Length - 1, 1);
InsertSQL.Append(")\n");
StringBuilder fieldValues = new StringBuilder();
Epi.Data.Query query = db.CreateQuery("");
while (reader.Read())
{
recordCount++;
if (recordCount % 1000 == 0)
{
Epi.Data.DBReadExecute.ExecuteSQL(ConnectionString, InsertSQL.ToString() + query.GetInsertValue(fieldValues.ToString()), 0);
fieldValues.Length = 0;
RaiseEventImportStatus(string.Format("Processed {0:n0} records on page {1}", recordCount, page.Name)); // TODO: Make sure to modify this so that it doesn't appear in the log.
RaiseEventProgressBarStep();
}
fieldValues = fieldValues.Append(reader["UniqueKey"].ToString());
fieldValues.Append(",");
//fieldValues = fieldValues.Append(Util.InsertInSingleQuotes(System.Guid.NewGuid().ToString()));
fieldValues = fieldValues.Append(Util.InsertInSingleQuotes("temp" + recordCount.ToString()));
fieldValues.Append(",");
foreach (Field field in /*view.Fields.TableColumnFields*/page.Fields)
{
// Eliminate UniqueKeyFields. They are not inserted explicitly.
if (!(field is UniqueKeyField) && !(field is GlobalRecordIdField) && (field is InputFieldWithoutSeparatePrompt || field is InputFieldWithSeparatePrompt))
{
if (!Util.IsEmpty(reader[field.Name]))
{
Object fieldValue = reader[field.Name];
string fieldValueString = string.Empty;
if (field is DateField)
{
fieldValueString = db.FormatDate((DateTime)fieldValue);
}
else if (field is ImageField)
{
//fieldValueString = db.FormatTime((DateTime)fieldValue);
}
else if (field is TimeField)
{
fieldValueString = db.FormatTime((DateTime)fieldValue);
}
else if (field is DateTimeField)
{
fieldValueString = db.FormatDateTime((DateTime)fieldValue);
}
else if ((field is PhoneNumberField) || (field is TableBasedDropDownField) || (field is TextField))
{
fieldValueString = Util.InsertInSingleQuotes(fieldValue.ToString());
}
else if (field is CheckBoxField)
{
bool checkboxValue = bool.Parse(fieldValue.ToString());
if (checkboxValue)
{
fieldValueString = "1";
}
else
{
fieldValueString = "0";
}
}
else if (field is OptionField)
{
try
{
string viewNameWithPrefix = "view" + view.Name;
string tableName = view.TableName;
DataTable fieldsTable = sourceProject.Metadata.GetFieldsAsDataTable(viewNameWithPrefix);
foreach (DataRow fieldRow in fieldsTable.Rows)
{
string fieldName = fieldRow["Name"].ToString();
if (fieldName == field.Name)
{
//if(db.ColumnExists(tableName, fieldName) == false) { break; }
string[] items = fieldRow["Lists"].ToString().Split(';');
for (int i = 0; i < items.Length; i++)
{
if (items[i] == fieldValue.ToString())
{
fieldValueString = i.ToString();
}
}
}
}
}
catch
{
}
}
else
{
fieldValueString = fieldValue.ToString();
}
fieldValues.Append(fieldValueString);
fieldValues.Append(",");
}
else
{
fieldValues.Append("null,");
}
}
else if (field is GlobalRecordIdField)
{
string fieldValueString = Util.InsertInSingleQuotes(System.Guid.NewGuid().ToString());
if (view.IsRelatedView == false)
{
fieldValues.Append(fieldValueString);
}
else
{
fieldValues.Append("''");
}
fieldValues.Append(",");
}
//else if (field is UniqueKeyField/* && hasRelatedViews*/)
//{
// //if (!Util.IsEmpty(reader[field.Name]))
// //{
// Object fieldValue = reader[field.Name];
// string fieldValueString = string.Empty;
// fieldValueString = fieldValue.ToString();
// fieldValues.Append(fieldValueString);
// fieldValues.Append(",");
// //}
//}
}
fieldValues.Remove(fieldValues.Length - 1, 1);
fieldValues.Append(";");
}
// execute the remaining statments
if (string.IsNullOrEmpty(fieldValues.ToString()) == false)
{
Epi.Data.DBReadExecute.ExecuteSQL(ConnectionString, InsertSQL.ToString() + query.GetInsertValue(fieldValues.ToString()), 0);
}
}
UpdatePageTableGUIDs(view);
if (view.IsRelatedView)
{
//Query updateQuery = db.CreateQuery("update " + view.TableName + " set OldFKEY = FKEY");
//db.ExecuteNonQuery(updateQuery);
}
RaiseEventImportStatus(string.Format(SharedStrings.IMPORT_COMPLETE, recordCount));
//System.Console.WriteLine("Finished Copy Data {0}", System.DateTime.Now);
}
}
catch (SqlException se)
{
if (se.Number == 296)
{
throw new GeneralException(string.Format(SharedStrings.IMPORT_ERROR_BAD_DATE_DATA, view.Name));
}
}
finally
{
}
}