///--------------------------------------------------------------------------------
/// <summary>This loads information from a MySQL foreign key.</summary>
///
/// <param name="sqlConnection">The input sql connection</param>
/// <param name="variables">Database level variables</param>
/// <param name="keyColumn">The key column row data</param>
///--------------------------------------------------------------------------------
public void LoadMySQLColumn(MySqlConnection sqlConnection, NameObjectCollection variables, DataRow keyColumn)
{
try
{
// load foreign key information
foreach (DataColumn column in keyColumn.Table.Columns)
{
// load important properties into foreign key, the rest as additional sql properties
switch (column.ColumnName)
{
case "COLUMN_NAME":
SqlForeignKeyColumnName = keyColumn[column.ColumnName].GetString();
break;
case "REFERENCED_COLUMN_NAME":
ReferencedColumn = keyColumn[column.ColumnName].GetString();
break;
case "ORDINAL_POSITION":
Order = keyColumn[column.ColumnName].GetInt();
break;
default:
SqlProperty property = new SqlProperty();
property.SqlPropertyID = Guid.NewGuid();
property.SqlForeignKeyColumn = this;
property.LoadMySQLProperty(column.ColumnName, null, keyColumn[column.ColumnName].GetString());
SqlPropertyList.Add(property);
break;
}
}
}
catch (ApplicationAbortException)
{
throw;
}
catch (Exception ex)
{
bool reThrow = BusinessConfiguration.HandleException(ex);
if (reThrow)
{
throw;
}
}
}
///--------------------------------------------------------------------------------
/// <summary>This loads information from a MySQL database.</summary>
///
/// <param name="sqlConnection">The input sql connection</param>
///--------------------------------------------------------------------------------
public void LoadMySQLDatabase(MySqlConnection sqlConnection)
{
try
{
// load the basic database information
SqlDatabaseName = sqlConnection.Database;
//Owner = sqlDatabase.Owner;
//PrimaryFilePath = sqlDatabase.PrimaryFilePath;
//DefaultSchema = sqlDatabase.DefaultSchema;
//DefaultFileGroup = sqlDatabase.DefaultFileGroup;
//CreateDate = sqlDatabase.CreateDate;
// load variables
NameObjectCollection variables = new NameObjectCollection();
MySqlCommand command = sqlConnection.CreateCommand();
command.CommandText = "SHOW VARIABLES;";
MySqlDataReader Reader;
Reader = command.ExecuteReader();
while (Reader.Read())
{
variables[Reader.GetValue(0).ToString()] = Reader.GetValue(1).ToString();
}
Reader.Close();
// add variables to database properties
foreach (string variable in variables.AllKeys)
{
if (DebugHelper.DebugAction == DebugAction.Stop)
{
return;
}
SqlProperty property = new SqlProperty();
property.SqlPropertyID = Guid.NewGuid();
property.SqlDatabase = this;
property.LoadMySQLProperty(variable, null, variables[variable].ToString());
SqlPropertyList.Add(property);
}
// load tables of schema info
DataTable tables = null;
DataTable columns = null;
DataTable indexes = null;
DataTable indexColumns = null;
DataTable foreignKeys = null;
DataTable foreignKeyColumns = null;
try
{
tables = sqlConnection.GetSchema("Tables");
}
catch
{
Solution.ShowIssue(String.Format(DisplayValues.Exception_MySQLSchemaLoad, "Tables", SqlDatabaseName), null, Solution.IsSampleMode);
}
try
{
columns = sqlConnection.GetSchema("Columns");
}
catch
{
Solution.ShowIssue(String.Format(DisplayValues.Exception_MySQLSchemaLoad, "Columns", SqlDatabaseName), null, Solution.IsSampleMode);
}
try
{
indexes = sqlConnection.GetSchema("Indexes");
}
catch
{
Solution.ShowIssue(String.Format(DisplayValues.Exception_MySQLSchemaLoad, "Indexes", SqlDatabaseName), null, Solution.IsSampleMode);
}
try
{
indexColumns = sqlConnection.GetSchema("IndexColumns");
}
catch
{
Solution.ShowIssue(String.Format(DisplayValues.Exception_MySQLSchemaLoad, "IndexColumns", SqlDatabaseName), null, Solution.IsSampleMode);
}
try
{
foreignKeys = sqlConnection.GetSchema("Foreign Keys");
}
catch
{
Solution.ShowIssue(String.Format(DisplayValues.Exception_MySQLSchemaLoad, "Foreign Keys", SqlDatabaseName), null, Solution.IsSampleMode);
}
try
{
foreignKeyColumns = sqlConnection.GetSchema("Foreign Key Columns");
}
catch
{
Solution.ShowIssue(String.Format(DisplayValues.Exception_MySQLSchemaLoad, "Foreign Key Columns", SqlDatabaseName), null, Solution.IsSampleMode);
}
// load information for each table
if (tables != null)
{
foreach (DataRow row in tables.Rows)
{
if (DebugHelper.DebugAction == DebugAction.Stop)
{
return;
}
SqlTable table = new SqlTable();
table.SqlTableID = Guid.NewGuid();
table.SqlDatabase = this;
table.LoadMySQLTable(sqlConnection, variables, row, columns, indexes, indexColumns, foreignKeys, foreignKeyColumns);
SqlTableList.Add(table);
}
}
}
catch (ApplicationAbortException)
{
throw;
}
catch (Exception ex)
{
bool reThrow = BusinessConfiguration.HandleException(ex);
if (reThrow)
{
throw;
}
}
}
///--------------------------------------------------------------------------------
/// <summary>This loads information from a MySQL foreign key.</summary>
///
/// <param name="sqlConnection">The input sql connection</param>
/// <param name="variables">Database level variables</param>
/// <param name="key">The key row data</param>
/// <param name="keyColumns">The key columns table data</param>
///--------------------------------------------------------------------------------
public void LoadMySQLForeignKey(MySqlConnection sqlConnection, NameObjectCollection variables, DataRow key, DataTable keyColumns)
{
try
{
// load foreign key information
foreach (DataColumn column in key.Table.Columns)
{
if (DebugHelper.DebugAction == DebugAction.Stop)
{
return;
}
// load important properties into foreign key, the rest as additional sql properties
switch (column.ColumnName)
{
case "constraint_name":
SqlForeignKeyName = key[column.ColumnName].GetString();
break;
case "referenced_table_name":
ReferencedTable = key[column.ColumnName].GetString();
break;
case "referenced_table_schema":
ReferencedTableSchema = key[column.ColumnName].GetString();
break;
default:
SqlProperty property = new SqlProperty();
property.SqlPropertyID = Guid.NewGuid();
property.SqlForeignKey = this;
property.LoadMySQLProperty(column.ColumnName, null, key[column.ColumnName].GetString());
SqlPropertyList.Add(property);
break;
}
}
// load information for each column
if (keyColumns != null)
{
foreach (DataRow row in keyColumns.Rows)
{
if (DebugHelper.DebugAction == DebugAction.Stop)
{
return;
}
if (row["TABLE_NAME"].GetString() == SqlTable.SqlTableName && row["CONSTRAINT_NAME"].GetString() == SqlForeignKeyName)
{
SqlForeignKeyColumn column = new SqlForeignKeyColumn();
column.SqlForeignKeyColumnID = Guid.NewGuid();
column.SqlForeignKey = this;
column.LoadMySQLColumn(sqlConnection, variables, row);
SqlForeignKeyColumnList.Add(column);
}
}
}
}
catch (ApplicationAbortException)
{
throw;
}
catch (Exception ex)
{
bool reThrow = BusinessConfiguration.HandleException(ex);
if (reThrow)
{
throw;
}
}
}
///--------------------------------------------------------------------------------
/// <summary>This loads information from a MySQL column.</summary>
///
/// <param name="sqlConnection">The input sql connection</param>
/// <param name="variables">Database level variables</param>
/// <param name="column">The column row data</param>
///--------------------------------------------------------------------------------
public void LoadMySQLColumn(MySqlConnection sqlConnection, NameObjectCollection variables, DataRow column)
{
try
{
// doesn't seem to be a column property for these...
IdentitySeed = variables["auto_increment_offset"].GetInt();
IdentityIncrement = variables["auto_increment_increment"].GetInt();
//IsForeignKey = sqlColumn.IsForeignKey;
// load column information
foreach (DataColumn item in column.Table.Columns)
{
// load important properties into column, the rest as additional sql properties
switch (item.ColumnName)
{
case "COLUMN_NAME":
SqlColumnName = column[item.ColumnName].GetString();
break;
case "DATA_TYPE":
DataType = column[item.ColumnName].GetString();
break;
case "CHARACTER_MAXIMUM_LENGTH":
MaximumLength = column[item.ColumnName].GetInt();
break;
case "NUMERIC_PRECISION":
NumericPrecision = column[item.ColumnName].GetInt();
break;
case "NUMERIC_SCALE":
NumericScale = column[item.ColumnName].GetInt();
break;
case "COLUMN_DEFAULT":
Default = column[item.ColumnName].GetString();
break;
case "COLUMN_KEY":
InPrimaryKey = column[item.ColumnName].GetString() == "PRI";
break;
case "IS_NULLABLE":
Nullable = column[item.ColumnName].GetBool();
break;
case "ORDINAL_POSITION":
Order = column[item.ColumnName].GetInt();
break;
case "EXTRA":
Identity = column[item.ColumnName].GetString().Contains("auto_increment");
break;
default:
SqlProperty property = new SqlProperty();
property.SqlPropertyID = Guid.NewGuid();
property.SqlColumn = this;
property.LoadMySQLProperty(item.ColumnName, null, column[item.ColumnName].GetString());
SqlPropertyList.Add(property);
break;
}
}
}
catch (ApplicationAbortException)
{
throw;
}
catch (Exception ex)
{
bool reThrow = BusinessConfiguration.HandleException(ex);
if (reThrow)
{
throw;
}
}
}
///--------------------------------------------------------------------------------
/// <summary>This loads information from a MySQL table.</summary>
///
/// <param name="sqlConnection">The input sql connection</param>
/// <param name="variables">Database level variables</param>
/// <param name="table">The table row data</param>
/// <param name="columns">The columns table data</param>
/// <param name="indexes">The indexes table data</param>
/// <param name="indexColumns">The index columns table data</param>
/// <param name="keys">The keys table data</param>
/// <param name="keyColumns">The key columns table data</param>
///--------------------------------------------------------------------------------
public void LoadMySQLTable(MySqlConnection sqlConnection, NameObjectCollection variables, DataRow table, DataTable columns, DataTable indexes, DataTable indexColumns, DataTable keys, DataTable keyColumns)
{
try
{
// load table information
foreach (DataColumn column in table.Table.Columns)
{
// load important properties into table, the rest as additional sql properties
switch (column.ColumnName)
{
case "TABLE_NAME":
SqlTableName = table[column.ColumnName].GetString();
break;
case "CREATE_TIME":
CreateDate = table[column.ColumnName].GetDateTime();
break;
case "UPDATE_TIME":
DateLastModified = table[column.ColumnName].GetDateTime();
break;
default:
SqlProperty property = new SqlProperty();
property.SqlPropertyID = Guid.NewGuid();
property.SqlTable = this;
property.LoadMySQLProperty(column.ColumnName, null, table[column.ColumnName].GetString());
SqlPropertyList.Add(property);
break;
}
}
// load information for each column
if (columns != null)
{
foreach (DataRow row in columns.Rows)
{
if (DebugHelper.DebugAction == DebugAction.Stop)
{
return;
}
if (row["TABLE_NAME"].GetString() == SqlTableName)
{
SqlColumn column = new SqlColumn();
column.SqlColumnID = Guid.NewGuid();
column.SqlTable = this;
column.LoadMySQLColumn(sqlConnection, variables, row);
SqlColumnList.Add(column);
}
}
}
// load information for each index
if (indexes != null)
{
foreach (DataRow row in indexes.Rows)
{
if (DebugHelper.DebugAction == DebugAction.Stop)
{
return;
}
if (row["TABLE_NAME"].GetString() == SqlTableName)
{
SqlIndex index = new SqlIndex();
index.SqlIndexID = Guid.NewGuid();
index.SqlTable = this;
index.LoadMySQLIndex(sqlConnection, variables, row, indexColumns);
SqlIndexList.Add(index);
}
}
}
// load information for each foreign key
if (keys != null)
{
foreach (DataRow row in keys.Rows)
{
if (DebugHelper.DebugAction == DebugAction.Stop)
{
return;
}
if (row["table_name"].GetString() == SqlTableName)
{
SqlForeignKey key = new SqlForeignKey();
key.SqlForeignKeyID = Guid.NewGuid();
key.SqlTable = this;
key.LoadMySQLForeignKey(sqlConnection, variables, row, keyColumns);
SqlForeignKeyList.Add(key);
}
}
}
}
catch (ApplicationAbortException)
{
throw;
}
catch (Exception)
{
throw;
}
}
///--------------------------------------------------------------------------------
/// <summary>This loads information from a MySQL index.</summary>
///
/// <param name="sqlConnection">The input sql connection</param>
/// <param name="variables">Database level variables</param>
/// <param name="index">The table row data</param>
/// <param name="indexColumns">The index columns table data</param>
///--------------------------------------------------------------------------------
public void LoadMySQLIndex(MySqlConnection sqlConnection, NameObjectCollection variables, DataRow index, DataTable indexColumns)
{
try
{
// load index information
foreach (DataColumn column in index.Table.Columns)
{
// load important properties into index, the rest as additional sql properties
switch (column.ColumnName)
{
case "INDEX_NAME":
SqlIndexName = index[column.ColumnName].GetString();
break;
case "PRIMARY":
IsClustered = index[column.ColumnName].GetBool(); // may not be a good setting
break;
case "UNIQUE":
IsUnique = index[column.ColumnName].GetBool();
break;
default:
SqlProperty property = new SqlProperty();
property.SqlPropertyID = Guid.NewGuid();
property.SqlIndex = this;
property.LoadMySQLProperty(column.ColumnName, null, index[column.ColumnName].GetString());
SqlPropertyList.Add(property);
break;
}
}
// load information for each column
if (indexColumns != null)
{
foreach (DataRow row in indexColumns.Rows)
{
if (DebugHelper.DebugAction == DebugAction.Stop)
{
return;
}
if (row["TABLE_NAME"].GetString() == SqlTable.SqlTableName && row["INDEX_NAME"].GetString() == SqlIndexName)
{
SqlIndexedColumn column = new SqlIndexedColumn();
column.SqlIndexedColumnID = Guid.NewGuid();
column.SqlIndex = this;
column.LoadMySQLColumn(sqlConnection, variables, row);
SqlIndexedColumnList.Add(column);
}
}
}
}
catch (ApplicationAbortException)
{
throw;
}
catch (Exception ex)
{
bool reThrow = BusinessConfiguration.HandleException(ex);
if (reThrow)
{
throw;
}
}
}