public void getSchema() { try { if (conectionString.Contains("Provider=") == true) { //add logic } else if (conectionString.Contains("Driver=") == true) { database = con.Database.ToString(); tables.Clear(); columns.Clear(); tables = con.GetSchema("Tables"); columns = con.GetSchema("Columns"); if (conectionString.Contains("Microsoft Text Driver")) { databases.Columns.Add("Database"); databases.Rows.Add(database); } else { if (databases.Rows.Count <= 0) { databases.Columns.Add("Database"); OdbcCommand oCmd = new OdbcCommand("SHOW DATABASES", con); OdbcDataReader oDR = oCmd.ExecuteReader(); if (oDR.HasRows == true) { while (oDR.Read()) { for (int i = 0; i < oDR.FieldCount; i++) { databases.Rows.Add(oDR.GetValue(i).ToString()); } } } oDR.Close(); oCmd.Dispose(); } } } else { database = mycon.Database.ToString(); tables.Clear(); columns.Clear(); tables = mycon.GetSchema("Tables"); columns = mycon.GetSchema("Columns"); if (databases.Rows.Count <= 0) { databases.Columns.Add("Database"); MySqlCommand oCmd = new MySqlCommand("SHOW DATABASES", mycon); MySqlDataReader oDR = oCmd.ExecuteReader(); if (oDR.HasRows == true) { while (oDR.Read()) { for (int i = 0; i < oDR.FieldCount; i++) { databases.Rows.Add(oDR.GetValue(i).ToString()); } } } oDR.Close(); oCmd.Dispose(); } } if (conectionString.Contains("Microsoft Text Driver")) { DBschema.ConnectionName = "QDSCSV"; //BEGIN SCHEMA INFO - own Classes Beta foreach (DataRow bd in databases.Rows) { //if(bd[0].ToString() == database) { DatabaseSchema tmpDB = new DatabaseSchema(bd[0].ToString()); tmpDB.ConnectionName = DBschema.ConnectionName; foreach (DataRow tbl in tables.Rows) { if (tbl[0].ToString() == bd[0].ToString()) { TableSchema tmpTable = new TableSchema(tbl[2].ToString()); tmpTable.ConnectionName = DBschema.ConnectionName; tmpTable.DatabaseName = tmpDB.DatabaseName; if (tables.Columns.Contains("ENGINE") == true) { tmpTable.Engine = "CSV"; } foreach (DataRow col in columns.Rows) { if (tbl["TABLE_NAME"].ToString() == col["TABLE_NAME"].ToString()) { if (columns.Columns.Contains("COLUMN_NAME") == true) { ColumnSchema tmpCol = new ColumnSchema(col["COLUMN_NAME"].ToString()); tmpCol.ConnectionName = DBschema.ConnectionName; tmpCol.DatabaseName = tmpDB.DatabaseName; tmpCol.TableName = tmpTable.TableName; if (columns.Columns.Contains("COLUMN_KEY") == true) { if (col["COLUMN_KEY"].ToString() == "PRI") { tmpCol.PrimaryKey = true; } else { tmpCol.PrimaryKey = false; } } if (columns.Columns.Contains("ORDINAL_POSITION") == true) { tmpCol.ColumnPosition = int.Parse(col["ORDINAL_POSITION"].ToString()); } if (columns.Columns.Contains("TYPE_NAME") == true) { tmpCol.DataType = col["TYPE_NAME"].ToString(); } if (columns.Columns.Contains("COLUMN_DEFAULT") == true) { tmpCol.DefaultValue = col["COLUMN_DEFAULT"].ToString(); } if (columns.Columns.Contains("COLUMN_COMMENT") == true) { tmpCol.Comment = col["COLUMN_COMMENT"].ToString(); } if (columns.Columns.Contains("IS_NULLABLE") == true) { if (col["IS_NULLABLE"].ToString() == "YES") { tmpCol.NotNull = false; } else { tmpCol.NotNull = true; } } if (columns.Columns.Contains("EXTRA") == true) { if (col["EXTRA"].ToString() == "auto_increment") { tmpCol.AutoIncrement = true; } else { tmpCol.AutoIncrement = false; } } tmpTable.addColumn(tmpCol); } } } tmpDB.addTable(tmpTable); } //If Table = Table } //Foreach Table DBschema.addDatabase(tmpDB); //} }//Foreach Database //END SCHEMA INFO } else { DBschema.ConnectionName = "QDS"; //BEGIN SCHEMA INFO - own Classes Beta foreach (DataRow bd in databases.Rows) { //if(bd[0].ToString() == database) { DatabaseSchema tmpDB = new DatabaseSchema(bd[0].ToString()); tmpDB.ConnectionName = DBschema.ConnectionName; foreach (DataRow tbl in tables.Rows) { if (tbl["TABLE_SCHEMA"].ToString() == bd[0].ToString()) { TableSchema tmpTable = new TableSchema(tbl["TABLE_NAME"].ToString()); tmpTable.ConnectionName = DBschema.ConnectionName; tmpTable.DatabaseName = tmpDB.DatabaseName; if (tables.Columns.Contains("ENGINE") == true) { tmpTable.Engine = tbl["ENGINE"].ToString(); } if (tables.Columns.Contains("AUTO_INCREMENT") == true) { int ai = 0; Int32.TryParse(tbl["AUTO_INCREMENT"].ToString(), out ai); tmpTable.AutoIncrement = ai; } foreach (DataRow col in columns.Rows) { if (tbl["TABLE_NAME"].ToString() == col["TABLE_NAME"].ToString()) { if (columns.Columns.Contains("COLUMN_NAME") == true) { ColumnSchema tmpCol = new ColumnSchema(col["COLUMN_NAME"].ToString()); tmpCol.ConnectionName = DBschema.ConnectionName; tmpCol.DatabaseName = tmpDB.DatabaseName; tmpCol.TableName = tmpTable.TableName; if (columns.Columns.Contains("COLUMN_KEY") == true) { if (col["COLUMN_KEY"].ToString() == "PRI") { tmpCol.PrimaryKey = true; } else { tmpCol.PrimaryKey = false; } } if (columns.Columns.Contains("ORDINAL_POSITION") == true) { int idx = 0; Int32.TryParse(col["ORDINAL_POSITION"].ToString(), out idx); tmpCol.ColumnPosition = idx; } //if(columns.Columns.Contains("DATA_TYPE") == true) { // tmpCol.DataType = col["DATA_TYPE"].ToString(); //} if (columns.Columns.Contains("COLUMN_TYPE") == true) { tmpCol.DataType = col["COLUMN_TYPE"].ToString(); } if (columns.Columns.Contains("COLUMN_DEFAULT") == true) { tmpCol.DefaultValue = col["COLUMN_DEFAULT"].ToString(); } if (columns.Columns.Contains("COLUMN_COMMENT") == true) { tmpCol.Comment = col["COLUMN_COMMENT"].ToString(); } if (columns.Columns.Contains("IS_NULLABLE") == true) { if (col["IS_NULLABLE"].ToString() == "YES") { tmpCol.NotNull = false; } else { tmpCol.NotNull = true; } } if (columns.Columns.Contains("EXTRA") == true) { if (col["EXTRA"].ToString() == "auto_increment") { tmpCol.AutoIncrement = true; } else { tmpCol.AutoIncrement = false; } } tmpTable.addColumn(tmpCol); } } } tmpDB.addTable(tmpTable); } //If Table = Table } //Foreach Table DBschema.addDatabase(tmpDB); //} }//Foreach Database //END SCHEMA INFO } } catch (Exception e) { error = e.Message.ToString(); } }
public bool Equals(ColumnSchema obj) { return(obj != null && obj.GetHashCode() == this.GetHashCode()); }
public string SQLAlterTable(TableSchema newSchema) { string sqlh = ""; string sql = ""; int i = 0; if (Columns != null) { if (newSchema.Columns != null) { sqlh += "ALTER TABLE `" + DatabaseName + "`.`" + TableName + "`\n"; for (i = 0; i < newSchema.Columns.Count(); i++) { ColumnSchema col = newSchema.Columns[i]; if (C.ContainsKey(col.ColumnName) == false) { //ADD NEW COLUMN if (sql == "") { sql += sqlh + "ADD COLUMN "; } else { sql += "ADD COLUMN "; } sql += "`" + col.ColumnName + "` "; sql += col.DataType + " "; //NN if (col.NotNull == true) { sql += "NOT NULL "; } else { sql += "NULL "; } //DV if (col.DefaultValue == "") { sql += "DEFAULT NULL "; } else { sql += "DEFAULT " + col.DefaultValue + " "; } //AI if (col.AutoIncrement == true) { sql += "AUTO_INCREMENT "; } //Position if (col.ColumnPosition == 0) { sql += "FIRST "; } else { sql += "AFTER `" + newSchema.Columns[col.ColumnPosition - 1].ColumnName + "`"; } sql += "\n"; } else { //CHANGE COLUMN ColumnSchema oldcol = C[col.ColumnName]; if (col.ColumnName != oldcol.ColumnName || col.PrimaryKey != oldcol.PrimaryKey || col.DataType != oldcol.DataType || col.AutoIncrement != oldcol.AutoIncrement || col.NotNull != oldcol.NotNull || col.Unique != oldcol.Unique || col.UnSigned != oldcol.UnSigned || col.Generated != oldcol.Generated || col.DefaultValue != oldcol.DefaultValue || col.ColumnPosition != oldcol.ColumnPosition ) { if (sql == "") { sql += sqlh + "CHANGE COLUMN "; } else { sql += "CHANGE COLUMN "; } sql += "`" + col.ColumnName + "` "; sql += "`" + col.ColumnName + "` "; sql += col.DataType + " "; //NN if (col.NotNull == true) { sql += "NOT NULL "; } else { sql += "NULL "; } //DV if (col.DefaultValue == "") { sql += "DEFAULT NULL "; } else { sql += "DEFAULT " + col.DefaultValue + " "; } //AI if (col.AutoIncrement == true) { sql += "AUTO_INCREMENT "; } //Position if (col.ColumnPosition == oldcol.ColumnPosition) { sql += "FIRST "; } else { sql += "AFTER `" + newSchema.Columns[col.ColumnPosition - 1].ColumnName + "`"; } sql += "\n"; } } } if (sql.Length > 0) { sql = sql.Substring(0, sql.Length - 1); sql += ";\n"; } } } return(sql); }