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