public static List<Column> AddPrimaryKeysToTop(List<Column> columns, string tableName)
{
List<PrimaryKey> pkeys = DataLayer.Configuration.GetPrimaryKey(tableName);
if (pkeys.Count != columns.Count)
{
foreach (PrimaryKey pkey in pkeys)
{
columns.Remove(columns.FirstOrDefault(x => x.ColumnName == pkey.Key));
Column column = new Column();
int j = 100;
for (int i = 0; i < columns.Count; i++)
{
if (columns[i].ColumnName != pkey.Key)
{
j = i - 1;
}
}
if (j != 100)
columns.Insert(j, column);
else
{
columns.Insert(0, column);
}
}
}
return columns;
}
public static List<Database> GetAllData()
{
List<Database> databases = new List<Database>();
XmlDocument xmlDoc = new XmlDocument();
xmlDoc.Load(filePath);
XmlNodeList nodelistDB = xmlDoc.DocumentElement.SelectNodes("/Databases/DataBase");
if (nodelistDB != null)
{
foreach (XmlNode nodeDB in nodelistDB)
{
Database database = new Database();
XmlNode DatabaseName = nodeDB.Attributes.GetNamedItem("dataBaseName");
if (DatabaseName != null)
database.DatabaseName = DatabaseName.Value;
XmlNodeList nodelistTB = xmlDoc.DocumentElement.SelectNodes("/Databases/DataBase/Tables/Table");
List<Table> tablesList = new List<Table>();
foreach (XmlNode nodeTB in nodelistTB)
{
if (nodeTB != null && nodeTB.ParentNode.ParentNode == nodeDB)
{
Table table = new Table();
XmlNode tablename = nodeTB.Attributes.GetNamedItem("tableName");
if (tablename != null)
table.TableName = tablename.Value;
XmlNode fileName = nodeTB.Attributes.GetNamedItem("fileName");
if (fileName != null)
table.FileName = fileName.Value;
XmlNode rowLength = nodeTB.Attributes.GetNamedItem("rowLength");
if (rowLength != null)
table.RowLength = int.Parse(rowLength.Value);
XmlNodeList nodeListCo = xmlDoc.DocumentElement.SelectNodes("/Databases/DataBase/Tables/Table/Structure/Attribute");
List<Column> columnsList = new List<Column>();
foreach (XmlNode nodeCo in nodeListCo)
{
if (nodeCo != null && nodeCo.ParentNode.ParentNode == nodeTB)
{
Column column = new Column();
XmlNode ColumnName = nodeCo.Attributes.GetNamedItem("attributeName");
if (ColumnName != null)
column.ColumnName = ColumnName.Value;
XmlNode Type = nodeCo.Attributes.GetNamedItem("type");
if (Type != null)
column.Type = Type.Value;
XmlNode Length = nodeCo.Attributes.GetNamedItem("length");
if (Type != null)
column.Length = int.Parse(Length.Value);
XmlNode isnull = nodeCo.Attributes.GetNamedItem("isnull");
if (Type != null)
column.IsNull = Utilities.GetBoolValueFromString(isnull.Value);
columnsList.Add(column);
}
}
table.Columns = columnsList;
XmlNodeList nodeListPK = xmlDoc.DocumentElement.SelectNodes("/Databases/DataBase/Tables/Table/primaryKey/pkAttribute");
if (nodeListPK != null)
{
List<PrimaryKey> primaryKeysList = new List<PrimaryKey>();
foreach (XmlNode nodePK in nodeListPK)
{
if (nodePK != null && nodePK.ParentNode.ParentNode == nodeTB)
{
PrimaryKey primaryKey = new PrimaryKey();
primaryKey.Key = nodePK.InnerText;
primaryKeysList.Add(primaryKey);
}
}
table.PrimaryKey = primaryKeysList;
}
XmlNodeList nodeListUK = xmlDoc.DocumentElement.SelectNodes("/Databases/DataBase/Tables/Table/uniqueKeys/UniqueAttribute");
if (nodeListUK.Count > 0)
{
List<UniqueKey> uniqueKeysList = new List<UniqueKey>();
foreach (XmlNode nodeUK in nodeListUK)
{
if (nodeUK != null && nodeUK.ParentNode.ParentNode == nodeTB)
{
UniqueKey uniqueKey = new UniqueKey();
uniqueKey.Key = nodeUK.InnerText;
uniqueKeysList.Add(uniqueKey);
}
}
table.UniqueKey = uniqueKeysList;
}
XmlNodeList nodeListIF = xmlDoc.DocumentElement.SelectNodes("/Databases/DataBase/Tables/Table/IndexFiles/IndexFile");
if (nodeListIF.Count > 0)
{
List<IndexFile> indexfilelist = new List<IndexFile>();
foreach (XmlNode nodeIF in nodeListIF)
{
if (nodeIF != null && nodeIF.ParentNode.ParentNode == nodeTB)
{
IndexFile indexfile = new IndexFile();
XmlNode IndexName = nodeIF.Attributes.GetNamedItem("indexName");
if (IndexName != null)
indexfile.IndexName = IndexName.Value;
XmlNode KeyLength = nodeIF.Attributes.GetNamedItem("keyLength");
if (KeyLength != null)
indexfile.KeyLength = int.Parse(KeyLength.Value);
XmlNode IsUnique = nodeIF.Attributes.GetNamedItem("isUnique");
if (IsUnique != null)
indexfile.IsUnique = Utilities.GetBoolValueFromString(IsUnique.Value);
XmlNode IndexType = nodeIF.Attributes.GetNamedItem("indexType");
if (IndexType != null)
indexfile.IndexType = IndexType.Value;
XmlNode IndexAttributeNode = nodeIF.FirstChild.FirstChild;
if (IndexAttributeNode.Name == "IAttribute" && IndexAttributeNode.InnerText != "")
indexfile.IndexAttribute = IndexAttributeNode.InnerText;
indexfilelist.Add(indexfile);
}
}
table.IndexFile = indexfilelist;
}
XmlNodeList nodeListFK = xmlDoc.DocumentElement.SelectNodes("/Databases/DataBase/Tables/Table/foreignKeys/foreignKey");
if (nodeListFK.Count > 0)
{
List<ForeignKey> FKlist = new List<ForeignKey>();
foreach (XmlNode nodeFK in nodeListFK)
{
if (nodeFK.ParentNode.ParentNode == nodeTB)
{
ForeignKey fk = new ForeignKey();
fk.Key = nodeFK.FirstChild.InnerText;
XmlNode nodeFKRef = nodeFK.LastChild;
fk.RefTable = nodeFKRef.FirstChild.InnerText;
fk.RefAttribute = nodeFKRef.LastChild.InnerText;
FKlist.Add(fk);
}
}
table.ForeignKey = FKlist;
}
tablesList.Add(table);
}
}
database.Tables = tablesList;
databases.Add(database);
}
}
return databases;
}
public static List<Column> GetTableColumns(string databaseName, string tableName)
{
XmlDocument xmlDoc = new XmlDocument();
xmlDoc.Load(filePath);
XmlNodeList attrNodeList = xmlDoc.DocumentElement.SelectNodes("/Databases/DataBase/Tables/Table/Structure/Attribute");
List<Column> listcolumns = new List<Column>();
foreach (XmlNode node in attrNodeList)
{
XmlNode parentNodeName = node.ParentNode.ParentNode.Attributes.GetNamedItem("tableName");
if (parentNodeName.Value.ToString() == tableName && node.ParentNode.ParentNode.ParentNode.ParentNode.Attributes.GetNamedItem("dataBaseName").Value.ToString() == databaseName)
{
Column column = new Column();
XmlNode columnName = node.Attributes.GetNamedItem("attributeName");
if (columnName != null)
column.ColumnName = columnName.Value;
XmlNode Type = node.Attributes.GetNamedItem("type");
if (Type != null)
{
column.Type = Type.Value;
}
XmlNode Length = node.Attributes.GetNamedItem("length");
if (Length != null)
{
column.Length = Convert.ToInt32(Length.Value.ToString());
}
XmlNode IsNull = node.Attributes.GetNamedItem("isnull");
if (IsNull != null)
{
column.IsNull = Utilities.GetBoolValueFromString(IsNull.Value.ToString());
}
listcolumns.Add(column);
}
}
if (listcolumns.Count > 0)
return listcolumns;
else return null;
}
private void button1_Click(object sender, EventArgs e)
{
if (listBox2.Items.Count == 0)
{
MessageBox.Show("You need to select a database.");
return;
}
if (string.IsNullOrEmpty(txtTBName.Text.Replace(" ", "")))
{
MessageBox.Show("You need to insert a table name");
return;
}
if (dataGridView1.Rows.Count <= 1)
{
MessageBox.Show("You need to add a column at least.");
return;
}
List<Database> list = DataLayer.Configuration.GetAllData();
foreach (var item in listBox2.Items)
{
if (list.FirstOrDefault(x => x.DatabaseName == item.ToString()).Tables.Count(x => x.TableName == txtTBName.Text) > 0)
{
MessageBox.Show("There already exists a table with this name!");
return;
}
}
Table table = new Table();
table.TableName = txtTBName.Text;
List<Column> columns = new List<Column>();
List<PrimaryKey> pklist = new List<PrimaryKey>();
List<UniqueKey> ukList = new List<UniqueKey>();
List<ForeignKey> fkList = new List<ForeignKey>();
foreach (DataGridViewRow row in dataGridView1.Rows)
{
Column column = new Column();
if (row.Cells[0].Value != null)
if (!string.IsNullOrEmpty(row.Cells[0].Value.ToString()))
column.ColumnName = row.Cells[0].Value.ToString();
if (row.Cells[1].Value != null)
{
if (!string.IsNullOrEmpty(row.Cells[1].Value.ToString()))
column.Type = row.Cells[1].Value.ToString();
}
if (row.Cells[2].Value != null)
{
column.IsNull = Utils.Utilities.GetBoolFromString(row.Cells[2].Value.ToString());
}
if (!string.IsNullOrEmpty(column.ColumnName) && !string.IsNullOrEmpty(column.Type))
columns.Add(column);
if (row.Cells[3].Value != null)
{
int parse = 0;
Int32.TryParse(row.Cells[3].Value.ToString(), out parse);
if (parse != 0)
column.Length = parse;
}
PrimaryKey pk = new PrimaryKey();
if (row.Cells[4].Value != null && Utils.Utilities.GetBoolFromString(row.Cells[4].Value.ToString()))
{
pk.Key = row.Cells[0].Value.ToString();
pklist.Add(pk);
}
UniqueKey uk = new UniqueKey();
if (row.Cells[5].Value != null && Utils.Utilities.GetBoolFromString(row.Cells[5].Value.ToString()))
{
uk.Key = row.Cells[0].Value.ToString();
ukList.Add(uk);
}
ForeignKey fKey = new ForeignKey();
if (row.Cells[6].Value != null)
{
fKey.Key = row.Cells[0].Value.ToString();
string[] key = Utils.Utilities.Split(row.Cells[6].Value.ToString());
fKey.RefTable = key[0];
fKey.RefAttribute = key[1];
fkList.Add(fKey);
}
}
foreach (DataGridViewRow row in dataGridView1.Rows)
{
PrimaryKey pk = new PrimaryKey();
if (row.Cells[3].Value != null)
{
pk.Key = row.Cells[0].Value.ToString();
}
}
table.PrimaryKey = pklist;
table.UniqueKey = ukList;
table.ForeignKey = fkList;
table.Columns = columns;
List<Database> dbs = new List<Database>();
foreach (var item in listBox2.Items)
{
Database d = new Database();
if (item != null)
d.DatabaseName = item.ToString();
d.Tables = new List<Table>();
d.Tables.Add(table);
dbs.Add(d);
}
DataLayer.Configuration.AddTable(dbs);
MessageBox.Show("Table " + table.TableName + " created successfully!");
}