/// <summary> /// Method that generates the command /// </summary> protected override void GenerateCommand() { string expression = txtRecAffected.Text.Trim(); #region preconditions if (expression.Trim() != "*" && !ValidExpression(expression)) //dcs0 8/1/2008 { throw new GeneralException(string.Format(SharedStrings.INVALID_EXPRESSION, expression)); } #endregion Preconditions WordBuilder command = new WordBuilder(); command.Append(CommandNames.UNDELETE); //command.Append(txtRecAffected.Text.Trim()); command.Append(expression); // dcs0 9/17/2008 if (cbkRunSilent.Checked) { command.Append(CommandNames.RUNSILENT); } CommandText = command.ToString(); try { this.EpiInterpreter.Parse(CommandText); } catch (Exception ex) { throw new GeneralException(string.Format(SharedStrings.INVALID_EXPRESSION, expression)); } }
/// <summary> /// Generates command text /// </summary> protected override void GenerateCommand() { WordBuilder command = new WordBuilder(); command.Append(CommandNames.SUMMARIZE); //Append aggregates foreach (string item in lbxVar.Items) { command.Append(item); } //Append output table command.Append(CommandNames.TO); command.Append(txtOutput.Text.Trim()); //Append strata variables if (lbxGroupBy.Items.Count > 0) { command.Append(CommandNames.STRATAVAR); command.Append(StringLiterals.EQUAL); foreach (string item in lbxGroupBy.Items) { command.Append(item); } } //Append weighted variables if (cmbWeight.SelectedItem != null && !string.IsNullOrEmpty(cmbWeight.SelectedItem.ToString())) { command.Append(CommandNames.WEIGHTVAR); command.Append(StringLiterals.EQUAL); command.Append(cmbWeight.SelectedItem.ToString()); } CommandText = command.ToString(); }
/// <summary> /// Method that generates the command /// </summary> protected override void GenerateCommand() { string expression = txtRecAffected.Text.Trim(); #region preconditions if (expression.Trim() != "*" && !ValidExpression(expression)) //dcs0 8/1/2008 { throw new GeneralException(string.Format(SharedStrings.INVALID_EXPRESSION, expression)); } #endregion Preconditions WordBuilder command = new WordBuilder(); command.Append(CommandNames.UNDELETE); //command.Append(txtRecAffected.Text.Trim()); command.Append(expression); // dcs0 9/17/2008 if (cbkRunSilent.Checked) { command.Append(CommandNames.RUNSILENT); } CommandText = command.ToString(); try { this.EpiInterpreter.Parse(CommandText); } catch(Exception ex) { throw new GeneralException(string.Format(SharedStrings.INVALID_EXPRESSION, expression)); } }
/// <summary> /// Generates command test /// </summary> protected override void GenerateCommand() { WordBuilder command = new WordBuilder(); command.Append((CommandNames.SELECT).ToUpper()); command.Append(txtSelectCriteria.Text.Trim()); CommandText = command.ToString() ; }
/// <summary> /// Gets a count of records in a column /// </summary> /// <param name="columnName">Name of the column</param> /// <returns>Number of records in a column</returns> public int GetRecordCount(Rule_Context pContext, string columnName) { #region Input Validation if (string.IsNullOrEmpty(columnName)) { throw new ArgumentNullException("columnName"); } #endregion Input Validation WordBuilder queryBuilder = new WordBuilder(); queryBuilder.Append("select count" + Util.InsertInParantheses(columnName)); queryBuilder.Append(GetSqlStatementPartFrom(pContext)); if (Db.ColumnExists(PrimaryTable.TableName, ColumnNames.REC_STATUS)) { string whereStatement = GetSqlStatementPartWhere(); queryBuilder.Append(whereStatement); } else if (!string.IsNullOrEmpty(selectCriteria)) { string whereClause = " where " + this.selectCriteria; queryBuilder.Append(whereClause); } Query query = Db.CreateQuery(queryBuilder.ToString()); //recast to int before return to remove cast run time error int result = Int32.Parse((Db.ExecuteScalar(query)).ToString()); return(result); }
/// <summary> /// Generates command text /// </summary> protected override void GenerateCommand() { WordBuilder command = new WordBuilder(); command.Append(CommandNames.WRITE); command.Append((rdbReplace.Checked) ? CommandNames.REPLACE : CommandNames.APPEND); if (cmbOutputFormat.Text.ToUpper() == "TEXT" || cmbOutputFormat.Text.ToUpper() == "FLAT ASCII FILE") { command.Append("\"TEXT\""); } else { command.Append("\"Epi7\""); } command.Append("{" + txtFileName.Text.Trim() + "}"); command.Append(":"); command.Append(FieldNameNeedsBrackets(cmbDataTable.Text) ? Util.InsertInSquareBrackets(cmbDataTable.Text) : cmbDataTable.Text); if (lbxVariables.SelectedItems.Count < 1) { command.Append(StringLiterals.STAR); } else { if (cbxAllExcept.Checked) { command.Append("* EXCEPT"); } foreach (string s in lbxVariables.SelectedItems) { command.Append(FieldNameNeedsBrackets(s) ? Util.InsertInSquareBrackets(s) : s); } } CommandText = command.ToString(); }
/// <summary> /// Generates command test /// </summary> protected override void GenerateCommand() { WordBuilder command = new WordBuilder(); command.Append((CommandNames.SELECT).ToUpperInvariant()); command.Append(txtSelectCriteria.Text.Trim()); CommandText = command.ToString(); }
/// <summary> /// Generates command text /// </summary> protected override void GenerateCommand() { WordBuilder command = new WordBuilder(); command.Append(CommandNames.DEFINE); command.Append(txtGroupVar.Text.Trim()); command.Append(CommandNames.GROUPVAR); foreach (string s in lbxVariables.SelectedItems) { command.Append(s); } CommandText = command.ToString(); }
/// <summary> /// Generates the command text /// </summary> protected override void GenerateCommand() { StringBuilder sb = new StringBuilder(CommandNames.HEADER); sb.Append(StringLiterals.SPACE); sb.Append(1.ToString()); sb.Append(StringLiterals.SPACE); if (true) { sb.Append(Util.InsertInDoubleQuotes(txtTitle.Text)); WordBuilder wb = new WordBuilder(StringLiterals.COMMA); if (boldToolStripButton.Checked || italicToolStripButton.Checked || underlineToolStripButton.Checked) { sb.Append(StringLiterals.SPACE); if (boldToolStripButton.Checked) { wb.Append("BOLD"); } if (italicToolStripButton.Checked) { wb.Append("ITALIC"); } if (underlineToolStripButton.Checked) { wb.Append("UNDERLINE"); } sb.Append(Util.InsertInParantheses(wb.ToString())); } if (!string.IsNullOrEmpty(textFontColorName) && !string.IsNullOrEmpty(fontSizeToolStripDDL.Text)) { sb.Append(" TEXTFONT "); if (!string.IsNullOrEmpty(textFontColorName)) { sb.Append(textFontColorName.ToUpperInvariant()); } if (!string.IsNullOrEmpty(fontSizeToolStripDDL.Text)) { sb.Append(StringLiterals.SPACE); sb.Append(fontSizeToolStripDDL.Text); } } } CommandText = sb.ToString(); }
/// <summary> /// Generates the command text /// </summary> protected override void GenerateCommand() { WordBuilder command = new WordBuilder(); command.Append(CommandNames.LIST); if (lbxVariables.Items.Count > 0) { if (cbxAllExcept.Checked) { command.Append(StringLiterals.STAR); command.Append(StringLiterals.SPACE); command.Append(CommandNames.EXCEPT); } foreach (string item in lbxVariables.Items) { command.Append(FieldNameNeedsBrackets(item) ? Util.InsertInSquareBrackets(item) : item); } } else { command.Append(cmbVar.Text); } if (WinUtil.GetSelectedRadioButton(gbxDisplayMode) == rdbGrid) { command.Append(CommandNames.GRIDTABLE); } else if (WinUtil.GetSelectedRadioButton(gbxDisplayMode) == rdbAllowUpdates) { command.Append(CommandNames.UPDATE); } CommandText = command.ToString(); }
/// <summary> /// Generate the command text /// </summary> protected override void GenerateCommand() { WordBuilder command = new WordBuilder(); command.Append(CommandNames.SORT); foreach (string item in lbxSortVar.Items) { command.Append(item.Substring(0,item.Length - 4));; //Append 'DESCENDING' if (item.EndsWith(StringLiterals.HYPHEN + StringLiterals.PARANTHESES_CLOSE)) { command.Append(CommandNames.DESCENDING); } } CommandText = command.ToString(); }
/// <summary> /// Generate the command text /// </summary> protected override void GenerateCommand() { WordBuilder command = new WordBuilder(); command.Append(CommandNames.SORT); foreach (string item in lbxSortVar.Items) { command.Append(item.Substring(0, item.Length - 4));; //Append 'DESCENDING' if (item.EndsWith(StringLiterals.HYPHEN + StringLiterals.PARANTHESES_CLOSE)) { command.Append(CommandNames.DESCENDING); } } CommandText = command.ToString(); }
/// <summary> /// Generates the command text /// </summary> protected override void GenerateCommand() { StringBuilder sb = new StringBuilder(CommandNames.HEADER); sb.Append(StringLiterals.SPACE); sb.Append(1.ToString()); sb.Append(StringLiterals.SPACE); if (true) { sb.Append(Util.InsertInDoubleQuotes(txtTitle.Text)); WordBuilder wb = new WordBuilder(StringLiterals.COMMA); if (boldToolStripButton.Checked || italicToolStripButton.Checked || underlineToolStripButton.Checked) { sb.Append(StringLiterals.SPACE); if (boldToolStripButton.Checked) wb.Append("BOLD"); if (italicToolStripButton.Checked) wb.Append("ITALIC"); if (underlineToolStripButton.Checked) wb.Append("UNDERLINE"); sb.Append(Util.InsertInParantheses(wb.ToString())); } if (!string.IsNullOrEmpty(textFontColorName) && !string.IsNullOrEmpty(fontSizeToolStripDDL.Text)) { sb.Append(" TEXTFONT "); if (!string.IsNullOrEmpty(textFontColorName)) { sb.Append(textFontColorName.ToUpper()); } if (!string.IsNullOrEmpty(fontSizeToolStripDDL.Text)) { sb.Append(StringLiterals.SPACE); sb.Append(fontSizeToolStripDDL.Text); } } } CommandText = sb.ToString(); }
/// <summary> /// Generates command text /// </summary> protected override void GenerateCommand() { WordBuilder command = new WordBuilder(); command.Append(CommandNames.ASSIGN); command.Append(cmbAssignVar.Text); command.Append("="); // Fix for defect #20 - prevent the resulting command from having to = signs if the user supplies one by accident. string expression = txtExpression.Text.Trim(); if (expression.Length > 0) { if (expression[0] == '=') { expression = expression.Remove(0, 1); expression = expression.Trim(); } } command.Append(expression); CommandText = command.ToString(); }
/// <summary> /// Generates command text /// </summary> protected override void GenerateCommand() { WordBuilder command = new WordBuilder(); command.Append(CommandNames.WRITE); command.Append((rdbReplace.Checked) ? CommandNames.REPLACE : CommandNames.APPEND); if (cmbOutputFormat.Text.ToUpperInvariant() == "TEXT" || cmbOutputFormat.Text.ToUpperInvariant() == "FLAT ASCII FILE") { command.Append("\"TEXT\""); } else { command.Append("\"Epi7\""); } command.Append("{" + txtFileName.Text.Trim() + "}"); command.Append(":"); command.Append(FieldNameNeedsBrackets(cmbDataTable.Text) ? Util.InsertInSquareBrackets(cmbDataTable.Text) : cmbDataTable.Text); if (lbxVariables.SelectedItems.Count < 1) { command.Append(StringLiterals.STAR); } else { if (cbxAllExcept.Checked) { command.Append("* EXCEPT"); } foreach (string s in lbxVariables.SelectedItems) { command.Append(FieldNameNeedsBrackets(s) ? Util.InsertInSquareBrackets(s) : s); } } CommandText = command.ToString(); }
/// <summary> /// Handles the Click event of the OK button /// </summary> /// <param name="sender">Object that fired the event</param> /// <param name="e">.NET supplied event parameters</param> private void btnOk_Click(object sender, System.EventArgs e) { //Output = "ASSIGN " + cbxAssignVariable.SelectedItem.ToString() + " = " + txtAssignment.Text; WordBuilder command = new WordBuilder(); command.Append(CommandNames.ASSIGN); command.Append(cbxAssignVariable.SelectedItem.ToString()); command.Append("="); // Fix for defect #140 - prevent the resulting command from having two = signs if the user supplies one by accident. string expression = txtAssignment.Text.Trim(); if (expression.Length > 0) { if (expression[0] == '=') { expression = expression.Remove(0, 1); expression = expression.Trim(); } } command.Append(expression); Output = command.ToString(); this.DialogResult = DialogResult.OK; this.Hide(); }
public string GetWhereClause(View form) { string baseTableName = "t"; string whereClause = String.Empty; string recStatusComparison = "[" + baseTableName + "].[RECSTATUS] = 1"; if (RecordProcessingScope == Epi.RecordProcessingScope.Both) { recStatusComparison = "[" + baseTableName + "].[RECSTATUS] >= 0"; } else if (RecordProcessingScope == Epi.RecordProcessingScope.Deleted) { recStatusComparison = "[" + baseTableName + "].[RECSTATUS] = 0"; } whereClause = " WHERE " + recStatusComparison;// +" AND ("; string logicalOperatorString = " AND "; if (_conditionJoinType == ConditionJoinTypes.Or) { logicalOperatorString = " OR "; } bool hasFilterConditions = false; WordBuilder filterSql = new WordBuilder(logicalOperatorString); foreach (IRowFilterCondition rowFc in _rowFilterConditions) { hasFilterConditions = true; filterSql.Append(rowFc.Sql); } if (hasFilterConditions) { whereClause = whereClause + " AND (" + filterSql + ")"; } return(whereClause); }
/// <summary> /// Generates command text /// </summary> protected override void GenerateCommand() { string expression = txtRecAffected.Text.Trim(); #region preconditions if (expression.Trim() != "*" && !ValidExpression(expression)) //dcs0 8/1/2008 { MessageBox.Show(string.Format(SharedStrings.INVALID_EXPRESSION, expression)); CommandText = ""; return; } if (EpiInterpreter.Context.CurrentRead.File.Contains(".xlsx") || EpiInterpreter.Context.CurrentRead.File.Contains(".xls") || EpiInterpreter.Context.CurrentRead.File.Contains("FMT=Delimited")) { MessageBox.Show("Deletion not supported for this datasource", "DELETE RECORDS"); Close(); throw new GeneralException(string.Format(SharedStrings.INVALID_DATA_SOURCE, "Excel")); } #endregion Preconditions WordBuilder command = new WordBuilder(); command.Append(CommandNames.DELETE); if (expression.Trim() != "*") { command.Append("("); } command.Append(expression); if (expression.Trim() != "*") { command.Append(")"); } if (rdbPermDeletion.Checked) { command.Append(CommandNames.PERMANENT); } if (cbkRunSilent.Checked) { command.Append(CommandNames.RUNSILENT); } CommandText = command.ToString(); }
/// <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="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, 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); } foreach (Page page in form.Pages) { sb = new StringBuilder(); sb.Append(" insert into "); sb.Append(db.InsertInEscape(page.TableName)); sb.Append(StringLiterals.SPACE); sb.Append(StringLiterals.SPACE); sb.Append("([GlobalRecordId])"); sb.Append(" values ("); sb.Append("'" + guid + "'"); sb.Append(") "); insertQuery = db.CreateQuery(sb.ToString()); 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> /// 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."); }
/// <summary> /// Generates command text /// </summary> protected override void GenerateCommand() { string expression = txtRecAffected.Text.Trim(); #region preconditions if (expression.Trim() != "*" && !ValidExpression(expression)) //dcs0 8/1/2008 { throw new GeneralException(string.Format(SharedStrings.INVALID_EXPRESSION, expression)); } if (EpiInterpreter.Context.CurrentRead.File.Contains(".xlsx") || EpiInterpreter.Context.CurrentRead.File.Contains(".xls") || EpiInterpreter.Context.CurrentRead.File.Contains("FMT=Delimited")) { MessageBox.Show("Deletion not supported for this datasource", "DELETE RECORDS"); Close(); throw new GeneralException(string.Format(SharedStrings.INVALID_DATA_SOURCE, "Excel")); } #endregion Preconditions WordBuilder command = new WordBuilder(); command.Append(CommandNames.DELETE); if(expression.Trim() != "*") command.Append("("); command.Append(expression); if (expression.Trim() != "*") command.Append(")"); if (rdbPermDeletion.Checked) { command.Append(CommandNames.PERMANENT); } if (cbkRunSilent.Checked) { command.Append(CommandNames.RUNSILENT); } CommandText = command.ToString(); }
private static void ScanForwardLetters(Letter currentLetter, List<WordBuilder> builderList, WordBuilder previousBuilder) { WordBuilder newBuilder = new WordBuilder(previousBuilder);//ShorewoodPool.GetBuilder(previousBuilder); newBuilder.Append(currentLetter); builderList.Add(new WordBuilder(newBuilder));//ShorewoodPool.GetBuilder(newBuilder)); if ((newBuilder.CurrentString.Length > Shorewood.normalGameplayRenderer.grid.minimumWordLength)) { if (!CheckValidWord(newBuilder)) { //ShorewoodPool.ReturnBuilder(newBuilder); return; } } if (Shorewood.normalGameplayRenderer.grid.GetLetter(currentLetter.upperRightNeighbor) != Letter.EmptyLetter) { ScanForwardLetters(Shorewood.normalGameplayRenderer.grid.GetLetter(currentLetter.upperRightNeighbor), builderList, newBuilder); } if (Shorewood.normalGameplayRenderer.grid.GetLetter(currentLetter.rightNeighbor) != Letter.EmptyLetter) { ScanForwardLetters(Shorewood.normalGameplayRenderer.grid.GetLetter(currentLetter.rightNeighbor), builderList, newBuilder); } if (Shorewood.normalGameplayRenderer.grid.GetLetter(currentLetter.lowerRightNeighbor) != Letter.EmptyLetter) { ScanForwardLetters(Shorewood.normalGameplayRenderer.grid.GetLetter(currentLetter.lowerRightNeighbor), builderList, newBuilder); } //ShorewoodPool.ReturnBuilder(newBuilder); return; }
/// <summary> /// Get Patterns in metaPatterns table. /// </summary> /// <returns>DataTable of pattern information from metaPatterns table.</returns> public DataTable GetPatterns() { try { WordBuilder columnNames = new WordBuilder(StringLiterals.COMMA); columnNames.Append("PatternId"); columnNames.Append("DataTypeId"); columnNames.Append("Expression"); columnNames.Append("Mask"); columnNames.Append("FormattedExpression"); Query query = db.CreateQuery("select" + Util.InsertIn(columnNames.ToString(), StringLiterals.SPACE) + Util.InsertIn( "from", StringLiterals.SPACE) + db.InsertInEscape("metaPatterns")); return db.Select(query); } catch (Exception ex) { throw new GeneralException("Could not retrieve patterns", ex); } }
/// <summary> /// Insert record into table. /// </summary> /// <param name="view">View of a table.</param> /// <returns>Unique key of the record.</returns> public int SaveRecord(Page page) { if (TableExists(page.TableName) == false) { return 0; } Configuration config = Configuration.GetNewInstance(); try { WordBuilder fieldNames = new WordBuilder(StringLiterals.COMMA); WordBuilder fieldValues = new WordBuilder(StringLiterals.COMMA); List<QueryParameter> fieldValueParams = new List<QueryParameter>(); fieldNames.Append("GlobalRecordId"); fieldValues.Append("@GlobalRecordId"); fieldValueParams.Add(page.view.GlobalRecordIdField.CurrentRecordValueAsQueryParameter); foreach (RenderableField renderableField in page.Fields) { if (renderableField is GridField || renderableField is GroupField) { continue; } else if (renderableField is IDataField) { IDataField dataField = (IDataField)renderableField; if (dataField is UniqueKeyField) { continue; } else { 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 { } } StringBuilder sb = new StringBuilder(); sb.Append(" insert into "); sb.Append(dbDriver.InsertInEscape(page.TableName)); sb.Append(StringLiterals.SPACE); sb.Append(Util.InsertInParantheses(fieldNames.ToString())); sb.Append(" values ("); sb.Append(fieldValues.ToString()); sb.Append(") "); Query insertQuery = dbDriver.CreateQuery(sb.ToString()); insertQuery.Parameters = fieldValueParams; dbDriver.ExecuteNonQuery(insertQuery); return 0; } finally { } }
/// <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> /// Insert record into table. /// </summary> /// <param name="view">View of a table.</param> /// <param name="reader">Forward-only stream of result sets obtained by executing a command.</param> public void SaveRecord(View view, IDataReader reader) { try { bool hasData = false; WordBuilder fieldNames = new WordBuilder(); WordBuilder fieldValues = new WordBuilder(); fieldNames.Delimitter = StringLiterals.COMMA; fieldValues.Delimitter = StringLiterals.COMMA; foreach (Field field in view.Fields.TableColumnFields) { // Eliminate UniqueKeyFields. They are not inserted explicitly. if (!(field is UniqueKeyField)) { if (!Util.IsEmpty(reader[field.Name])) { hasData = true; fieldNames.Append(dbDriver.InsertInEscape(field.Name)); Object fieldValue = reader[field.Name]; string fieldValueString = string.Empty; // string fieldValue = reader[field.Name].ToString(); // TODO: Determine if the value is null. If so, no further formatting required. if (field is DateField) { fieldValueString = dbDriver.FormatDate((DateTime)fieldValue); } else if (field is TimeField) { fieldValueString = dbDriver.FormatTime((DateTime)fieldValue); } else if (field is DateTimeField) { fieldValueString = dbDriver.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 { fieldValueString = fieldValue.ToString(); } fieldValues.Append(fieldValueString); } } } // Concatenate the query clauses into one SQL statement. string queryString = "insert into "; queryString += dbDriver.InsertInEscape(view.TableName) + StringLiterals.SPACE; queryString += Util.InsertInParantheses(fieldNames.ToString()); queryString += StringLiterals.SPACE + "values" + StringLiterals.SPACE; queryString += Util.InsertInParantheses(fieldValues.ToString()); Query insertQuery = dbDriver.CreateQuery(queryString); if (hasData) { dbDriver.ExecuteNonQuery(insertQuery); } } finally { } }
/// <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> /// Builds the command text /// </summary> protected override void GenerateCommand() { Configuration config = Configuration.GetNewInstance(); KeyValuePairCollection kvPairs = new KeyValuePairCollection(); kvPairs.Delimiter = CharLiterals.SPACE; if ((cmbYesAs.Text != config.Settings.RepresentationOfYes) || (hasYesAsChanged)) { kvPairs.Add(new KeyValuePair(ShortHands.YES, Util.InsertInDoubleQuotes(cmbYesAs.Text))); } if ((cmbNoAs.Text != config.Settings.RepresentationOfNo) || (hasNoAsChanged)) { kvPairs.Add(new KeyValuePair(ShortHands.NO, Util.InsertInDoubleQuotes(cmbNoAs.Text))); } if ((cmbMissingAs.Text != config.Settings.RepresentationOfMissing) || (hasMissingAsChanged)) { kvPairs.Add(new KeyValuePair(ShortHands.MISSING, Util.InsertInDoubleQuotes(cmbMissingAs.Text))); } if ((cbxGraphics.Checked != config.Settings.ShowGraphics) || (hasShowGraphicChanged)) { kvPairs.Add(new KeyValuePair(CommandNames.FREQGRAPH, Epi.Util.GetShortHand(cbxGraphics.Checked))); } if ((cbxHyperlinks.Checked != config.Settings.ShowHyperlinks) || (hasShowHyperlinkChanged)) { kvPairs.Add(new KeyValuePair(CommandNames.HYPERLINKS, Epi.Util.GetShortHand(cbxHyperlinks.Checked))); } if ((cbxPercents.Checked != config.Settings.ShowPercents) || (hasShowPercentsChanged)) { kvPairs.Add(new KeyValuePair(CommandNames.PERCENTS, Epi.Util.GetShortHand(cbxPercents.Checked))); } if ((cbxSelectCriteria.Checked != config.Settings.ShowSelection) || (hasShowSelectChanged)) { kvPairs.Add(new KeyValuePair(CommandNames.SELECT, Epi.Util.GetShortHand(cbxSelectCriteria.Checked))); } if ((cbxShowPrompt.Checked != config.Settings.ShowCompletePrompt) || (hasShowPromptChanged)) { kvPairs.Add(new KeyValuePair(CommandNames.SHOWPROMPTS, Epi.Util.GetShortHand(cbxShowPrompt.Checked))); } if ((cbxTablesOutput.Checked != config.Settings.ShowTables) || (hasShowTablesChanged)) { kvPairs.Add(new KeyValuePair(CommandNames.TABLES, Epi.Util.GetShortHand(cbxTablesOutput.Checked))); } if ((cbxIncludeMissing.Checked != config.Settings.IncludeMissingValues) || (hasIncludeMissingChanged)) { kvPairs.Add(new KeyValuePair(CommandNames.MISSING, Epi.Util.GetShortHand(cbxIncludeMissing.Checked))); } if (hasStatisticsLevelChanged) { StatisticsLevel levelIdSelected = (StatisticsLevel)short.Parse(WinUtil.GetSelectedRadioButton(gbxStatistics).Tag.ToString()); string levelTagSelected = AppData.Instance.GetStatisticsLevelById(levelIdSelected).Tag; kvPairs.Add(new KeyValuePair(CommandNames.STATISTICS, levelTagSelected)); } if (hasProcessRecordsChanged) { RecordProcessingScope scopeIdSelected = (RecordProcessingScope)short.Parse(WinUtil.GetSelectedRadioButton(gbxProcessRecords).Tag.ToString()); string scopeTagSelected = AppData.Instance.GetRecordProcessessingScopeById(scopeIdSelected).Tag; kvPairs.Add(new KeyValuePair(CommandNames.PROCESS, scopeTagSelected)); } WordBuilder command = new WordBuilder(); //Generate command only if there are key value pairs if (kvPairs.Count > 0) { if (!this.isDialogMode) { command.Append(CommandNames.SET); } command.Append(kvPairs.ToString()); if (!this.isDialogMode) { command.Append(" END-SET\n"); } this.CommandText = command.ToString(); } else { this.CommandText = string.Empty; } }
/// <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> /// 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> /// Processes all of the fields on a given form, page-by-page, except for the fields on the base table. /// </summary> /// <param name="sourceView">The source form</param> /// <param name="destinationView">The destination form</param> /// <param name="destinationGUIDList">The list of GUIDs that exist in the destination</param> private void ProcessPages(View sourceView, View destinationView, List<string> destinationGUIDList) { for (int i = 0; i < sourceView.Pages.Count; i++) { sourceView.LoadFirstRecord(); OnAddStatusMessage(string.Format(ImportExportSharedStrings.PROCESSING_PAGE, (i + 1).ToString(), sourceView.Pages.Count.ToString())); int recordsInserted = 0; int recordsUpdated = 0; Page sourcePage = sourceView.Pages[i]; Page destinationPage = destinationView.Pages[i]; try { List<string> fieldsToSkip = new List<string>(); foreach (Field sourceField in sourceView.Fields) { bool found = false; foreach (Field destinationField in destinationView.Fields) { if (destinationField.Name.ToLower().Equals(sourceField.Name.ToLower())) { found = true; } } if (!found) { fieldsToSkip.Add(sourceField.Name); } } if (ColumnsToNull != null && ColumnsToNull.ContainsKey(sourceView.Name)) { List<string> toNull = ColumnsToNull[sourceView.Name]; foreach (string s in toNull) { if (!fieldsToSkip.Contains(s)) { fieldsToSkip.Add(s); } } } IDataReader sourceReader = sourceProjectDataDriver.GetTableDataReader(sourcePage.TableName); while (sourceReader.Read()) { //if (importWorker.CancellationPending) //{ // this.BeginInvoke(new SetStatusDelegate(AddStatusMessage), "Import cancelled."); // return; //} if (OnCheckForCancellation()) { OnAddStatusMessage(ImportExportSharedStrings.IMPORT_CANCELLED); sourceReader.Close(); sourceReader.Dispose(); return; } WordBuilder fieldNames = new WordBuilder(StringLiterals.COMMA); WordBuilder fieldValues = new WordBuilder(StringLiterals.COMMA); List<QueryParameter> fieldValueParams = new List<QueryParameter>(); string GUID = sourceReader["GlobalRecordId"].ToString(); if (sourceGUIDs != null && !sourceGUIDs.Contains(GUID)) { continue; } if (destinationGUIDList.Contains(GUID) && update) { // UPDATE matching records string updateHeader = string.Empty; string whereClause = string.Empty; fieldValueParams = new List<QueryParameter>(); StringBuilder sb = new StringBuilder(); int columnIndex = 0; // Build the Update statement which will be reused sb.Append(SqlKeyWords.UPDATE); sb.Append(StringLiterals.SPACE); sb.Append(destinationProjectDataDriver.InsertInEscape(destinationPage.TableName)); sb.Append(StringLiterals.SPACE); sb.Append(SqlKeyWords.SET); sb.Append(StringLiterals.SPACE); updateHeader = sb.ToString(); sb.Remove(0, sb.ToString().Length); // Build the WHERE caluse which will be reused sb.Append(SqlKeyWords.WHERE); sb.Append(StringLiterals.SPACE); sb.Append(destinationProjectDataDriver.InsertInEscape(ColumnNames.GLOBAL_RECORD_ID)); sb.Append(StringLiterals.EQUAL); sb.Append("'"); sb.Append(GUID); sb.Append("'"); whereClause = sb.ToString(); sb.Remove(0, sb.ToString().Length); int fieldsInQuery = 0; // Now build the field update statements in 100 field chunks foreach (RenderableField renderableField in sourcePage.Fields) { if (renderableField is GridField || renderableField is GroupField || renderableField is ImageField || fieldsToSkip.Contains(renderableField.Name)) // TODO: Someday, allow image fields { continue; } else if (renderableField is IDataField) { IDataField dataField = (IDataField)renderableField; if (dataField.FieldType != MetaFieldType.UniqueKey && dataField is RenderableField) { columnIndex += 1; //if (dataField.CurrentRecordValueObject == null) if (sourceReader[renderableField.Name] == DBNull.Value || string.IsNullOrEmpty(sourceReader[renderableField.Name].ToString())) { //sb.Append(SqlKeyWords.NULL); } else { switch (dataField.FieldType) { case MetaFieldType.Date: case MetaFieldType.DateTime: case MetaFieldType.Time: fieldValueParams.Add(new QueryParameter("@" + renderableField.Name, DbType.DateTime, Convert.ToDateTime(sourceReader[renderableField.Name]))); break; case MetaFieldType.Checkbox: fieldValueParams.Add(new QueryParameter("@" + renderableField.Name, DbType.Boolean, Convert.ToBoolean(sourceReader[renderableField.Name]))); break; case MetaFieldType.CommentLegal: case MetaFieldType.LegalValues: case MetaFieldType.Codes: case MetaFieldType.Text: case MetaFieldType.TextUppercase: case MetaFieldType.PhoneNumber: case MetaFieldType.UniqueRowId: case MetaFieldType.ForeignKey: case MetaFieldType.GlobalRecordId: case MetaFieldType.Multiline: fieldValueParams.Add(new QueryParameter("@" + renderableField.Name, DbType.String, sourceReader[renderableField.Name])); break; case MetaFieldType.Number: case MetaFieldType.RecStatus: case MetaFieldType.YesNo: fieldValueParams.Add(new QueryParameter("@" + renderableField.Name, DbType.Single, sourceReader[renderableField.Name])); break; case MetaFieldType.GUID: fieldValueParams.Add(new QueryParameter("@" + renderableField.Name, DbType.Guid, sourceReader[renderableField.Name])); break; case MetaFieldType.Option: if (optionFieldsAsStrings.Contains(renderableField.Name)) { fieldValueParams.Add(new QueryParameter("@" + renderableField.Name, DbType.String, sourceReader[renderableField.Name])); } else { fieldValueParams.Add(new QueryParameter("@" + renderableField.Name, DbType.Int16, sourceReader[renderableField.Name])); } break; case MetaFieldType.Image: OnAddStatusMessage(string.Format(ImportExportSharedStrings.WARNING_FIELD_NOT_IMPORTED, renderableField.Name)); continue; default: throw new ApplicationException(ImportExportSharedStrings.UNRECOGNIZED_FIELD_TYPE); } sb.Append(StringLiterals.LEFT_SQUARE_BRACKET); sb.Append(((Epi.INamedObject)dataField).Name); sb.Append(StringLiterals.RIGHT_SQUARE_BRACKET); sb.Append(StringLiterals.EQUAL); sb.Append(StringLiterals.COMMERCIAL_AT); sb.Append(((Epi.INamedObject)dataField).Name); sb.Append(StringLiterals.COMMA); } } if ((columnIndex % 100) == 0 && columnIndex > 0) { if (sb.ToString().LastIndexOf(StringLiterals.COMMA).Equals(sb.ToString().Length - 1)) { sb.Remove(sb.ToString().LastIndexOf(StringLiterals.COMMA), 1); } Query updateQuery = destinationProjectDataDriver.CreateQuery(updateHeader + StringLiterals.SPACE + sb.ToString() + StringLiterals.SPACE + whereClause); updateQuery.Parameters = fieldValueParams; destinationProjectDataDriver.ExecuteNonQuery(updateQuery); columnIndex = 0; sb.Remove(0, sb.ToString().Length); fieldValueParams.Clear(); } } fieldsInQuery++; } if (fieldsInQuery == 0) { continue; } if (sb.Length > 0) { if (sb.ToString().LastIndexOf(StringLiterals.COMMA).Equals(sb.ToString().Length - 1)) { int startIndex = sb.ToString().LastIndexOf(StringLiterals.COMMA); if (startIndex >= 0) { sb.Remove(startIndex, 1); } } Query updateQuery = destinationProjectDataDriver.CreateQuery(updateHeader + StringLiterals.SPACE + sb.ToString() + StringLiterals.SPACE + whereClause); updateQuery.Parameters = fieldValueParams; destinationProjectDataDriver.ExecuteNonQuery(updateQuery); columnIndex = 0; sb.Remove(0, sb.ToString().Length); fieldValueParams.Clear(); } recordsUpdated++; } else if (!destinationGUIDList.Contains(GUID) && append) { fieldNames.Append("GlobalRecordId"); fieldValues.Append("@GlobalRecordId"); fieldValueParams.Add(new QueryParameter("@GlobalRecordId", DbType.String, GUID)); int fieldsInQuery = 0; // INSERT unmatched records foreach (RenderableField renderableField in sourcePage.Fields) { if (renderableField is GridField || renderableField is GroupField || fieldsToSkip.Contains(renderableField.Name)) { continue; } else if (renderableField is IDataField) { IDataField dataField = (IDataField)renderableField; if (dataField is UniqueKeyField) { continue; } else { if (sourceReader[renderableField.Name] == DBNull.Value || string.IsNullOrEmpty(sourceReader[renderableField.Name].ToString())) //if (dataField.CurrentRecordValueObject == null) { //fieldValues.Append(" null "); // TODO: Check to make sure we shouldn't be using this } else { String fieldName = ((Epi.INamedObject)dataField).Name; //fieldValueParams.Add(dataField.CurrentRecordValueAsQueryParameter); switch (dataField.FieldType) { case MetaFieldType.Date: case MetaFieldType.DateTime: case MetaFieldType.Time: fieldValueParams.Add(new QueryParameter("@" + fieldName, DbType.DateTime, Convert.ToDateTime(sourceReader[fieldName]))); break; case MetaFieldType.Checkbox: fieldValueParams.Add(new QueryParameter("@" + fieldName, DbType.Boolean, Convert.ToBoolean(sourceReader[fieldName]))); break; case MetaFieldType.CommentLegal: case MetaFieldType.LegalValues: case MetaFieldType.Codes: case MetaFieldType.Text: case MetaFieldType.TextUppercase: case MetaFieldType.PhoneNumber: case MetaFieldType.UniqueRowId: case MetaFieldType.ForeignKey: case MetaFieldType.GlobalRecordId: case MetaFieldType.Multiline: fieldValueParams.Add(new QueryParameter("@" + fieldName, DbType.String, sourceReader[fieldName])); break; case MetaFieldType.Number: case MetaFieldType.YesNo: case MetaFieldType.RecStatus: fieldValueParams.Add(new QueryParameter("@" + fieldName, DbType.Single, sourceReader[fieldName])); break; case MetaFieldType.GUID: fieldValueParams.Add(new QueryParameter("@" + fieldName, DbType.Guid, sourceReader[fieldName])); break; case MetaFieldType.Option: if (optionFieldsAsStrings.Contains(renderableField.Name)) { fieldValueParams.Add(new QueryParameter("@" + renderableField.Name, DbType.String, sourceReader[fieldName])); } else { fieldValueParams.Add(new QueryParameter("@" + renderableField.Name, DbType.Int16, sourceReader[fieldName])); } break; case MetaFieldType.Image: OnAddStatusMessage(string.Format(ImportExportSharedStrings.WARNING_FIELD_NOT_IMPORTED, renderableField.Name)); continue; default: throw new ApplicationException(ImportExportSharedStrings.UNRECOGNIZED_FIELD_TYPE); } fieldNames.Append(destinationProjectDataDriver.InsertInEscape(((Epi.INamedObject)dataField).Name)); fieldValues.Append("@" + fieldName); } } } fieldsInQuery++; } if (fieldsInQuery == 0) { continue; } // Concatenate the query clauses into one SQL statement. StringBuilder sb = new StringBuilder(); sb.Append(" insert into "); sb.Append(destinationProjectDataDriver.InsertInEscape(destinationPage.TableName)); sb.Append(StringLiterals.SPACE); sb.Append(Util.InsertInParantheses(fieldNames.ToString())); sb.Append(" values ("); sb.Append(fieldValues.ToString()); sb.Append(") "); Query insertQuery = destinationProjectDataDriver.CreateQuery(sb.ToString()); insertQuery.Parameters = fieldValueParams; destinationProjectDataDriver.ExecuteNonQuery(insertQuery); recordsInserted++; } //this.BeginInvoke(new SetProgressBarDelegate(IncrementProgressBarValue), 1); OnSetProgress(1); } sourceReader.Close(); sourceReader.Dispose(); } catch (Exception ex) { OnAddStatusMessage(string.Format(ImportExportSharedStrings.ERROR_WITH_MESSAGE, ex.Message)); } finally { } if (update && append) { OnAddStatusMessage(string.Format(ImportExportSharedStrings.IMPORT_PAGE_UPDATED_AND_APPENDED, destinationPage.Name, recordsInserted.ToString(), recordsUpdated.ToString())); } else if (update && !append) { OnAddStatusMessage(string.Format(ImportExportSharedStrings.IMPORT_PAGE_UPDATED, destinationPage.Name, recordsUpdated.ToString())); } else if (!update && append) { OnAddStatusMessage(string.Format(ImportExportSharedStrings.IMPORT_PAGE_APPENDED, destinationPage.Name, recordsInserted.ToString())); } } }
/// <summary> /// /// </summary> /// <param name="db"></param> /// <param name="dataTable"></param> /// <param name="dRow"></param> /// <param name="sTableName"></param> /// <returns></returns> protected Query CreateInsertQuery(IDbDriver db, DataTable dataTable, DataRow dRow, string sTableName) { Query result; try { // Begin Insert clause StringBuilder sbInsert = new StringBuilder(); WordBuilder fieldNames = new WordBuilder(StringLiterals.COMMA); WordBuilder fieldValues = new WordBuilder(StringLiterals.COMMA); sbInsert.Append("Insert Into "); sbInsert.Append(Util.InsertIn(dbDriver.InsertInEscape(sTableName), StringLiterals.SPACE)); foreach (DataColumn column in dataTable.Columns) { fieldNames.Append(dbDriver.InsertInEscape(column.ColumnName)); if (column.DataType.IsPrimitive) { if (dRow[column.ColumnName].ToString().Length == 0) { fieldValues.Append("null"); } else if (column.DataType.Name == "Boolean") { if (dRow[column.ColumnName] != null) { if (dRow[column.ColumnName].ToString() == "True") { fieldValues.Append("1"); } else { fieldValues.Append("0"); } } else { fieldValues.Append("null"); } } else { fieldValues.Append(dRow[column.ColumnName].ToString()); } } else { if (column.DataType.Name == "Boolean") { if (dRow[column.ColumnName] != null) { if (dRow[column.ColumnName].ToString() == "True") { fieldValues.Append("1"); } else { fieldValues.Append("0"); } } else { fieldValues.Append("null"); } } else { fieldValues.Append(Util.InsertInSingleQuotes(dRow[column.ColumnName].ToString())); } } } sbInsert.Append(Util.InsertInParantheses(fieldNames.ToString())); sbInsert.Append(Util.InsertIn("Values", StringLiterals.SPACE)); sbInsert.Append(Util.InsertInParantheses(fieldValues.ToString())); // End Values clause result = dbDriver.CreateQuery(sbInsert.ToString()); } catch (Exception ex) { throw new GeneralException("Could not create insert query", ex); } return result; }
/// <summary> /// Processes a form's base table /// </summary> /// <param name="sourceView">The source form</param> /// <param name="destinationView">The destination form</param> /// <param name="destinationGUIDList">The list of GUIDs that exist in the destination</param> private void ProcessBaseTable(View sourceView, View destinationView, List<string> destinationGUIDList) { sourceView.LoadFirstRecord(); OnAddStatusMessage(ImportExportSharedStrings.PROCESSING_BASE_TABLE); int recordsInserted = 0; int recordsUpdated = 0; string sourceTable = sourceView.TableName; string destinationTable = destinationView.TableName; optionFieldsAsStrings = new List<string>(); // Check for string-based option fields. foreach (Field f in destinationView.Fields) { if (f is OptionField) { OptionField optionField = f as OptionField; if (optionField != null) { DataTable dt = destinationProjectDataDriver.GetTopTwoTable(optionField.Page.TableName); if (dt.Columns[optionField.Name].DataType.ToString().Equals("System.String", StringComparison.OrdinalIgnoreCase)) { optionFieldsAsStrings.Add(f.Name); } } } } try { List<string> newGUIDList = new List<string>(); IDataReader sourceReader = sourceProjectDataDriver.GetTableDataReader(sourceView.TableName); while (sourceReader.Read()) { object recordStatus = sourceReader["RECSTATUS"]; QueryParameter paramRecordStatus = new QueryParameter("@RECSTATUS", DbType.Int32, recordStatus); //if (importWorker.CancellationPending) //{ // this.BeginInvoke(new SetStatusDelegate(AddStatusMessage), "Import cancelled."); // return; //} if (OnCheckForCancellation()) { OnAddStatusMessage(ImportExportSharedStrings.IMPORT_CANCELLED); sourceReader.Close(); sourceReader.Dispose(); sourceReader = null; return; } WordBuilder fieldNames = new WordBuilder(StringLiterals.COMMA); WordBuilder fieldValues = new WordBuilder(StringLiterals.COMMA); List<QueryParameter> fieldValueParams = new List<QueryParameter>(); string GUID = sourceReader["GlobalRecordId"].ToString(); if (sourceGUIDs != null && !sourceGUIDs.Contains(GUID)) { continue; } fieldNames.Append("GlobalRecordId"); fieldValues.Append("@GlobalRecordId"); string FKEY = sourceReader["FKEY"].ToString(); QueryParameter paramFkey = new QueryParameter("@FKEY", DbType.String, FKEY); // don't add this yet QueryParameter paramGUID = new QueryParameter("@GlobalRecordId", DbType.String, GUID); fieldValueParams.Add(paramGUID); if (destinationGUIDList.Contains(GUID)) { if (update) { // UPDATE matching records string updateHeader = string.Empty; string whereClause = string.Empty; fieldValueParams = new List<QueryParameter>(); StringBuilder sb = new StringBuilder(); // Build the Update statement which will be reused sb.Append(SqlKeyWords.UPDATE); sb.Append(StringLiterals.SPACE); sb.Append(destinationProjectDataDriver.InsertInEscape(destinationTable)); sb.Append(StringLiterals.SPACE); sb.Append(SqlKeyWords.SET); sb.Append(StringLiterals.SPACE); updateHeader = sb.ToString(); sb.Remove(0, sb.ToString().Length); // Build the WHERE caluse which will be reused sb.Append(SqlKeyWords.WHERE); sb.Append(StringLiterals.SPACE); sb.Append(destinationProjectDataDriver.InsertInEscape(ColumnNames.GLOBAL_RECORD_ID)); sb.Append(StringLiterals.EQUAL); sb.Append("'"); sb.Append(GUID); sb.Append("'"); whereClause = sb.ToString(); sb.Remove(0, sb.ToString().Length); //if (sourceView.ForeignKeyFieldExists) if (!string.IsNullOrEmpty(FKEY)) { sb.Append(StringLiterals.LEFT_SQUARE_BRACKET); sb.Append("FKEY"); sb.Append(StringLiterals.RIGHT_SQUARE_BRACKET); sb.Append(StringLiterals.EQUAL); sb.Append(StringLiterals.COMMERCIAL_AT); sb.Append("FKEY"); fieldValueParams.Add(paramFkey); Query updateQuery = destinationProjectDataDriver.CreateQuery(updateHeader + StringLiterals.SPACE + sb.ToString() + StringLiterals.SPACE + whereClause); updateQuery.Parameters = fieldValueParams; destinationProjectDataDriver.ExecuteNonQuery(updateQuery); sb.Remove(0, sb.ToString().Length); fieldValueParams.Clear(); recordsUpdated++; } } } else { if (append) { if (!string.IsNullOrEmpty(FKEY)) { fieldNames.Append("FKEY"); fieldValues.Append("@FKEY"); fieldValueParams.Add(paramFkey); } fieldNames.Append("RECSTATUS"); fieldValues.Append("@RECSTATUS"); fieldValueParams.Add(paramRecordStatus); // Concatenate the query clauses into one SQL statement. StringBuilder sb = new StringBuilder(); sb.Append(" insert into "); sb.Append(destinationProjectDataDriver.InsertInEscape(destinationTable)); sb.Append(StringLiterals.SPACE); sb.Append(Util.InsertInParantheses(fieldNames.ToString())); sb.Append(" values ("); sb.Append(fieldValues.ToString()); sb.Append(") "); Query insertQuery = destinationProjectDataDriver.CreateQuery(sb.ToString()); insertQuery.Parameters = fieldValueParams; destinationProjectDataDriver.ExecuteNonQuery(insertQuery); recordsInserted++; } } OnSetProgress(1); } sourceReader.Close(); sourceReader.Dispose(); sourceReader = null; } catch (Exception ex) { OnAddStatusMessage(string.Format(ImportExportSharedStrings.ERROR_WITH_MESSAGE, ex.Message)); } finally { } if (update && append) { OnAddStatusMessage(string.Format(ImportExportSharedStrings.IMPORT_TABLE_UPDATED_AND_APPENDED, destinationTable, recordsInserted.ToString(), recordsUpdated.ToString())); } else if (update && !append) { OnAddStatusMessage(string.Format(ImportExportSharedStrings.IMPORT_TABLE_UPDATED, destinationTable, recordsUpdated.ToString())); } else if (!update && append) { OnAddStatusMessage(string.Format(ImportExportSharedStrings.IMPORT_TABLE_APPENDED, destinationTable, recordsInserted.ToString())); } }
/// <summary> /// Inserts a grid record if update grid record fails as attempted by SaveGridRecord(). /// </summary> /// <param name="recordID">Id of current Record.</param> /// <param name="field">GridField object.</param> /// <param name="dRow">DataRow of DataTable to save.</param> /// <param name="sTableName">View name plus field name</param> /// <returns>Number rows affected.</returns> private int InsertGridRecord(string globalRecordId, string gridLevelGlobalRecordId, string uniqueRowId, GridField field, DataRow dRow, string sTableName) { try { StringBuilder sbInsert = new StringBuilder(); WordBuilder fieldNames = new WordBuilder(StringLiterals.COMMA); WordBuilder fieldValues = new WordBuilder(StringLiterals.COMMA); sbInsert.Append("insert into "); sbInsert.Append(Util.InsertIn(sTableName, StringLiterals.SPACE)); foreach (GridColumnBase column in field.Columns) { if (column.Name != ColumnNames.UNIQUE_KEY) { fieldNames.Append(dbDriver.InsertInEscape(column.Name)); } } sbInsert.Append(Util.InsertInParantheses(fieldNames.ToString())); sbInsert.Append(Util.InsertIn("values", StringLiterals.SPACE)); foreach (GridColumnBase column in field.Columns) { if (column.Name != ColumnNames.UNIQUE_KEY) { if (column is NumberColumn) { if (dRow[column.Name].ToString().Length == 0) { fieldValues.Append("null"); } else { fieldValues.Append(dRow[column.Name].ToString()); } } else { if (column.Name == ColumnNames.FOREIGN_KEY) { fieldValues.Append(Util.InsertInSingleQuotes(globalRecordId)); } else if (column.Name == ColumnNames.GLOBAL_RECORD_ID) { fieldValues.Append(Util.InsertInSingleQuotes(gridLevelGlobalRecordId)); } else if (column.Name == ColumnNames.UNIQUE_ROW_ID) { fieldValues.Append(Util.InsertInSingleQuotes(uniqueRowId)); } else if (column is TimeColumn || column is DateColumn || column is DateTimeColumn ) { string valueString = "null"; if(string.IsNullOrEmpty(dRow[column.Name].ToString()) == false) { valueString = Util.InsertInSingleQuotes(dRow[column.Name].ToString()); } fieldValues.Append(valueString); } else if (column is CheckboxColumn) { string valueString = "0"; if (dRow[column.Name].ToString().ToLower().Contains("true")) { valueString = "1"; } fieldValues.Append(valueString); } else if (column is YesNoColumn) { string valueString = "null"; if (dRow[column.Name].ToString().Contains("1")) { valueString = "1"; } else if (dRow[column.Name].ToString().Contains("0")) { valueString = "0"; } fieldValues.Append(valueString); } else if (column.Name == ColumnNames.REC_STATUS) { fieldValues.Append("1"); } else { fieldValues.Append(Util.InsertInSingleQuotes(dRow[column.Name].ToString())); } } } } sbInsert.Append(Util.InsertInParantheses(fieldValues.ToString())); Query insertQuery = dbDriver.CreateQuery(sbInsert.ToString()); return dbDriver.ExecuteNonQuery(insertQuery); } catch (Exception ex) { throw new GeneralException("Could not insert grid record", ex); } }
/// <summary> /// Builds the command text /// </summary> protected override void GenerateCommand() { Configuration config = Configuration.GetNewInstance(); KeyValuePairCollection kvPairs = new KeyValuePairCollection(); kvPairs.Delimiter = CharLiterals.SPACE; if ((settingsPanel.RepresentationOfYes != config.Settings.RepresentationOfYes) || (hasYesAsChanged)) { kvPairs.Add(new KeyValuePair(ShortHands.YES, Util.InsertInDoubleQuotes(settingsPanel.RepresentationOfYes))); } if ((settingsPanel.RepresentationOfNo != config.Settings.RepresentationOfNo) || (hasNoAsChanged)) { kvPairs.Add(new KeyValuePair(ShortHands.NO, Util.InsertInDoubleQuotes(settingsPanel.RepresentationOfNo))); } if ((settingsPanel.RepresentationOfMissing != config.Settings.RepresentationOfMissing) || (hasMissingAsChanged)) { kvPairs.Add(new KeyValuePair(ShortHands.MISSING, Util.InsertInDoubleQuotes(settingsPanel.RepresentationOfMissing))); } if ((settingsPanel.ShowGraphics != config.Settings.ShowGraphics) || (hasShowGraphicChanged)) { kvPairs.Add(new KeyValuePair(CommandNames.FREQGRAPH, Epi.Util.GetShortHand(settingsPanel.ShowGraphics))); } if ((settingsPanel.ShowHyperlinks != config.Settings.ShowHyperlinks) || (hasShowHyperlinkChanged)) { kvPairs.Add(new KeyValuePair(CommandNames.HYPERLINKS, Epi.Util.GetShortHand(settingsPanel.ShowHyperlinks))); } if ((settingsPanel.ShowPercents != config.Settings.ShowPercents) || (hasShowPercentsChanged)) { kvPairs.Add(new KeyValuePair(CommandNames.PERCENTS, Epi.Util.GetShortHand(settingsPanel.ShowPercents))); } if ((settingsPanel.ShowSelectCriteria != config.Settings.ShowSelection) || (hasShowSelectChanged)) { kvPairs.Add(new KeyValuePair(CommandNames.SELECT, Epi.Util.GetShortHand(settingsPanel.ShowSelectCriteria))); } if ((settingsPanel.ShowPrompt != config.Settings.ShowCompletePrompt) || (hasShowPromptChanged)) { kvPairs.Add(new KeyValuePair(CommandNames.SHOWPROMPTS, Epi.Util.GetShortHand(settingsPanel.ShowPrompt))); } if ((settingsPanel.ShowTablesOutput != config.Settings.ShowTables) || (hasShowTablesChanged)) { kvPairs.Add(new KeyValuePair(CommandNames.TABLES, Epi.Util.GetShortHand(settingsPanel.ShowTablesOutput))); } if ((settingsPanel.ShowIncludeMissing != config.Settings.IncludeMissingValues) || (hasIncludeMissingChanged)) { kvPairs.Add(new KeyValuePair(CommandNames.MISSING, Epi.Util.GetShortHand(settingsPanel.ShowIncludeMissing))); } if (hasStatisticsLevelChanged) { RadioButton rbSelected = settingsPanel.StatisticLevel; StatisticsLevel levelIdSelected = (StatisticsLevel)short.Parse(rbSelected.Tag.ToString()); string levelTagSelected = AppData.Instance.GetStatisticsLevelById(levelIdSelected).Tag; kvPairs.Add(new KeyValuePair(CommandNames.STATISTICS, levelTagSelected)); } if (hasProcessRecordsChanged) { RadioButton rbSelected = settingsPanel.ProcessRecords; RecordProcessingScope scopeIdSelected = (RecordProcessingScope)short.Parse(rbSelected.Tag.ToString()); string scopeTagSelected = AppData.Instance.GetRecordProcessessingScopeById(scopeIdSelected).Tag; kvPairs.Add(new KeyValuePair(CommandNames.PROCESS, scopeTagSelected)); } WordBuilder command = new WordBuilder(); //Generate command only if there are key value pairs if (kvPairs.Count > 0) { if (!this.isDialogMode) { command.Append(CommandNames.SET); } command.Append(kvPairs.ToString()); if (!this.isDialogMode) { command.Append(" END-SET\n"); } this.CommandText = command.ToString(); } else { this.CommandText = string.Empty; } }
/// <summary> /// Get Grid field Table Data for current view record. /// </summary> /// <param name="view">Current view of project.</param> /// <param name="field">Grid field</param> /// <returns>Grid field table contents.</returns> public System.Data.DataTable GetGridTableData(View view, GridField field) { StringBuilder sbSelect = new StringBuilder(); WordBuilder wbSelect = new WordBuilder(StringLiterals.COMMA); // Begin Select clause foreach (GridColumnBase column in ((GridField)field).Columns) { wbSelect.Append(dbDriver.InsertInEscape(column.Name)); } sbSelect.Append("select"); sbSelect.Append(StringLiterals.SPACE); sbSelect.Append(wbSelect.ToString()); // End Select clause // Begin From clause sbSelect.Append(Util.InsertIn("from", StringLiterals.SPACE)); sbSelect.Append(dbDriver.InsertInEscape(view.TableName + field.Page.Id.ToString() + field.Name)); sbSelect.Append(Util.InsertIn("where", StringLiterals.SPACE)); sbSelect.Append(dbDriver.InsertInEscape(ColumnNames.FOREIGN_KEY) + StringLiterals.EQUAL + "'" + view.CurrentGlobalRecordId + "'"); // End From clause Data.Query selectQuery = dbDriver.CreateQuery(sbSelect.ToString()); return dbDriver.Select(selectQuery); }
/// <summary> /// Gets all the programs saved in a project /// </summary> /// <returns>DataTable containing a list of all programs in a project</returns> public virtual DataTable GetPgms() { try { WordBuilder columnNames = new WordBuilder(StringLiterals.COMMA); columnNames.Append("ProgramId"); columnNames.Append("Name"); columnNames.Append("Content"); columnNames.Append("Comment"); columnNames.Append("DateCreated"); columnNames.Append("DateModified"); columnNames.Append("Author"); Query query = db.CreateQuery("select" + Util.InsertIn(columnNames.ToString(), StringLiterals.SPACE) + Util.InsertIn("from", StringLiterals.SPACE) + db.InsertInEscape("metaPrograms")); return db.Select(query); } catch (Exception ex) { throw new GeneralException("Could not retrieve programs", ex); } }
protected override bool CreateNewRow(IDbConnection conn, View form, XElement record, string guid, string fkey = "", string recStatus = "1", string firstSaveId = "", string lastSaveId = "", DateTime?firstSaveTime = null, DateTime?lastSaveTime = null) { SqlConnection connection = conn as SqlConnection; IDbDriver db = Project.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(recStatus)) { fields.Append("[RecStatus]"); } if (!String.IsNullOrEmpty(firstSaveId)) { fields.Append("[FirstSaveLogonName]"); } if (!String.IsNullOrEmpty(lastSaveId)) { fields.Append("[LastSaveLogonName]"); } if (firstSaveTime.HasValue) { firstSaveTime = new DateTime(firstSaveTime.Value.Year, firstSaveTime.Value.Month, firstSaveTime.Value.Day, firstSaveTime.Value.Hour, firstSaveTime.Value.Minute, firstSaveTime.Value.Second); fields.Append("[FirstSaveTime]"); } if (lastSaveTime.HasValue) { lastSaveTime = new DateTime(lastSaveTime.Value.Year, lastSaveTime.Value.Month, lastSaveTime.Value.Day, lastSaveTime.Value.Hour, lastSaveTime.Value.Minute, lastSaveTime.Value.Second); 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(recStatus)) { values.Append("@RecStatus"); parameters.Add(new QueryParameter("@RecStatus", DbType.Int32, Convert.ToInt32(recStatus))); } 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; using (SqlTransaction transaction = connection.BeginTransaction("SampleTransaction")) { try { using (IDbCommand baseTableCommand = GetCommand(insertQuery.SqlStatement, conn, insertQuery.Parameters)) { baseTableCommand.Transaction = transaction; object baseObj = baseTableCommand.ExecuteNonQuery(); } //foreach (Page page in form.Pages) //{ // sb = new StringBuilder(); // sb.Append(" insert into "); // sb.Append(db.InsertInEscape(page.TableName)); // sb.Append(StringLiterals.SPACE); // sb.Append(StringLiterals.SPACE); // sb.Append("([GlobalRecordId])"); // sb.Append(" values ("); // sb.Append("'" + guid + "'"); // sb.Append(") "); // insertQuery = db.CreateQuery(sb.ToString()); // using (IDbCommand pageTableCommand = GetCommand(insertQuery.SqlStatement, conn, insertQuery.Parameters)) // { // pageTableCommand.Transaction = transaction; // object pageObj = pageTableCommand.ExecuteNonQuery(); // } //} foreach (Page page in form.Pages) { WordBuilder wbFieldNames = new WordBuilder(", "); WordBuilder wbParamNames = new WordBuilder(", "); List <QueryParameter> pageInsertParameters = new List <QueryParameter>(); foreach (RenderableField field in page.Fields) { if (field is IDataField && record.Element(field.Name) != null) { wbFieldNames.Add(field.Name); wbParamNames.Add("@" + field.Name); pageInsertParameters.Add( GetQueryParameterForField(field, FormatFieldData(form, field.Name, record.Element(field.Name).Value), form, page)); } } wbFieldNames.Add("GlobalRecordId"); wbParamNames.Add("@GlobalRecordId"); pageInsertParameters.Add(new QueryParameter("@GlobalRecordId", DbType.String, guid)); Query inserteQuery = db.CreateQuery("INSERT INTO " + page.TableName + " (" + wbFieldNames.ToString() + ") VALUES (" + wbParamNames.ToString() + ")"); foreach (QueryParameter parameter in pageInsertParameters) { inserteQuery.Parameters.Add(parameter); } using (IDbCommand pageTableCommand = GetCommand(inserteQuery.SqlStatement, conn, inserteQuery.Parameters)) { pageTableCommand.Transaction = transaction; object obj = pageTableCommand.ExecuteNonQuery(); } } transaction.Commit(); } catch (Exception ex) { Epi.Logger.Log(String.Format(DateTime.Now + ": " + "Commit Exception Type: {0}", ex.GetType())); Epi.Logger.Log(String.Format(DateTime.Now + ": " + "Commit Exception Message: {0}", ex.Message)); try { transaction.Rollback(); } catch (Exception ex2) { Epi.Logger.Log(String.Format(DateTime.Now + ": " + "Rollback Exception Type: {0}", ex2.GetType())); Epi.Logger.Log(String.Format(DateTime.Now + ": " + "Rollback Exception Message: {0}", ex2.Message)); } return(false); } } return(true); }
/// <summary> /// Generates the command text /// </summary> protected override void GenerateCommand() { WordBuilder command = new WordBuilder(); command.Append(CommandNames.GRAPH); if (listBoxVariables.Items.Count > 0) { foreach (string item in listBoxVariables.Items) { command.Append(FieldNameNeedsBrackets(item) ? Util.InsertInSquareBrackets(item) : item); } } else { command.Append(FieldNameNeedsBrackets(comboBoxMainVariable.Text) ? Util.InsertInSquareBrackets(comboBoxMainVariable.Text) : comboBoxMainVariable.Text); } if (comboBoxBarOfEachValueOf.Text != string.Empty) { command.Append(string.Format("* {0}", FieldNameNeedsBrackets(comboBoxBarOfEachValueOf.Text) ? Util.InsertInSquareBrackets(comboBoxBarOfEachValueOf.Text) : comboBoxBarOfEachValueOf.Text)); } if (comboBoxGraphType.Text != string.Empty) { if (comboBoxGraphType.Text == "EAR (Early Aberration Reporting)") { command.Append("GRAPHTYPE=\"EAR\""); } else { command.Append(string.Format("GRAPHTYPE=\"{0}\"", comboBoxGraphType.Text)); } } if (comboBoxStrataVar.Text != string.Empty) { command.Append(string.Format("STRATAVAR={0}", FieldNameNeedsBrackets(comboBoxStrataVar.Text) ? Util.InsertInSquareBrackets(comboBoxStrataVar.Text) : comboBoxStrataVar.Text)); } if (comboBoxWeightVar.Text != string.Empty) { string weightVar = FieldNameNeedsBrackets(comboBoxWeightVar.Text) ? Util.InsertInSquareBrackets(comboBoxWeightVar.Text) : comboBoxWeightVar.Text; switch (comboBoxShowValueOf.Text) { case "Average": weightVar = string.Format("AVG({0})", weightVar); break; case "Count": weightVar = string.Format("COUNT({0})", weightVar); break; case "Sum": weightVar = string.Format("SUM({0})", weightVar); break; case "Minimum": weightVar = string.Format("MIN({0})", weightVar); break; case "Maximum": weightVar = string.Format("MAX({0})", weightVar); break; case "Count %": weightVar = string.Format("PERCENT({0})", weightVar); break; case "Sum %": weightVar = string.Format("SUMPCT({0})", weightVar); break; } command.Append(string.Format("WEIGHTVAR={0}", weightVar)); } else { if (comboBoxShowValueOf.Text == "Count %") { command.Append("WEIGHTVAR=PERCENT()"); } } if (textBoxTitle.Text != string.Empty) { command.Append(string.Format("TITLETEXT=\"{0}\"", textBoxTitle.Text)); } if (textBoxXAxisLabel.Text != string.Empty) { command.Append(string.Format("XTITLE=\"{0}\"", textBoxXAxisLabel.Text)); } if (textBoxYAxisLabel.Text != string.Empty) { command.Append(string.Format("YTITLE=\"{0}\"", textBoxYAxisLabel.Text)); } if (comboBoxDateFormat.Text != string.Empty) { command.Append(string.Format("DATEFORMAT=\"{0}\"", comboBoxDateFormat.Text)); } if (comboBoxIntervalType.Text != string.Empty) { command.Append(string.Format("INTERVAL=\"{0} {1}\"", textBoxInterval.Text, comboBoxIntervalType.Text)); } if (startDateTime.Checked) { command.Append(string.Format("STARTFROM=\"{0}\"", startDateTime.Value.ToString())); } CommandText = command.ToString(); }
/// <summary> /// Creates a new blank row for a given form's base table and all of its page tables. /// </summary> /// <param name="gridField">The grid field where the row should be added.</param> /// <param name="guid">The Guid value to use for the row.</param> /// <param name="urid">The unique row id.</param> /// <param name="fkey">The foreign key for the row.</param> protected virtual void CreateNewBlankGridRow(GridField gridField, string guid, string urid, string fkey) { #region Input Validation if (string.IsNullOrEmpty(guid)) { throw new ArgumentNullException("guid"); } if (string.IsNullOrEmpty(urid)) { throw new ArgumentNullException("urid"); } if (string.IsNullOrEmpty(fkey)) { throw new ArgumentNullException("fkey"); } if (gridField == null) { throw new ArgumentNullException("gridField"); } #endregion // Input Validation if (Conn.State != ConnectionState.Open) { Conn.Open(); } IDbDriver db = DestinationProject.CollectedData.GetDatabase(); StringBuilder sb = new StringBuilder(); sb.Append("insert into " + db.InsertInEscape(gridField.TableName) + " "); WordBuilder fields = new WordBuilder(","); fields.Append("[GlobalRecordId], [UniqueRowId], [FKEY]"); sb.Append("(" + fields.ToString() + ") values ("); WordBuilder values = new WordBuilder(","); values.Append("'" + guid + "', '" + urid + "', '" + fkey + "'"); sb.Append(values.ToString()); sb.Append(") "); Epi.Data.Query insertQuery = db.CreateQuery(sb.ToString()); 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> /// Generates a Query object that can be executed to return only the GlobalRecordId values matching the filter conditions. This method can only be used for Epi Info 7 forms. /// </summary> /// <param name="form">The form to process</param> /// <returns>Query</returns> public virtual Query GetGuidSelectQuery(View form) { // pre Contract.Requires(form != null); // post Contract.Ensures(Contract.Result <Query>() != null); Contract.Ensures(!String.IsNullOrEmpty(Contract.Result <Query>().SqlStatement)); // assumes Contract.Assume(DataDriver != null); if (DataDriver == null) { throw new InvalidOperationException(); } if (form == null) { throw new ArgumentNullException("form"); } string baseTableName = "t"; string fromClause = form.FromViewSQL; Contract.Assert(!String.IsNullOrEmpty(fromClause)); WordBuilder columns = new WordBuilder(", "); columns.Append("[" + baseTableName + "].[GlobalRecordId]"); columns.Append("[" + baseTableName + "].[FKEY]"); columns.Append("[" + baseTableName + "].[RECSTATUS]"); if (DataDriver.ColumnExists(baseTableName, "FirstSaveLogonName")) { columns.Append("[" + baseTableName + "].[FirstSaveLogonName]"); columns.Append("[" + baseTableName + "].[FirstSaveTime]"); columns.Append("[" + baseTableName + "].[LastSaveLogonName]"); columns.Append("[" + baseTableName + "].[LastSaveTime]"); } string recStatusComparison = "[" + baseTableName + "].[RECSTATUS] = 1"; if (RecordProcessingScope == Epi.RecordProcessingScope.Both) { recStatusComparison = "[" + baseTableName + "].[RECSTATUS] >= 0"; } else if (RecordProcessingScope == Epi.RecordProcessingScope.Deleted) { recStatusComparison = "[" + baseTableName + "].[RECSTATUS] = 0"; } string fullSql = "SELECT " + columns.ToString() + " " + fromClause + " WHERE " + recStatusComparison + " AND ("; //filterSql.Append(fullSql); string logicalOperatorString = " AND "; if (_conditionJoinType == ConditionJoinTypes.Or) { logicalOperatorString = " OR "; } WordBuilder filterSql = new WordBuilder(logicalOperatorString); foreach (IRowFilterCondition rowFc in _rowFilterConditions) { filterSql.Append(rowFc.Sql); } string fSql = fullSql + " " + filterSql.ToString() + ")"; Query selectQuery = DataDriver.CreateQuery(fSql); Contract.Assert(selectQuery != null); Contract.Assert(!String.IsNullOrEmpty(selectQuery.SqlStatement)); foreach (IRowFilterCondition rowFc in _rowFilterConditions) { selectQuery.Parameters.Add(rowFc.Parameter); } return(selectQuery); }
/// <summary> /// Gets a count of records in a column /// </summary> /// <param name="columnName">Name of the column</param> /// <returns>Number of records in a column</returns> public int GetRecordCount(Rule_Context pContext, string columnName) { #region Input Validation if (string.IsNullOrEmpty(columnName)) { throw new ArgumentNullException("columnName"); } #endregion Input Validation WordBuilder queryBuilder = new WordBuilder(); queryBuilder.Append("select count" + Util.InsertInParantheses(columnName)); queryBuilder.Append(GetSqlStatementPartFrom(pContext)); if (Db.ColumnExists(PrimaryTable.TableName, ColumnNames.REC_STATUS)) { string whereStatement = GetSqlStatementPartWhere(); queryBuilder.Append(whereStatement); } else if (!string.IsNullOrEmpty(selectCriteria)) { string whereClause = " where " + this.selectCriteria; queryBuilder.Append(whereClause); } Query query = Db.CreateQuery(queryBuilder.ToString()); //recast to int before return to remove cast run time error int result = Int32.Parse((Db.ExecuteScalar(query)).ToString()); return result; }