/// <summary> /// performs execution of the WRITE command /// </summary> /// <returns>object</returns> public override object Execute() { object result = null; Context.AnalysisCheckCodeInterface.ShowWaitDialog("Exporting data..."); //string[] tmp = this.OutTarget.ToString().Split(':'); //string FilePath = null; //if (tmp.Length <= 2) //{ // FilePath = tmp[0]; //} //else //{ // FilePath = tmp[0] + ":" + tmp[1]; //} //FilePath = FilePath.Trim().Trim(new char[] { '\'' }); //string TableName; //if (tmp.Length > 1) //{ // TableName = tmp[tmp.Length - 1].Replace("]", "").Replace("[", "").Trim().Trim('\''); //} //else //{ // TableName = this.OutTarget; // FilePath = this.Context.CurrentProject.CollectedDataConnectionString; //} CurrentDataTable = this.Context.DataSet.Tables["output"].Clone(); foreach (DataRow row in this.Context.GetOutput(new List <string>())) { CurrentDataTable.ImportRow(row); } if (this.IdentifierList[0] == "*") { for (int i = 0; i < CurrentDataTable.Columns.Count; i++) { IVariable var = (IVariable)this.Context.GetVariable(CurrentDataTable.Columns[i].ColumnName); if (var != null) { if (var.VarType != VariableType.Global && var.VarType != VariableType.Permanent) { TempVariableList.Add(CurrentDataTable.Columns[i].ColumnName.ToUpperInvariant()); } } else { TempVariableList.Add(CurrentDataTable.Columns[i].ColumnName.ToUpperInvariant()); } } } else { for (int i = 0; i < this.IdentifierList.Length; i++) { TempVariableList.Add(this.IdentifierList[i].ToUpperInvariant()); } } if (isExceptionList) { for (int i = CurrentDataTable.Columns.Count - 1; i > -1; i--) { if (TempVariableList.Contains(CurrentDataTable.Columns[i].ColumnName.ToUpperInvariant())) { //CurrentDataTable.Columns.Remove(CurrentDataTable.Columns[i]); } else { if (this.IdentifierList[0] == "*") { IVariable var = (IVariable)this.Context.GetVariable(CurrentDataTable.Columns[i].ColumnName); if (var != null) { if (var != null && var.VarType != VariableType.Global && var.VarType != VariableType.Permanent) { VariableList.Add(var.Name.ToUpperInvariant()); } } } else { VariableList.Add(CurrentDataTable.Columns[i].ColumnName.ToUpperInvariant()); } } } } else // is NOT an isExceptionList { for (int i = 0; i < CurrentDataTable.Columns.Count; i++) { if (TempVariableList.Contains(CurrentDataTable.Columns[i].ColumnName.ToUpperInvariant())) { VariableList.Add(CurrentDataTable.Columns[i].ColumnName.ToUpperInvariant()); } else { //CurrentDataTable.Columns.Remove(CurrentDataTable.Columns[i]); } } } try { Dictionary <string, List <TableColumn> > WideTableColumns = null; DataSets.Config.DataDriverDataTable dataDrivers = Configuration.GetNewInstance().DataDrivers; IDbDriverFactory dbFactory = null; foreach (DataSets.Config.DataDriverRow dataDriver in dataDrivers) { dbFactory = DbDriverFactoryCreator.GetDbDriverFactory(dataDriver.Type); if (dbFactory.CanClaimConnectionString(FilePath)) { break; } } OutputDriver = DBReadExecute.GetDataDriver(FilePath, this.isConnectionString); if (OutputDriver.GetType().Name.Equals("CsvFile", StringComparison.OrdinalIgnoreCase) || this.FileDataFormat.Equals("TEXT", StringComparison.OrdinalIgnoreCase)) { if (!this.TableName.EndsWith(".txt") && !this.TableName.EndsWith(".csv") && !this.TableName.EndsWith("#csv") && !this.TableName.EndsWith("#txt")) { this.TableName = this.TableName + ".csv"; } } this.OutTarget = this.FilePath + ":" + this.TableName; this.curFile = OutputDriver.DataSource; if (!OutputDriver.CheckDatabaseExistance(FilePath, TableName, this.isConnectionString)) { DbDriverInfo collectDbInfo = new DbDriverInfo(); Type SqlDriverType = Type.GetType("Epi.Data.SqlServer.SqlDBFactory, Epi.Data.SqlServer"); if (DBReadExecute.DataSource.GetType().AssemblyQualifiedName == SqlDriverType.AssemblyQualifiedName) { collectDbInfo.DBCnnStringBuilder = new System.Data.SqlClient.SqlConnectionStringBuilder(); } else { collectDbInfo.DBCnnStringBuilder = new System.Data.OleDb.OleDbConnectionStringBuilder(); } collectDbInfo.DBCnnStringBuilder.ConnectionString = dbFactory.ConvertFileStringToConnectionString(FilePath); //collectDbInfo.DBCnnStringBuilder = dbFactory.RequestDefaultConnection(dbFactory.FilePath.Trim()); OutputDriver = dbFactory.CreateDatabaseObject(collectDbInfo.DBCnnStringBuilder); collectDbInfo.DBName = OutputDriver.DbName; dbFactory.CreatePhysicalDatabase(collectDbInfo); } bool?deleteSuccessful = null; if (this.WriteMode.Equals("REPLACE", StringComparison.OrdinalIgnoreCase) && DBReadExecute.CheckDatabaseTableExistance(FilePath, TableName, this.isConnectionString)) { deleteSuccessful = OutputDriver.DeleteTable(TableName); } List <TableColumn> TableColumns = new List <TableColumn>(); if (!DBReadExecute.CheckDatabaseTableExistance(FilePath, TableName, this.isConnectionString)) { foreach (DataColumn column in CurrentDataTable.Columns) { if (VariableList.Contains(column.ColumnName.ToUpperInvariant())) { bool isPermanentVariable = false; IVariable candidate = Context.MemoryRegion.GetVariable(column.ColumnName); if (candidate != null && candidate.IsVarType(VariableType.Permanent)) { isPermanentVariable = true; } if (isPermanentVariable == false) { TableColumn newTableColumn; if (column.DataType.ToString() == "System.String") { if (column.MaxLength <= 0) { newTableColumn = new TableColumn(column.ColumnName.ToString(), ConvertToGenericType(column.DataType), int.MaxValue, column.AllowDBNull); } else { newTableColumn = new TableColumn(column.ColumnName.ToString(), ConvertToGenericType(column.DataType), column.MaxLength, column.AllowDBNull); } } else if (column.DataType.ToString() == "System.Guid") { if (column.MaxLength <= 0) { newTableColumn = new TableColumn(column.ColumnName.ToString(), ConvertToGenericType(column.DataType), int.MaxValue, column.AllowDBNull); } else { newTableColumn = new TableColumn(column.ColumnName.ToString(), ConvertToGenericType(column.DataType), column.MaxLength, column.AllowDBNull); } } else { newTableColumn = new TableColumn(column.ColumnName.ToString(), ConvertToGenericType(column.DataType), column.AllowDBNull); } newTableColumn.AllowNull = column.AllowDBNull; newTableColumn.IsIdentity = column.Unique; TableColumns.Add(newTableColumn); } } } if ( ( (!(OutputDriver.GetType().Name.Equals("AccessDatabase", StringComparison.OrdinalIgnoreCase) || OutputDriver.GetType().Name.Equals("Access2007Database", StringComparison.OrdinalIgnoreCase) || OutputDriver.GetType().Name.Equals("ExcelWorkbook", StringComparison.OrdinalIgnoreCase) || OutputDriver.GetType().Name.Equals("Excel2007Workbook", StringComparison.OrdinalIgnoreCase)) && VariableList.Count <= Max_Number_Columns) || OutputDriver.GetType().Name.Equals("SqlDatabase", StringComparison.OrdinalIgnoreCase) ) ) { OutputDriver.CreateTable(TableName, TableColumns); } else { if (OutputDriver.GetType().Name.Equals("ExcelWorkbook", StringComparison.OrdinalIgnoreCase) || OutputDriver.GetType().Name.Equals("Excel2007Workbook", StringComparison.OrdinalIgnoreCase)) { WideTableColumns = this.CreateExcelWideTable(TableColumns); } else if (!OutputDriver.GetType().Name.Equals("CsvFile", StringComparison.OrdinalIgnoreCase)) { WideTableColumns = this.CreateAccessWideTable(TableColumns); } } } else // check that column name exists in destinationl { foreach (string columnName in VariableList) { bool isFound = false; foreach (DataColumn column in CurrentDataTable.Columns) { if (column.ColumnName.ToUpperInvariant() == columnName.ToUpperInvariant()) { isFound = true; break; } } if (!isFound) { TableColumn newTableColumn; DataColumn column = CurrentDataTable.Columns[columnName]; if (column.DataType.ToString() == "System.String") { newTableColumn = new TableColumn(column.ColumnName.ToString(), ConvertToGenericType(column.DataType), column.MaxLength, column.AllowDBNull); } else if (column.DataType.ToString() == "System.Guid") { newTableColumn = new TableColumn(column.ColumnName.ToString(), ConvertToGenericType(column.DataType), column.MaxLength, column.AllowDBNull); } else { newTableColumn = new TableColumn(column.ColumnName.ToString(), ConvertToGenericType(column.DataType), column.AllowDBNull); } newTableColumn.AllowNull = column.AllowDBNull; newTableColumn.IsIdentity = column.Unique; OutputDriver.AddColumn(TableName, newTableColumn); } } if ((OutputDriver.GetType().Name.Equals("AccessDatabase", StringComparison.OrdinalIgnoreCase) || OutputDriver.GetType().Name.Equals("Access2007Database", StringComparison.OrdinalIgnoreCase) || OutputDriver.GetType().Name.Equals("ExcelWorkbook", StringComparison.OrdinalIgnoreCase) || OutputDriver.GetType().Name.Equals("Excel2007Workbook", StringComparison.OrdinalIgnoreCase)) && VariableList.Count > Max_Number_Columns) { foreach (DataColumn column in CurrentDataTable.Columns) { if (VariableList.Contains(column.ColumnName.ToUpperInvariant())) { bool isPermanentVariable = false; IVariable candidate = Context.MemoryRegion.GetVariable(column.ColumnName); if (candidate != null && candidate.IsVarType(VariableType.Permanent)) { isPermanentVariable = true; } if (isPermanentVariable == false) { TableColumn newTableColumn; if (column.DataType.ToString() == "System.String") { if (column.MaxLength <= 0) { newTableColumn = new TableColumn(column.ColumnName.ToString(), ConvertToGenericType(column.DataType), int.MaxValue, column.AllowDBNull); } else { newTableColumn = new TableColumn(column.ColumnName.ToString(), ConvertToGenericType(column.DataType), column.MaxLength, column.AllowDBNull); } } else if (column.DataType.ToString() == "System.Guid") { if (column.MaxLength <= 0) { newTableColumn = new TableColumn(column.ColumnName.ToString(), ConvertToGenericType(column.DataType), int.MaxValue, column.AllowDBNull); } else { newTableColumn = new TableColumn(column.ColumnName.ToString(), ConvertToGenericType(column.DataType), column.MaxLength, column.AllowDBNull); } } else { newTableColumn = new TableColumn(column.ColumnName.ToString(), ConvertToGenericType(column.DataType), column.AllowDBNull); } newTableColumn.AllowNull = column.AllowDBNull; newTableColumn.IsIdentity = column.Unique; TableColumns.Add(newTableColumn); } } } if (OutputDriver.GetType().Name.Equals("ExcelWorkbook", StringComparison.OrdinalIgnoreCase) || OutputDriver.GetType().Name.Equals("Excel2007Workbook", StringComparison.OrdinalIgnoreCase)) { WideTableColumns = this.CreateExcelWideTable(TableColumns); } else { WideTableColumns = this.CreateAccessWideTable(TableColumns, false); } } } ////APPEND| REPLACE | !Null //if (this.WriteMode.Equals("REPLACE", StringComparison.OrdinalIgnoreCase)) //{ // WriteMethod = this.ReplaceWrite; //} //else //{ // WriteMethod = this.AppendWrite; //} if (OutputDriver.GetType().Name.Equals("CsvFile", StringComparison.OrdinalIgnoreCase) || this.FileDataFormat.Equals("TEXT", StringComparison.OrdinalIgnoreCase)) { if (TableColumns.Count == 0) { foreach (DataColumn column in CurrentDataTable.Columns) { if (VariableList.Contains(column.ColumnName.ToUpperInvariant())) { bool isPermanentVariable = false; IVariable candidate = Context.MemoryRegion.GetVariable(column.ColumnName); if (candidate != null && candidate.IsVarType(VariableType.Permanent)) { isPermanentVariable = true; } if (isPermanentVariable == false) { TableColumn newTableColumn; if (column.DataType.ToString() == "System.String") { if (column.MaxLength <= 0) { newTableColumn = new TableColumn(column.ColumnName.ToString(), ConvertToGenericType(column.DataType), int.MaxValue, column.AllowDBNull); } else { newTableColumn = new TableColumn(column.ColumnName.ToString(), ConvertToGenericType(column.DataType), column.MaxLength, column.AllowDBNull); } } else if (column.DataType.ToString() == "System.Guid") { if (column.MaxLength <= 0) { newTableColumn = new TableColumn(column.ColumnName.ToString(), ConvertToGenericType(column.DataType), int.MaxValue, column.AllowDBNull); } else { newTableColumn = new TableColumn(column.ColumnName.ToString(), ConvertToGenericType(column.DataType), column.MaxLength, column.AllowDBNull); } } else { newTableColumn = new TableColumn(column.ColumnName.ToString(), ConvertToGenericType(column.DataType), column.AllowDBNull); } newTableColumn.AllowNull = column.AllowDBNull; newTableColumn.IsIdentity = column.Unique; TableColumns.Add(newTableColumn); } } } } this.WriteCSVFile(TableColumns); } else if ((OutputDriver.GetType().Name.Equals("AccessDatabase", StringComparison.OrdinalIgnoreCase) || OutputDriver.GetType().Name.Equals("Access2007Database", StringComparison.OrdinalIgnoreCase) || OutputDriver.GetType().Name.Equals("ExcelWorkbook", StringComparison.OrdinalIgnoreCase) || OutputDriver.GetType().Name.Equals("Excel2007Workbook", StringComparison.OrdinalIgnoreCase)) && VariableList.Count > Max_Number_Columns) { this.PopulateTable(WideTableColumns); } else { DataTable sourceTable = OutputDriver.GetTableData(TableName); OutputDriver.IsBulkOperation = true; StringBuilder sqlquery = new StringBuilder(); int count = 0; sqlquery.Append("create table [" + TableName + "] ( "); foreach (string column in VariableList) { string columnName = String.Empty; if (!column.Contains(".") && !OutputDriver.ColumnExists(TableName, column)) { columnName = column; if (count > 0) { sqlquery.Append(", "); } sqlquery.Append(" [" + columnName + "] " + DBReadExecute.SQLGetType(CurrentDataTable.Columns[column])); count++; } } sqlquery.Append(" )"); if (count > 0) { Query qr = OutputDriver.CreateQuery(sqlquery.ToString()); OutputDriver.ExecuteNonQuery(qr); } OutputDriver.IsBulkOperation = false; //Insert data into table ////Open connection ////Setup Schema ////Loop through records ////Close connection DataTable WritableTable = CurrentDataTable.Clone(); for (int i = WritableTable.Columns.Count - 1; i > -1; i--) { if (WritableTable.Columns[i].DataType == typeof(Guid)) { WritableTable.Columns[i].DataType = typeof(String); } } for (int i = WritableTable.Columns.Count - 1; i > -1; i--) { DataColumn col = WritableTable.Columns[i]; if (!VariableList.Contains(col.ColumnName.ToUpperInvariant())) { WritableTable.Columns.Remove(col); } } foreach (DataRow row in CurrentDataTable.Select("", this.Context.SortExpression.ToString())) { DataRow newRow = WritableTable.NewRow(); foreach (string column in VariableList) { newRow[column] = row[column]; } WritableTable.Rows.Add(newRow); } System.Data.Common.DbDataReader DataReader = WritableTable.CreateDataReader(); DBReadExecute.InsertBulkRows(FilePath, "Select * From [" + TableName + "]", DataReader, SetGadgetStatusHandler); if (CurrentDataTable.Rows.Count > 0) { this.statusMessage = "Export completed successfully, "; } else { this.statusMessage = "Export was not completed successfully, "; } this.statusMessage += CurrentDataTable.Rows.Count.ToString() + " records written."; DataReader.Close(); } } catch (Exception ex) { this.statusMessage = "Problems exporting records: " + ex.ToString(); System.Console.Write(ex); } finally { Context.AnalysisCheckCodeInterface.HideWaitDialog(); } /* * * * * * Comments * Records deleted in Enter or selected in Analysis are handled as in other Analysis commands. * Defined variables may be written, allowing the creation of a new Epi Info file that makes the changes permanent. * Global and permanent variables will not be written unless explicitly specified. * To write only selected variables, the word EXCEPT may be inserted to indicate all variables except those following EXCEPT. * If the output file specified does not exist, the WRITE command will attempt to create it. * Either APPEND or REPLACE must be specified to indicate that an existing file/table by the * same name will be erased or records will be appended to the existing file/table. * If some, but not all, of the fields being written match those in an existing file during an APPEND, * the unmatched fields are added to the output table. * For Epi 6 REC or Access/EpiInfo table outputs, if there are no records, * the WRITE command creates a table/file with variable information but no data. * * WRITE <METHOD> {<output type>} {<project>:}table {[<variable(s)>]} * WRITE <METHOD> {<output type>} {<project>:}table * EXCEPT {[<variable(s)>]} * * The <METHOD> represents either REPLACE or APPEND * The <project> represents the path and filename of the output. * The <variable(s)> represents one or more variable names. * The <output type> represents the following allowable outputs: * * Database Type Specifier Element * * Jet "Access 97" "Access 2000" * "Epi 2000" <path:<table> * dBase III "dBase III" <path> * dBase IV "dBase IV" <path> * dBase 5.0 "dBase 5.0" <path> * Paradox 3.x "Paradox 3.x" <path> * Paradox 4.x "Paradox 4.x" <path> * FoxPro 2.0 "FoxPro 2.0" <path> * FoxPro 2.5 "FoxPro 2.5" <path> * FoxPro 2.6 "FoxPro 2.6" <path> * Excel 3.0 "Excel 3.0" <path> * Excel 4.0 "Excel 4.0" <path> * Epi Info 6 "Epi6" <path> * Text (Delimited) "Text" <path> * */ args.Add("COMMANDNAME", CommandNames.WRITE); args.Add("WRITEMODE", this.WriteMode); args.Add("OUTTARGET", this.OutTarget); args.Add("STATUS", this.statusMessage); //args.Add("PROGRESST", this.progress.ToString()); //args.Add("ROWCOUNT", CurrentDataTable.Select("", this.Context.SortExpression.ToString()).Length.ToString()); this.Context.AnalysisCheckCodeInterface.Display(args); return(result); }
private void Export() { if (string.IsNullOrEmpty(txtConnectionInformation.Text)) { return; } string tableName = string.Empty; if (cmbDestinationTable.SelectedItem != null) { tableName = cmbDestinationTable.SelectedItem.ToString(); } else { tableName = cmbDestinationTable.Text; } if (string.IsNullOrEmpty(tableName)) { return; } if ( cmbDataFormats.SelectedItem != null && (cmbDataFormats.SelectedItem.ToString().Contains(".mdb") || cmbDataFormats.SelectedItem.ToString().Contains("SQL Server")) && db.TableExists("metaViews") && db.ColumnExists("metaViews", "Name") && db.TableExists("metaFields") && db.ColumnExists("metaFields", "DataTableName") ) { List <string> columnNames = new List <string>(); columnNames.Add("Name"); DataTable dt = db.GetTableData("metaViews", columnNames); foreach (DataRow row in dt.Rows) { string rowDataTable = row["Name"].ToString(); if (rowDataTable.StartsWith(tableName) || rowDataTable.ToLower().StartsWith(tableName.ToLower())) { Epi.WPF.Dashboard.Dialogs.MsgBox.ShowError(ImportExportSharedStrings.ERROR_EXPORT_CANNOT_USE_EXISTING_FORM_NAME); return; } } columnNames = new List <string>(); columnNames.Add("DataTableName"); dt = db.GetTableData("metaFields", columnNames); foreach (DataRow row in dt.Rows) { string rowDataTable = row["DataTableName"].ToString(); if (!string.IsNullOrEmpty(rowDataTable) && (tableName.StartsWith(rowDataTable) || tableName.ToLower().StartsWith(rowDataTable.ToLower()))) { string diff = tableName.Replace(rowDataTable, ""); int num = -1; bool success = int.TryParse(diff, out num); if (success) { Epi.WPF.Dashboard.Dialogs.MsgBox.ShowError(ImportExportSharedStrings.ERROR_EXPORT_CANNOT_OVERWRITE_FORM_TABLE); return; } } } List <string> protectedTableNames = new List <string>(); protectedTableNames.Add("metaBackgrounds"); protectedTableNames.Add("metaDataTypes"); protectedTableNames.Add("metaDbInfo"); protectedTableNames.Add("metaFields"); protectedTableNames.Add("metaFieldTypes"); protectedTableNames.Add("metaImages"); protectedTableNames.Add("metaLinks"); protectedTableNames.Add("metaPages"); protectedTableNames.Add("metaViews"); foreach (string protectedTableName in protectedTableNames) { if (protectedTableName.Equals(tableName) || protectedTableName.ToLower().Equals(tableName.ToLower())) { Epi.WPF.Dashboard.Dialogs.MsgBox.ShowError(ImportExportSharedStrings.ERROR_EXPORT_CANNOT_OVERWRITE_META); return; } } } List <string> badFieldNames = new List <string>(); foreach (string fieldName in lbxExportFields.SelectedItems) { if (fieldName.StartsWith(" ") || fieldName.EndsWith(" ")) { badFieldNames.Add(fieldName); } } string fileName = db.DataSource + "\\" + tableName; if (!fileName.ToLower().EndsWith(".csv") && !fileName.ToLower().EndsWith(".txt")) { fileName = fileName + ".csv"; } bool writeToFlatFile = false; if (cmbDataFormats.SelectedItem.ToString().ToLower().Contains("ascii")) { writeToFlatFile = true; } if (!writeToFlatFile) { if (badFieldNames.Count > 0) { string problemFields = string.Empty; int count = 0; foreach (string fieldName in badFieldNames) { problemFields = problemFields + fieldName + ", "; count++; } problemFields = problemFields.TrimEnd().TrimEnd(','); problemFields = problemFields + "."; Epi.WPF.Dashboard.Dialogs.MsgBox.ShowError(string.Format(SharedStrings.EXPORT_CANNOT_PROCEED_LEADING_TRAILING_SPACES, problemFields)); return; } } messagePanel.MessagePanelType = Controls.MessagePanelType.StatusPanel; messagePanel.Text = string.Empty; messagePanel.Visibility = System.Windows.Visibility.Visible; messagePanel.SetProgressBarMax(dashboardHelper.RecordCount); messagePanel.SetProgressBarValue(0); messagePanel.ShowProgressBar(); exportFields.Clear(); if (lbxExportFields.SelectedItems.Count > 0) { foreach (string item in lbxExportFields.SelectedItems) { if (!string.IsNullOrEmpty(item)) { exportFields.Add(item); } } } if (lbxExportFields.Items.Count == lbxExportFields.SelectedItems.Count) { allFieldsSelected = true; } else { allFieldsSelected = false; } if (checkboxUsePrompts.IsChecked == true) { useFieldPrompts = true; } else { useFieldPrompts = false; } if (checkboxTabOrder.IsChecked == true) { useTabOrder = true; } else { useTabOrder = false; } if (worker != null && worker.WorkerSupportsCancellation) { worker.CancelAsync(); } lock (syncLock) { btnExport.IsEnabled = false; cmbDestinationTable.IsEnabled = false; cmbDataFormats.IsEnabled = false; btnConnectionBrowse.IsEnabled = false; worker = new System.ComponentModel.BackgroundWorker(); worker.WorkerSupportsCancellation = true; if (!writeToFlatFile) { worker.DoWork += new System.ComponentModel.DoWorkEventHandler(datadriverWorker_DoWork); worker.RunWorkerCompleted += new System.ComponentModel.RunWorkerCompletedEventHandler(datadriverWorker_WorkerCompleted); worker.RunWorkerAsync(tableName); } else { worker.DoWork += new System.ComponentModel.DoWorkEventHandler(fileIOWorker_DoWork); worker.RunWorkerCompleted += new System.ComponentModel.RunWorkerCompletedEventHandler(fileIOWorker_WorkerCompleted); worker.RunWorkerAsync(fileName); } } }