/// <summary>
/// Create Physical Database
/// </summary>
/// <param name="dbInfo">Database driver information.</param>
public void CreatePhysicalDatabase(DbDriverInfo dbInfo)
{
string filepath = ((OleDbConnectionStringBuilder)dbInfo.DBCnnStringBuilder).DataSource;
//string filepath = dbInfo.DBName;
if (!string.IsNullOrEmpty(filepath))
{
//TODO: refactor this, make one function for all: ExtractTemplate(FileType, FilePath)
if (filepath.EndsWith(".mdb", true, CultureInfo.InvariantCulture))
{
ResourceLoader.ExtractAccess2003Template(filepath);
File.SetAttributes(filepath, FileAttributes.Normal);
return;
}
else if (filepath.EndsWith(".accdb", true, CultureInfo.InvariantCulture))
{
ResourceLoader.ExtractAccess2007Template(filepath);
File.SetAttributes(filepath, FileAttributes.Normal);
return;
}
else if (filepath.EndsWith(".xls", true, CultureInfo.InvariantCulture))
{
//ResourceLoader.ExtractTemplate(filepath);
//File.SetAttributes(filepath, FileAttributes.Normal);
//return;
}
}
}
/// <summary>
/// Create a database
/// </summary>
/// <param name="dbInfo">DbDriverInfo</param>
public void CreatePhysicalDatabase(DbDriverInfo dbInfo)
{
NpgsqlConnectionStringBuilder masterBuilder = new NpgsqlConnectionStringBuilder(dbInfo.DBCnnStringBuilder.ToString());
NpgsqlConnectionStringBuilder tempBuilder = new NpgsqlConnectionStringBuilder(dbInfo.DBCnnStringBuilder.ToString());
//tempBuilder = dbInfo.DBCnnStringBuilder as MySqlConnectionStringBuilder;
//The "test" database is installed by default with MySQL. System needs to login to this database to create a new database.
tempBuilder.Database = "information_schema";
NpgsqlConnection masterConnection = new NpgsqlConnection(tempBuilder.ToString());
try
{
NpgsqlCommand command = masterConnection.CreateCommand();
if (dbInfo.DBName != null)
{
command.CommandText = "create database " + dbInfo.DBName + ";";
}
masterConnection.Open();
//Logger.Log(command.CommandText);
command.ExecuteNonQuery();
//reset database to new database for correct storage of meta tables
tempBuilder.Database = dbInfo.DBName;
}
catch (Exception ex)
{
throw new System.ApplicationException("Could not create new MySQL Database", ex);//(Epi.SharedStrings.CAN_NOT_CREATE_NEW_MYSQL, ex);
}
finally
{
masterConnection.Close();
}
}
/// <summary>
/// Creates a physical database.
/// </summary>
/// <param name="dbInfo">Database driver information.</param>
public void CreatePhysicalDatabase(DbDriverInfo dbInfo)
{
try
{
SqlConnectionStringBuilder masterBuilder = new SqlConnectionStringBuilder(dbInfo.DBCnnStringBuilder.ToString());
masterBuilder.InitialCatalog = "Master";
//masterBuilder.IntegratedSecurity = true;
SqlConnection masterConnection = new SqlConnection(masterBuilder.ToString());
IDbCommand command = masterConnection.CreateCommand();
command.CommandText = string.Format("SELECT Count(*) FROM sysdatabases WHERE name='{0}'", dbInfo.DBName);
masterConnection.Open();
object result = command.ExecuteScalar();
if ((int)result == 0)
{
command.CommandText = "create database [" + dbInfo.DBName + "]";
//Logger.Log(command.CommandText);
command.ExecuteNonQuery();
}
masterConnection.Close();
}
catch (ApplicationException)
{
throw new System.ApplicationException("Could not create new SQL database. Please contact your SQL server administrator.");
}
finally
{
}
}
private void TearoffDbDriver(object sender, TabControlCancelEventArgs e)
{
if (e.TabPage.Controls.Count != 0)
{
// Already loaded.
//
return;
}
try
{
var info = (DbDriverInfo)e.TabPage.Tag;
info.Driver = _dbDriverManager.GetDriver(info.DriverName);
info.ConfigControl = info.Driver.CreateConfigControl();
info.ConfigControl.DockInPanel(e.TabPage);
info.ConfigControl.CustomInitialize(true);
info.ConfigControl.ConnectionStringChanged += ConfigConnectionStringChanged;
_curDriverInfo = (DbDriverInfo)tbcEngine.SelectedTab.Tag;
UpdateState(info.ConfigControl);
}
catch (Exception ex)
{
var errorLabel = new Label
{
Text = ex.GetBaseException().Message,
Dock = DockStyle.Fill
};
e.TabPage.Controls.Add(errorLabel);
}
}
/// <summary>
/// Default Constructor
/// </summary>
public ProjectCreationDialog()
{
InitializeComponent();
project = new Project();
metaDbInfo = new DbDriverInfo();
collectedDataDBInfo = new DbDriverInfo();
}
/// <summary>
/// Creates a project file and project object for a database file.
/// </summary>
/// <param name="databaseFileName">The database file name.</param>
/// <param name="createPRJFile">Whether or not to create the PRJ file on disk.</param>
/// <returns>An Epi Info project object</returns>
public static Project CreateProjectFileFromDatabase(string databaseFileName, bool createPRJFile)
{
if (IsDatabaseEpiProject(databaseFileName, false) == false)
{
return(null);
}
Project project = new Project();
string fileName = databaseFileName;
string directory = Path.GetDirectoryName(fileName);
if (fileName.ToLowerInvariant().EndsWith(".mdb"))
{
DbDriverInfo collectedDataDBInfo = new DbDriverInfo();
IDbDriverFactory collectedDBFactory = DbDriverFactoryCreator.GetDbDriverFactory("Epi.Data.Office.AccessDBFactory, Epi.Data.Office");// GetSelectedCollectedDataDriver();
string GUID = System.Guid.NewGuid().ToString();
collectedDataDBInfo.DBCnnStringBuilder = collectedDBFactory.RequestDefaultConnection(GUID, GUID);
FileInfo fi = new FileInfo(fileName);
project.Name = fi.Name.Substring(0, fi.Name.Length - 4);
project.Location = directory;
if (collectedDataDBInfo.DBCnnStringBuilder.ContainsKey("Provider"))
{
collectedDataDBInfo.DBCnnStringBuilder["Data Source"] = project.FilePath.Substring(0, project.FilePath.Length - 4) + ".mdb";
}
if (!Directory.Exists(project.Location))
{
Directory.CreateDirectory(project.Location);
}
project.Id = project.GetProjectId();
project.Description = string.Empty;
project.CollectedDataDbInfo = collectedDataDBInfo;
project.CollectedDataDriver = "Epi.Data.Office.AccessDBFactory, Epi.Data.Office";
project.CollectedDataConnectionString = collectedDataDBInfo.DBCnnStringBuilder.ToString();
Logger.Log(DateTime.Now + ": " + string.Format("Project [{0}] created in {1} by user [{2}].", project.Name, project.Location, System.Security.Principal.WindowsIdentity.GetCurrent().Name.ToString()));
project.MetadataSource = MetadataSource.SameDb;
if (createPRJFile)
{
try
{
project.Save();
}
catch (UnauthorizedAccessException ex)
{
return(null);
}
}
}
return(project);
}
//DbConnectionStringBuilder cnnInfo;
/// <summary>
/// Obtains the meta data collection string of the project file path input by the user
/// </summary>
private void GetMetaConnectionString()
{
IDbDriverFactory metaDBFactory = GetSelectedCollectedDataDriver();
DbDriverInfo dbInfo = new DbDriverInfo();
dbInfo.DBCnnStringBuilder = metaDBFactory.RequestNewConnection(txtCollectedData.Text.Trim());
this.MetaDBInfo = dbInfo;
if (this.metaDbInfo.DBCnnStringBuilder != null)
{
metadataConnectionString = this.metaDbInfo.DBCnnStringBuilder.ConnectionString;
}
}
public ProjectFromExcelDialog(string selectedExcel)
{
InitializeComponent();
SelectedExcelPath = selectedExcel;
project = new Project();
metaDbInfo = new DbDriverInfo();
collectedDataDBInfo = new DbDriverInfo();
Epi.Template template = new Epi.Template();
projectTable = template.GetProjectTable("Projects");
}
/// <summary>
/// Constructor for the class
/// </summary>
public ProjectCreationDialog(MainForm mainForm)
: base(mainForm)
{
#region Input Validation
if (mainForm == null)
{
throw new ArgumentNullException("mainform");
}
#endregion //Input Validation
InitializeComponent();
metaDbInfo = new DbDriverInfo();
collectedDataDBInfo = new DbDriverInfo();
txtProjectLocation.ReadOnly = true;
}
private void ConnectDatabase(DbDriverInfo info)
{
try
{
info.ConfigControl.BuildConnectionString();
if (info.Driver.CheckConnectionString(info.ConfigControl.ConnectionString))
{
info.ConfigControl.OnConnectSucceeded();
}
}
catch (Exception ex)
{
MessageBox.Show(this, ex.Message,
ApplicationInfo.ApplicationName,
MessageBoxButtons.OK, MessageBoxIcon.Error);
}
UpdateState(info.ConfigControl);
}
private void CreateDatabase(DbDriverInfo info)
{
try
{
if (!info.ConfigControl.PrepareCreateConnectionString())
{
return;
}
info.Driver.CreateSchemaDriver().CreateDatabase(info.ConfigControl.ConnectionString);
}
catch (Exception ex)
{
MessageBox.Show(this, ex.Message,
ApplicationInfo.ApplicationName,
MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
ConnectDatabase(info);
MessageBox.Show(this, "Database created successfully.",
ApplicationInfo.ApplicationName,
MessageBoxButtons.OK, MessageBoxIcon.Information);
}
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);
}
public void CreatePhysicalDatabase(DbDriverInfo dbInfo)
{
throw new NotImplementedException();
}
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);
}
private void tbcEngine_SelectedIndexChanged(object sender, EventArgs e)
{
_curDriverInfo = (DbDriverInfo)tbcEngine.SelectedTab.Tag;
UpdateState(_curDriverInfo.ConfigControl);
}
/// <summary>
/// Creates a physical database.
/// </summary>
/// <param name="dbInfo">Database driver information.</param>
public void CreatePhysicalDatabase(DbDriverInfo dbInfo)
{
try
{
SqlConnectionStringBuilder masterBuilder = new SqlConnectionStringBuilder(dbInfo.DBCnnStringBuilder.ToString());
masterBuilder.InitialCatalog = "Master";
//masterBuilder.IntegratedSecurity = true;
SqlConnection masterConnection = new SqlConnection(masterBuilder.ToString());
IDbCommand command = masterConnection.CreateCommand();
command.CommandText = string.Format("SELECT Count(*) FROM sysdatabases WHERE name='{0}'", dbInfo.DBName);
masterConnection.Open();
object result = command.ExecuteScalar();
if ((int)result == 0)
{
command.CommandText = "create database [" + dbInfo.DBName + "]";
//Logger.Log(command.CommandText);
command.ExecuteNonQuery();
}
masterConnection.Close();
}
catch (ApplicationException)
{
throw new System.ApplicationException("Could not create new SQL database. Please contact your SQL server administrator.");
}
finally
{
}
}
/// <summary>
/// Create a database
/// </summary>
/// <param name="dbInfo">DbDriverInfo</param>
public void CreatePhysicalDatabase(DbDriverInfo dbInfo)
{
NpgsqlConnectionStringBuilder masterBuilder = new NpgsqlConnectionStringBuilder(dbInfo.DBCnnStringBuilder.ToString());
NpgsqlConnectionStringBuilder tempBuilder = new NpgsqlConnectionStringBuilder(dbInfo.DBCnnStringBuilder.ToString());
//tempBuilder = dbInfo.DBCnnStringBuilder as MySqlConnectionStringBuilder;
//The "test" database is installed by default with MySQL. System needs to login to this database to create a new database.
tempBuilder.Database = "information_schema";
NpgsqlConnection masterConnection = new NpgsqlConnection(tempBuilder.ToString());
try
{
NpgsqlCommand command = masterConnection.CreateCommand();
if(dbInfo.DBName != null)
{
command.CommandText = "create database " + dbInfo.DBName + ";";
}
masterConnection.Open();
//Logger.Log(command.CommandText);
command.ExecuteNonQuery();
//reset database to new database for correct storage of meta tables
tempBuilder.Database = dbInfo.DBName;
}
catch (Exception ex)
{
throw new System.ApplicationException("Could not create new MySQL Database", ex);//(Epi.SharedStrings.CAN_NOT_CREATE_NEW_MYSQL, ex);
}
finally
{
masterConnection.Close();
}
}
private void ConnectDatabase(DbDriverInfo info)
{
try
{
info.ConfigControl.BuildConnectionString();
if (info.Driver.CheckConnectionString(info.ConfigControl.ConnectionString))
info.ConfigControl.OnConnectSucceeded();
}
catch (Exception ex)
{
MessageBox.Show(this, ex.Message,
ApplicationInfo.ApplicationName,
MessageBoxButtons.OK, MessageBoxIcon.Error);
}
UpdateState(info.ConfigControl);
}
private void CreateDatabase(DbDriverInfo info)
{
try
{
if (!info.ConfigControl.PrepareCreateConnectionString())
return;
info.Driver.CreateSchemaDriver().CreateDatabase(info.ConfigControl.ConnectionString);
}
catch (Exception ex)
{
MessageBox.Show(this, ex.Message,
ApplicationInfo.ApplicationName,
MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
ConnectDatabase(info);
MessageBox.Show(this, "Database created successfully.",
ApplicationInfo.ApplicationName,
MessageBoxButtons.OK, MessageBoxIcon.Information);
}
/// <summary>
/// Creates a project file and project object for a given database.
/// </summary>
/// <param name="connectionString">The database file name or connection string.</param>
/// <param name="createPRJFile">Whether or not to create the PRJ file on disk.</param>
/// <param name="prjFileName">The name of the desired PRJ file</param>
/// <param name="prjFileLocation">The directory to the desired PRJ file</param>
/// <returns>An Epi Info project object</returns>
public static Project CreateProjectFileFromDatabase(string connectionString, bool createPRJFile, string prjFileLocation = "", string prjFileName = "")
{
if (IsDatabaseEpiProject(connectionString, true) == false)
{
return(null);
}
Project project = new Project();
DbDriverInfo collectedDataDBInfo = new DbDriverInfo();
IDbDriverFactory collectedDBFactory = DbDriverFactoryCreator.GetDbDriverFactory("Epi.Data.SqlServer.SqlDBFactory, Epi.Data.SqlServer");
string GUID = System.Guid.NewGuid().ToString();
//collectedDataDBInfo.DBCnnStringBuilder = collectedDBFactory.RequestNewConnection(connectionInfo);
string[] pieces = connectionString.Split(';');
foreach (string piece in pieces)
{
if (!piece.ToLowerInvariant().StartsWith("password"))
{
string[] parts = piece.Split('=');
collectedDataDBInfo.DBCnnStringBuilder[parts[0]] = parts[1];
}
else
{
string[] parts = piece.Split('=');
int indexOf = piece.IndexOf("=");
string password = piece.Substring(indexOf + 1);
password = password.TrimStart('\"').TrimEnd('\"');;
collectedDataDBInfo.DBCnnStringBuilder[parts[0]] = password;
}
}
IDbDriver driver = collectedDBFactory.CreateDatabaseObject(collectedDataDBInfo.DBCnnStringBuilder);
project.CollectedData.Initialize(collectedDataDBInfo, "Epi.Data.SqlServer.SqlDBFactory, Epi.Data.SqlServer", false);
project.Name = prjFileName; //fi.Name.Substring(0, fi.Name.Length - 4);
project.Location = prjFileLocation; //directory;
project.Id = project.GetProjectId();
project.Description = string.Empty;
project.CollectedDataDbInfo = collectedDataDBInfo;
project.CollectedDataDriver = "Epi.Data.SqlServer.SqlDBFactory, Epi.Data.SqlServer";
project.CollectedDataConnectionString = connectionString;
project.MetadataSource = MetadataSource.SameDb;
project.Metadata.AttachDbDriver(project.CollectedData.GetDbDriver());
if (createPRJFile)
{
try
{
project.Save();
}
catch (UnauthorizedAccessException ex)
{
return(null);
}
}
return(project);
}
private void TearoffDbDriver(object sender, TabControlCancelEventArgs e)
{
if (e.TabPage.Controls.Count != 0)
{
// Already loaded.
//
return;
}
try
{
var info = (DbDriverInfo) e.TabPage.Tag;
info.Driver = _dbDriverManager.GetDriver(info.DriverName);
info.ConfigControl = info.Driver.CreateConfigControl();
info.ConfigControl.DockInPanel(e.TabPage);
info.ConfigControl.CustomInitialize(true);
info.ConfigControl.ConnectionStringChanged += ConfigConnectionStringChanged;
_curDriverInfo = (DbDriverInfo)tbcEngine.SelectedTab.Tag;
UpdateState(info.ConfigControl);
}
catch (Exception ex)
{
var errorLabel = new Label
{
Text = ex.GetBaseException().Message,
Dock = DockStyle.Fill
};
e.TabPage.Controls.Add(errorLabel);
}
}
private void tbcEngine_SelectedIndexChanged(object sender, EventArgs e)
{
_curDriverInfo = (DbDriverInfo)tbcEngine.SelectedTab.Tag;
UpdateState(_curDriverInfo.ConfigControl);
}
/// <summary>
/// Create Physical Database
/// </summary>
/// <param name="dbInfo">Database driver information.</param>
public void CreatePhysicalDatabase(DbDriverInfo dbInfo)
{
CreateDatabaseObject(dbInfo.DBCnnStringBuilder);
}
public ProjectFromTemplateDialog(string selectedTemplatePath)
{
SelectedTemplatePath = selectedTemplatePath;
InitializeComponent();
project = new Project();
metaDbInfo = new DbDriverInfo();
collectedDataDBInfo = new DbDriverInfo();
projectTemplateListView.Dock = DockStyle.None;
projectTemplateListView.View = System.Windows.Forms.View.Details;
projectTemplateListView.Sorting = System.Windows.Forms.SortOrder.Ascending;
projectTemplateListView.GridLines = false;
projectTemplateListView.Scrollable = true;
projectTemplateListView.FullRowSelect = true;
projectTemplateListView.AllowColumnReorder = true;
projectTemplateListView.HideSelection = false;
ColumnHeader templateName = new ColumnHeader();
templateName.Text = "Template Name";
templateName.Width = -1;
ColumnHeader creationDate = new ColumnHeader();
creationDate.Text = "Creation Date";
creationDate.Width = -1;
ColumnHeader description = new ColumnHeader();
description.Text = "Description";
description.Width = -1;
ColumnHeader path = new ColumnHeader();
path.Text = "Path";
path.Width = -1;
projectTemplateListView.Columns.AddRange(
new ColumnHeader[]
{
templateName,
creationDate,
description,
path
});
lvwColumnSorter = new ListViewColumnSorter();
projectTemplateListView.ListViewItemSorter = lvwColumnSorter;
Epi.Template template = new Template();
projectTable = template.GetProjectTable("Projects");
foreach (DataRow row in template.GetProjectTable("Forms").Rows)
{
projectTable.Rows.Add(row.ItemArray);
}
foreach (DataRow row in projectTable.Rows)
{
string itemText = row["TemplateName"].ToString();
if (string.IsNullOrEmpty(itemText) == false)
{
ListViewItem item = new ListViewItem(itemText, 0);
string subItemText = row["TemplateCreateDate"].ToString();
ListViewItem.ListViewSubItem subItem = new ListViewItem.ListViewSubItem();
subItem.Text = subItemText;
subItem.Tag = System.DateTime.Now;
item.SubItems.Add(subItem);
item.SubItems.Add("");
item.SubItems.Add(row["TemplatePath"].ToString());
item.Tag = row["TemplatePath"].ToString();
projectTemplateListView.Items.Add(item);
}
}
}
