private void gridGroupingControl1_TableControlCellDrawn(object sender, GridTableControlDrawCellEventArgs e)
{
GridTableCellStyleInfo style = e.TableControl.Model[e.Inner.RowIndex, e.Inner.ColIndex];
GridTableCellStyleInfoIdentity id = style.TableCellIdentity;
Element el = id.DisplayElement;
if (el is ExtraSection)
{
if (id.ColIndex == 0)
{
// Row Header and ExtraSection is CurrentElement
if (el == el.ParentTable.CurrentElement)
{
// Draw Record indicator
try
{
Rectangle iconBounds = Rectangle.FromLTRB(e.Inner.Bounds.Right - 15, e.Inner.Bounds.Top, e.Inner.Bounds.Right, e.Inner.Bounds.Bottom);
iconBounds.Offset(-2, 0);
IconPainter.PaintIcon(e.Inner.Graphics, iconBounds, Point.Empty, "SFARROW.BMP", style.TextColor);
}
catch
{ }
}
}
}
}
/// <summary>
/// Using the Cell Button for Expand an collapse in NestedTable.
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
void gridGroupingControl1_TableControlCellButtonClicked(object sender, GridTableControlCellButtonClickedEventArgs e)
{
GridTableCellStyleInfo style = this.gridGroupingControl1.Table.GetTableCellStyle(e.Inner.RowIndex, e.Inner.ColIndex);
if (style.TableCellIdentity == null)
{
return;
}
if (style.TableCellIdentity.TableCellType == GridTableCellType.RecordPlusMinusCell)
{
if (style.TableCellIdentity.DisplayElement.Kind == DisplayElementKind.NestedTable)
{
// NestedTable
GridNestedTable nt = (GridNestedTable)style.TableCellIdentity.DisplayElement;
foreach (Element el in nt.ChildTable.Elements)
{
if (el.Kind == DisplayElementKind.Record)
{
if (style.TableCellIdentity.RowIndex == this.gridGroupingControl1.Table.NestedDisplayElements.IndexOf(el))
{
Record r = Record.GetParentRecord(el);
bool shouldExpand = !r.IsExpanded;
this.gridGroupingControl1.Table.CurrentRecord = r;
r.IsExpanded = shouldExpand;
e.Inner.Cancel = true;
}
}
}
}
}
}
//control the current cell
void gridGroupingControl1_TableControlCurrentCellActivated(object sender, GridTableControlEventArgs e)
{
// Retrieve the current element
Element el = GetNestedCurrentElement();
if (el != null)
{
Record r = el.GetRecord();
// current field.
if (r != null)
{
GridTable table = (GridTable)r.ParentDetails.ParentChildTable.ParentTable;
FieldDescriptor fd = table.CurrentRecordManager.CurrentField;
if (fd != null)
{
this.lblCell.Text = r.GetValue(fd.Name).ToString();
// TableCellStyle
GridTableCellStyleInfo style = table.GetTableCellStyle(r, fd);
this.lblCell.Text += " (" + style.ToString().Replace('\n', ';') + ")";
GridTableCellStyleInfoIdentity identity = style.TableCellIdentity;
// you can check identity for more information about cell.
}
}
// Record
this.lblElement.Text = el.ToString();
}
}
/// <summary>
/// Helper routine for drawing picture in covered range of column Employees_Photo.
/// </summary>
void TableModel_QueryCoveredRange(object sender, GridQueryCoveredRangeEventArgs e)
{
GridTableModel model = (GridTableModel)sender;
GridTable table = model.Table;
if (e.RowIndex < table.DisplayElements.Count)
{
// Get the element displayed at the row
Element el = table.DisplayElements[e.RowIndex];
// Check if element is a record (could also be a column header or group caption ...)
if (Element.IsRecord(el) && el.ParentGroup != null)
{
GridTableCellStyleInfo style = model[e.RowIndex, e.ColIndex];
GridTableCellStyleInfoIdentity id = style.TableCellIdentity;
// The cell identity contains column information. We check if the column is Employees_Photo
if (id.Column != null && id.Column.MappingName == Employees_Photo)
{
Group group = el.ParentGroup;
// Grouped by Customers_ContactName - Do not cover cells if not grouped by Employee ...
if (group != null && group.CategoryColumns.Count > 0 && group.CategoryColumns[0].Name == Customers_ContactName)
{
int tablePos = table.DisplayElements.IndexOf(group);
int firstRow = tablePos + 1;
int lastRow = tablePos + group.GetVisibleCount() - 1;
e.Range = GridRangeInfo.Cells(firstRow, e.ColIndex, lastRow, e.ColIndex);
gridGroupingControl1.TableModel.CoveredRanges.Add(e.Range);
e.Handled = true;
}
}
}
}
}
void gridGroupingControl1_TableControlCellMouseHover(object sender, GridTableControlCellMouseEventArgs e)
{
GridTableCellStyleInfo style = e.TableControl.GetTableViewStyleInfo(e.Inner.RowIndex, e.Inner.ColIndex);
Element el = e.TableControl.PointToNestedDisplayElement(e.Inner.MouseEventArgs.Location);
if (el != null && el.Kind == DisplayElementKind.FilterBar)
{
int row = el.ParentTable.Elements.IndexOf(el);
GridCellLayout cellLayout;
cellLayout = e.TableControl.GetCellRenderer(row, e.Inner.ColIndex).GetCellLayout(row, e.Inner.ColIndex, style);
Rectangle rect = new Rectangle(e.Inner.MouseEventArgs.Location, new Size(1, 1));
rect.X += 1;
rect.Y -= 1;
if (cellLayout.Buttons.Length > 1)
{
if (cellLayout.Buttons[0].IntersectsWith(rect))
{
toolTip.SetToolTip(e.TableControl, "The collection of unique items which are available in this column.");
toolTip.Active = true;
}
else if (cellLayout.Buttons[1].IntersectsWith(rect))
{
toolTip.SetToolTip(e.TableControl, "The collection of compare operators to filter the column.");
toolTip.Active = true;
}
}
}
}
public Form1()
{
//
// Required for Windows Form Designer support
//
InitializeComponent();
if (DpiAware.GetCurrentDpi() > 96)
{
this.CaptionBarHeight = (int)DpiAware.LogicalToDeviceUnits(this.CaptionBarHeight);
}
gridGroupingControl1 = new GridGroupingControl();
// now assign the datasource
gridGroupingControl1.DataSource = table;
SampleCustomization();
GridSettings();
Console.WriteLine(GridPropertyTypeDefaultStyleCollection.Default["System.Boolean"].Style);
// enable/disable UITYpeEditors for boolean cells only and show checkbox instead.
bool displayCheckBoxForBooleanFields = false;
if (displayCheckBoxForBooleanFields)
{
GridPropertyTypeDefaultStyle booleanDefault = this.gridGroupingControl1.Engine.PropertyTypeDefaultStyles["System.Boolean"];
booleanDefault.AllowDropDown = false;
}
#region Sample code to get the row and column index
// Sample code to get the row and column index in the grid for a column (works also
// if column sets with multiple rows are specified).
bool useOldCodeToGetCellInfo = false;
if (useOldCodeToGetCellInfo)
{
GridColumnDescriptor cd = this.gridGroupingControl1.TableDescriptor.Columns["Boolean"];
int relativeRowIndex, colIndex;
this.gridGroupingControl1.TableDescriptor.ColumnToRowColIndex(cd.MappingName, out relativeRowIndex, out colIndex);
Record r = this.gridGroupingControl1.Table.Records[0];
int recordRowIndex = this.gridGroupingControl1.Table.DisplayElements.IndexOf(r);
int rowIndex = recordRowIndex + relativeRowIndex;
GridTableCellStyleInfo style = this.gridGroupingControl1.Table.GetTableCellStyle(rowIndex, colIndex + this.gridGroupingControl1.TableDescriptor.GetColumnIndentCount());
Console.WriteLine(style.TableCellIdentity.ToString());
Console.WriteLine(style.ToString());
}
else
{
// Newer code using new GetTableCellStyle overloads after version 3.0.0.16
Record r = this.gridGroupingControl1.Table.Records[0];
GridTableCellStyleInfo style = this.gridGroupingControl1.Table.GetTableCellStyle(r, "Boolean");
Console.WriteLine(style.TableCellIdentity.ToString());
Console.WriteLine(style.ToString());
}
#endregion
}
private void gridGroupingControl1_TableControlCurrentCellActivating(object sender, Syncfusion.Windows.Forms.Grid.Grouping.GridTableControlCurrentCellActivatingEventArgs e)
{
GridTableCellStyleInfo style = this.gridGroupingControl1.TableControl.GetTableViewStyleInfo(e.Inner.RowIndex, e.Inner.ColIndex);
if (style.TableCellIdentity.TableCellType == GridTableCellType.RecordFieldCell || style.TableCellIdentity.TableCellType == GridTableCellType.AlternateRecordFieldCell)
{
e.Inner.Cancel = true;
}
}
// In case you want to handle other Mouse events like Mouse Move and Mouse Hover
// you sometimes need to handle groupingGrid1_TableControlCellHitTest
private void gridGroupingControl1_TableControlCellHitTest(object sender, GridTableControlCellHitTestEventArgs e)
{
GridTableCellStyleInfo style = (GridTableCellStyleInfo)e.TableControl.Model[e.Inner.RowIndex, e.Inner.ColIndex];
if (style.TableCellIdentity.TableCellType == GridTableCellType.ColumnHeaderCell)
{
// Uncommenting this will make gridGroupingControl1_TableControlCellClick not being hit
// for ColumnHeaderCell
//e.Inner.Cancel = true;
//e.Inner.Result = 0;
}
}
void GridSettings()
{
comboTable = this.GetTheComboTable();
gridTable = this.GetTheGridTable();
this.gridGroupingControl1.DataSource = this.GetTheGridTable();
gridGroupingControl1.TopLevelGroupOptions.ShowFilterBar = true;
gridGroupingControl1.TopLevelGroupOptions.ShowAddNewRecordBeforeDetails = false;
GridTableCellStyleInfo ggcStyle = gridGroupingControl1.TableDescriptor.Columns["Customers"].Appearance.AnyRecordFieldCell;
ggcStyle.CellType = "ComboBox";
ggcStyle.DataSource = this.comboTable;
ggcStyle.ValueMember = "CustomerID";
ggcStyle.DisplayMember = "CustomerName";
this.gGCFilter = new GroupingGridFilterBarExt();
this.gridGroupingControl1.TableControl.DpiAware = true;
gridGroupingControl1.TableModel.ColWidths.ResizeToFit(GridRangeInfo.Rows(1, 10));
gridGroupingControl1.TableModel.ColWidths[2] = 150;
gridGroupingControl1.QueryCellStyleInfo += new GridTableCellStyleInfoEventHandler(gridGroupingControl1_QueryCellStyleInfo);
this.gridGroupingControl1.GridVisualStyles = GridVisualStyles.Metro;
this.BackColor = Color.White;
this.propertyGrid1.BackColor = Color.White;
this.propertyGrid1.SelectedObject = this.gridGroupingControl1;
this.gridGroupingControl1.AllowProportionalColumnSizing = true;
this.gridGroupingControl1.TableOptions.ListBoxSelectionMode = SelectionMode.MultiExtended;
//Navigate to other control using tabkey navigation
this.gridGroupingControl1.WantTabKey = false;
#region HeaderText
foreach (GridColumnDescriptor col in this.gridGroupingControl1.TableDescriptor.Columns)
{
Regex rex = new Regex(@"\p{Lu}");
int index = rex.Match(col.MappingName.Substring(1)).Index;
string name = "";
while (index > 0)
{
name += col.MappingName.Substring(0, index + 1) + " ";
string secondName = col.MappingName.Substring(index + 1);
index = rex.Match(secondName.Substring(1)).Index;
while (index > 0)
{
name += secondName.Substring(0, index + 1) + " ";
index = rex.Match(col.MappingName.Substring(name.Replace(" ", "").Length).Substring(1)).Index;
}
}
name += col.MappingName.Substring(name.Replace(" ", "").Length);
col.HeaderText = name;
}
#endregion
}
/// <summary>
/// Disable sorting for dependet columns based on other records
/// </summary>
void gridGroupingControl1_TableControlCellClick(object sender, GridTableControlCellClickEventArgs e)
{
GridTableCellStyleInfo style = e.TableControl.Model[e.Inner.RowIndex, e.Inner.ColIndex] as GridTableCellStyleInfo;
if (style != null &&
style.TableCellIdentity.DisplayElement is GridColumnHeaderRow &&
style.TableCellIdentity.Column != null &&
style.TableCellIdentity.Column.Name.IndexOf("Count") != -1)
{
MessageBox.Show("Can't sort by this column. Its contents are dependant on the order of records and will change if you sort by another column.");
e.Inner.Cancel = true;
}
}
private void gridGroupingControl1_TableControlCurrentCellActivated(object sender, GridTableControlEventArgs e)
{
// Navigate to ExtraSection when textbox inside ExtraSection has become CurrentCell.
GridCurrentCell gcc = e.TableControl.CurrentCell;
GridTableCellStyleInfo style = e.TableControl.Model[gcc.RowIndex, gcc.ColIndex];
GridTableCellStyleInfoIdentity id = style.TableCellIdentity;
if (id.DisplayElement is ExtraSection)
{
id.Table.CurrentRecordManager.NavigateTo(id.DisplayElement);
}
}
void gridGroupingControl1_TableControlPrepareViewStyleInfo(object sender, GridTableControlPrepareViewStyleInfoEventArgs e)
{
GridTableCellStyleInfo style = (GridTableCellStyleInfo)e.Inner.Style;
BlinkState bs = gridGroupingControl1.GetBlinkState(style.TableCellIdentity);
if (bs != BlinkState.None)
{
if (bs == BlinkState.NewRecord)
{
e.Inner.Style.BaseStyle = "CustomStyle";
}
}
}
private void gridGroupingControl1_TableControlCellClick(object sender, GridTableControlCellClickEventArgs e)
{
if (e.Inner.MouseEventArgs.Button == MouseButtons.Left)
{
if (e.Inner.RowIndex > 1)
{
GridTableCellStyleInfo style = e.TableControl.GetTableViewStyleInfo(e.Inner.RowIndex, e.Inner.ColIndex);
if (style.TableCellIdentity.TableCellType == GridTableCellType.GroupCaptionPlusMinusCell)
{
GridCaptionRow curGrp = e.TableControl.Table.NestedDisplayElements[e.Inner.RowIndex] as GridCaptionRow;
curGrp.ParentGroup.IsExpanded = !curGrp.ParentGroup.IsExpanded;
}
}
}
}
void grid_TableControlPrepareViewStyleInfo(object sender, GridTableControlPrepareViewStyleInfoEventArgs e)
{
GridTableCellStyleInfo style = e.Inner.Style as GridTableCellStyleInfo;
if (!showCellBorderInTreeCell)
{
if (style.TableCellIdentity.ColIndex == TREECOLUMN)
{
style.Borders.Bottom = GridBorder.Empty;
}
}
if (colorCodeSiblings && style.TableCellIdentity.DisplayElement.GetRecord() != null)
{
style.BackColor = GetIndentLevelColor(GetNode(e.Inner.RowIndex).Indent);
}
}
void TableControl_PrepareViewStyleInfo(object sender, Syncfusion.Windows.Forms.Grid.GridPrepareViewStyleInfoEventArgs e)
{
GridTableCellStyleInfo style = (GridTableCellStyleInfo)e.Style;
GridTableCellStyleInfoIdentity id = style.TableCellIdentity;
// Cell identity contains information about the cell (element, column etc.)
// Check if this is a indent cell
if (id.TableCellType == GridTableCellType.GroupIndentCell)
{
Group group = id.DisplayElement.ParentGroup;
// Check if this is the indent of a Categories_CategoryName group.
if (group != null && group.CategoryColumns.Count > 0 && group.CategoryColumns[0].Name == Customers_ContactName)
{
// And if the group is not empty ...
if (group.Records.Count > 0)
{
// Get the category name from the first record
Record r = group.Records[0];
object value = r.GetValue(Customers_ContactName);
// Should be a string
string empName = value as string;
// Assign the name to the CellValue of the Indent cell and also adjust the font, color and other style settings.
if (empName != null)
{
style.Interior = new BrushInfo(GradientStyle.Vertical, Color.FromArgb(219, 226, 242), Color.FromArgb(255, 187, 111));
style.CellValue = empName;
style.CellType = "Static";
style.HorizontalAlignment = GridHorizontalAlignment.Center;
style.VerticalAlignment = GridVerticalAlignment.Middle;
style.Trimming = StringTrimming.EllipsisCharacter;
style.Font.Bold = true;
style.Font.Italic = true;
style.Font.Orientation = 270;
}
}
}
}
if (e.Style.CellType.ToString() == "Image")
{
e.Style.ImageSizeMode = GridImageSizeMode.CenterImage;
}
}
private void gridGroupingControl1_TableControlCellMouseHover(object sender, GridTableControlCellMouseEventArgs e)
{
GridTableCellStyleInfo style = (GridTableCellStyleInfo)e.TableControl.Model[e.Inner.RowIndex, e.Inner.ColIndex];
GridTableCellStyleInfoIdentity id = style.TableCellIdentity;
//
// Note the check for not being a GridTableCellType.NestedTableCell below. If the mouse hovers
// over an area of a nested table, this event will get hit twice: First for the outer
// TableControl (which routes the event to the inner TableControl) and then afterward
// for the inner TableControl. For the outer TableControl the TableCellType
// is GridTableCellType.NestedTableCell
//
if (id.TableCellType != GridTableCellType.NestedTableCell)
{
string column = (id.Column != null) ? id.Column.ToString() : "";
Console.WriteLine("MouseHover over " + id.TableCellType.ToString() + "(" + column + ", " + id.DisplayElement.GetType().Name + ")");
}
}
void grid_TableControlCellClick(object sender, GridTableControlCellClickEventArgs e)
{
GridTableCellStyleInfo style = e.TableControl.GetTableViewStyleInfo(e.Inner.RowIndex, e.Inner.ColIndex);
if (style.TableCellIdentity.TableCellType == GridTableCellType.ColumnHeaderCell &&
style.TableCellIdentity.Column != null)
{
if (style.TableCellIdentity.Column.Name == sortColumn)
{
Sort(sortColumn);
}
else
{
sortDirection = ListSortDirection.Ascending;
Sort(style.TableCellIdentity.Column.Name);
}
e.Inner.Cancel = true;
}
}
private void gridGroupingControl1_TableControlCellHitTest(object sender, GridTableControlCellHitTestEventArgs e)
{
gridGroupingControl1.TableModel.Options.SelectCellsMouseButtonsMask = MouseButtons.Left | MouseButtons.Right;
if (e.Inner.MouseEventArgs.Button == System.Windows.Forms.MouseButtons.Right)
{
GridCurrentCell cc = e.TableControl.CurrentCell;
GridTableCellStyleInfo style = e.TableControl.Model[e.Inner.RowIndex, e.Inner.ColIndex] as GridTableCellStyleInfo;
SelectedRowForDT = (e.TableControl.TableDescriptor.ColIndexToField(e.Inner.RowIndex) - 2);
SelectedColumForDT = e.TableControl.TableDescriptor.ColIndexToField(e.Inner.ColIndex);
SelectedHeaderText = style.TableCellIdentity.Column.MappingName;
contextMenuStrip1.Show(gridGroupingControl1, e.Inner.MouseEventArgs.X, e.Inner.MouseEventArgs.Y);
}
}
private void gridGroupingControl1_TableControlCellClick(object sender, GridTableControlCellClickEventArgs e)
{
GridTableCellStyleInfo style = (GridTableCellStyleInfo)e.TableControl.Model[e.Inner.RowIndex, e.Inner.ColIndex];
GridTableCellStyleInfoIdentity id = style.TableCellIdentity;
if (id.TableCellType == GridTableCellType.ColumnHeaderCell
)
{
// Workaround - gridGroupingControl1_TableControlCellButtonClicked does not get hit so
// we'll check for the area where the button is and set e.Inner.Cancel = true
GridCellRendererBase r = e.TableControl.CellRenderers[style.CellType];
r.PerformLayout(e.Inner.RowIndex, e.Inner.ColIndex, style, e.TableControl.RangeInfoToRectangle(GridRangeInfo.Cell(e.Inner.RowIndex, e.Inner.ColIndex)));
if (r.RaiseHitTest(e.Inner.RowIndex, e.Inner.ColIndex, e.Inner.MouseEventArgs, null) == GridHitTestContext.CellButtonElement)
{
string column = (id.Column != null) ? id.Column.ToString() : "";
string s = "Clicked on " + id.TableCellType.ToString() + "(" + column + ", " + id.DisplayElement.GetType().Name + ")";
MessageBox.Show(s);
e.Inner.Cancel = true;
}
}
}
void gridGroupingControl1_QueryCellStyleInfo(object sender, GridTableCellStyleInfoEventArgs e)
{
GridTableCellStyleInfo style = (GridTableCellStyleInfo)e.Style;
if (e.TableCellIdentity.TableCellType == GridTableCellType.RecordFieldCell ||
e.TableCellIdentity.TableCellType == GridTableCellType.AlternateRecordFieldCell)
{
if (e.TableCellIdentity.Column.FieldDescriptor.FieldPropertyType == typeof(string))
{
return;
}
{
// Get the value from column "1" and color all cells in record based
// on this value.
Record r = e.Style.TableCellIdentity.DisplayElement.GetRecord();
object value = r.GetValue("1");
int v = Convert.ToInt32(value) % colors.Length;
e.Style.BackColor = colors[v];
}
}
}
private void gridGroupingControl1_TableControlCellButtonClicked(object sender, GridTableControlCellButtonClickedEventArgs e)
{
GridTableCellStyleInfo style = (GridTableCellStyleInfo)e.TableControl.Model[e.Inner.RowIndex, e.Inner.ColIndex];
GridTableCellStyleInfoIdentity id = style.TableCellIdentity;
if (id.TableCellType != GridTableCellType.NestedTableCell)
{
string column = (id.Column != null) ? id.Column.ToString() : "";
string s = "TableControlCellButtonClicked on " + id.TableCellType.ToString() + "(" + column + ", " + id.DisplayElement.GetType().Name + ")";
Console.WriteLine(s);
e.Inner.Cancel = true;
}
if (id.TableCellType == GridTableCellType.RecordPlusMinusCell)
{
// Expand record without moving current record to that record (change default behavior
// of grid).
Record r = id.DisplayElement.ParentRecord;
r.IsExpanded = !r.IsExpanded;
e.Inner.Cancel = true; // don't let grid handle CellButtonClicked - otherwise it would reverse the IsExpanded call.
}
}
/// <summary>
/// Helper routine for drawing picture in covered range of column Employees_Photo.
/// </summary>
void gridGroupingControl1_TableControlCellDrawn(object sender, GridTableControlDrawCellEventArgs e)
{
GridTableCellStyleInfo style = (GridTableCellStyleInfo)e.Inner.Style;
GridTableCellStyleInfoIdentity id = style.TableCellIdentity;
// Employees_Photo column
if (id.Column != null && id.Column.MappingName == Employees_Photo)
{
Group group = id.DisplayElement.ParentGroup;
// Grouped by Customers_ContactName
if (group != null && group.CategoryColumns.Count > 0 && group.CategoryColumns[0].Name == Customers_ContactName)
{
if (group.Records.Count > 0)
{
Record r = group.Records[0];
object value = r.GetValue(id.Column.FieldDescriptor);
// Should be byte[] (image stream ...)
byte[] byteStream = value as byte[];
if (byteStream != null)
{
Graphics graphics = e.Inner.Graphics;
Image image = GridImageUtil.ConvertToImage(byteStream);
Rectangle bounds = e.Inner.Bounds;
bounds = GridMargins.RemoveMargins(bounds, style.TextMargins.ToMargins());
bounds = GridMargins.RemoveMargins(bounds, style.BorderMargins.ToMargins());
style.ImageSizeMode = GridImageSizeMode.CenterImage;
GridImageUtil.DrawImage(image, bounds, graphics, bounds, style, false);
e.Inner.Cancel = true; // signals you did your own drawing.
}
}
}
}
}
/// <summary>
/// Setup the database DataSet, populate the grid with database data, and format it.
/// </summary>
private void SetupDatabaseGrid()
{
#region Data Source Setup
// Used to retrieve data from the database and to populate the grid.
SQLiteDataAdapter employeeTableAdapter = new SQLiteDataAdapter("SELECT * FROM Employees", Queries.connection);
SQLiteDataAdapter employeeScheduleTableAdapter = new SQLiteDataAdapter("SELECT * FROM [Employee Schedule]", Queries.connection);
SQLiteDataAdapter customersTableAdapter = new SQLiteDataAdapter("SELECT * FROM Customers", Queries.connection);
SQLiteDataAdapter customerAttendanceTableAdapter = new SQLiteDataAdapter("SELECT * FROM [Customer Attendance]", Queries.connection);
// Setup the DataSet and add tables to it in order to hold corresponding database values.
database.DataSetName = "Database";
database.Tables.Add(new DataTable("Employees"));
database.Tables.Add(new DataTable("Employee Schedule"));
database.Tables.Add(new DataTable("Customers"));
database.Tables.Add(new DataTable("Customer Attendance"));
try {
// Populate the DataSet using the SQL adapters.
employeeTableAdapter.Fill(database, "Employees");
employeeScheduleTableAdapter.Fill(database, "Employee Schedule");
customersTableAdapter.Fill(database, "Customers");
customerAttendanceTableAdapter.Fill(database, "Customer Attendance");
}
catch (Exception) {
// Display error message and force-exit application when message is dismissed.
if (MessageBoxAdv.Show(this, "Unable to open database file.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error) == DialogResult.OK)
{
DB_LOCK.Close();
System.Diagnostics.Process.GetCurrentProcess().Kill();
}
}
// Set the data source of the grid to the DataSet.
databaseGrid.DataSource = database;
#endregion
#region Grid Combo Box Formatting
// Holds the combo box values of the employee schedule table.
StringCollection employeeScheduleValues = new StringCollection()
{
"AM/PM", "AM", "PM", "OFF"
};
// Holds the combo box values of the customer attendance table.
StringCollection customerAttendanceValues = new StringCollection()
{
"AM", "PM", "AM/PM", "N/A"
};
// Holds the combo box values of the customer membership entry in the Customer table.
StringCollection customerMembershipValues = new StringCollection()
{
"N/A", "SMILE PLUS"
};
// Format the employee schedule table and add combo boxes.
for (int i = 2; i < databaseGrid.GetTable("Employee Schedule").TableDescriptor.Columns.Count; i++)
{
GridTableCellStyleInfo styleEmployeeSchedule = databaseGrid.GetTable("Employee Schedule").TableDescriptor.Columns[i].Appearance.AnyRecordFieldCell;
styleEmployeeSchedule.DropDownStyle = GridDropDownStyle.Exclusive;
databaseGrid.GetTable("Employee Schedule").TableDescriptor.Columns[i].Appearance.AnyRecordFieldCell.CellType = GridCellTypeName.ComboBox;
databaseGrid.GetTable("Employee Schedule").TableDescriptor.Columns[i].Appearance.AnyRecordFieldCell.ChoiceList = employeeScheduleValues;
databaseGrid.GetTable("Employee Schedule").TableDescriptor.Columns[i].Appearance.AnyRecordFieldCell.CellValue = "CHOICES";
}
// Format the customer attendance table and add combo boxes.
for (int i = 2; i < databaseGrid.GetTable("Customer Attendance").TableDescriptor.Columns.Count; i++)
{
GridTableCellStyleInfo customerAttendance = databaseGrid.GetTable("Customer Attendance").TableDescriptor.Columns[i].Appearance.AnyRecordFieldCell;
customerAttendance.DropDownStyle = GridDropDownStyle.Exclusive;
databaseGrid.GetTable("Customer Attendance").TableDescriptor.Columns[i].Appearance.AnyRecordFieldCell.CellType = GridCellTypeName.ComboBox;
databaseGrid.GetTable("Customer Attendance").TableDescriptor.Columns[i].Appearance.AnyRecordFieldCell.ChoiceList = customerAttendanceValues;
databaseGrid.GetTable("Customer Attendance").TableDescriptor.Columns[i].Appearance.AnyRecordFieldCell.CellValue = "CHOICES";
}
// Format the Customer table and add membership combo boxes.
GridTableCellStyleInfo customers = databaseGrid.GetTable("Customers").TableDescriptor.Columns["Membership"].Appearance.AnyRecordFieldCell;
customers.DropDownStyle = GridDropDownStyle.Exclusive;
databaseGrid.GetTable("Customers").TableDescriptor.Columns["Membership"].Appearance.AnyRecordFieldCell.CellType = GridCellTypeName.ComboBox;
databaseGrid.GetTable("Customers").TableDescriptor.Columns["Membership"].Appearance.AnyRecordFieldCell.ChoiceList = customerMembershipValues;
databaseGrid.GetTable("Customers").TableDescriptor.Columns["Membership"].Appearance.AnyRecordFieldCell.CellValue = "CHOICES";
#endregion
#region Alignment
// Set alignment of id fields.
databaseGrid.GetTable("Employees").TableDescriptor.Columns["Id"].Appearance.AnyRecordFieldCell.HorizontalAlignment = GridHorizontalAlignment.Center;
databaseGrid.GetTable("Employee Schedule").TableDescriptor.Columns["Id"].Appearance.AnyRecordFieldCell.HorizontalAlignment = GridHorizontalAlignment.Center;
databaseGrid.GetTable("Customers").TableDescriptor.Columns["Id"].Appearance.AnyRecordFieldCell.HorizontalAlignment = GridHorizontalAlignment.Center;
databaseGrid.GetTable("Customer Attendance").TableDescriptor.Columns["Id"].Appearance.AnyRecordFieldCell.HorizontalAlignment = GridHorizontalAlignment.Center;
// Set alignment of phone fields.
databaseGrid.GetTable("Employees").TableDescriptor.Columns["Phone"].Appearance.AnyRecordFieldCell.HorizontalAlignment = GridHorizontalAlignment.Left;
databaseGrid.GetTable("Customers").TableDescriptor.Columns["Phone"].Appearance.AnyRecordFieldCell.HorizontalAlignment = GridHorizontalAlignment.Left;
#endregion
#region Readonly & Input Limitation Settings
// Make the 'Id' and 'Name' cells readonly so as not to allow editing outside of appropriate tables.
// This way, employee and customer names can only be edited from the 'Employees' and 'Customers' table respectively,
// while ids cannot be edited at all.
databaseGrid.GetTable("Employee Schedule").TableDescriptor.Columns["Id"].Appearance.AnyRecordFieldCell.ReadOnly = true;
databaseGrid.GetTable("Employee Schedule").TableDescriptor.Columns["Name"].Appearance.AnyRecordFieldCell.ReadOnly = true;
databaseGrid.GetTable("Employees").TableDescriptor.Columns["Id"].Appearance.AnyRecordFieldCell.ReadOnly = true;
databaseGrid.GetTable("Customer Attendance").TableDescriptor.Columns["Id"].Appearance.AnyRecordFieldCell.ReadOnly = true;
databaseGrid.GetTable("Customer Attendance").TableDescriptor.Columns["Name"].Appearance.AnyRecordFieldCell.ReadOnly = true;
databaseGrid.GetTable("Customers").TableDescriptor.Columns["Id"].Appearance.AnyRecordFieldCell.ReadOnly = true;
// Limit the input length of 'Phone' column cells.
databaseGrid.GetTable("Employees").TableDescriptor.Columns["Phone"].Appearance.AnyRecordFieldCell.MaxLength = 12;
databaseGrid.GetTable("Customers").TableDescriptor.Columns["Phone"].Appearance.AnyRecordFieldCell.MaxLength = 12;
#endregion
#region Phone Mask
// Set employee phone field mask.
databaseGrid.GetTable("Employees").TableDescriptor.Columns["Phone"].Appearance.AnyRecordFieldCell.CellType = GridCellTypeName.MaskEdit;
databaseGrid.GetTable("Employees").TableDescriptor.Columns["Phone"].Appearance.AnyRecordFieldCell.MaskEdit.Mask = "(999)-999-9999";
databaseGrid.GetTable("Employees").TableDescriptor.Columns["Phone"].Appearance.AnyRecordFieldCell.MaskEdit.ClipMode = Syncfusion.Windows.Forms.Tools.ClipModes.ExcludeLiterals;
// Set customer phone field mask.
databaseGrid.GetTable("Customers").TableDescriptor.Columns["Phone"].Appearance.AnyRecordFieldCell.CellType = GridCellTypeName.MaskEdit;
databaseGrid.GetTable("Customers").TableDescriptor.Columns["Phone"].Appearance.AnyRecordFieldCell.MaskEdit.Mask = "(999)-999-9999";
databaseGrid.GetTable("Customers").TableDescriptor.Columns["Phone"].Appearance.AnyRecordFieldCell.MaskEdit.ClipMode = Syncfusion.Windows.Forms.Tools.ClipModes.ExcludeLiterals;
#endregion
#region Masking Value Type Overriding
// Override employee phone field value type to allow masking.
databaseGrid.GetTable("Employees").TableDescriptor.Columns["Phone"].Appearance.AnyRecordFieldCell.CellValueType = typeof(String);
// Override customer phone field value type to allow masking.
databaseGrid.GetTable("Customers").TableDescriptor.Columns["Phone"].Appearance.AnyRecordFieldCell.CellValueType = typeof(String);
#endregion
// Format the column header text to be bold.
databaseGrid.Appearance.ColumnHeaderCell.Font.Bold = true;
ResizeGridColumns();
}
/// <summary>
/// Setup the database DataSet, populate the grid with database data, and format it.
/// </summary>
private void SetupDatabaseGrid()
{
// Used to retrieve data from the database and to populate the grid.
SQLiteDataAdapter employeeTableAdapter = new SQLiteDataAdapter("SELECT * FROM Employees", Queries.connection);
SQLiteDataAdapter employeeScheduleTableAdapter = new SQLiteDataAdapter("SELECT * FROM [Employee Schedule]", Queries.connection);
SQLiteDataAdapter customersTableAdapter = new SQLiteDataAdapter("SELECT * FROM Customers", Queries.connection);
SQLiteDataAdapter customerAttendanceTableAdapter = new SQLiteDataAdapter("SELECT * FROM [Customer Attendance]", Queries.connection);
// Setup the DataSet oject and add tables to it in order to hold corresponding database values.
database.DataSetName = "Database";
database.Tables.Add(new DataTable("Employees"));
database.Tables.Add(new DataTable("Employee Schedule"));
database.Tables.Add(new DataTable("Customers"));
database.Tables.Add(new DataTable("Customer Attendance"));
// Fill data tables and populate DataSet using the SQL adapters.
employeeTableAdapter.Fill(database, "Employees");
employeeScheduleTableAdapter.Fill(database, "Employee Schedule");
customersTableAdapter.Fill(database, "Customers");
customerAttendanceTableAdapter.Fill(database, "Customer Attendance");
// Set the data source of the grid to the DataSource.
// Makes the grid show the values.
databaseGrid.DataSource = database;
// Holds the combo box values of the employee schedule table.
StringCollection employeeScheduleValues = new StringCollection();
employeeScheduleValues.Add("AM/PM");
employeeScheduleValues.Add("AM");
employeeScheduleValues.Add("PM");
employeeScheduleValues.Add("OFF");
// Format the employee schedule table and add combo boxes.
for (int i = 2; i < databaseGrid.GetTable("Employee Schedule").TableDescriptor.Columns.Count; i++)
{
GridTableCellStyleInfo styleEmployeeSchedule = databaseGrid.GetTable("Employee Schedule").TableDescriptor.Columns[i].Appearance.AnyRecordFieldCell;
styleEmployeeSchedule.DropDownStyle = GridDropDownStyle.Exclusive;
databaseGrid.GetTable("Employee Schedule").TableDescriptor.Columns[i].Appearance.AnyRecordFieldCell.CellType = GridCellTypeName.ComboBox;
databaseGrid.GetTable("Employee Schedule").TableDescriptor.Columns[i].Appearance.AnyRecordFieldCell.ChoiceList = employeeScheduleValues;
databaseGrid.GetTable("Employee Schedule").TableDescriptor.Columns[i].Appearance.AnyRecordFieldCell.CellValue = "CHOICES";
}
// Holds the combo box values of the customer attendance table.
StringCollection customerAttendanceValues = new StringCollection();
customerAttendanceValues.Add("AM");
customerAttendanceValues.Add("PM");
customerAttendanceValues.Add("AM/PM");
customerAttendanceValues.Add("N/A");
// Format the customer attendance table and add combo boxes.
for (int i = 2; i < databaseGrid.GetTable("Customer Attendance").TableDescriptor.Columns.Count; i++)
{
GridTableCellStyleInfo customerAttendance = databaseGrid.GetTable("Customer Attendance").TableDescriptor.Columns[i].Appearance.AnyRecordFieldCell;
customerAttendance.DropDownStyle = GridDropDownStyle.Exclusive;
databaseGrid.GetTable("Customer Attendance").TableDescriptor.Columns[i].Appearance.AnyRecordFieldCell.CellType = GridCellTypeName.ComboBox;
databaseGrid.GetTable("Customer Attendance").TableDescriptor.Columns[i].Appearance.AnyRecordFieldCell.ChoiceList = customerAttendanceValues;
databaseGrid.GetTable("Customer Attendance").TableDescriptor.Columns[i].Appearance.AnyRecordFieldCell.CellValue = "CHOICES";
}
// Holds the combo box values of the customer membership cell in the Customer table.
StringCollection customerMembershipValues = new StringCollection();
customerMembershipValues.Add("N/A");
customerMembershipValues.Add("SMILE PLUS");
// Format the Customer table and add membership combo boxes.
GridTableCellStyleInfo customers = databaseGrid.GetTable("Customers").TableDescriptor.Columns[2].Appearance.AnyRecordFieldCell;
customers.DropDownStyle = GridDropDownStyle.Exclusive;
databaseGrid.GetTable("Customers").TableDescriptor.Columns[2].Appearance.AnyRecordFieldCell.CellType = GridCellTypeName.ComboBox;
databaseGrid.GetTable("Customers").TableDescriptor.Columns[2].Appearance.AnyRecordFieldCell.ChoiceList = customerMembershipValues;
databaseGrid.GetTable("Customers").TableDescriptor.Columns[2].Appearance.AnyRecordFieldCell.CellValue = "CHOICES";
// Make the Id and Name cells readonly so as not to allow editing outside of appropriate tables.
// This way, employee names can only be edited from the Employee table, while Ids cannot be changed.
databaseGrid.GetTable("Employee Schedule").TableDescriptor.Columns[0].Appearance.AnyRecordFieldCell.ReadOnly = true;
databaseGrid.GetTable("Employee Schedule").TableDescriptor.Columns[1].Appearance.AnyRecordFieldCell.ReadOnly = true;
databaseGrid.GetTable("Employees").TableDescriptor.Columns[0].Appearance.AnyRecordFieldCell.ReadOnly = true;
// Make the Id and Name cells readonly so as not to allow editing outside of appropriate tables.
// This way, customer names can only be edited from the Employee table, while Ids cannot be changed.
databaseGrid.GetTable("Customer Attendance").TableDescriptor.Columns[0].Appearance.AnyRecordFieldCell.ReadOnly = true;
databaseGrid.GetTable("Customer Attendance").TableDescriptor.Columns[1].Appearance.AnyRecordFieldCell.ReadOnly = true;
databaseGrid.GetTable("Customers").TableDescriptor.Columns[0].Appearance.AnyRecordFieldCell.ReadOnly = true;
// Limit input length of Phone column cells
databaseGrid.GetTable("Employees").TableDescriptor.Columns["Phone"].Appearance.AnyRecordFieldCell.MaxLength = 12;
databaseGrid.GetTable("Customers").TableDescriptor.Columns["Phone"].Appearance.AnyRecordFieldCell.MaxLength = 12;
// Set message box style
MessageBoxAdv.MessageBoxStyle = MessageBoxAdv.Style.Metro;
}
