/// <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()));
}
}
void ToNodeFiller_DoWork(object sender, DoWorkEventArgs e)
{
try
{
if (lvLinkedTo.InvokeRequired)
{
lvLinkedTo.Invoke(new MethodInvoker(delegate
{
lvLinkedTo.Items.Clear();
lvLinkedTo.Groups.Clear();
}));
}
else
{
lvLinkedTo.Items.Clear();
lvLinkedTo.Groups.Clear();
}
if (btnUnlinkTo.InvokeRequired)
{
btnUnlinkTo.Invoke(new MethodInvoker(delegate
{
btnUnlinkTo.Enabled = false;
}));
}
else
{
btnUnlinkTo.Enabled = false;
}
string uniqueKeys = "";
string parens = "";
string joins = "";
foreach (View view in enterMainForm.View.Project.Views)
{
// IF statement added by E. Knudsen 4/5/2011 to prevent errors in Enter if another view (form) in the
// same project didn't have a data table.
//if (!string.IsNullOrEmpty(view.TableName) && db.TableExists(view.TableName))
//{
if (!view.IsRelatedView)
{
uniqueKeys += "t" + view.Id + ".UniqueKey as Key" + view.Id + ", ";
parens += "(";
joins += "left outer join " + view.TableName + " t" + view.Id + " on m.ToRecordGuid = t" + view.Id + ".GlobalRecordId) ";
}
//}
}
uniqueKeys = uniqueKeys.Substring(0, uniqueKeys.Length - 2) + " ";
Query query = db.CreateQuery(@"Select FromRecordGuid, ToRecordGuid, FromViewId, ToViewId, " + uniqueKeys + " from " + parens + "metaLinks m " + joins + " where m.FromRecordGuid = @GlobalRecordId");
QueryParameter parameter = new QueryParameter("@GlobalRecordId", DbType.StringFixedLength, enterMainForm.View.CurrentGlobalRecordId);
parameter.Size = enterMainForm.View.CurrentGlobalRecordId.Length;
query.Parameters.Add(parameter);
DataTable data = db.Select(query);
if (data.Rows.Count > 0)
{
foreach (View view in enterMainForm.View.Project.Views)
{
if (lvLinkedTo.InvokeRequired)
{
lvLinkedTo.Invoke(new MethodInvoker(delegate
{
lvLinkedTo.Groups.Add(view.Id.ToString(), view.Name);
}));
}
else
{
lvLinkedTo.Groups.Add(view.Id.ToString(), view.Name);
}
}
}
foreach (DataRow row in data.Rows)
{
ListViewItem item = new ListViewItem(row["Key" + row["ToViewId"].ToString()].ToString());
for (int x = 0; x < data.Columns.Count; x++)
{
item.SubItems.Add(row[x].ToString());
}
item.ImageIndex = 0;
item.Group = lvLinkedTo.Groups[row["ToViewId"].ToString()];
if (lvLinkedTo.InvokeRequired)
{
lvLinkedTo.Invoke(new MethodInvoker(delegate
{
lvLinkedTo.Items.Add(item);
}));
}
else
{
lvLinkedTo.Items.Add(item);
}
}
}
catch (Exception ex)
{
// do nothing!
}
}
/// <summary>
/// Processes a form's base table
/// </summary>
/// <param name="result">The web survey result</param>
/// <param name="destinationView">The destination form</param>
/// <param name="destinationGUIDList">The list of GUIDs that exist in the destination</param>
private void ProcessBaseTable(Dictionary<string, Dictionary<string, WebFieldData>> result, 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;
foreach (KeyValuePair<string,Dictionary<string, WebFieldData>> surveyAnswer in result)
{
QueryParameter paramRecordStatus = new QueryParameter("@RECSTATUS", DbType.Int32, 1);
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 = surveyAnswer.Key; // sourceReader["GlobalRecordId"].ToString();
string FKEY = string.Empty; // sourceReader["FKEY"].ToString(); // FKEY not needed, no related forms to process
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.ToUpper()))
{
update = true;
append = false;
}
else
{
append = true;
update = false;
}
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)
{
try
{
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;
System.Diagnostics.Debug.Print(insertQuery.SqlStatement);
destinationProjectDataDriver.ExecuteNonQuery(insertQuery);
foreach (Page page in destinationView.Pages)
{
sb = new StringBuilder();
sb.Append(" insert into ");
sb.Append(destinationProjectDataDriver.InsertInEscape(page.TableName));
sb.Append(StringLiterals.SPACE);
sb.Append("([GlobalRecordId])");
sb.Append(" values (");
sb.Append("'" + GUID + "'");
sb.Append(") ");
insertQuery = destinationProjectDataDriver.CreateQuery(sb.ToString());
destinationProjectDataDriver.ExecuteNonQuery(insertQuery);
}
recordsInserted++;
}
catch(Exception ex)
{
throw ex;
}
// }
}
this.BeginInvoke(new SetProgressBarDelegate(IncrementProgressBarValue), 1);
}
//sourceReader.Close();
//sourceReader.Dispose();
//}
//catch (Exception ex)
//{
// this.BeginInvoke(new SetStatusDelegate(AddErrorStatusMessage), ex.Message);
// return false;
//}
//finally
//{
//}
this.BeginInvoke(new SetStatusDelegate(AddStatusMessage), "On base table '" + destinationTable + "', " + recordsInserted.ToString() + " record(s) inserted and " + recordsUpdated.ToString() + " record(s) updated.");
}
public Int32 InsertMetaImage(byte[] imageAsBytes)
{
Query insertQuery = db.CreateQuery
(
"insert into [metaImages] ([Image],[ImageUniqueValue]) " +
"VALUES (@Image,@ImageUniqueValue)"
);
Data.QueryParameter imageParam = new QueryParameter("@Image", DbType.Object, imageAsBytes);
imageParam.Size = imageAsBytes.Length + 1;
insertQuery.Parameters.Add(imageParam);
insertQuery.Parameters.Add(new QueryParameter("@ImageUniqueValue", DbType.Int32, imageAsBytes.Length));
db.ExecuteNonQuery(insertQuery);
return GetMaxImageId();
}
void UpdateBaseTable(View view)
{
bool hasColumns = dbDriver.ColumnExists(view.TableName, ColumnNames.RECORD_LAST_SAVE_LOGON_NAME);
if (hasColumns)
{
System.Security.Principal.WindowsIdentity winId = System.Security.Principal.WindowsIdentity.GetCurrent();
String winIdName = winId.Name;
StringBuilder sbUpdate = new StringBuilder();
sbUpdate.Append("update " + view.TableName);
sbUpdate.Append(Util.InsertIn("set", StringLiterals.SPACE));
sbUpdate.Append(StringLiterals.LEFT_SQUARE_BRACKET);
sbUpdate.Append(ColumnNames.RECORD_LAST_SAVE_LOGON_NAME);
sbUpdate.Append(StringLiterals.RIGHT_SQUARE_BRACKET);
sbUpdate.Append(StringLiterals.EQUAL);
sbUpdate.Append(Util.InsertInSingleQuotes(winIdName));
sbUpdate.Append(", ");
sbUpdate.Append(StringLiterals.LEFT_SQUARE_BRACKET);
sbUpdate.Append(ColumnNames.RECORD_LAST_SAVE_TIME);
sbUpdate.Append(StringLiterals.RIGHT_SQUARE_BRACKET);
sbUpdate.Append(StringLiterals.EQUAL);
sbUpdate.Append("@" + ColumnNames.RECORD_LAST_SAVE_TIME);
sbUpdate.Append(", ");
sbUpdate.Append(StringLiterals.LEFT_SQUARE_BRACKET);
sbUpdate.Append(ColumnNames.REC_STATUS);
sbUpdate.Append(StringLiterals.RIGHT_SQUARE_BRACKET);
sbUpdate.Append(StringLiterals.EQUAL);
sbUpdate.Append("@" + ColumnNames.REC_STATUS);
sbUpdate.Append(Util.InsertIn("where", StringLiterals.SPACE));
sbUpdate.Append(ColumnNames.GLOBAL_RECORD_ID + StringLiterals.EQUAL);
sbUpdate.Append(Util.InsertInSingleQuotes(view.CurrentGlobalRecordId));
Query updateQuery = dbDriver.CreateQuery(sbUpdate.ToString());
QueryParameter time = new QueryParameter("@" + ColumnNames.RECORD_LAST_SAVE_TIME, DbType.DateTime, DateTime.Now.ToString());
updateQuery.Parameters.Add(time);
QueryParameter RecStatus = new QueryParameter("@" + ColumnNames.REC_STATUS, DbType.Int32, view.RecStatusField.CurrentRecordValue);
updateQuery.Parameters.Add(RecStatus);
dbDriver.ExecuteNonQuery(updateQuery);
}
}
/// <summary>
/// Returns a native equivalent of a DbParameter
/// </summary>
/// <returns>Native equivalent of a DbParameter</returns>
protected OleDbParameter ConvertToNativeParameter(QueryParameter parameter)
{
if (parameter.DbType.Equals(DbType.Guid))
{
parameter.Value = new Guid(parameter.Value.ToString());
}
OleDbParameter param = new OleDbParameter(parameter.ParameterName, CovertToNativeDbType(parameter.DbType), parameter.Size, parameter.Direction, parameter.IsNullable, parameter.Precision, parameter.Scale, parameter.SourceColumn, parameter.SourceVersion, parameter.Value);
return param;
}
/// <summary>
/// Constructs the object
/// </summary>
protected override void Construct()
{
Parameter = new QueryParameter(ParameterName, System.Data.DbType.String, Value);
}
/// <summary>
/// Processes a form's base table
/// </summary>
/// <param name="destinationView">The destination form</param>
/// <param name="destinationGUIDList">The list of GUIDs that exist in the destination</param>
private void ProcessBaseTable(View destinationView, List<string> destinationGUIDList)
{
this.BeginInvoke(new SetStatusDelegate(SetStatusMessage), "Processing records on base table...");
int recordsInserted = 0;
int recordsUpdated = 0;
string destinationTable = destinationView.TableName;
try
{
//IDataReader sourceReader = sourceProjectDataDriver.GetTableDataReader(sourceView.TableName);
foreach (string surveyGUID in surveyGUIDs /*Epi.Web.Common.DTO.SurveyAnswerDTO surveyAnswer in result.SurveyResponseList*/)
{
object recordStatus = 1; // no marking for deletion supported at this time.
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 = surveyGUID; // surveyAnswer.ResponseId; // sourceReader["GlobalRecordId"].ToString();
string FKEY = string.Empty; // sourceReader["FKEY"].ToString(); // FKEY not needed, no related forms to process
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);
}
}
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.");
}
/// <summary>
/// Save Code table data.
/// </summary>
/// <param name="dataTable">Code table data.</param>
/// <param name="tableName">Code table name.</param>
/// <param name="columnNames">Code table column names.</param>
public void SaveCodeTableData(DataTable dataTable, string tableName, string[] columnNames)
{
Query insertQuery;
StringBuilder sb = null;
Query updateQuery;
try
{
sb = new StringBuilder();
sb.Append(SqlKeyWords.INSERT);
sb.Append(StringLiterals.SPACE);
sb.Append(SqlKeyWords.INTO);
sb.Append(StringLiterals.LEFT_SQUARE_BRACKET);
sb.Append(tableName);
sb.Append(StringLiterals.RIGHT_SQUARE_BRACKET);
sb.Append(StringLiterals.PARANTHESES_OPEN);
for (int i = 0; i < columnNames.Length; i++)
{
sb.Append(StringLiterals.LEFT_SQUARE_BRACKET);
sb.Append(columnNames[i]);
sb.Append(StringLiterals.RIGHT_SQUARE_BRACKET);
if (i < columnNames.Length - 1)
{
sb.Append(StringLiterals.COMMA);
sb.Append(StringLiterals.SPACE);
}
}
sb.Append(StringLiterals.PARANTHESES_CLOSE);
sb.Append(StringLiterals.SPACE);
sb.Append(SqlKeyWords.VALUES);
sb.Append(StringLiterals.PARANTHESES_OPEN);
for (int i = 0; i < columnNames.Length; i++)
{
sb.Append(StringLiterals.COMMERCIAL_AT);
sb.Append(columnNames[i].Replace(" ", ""));
if (i < columnNames.Length - 1)
{
sb.Append(StringLiterals.COMMA);
sb.Append(StringLiterals.SPACE);
}
}
sb.Append(StringLiterals.PARANTHESES_CLOSE);
insertQuery = db.CreateQuery(sb.ToString());
for (int i = 0; i < columnNames.Length; i++)
{
QueryParameter insertParameter = new QueryParameter(StringLiterals.COMMERCIAL_AT + columnNames[i].Replace(" ", ""), DbType.String, columnNames[i], columnNames[i]);
insertQuery.Parameters.Add(insertParameter);
}
sb.Remove(0, sb.Length);
sb.Append(SqlKeyWords.UPDATE);
sb.Append(StringLiterals.SPACE);
sb.Append(StringLiterals.LEFT_SQUARE_BRACKET);
sb.Append(tableName);
sb.Append(StringLiterals.RIGHT_SQUARE_BRACKET);
sb.Append(StringLiterals.SPACE);
sb.Append(SqlKeyWords.SET);
sb.Append(StringLiterals.SPACE);
for (int i = 0; i < columnNames.Length; i++)
{
sb.Append(StringLiterals.LEFT_SQUARE_BRACKET);
sb.Append(columnNames[i]);
sb.Append(StringLiterals.RIGHT_SQUARE_BRACKET);
sb.Append(StringLiterals.SPACE);
sb.Append(StringLiterals.EQUAL);
sb.Append(StringLiterals.SPACE);
sb.Append(StringLiterals.COMMERCIAL_AT);
sb.Append(StringLiterals.NEW_VALUE);
sb.Append(i.ToString());
if (i < (columnNames.Length-1))
{
sb.Append(StringLiterals.COMMA);
sb.Append(StringLiterals.SPACE);
}
}
sb.Append(StringLiterals.SPACE);
sb.Append(SqlKeyWords.WHERE);
sb.Append(StringLiterals.SPACE);
for (int i = 0; i < columnNames.Length; i++)
{
sb.Append(StringLiterals.LEFT_SQUARE_BRACKET);
sb.Append(columnNames[i]);
sb.Append(StringLiterals.RIGHT_SQUARE_BRACKET);
sb.Append(StringLiterals.SPACE);
sb.Append(StringLiterals.EQUAL);
sb.Append(StringLiterals.SPACE);
sb.Append(StringLiterals.COMMERCIAL_AT);
sb.Append(StringLiterals.OLD_VALUE);
sb.Append(i.ToString());
if (i < (columnNames.Length - 1))
{
sb.Append(StringLiterals.SPACE);
sb.Append(SqlKeyWords.AND);
sb.Append(StringLiterals.SPACE);
}
}
updateQuery = db.CreateQuery(sb.ToString());
for (int i = 0; i < columnNames.Length; i++)
{
updateQuery.Parameters.Add(new QueryParameter(StringLiterals.COMMERCIAL_AT + StringLiterals.NEW_VALUE + i.ToString(), DbType.String, columnNames[i], columnNames[i]));
updateQuery.Parameters.Add(new QueryParameter(StringLiterals.COMMERCIAL_AT + StringLiterals.OLD_VALUE + i.ToString(), DbType.String, columnNames[i], columnNames[i]));
updateQuery.Parameters[1].SourceVersion = DataRowVersion.Original;
}
db.Update(dataTable, tableName, insertQuery, updateQuery);
}
catch (Exception ex)
{
throw new GeneralException("Could not save data for code table", ex.Message + " " + ex.StackTrace);
}
finally
{
updateQuery = null;
insertQuery = null;
sb = null;
}
}
/// <summary>
/// Returns a native equivalent of a DbParameter
/// </summary>
/// <returns>A native equivalent of a DbParameter</returns>
protected virtual NpgsqlParameter ConvertToNativeParameter(QueryParameter parameter)
{
//TODO: Test this when MySQL comes back on the radar.
if (parameter.DbType.Equals(DbType.Guid))
{
parameter.Value = new Guid(parameter.Value.ToString());
}
return new NpgsqlParameter(parameter.ParameterName, CovertToNativeDbType(parameter.DbType), parameter.Size, parameter.SourceColumn, parameter.Direction, parameter.IsNullable, parameter.Precision, parameter.Scale, parameter.SourceVersion, parameter.Value);
}
/// <summary>
/// Insert Code table data.
/// </summary>
/// <param name="dataTable">Code table data.</param>
/// <param name="tableName">Code table name.</param>
/// <param name="columnNames">Code table name columns.</param>
public void InsertCodeTableData(DataTable dataTable, string tableName, string[] columnNames)
{
Query insertQuery;
bool reportProgress = false;
int rowCount = 0;
StringBuilder sb = null;
int step = 0;
try
{
sb = new StringBuilder();
// Build the base INSERT statement. This command will be reused for each data row.
sb.Append(SqlKeyWords.INSERT);
sb.Append(StringLiterals.SPACE);
sb.Append(SqlKeyWords.INTO);
sb.Append(StringLiterals.LEFT_SQUARE_BRACKET);
sb.Append(tableName);
sb.Append(StringLiterals.RIGHT_SQUARE_BRACKET);
sb.Append(StringLiterals.PARANTHESES_OPEN);
for (int i = 0; i < columnNames.Length; i++)
{
sb.Append(StringLiterals.LEFT_SQUARE_BRACKET);
sb.Append(columnNames[i]);
sb.Append(StringLiterals.RIGHT_SQUARE_BRACKET);
if (i < columnNames.Length - 1)
{
sb.Append(StringLiterals.COMMA);
sb.Append(StringLiterals.SPACE);
}
}
sb.Append(StringLiterals.PARANTHESES_CLOSE);
sb.Append(StringLiterals.SPACE);
sb.Append(SqlKeyWords.VALUES);
sb.Append(StringLiterals.PARANTHESES_OPEN);
for (int i = 0; i < columnNames.Length; i++)
{
sb.Append(StringLiterals.COMMERCIAL_AT);
sb.Append(columnNames[i].Replace(" ", ""));
if (i < columnNames.Length - 1)
{
sb.Append(StringLiterals.COMMA);
sb.Append(StringLiterals.SPACE);
}
}
sb.Append(StringLiterals.PARANTHESES_CLOSE);
insertQuery = db.CreateQuery(sb.ToString());
// Determine if we need to report progress and what progress step to count.
rowCount = dataTable.Rows.Count;
step = (rowCount/10);
// If row count is too small, skip progress reporting.
reportProgress = (step > 0);
if (reportProgress)
{
RaiseProgressReportBeginEvent(0, rowCount, step);
}
for (int rowIndex = 0; rowIndex < rowCount; rowIndex++)
{
DataRow row = dataTable.Rows[rowIndex];
insertQuery.Parameters.Clear();
for (int i = 0; i < columnNames.Length; i++)
{
string columnName = string.Empty;
if (row[columnNames[i]] is string)
{
columnName = (string)row[columnNames[i]];
}
string atColumnName = StringLiterals.COMMERCIAL_AT + columnNames[i].Replace(" ", "");
QueryParameter param = new QueryParameter(atColumnName, DbType.String, columnName);
insertQuery.Parameters.Add(param);
}
db.ExecuteNonQuery(insertQuery);
if (reportProgress)
{
int rem = 0;
Math.DivRem(rowIndex, step, out rem);
if (rem.Equals(0))
{
RaiseProgressReportUpdateEvent();
}
}
}
}
catch (Exception ex)
{
throw new GeneralException("Could not insert code into code table", ex);
}
finally
{
insertQuery = null;
sb = null;
if (reportProgress)
{
RaiseProgressReportEndEvent();
}
}
}
/// <summary>
/// Gets a view object based on view name.
/// </summary>
/// <param name="viewFullName">Name of the view.</param>
/// <returns>A view object.</returns>
public View GetViewByFullName(string viewFullName)
{
#region Input Validation
if (string.IsNullOrEmpty(viewFullName))
{
throw new ArgumentNullException("View Full Name");
}
#endregion
try
{
string viewName = viewFullName.Substring(viewFullName.LastIndexOf(":") + 1);
Query query = db.CreateQuery("select [ViewId], [Name], [CheckCode], [CheckCodeBefore], [CheckCodeAfter], [RecordCheckCodeBefore], [RecordCheckCodeAfter], [CheckCodeVariableDefinitions], [IsRelatedView], [Width], [Height], [Orientation], [LabelAlign] " +
Util.InsertIn("from", StringLiterals.SPACE) + db.InsertInEscape("metaViews") +
Util.InsertIn("where", StringLiterals.SPACE) + db.InsertInEscape("Name") + StringLiterals.EQUAL + "@ViewName");
QueryParameter parameter = new QueryParameter("@ViewName", DbType.String, viewName);
parameter.Size = viewName.Length;
query.Parameters.Add(parameter);
DataTable results = db.Select(query);
if (results.Rows.Count > 0)
{
return new View(results.Rows[0], Project);
}
else
{
return null;
}
}
finally
{
}
}
/// <summary>
/// Constructs the object
/// </summary>
protected override void Construct()
{
Parameter = new QueryParameter(ParameterName, System.Data.DbType.DateTime2, Convert.ToDateTime(Value));
}
/// <summary>
/// Constructs the object
/// </summary>
protected override void Construct()
{
Parameter = new QueryParameter(ParameterName, System.Data.DbType.Int16, bool.Parse(Value.ToString()));
}
/// <summary>
/// Insert record into table.
/// </summary>
/// <param name="view">View of a table.</param>
/// <returns>Unique key of the record.</returns>
public int SaveRecord(View view)
{
Configuration config = Configuration.GetNewInstance();
try
{
WordBuilder fieldNames = new WordBuilder(StringLiterals.COMMA);
WordBuilder fieldValues = new WordBuilder(StringLiterals.COMMA);
List<QueryParameter> fieldValueParams = new List<QueryParameter>();
foreach (IDataField dataField in view.Fields.DataFields)
{
if (dataField is GlobalRecordIdField || dataField is ForeignKeyField)
{
fieldNames.Append(dbDriver.InsertInEscape(((Epi.INamedObject)dataField).Name));
if (dataField.CurrentRecordValueObject == null)
{
fieldValues.Append(" null ");
}
else if (string.IsNullOrEmpty(dataField.CurrentRecordValueObject.ToString()))
{
fieldValues.Append(" null ");
}
else
{
String fieldName = ((Epi.INamedObject)dataField).Name;
fieldValues.Append("@" + fieldName);
fieldValueParams.Add(dataField.CurrentRecordValueAsQueryParameter);
}
}
else
{
continue;
}
}
System.Security.Principal.WindowsIdentity winId = System.Security.Principal.WindowsIdentity.GetCurrent();
String winIdName = winId.Name;
bool hasSaveColumns = dbDriver.ColumnExists(view.TableName, ColumnNames.RECORD_FIRST_SAVE_LOGON_NAME);
StringBuilder sb = new StringBuilder();
sb.Append(" insert into ");
sb.Append(dbDriver.InsertInEscape(view.TableName));
sb.Append(StringLiterals.SPACE);
if (hasSaveColumns)
{
sb.Append(Util.InsertInParantheses(fieldNames.ToString() + ", [" + ColumnNames.RECORD_FIRST_SAVE_LOGON_NAME + "], [" + ColumnNames.RECORD_FIRST_SAVE_TIME + "]"));
sb.Append(" values (");
sb.Append(fieldValues.ToString() + ", @" + ColumnNames.RECORD_FIRST_SAVE_LOGON_NAME +", @" + ColumnNames.RECORD_FIRST_SAVE_TIME );
}
else
{
sb.Append(Util.InsertInParantheses(fieldNames.ToString()));
sb.Append(" values (");
sb.Append(fieldValues.ToString());
}
sb.Append(") ");
Query insertQuery = dbDriver.CreateQuery(sb.ToString());
insertQuery.Parameters = fieldValueParams;
if (hasSaveColumns)
{
QueryParameter name = new QueryParameter("@" + ColumnNames.RECORD_FIRST_SAVE_LOGON_NAME, DbType.String, winIdName);
insertQuery.Parameters.Add(name);
QueryParameter time = new QueryParameter("@" + ColumnNames.RECORD_FIRST_SAVE_TIME, DbType.DateTime, DateTime.Now.ToString());
insertQuery.Parameters.Add(time);
}
dbDriver.ExecuteNonQuery(insertQuery);
if (hasSaveColumns)
{
UpdateBaseTable(view);
}
string relatedViewFilter = string.Empty;
if (view.IsRelatedView)
{
relatedViewFilter = " where ";
relatedViewFilter += dbDriver.InsertInEscape(ColumnNames.FOREIGN_KEY);
relatedViewFilter += StringLiterals.EQUAL + "'" + view.ForeignKeyField.CurrentRecordValueString + "'";
}
Query selectQuery = dbDriver.CreateQuery(" select" +
Util.InsertIn("max" + Util.InsertInParantheses(dbDriver.InsertInEscape(ColumnNames.UNIQUE_KEY)), StringLiterals.SPACE) +
"from" + StringLiterals.SPACE + dbDriver.InsertInEscape(view.TableName) +
relatedViewFilter);
foreach (Epi.Page page in view.Pages)
{
this.SaveRecord(page);
}
int recordID;
int.TryParse(dbDriver.ExecuteScalar(selectQuery).ToString(), out recordID);
foreach (GridField grid in view.Fields.GridFields)
{
this.SaveGridRecord(view, recordID, grid, grid.DataSource);
grid.DataSource = null;
}
return recordID;
}
finally
{
}
}
/// <summary>
/// Helper method for the CreateFromXml method. Loads a list of row filter conditions from XML.
/// </summary>
/// <param name="element">XmlElement to process</param>
private void CreateRowFiltersFromXML(XmlElement element)
{
if (element.Name.ToLower().Equals("rowfilter"))
{
foreach (XmlElement childElement in element.ChildNodes)
{
if (childElement.Name.ToLower().Equals("rowfilterquery"))
{
selectQuery = sourceProject.CollectedData.GetDbDriver().CreateQuery(childElement.InnerText.Replace(">", ">").Replace("<", "<"));
}
else if (childElement.Name.ToLower().Equals("rowfilterparameters"))
{
foreach (XmlElement gcElement in childElement.ChildNodes)
{
System.Data.DbType dbType = System.Data.DbType.String;
string name = "";
object value = null;
foreach (XmlElement ggcElement in gcElement.ChildNodes)
{
switch (ggcElement.Name.ToLower())
{
case "dbtype":
dbType = ((DbType)Int32.Parse(ggcElement.InnerText));
break;
case "name":
name = ggcElement.InnerText;
break;
case "value":
value = ggcElement.InnerText;
break;
}
}
QueryParameter queryParameter = new QueryParameter(name, dbType, value);
if (selectQuery != null)
{
selectQuery.Parameters.Add(queryParameter);
}
}
}
else if (childElement.Name.ToLower().Equals("rowfilterconditions"))
{
List<IRowFilterCondition> conditions = new List<IRowFilterCondition>();
foreach (XmlElement grandChildElement in childElement.ChildNodes)
{
if (grandChildElement.HasAttribute("filterType"))
{
string strType = grandChildElement.Attributes["filterType"].Value + ",Epi.ImportExport";
Type filterType = Type.GetType(strType, false, true);
if (filterType != null)
{
IRowFilterCondition condition = (IRowFilterCondition)Activator.CreateInstance(filterType, new object[] { });
condition.CreateFromXml(grandChildElement);
conditions.Add(condition);
}
}
}
this.rowFilterConditions = conditions;
}
}
}
string filter = String.Empty;
if (this.rowFilterConditions.Count > 0)
{
filter = ImportExportSharedStrings.SCRIPT_FILTERS;
WordBuilder wb = new WordBuilder(" and ");
foreach (IRowFilterCondition rfc in this.rowFilterConditions)
{
wb.Add(rfc.Description);
}
filter = filter + wb.ToString();
CallbackAddStatusMessage(filter);
}
}
public virtual void BuildSql()
{
Parameter = new QueryParameter(ParameterName, System.Data.DbType.String, Value);
Sql = "[" + ColumnName + "] " + GetOperatorString(ConditionOperator) + " " + ParameterName;
}
