public DialogInsertingForm(DatabaseHelper dbHelper, TableList tablesList)
{
this.dbHelper = dbHelper;
this.tablesList = tablesList;
InitializeComponent();
getLocalizedLabelsMessages();
getDefaultValues();
}
public InsertDataForm(StartScreenForm startForm, TableList tablesList)
{
this.startForm = startForm;
InitializeComponent();
this.dbHelper = new DatabaseHelper(startForm.Template.DBconfig);
this.TablesList = tablesList;
getLocalizedLabelsMessages();
getDefaultValues();
createTablesView();
}
public ExecuteSQLForm(TableList tablesList, DBConfig dbconfig, Boolean isInserting)
{
this.isInserting = isInserting;
this.dbconfig = dbconfig;
this.tablesList = tablesList;
this.dbHelper = new DatabaseHelper(dbconfig);
InitializeComponent();
getLocalizedLabelsMessages();
getDefaultValues();
}
public ChangeColumnForm(List<Table> tables, DBConfig dbconfig)
{
this.TablesList = new TableList(tables);
this.dbconfig = dbconfig;
this.dbHelper = new DatabaseHelper(dbconfig);
InitializeComponent();
getLocalizedLabelsMessages();
getDefaultValues();
createTablesView();
}
public TablesForm(TableList tablesList, StartScreenForm startForm)
{
this.startForm = startForm;
InitializeComponent();
getLocalizedLabelsMessages();
this.tablesList = tablesList;
this.Icon = Properties.Resources.icooddb;
createTablesTabsView(tablesList.Tables);
updateSQLCreateTables();
getDefaultValues();
}
public void readTableLayoutFile(String tableLayoutFilePath, ref TableList tablesList)
{
int countTable = 0;
int countColumn = 0;
foreach (String s in FileUtil.openFile(tableLayoutFilePath))
{
if (countTable == 0 && !s.Contains("Table1"))
return;
if (!s.Equals("=="))
{
List<String> values = s.Split(';').ToList();
if (values[0].Split(':')[0].Equals(tablesList.Tables[countTable].OriginalTableName))
{
if (values[0].Split(':')[1] != null && !values[0].Split(':')[1].Equals(""))
{
tablesList.Tables[countTable].TableName = values[1];
}
}
else if (values[0].Split(':')[0].Equals(tablesList.Tables[countTable].Columns[countColumn].OriginalColumnName))
{
if (values[0].Split(':')[1] != null && !values[0].Split(':')[1].Equals(""))
tablesList.Tables[countTable].Columns[countColumn].ColumnName = values[1];
if (values[1].Split(':')[0] != null && values[1].Split(':')[0].Equals("PK"))
if (values[1].Split(':')[1] != null)
tablesList.Tables[countTable].Columns[countColumn].IsPK = Boolean.Parse(values[1].Split(':')[1]);
if (values[2].Split(':')[0] != null && values[1].Split(':')[0].Equals("FK"))
{
if (values[2].Split(':')[1] != null)
tablesList.Tables[countTable].Columns[countColumn].IsPK = Boolean.Parse(values[2].Split(':')[1]);
if (tablesList.Tables[countTable].Columns[countColumn].IsPK)
{
KeyValue reference = new KeyValue();
if (values[3].Split(':')[0].Equals("ReferenceDB") && values[3].Split(':')[1] != null)
reference.Key = values[3].Split(':')[1];
if (values[4].Split(':')[0].Equals("ReferenceTableColumn") && values[4].Split(':')[1] != null)
reference.Value = values[4].Split(':')[1];
}
else
{
tablesList.Tables[countTable].Columns[countColumn].Reference = null;
}
countColumn++;
}
}
}
else
{
countTable++;
}
}
}
private static String getSQLRows(TableList tablesList, int limit)
{
StringBuilder sql = new StringBuilder();
foreach (Table t in tablesList.Tables)
{
List<Column> columnsExcluded = t.getListColumnsExcluded();
List<String> columnNames = t.columnNames();
if ((columnNames != null && columnNames.Count > 0)
&& (t.Rows != null && t.Rows.Count > 0))
{
sql.Append("INSERT INTO " + t.TableName);
sql.Append("(");
sql.Append(t.columnNames()[0]);
for (int i = 1; i < t.columnNames().Count(); i++)
sql.Append("," + t.columnNames()[i]);
sql.Append(") ");
sql.Append(Environment.NewLine);
sql.Append("VALUES ");
sql.Append(Environment.NewLine);
sql.Append("(" + t.Rows[0].ToString(columnsExcluded) + ")");
if (limit > -1)
{
for (int i = 1; i < limit && i < t.Rows.Count; i++)
{
sql.Append(", " + Environment.NewLine + "(" + t.Rows[i].ToString(columnsExcluded) + ")");
}
sql.Append(";");
}
else
{
for (int i = 1; i < t.Rows.Count; i++)
{
sql.Append(", " + Environment.NewLine + "(" + t.Rows[i].ToString(columnsExcluded) + ")");
}
sql.Append(";");
}
}
}
return sql.ToString();
}
public String createNewTableLayout(TableList tablesList)
{
StringBuilder fileContent = new StringBuilder("");
foreach (Table t in tablesList.Tables)
{
fileContent.Append(t.OriginalTableName + ":" + t.TableName + "\n");
fileContent.Append("{\n");
foreach (Column c in t.Columns)
{
fileContent.Append(c.OriginalColumnName + ":" + c.ColumnName + ";");
fileContent.Append("PK:" + c.IsPK.ToString() + ";");
if (c.IsFK)
{
fileContent.Append("FK:" + c.IsFK.ToString() + ";");
fileContent.Append("ReferenceDB:" + c.Reference.Key + ";");
fileContent.Append("ReferenceTableColumn:" + c.Reference.Value.ToString() + ";");
}
fileContent.Append("\n");
}
fileContent.Append("}\n");
}
return fileContent.ToString();
}
private void btnOpenFile_Click_1(object sender, EventArgs e)
{
String validationMsg = "";
openDataFileDialog = new OpenFileDialog();
openDataFileDialog.Filter = "(*.csv, *.txt)|*.csv;*.txt";//"(*.xml, *.csv, *.txt)|*.xml;*.csv;*.txt" "|*.xml;*.csv;*.txt";
openDataFileDialog.Title = openFileDialogTitleMsg;
DialogResult result = openDataFileDialog.ShowDialog();
if ("OK".Equals(result.ToString()))
{
filePath = openDataFileDialog.FileName.ToString();
if (filePath.EndsWith("XML") || filePath.EndsWith("xml"))
{
if (XMLFileUtil.IsValidXML(filePath, out validationMsg))
{
this.txbDataFilePath.Text = filePath;
this.lblTemplateFile.Enabled = true;
this.lblTemplateHelp.Enabled = true;
this.txbTemplateFilePath.Text = "";
this.txbTemplateFilePath.Enabled = true;
this.btnOpenTemplate.Enabled = true;
//openFileDialog.Dispose();
//List<Table> tables = XMLFileUtil.getTablesFromXMLFileNew(filePath);
// TablesForm tablesForm = new TablesForm(tables, this);
// tablesForm.Show();
// this.Hide();
}
else
{
filePath = "";
this.lblTemplateFile.Enabled = false;
this.lblTemplateHelp.Enabled = false;
this.txbTemplateFilePath.Text = "";
this.txbTemplateFilePath.Enabled = false;
this.btnOpenTemplate.Enabled = false;
showErrorInfoWindow(msgErrorInvalidDataFile + ":" + System.Environment.NewLine + validationMsg);
}
}
else if ((filePath.EndsWith("CSV") || filePath.EndsWith("csv"))
|| (filePath.EndsWith("TXT") || filePath.EndsWith("txt")))
{
this.txbDataFilePath.Text = filePath;
this.lblTemplateFile.Enabled = true;
this.lblTemplateHelp.Enabled = true;
this.txbTemplateFilePath.Text = "";
this.txbTemplateFilePath.Enabled = true;
this.btnOpenTemplate.Enabled = true;
Table table = FileUtil.getTableFromFile(filePath);
List<Table> tables = new List<Table>();
tables.Add(table);
tableList = new TableList(tables);
// TablesForm tablesForm = new TablesForm(tables, this);
// tablesForm.Show();
// this.Hide();
}
else
{
filePath = "";
this.lblTemplateFile.Enabled = false;
this.lblTemplateHelp.Enabled = false;
this.txbTemplateFilePath.Text = "";
this.txbTemplateFilePath.Enabled = false;
this.btnOpenTemplate.Enabled = false;
showErrorInfoWindow(msgErrorInvalidDataFile + ":" + System.Environment.NewLine + msgInvalidExtension);
}
}
}
public static String insertRows(TableList tablesList)
{
return SQLGenerator.getSQLRows(tablesList, -1);
}
public Template getTemplateFile(String templateFilePath)
{
this.templateFilePath = templateFilePath;
List<String> file = openFile(this.templateFilePath);
DBConfig dbConfig = new DBConfig();
TableList tablelist = new TableList(new List<Table>());
Table table = null;
foreach (String s in file)
{
if (!s.Equals(""))
{
if ("//".Equals(s))
tablelist.Tables.Add(table);
else
{
String key = s.Split('=')[0];
String value = s.Split('=')[1];
if ("Db".Equals(key))
dbConfig.Db = value;
if ("Server".Equals(key))
dbConfig.Server = value;
if ("Port".Equals(key))
dbConfig.Port = value;
if ("User".Equals(key))
dbConfig.User = value;
if ("Password".Equals(key))
dbConfig.Password = value;
if("DbName".Equals(key))
dbConfig.DbName = value;
if ("Table".Equals(key))
{
table = new Table();
table.OriginalTableName = value.Split(':')[0];
table.TableName = value.Split(':')[1];
table.Columns = new List<Column>();
}
if ("Column".Equals(key))
{
String columnNames = value.Split(';')[0];
String sqlType = value.Split(';')[1];
String pk = value.Split(';')[2];
String fk = value.Split(';')[3];
String nullabe = value.Split(';')[4];
String unique = value.Split(';')[5];
String defValue = value.Split(';')[6];
String defValueOnError = value.Split(';')[7];
String referenceDB = value.Split(';')[8];
String referenceTableColum = value.Split(';')[9];
String skip = value.Split(';')[10];
Column column = new Column(columnNames.Split(':')[0]);
column.ColumnName = columnNames.Split(':')[1] == null || columnNames.Split(':')[1].Equals("") ? column.ColumnName : columnNames.Split(':')[1];
column.sqlType = sqlType.Split(':')[1] == null || sqlType.Split(':')[1].Equals("") ? column.sqlType : sqlType.Split(':')[1];
column.IsPK = pk.Split(':')[1] != null && pk.Split(':')[1].ToLower().Equals("true");
column.IsFK = fk.Split(':')[1] != null && fk.Split(':')[1].ToLower().Equals("true");
column.IsNullable = nullabe.Split(':')[1] != null && nullabe.Split(':')[1].ToLower().Equals("true");
column.IsUnique = unique.Split(':')[1] != null && unique.Split(':')[1].ToLower().Equals("true");
column.defaulValue = defValue.Split(':')[1] != null ? defValue.Split(':')[1] : "";
column.defaulValueOnError = defValueOnError.Split(':')[1] != null ? defValueOnError.Split(':')[1] : "";
column.SKIP = skip.Split(':')[1] != null && skip.Split(':')[1].ToLower().Equals("true");
if (column.IsFK)
column.Reference = new VO.KeyValue(referenceDB.Split(':')[1], referenceTableColum.Split(':')[1]);
table.Columns.Add(column);
}
}
}
}
return new Template(tablelist, dbConfig);
}
public Template(TableList TableList, DBConfig DBconfig)
{
this.TableList = TableList;
this.DBconfig = DBconfig;
}