private int GetRecordCount(string filePath, string identifier) { int recordCount = 0; 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)) { recordCount = (int)DBReadExecute.GetScalar(filePath, "SELECT COUNT(*) FROM " + Context.CurrentProject.Views[identifier].TableName); } else { recordCount = (int)DBReadExecute.GetScalar(filePath, "SELECT COUNT(*) FROM " + IdentifierBuilder.ToString()); } } else { recordCount = (int)DBReadExecute.GetScalar(filePath, "SELECT COUNT(*) FROM " + IdentifierBuilder.ToString()); } return(recordCount); }
/// <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); }