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
Functions
GetPrimayKeyValueFromDataRow 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;
string strRowNumber = (e.RowIndex + 1).ToString();
while (strRowNumber.Length < grid.RowCount.ToString().Length) strRowNumber = "0" + strRowNumber;
SizeF size = e.Graphics.MeasureString(strRowNumber, grid.Font);
if (grid.RowHeadersWidth < (int)(size.Width + 20)) grid.RowHeadersWidth = (int)(size.Width + 20);
Brush b = SystemBrushes.ControlText;
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 variables
DataTable 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();
}
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;
}
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;
}
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):
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;
grid.Rows[rowid].HeaderCell.Style.Padding = new Padding(24, 0, 0, 0);
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);
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))
{
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;
}
}
}
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
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:
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 table
public 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)
{
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;
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;
Control[] olddbdsc = kryptonPanel.Controls.Find("Dbdsc", true);
foreach (Control ctrl in olddbdsc)
{
kryptonPanel.Controls.Remove(ctrl);
ctrl.Dispose();
}
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
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
.