DataGridView based user control with edit dialog






4.85/5 (20 votes)
Simplify handling data tables

Introduction
DataGridView control is often used in applications that use databases. Normally control displays data in a table format list and allows you to add / modify and delete lines. Each entry in Table corresponds to a line. But there are many programmers who does not agree editing data directly in DataGridView control (among them myself). In this case control is used for display and editing operations are performed in forms. For example to add a record we open a form that displays fields that need to be introduced. After the data is entered by the user we check if they are valid and if everything is ok are added to the table and in DataGridView control. For modify, data also placed in a form that is opened by the user. The changes are checked, saved in the table (if everything is OK) and updated in DataGridView control. This is a scenario often encountered in practice. Where is the problem? Suppose we have a database with many tables 20 to 30. According to the method described above would mean to build 20 to 30 to form to add / change record - one form for each table. That could mean a large consumption of time and also can be pretty boring.
Goal
An alternative would be to create a user control (DbdscControl) to whom you give the name of the database table and open the table in a DataGridView control and then for adding, modifying and deleting records using a single form. This form is meant to be generally able to adapt the structure of each table. To ensure this generalization we created an auxiliary database in Access format (Dbdsc.mdb) which contains 2 tables (Fields and GridForm). This will be retained the necessary information about each table and each field. This information is used by DbdscControl to know how to behave to each table and each field. To view and change these settings using a special form that allows modification of the data Dbdsc.mdb.
Example of editing form

GridForm table structure: TableName – string - name of the table gridAlternateColor – number - indicate alternate color (argb format) gridShowStatus – true/false – if true show status bar gridShowRowHeaders – true/false - row header with line number gridStyle – string - ‘List’ (default)/’Sheet’ gridRowHeight – number – particular row height formWidth – number – width of editing form formHeight – number – height of editing form formShowInfo – true/false – show/hide panel wich contains extra information about field (see formInfo) formSplitDistance – number – if formShowInfo is true then nedds to know size of split formRowHeaderWidth – number – width of header row in editing form
Fields table structure: TableName – string – table name FieldName – string – field name gridHeaderText – string – header text gridHeaderAlign - header align (‘Left’/‘Right’/’Center’) gridVisible – true/false – show/hide column gridWidth – number – width of column gridAlign - string cell align (‘Left’/‘Right’/’Center’) gridIndex – number – position in grid gridFormat – string – standard format string formHeaderText – string – text displayed in form for this field formVisible – true/false – show/hide this field formEditable – true/false – if true the field is read-only formAlign – string – form align (‘Left’/‘Right’/’Center’) formIndex – number – position in form formFormat – string - standard format string formInfo – string – extra information about field formControl – string – indicate control used in editing form (‘Calendar’, ‘Checkbox’, ’Combo’) formColor – number - indicate color used for this field (argb format) formComboElem – string – if control is ‘Combo’ here we can put a string of elements separate by ‘*’ (e.g. ‘Male’*’Female’*’Unknown’, 100*2500*3200) formComboTableName – string – if control is ‘Combo’ and elements are contained in another table then we put here the name of that table formComboField – string - if control is ‘Combo’ and elements are contained in another table then we put here the name of the field (table name must be in formComboTableName) formComboWhere- string-where clause for control ‘Combo’ formComboSQL - string - sql clause (SELECT [Employee ID], [First Name]&' '&[Last Name] FROM [Employee] ORDER BY [First Name], [Last Name] this will create two column combo box) formRequired – true/false – field value required formDefaultValue – string – not used yet formCellHeight - number – height for this field formCellWrap – true/false – used for large text fields
Example of form for editing settings

