public CountryNameEditor(EpiDataHelper dataHelper)
{
InitializeComponent();
Database = dataHelper.Database;
DataHelper = dataHelper;
IDbDriver db = DataHelper.Database; // lazy
if (!db.TableExists("codeCountryList"))
{
List <Epi.Data.TableColumn> tableColumns = new List <Epi.Data.TableColumn>();
tableColumns.Add(new Epi.Data.TableColumn("COUNTRY", GenericDbColumnType.String, 255, true, false));
db.CreateTable("codeCountryList", tableColumns);
}
Query selectQuery = Database.CreateQuery("SELECT * FROM [codeCountryList] ORDER BY COUNTRY");
bool exists = Database.TableExists("codeCountryList");
dg.ItemsSource = (Database.Select(selectQuery)).DefaultView;
}
/// <summary>
/// Handles the DoWorker event for the worker
/// </summary>
/// <param name="sender">Object that fired the event</param>
/// <param name="e">.NET supplied event parameters</param>
private void worker_DoWork(object sender, System.ComponentModel.DoWorkEventArgs e)
{
lock (syncLock)
{
System.Diagnostics.Stopwatch stopWatch = new System.Diagnostics.Stopwatch();
stopWatch.Start();
try
{
string tableName = (string)e.Argument;
SetGadgetStatusHandler requestUpdateStatus = new SetGadgetStatusHandler(RequestUpdateStatusMessage);
CheckForCancellationHandler checkForCancellation = new CheckForCancellationHandler(IsCancelled);
if (db.TableExists(tableName))
{
db.DeleteTable(tableName);
}
List <Epi.Data.TableColumn> tcList = dashboardHelper.GetFieldsAsListOfEpiTableColumns();
db.CreateTable(tableName, tcList);
DataView dv = dashboardHelper.DataSet.Tables[0].DefaultView;
System.Data.Common.DbDataReader dataReader = dv.ToTable().CreateDataReader();
db.InsertBulkRows("Select * From [" + tableName + "]", dataReader, requestUpdateStatus, checkForCancellation);
}
catch (Exception ex)
{
this.Dispatcher.BeginInvoke(new SetStatusDelegate(SetWarningMessage), ex.Message);
}
finally
{
stopWatch.Stop();
System.Diagnostics.Debug.Print("Export thread finished in " + stopWatch.Elapsed.ToString());
}
}
}
void WriteResourcesToDatabase(GlobalizationDataSet ds, IDbDriver db)
{
AppendToLog("Please wait...");
List<TableColumn> columns = new List<TableColumn>();
columns.Add(new TableColumn("AssemblyName", GenericDbColumnType.String, 255, false));
columns.Add(new TableColumn("ManifestResourceName", GenericDbColumnType.String, 255, false));
columns.Add(new TableColumn("ResourceName", GenericDbColumnType.String, 255, false));
columns.Add(new TableColumn("ResourceValue", GenericDbColumnType.StringLong, false));
columns.Add(new TableColumn("ResourceType", GenericDbColumnType.String, 255, false));
columns.Add(new TableColumn("ResourceVersion", GenericDbColumnType.String, 255, false));
columns.Add(new TableColumn("SourceCultureName", GenericDbColumnType.String, 255, false));
columns.Add(new TableColumn("SourceValue", GenericDbColumnType.StringLong, 255, false));
//columns.Add(new TableColumn("CreationDate", GenericDbColumnType.String, 255, false));
DbDriverInfo dbInfo = new DbDriverInfo();
dbInfo.DBName = "CulturalResources";
dbInfo.DBCnnStringBuilder = new System.Data.OleDb.OleDbConnectionStringBuilder();
dbInfo.DBCnnStringBuilder.ConnectionString = db.ConnectionString;
dbFactory.CreatePhysicalDatabase(dbInfo);
// db.CreateDatabase("CulturalResources");
string tableName = ds.CulturalResources.TableName;
db.CreateTable(tableName, columns);
StringBuilder sb = new StringBuilder();
sb.Append("insert into ");
sb.Append(tableName);
sb.Append(" (");
for (int x = 0; x < columns.Count; x++)
{
sb.Append(columns[x].Name);
if (x < columns.Count - 1)
{
sb.Append(", ");
}
}
sb.Append(") values ");
sb.Append(" (");
for (int x = 0; x < columns.Count; x++)
{
sb.Append("@");
sb.Append(columns[x].Name);
if (x < columns.Count - 1)
{
sb.Append(", ");
}
}
sb.Append(")");
Query insertQuery = db.CreateQuery(sb.ToString());
for (int x = 0; x < columns.Count; x++)
{
insertQuery.Parameters.Add(new QueryParameter("@" + columns[x].Name, DbType.String, columns[x].Name, columns[x].Name));
}
sb.Remove(0, sb.Length);
sb.Append("update [").Append(tableName);
sb.Append(StringLiterals.RIGHT_SQUARE_BRACKET).Append(StringLiterals.SPACE);
sb.Append("set").Append(StringLiterals.SPACE);
for (int x = 0; x < columns.Count; x++)
{
sb.Append(StringLiterals.LEFT_SQUARE_BRACKET);
sb.Append(columns[x].Name);
sb.Append(StringLiterals.RIGHT_SQUARE_BRACKET);
sb.Append(StringLiterals.EQUAL);
sb.Append("@NewValue").Append(StringLiterals.SPACE);
sb.Append(", ");
}
sb.Append("where ");
for (int x = 0; x < columns.Count; x++)
{
sb.Append(columns[x].Name).Append(StringLiterals.SPACE);
sb.Append(StringLiterals.EQUAL);
sb.Append("@OldValue");
if (columns.Count > 1)
{
sb.Append(" and");
}
}
Query updateQuery = db.CreateQuery(sb.ToString());
for (int x = 0; x < columns.Count; x++)
{
updateQuery.Parameters.Add(new QueryParameter("@NewValue", DbType.String, columns[x].Name, columns[x].Name));
updateQuery.Parameters.Add(new QueryParameter("@OldValue", DbType.String, columns[x].Name, columns[x].Name));
updateQuery.Parameters[1].SourceVersion = DataRowVersion.Original;
}
db.Update(ds.Tables[0], tableName, insertQuery, null);
}
/// <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);
}
void WriteResourcesToDatabase(GlobalizationDataSet ds, IDbDriver db)
{
AppendToLog("Please wait...");
List <TableColumn> columns = new List <TableColumn>();
columns.Add(new TableColumn("AssemblyName", GenericDbColumnType.String, 255, false));
columns.Add(new TableColumn("ManifestResourceName", GenericDbColumnType.String, 255, false));
columns.Add(new TableColumn("ResourceName", GenericDbColumnType.String, 255, false));
columns.Add(new TableColumn("ResourceValue", GenericDbColumnType.StringLong, false));
columns.Add(new TableColumn("ResourceType", GenericDbColumnType.String, 255, false));
columns.Add(new TableColumn("ResourceVersion", GenericDbColumnType.String, 255, false));
columns.Add(new TableColumn("SourceCultureName", GenericDbColumnType.String, 255, false));
columns.Add(new TableColumn("SourceValue", GenericDbColumnType.StringLong, 255, false));
//columns.Add(new TableColumn("CreationDate", GenericDbColumnType.String, 255, false));
DbDriverInfo dbInfo = new DbDriverInfo();
dbInfo.DBName = "CulturalResources";
dbInfo.DBCnnStringBuilder = new System.Data.OleDb.OleDbConnectionStringBuilder();
dbInfo.DBCnnStringBuilder.ConnectionString = db.ConnectionString;
dbFactory.CreatePhysicalDatabase(dbInfo);
// db.CreateDatabase("CulturalResources");
string tableName = ds.CulturalResources.TableName;
db.CreateTable(tableName, columns);
StringBuilder sb = new StringBuilder();
sb.Append("insert into ");
sb.Append(tableName);
sb.Append(" (");
for (int x = 0; x < columns.Count; x++)
{
sb.Append(columns[x].Name);
if (x < columns.Count - 1)
{
sb.Append(", ");
}
}
sb.Append(") values ");
sb.Append(" (");
for (int x = 0; x < columns.Count; x++)
{
sb.Append("@");
sb.Append(columns[x].Name);
if (x < columns.Count - 1)
{
sb.Append(", ");
}
}
sb.Append(")");
Query insertQuery = db.CreateQuery(sb.ToString());
for (int x = 0; x < columns.Count; x++)
{
insertQuery.Parameters.Add(new QueryParameter("@" + columns[x].Name, DbType.String, columns[x].Name, columns[x].Name));
}
sb.Remove(0, sb.Length);
sb.Append("update [").Append(tableName);
sb.Append(StringLiterals.RIGHT_SQUARE_BRACKET).Append(StringLiterals.SPACE);
sb.Append("set").Append(StringLiterals.SPACE);
for (int x = 0; x < columns.Count; x++)
{
sb.Append(StringLiterals.LEFT_SQUARE_BRACKET);
sb.Append(columns[x].Name);
sb.Append(StringLiterals.RIGHT_SQUARE_BRACKET);
sb.Append(StringLiterals.EQUAL);
sb.Append("@NewValue").Append(StringLiterals.SPACE);
sb.Append(", ");
}
sb.Append("where ");
for (int x = 0; x < columns.Count; x++)
{
sb.Append(columns[x].Name).Append(StringLiterals.SPACE);
sb.Append(StringLiterals.EQUAL);
sb.Append("@OldValue");
if (columns.Count > 1)
{
sb.Append(" and");
}
}
Query updateQuery = db.CreateQuery(sb.ToString());
for (int x = 0; x < columns.Count; x++)
{
updateQuery.Parameters.Add(new QueryParameter("@NewValue", DbType.String, columns[x].Name, columns[x].Name));
updateQuery.Parameters.Add(new QueryParameter("@OldValue", DbType.String, columns[x].Name, columns[x].Name));
updateQuery.Parameters[1].SourceVersion = DataRowVersion.Original;
}
db.Update(ds.Tables[0], tableName, insertQuery, null);
}
/// <summary>
/// Handles the DoWorker event for the worker
/// </summary>
/// <param name="sender">Object that fired the event</param>
/// <param name="e">.NET supplied event parameters</param>
private void datadriverWorker_DoWork(object sender, System.ComponentModel.DoWorkEventArgs e)
{
lock (syncLock)
{
System.Diagnostics.Stopwatch stopWatch = new System.Diagnostics.Stopwatch();
stopWatch.Start();
try
{
string tableName = (string)e.Argument;
SetGadgetStatusHandler requestUpdateStatus = new SetGadgetStatusHandler(RequestUpdateStatusMessage);
CheckForCancellationHandler checkForCancellation = new CheckForCancellationHandler(IsCancelled);
if (db.TableExists(tableName) && !db.ConnectionDescription.ToLower().Contains("excel"))
{
db.DeleteTable(tableName);
}
else if (db.TableExists(tableName))
{
this.Dispatcher.BeginInvoke(new SetStatusDelegate(SetErrorMessage), string.Format(SharedStrings.DASHBOARD_EXPORT_EXCEL_TABLE_OVERWRITE_FAIL, tableName));
stopWatch.Stop();
return;
}
dashboardHelper.PopulateDataSet(); // the only reason to call this is to see if any new user-defined vars have been added and apply them.
List <Epi.Data.TableColumn> tcList = dashboardHelper.GetFieldsAsListOfEpiTableColumns(useTabOrder);
allFieldsSelected = false; // TODO: Fix?
if (allFieldsSelected)
{
if (tcList.Count <= 250)
{
db.CreateTable(tableName, tcList);
DataView dv = dashboardHelper.DataSet.Tables[0].DefaultView;
DataTable table = dv.ToTable(false);
if (useTabOrder)
{
dashboardHelper.OrderColumns(table, true);
}
System.Data.Common.DbDataReader dataReader = table.CreateDataReader();
db.InsertBulkRows("Select * From [" + tableName + "]", dataReader, requestUpdateStatus, checkForCancellation);
}
else
{
Dictionary <string, List <Epi.Data.TableColumn> > fieldTableDictionary = new Dictionary <string, List <Epi.Data.TableColumn> >();
int totalTablesNeeded = (tcList.Count / 250) + 1;
int tableNumber = 0;
List <Epi.Data.TableColumn> tableTcList = new List <Epi.Data.TableColumn>();
for (int i = 0; i < tcList.Count; i++)
{
if (i % 250 == 0)
{
if (tableNumber != 0)
{
fieldTableDictionary.Add(tableName + tableNumber.ToString(), tableTcList);
}
tableTcList = new List <Epi.Data.TableColumn>();
tableNumber++;
}
tableTcList.Add(tcList[i]);
if (i == tcList.Count - 1)
{
fieldTableDictionary.Add(tableName + tableNumber.ToString(), tableTcList);
}
}
foreach (KeyValuePair <string, List <Epi.Data.TableColumn> > kvp in fieldTableDictionary)
{
if (db.TableExists(kvp.Key))
{
this.Dispatcher.BeginInvoke(new SetStatusDelegate(SetErrorMessage), string.Format(SharedStrings.DASHBOARD_EXPORT_WIDE_TABLE_FAIL, totalTablesNeeded.ToString()));
return;
}
}
foreach (KeyValuePair <string, List <Epi.Data.TableColumn> > kvp in fieldTableDictionary)
{
db.CreateTable(kvp.Key, kvp.Value);
}
foreach (KeyValuePair <string, List <Epi.Data.TableColumn> > kvp in fieldTableDictionary)
{
List <string> exportTableFields = new List <string>();
foreach (Epi.Data.TableColumn tc in kvp.Value)
{
exportTableFields.Add(tc.Name);
}
DataView dv = dashboardHelper.DataSet.Tables[0].DefaultView;
DataTable table = dv.ToTable(false, exportTableFields.ToArray());
if (useTabOrder)
{
dashboardHelper.OrderColumns(table, true);
}
System.Data.Common.DbDataReader dataReader = table.CreateDataReader(); //dv.ToTable().CreateDataReader();
db.InsertBulkRows("Select * From [" + kvp.Key + "]", dataReader, requestUpdateStatus, checkForCancellation);
}
}
}
else
{
List <Epi.Data.TableColumn> tcFilteredList = new List <Epi.Data.TableColumn>();
foreach (string columnName in exportFields)
{
foreach (Epi.Data.TableColumn tc in tcList)
{
if (tc.Name.Equals(columnName) && !tcFilteredList.Contains(tc))
{
tcFilteredList.Add(tc);
}
}
}
if (tcFilteredList.Count <= 250)
{
db.CreateTable(tableName, tcFilteredList);
DataView dv = dashboardHelper.DataSet.Tables[0].DefaultView;
DataTable table = dv.ToTable(false, exportFields.ToArray());
if (useTabOrder)
{
dashboardHelper.OrderColumns(table, true);
}
System.Data.Common.DbDataReader dataReader = table.CreateDataReader(); //dv.ToTable().CreateDataReader();
db.InsertBulkRows("Select * From [" + tableName + "]", dataReader, requestUpdateStatus, checkForCancellation);
}
else
{
Dictionary <string, List <Epi.Data.TableColumn> > fieldTableDictionary = new Dictionary <string, List <Epi.Data.TableColumn> >();
int totalTablesNeeded = (tcFilteredList.Count / 250) + 1;
int tableNumber = 0;
List <Epi.Data.TableColumn> tableTcList = new List <Epi.Data.TableColumn>();
for (int i = 0; i < tcFilteredList.Count; i++)
{
if (i % 250 == 0)
{
if (tableNumber != 0)
{
foreach (Epi.Data.TableColumn tc in tcFilteredList)
{
if (tc.Name == "GlobalRecordId")
{
if (!tableTcList.Contains(tc))
{
tableTcList.Add(tc);
}
}
}
fieldTableDictionary.Add(tableName + tableNumber.ToString(), tableTcList);
}
tableTcList = new List <Epi.Data.TableColumn>();
tableNumber++;
}
tableTcList.Add(tcFilteredList[i]);
if (i == tcFilteredList.Count - 1)
{
foreach (Epi.Data.TableColumn tc in tcFilteredList)
{
if (tc.Name == "GlobalRecordId")
{
if (!tableTcList.Contains(tc))
{
tableTcList.Add(tc);
}
}
}
fieldTableDictionary.Add(tableName + tableNumber.ToString(), tableTcList);
}
}
foreach (KeyValuePair <string, List <Epi.Data.TableColumn> > kvp in fieldTableDictionary)
{
if (db.TableExists(kvp.Key))
{
this.Dispatcher.BeginInvoke(new SetStatusDelegate(SetErrorMessage), string.Format(SharedStrings.DASHBOARD_EXPORT_WIDE_TABLE_FAIL, totalTablesNeeded.ToString()));
return;
}
}
foreach (KeyValuePair <string, List <Epi.Data.TableColumn> > kvp in fieldTableDictionary)
{
db.CreateTable(kvp.Key, kvp.Value);
}
foreach (KeyValuePair <string, List <Epi.Data.TableColumn> > kvp in fieldTableDictionary)
{
List <string> exportTableFields = new List <string>();
foreach (Epi.Data.TableColumn tc in kvp.Value)
{
exportTableFields.Add(tc.Name);
}
DataView dv = dashboardHelper.DataSet.Tables[0].DefaultView;
DataTable table = dv.ToTable(false, exportTableFields.ToArray());
if (useTabOrder)
{
dashboardHelper.OrderColumns(table, true);
}
System.Data.Common.DbDataReader dataReader = table.CreateDataReader(); //dv.ToTable().CreateDataReader();
db.InsertBulkRows("Select * From [" + kvp.Key + "]", dataReader, requestUpdateStatus, checkForCancellation);
}
}
}
}
catch (Exception ex)
{
this.Dispatcher.BeginInvoke(new SetStatusDelegate(SetErrorMessage), ex.Message);
}
finally
{
stopWatch.Stop();
System.Diagnostics.Debug.Print("Export thread finished in " + stopWatch.Elapsed.ToString());
}
}
}