/// <summary> /// Assigns values to all linked fields based on a code table. /// </summary> /// <param name="sender">The control that contains the link references</param> public void FilterList(object sender, System.EventArgs e) { string TestValue = null; if (sender is ListFieldComboBox) { ListFieldComboBox lvcb = (ListFieldComboBox)sender; TestValue = lvcb.SelectedValue.ToString(); } else { TestValue = ((Control)sender).Text; } this.DataSource = null; Epi.Fields.DDListField field = (Epi.Fields.DDListField)Epi.Windows.Enter.PresentationLogic.ControlFactory.Instance.GetAssociatedField(this); string SourceTable = field.SourceTable; string relateField = null; string displayfield = field.TextColumnName; foreach (System.Collections.Generic.KeyValuePair <string, int> kvp in field.pairAssociated) { relateField = kvp.Key; break; } System.Data.DataTable drv = DBReadExecute.GetDataTable(field.GetProject().FilePath, string.Format("Select * from {0} Where {1} Like '{2}%'", SourceTable, relateField, TestValue)); this.Items.Clear(); this.DataSource = drv; this.DisplayMember = displayfield; this.ValueMember = relateField; this.RefreshItems(); }
/// <summary> /// Returns a 2x2 table for the tables command /// </summary> /// <remarks>The cells are numbered as they are in Epi3</remarks> /// <param name="outcome"></param> /// <param name="exposure"></param> /// <returns>DataTable</returns> public DataSet GetDataSet2x2(Rule_Context pContext, string exposure, string outcome) { DataSet ds = null; try { StringBuilder sb = new StringBuilder("select "); sb.Append(exposure).Append(" AS [").Append(ColumnNames.EXPOSURE).Append("], ["); sb.Append(outcome).Append("] AS [").Append(ColumnNames.OUTCOME); sb.Append("], count([").Append(outcome).Append("]) AS [").Append(ColumnNames.COUNT); sb.Append("] "); sb.Append(GetSqlStatementPartFrom(pContext)); //if there is no project, there will not be a column Rec Status if (PrimaryTable.Columns.Contains(ColumnNames.REC_STATUS)) { sb.Append(ExcludeMissing(GetSqlStatementPartWhere(), new string[] { exposure, outcome })); } //a natively read MDB, and SELECT chosen else if (!string.IsNullOrEmpty(selectCriteria)) { string whereClause = " where " + this.selectCriteria; sb.Append(ExcludeMissing(whereClause, new string[] { exposure, outcome })); } sb.Append(" group by [").Append(exposure).Append("], [").Append(outcome); sb.Append("] order by [").Append(exposure); sb.Append("], [").Append(outcome).Append("];"); DataTable Table2x2 = DBReadExecute.GetDataTable(pContext.CurrentRead.File, sb.ToString()); //Query query = Db.CreateQuery(sb.ToString()); //DataTable Table2x2 = Db.Select(query); Table2x2.TableName = "Table2x2"; ds = new DataSet("dsTable2x2"); ds.Tables.Add(Table2x2); //DataTable distinctOutcomes = DistinctColumn(outcome); //distinctOutcomes.TableName = "DistinctOutcomes"; //ds.Tables.Add(distinctOutcomes); } catch (Exception ex) { throw new GeneralException(SharedStrings.UNABLE_CREATE_2X2, ex); } return(ds); }
public void UpdateCurrentRecordNumber() { if (this.mView.CurrentRecordId > 0) { if (!this.RecordNumberMap.ContainsKey(this.mView.CurrentRecordId)) { string relatedViewFilter = string.Empty; if (this.mView.IsRelatedView) { relatedViewFilter = " and "; relatedViewFilter += ColumnNames.FOREIGN_KEY; relatedViewFilter += StringLiterals.EQUAL + "'" + mView.ForeignKeyField.CurrentRecordValueString + "'"; } DataTable DT = DBReadExecute.GetDataTable(this.mView.Project.FilePath, "Select Count(*) From [" + this.mView.TableName + "] Where UniqueKey <= " + this.mView.CurrentRecordId.ToString() + relatedViewFilter); if (DT.Rows.Count > 0) { this.RecordNumberMap.Add(this.mView.CurrentRecordId, int.Parse(DT.Rows[0][0].ToString())); } } } }
/// <summary> /// performs execution of the READ command /// </summary> /// <returns>object</returns> public override object Execute() { object result = null; DataTable outputTable; int recordCount = 0; Context.isReadMode = true; Context.GroupVariableList = new Dictionary <string, List <string> >(StringComparer.OrdinalIgnoreCase); if (File.ToUpperInvariant().StartsWith("CONFIG:")) { string[] datakey = File.Split(':'); string connectionString = null; Configuration config = Configuration.GetNewInstance(); for (int i = 0; i < config.RecentDataSources.Count; i++) { Epi.DataSets.Config.RecentDataSourceRow row = config.RecentDataSources[i]; if (row.Name.ToUpperInvariant() == datakey[1].ToUpperInvariant()) { connectionString = Configuration.Decrypt(row.ConnectionString); break; } } File = connectionString; } if (_isSimpleRead) { if (Context.CurrentProject == null) { if (Context.CurrentRead == null) { throw new GeneralException(SharedStrings.NO_CURRENT_PROJECT); } else { Context.AddConnection("_DB", Context.CurrentRead.File); outputTable = DBReadExecute.GetDataTable(Context.CurrentRead.File, "Select TOP 2 * from " + Identifier); recordCount = (int)DBReadExecute.GetScalar(Context.CurrentRead.File, "SELECT COUNT(*) FROM " + Identifier); File = Context.CurrentRead.File; IsEpi7ProjectRead = Context.CurrentRead.IsEpi7ProjectRead; Context.CurrentRead = this; } } else { Context.AddConnection("_DB", Context.CurrentProject.CollectedDataConnectionString); outputTable = DBReadExecute.GetDataTable(Context.CurrentProject.CollectedDataConnectionString, "Select TOP 2 * from " + Context.CurrentProject.views[Identifier].FromViewSQL); recordCount = (int)DBReadExecute.GetScalar(Context.CurrentProject.CollectedDataConnectionString, "SELECT COUNT(*) FROM " + Identifier); File = Context.CurrentProject.FilePath; Context.CurrentRead = this; IsEpi7ProjectRead = true; } } else { Context.AddConnection("_DB", File); Context.CurrentRead = this; string[] identifiers = Identifier.Split('.'); StringBuilder identifierBuilder = new StringBuilder(); for (int i = 0; i < identifiers.Length; i++) { identifierBuilder.Append("["); identifierBuilder.Append(identifiers[i]); identifierBuilder.Append("]."); } identifierBuilder.Length = identifierBuilder.Length - 1; if (DBReadExecute.ProjectFileName != "") { IsEpi7ProjectRead = true; Context.CurrentProject = new Project(DBReadExecute.ProjectFileName); if (Context.CurrentProject.Views.Exists(Identifier)) { string tableName = Context.CurrentProject.Views[Identifier].TableName; DataSets.Config.DataDriverDataTable dataDrivers = Configuration.GetNewInstance().DataDrivers; if (!DBReadExecute.CheckDatabaseTableExistance(DBReadExecute.ParseConnectionString(File), tableName, true)) { throw new GeneralException(string.Format("The Datatable for [{0}] does NOT exist.\nPlease be sure the Datatable exists before trying to READ.", Identifier)); } else { string query = GetEpi7ProjectRecordCountQuery(tableName); recordCount = (int)DBReadExecute.GetScalar(File, query); outputTable = DBReadExecute.GetDataTable(File, "Select TOP 2 * From [" + Context.CurrentProject.Views[Identifier].TableName + "]"); foreach (Page page in Context.CurrentProject.Views[Identifier].Pages) { outputTable = JoinTables(outputTable, DBReadExecute.GetDataTable(File, "Select TOP 2 * From [" + page.TableName + "]")); } } } else { outputTable = DBReadExecute.GetDataTable(File, "Select TOP 2 * FROM " + identifierBuilder.ToString()); recordCount = (int)DBReadExecute.GetScalar(File, "SELECT COUNT(*) FROM " + identifierBuilder.ToString()); } } else { String identifier = identifierBuilder.ToString(); outputTable = DBReadExecute.GetDataTable(File, "Select TOP 2 * from " + identifier); recordCount = int.Parse(DBReadExecute.GetScalar(File, "SELECT COUNT(*) FROM " + identifier).ToString()); } } outputTable.TableName = "Output"; Context.DataTableRefreshNeeded = true; if (Context.DataSet.Tables.Contains("Output")) { Context.DataSet.Tables.Remove("Output"); } Context.DataSet.Tables.Add(outputTable); if (Context.DataSet.Tables.Contains("datasource")) { Context.DataSet.Tables.Remove("datasource"); } DataTable datasouceTable = new DataTable("datasource"); foreach (DataColumn column in outputTable.Columns) { DataColumn newColumn = new DataColumn(column.ColumnName); newColumn.DataType = column.DataType; datasouceTable.Columns.Add(newColumn); } Context.DataSet.Tables.Add(datasouceTable); Context.MemoryRegion.RemoveVariablesInScope(VariableType.DataSource); Context.ReadDataSource(datasouceTable); Context.GroupVariableList = new Dictionary <string, List <string> >(); if (Rule_Read.RemoveVariables) { if (Context.DataSet.Tables.Contains("variables")) { Context.DataSet.Tables.Remove("variables"); Context.MemoryRegion.RemoveVariablesInScope(VariableType.Standard); } if (Context.SelectExpression != null) { Context.SelectExpression.Clear(); } if (Context.SubroutineList != null) { Context.SubroutineList.Clear(); } if (Context.Subroutine != null) { Context.Subroutine.Clear(); } Context.SelectString.Length = 0; Context.SortExpression.Length = 0; } else { Rule_Read.RemoveVariables = true; } Context.SyncVariableAndOutputTable(); Context.ClearParticipatingVariableList(); Context.GetOutput(); result = string.Format("number of records read {0}", recordCount); Dictionary <string, string> args = new Dictionary <string, string>(); args.Add("COMMANDNAME", CommandNames.READ); args.Add("FILENAME", File); args.Add("TABLENAME", Identifier); args.Add("ROWCOUNT", recordCount.ToString()); Project currentProject = Context.CurrentProject; if (string.IsNullOrEmpty(File) && currentProject == null) { throw new GeneralException(SharedStrings.NO_CURRENT_PROJECT); } Context.AnalysisCheckCodeInterface.Display(args); return(result); }
private DataTable GetDataTable(string filePath, string identifier) { System.Data.DataTable table; if (filePath.ToUpperInvariant().StartsWith("CONFIG:")) { string[] datakey = filePath.Split(':'); string connectionString = null; Configuration config = Configuration.GetNewInstance(); for (int i = 0; i < config.RecentDataSources.Count; i++) { Epi.DataSets.Config.RecentDataSourceRow row = config.RecentDataSources[i]; if (row.Name.ToUpperInvariant() == datakey[1].ToUpperInvariant()) { connectionString = Configuration.Decrypt(row.ConnectionString); break; } } filePath = connectionString; } string[] Identifiers = identifier.Split('.'); StringBuilder IdentifierBuilder = new StringBuilder(); for (int i = 0; i < Identifiers.Length; i++) { IdentifierBuilder.Append("["); IdentifierBuilder.Append(Identifiers[i]); IdentifierBuilder.Append("]."); } IdentifierBuilder.Length = IdentifierBuilder.Length - 1; if (DBReadExecute.ProjectFileName != "") { if (Context.CurrentProject.Views.Exists(identifier)) { string selectStatement = "Select * From [" + identifier + "]"; table = DBReadExecute.GetDataTable(Context.CurrentRead.File, selectStatement); foreach (Page page in Context.CurrentProject.Views[identifier].Pages) { DataTable pageTable = DBReadExecute.GetDataTable(Context.CurrentRead.File, "Select * From [" + page.TableName + "]"); table = JoinPagesTables(table, pageTable); } } else { if (filePath.EndsWith(".prj")) { Epi.Data.IDbDriver driver = DBReadExecute.GetDataDriver(filePath); table = driver.GetTableData(identifier); DataTable pageTables = DBReadExecute.GetDataTable(driver, "Select DISTINCT PageId FROM metaFields Where DataTableName = '" + identifier + "' AND PageId <> null"); foreach (DataRow row in pageTables.Rows) { DataTable pageTable = driver.GetTableData(identifier + row["PageId"]); table = JoinPagesTables(table, pageTable); } } else { table = DBReadExecute.GetDataTable(filePath, "Select * FROM " + IdentifierBuilder.ToString()); } } } else { if (filePath.EndsWith(".prj")) { Epi.Data.IDbDriver driver = DBReadExecute.GetDataDriver(filePath); table = driver.GetTableData(identifier); DataTable pageTables = DBReadExecute.GetDataTable(driver, "Select DISTINCT PageId FROM metaFields Where DataTableName = '" + identifier + "' AND PageId <> null"); foreach (DataRow row in pageTables.Rows) { DataTable pageTable = driver.GetTableData(identifier + row["PageId"]); table = JoinPagesTables(table, pageTable); } } else { table = DBReadExecute.GetDataTable(filePath, "Select * from " + IdentifierBuilder.ToString()); } } return(table); }
private void BuildKeyDialog_Load(object sender, EventArgs e) { if (SelectedDataSource != null) { if (SelectedDataSource is IDbDriver) { IDbDriver db = SelectedDataSource as IDbDriver; //--EI-114 // relatedFields = db.GetTableColumnNames(RelatedTable); if (RelatedTable.Contains(StringLiterals.SPACE)) { string pstr = "Select TOP 2 * from [" + RelatedTable + "]"; DataTable relfields = DBReadExecute.GetDataTable(db, pstr); foreach (DataColumn dc in relfields.Columns) { relatedFields.Add(dc.ColumnName); } } else { relatedFields = db.GetTableColumnNames(RelatedTable); } //--- } else if (SelectedDataSource is Project) { Project project = SelectedDataSource as Project; if (project.Views.Exists(relatedTable)) { foreach (Epi.Fields.IField field in project.Views[RelatedTable].Fields) { if (!(field is Epi.Fields.LabelField) & !(field is Epi.Fields.CommandButtonField) & !(field is Epi.Fields.PhoneNumberField)//EI-705 & !(field is Epi.Fields.MultilineTextField) & !(field is Epi.Fields.GroupField) & !(field is Epi.Fields.CheckBoxField) & !(field is Epi.Fields.ImageField) & !(field is Epi.Fields.OptionField) & !(field is Epi.Fields.GridField) & !(field is Epi.Fields.MirrorField)) { relatedFields.Add(field.Name); } } } else { relatedFields = project.GetTableColumnNames(RelatedTable); } } if (this.EpiInterpreter.Context.DataSet != null) { View CurrentView = null; bool currentReadIdentifierIsView = false; if (this.EpiInterpreter.Context.CurrentProject != null) { currentReadIdentifierIsView = this.EpiInterpreter.Context.CurrentProject.IsView(this.EpiInterpreter.Context.CurrentRead.Identifier); } if (currentReadIdentifierIsView) { CurrentView = this.EpiInterpreter.Context.CurrentProject.GetViewByName(this.EpiInterpreter.Context.CurrentRead.Identifier); //EI-705 foreach (DataColumn column in this.EpiInterpreter.Context.DataSet.Tables["Output"].Columns) { if ((CurrentView.Fields.Exists(column.ColumnName) && !(CurrentView.Fields[column.ColumnName] is Epi.Fields.LabelField)) & (CurrentView.Fields.Exists(column.ColumnName) && !(CurrentView.Fields[column.ColumnName] is Epi.Fields.CommandButtonField)) & (CurrentView.Fields.Exists(column.ColumnName) && !(CurrentView.Fields[column.ColumnName] is Epi.Fields.PhoneNumberField)) & (CurrentView.Fields.Exists(column.ColumnName) && !(CurrentView.Fields[column.ColumnName] is Epi.Fields.MultilineTextField)) & (CurrentView.Fields.Exists(column.ColumnName) && !(CurrentView.Fields[column.ColumnName] is Epi.Fields.GroupField)) & (CurrentView.Fields.Exists(column.ColumnName) && !(CurrentView.Fields[column.ColumnName] is Epi.Fields.CheckBoxField)) & (CurrentView.Fields.Exists(column.ColumnName) && !(CurrentView.Fields[column.ColumnName] is Epi.Fields.ImageField)) & (CurrentView.Fields.Exists(column.ColumnName) && !(CurrentView.Fields[column.ColumnName] is Epi.Fields.OptionField)) & (CurrentView.Fields.Exists(column.ColumnName) && !(CurrentView.Fields[column.ColumnName] is Epi.Fields.GridField)) & (CurrentView.Fields.Exists(column.ColumnName) && !(CurrentView.Fields[column.ColumnName] is Epi.Fields.MirrorField))) { currentFields.Add(column.ColumnName); } } } else { foreach (DataColumn column in this.EpiInterpreter.Context.DataSet.Tables["Output"].Columns) { currentFields.Add(column.ColumnName); } } } currentFields.Sort(); } //rdbCurrentTable.Checked = true; relatedFields.Sort(); currentFields.Sort(); //cmbAvailableVariables2.DataSource = relatedFields; lbxRelatedTableFields.DataSource = relatedFields; //cmbAvailableVariables.DataSource = currentFields; lbxCurrentTableFields.DataSource = currentFields; lbxCurrentTableFields.SelectedIndex = -1; lbxRelatedTableFields.SelectedIndex = -1; if (CallingDialog == "RELATE") { lblBuildKeyInstructions.Text = SharedStrings.BUILDKEY_RELATE_INSTRUCTIONS; } else { lblBuildKeyInstructions.Text = SharedStrings.BUILDKEY_MERGE_INSTRUCTIONS; } this.LoadIsFinished = true; }
private void BuildKeyDialog_Load(object sender, EventArgs e) { if (SelectedDataSource != null) { if (SelectedDataSource is IDbDriver) { IDbDriver db = SelectedDataSource as IDbDriver; //--EI-114 // relatedFields = db.GetTableColumnNames(RelatedTable); if (RelatedTable.Contains(StringLiterals.SPACE)) { string pstr = "Select TOP 2 * from [" + RelatedTable + "]"; DataTable relfields = DBReadExecute.GetDataTable(db, pstr); foreach (DataColumn dc in relfields.Columns) { relatedFields.Add(dc.ColumnName); } } else { relatedFields = db.GetTableColumnNames(RelatedTable); } //--- } else if (SelectedDataSource is Project) { Project project = SelectedDataSource as Project; if (project.Views.Exists(relatedTable)) { foreach (Epi.Fields.IField field in project.Views[RelatedTable].Fields) { if (!(field is Epi.Fields.LabelField) & !(field is Epi.Fields.CommandButtonField) & !(field is Epi.Fields.PhoneNumberField) & !(field is Epi.Fields.MultilineTextField) & !(field is Epi.Fields.GroupField) & !(field is Epi.Fields.CheckBoxField) & !(field is Epi.Fields.ImageField) & !(field is Epi.Fields.OptionField) & !(field is Epi.Fields.GridField) & !(field is Epi.Fields.MirrorField)) { relatedFields.Add(field.Name); } } } else { relatedFields = project.GetTableColumnNames(RelatedTable); } } currentFields = GetCurrentFields(); } relatedFields.Sort(); currentFields.Sort(); lbxRelatedTableFields.DataSource = relatedFields; lbxCurrentTableFields.DataSource = currentFields; lbxCurrentTableFields.SelectedIndex = -1; lbxRelatedTableFields.SelectedIndex = -1; if (CallingDialog == "RELATE") { lblBuildKeyInstructions.Text = SharedStrings.BUILDKEY_RELATE_INSTRUCTIONS; } else { lblBuildKeyInstructions.Text = SharedStrings.BUILDKEY_MERGE_INSTRUCTIONS; } this.LoadIsFinished = true; }