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);
}