Control will function in the following way: 1. Initialization - gets the connection string and table name 2. Read data 3. Read the settings (Dbdsc.mdb) 4. Show data under the settings (Dbdsc.mdb) 5. To add / change data opens a form - before being displayed examines the settings (Dbdsc.mdb) 6. To change the settings we use another form that allows changes to settings tables (Fileds, GridForm) and save these settings back in Dbdsc.mdb. 7. Some settings must be saved even if not open settings form (if the user resizes a column the new length must be saved). This model would mean that for each control Dbdsc table we can use without need any code, we only need to perform specific settings for each table.
Using the code
DbdscControl control is in Dbdsc.dll. The main classes are : Config - This class read / save settings are retained in Dbdsc.mdb ConfigGridForm - form to display / change settings related to a table DbdscControl - control itself DbdscEditForm - form editing used by DbdscControl
Helper class
FunctionsGetPrimayKeyValueFromDataRow takes a DataTable tbl and a DataRow from tbl to retreive primary kes values in that row.
static public object[] GetPrimayKeyValueFromDataRow(DataTable tbl, DataRow dr) { object[] pkval = new object[tbl.PrimaryKey.Length]; DataColumn[] keys = tbl.PrimaryKey; for (int i = 0; i < keys.Length; i++) pkval[i] = dr[keys[i].ColumnName]; return pkval; }GetPrimayKeyDisplayedNames return displayed names (in editing form) for primary keys fields using config object (see Cofig class)
static public string[] GetPrimayKeyDisplayedNames(DataTable tbl, Config config) { string[] pknm = new string[tbl.PrimaryKey.Length]; DataColumn[] keys = tbl.PrimaryKey; for (int i = 0; i < keys.Length; i++) { DataRow drconfig = config.getFieldRow(keys[i].ColumnName); pknm[i] = drconfig["formHeaderText"].ToString(); } return pknm; }Equals test if two arrays of objects are equal testing element by element (if arrays have different sizes then return false)
static public bool Equals(object[] o1, object[] o2) { if (o1.Length != o2.Length) return false; bool retval = true; for (int i = 0; i < o1.Length; i++) { if (!o1[i].Equals(o2[i])) { retval = false; break; } } return retval; }Concat concatenate strings using separator – used when editing form perform minimal validation to construct a message to user.
static public string Concat(string[] strstr, string separator) { string retval = string.Empty; for (int i = 0; i < strstr.Length; i++) { if (!string.IsNullOrEmpty(retval)) retval += separator; retval += strstr[i]; } return retval; }GridRowPostPaint is used for display row number in grids
static public void GridRowPostPaint(object sender, DataGridViewRowPostPaintEventArgs e) { DataGridView grid = sender as DataGridView; if (!grid.RowHeadersVisible) return; //this method overrides the DataGridView's RowPostPaint event //in order to automatically draw numbers on the row header cells //and to automatically adjust the width of the column containing //the row header cells so that it can accommodate the new row //numbers, //store a string representation of the row number in 'strRowNumber' string strRowNumber = (e.RowIndex + 1).ToString(); //prepend leading zeros to the string if necessary to improve //appearance. For example, if there are ten rows in the grid, //row seven will be numbered as "07" instead of "7". Similarly, if //there are 100 rows in the grid, row seven will be numbered as "007". while (strRowNumber.Length < grid.RowCount.ToString().Length) strRowNumber = "0" + strRowNumber; //determine the display size of the row number string using //the DataGridView's current font. SizeF size = e.Graphics.MeasureString(strRowNumber, grid.Font); //adjust the width of the column that contains the row header cells //if necessary if (grid.RowHeadersWidth < (int)(size.Width + 20)) grid.RowHeadersWidth = (int)(size.Width + 20); //this brush will be used to draw the row number string on the //row header cell using the system's current ControlText color Brush b = SystemBrushes.ControlText; //draw the row number string on the current row header cell using //the brush defined above and the DataGridView's default font e.Graphics.DrawString(strRowNumber, grid.Font, b, e.RowBounds.Location.X + 15, e.RowBounds.Location.Y + ((e.RowBounds.Height - size.Height) / 2)); }GetPrimaryKeys returns a array of strings wich contains primary kes names from a table using GetOleDbSchemaTable
static public string[] GetPrimaryKeys(OleDbConnection cn, string strTableName) { if (cn == null || cn.State == ConnectionState.Closed) { MessageBox.Show("GetPrimaryKeys - connection is closed"); return null; } strTableName = strTableName.Replace("[", string.Empty); strTableName = strTableName.Replace("]", string.Empty); DataTable schemaTable = cn.GetOleDbSchemaTable( OleDbSchemaGuid.Primary_Keys, new object[] { null, null, strTableName }); string[] strpk = new string[schemaTable.Rows.Count]; for (int i = 0; i < strpk.Length; i++) { DataRow dr = schemaTable.Rows[i]; strpk[i] = dr[3].ToString(); } return strpk; }GetTables return a DataTable with list of tables in a database using GetOleDbSchemaTable
static public DataTable GetTables(string connectionString) { DataTable retVal = null; OleDbConnection cn = new OleDbConnection(connectionString); using (cn) { cn.Open(); retVal = cn.GetOleDbSchemaTable( OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" }); cn.Close(); } return retVal; }
Config class
This class manages settings conatined in Dbdsc.mdb. Class variablesDataTable tblConfigFields = new DataTable(); DataTable tblConfigGridForm = new DataTable(); string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\\Dbdsc.mdb;Persist Security Info=True"; string TableName = string.Empty;Constructor: When object is created contructor fill tables tblConfigFields and tblConfigGridForm.
public Config(string TableName) { this.TableName = TableName; OleDbConnection cn = new OleDbConnection(connectionString); using (cn) { #region fill field info OleDbDataAdapter adConfigFields = new OleDbDataAdapter("SELECT * FROM Fields WHERE TableName='" + TableName + "'", cn); adConfigFields.Fill(tblConfigFields); tblConfigFields.PrimaryKey = new DataColumn[] { tblConfigFields.Columns["FieldName"] }; #endregion #region fill grid/form info OleDbDataAdapter adConfigGridForm = new OleDbDataAdapter("SELECT * FROM GridForm WHERE TableName='" + TableName + "'", cn); adConfigGridForm.Fill(tblConfigGridForm); tblConfigGridForm.PrimaryKey = new DataColumn[]{ tblConfigGridForm.Columns["TableName"] }; #endregion } }When a table name is not found (in Fields and GridForm – that means there’s no settings created for that table) we have a function that creates some basic settings.
public void CreateSettings(DataTable tbl) { if (tblConfigGridForm.Rows.Count == 0) { DataRow rowConfig = tblConfigGridForm.NewRow(); rowConfig["TableName"] = TableName; tblConfigGridForm.Rows.Add(rowConfig); UpdateAll(); } if (tblConfigFields.Rows.Count == 0) { foreach (DataColumn col in tbl.Columns) { DataRow rowConfig = tblConfigFields.NewRow(); rowConfig["TableName"] = TableName; rowConfig["FieldName"] = col.ColumnName; rowConfig["gridHeaderText"] = col.ColumnName; rowConfig["gridVisible"] = true; rowConfig["formHeaderText"] = col.ColumnName; rowConfig["formVisible"] = true; rowConfig["formEditable"] = true; rowConfig["gridIndex"] = tbl.Columns.IndexOf(col.ColumnName); tblConfigFields.Rows.Add(rowConfig); } UpdateAll(); } }After are readed we want to see settings effect
public void Apply(DbdscControl target) { #region grid/form Color alternate = getGridAlternateColor(); if (alternate != Color.White && alternate != Color.Empty) target.grid.AlternatingRowsDefaultCellStyle.BackColor = alternate; target.statusStrip.Visible = getGridShowStatus(); KryptonDataGridView kgrid = (KryptonDataGridView)target.grid; kgrid.GridStyles.Style = getGridStyle(); kgrid.RowHeadersVisible = getGridShowRowHeaders(); #endregion #region fileds for (int i = 0; i < target.grid.Columns.Count; i++) { DataGridViewColumn dgvc = target.grid.Columns[i]; DataRow dr = tblConfigFields.Rows.Find(dgvc.DataPropertyName); if (dr == null) continue; bool gridVisible = false; bool.TryParse(dr["gridVisible"].ToString(), out gridVisible); dgvc.Visible = gridVisible; string gridAlign = dr["gridAlign"].ToString(); switch (gridAlign) { case "Left": dgvc.DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleLeft; break; case "Center": dgvc.DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter; break; case "Right": dgvc.DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight; break; default: dgvc.DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleLeft; break; } string gridHeaderAlign = dr["gridHeaderAlign"].ToString(); switch (gridHeaderAlign) { case "Left": dgvc.HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleLeft; break; case "Center": dgvc.HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter; break; case "Right": dgvc.HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleRight; break; default: dgvc.HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleLeft; break; } int widthcol = 50; int.TryParse(dr["gridWidth"].ToString(), out widthcol); if (widthcol == 0) widthcol = 50; dgvc.Width = widthcol; dgvc.HeaderText = dr["gridHeaderText"].ToString(); string fmt = string.Empty; if (dr["gridFormat"] != DBNull.Value) fmt = dr["gridFormat"].ToString(); if (!string.IsNullOrEmpty(fmt)) dgvc.DefaultCellStyle.Format = fmt; } foreach (DataRow dr in tblConfigFields.Select("gridIndex>=0", "gridIndex")) { string dataPropertyName = dr["FieldName"].ToString(); int index = 0; int.TryParse(dr["gridIndex"].ToString(), out index); target.grid.Columns[dataPropertyName].DisplayIndex = index; } #endregion }The second iteration of columns are for restaurate column position. And we want to save settings
public void UpdateAll() { bool s1 = UpdateConfigFields(); bool s2 = UpdateConfigGridForm(); } ///For saving we use OleDbCommandBuilder class with ConflictOption set to ConflictOption.OverwriteChanges to avoid Concurrency exceptions. Other functions in Config class are destined to retreive/save individual values, for example when editing form opens we want to know the size (default 500/500):/// Save chages - fields /// ///private bool UpdateConfigFields() { bool success = true; DataTable tblchanges = tblConfigFields.GetChanges(); if (tblchanges != null && tblchanges.Rows.Count > 0 && !tblchanges.HasErrors) { OleDbConnection cn = new OleDbConnection(connectionString); using (cn) { cn.Open(); OleDbDataAdapter adConfigFields = new OleDbDataAdapter("SELECT * FROM Fields WHERE TableName='" + TableName + "'", cn); OleDbTransaction t = cn.BeginTransaction(IsolationLevel.ReadCommitted); OleDbCommandBuilder bldConfigFields = new OleDbCommandBuilder(adConfigFields); bldConfigFields.ConflictOption = ConflictOption.OverwriteChanges; bldConfigFields.SetAllValues = false; bldConfigFields.QuotePrefix = "["; bldConfigFields.QuoteSuffix = "]"; adConfigFields.SelectCommand.Transaction = t; adConfigFields.InsertCommand = bldConfigFields.GetInsertCommand(); adConfigFields.UpdateCommand = bldConfigFields.GetUpdateCommand(); adConfigFields.DeleteCommand = bldConfigFields.GetDeleteCommand(); try { adConfigFields.Update(tblchanges); tblConfigFields.AcceptChanges(); t.Commit(); } catch (Exception ex) { t.Rollback(); success = false; MessageBox.Show("Error saving fields config for " + TableName + "\n" + ex.Message); } cn.Close(); } } return success; } /// /// Save changes - grid/form /// ///private bool UpdateConfigGridForm() { bool success = true; DataTable tblchanges = tblConfigGridForm.GetChanges(); if (tblchanges != null && tblchanges.Rows.Count > 0 && !tblchanges.HasErrors) { OleDbConnection cn = new OleDbConnection(connectionString); using (cn) { cn.Open(); OleDbDataAdapter adConfigGridForm = new OleDbDataAdapter("SELECT * FROM GridForm WHERE TableName='" + TableName + "'", cn); OleDbTransaction t = cn.BeginTransaction(IsolationLevel.ReadCommitted); OleDbCommandBuilder bldConfigGridForm = new OleDbCommandBuilder(adConfigGridForm); bldConfigGridForm.ConflictOption = ConflictOption.OverwriteChanges; bldConfigGridForm.SetAllValues = false; bldConfigGridForm.QuotePrefix = "["; bldConfigGridForm.QuoteSuffix = "]"; adConfigGridForm.SelectCommand.Transaction = t; adConfigGridForm.InsertCommand = bldConfigGridForm.GetInsertCommand(); adConfigGridForm.UpdateCommand = bldConfigGridForm.GetUpdateCommand(); adConfigGridForm.DeleteCommand = bldConfigGridForm.GetDeleteCommand(); try { adConfigGridForm.Update(tblchanges); tblConfigGridForm.AcceptChanges(); t.Commit(); } catch (Exception ex) { t.Rollback(); success = false; MessageBox.Show("Error saving grid/form config for " + TableName + "\n" + ex.Message); } cn.Close(); } } return success; }
public Size getFormSize() { DataRow dr = tblConfigGridForm.Rows.Find(TableName); if (dr == null) return new Size(500, 500); int w = 0; int h = 0; int.TryParse(dr["formWidth"].ToString(), out w); int.TryParse(dr["formHeight"].ToString(), out h); if (w == 0) w = 500; if (h == 0) h = 500; Size szForm = new Size(w, h); return szForm; }When user press edtiting form OK button we save the new size
public void setFormSize(Size szNewSize) { DataRow dr = tblConfigGridForm.Rows.Find(TableName); if (dr == null) { dr = tblConfigGridForm.NewRow(); dr["TableName"] = TableName; tblConfigGridForm.Rows.Add(dr); } dr["formWidth"] = szNewSize.Width; dr["formHeight"] = szNewSize.Height; UpdateAll(); }For fields we have an auxiliar function to retreive settings. That function use DataTable Find function and returns DataRow that corresponds FieldName.
public DataRow getFieldRow(string FieldName) { DataRow drconfig = tblConfigFields.Rows.Find(FieldName); return drconfig; }For example in Apply function we call getFieldRow and we use that row to get setting value
{...} DataRow dr = tblConfigFields.Rows.Find(dgvc.DataPropertyName); if (dr == null) continue; bool gridVisible = false; bool.TryParse(dr["gridVisible"].ToString(), out gridVisible); dgvc.Visible = gridVisible; {...}
DbdscEditForm - Form derrived
Contains OK/Cancel buttons and a splitter with two panels: one for grid (here we display fields and values) and one for some extra information about selected field. Fields are displayed in grid vertically and when user select a row (field correspondent) DataRow drConfig = config.getFieldRow(DenCmp) string strInfo = config.getFormInfo(drConfig) and update info panel content with strInfo Grid - is a DataGridView control with one column (and HeaderRow column). In row header display field associated text (formHeaderText) and in column field value DbdscEditForm contructor:public DbdscEditForm(DataRow dr, OleDbDataAdapter ad, Config config, DlgMode dlgMode, bool isDesign) { InitializeComponent(); this.dr = dr; this.tbl = dr.Table; this.ad = ad; this.dlgMode = dlgMode; this.config = config; this.Size = config.getFormSize(); this.splitGridInfo.SplitterDistance = config.getFormSplitDistance(); this.splitGridInfo.Panel2Collapsed = !config.getFormShowInfo(); this.grid.RowHeadersWidth = config.getFormRowHeaderWidth(); this.isDesign = isDesign; if (isDesign) { grid.AllowDrop = true; grid.AllowUserToResizeRows = true; #region Drag&&Drop events grid.DragDrop += new DragEventHandler(GridEditInreg_DragDrop); grid.DragOver += new DragEventHandler(GridEditInreg_DragOver); grid.MouseDown += new MouseEventHandler(GridEditInreg_MouseDown); grid.MouseMove += new MouseEventHandler(GridEditInreg_MouseMove); #endregion } }Editing form constructor parameters are: DataRow dr – when user select a row in DbdscControl grid and double-click or right click and press Edit DbdscControl read DataRow from grid OleDbDataAdapter ad – table adapter that corresponds to dr.Table Config config – config object – nedeed for fields settings DlgMode dlgMode – enum DlgMode { Add, Edit } – when user press OK the form needs to know what minmal validation performs, for example if is edit mode check if primary kes values are changed and the new values already exists in table bool isDesign – variable used to establish two modes: when isDesign is true some user inputs are saved in settings, for emaple if user re-order grid rows the new order is saved (in Fields - formIndex). isDesign is passed from DbdscControl In Load event of the form we add one line for each visible column (formVisible) and we apply settings. For that we iterate DataRow dr elements (contained in DataRow.ItemArray) and we use config object to retreive particular settings for each field
private void DbdscEditForm_Load(object sender, EventArgs e) { grid.GridStyles.Style = DataGridViewStyle.Sheet; grid.GridStyles.StyleColumn = GridStyle.Sheet; grid.GridStyles.StyleRow = GridStyle.Sheet; grid.ColumnHeadersHeightSizeMode = DataGridViewColumnHeadersHeightSizeMode.DisableResizing; grid.GridStyles.Style = DataGridViewStyle.List; for (int i = 0; i < dr.ItemArray.Length; i++) { DataRow drconfig = config.getFieldRow(tbl.Columns[i].ColumnName); if (drconfig == null) continue; bool visible = config.getFormVisible(drconfig); if (visible) { int index = config.getFormIndex(drconfig); int rowid = 0; if (index > grid.Rows.Count - 1 || index == -1) { rowid = grid.Rows.Add(dr[i]); } else { grid.Rows.Insert(index, 1); rowid = index; } grid.Rows[rowid].HeaderCell.Value = drconfig["formHeaderText"].ToString(); grid.Rows[rowid].HeaderCell.Tag = tbl.Columns[i].ColumnName; //field name is saved here grid.Rows[rowid].HeaderCell.Style.Padding = new Padding(24, 0, 0, 0); //grid.Rows[rowid].HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleRight; bool editable = config.getFormEditable(drconfig); if (!editable) { Color clrreadonly = SystemColors.ControlLight; grid.Rows[rowid].DefaultCellStyle.BackColor = clrreadonly; grid.Rows[rowid].ReadOnly = true; } grid.Rows[rowid].DefaultCellStyle.Alignment = config.getFormAlign(drconfig); bool wrap = config.getFormCellWrap(drconfig); if (wrap) grid.Rows[rowid].DefaultCellStyle.WrapMode = DataGridViewTriState.True; int height = config.getFormCellHeight(drconfig); if (height != 0) grid.Rows[rowid].Height = height; Color clr = config.getFormColor(drconfig); if (clr != Color.White) grid.Rows[rowid].DefaultCellStyle.BackColor = clr; string fmt = config.getFormFormat(drconfig); if (!string.IsNullOrEmpty(fmt)) grid.Rows[rowid].DefaultCellStyle.Format = fmt; #region create control string controledt = config.getFormControl(drconfig); switch (controledt) { case "Combo": string elemCombo = drconfig["formComboElem"].ToString(); string tblCombo = drconfig["formComboTableName"].ToString(); string cmpCombo = drconfig["formComboField"].ToString(); string condCombo = drconfig["formComboWhere"].ToString(); string sqlCombo = drconfig["formComboSQL"].ToString(); //DataGridViewMultiColumnComboCell cell_cmb = new DataGridViewMultiColumnComboCell(ad.SelectCommand.Connection, elemCombo, tblCombo, cmpCombo, condCombo, sqlCombo, this.Font); DataGridViewMultiColumnComboCell cell_cmb = new DataGridViewMultiColumnComboCell(ad.SelectCommand.Connection, elemCombo, tblCombo, cmpCombo, condCombo, sqlCombo); cell_cmb.DisplayStyle = DataGridViewComboBoxDisplayStyle.Nothing; Color alternate = config.getGridAlternateColor(); if (alternate != Color.White) cell_cmb.AlternateColor = alternate; grid.Rows[rowid].Cells[0] = cell_cmb; grid.Rows[rowid].Cells[0].Value = dr[i]; break; case "Calendar": CalendarCell cell_data = new CalendarCell(); grid.Rows[rowid].Cells[0] = cell_data; if (dr[i] != DBNull.Value) grid.Rows[rowid].Cells[0].Value = dr[i]; break; case "Checkbox": KryptonDataGridViewCheckBoxCell cell_chk = new KryptonDataGridViewCheckBoxCell(); grid.Rows[rowid].Cells[0] = cell_chk; grid.Rows[rowid].Cells[0].Value = dr[i]; break; default: object obj = dr[i]; Type t = obj.GetType(); if (t.FullName != "System.Byte[]") { DataGridViewTextBoxCell cell_string = new DataGridViewTextBoxCell(); grid.Rows[rowid].Cells[0] = cell_string; grid.Rows[rowid].Cells[0].Value = dr[i]; } else { DataGridViewImageCell cell_img = new DataGridViewImageCell(); grid.Rows[rowid].Cells[0] = cell_img; grid.Rows[rowid].Cells[0].Value = dr[i]; } break; } #endregion } } }Rows are put in order given by formIndex and take all needed settings from Fields table (Dbdsc.mdb). Another aspect is control associated with field. Control si taken from same Fields table – formControl wich can be ‘Calendar’, ‘Checkbox’, ‘Combo’. Default value for formControl is NULL and corresponds with DataGridViewTextBoxCell. When user press OK button DbdscEditForm performs a minimal validation using some function from Helper class and DataRow dr (for edit mode dr contains old values) and using functions from config object getFormRequired. First construct pkval object array wich represent new primary keys values. Then compare it with values existing in data table. If new values are already in table then construct a message string to user and return false.
private bool Validation() { bool retval = true; object[] pkval = GetPrimayKeyValueFromGrid(); if (dlgMode == DlgMode.Add) { DataRow drex = tbl.Rows.Find(pkval); if (drex != null) { string[] info = Helper.GetPrimayKeyDisplayedNames(tbl, config); MessageBox.Show("Duplicate value for " + Helper.Concat(info, "; ")); return false; } } if (dlgMode == DlgMode.Edit) { object[] oldValues = Helper.GetPrimayKeyValueFromDataRow(tbl, dr); if (!Helper.Equals(oldValues, pkval)) //primary key value has changed { DataRow drex = tbl.Rows.Find(pkval); if (drex != null) { string[] info = Helper.GetPrimayKeyDisplayedNames(tbl, config); MessageBox.Show("Duplicate value for " + Helper.Concat(info, "; ")); return false; } } } //verify required fields for (int i = 0; i < grid.Rows.Count; i++) { string fieldname = grid.Rows[i].HeaderCell.Tag.ToString(); object value = grid.Rows[i].Cells[0].Value; string fieldnamedisplayed = grid.Rows[i].HeaderCell.Value.ToString(); if (value == null || string.IsNullOrEmpty(value.ToString())) { #region verify null value for required fields DataRow drconfig = config.getFieldRow(fieldname); if (drconfig != null) { if (config.getFormRequired(drconfig)) { grid.ClearSelection(); grid.CurrentCell = grid.Rows[i].Cells[0]; grid.Rows[i].Cells[0].Selected = true; MessageBox.Show("Enter value for " + fieldnamedisplayed); retval = false; break; } } #endregion } } return retval; }After validation the values from grid are converted back to original type (in grid are stored like strings) and passed to DataRow dr. Then save row in table. If isDesign is set to true performs settings save.
private void ok_Click(object sender, EventArgs e) { #region validation if (!Validation()) { this.DialogResult = DialogResult.None; return; } #endregion #region unformat value //here we must obtain back value without format for (int i = 0; i < grid.Rows.Count; i++) { string fieldname = grid.Rows[i].HeaderCell.Tag.ToString(); object formattedvalue = grid.Rows[i].Cells[0].Value; if (formattedvalue == null || formattedvalue == string.Empty) dr[fieldname] = DBNull.Value; else { switch (dr[fieldname].GetType().FullName) { case "System.Int16": Int16 valint16 = 0; Int16.TryParse(formattedvalue.ToString(), out valint16); dr[fieldname] = valint16; break; case "System.Int32": Int32 valint32 = 0; Int32.TryParse(formattedvalue.ToString(), out valint32); dr[fieldname] = valint32; break; case "System.DateTime": DateTime valdt; DateTime.TryParse(formattedvalue.ToString(), out valdt); dr[fieldname] = valdt; break; case "System.Decimal": decimal valdec = 0; decimal.TryParse(formattedvalue.ToString(), out valdec); dr[fieldname] = valdec; break; case "System.Double": double valdbl = 0; double.TryParse(formattedvalue.ToString(), out valdbl); dr[fieldname] = valdbl; break; default: //string dr[fieldname] = formattedvalue; break; } } } #endregion #region prepare transaction OleDbTransaction t = ad.SelectCommand.Connection.BeginTransaction(); ad.SelectCommand.Transaction = t; ad.UpdateCommand.Transaction = t; ad.InsertCommand.Transaction = t; ad.DeleteCommand.Transaction = t; #endregion #region if nedeed add data row to table if (dlgMode == DlgMode.Add) tbl.Rows.Add(dr); #endregion #region save try { ad.Update(tbl); t.Commit(); } catch (Exception ex) { t.Rollback(); MessageBox.Show(ex.Message); return; } tbl.AcceptChanges(); #endregion #region save settings if (isDesign) { config.setFormSize(this.Size); config.setFormSplitDistance(splitGridInfo.SplitterDistance); config.setFormRowHeaderWidth(grid.RowHeadersWidth); config.setFormRowsIndex(grid); config.setFormRowsHeights(grid); } #endregion }
DbdscControl class (derrived from UserControl)
Variables OleDbConnection cn - connection to database DataTable tbl – data table OleDbDataAdapter ad – adapter for tbl OleDbCommandBuilder bld – command builder for adapter ad string TableName – name of the table string[] PrimaKey – array of primary keys names Config config – config object bool isApplyConfig – varible use to prevent multiple event raise bool isDesign – when true settings maded are saved string Sort – ORDER BY clause //"Field1, Field2, ..." bool ColumnsOrderChaged – indicates if user chaged column order – is used in OnHandleDestroyed function for saving new order DbdscControl contains components are: DataGridView grid – used to display data StatusStrip – statusStrip containing a DropDownButton btnRecords ContextMenuStrip ctxMenu - menu control associate to statusStrip.btnRecords and grid.ContextMenuStrip. ctxMenu contain Add, Edit, Delete and Config items. The main function is Init wich takes connection string, table name and ‘where’ clause (there is another implementation without ‘where’ clause). Init performs following steps: 1. open connection then retreive primary keys names with Helper.GetPrimaryKeys function 2. construct sort clause based on primary keys names (like ‘Field1’, ‘Field2’,…) 3. construct Config object based on table name 4. construct OleDbDataAdapter and fill data table with data 5. define in data table primary keys 6. set grid data source to tablepublic void Init(string connectionString, string TableName, string Where) { this.cn = new OleDbConnection(connectionString); cn.Open(); this.TableName = TableName; this.PrimaKey = Helper.GetPrimaryKeys(cn, TableName); if (PrimaKey == null || PrimaKey.Length == 0) { //throw new NotImplementedException("You must define primary key for " + TableName); MessageBox.Show("Table " + TableName + " primary key missing"); cn.Close(); return; } Cursor = Cursors.WaitCursor; for (int i = 0; i < PrimaKey.Length; i++) { if (!string.IsNullOrEmpty(Sort)) Sort += ","; Sort += "[" + PrimaKey[i] + "]"; } grid.ColumnHeadersHeightSizeMode = DataGridViewColumnHeadersHeightSizeMode.DisableResizing; config = new Config(TableName); isApplyConfig = true; string strSQL = "SELECT * FROM " + TableName; if (!string.IsNullOrEmpty(Where)) strSQL += " WHERE " + Where; if (!string.IsNullOrEmpty(Sort)) strSQL += " ORDER BY " + Sort; ad = new OleDbDataAdapter(strSQL, cn); tbl.BeginLoadData(); ad.Fill(tbl); config.CreateSettings(tbl); tbl.TableName = TableName; DataColumn[] keys = new DataColumn[PrimaKey.Length]; for (int i = 0; i < PrimaKey.Length; i++) keys[i] = tbl.Columns[PrimaKey[i]]; tbl.PrimaryKey = keys; tbl.EndLoadData(); bld = new OleDbCommandBuilder(ad); bld.QuotePrefix = "["; bld.QuoteSuffix = "]"; ad.InsertCommand = bld.GetInsertCommand(); ad.DeleteCommand = bld.GetDeleteCommand(); ad.UpdateCommand = bld.GetUpdateCommand(); grid.DataSource = tbl; tbl.DefaultView.Sort = Sort; grid.AutoGenerateColumns = false; //that's for display index bug config.Apply(this); isApplyConfig = false; Cursor = Cursors.Default; }ctxMenu events When adding record we construct a new DbdscEditForm based on DataRow structure returned by tbl.NewRow(). After dialog was closed we try to select in grid the new row.
private void mnuAddRecord_Click(object sender, EventArgs e) { DataRow dr = tbl.NewRow(); DbdscEditForm edform = new DbdscEditForm(dr, ad, config, DlgMode.Add, isDesign); if (edform.ShowDialog() == DialogResult.OK) { #region try to select added row object[] pkval = Helper.GetPrimayKeyValueFromDataRow(tbl, dr); int index = tbl.DefaultView.Find(pkval); if (index != -1) { grid.ClearSelection(); grid.Rows[index].Selected = true; } #endregion } }For editing we obtain a DataRow via DataGridView.SelectedRows[index]. DataBoundItem.
private void mnuEditRecord_Click(object sender, EventArgs e) { if (grid.SelectedRows.Count > 1) return; DataRowView drv = (DataRowView)grid.SelectedRows[0].DataBoundItem; DbdscEditForm edform = new DbdscEditForm(drv.Row, ad, config, DlgMode.Edit, isDesign); edform.ShowDialog(); }Deleting use GetDeleteCommand function wich builds a OleDbCommand using primary keys names and values from selected DataRow
private OleDbCommand GetDeleteCommand(DataRow dr) { OleDbCommand cmd = new OleDbCommand(); string strSQL = "DELETE * FROM " + TableName + " WHERE "; string strWhere = string.Empty; for (int i = 0; i < PrimaKey.Length; i++) { if (!string.IsNullOrEmpty(strWhere)) strWhere += " AND "; strWhere += "[" + PrimaKey[i] + "]=@p" + i; OleDbParameter p = new OleDbParameter("@p" + i, dr[PrimaKey[i]]); cmd.Parameters.Add(p); } cmd.CommandText = strSQL + strWhere; return cmd; } private void mnuDeleteRecord_Click(object sender, EventArgs e) { if (grid.SelectedRows.Count > 1) return; DialogResult dlgres = MessageBox.Show("You are sure?", "Delete records", MessageBoxButtons.YesNo, MessageBoxIcon.Question); if (dlgres != DialogResult.Yes) return; DataRowView drv = (DataRowView)grid.SelectedRows[0].DataBoundItem; OleDbTransaction t = ad.SelectCommand.Connection.BeginTransaction(); OleDbCommand delCommand = GetDeleteCommand(drv.Row); delCommand.Connection = ad.SelectCommand.Connection; delCommand.Transaction = t; tbl.Rows.Remove(drv.Row); try { delCommand.ExecuteNonQuery(); t.Commit(); } catch (Exception ex) { t.Rollback(); MessageBox.Show("Error deleting record\n" + ex.Message); } }mnuConfig command opens configuration form
private void mnuConfig_Click(object sender, EventArgs e) { ConfigGridForm cfg = new ConfigGridForm(config, tbl); if (cfg.ShowDialog() == DialogResult.OK) { isApplyConfig = true; config.Apply(this); isApplyConfig = false; } }
Example application
In the example application in main form I placed a combo box that displays the list of tables in the database Xtreme.mdb:
private void DbdscTest_Load(object sender, EventArgs e) { DataTable listTables = Helper.GetTables(connectionString); if (listTables != null && listTables.Rows.Count > 0) { cmbTables.ComboBox.BeginUpdate(); cmbTables.ComboBox.DataSource = listTables; cmbTables.ComboBox.DisplayMember = listTables.Columns["TABLE_NAME"].ColumnName; cmbTables.ComboBox.ValueMember = listTables.Columns["TABLE_NAME"].ColumnName; cmbTables.ComboBox.EndUpdate(); } cmbTables.SelectedIndexChanged += new EventHandler(cmbTables_SelectedIndexChanged); }
To obtain list of tables we used the following function which is in Class Helper in Dbdsc:
static public DataTable GetTables(string connectionString) { DataTable retVal = null; OleDbConection cn = new OleDbConnection(connectionString); using (cn) { cn.Open(); retVal = cn.GetOleDbSchemaTable( OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" }); cn.Close(); } return retVal; }
This function returns a table containing the column “TABLE_NAME” (see Helper class). When we select a table from cmbTables:
private void cmbTables_SelectedIndexChanged(object sender, EventArgs e) { if (string.IsNullOrEmpty(cmbTables.Text)) return; //remove Dbdsc control Control[] olddbdsc = kryptonPanel.Controls.Find("Dbdsc", true); foreach (Control ctrl in olddbdsc) { kryptonPanel.Controls.Remove(ctrl); ctrl.Dispose(); } //add new Dbdsc control DbdscControl dbdsc = new DbdscControl(); dbdsc.Dock = DockStyle.Fill; kryptonPanel.Controls.Add(dbdsc); dbdsc.Init(connectionString, "[" + cmbTables.Text + "]"); }
To use DbdscControl add path to dll (References / Add Reference and add Dbdsc.dll and ComponentFactory.Krypton.Toolkit.dll) then in code call dbdsc.Init function as described above.
Conclusion
We can save time and energy using such an approach, generalizing the problem. However problems may arise, for example specific validation tables every need being addressed. PS sorry for poor english