/// <summary>
/// Opens an existing mdf file, called by the form constructor with 1 overload
/// </summary>
/// <param name="grid">The datagrid to bind the DB to</param>
public void initalizeDataGrid(DataGridView grid)
{
mainForm = ((Main)MdiParent);
// DB Connection Setup
connString = @"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=" + filepath + "; Integrated Security=True;Connect Timeout=30";
Db = new DatabaseOperations(connString);
Db.OpenDatabase(filepath);
page = new DBPagination(db, dtgrd_manufacturer, "Equipment", itemPerPageUpDown, pageSelector); // relinquish the DB to the page class
page.currPage = 0; // make sure the form shows the first page
Text = Path.GetFileNameWithoutExtension(filepath);
try
{
page.loadPage(); // database binding
}
catch (Exception e)
{
MessageBox.Show(e.Message);
}
grid.AllowUserToAddRows = true;
grid.AllowUserToDeleteRows = true;
grid.AllowUserToResizeColumns = true;
grid.ReadOnly = false;
grid.Columns[0].ReadOnly = true;
grid.Columns[0].DefaultCellStyle.SelectionBackColor = Color.LightGray;
grid.Columns[0].DefaultCellStyle.SelectionForeColor = Color.DarkGray;
grid.Columns[2].DefaultCellStyle.SelectionBackColor = Color.LightGray;
grid.Columns[2].DefaultCellStyle.SelectionForeColor = Color.DarkGray;
Db.Dispose(true);
}
/// <summary>
/// Opens an existing mdf file, called by the form constructor with 1 overload
/// </summary>
/// <param name="grid">The datagrid to bind the DB to</param>
public void initalizeDataGrid(DataGridView grid)
{
isSaved = true;
mainForm = ((Main)MdiParent);
// DB Connection Setup
connString = @"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=" + filepath + "; Integrated Security=True;Connect Timeout=30";
Db = new DatabaseOperations(connString);
Db.OpenDatabase(filepath);
equipmentPage = new DBPagination(db, dtgrd_equipment, "Equipment", itemPerPageUpDown, pageSelector); // relinquish the DB to the page class
equipmentPage.currPage = 0; // make sure the form shows the first page
manufacturerPage = new DBPagination(db, dtgrd_manufacturer, "Manufacturer", itemPerPageUpDown, pageSelector);
manufacturerPage.currPage = 0;
// refresh the list
pagedTabs = new List <DBPagination>();
pagedTabs.Add(equipmentPage);
pagedTabs.Add(manufacturerPage);
Text = Path.GetFileNameWithoutExtension(filepath);
DataGridViewComboBoxColumn conditionCol = (DataGridViewComboBoxColumn)grid.Columns[2];
conditionCol.DataSource = condList.conditionList.OrderBy(p => p.Priority).ToList();
conditionCol.DefaultCellStyle.NullValue = condList.conditionList[0].Value;
conditionCol.DisplayMember = "Value";
conditionCol.ValueMember = "Value";
conditionCol.SortMode = DataGridViewColumnSortMode.Automatic;
refreshManufCol();
try
{
manufacturerPage.loadPage(); //database binding for manufacturers
equipmentPage.loadPage(); // database binding
}
catch (Exception e)
{
MessageBox.Show(e.Message);
}
grid.AllowUserToAddRows = true;
grid.AllowUserToDeleteRows = true;
grid.AllowUserToResizeColumns = true;
grid.ReadOnly = false;
grid.Columns[0].ReadOnly = true;
grid.Columns[0].DefaultCellStyle.SelectionBackColor = Color.LightGray;
grid.Columns[0].DefaultCellStyle.SelectionForeColor = Color.DarkGray;
grid.Columns[2].DefaultCellStyle.SelectionBackColor = Color.LightGray;
grid.Columns[2].DefaultCellStyle.SelectionForeColor = Color.DarkGray;
colorRowsByCondition();
Db.Dispose(true);
}
/// <summary>
/// Creates a new DB and loads it up to the view
/// </summary>
/// <param name="grid"></param>
public void initializeDefGrid(DataGridView grid)
{
mainForm = ((Main)MdiParent);
//dir = Environment.GetFolderPath(Environment.SpecialFolder.Desktop) + @"\managementapp\";
//filepath = dir + string.Format("temp_{0}.mdf", mainForm.fileCounter);
//Text = string.Format("New Database {0}", mainForm.fileCounter);
//// Create temporary directory and make it hidden
//DirectoryInfo dirInf = Directory.CreateDirectory(dir);
//dirInf.Attributes = FileAttributes.Directory | FileAttributes.Hidden;
// DB Connection Setup
connString = @"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=" + filepath + "; Integrated Security=True;Connect Timeout=30";
Db = new DatabaseOperations(connString);
Db.CreateDatabase(filepath);
page = new DBPagination(db, dtgrd_manufacturer, "Equipment", itemPerPageUpDown, pageSelector); // relinquish the DB to the page class
page.currPage = 0; // make sure the form shows the first page
//Identity allows the 'ID' Attribute to be auto incremented. Its value does not have to specified when inserting to the table.
Db.CreateTable("Manufacturer", "ID", "int IDENTITY(1,1) not null PRIMARY KEY", "Name", "VARCHAR(255)", "Email", "VARCHAR(255)", "Number", "VARCHAR(255)", "Country", "VARCHAR(255)", "City", "VARCHAR(255)", "Zip", "VARCHAR(255)");
try
{
page.loadPage(); // database binding
}
catch (Exception e)
{
MessageBox.Show(e.Message);
}
grid.AllowUserToAddRows = true;
grid.AllowUserToDeleteRows = true;
grid.AllowUserToResizeColumns = true;
grid.ReadOnly = false;
grid.Columns[0].ReadOnly = true;
grid.Columns[0].DefaultCellStyle.SelectionBackColor = Color.LightGray;
grid.Columns[0].DefaultCellStyle.SelectionForeColor = Color.DarkGray;
grid.Columns[2].DefaultCellStyle.SelectionBackColor = Color.LightGray;
grid.Columns[2].DefaultCellStyle.SelectionForeColor = Color.DarkGray;
Db.Dispose(true);
}
private void btn_Delete_Click(object sender, EventArgs e)
{
DialogResult dialogResult = MessageBox.Show(string.Format("Are you sure you want to remove the item \"{0}\" from the table? This action cannot be undone.", dtgrd_manufacturer.Rows[dtgrd_manufacturer.CurrentCell.RowIndex].Cells[1].Value.ToString()), "Confirm Deletion", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (dialogResult == DialogResult.Yes)
{
try
{
page.Ds.Tables["Manufacturer"].Rows[dtgrd_manufacturer.CurrentCell.RowIndex].Delete();
scaleDatagrid(dtgrd_manufacturer);
page.Db.UpdateEquipDataSet(page.Ds); // perform necessarry operations to the DB based on the changes in the DS
page.Ds.Dispose();
page.Db.Dispose(true);
page.loadPage();
lbl_Pages.Text = page.pageCount.ToString() + " Page(s) in total";
lbl_RecordCount.Text = Page.totalRecords.ToString() + " Records present";
}
catch (Exception err)
{
MessageBox.Show(err.Message);
}
}
}
private void btn_Delete_Click(object sender, EventArgs e)
{
// TODO: Add multiple row deletion dialog box
//DialogResult dialogResult = DialogResult.No;
DialogResult dialogResult;
if (!isSaved)
{
MessageBox.Show("Please save any unsaved changes before proceeding with deletion.", "Unsaved Changes", MessageBoxButtons.OK, MessageBoxIcon.Error);
saveBtn_Click(sender, e);
return;
}
if (tab_Tables.SelectedTab.Name == "tabEquipment")
{
try
{
dialogResult = MessageBox.Show("Are you sure you want to remove the selected item(s) from the table? This action cannot be undone.", "Confirm Deletion", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (dialogResult == DialogResult.Yes)
{
foreach (DataGridViewRow row in dtgrd_equipment.SelectedRows)
{
equipmentPage.Ds.Tables["Equipment"].Rows[row.Index].Delete();
}
scaleDatagrid(dtgrd_equipment);
equipmentPage.Db.UpdateEquipDataSet(equipmentPage.Ds); // perform necessarry operations to the DB based on the changes in the DS
equipmentPage.Ds.Dispose();
equipmentPage.Db.Dispose(true);
equipmentPage.loadPage();
colorRowsByCondition();
//refreshManufCol();
lbl_Pages.Text = equipmentPage.pageCount.ToString() + " Page(s) in total";
lbl_RecordCount.Text = equipmentPage.totalRecords.ToString() + " Records present";
}
}
catch (Exception err)
{
MessageBox.Show(err.Message);
}
colorRowsByCondition();
}
else if (tab_Tables.SelectedTab.Name == "tabManufacturer")
{
List <String> presentValues = new List <string>();
bool exist = false;
foreach (DataGridViewRow row in dtgrd_manufacturer.SelectedRows)
{
if (db.checkIfManufacturerIsInUse(row.Cells[1].Value.ToString()))
{
presentValues.Add(row.Cells[1].Value.ToString());
}
}
if (presentValues.Count > 0)
{
exist = true;
}
if (exist)
{
var message = string.Join(Environment.NewLine, presentValues);
MessageBox.Show(string.Format("The following selected Manufacturer(s) is/are currently assigned to an Equipment and cannot be deleted. " + Environment.NewLine + "{0}" + Environment.NewLine + "Please un-select them then try again.", message), "Cannot Delete", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
}
else
{
try
{
dialogResult = MessageBox.Show("Are you sure you want to remove the selected item(s) from the table? This action cannot be undone.", "Confirm Deletion", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (dialogResult == DialogResult.Yes)
{
foreach (DataGridViewRow row in dtgrd_manufacturer.SelectedRows)
{
manufacturerPage.Ds.Tables["Manufacturer"].Rows[row.Index].Delete();
}
scaleDatagrid(dtgrd_manufacturer);
manufacturerPage.Db.updateManufacturerDataSet(manufacturerPage.Ds); // perform necessarry operations to the DB based on the changes in the DS
manufacturerPage.Ds.Dispose();
manufacturerPage.Db.Dispose(true);
manufacturerPage.loadPage();
lbl_Pages.Text = manufacturerPage.pageCount.ToString() + " Page(s) in total";
lbl_RecordCount.Text = manufacturerPage.totalRecords.ToString() + " Records present";
}
}
catch (Exception err)
{
MessageBox.Show(err.Message);
}
}
}
}
/// <summary>
/// Creates a new DB and loads it up to the view
/// </summary>
/// <param name="grid"></param>
public void initializeDefGrid(DataGridView grid)
{
isSaved = false;
mainForm = ((Main)MdiParent);
dir = Environment.GetFolderPath(Environment.SpecialFolder.Desktop) + @"\managementapp\";
filepath = dir + string.Format("temp_{0}.mdf", mainForm.fileCounter);
Text = string.Format("New Database {0}", mainForm.fileCounter);
// Create temporary directory and make it hidden
DirectoryInfo dirInf = Directory.CreateDirectory(dir);
dirInf.Attributes = FileAttributes.Directory | FileAttributes.Hidden;
// DB Connection Setup
connString = @"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=" + filepath + "; Integrated Security=True;Connect Timeout=30";
Db = new DatabaseOperations(connString);
Db.CreateDatabase(filepath);
equipmentPage = new DBPagination(db, dtgrd_equipment, "Equipment", itemPerPageUpDown, pageSelector); // relinquish the DB to the page class
equipmentPage.currPage = 0; // make sure the form shows the first page
manufacturerPage = new DBPagination(db, dtgrd_manufacturer, "Manufacturer", itemPerPageUpDown, pageSelector);
manufacturerPage.currPage = 0;
//Identity allows the 'ID' Attribute to be auto incremented. Its value does not have to specified when inserting to the table.
Db.CreateTable("Equipment", "ID", "int IDENTITY(1,1) not null PRIMARY KEY", "Name", "varchar(255)", "Condition", "varchar(255)", "Quantity", "int", "Price", "decimal(19,2)", "Department", "varchar(255)", "Manufacturer", "varchar(255)", "[Date_of_Purchase]", "date");
Db.CreateTable("Manufacturer", "ID", "int IDENTITY(1,1) not null PRIMARY KEY", "Name", "varchar(255)", "[Email_Address]", "varchar(255)", "[Contact_Number]", "varchar(255)", "[Country_of_Origin]", "varchar(255)", "City", "varchar(255)", "[Zip_Code]", "int");
Db.CreateTable("Department", "ID", "int IDENTITY(1,1) not null PRIMARY KEY", "Name", "varchar(255)");
//DataGridViewComboBoxColumn conditionCol = (DataGridViewComboBoxColumn)grid.Columns[2];
DataGridViewComboBoxColumn conditionCol = dtgrd_equipment.Columns[2] as DataGridViewComboBoxColumn;
conditionCol.DataSource = condList.conditionList.OrderBy(p => p.Priority).ToList();
conditionCol.DefaultCellStyle.NullValue = condList.conditionList[0].Value;
conditionCol.DisplayMember = "Value";
conditionCol.ValueMember = "Value";
conditionCol.SortMode = DataGridViewColumnSortMode.Automatic;
try
{
manufacturerPage.loadPage(); //database binding (manufacturer)
equipmentPage.loadPage(); // database binding
}
catch (Exception e)
{
MessageBox.Show(e.Message);
}
grid.AllowUserToAddRows = true;
grid.AllowUserToDeleteRows = true;
grid.AllowUserToResizeColumns = true;
grid.ReadOnly = false;
grid.Columns[0].ReadOnly = true;
grid.Columns[0].DefaultCellStyle.SelectionBackColor = Color.LightGray;
grid.Columns[0].DefaultCellStyle.SelectionForeColor = Color.DarkGray;
grid.Columns[2].DefaultCellStyle.SelectionBackColor = Color.LightGray;
grid.Columns[2].DefaultCellStyle.SelectionForeColor = Color.DarkGray;
Db.Dispose(true);
}