internal static List <ColumnValidation> GetColumnValidationList(
string table,
Dictionary <string, Column> columns, Dictionary <string, ForeignKeyColumnInfo> FKs)
{
List <ColumnValidation> colsValidation = null;
// First examine the cache
if (_metadata.TryGetValue(table, out colsValidation))
{
return(colsValidation);
}
colsValidation = new List <ColumnValidation>();
foreach (KeyValuePair <string, Column> kvp in columns)
{
ColumnValidation cv = new ColumnValidation(kvp.Value);
cv.ColumnType = ColumnType.Text;
cv.DefaultValue = "";
cv.MinValue = null;
cv.MaxValue = null;
cv.Required = true;
ForeignKeyColumnInfo fk = null;
if (FKs != null)
{
if (FKs.TryGetValue(cv.Column.ColumnName, out fk))
{
cv.HasLookup = true;
cv.LookupColumn = fk.ReferencedColumnName;
cv.FkInfo = fk;
}
}
colsValidation.Add(cv);
}
_metadata[table] = colsValidation;
return(colsValidation);
}
internal void RetrieveAllFkInfo(MySqlConnection con, string tableName, out Dictionary <string, ForeignKeyColumnInfo> myFKs)
{
string sql = string.Format(
@"select `constraint_name`, `table_name`, `column_name`, `referenced_table_name`, `referenced_column_name`
from information_schema.key_column_usage where table_schema = '{0}' and `constraint_name` in (
select `constraint_name` from information_schema.referential_constraints where `constraint_schema` = '{0}' and `table_name` = '{1}' )
",
con.Database, tableName);
if ((con.State & ConnectionState.Open) == 0)
{
con.Open();
}
Dictionary <string, ForeignKeyColumnInfo> fKs = new Dictionary <string, ForeignKeyColumnInfo>();
// Gather FK info per column pair
MySqlCommand cmd = new MySqlCommand(sql, con);
using (MySqlDataReader r = cmd.ExecuteReader())
{
while (r.Read())
{
ForeignKeyColumnInfo fk = new ForeignKeyColumnInfo()
{
ConstraintName = r.GetString(0),
TableName = r.GetString(1),
ColumnName = r.GetString(2),
ReferencedTableName = r.GetString(3),
ReferencedColumnName = r.GetString(4)
};
fKs.Add(fk.ColumnName, fk);
}
}
// Gather referenceable columns
foreach (ForeignKeyColumnInfo fk in fKs.Values)
{
fk.ReferenceableColumns = GetColumnsFromTableVanilla(fk.ReferencedTableName, con);
}
myFKs = fKs;
}
internal static void LoadGridColumns(DataGridView grid, MySqlConnection con, string Table,
List <ColumnValidation> colsValidation, Dictionary <string, Column> columns,
Dictionary <string, ForeignKeyColumnInfo> FKs)
{
#if NET_40_OR_GREATER
SortedSet <string> allColumns = new SortedSet <string>();
BindingSource binding = new BindingSource();
//reset grid
grid.DataSource = null;
grid.Columns.Clear();
grid.Rows.Clear();
grid.Update();
for (int i = 0; i < colsValidation.Count; i++)
{
ColumnValidation cv = colsValidation[i];
if (FKs != null)
{
ForeignKeyColumnInfo fk = null;
if (FKs.TryGetValue(cv.Column.ColumnName, out fk))
{
allColumns.UnionWith(fk.ReferenceableColumns);
}
}
}
grid.AutoGenerateColumns = false;
DataGridViewTextBoxColumn colName = new DataGridViewTextBoxColumn();
colName.DataPropertyName = "Name";
colName.HeaderText = "Column Name";
colName.Name = "colName";
colName.ReadOnly = true;
grid.Columns.Add(colName);
IdxColName = colName.Index;
DataGridViewCheckBoxColumn colRequired = new DataGridViewCheckBoxColumn();
colRequired.DataPropertyName = "Required";
colRequired.HeaderText = "Req.";
colRequired.ToolTipText = "Required";
colRequired.Name = "colRequired";
colRequired.ReadOnly = true;
grid.Columns.Add(colRequired);
IdxColRequired = colRequired.Index;
DataGridViewTextBoxColumn colDataType = new DataGridViewTextBoxColumn();
colDataType.DataPropertyName = "DataType";
colDataType.HeaderText = "Data Type";
colDataType.ToolTipText = "Data Type";
colDataType.Name = "colDataType";
colDataType.ReadOnly = true;
grid.Columns.Add(colDataType);
IdxColDataType = colDataType.Index;
DataGridViewTextBoxColumn colDefaultValue = new DataGridViewTextBoxColumn();
colDefaultValue.DataPropertyName = "DefaultValue";
colDefaultValue.HeaderText = "Def.";
colDefaultValue.ToolTipText = "Default value";
colDefaultValue.Name = "colDefaultValue";
colDefaultValue.Width = 90;
grid.Columns.Add(colDefaultValue);
IdxColDefaultValue = colDefaultValue.Index;
DataGridViewTextBoxColumn colMaxLength = new DataGridViewTextBoxColumn();
colMaxLength.DataPropertyName = "MaxLength";
colMaxLength.HeaderText = "Max Len";
colMaxLength.ToolTipText = "Max Length";
colMaxLength.Name = "colMaxLength";
grid.Columns.Add(colMaxLength);
IdxColMaxLength = colMaxLength.Index;
DataGridViewTextBoxColumn colMinValue = new DataGridViewTextBoxColumn();
colMinValue.DataPropertyName = "MinValue";
colMinValue.HeaderText = "Min Val.";
colMinValue.ToolTipText = "Min Value";
colMinValue.Name = "colMinValue";
grid.Columns.Add(colMinValue);
IdxColMinValue = colMinValue.Index;
DataGridViewTextBoxColumn colMaxValue = new DataGridViewTextBoxColumn();
colMaxValue.DataPropertyName = "MaxValue";
colMaxValue.HeaderText = "Max Val.";
colMaxValue.ToolTipText = "Max Value";
colMaxValue.Name = "colMaxValue";
grid.Columns.Add(colMaxValue);
IdxColMaxValue = colMaxValue.Index;
if (FKs != null && FKs.Count > 0)
{
DataGridViewCheckBoxColumn colHasLookup = new DataGridViewCheckBoxColumn();
colHasLookup.DataPropertyName = "HasLookup";
colHasLookup.HeaderText = "Has Lookup";
colHasLookup.Name = "colHasLookup";
colHasLookup.ToolTipText = "Checked to enable Lookup ComboBox, uncheck to use simple textbox, only available for Foreign key columns";
grid.Columns.Add(colHasLookup);
IdxColHaslookup = colHasLookup.Index;
DataGridViewComboBoxColumn colLookupColumn = new DataGridViewComboBoxColumn();
colLookupColumn.DataSource = allColumns.ToList();
colLookupColumn.DataPropertyName = "LookupColumn";
colLookupColumn.HeaderText = "Lookup Column";
colLookupColumn.Name = "colLookupColumn";
colLookupColumn.ToolTipText = "Pick the column from the foreign table to use as friendly value for this lookup.";
grid.Columns.Add(colLookupColumn);
IdxColLookupColumn = colLookupColumn.Index;
grid.Columns[IdxColHaslookup].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill;
grid.Columns[IdxColLookupColumn].AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells;
}
grid.DataSource = colsValidation;
grid.Columns[IdxColName].AutoSizeMode = DataGridViewAutoSizeColumnMode.ColumnHeader; //DataGridViewAutoSizeColumnMode.ColumnHeader;
grid.Columns[IdxColRequired].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill; //DataGridViewAutoSizeColumnMode.DisplayedCells;
grid.Columns[IdxColDataType].AutoSizeMode = DataGridViewAutoSizeColumnMode.ColumnHeader; //DataGridViewAutoSizeColumnMode.DisplayedCells;
grid.Columns[IdxColDefaultValue].AutoSizeMode = DataGridViewAutoSizeColumnMode.ColumnHeader;
grid.Columns[IdxColMaxLength].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill;
grid.Columns[IdxColMinValue].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill;
grid.Columns[IdxColMaxValue].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill;
if (FKs != null && FKs.Count > 0)
{
grid.Columns[IdxColHaslookup].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill;
grid.Columns[IdxColLookupColumn].AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells;
}
#endif
grid.AllowUserToAddRows = false;
grid.Refresh();
if (FKs != null && FKs.Count > 0)
{
// highlight the rows that are FK columns, disable the FK cells for columns (datagrid rows) which are not FKs.
foreach (DataGridViewRow row in grid.Rows)
{
if (!(bool)(row.Cells[IdxColHaslookup].Value))
{
row.Cells[IdxColHaslookup].ReadOnly = true;
row.Cells[IdxColLookupColumn].ReadOnly = true;
}
else
{
row.DefaultCellStyle.BackColor = Color.FromArgb(255, 197, 206, 216);
}
}
}
}
