private void SetupOutputDataSource()
{
try
{
ComboBoxItem selectedPlugIn = cmbDataFormats.SelectedItem as ComboBoxItem;
IDbDriverFactory dbFactory = null;
string plugin = string.Empty;
foreach (Epi.DataSets.Config.DataDriverRow row in dashboardHelper.Config.DataDrivers)
{
string content = selectedPlugIn.Content.ToString();
if (content.Equals(row.DisplayName))
{
plugin = row.Type;
}
}
selectedDataProvider = plugin;
bool isFlatFile = false;
dbFactory = DbDriverFactoryCreator.GetDbDriverFactory(plugin);
if (dbFactory.ArePrerequisitesMet())
{
DbConnectionStringBuilder dbCnnStringBuilder = new DbConnectionStringBuilder();
db = dbFactory.CreateDatabaseObject(dbCnnStringBuilder);
IConnectionStringGui dialog = dbFactory.GetConnectionStringGuiForExistingDb();
dialog.ShouldIgnoreNonExistance = true;
System.Windows.Forms.DialogResult result = ((System.Windows.Forms.Form)dialog).ShowDialog();
if (result == System.Windows.Forms.DialogResult.OK)
{
//this.savedConnectionStringDescription = dialog.ConnectionStringDescription;
bool success = false;
db.ConnectionString = dialog.DbConnectionStringBuilder.ToString();
DbDriverInfo dbInfo = new DbDriverInfo();
dbInfo.DBCnnStringBuilder = dbFactory.RequestNewConnection(db.DataSource);
if (db.ConnectionDescription.ToLower().Contains("csv file:"))
{
isFlatFile = true;
}
if (!isFlatFile)
{
if (!db.CheckDatabaseExistance(db.ConnectionString, "", true))
{
dbFactory.CreatePhysicalDatabase(dbInfo);
}
try
{
success = db.TestConnection();
}
catch
{
success = false;
MessageBox.Show("Could not connect to selected data source.");
}
}
else
{
success = true;
}
if (success)
{
this.SelectedDataSource = db;
controlNeedsRefresh = true;
txtConnectionInformation.Text = db.ConnectionString;
}
else
{
this.SelectedDataSource = null;
}
}
else
{
this.SelectedDataSource = null;
}
}
else
{
MessageBox.Show(dbFactory.PrerequisiteMessage, "Prerequisites not found");
}
if (selectedDataSource is IDbDriver)
{
db = selectedDataSource as IDbDriver;
//this.txtDataSource.Text = db.ConnectionString;
if (!isFlatFile)
{
System.Collections.Generic.List<string> tableNames = db.GetTableNames();
foreach (string tableName in tableNames)
{
ComboBoxItem newItem = new ComboBoxItem();//tableName, tableName, tableName);
newItem.Content = tableName;
cmbDestinationTable.Items.Add(tableName);
//this.cmbDataTable.Items.Add(newItem);
}
}
}
}
catch (Exception ex)
{
SetErrorMessage(ex.Message);
}
finally
{
pnlProgress.Visibility = System.Windows.Visibility.Collapsed;
}
}
private void SetupOutputDataSource()
{
try
{
ComboBoxItem selectedPlugIn = cmbDataFormats.SelectedItem as ComboBoxItem;
IDbDriverFactory dbFactory = null;
string plugin = string.Empty;
foreach (Epi.DataSets.Config.DataDriverRow row in dashboardHelper.Config.DataDrivers)
{
string content = selectedPlugIn.Content.ToString();
if (content.Equals(row.DisplayName))
{
plugin = row.Type;
}
}
selectedDataProvider = plugin;
bool isFlatFile = false;
dbFactory = DbDriverFactoryCreator.GetDbDriverFactory(plugin);
if (dbFactory.ArePrerequisitesMet())
{
DbConnectionStringBuilder dbCnnStringBuilder = new DbConnectionStringBuilder();
db = dbFactory.CreateDatabaseObject(dbCnnStringBuilder);
IConnectionStringGui dialog = dbFactory.GetConnectionStringGuiForExistingDb();
dialog.ShouldIgnoreNonExistance = true;
System.Windows.Forms.DialogResult result = ((System.Windows.Forms.Form)dialog).ShowDialog();
if (result == System.Windows.Forms.DialogResult.OK)
{
//this.savedConnectionStringDescription = dialog.ConnectionStringDescription;
bool success = false;
db.ConnectionString = dialog.DbConnectionStringBuilder.ToString();
DbDriverInfo dbInfo = new DbDriverInfo();
dbInfo.DBCnnStringBuilder = dbFactory.RequestNewConnection(db.DataSource);
if (db.ConnectionDescription.ToLower().Contains("csv file:"))
{
isFlatFile = true;
}
if (!isFlatFile)
{
if (!db.CheckDatabaseExistance(db.ConnectionString, "", true))
{
dbFactory.CreatePhysicalDatabase(dbInfo);
}
try
{
success = db.TestConnection();
}
catch
{
success = false;
MessageBox.Show("Could not connect to selected data source.");
}
}
else
{
success = true;
}
if (success)
{
this.SelectedDataSource = db;
controlNeedsRefresh = true;
txtConnectionInformation.Text = db.ConnectionString;
}
else
{
this.SelectedDataSource = null;
}
}
else
{
this.SelectedDataSource = null;
}
}
else
{
MessageBox.Show(dbFactory.PrerequisiteMessage, "Prerequisites not found");
}
if (selectedDataSource is IDbDriver)
{
db = selectedDataSource as IDbDriver;
//this.txtDataSource.Text = db.ConnectionString;
if (!isFlatFile)
{
System.Collections.Generic.List <string> tableNames = db.GetTableNames();
foreach (string tableName in tableNames)
{
ComboBoxItem newItem = new ComboBoxItem();//tableName, tableName, tableName);
newItem.Content = tableName;
cmbDestinationTable.Items.Add(tableName);
//this.cmbDataTable.Items.Add(newItem);
}
}
}
}
catch (Exception ex)
{
SetErrorMessage(ex.Message);
}
finally
{
messagePanel.Visibility = System.Windows.Visibility.Hidden;
}
}
/// <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);
}
/// <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.ToUpper());
}
}
else
{
TempVariableList.Add(CurrentDataTable.Columns[i].ColumnName.ToUpper());
}
}
}
else
{
for (int i = 0; i < this.IdentifierList.Length; i++)
{
TempVariableList.Add(this.IdentifierList[i].ToUpper());
}
}
if (isExceptionList)
{
for (int i = CurrentDataTable.Columns.Count - 1; i > -1; i--)
{
if (TempVariableList.Contains(CurrentDataTable.Columns[i].ColumnName.ToUpper()))
{
//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.ToUpper());
}
}
}
else
{
VariableList.Add(CurrentDataTable.Columns[i].ColumnName.ToUpper());
}
}
}
}
else // is NOT an isExceptionList
{
for (int i = 0; i < CurrentDataTable.Columns.Count; i++)
{
if (TempVariableList.Contains(CurrentDataTable.Columns[i].ColumnName.ToUpper()))
{
VariableList.Add(CurrentDataTable.Columns[i].ColumnName.ToUpper());
}
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);
}
if (this.WriteMode.Equals("REPLACE", StringComparison.OrdinalIgnoreCase) && DBReadExecute.CheckDatabaseTableExistance(FilePath, TableName, this.isConnectionString))
{
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.ToUpper()))
{
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.ToUpper() == columnName.ToUpper())
{
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.ToUpper()))
{
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.ToUpper()))
{
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);
StringBuilder ColumnSQL = new StringBuilder();
OutputDriver.IsBulkOperation = true;
foreach (string column in VariableList)
{
string columnName = String.Empty;
if (!column.Contains(".") && !OutputDriver.ColumnExists(TableName, column))
{
//add column
columnName = column;
Query qr = OutputDriver.CreateQuery("alter table [" + TableName + "] add [" + columnName + "] " + DBReadExecute.SQLGetType(CurrentDataTable.Columns[column]));
OutputDriver.ExecuteNonQuery(qr);
}
ColumnSQL.Append(" [");
ColumnSQL.Append(column);
ColumnSQL.Append("],");
}
OutputDriver.IsBulkOperation = false;
ColumnSQL.Length = ColumnSQL.Length - 1;
//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.ToUpper()))
{
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.";
}
}
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;
}