/// <summary> /// Adds a column to the table /// </summary> /// <param name="tableName">name of the table</param> /// <param name="column">The column</param> /// <returns>Boolean</returns> public abstract bool AddColumn(string tableName, TableColumn column);
/// <summary>
/// Adds a column to the table
/// </summary>
/// <param name="tableName">name of the table</param>
/// <param name="column">The column</param>
/// <returns>Boolean</returns>
public override bool AddColumn(string tableName, TableColumn column)
{
StringBuilder sb = new StringBuilder();
sb.Append("ALTER TABLE ");
sb.Append(tableName);
sb.Append(" ADD COLUMN ");
sb.Append(column.Name);
sb.Append(" ");
sb.Append(GetDbSpecificColumnType(column.DataType));
if (column.Length != null)
{
sb.Append("(");
sb.Append(column.Length.Value.ToString());
sb.Append(") ");
}
ExecuteNonQuery(CreateQuery(sb.ToString()));
return false;
}
/// <summary>
/// Creates a table with a System.Data.DataTable
/// </summary>
/// <param name="tableName">Name of the table.</param>
/// <param name="dataTable">Table</param>
public void CreateTable(string tableName, System.Data.DataTable dataTable)
{
List<Epi.Data.TableColumn> columns = new List<Epi.Data.TableColumn>();
for (int i = 0; dataTable.Columns.Count > i; i++)
{
Epi.Data.TableColumn col = new Epi.Data.TableColumn(
dataTable.Columns[i].ColumnName,
GetGenericDbColumnType(dataTable.Columns[i]),
dataTable.Columns[i].AllowDBNull);
columns.Add(col);
}
dbDriver.CreateTable(tableName, columns);
foreach (DataRow row in dataTable.Rows)
{
InsertDataTableRow(tableName, dataTable, row);
}
}
/// <summary>
/// Adds a column to the table
/// </summary>
/// <param name="tableName">name of the table</param>
/// <param name="column">The column</param>
/// <returns>Boolean</returns>
public override bool AddColumn(string tableName, TableColumn column)
{
// E. Knudsen - TODO
return false;
}
/// <summary>
/// Creates a new <see cref="Epi.Fields.DDLColumnOfLegalValues"/> in <see cref="Epi.Fields.GridField"/>.
/// </summary>
/// <param name="column"><see cref="Epi.Fields.DDLColumnOfLegalValues"/></param>
/// <returns>Id of the newly created column.</returns>
public int CreateGridColumn(DDLColumnOfLegalValues column)
{
try
{
if (db.ColumnExists("metaGridColumns", "IsUniqueField") == false)
{
TableColumn tableColumn = new TableColumn("IsUniqueField", GenericDbColumnType.Boolean, true);
db.AddColumn("metaGridColumns", tableColumn);
}
Query insertQuery = db.CreateQuery("insert into metaGridColumns([Name], [TextColumnName], [SourceTableName], [Width], [Position], [FieldTypeId], [Text], [ShouldRepeatLast], [IsExclusiveTable], [IsReadOnly], [IsRequired], [IsUniqueField], [FieldId], [Sort]) " +
"values (@Name, @TextColumnName, @SourceTableName, @Width, @Position, @FieldTypeId, @Text, @ShouldRepeatLast, @IsExclusiveTable, @IsReadOnly, @IsRequired, @IsUniqueField, @FieldId, @Sort)");
insertQuery.Parameters.Add(new QueryParameter("@Name", DbType.String, column.Name));
insertQuery.Parameters.Add(new QueryParameter("@TextColumnName", DbType.String, column.TextColumnName));
insertQuery.Parameters.Add(new QueryParameter("@SourceTableName", DbType.String, column.SourceTableName));
insertQuery.Parameters.Add(new QueryParameter("@Width", DbType.Int32, column.Width));
insertQuery.Parameters.Add(new QueryParameter("@Position", DbType.Int32, column.Position));
insertQuery.Parameters.Add(new QueryParameter("@FieldTypeId", DbType.Int32, (int)column.GridColumnType));
insertQuery.Parameters.Add(new QueryParameter("@Text", DbType.String, column.Text));
insertQuery.Parameters.Add(new QueryParameter("@ShouldRepeatLast", DbType.Boolean, column.ShouldRepeatLast));
insertQuery.Parameters.Add(new QueryParameter("@IsExclusiveTable", DbType.Boolean, column.IsExclusiveTable));
insertQuery.Parameters.Add(new QueryParameter("@IsReadOnly", DbType.Boolean, column.IsReadOnly));
insertQuery.Parameters.Add(new QueryParameter("@IsRequired", DbType.Boolean, column.IsRequired));
insertQuery.Parameters.Add(new QueryParameter("@IsUniqueField", DbType.Boolean, column.IsUniqueField));
insertQuery.Parameters.Add(new QueryParameter("@FieldId", DbType.Int32, column.Grid.Id));
insertQuery.Parameters.Add(new QueryParameter("@Sort", DbType.Boolean, column.ShouldSort));
db.ExecuteNonQuery(insertQuery);
return GetMaxGridColumnId(column.Grid.Id);
}
catch (Exception ex)
{
throw new GeneralException("Could not create grid column in the database", ex);
}
finally
{
}
}
/// <summary>
/// Reset and Repopulate metaFieldTypes table.
/// </summary>
/// <param name="field">View.</param>
/// <returns></returns>
public void SynchronizeMetaFieldtypes(View view)
{
Query query = null;
ArrayList SysVarList= new ArrayList();
string conMetaViews = "metaViews";
try
{
Query SelectQuery = db.CreateQuery("select * from Metafieldtypes");
DataTable SysVars = db.Select(SelectQuery);
foreach (DataRow row in SysVars.Rows)
{
SysVarList.Add(row[ColumnNames.NAME].ToString());
}
///Verify that all system var columns exists in View.
if (db.TableExists(view.TableName))
{
if (db.ColumnExists(view.TableName, ColumnNames.RECORD_FIRST_SAVE_TIME) == false)
{
TableColumn tableFirstSaveColumn = new TableColumn(ColumnNames.RECORD_FIRST_SAVE_TIME, GenericDbColumnType.DateTime, true);
db.AddColumn(view.TableName, tableFirstSaveColumn);
}
if (db.ColumnExists(view.TableName, ColumnNames.RECORD_LAST_SAVE_TIME) == false)
{
TableColumn tableLastSaveColumn = new TableColumn(ColumnNames.RECORD_LAST_SAVE_TIME, GenericDbColumnType.DateTime, true);
db.AddColumn(view.TableName, tableLastSaveColumn);
}
}
// ---for suma
if (db.ColumnExists(conMetaViews, ColumnNames.EWEOrganization_KEY) == false)
{
TableColumn tableEWEOrganizationColumn = new TableColumn(ColumnNames.EWEOrganization_KEY, GenericDbColumnType.StringLong, true);
db.AddColumn(conMetaViews, tableEWEOrganizationColumn);
}
if (db.ColumnExists(conMetaViews, ColumnNames.EiWSOrganization_KEY) == false)
{
TableColumn tableEWEOrganizationKeyColumn = new TableColumn(ColumnNames.EiWSOrganization_KEY, GenericDbColumnType.StringLong, true);
db.AddColumn(conMetaViews, tableEWEOrganizationKeyColumn);
}
if (db.ColumnExists(conMetaViews, ColumnNames.EIWsForm_ID) == false)
{
TableColumn tableEIWSFormIdColumn = new TableColumn(ColumnNames.EIWsForm_ID, GenericDbColumnType.StringLong, true);
db.AddColumn(conMetaViews, tableEIWSFormIdColumn);
}
if (db.ColumnExists(conMetaViews, ColumnNames.EWEForm_ID) == false)
{
TableColumn tableEWEFormIdColumn = new TableColumn(ColumnNames.EWEForm_ID, GenericDbColumnType.StringLong, true);
db.AddColumn(conMetaViews, tableEWEFormIdColumn);
}
//-------
///
foreach (AppDataSet.FieldTypesRow fieldType in AppData.Instance.FieldTypesDataTable.Rows)
{
if ((SysVarList.Contains(fieldType.Name) == false))
{
query = db.CreateQuery("insert into metaFieldTypes ([FieldTypeId], [Name], [HasRepeatLast], [HasRequired], [HasReadOnly], [HasRetainImageSize], [HasFont], [IsDropDown], [IsGridColumn], [DataTypeId], [IsSystem], [DefaultPatternId]) values (@FieldTypeId, @Name, @HasRepeatLast, @HasRequired, @HasReadOnly, @HasRetainImageSize, @HasFont, @IsDropDown, @IsGridColumn, @DataTypeId, @IsSystem, @DefaultPatternId)");
query.Parameters.Add(new QueryParameter("@FieldTypeId", DbType.Int32, fieldType.FieldTypeId));
query.Parameters.Add(new QueryParameter("@Name", DbType.String, fieldType.Name));
query.Parameters.Add(new QueryParameter("@HasRepeatLast", DbType.Boolean, fieldType.HasRepeatLast));
query.Parameters.Add(new QueryParameter("@HasRequired", DbType.Boolean, fieldType.HasRequired));
query.Parameters.Add(new QueryParameter("@HasReadOnly", DbType.Boolean, fieldType.HasReadOnly));
query.Parameters.Add(new QueryParameter("@HasRetainImageSize", DbType.Boolean, fieldType.HasRetainImageSize));
query.Parameters.Add(new QueryParameter("@HasFont", DbType.Boolean, fieldType.HasFont));
query.Parameters.Add(new QueryParameter("@IsDropDown", DbType.Boolean, fieldType.IsDropDown));
query.Parameters.Add(new QueryParameter("@IsGridColumn", DbType.Boolean, fieldType.IsGridColumn));
if (fieldType.IsDataTypeIdNull())
query.Parameters.Add(new QueryParameter("@DataTypeId", DbType.Int32, DBNull.Value));
else
query.Parameters.Add(new QueryParameter("@DataTypeId", DbType.Int32, fieldType.DataTypeId));
query.Parameters.Add(new QueryParameter("@IsSystem", DbType.Boolean, fieldType.IsSystem));
query.Parameters.Add(new QueryParameter("@DefaultPatternId", DbType.Int32, fieldType.DefaultPatternId));
db.ExecuteNonQuery(query);
}
}
}
catch (Exception ex)
{
throw new GeneralException("Unable to synchronize MetafieldTypes Table", ex);
}
finally
{
}
}
/// <summary>
/// Creates a new <see cref="Epi.Fields.GlobalRecordIdColumn"/> in <see cref="Epi.Fields.GridField"/>.
/// </summary>
/// <param name="column"><see cref="Epi.Fields.GlobalRecordId"/></param>
/// <returns>Id of the newly created column.</returns>
public int CreateGridColumn(GlobalRecordIdColumn column)
{
try
{
#region InputValidation
if (column == null)
{
throw new ArgumentNullException("GlobalRecordIdColumn");
}
#endregion
if (db.ColumnExists("metaGridColumns", "IsUniqueField") == false)
{
TableColumn tableColumn = new TableColumn("IsUniqueField", GenericDbColumnType.Boolean, true);
db.AddColumn("metaGridColumns", tableColumn);
}
Query insertQuery = db.CreateQuery("insert into metaGridColumns([Name], [FieldTypeId], [Width], [Size], [Position], [Text], [ShouldRepeatLast], [IsRequired], [IsReadOnly], [FieldId], [IsUniqueField]) " +
"values (@Name, @FieldTypeId, @Width, @Size, @Position, @Text, @ShouldRepeatLast, @IsRequired, @IsReadOnly, @FieldId, @IsUniqueField)");
insertQuery.Parameters.Add(new QueryParameter("@Name", DbType.String, column.Name));
insertQuery.Parameters.Add(new QueryParameter("@FieldTypeId", DbType.Int32, (int)column.GridColumnType));
insertQuery.Parameters.Add(new QueryParameter("@Width", DbType.Int32, 0));
insertQuery.Parameters.Add(new QueryParameter("@Size", DbType.Int32, 0));
insertQuery.Parameters.Add(new QueryParameter("@Position", DbType.Int32, 0));
insertQuery.Parameters.Add(new QueryParameter("@Text", DbType.String, string.Empty));
insertQuery.Parameters.Add(new QueryParameter("@ShouldRepeatLast", DbType.Boolean, false));
insertQuery.Parameters.Add(new QueryParameter("@IsRequired", DbType.Boolean, false));
insertQuery.Parameters.Add(new QueryParameter("@IsReadOnly", DbType.Boolean, true));
insertQuery.Parameters.Add(new QueryParameter("@FieldId", DbType.Int32, column.Grid.Id));
insertQuery.Parameters.Add(new QueryParameter("@IsUniqueField", DbType.Boolean, true));
db.ExecuteNonQuery(insertQuery);
return GetMaxGridColumnId(column.Grid.Id);
}
catch (Exception ex)
{
throw new GeneralException("Could not create grid column in the database", ex);
}
finally
{
}
}
/// <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;
}
