chuanshuoge: ADO.NET final

Final Project – Asset Management Application

Project Introduction

The project for this class requires an application to be built for an engineering company that CPRG254 Ltd has as a client. The company has requested an mdi multi-form application to keep track of assets assigned to each employee. The company needs to be able to manage assets such as desktop and laptop computers, IP desk phones and cell phones assigned to employees.

A Business Analyst has gathered application requirements through meetings with the company. They are presented in the Business Requirements section.

The Solution Architect has provided her requirements in the Technical Requirements section.

Project Due Date:

11:59 pm on Monday, April 25, 2016

Business Requirements

1. The main entities that must be part of the system include Employee, Department, Vendor, Asset, and Category of asset.

a. Employee data to track include first name(String), last name(String), the department they belong to(Department), date of hire(DateTime), phone number(String), and desk number(String)

b. Department only needs name(String)

c. Vendor needs name(String) and phone number(String)

d. Category requires name(String)

e. Asset data to capture include name(String), description(String), serial number(String), vendor(Vendor), employee assigned to(Employee), and date assigned(DateTime)

2. The company needs a form to enter departments into the system and edit the department name if required.

3. The company would like a form to add employees to the system and edit information if required. They should be able to select a department from a combobox on this form.

4. The company needs a form to input asset categories (such as Desktop PC, Laptop, IP Phone, and Cell Phone) and modify if required.

5. A form is required to enter Vendor information and modify if required.

6. A form is necessary to enter or update asset data. The Vendor supplying the asset is selected from a populated combobox. The employee assigned to and date assigned are not entered in this form.

7. An asset assignment form is needed to select the asset or assets for a selected employee. Once an employee is selected please display assets already assigned to this employee and their category (only one asset from each category is allowed to be assigned to an employee). Once assigned, the asset no longer displays an available asset. Group the display of unassigned assets by their category in separate controls (one for Desktop PC’s, one for Laptops, etc). The same form can be used to return assets. This returns the asset to the available pool.

8. Lookup forms are required for each of the main entities. Use context menus to call the form to add or edit the entity.

9. The asset lookup form should display all properties (including just the vendor name and employee full name) but not the Id. By default, the form displays all assets but it should be able to be filtered using radio buttons to display available assets, assets that are assigned, assets assigned to a selected employee, and assets by selected category.

Technical Requirements

1. The application will be developed with a multi-layered approach.

2. Four projects will be developed as follows:

a. CPRG254.Assets.UI. This is the windows forms project containing all the forms and is the startup project. It has dependencies on the other three projects. Add references to the Entity Framework installed in the data project

b. CPRG254.Assets.Domain. This is a class library containing just the main domain entities but not the managers. It has no project dependencies.

c. CPRG254.Assets.Repositories. This is as class library containing the domain entity manager classes. It has dependencies on the domain project and the data project. Add references to the Entity Framework installed in the data project.

d. CPRG254.Assets.Data. This is a class library containing just the custom DbContext class. It has dependency on the domain project. The latest Entity Framework needs to be installed through the NuGet Package Manager.

3. Use the Entity Framework to handle the database functionality in this project. Add the connection string to the data project app.config file and copy all the elements from the this app.config file to the app.config file in the UI project.

4. No database script will be provided as Entity Framework will create the database in SQL Server for you.

5. Follow a Domain Driven Design by coding the domain entity classes first, then code the manager classes. The forms can call the methods of the manager classes to get test data returned until the custom DbContext class is coded in the data layer and test data can be added to the database using the Entity Framework. Then refactor the manager methods to use the DBContext class.

6. Each domain entity requires an Id property (int) which will represent the primary key identity field in the database.

7. No dynamic sql statements or stored procedures need to be used in this application.

8. All CRUD activity including the retrieval of data is managed through LINQ to Entities. Let the Entity Framework work for you by concentrating on the domain objects and their managers.

Grading Criteria

Total marks for the assignment is 100 with a weight of 60% of the final grade and are awarded based on the following:

Requirement	Mark	Criteria
4 Projects developed	20	5 marks per project
5 Domain entities coded	10	2 marks per domain entity
5 Domain managers coded	15	3 marks per domain manager
Custom DbContext coded	5	All collections are present
5 Lookup forms developed	15	3 marks per lookup
5 Maintenance forms done	15	3 marks per maintenance form
Asset assignment form done	5	5 for full functionality, 3 for some, 1 minimum effort
Test data available (Bonus)	5	Bonus marks available
Application works as required	15	15 for full functionality, 1 mark deducted for each function not working as required

//main form

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

using System.Windows.Forms;

namespace CPRG254.Assets.UI

{

public partial class Main_form : Form

{

public Main_form()

{

InitializeComponent();

}

private class open_form<T> where T : Form, new()

{

private static T _form = null;

public open_form(Form main_form)

{

if (_form == null || _form.IsDisposed)

{

_form = new T();

_form.MdiParent = main_form;

_form.Show();

_form.Location = new Point(0, 0);

}

else

{

_form.BringToFront();

}

private void departmentToolStripMenuItem_Click(object sender, EventArgs e)

{

open_form<Department_form> new_form = new open_form<Department_form>(this);

}

private void employeeToolStripMenuItem_Click(object sender, EventArgs e)

{

open_form<Employee_form> new_form = new open_form<Employee_form>(this);

}

private void categoriesToolStripMenuItem_Click(object sender, EventArgs e)

{

open_form<Categories_form> new_form = new open_form<Categories_form>(this);

}

private void vendorToolStripMenuItem_Click(object sender, EventArgs e)

{

open_form<vendor_form> new_form = new open_form<vendor_form>(this);

}

private void assetToolStripMenuItem_Click(object sender, EventArgs e)

{

open_form<Asset_form> new_form = new open_form<Asset_form>(this);

}

private void assetAsignmentToolStripMenuItem_Click(object sender, EventArgs e)

{

open_form<asset_assignment_form> new_form = new open_form<asset_assignment_form>(this);

}

-----------------------------------------------------------
//asset_assignment_form

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using CPRG254.Assets.Domain;
using CPRG254.Assets.Respositories;

namespace CPRG254.Assets.UI
{
public partial class asset_assignment_form : Form
{
public asset_assignment_form()
{
InitializeComponent();

groupBox_employee.Dock = DockStyle.Fill;
groupBox_asset_category.Dock = DockStyle.Fill;
groupBox_asset_assignment.Dock = DockStyle.Fill;

var employees = Employee_manager.get_all();

var employees_combobox = employees.
Select(x => new
{
id = x.id,
name = x.id+" "+x.first_name+" "+x.last_name
}).
Where(x=> x.id != Employee_manager.find_ID("default","default"))
.ToList();

ux_employee.DataSource = employees_combobox;
ux_employee.DisplayMember = "name";
ux_employee.ValueMember = "id";

var categories = Category_manager.get_all().
Select(x => new
{
id = x.id,
name = x.id + " " + x.name
}).ToList();

ux_category.DataSource = categories;
ux_category.DisplayMember = "name";
ux_category.ValueMember = "id";

asset_assignment_manager.refresh_asset_assignment();

refresh_asset_asignment_datagridview();

}

//assign asset
private void button1_Click(object sender, EventArgs e)
{
foreach (DataGridViewRow single_row in ux_availabe_asset.SelectedRows)
{
int employee_id = Convert.ToInt32(ux_employee.Text.Split(' ')[0]);
int category_id = Convert.ToInt32(single_row.Cells[5].Value);
var employee_name = ux_employee.Text.Split(' ')[1] + ux_employee.Text.Split(' ')[2];
var category_name = (single_row.Cells[4].Value == null) ? "" : single_row.Cells[4].Value.ToString();

if (asset_assignment_manager.check_if_employee_has_asset_in_this_category(category_id, employee_id))
{
MessageBox.Show(employee_name + " has asset in " + category_name);
continue;
}

var assigned_asset = new asset_package
{
id = Convert.ToInt32(single_row.Cells[0].Value),
name = (single_row.Cells[1].Value == null) ? "" : single_row.Cells[1].Value.ToString(),
description = (single_row.Cells[2].Value == null) ? "" : single_row.Cells[2].Value.ToString(),
serial_number = (single_row.Cells[3].Value == null) ? "" : single_row.Cells[3].Value.ToString(),
_category_name =category_name,
_categoryid = category_id,
_vendor_name = ( single_row.Cells[6].Value == null) ? "": single_row.Cells[6].Value.ToString(),
_vendorid = Convert.ToInt32(single_row.Cells[7].Value),
_employee_name = employee_name,
_employeeid = employee_id,
date = DateTime.Now
};

ux_availabe_asset.Rows.RemoveAt(single_row.Index);

Asset_manager.update_by_asset_package(assigned_asset);
asset_assignment_manager.assign_asset(assigned_asset);
}
refresh_asset_asignment_datagridview();
}

//return asset
private void button2_Click(object sender, EventArgs e)
{
foreach (DataGridViewRow single_row in ux_assigned_asset.SelectedRows)
{
var returned_asset = new asset_package
{
id = Convert.ToInt32(single_row.Cells[0].Value),
name = (single_row.Cells[1].Value == null) ? "" : single_row.Cells[1].Value.ToString(),
description = (single_row.Cells[2].Value == null) ? "" : single_row.Cells[2].Value.ToString(),
serial_number = (single_row.Cells[3].Value == null) ? "" : single_row.Cells[3].Value.ToString(),
_category_name = (single_row.Cells[4].Value == null) ? "" : single_row.Cells[4].Value.ToString(),
_categoryid = Convert.ToInt32(single_row.Cells[5].Value),
_vendor_name = (single_row.Cells[6].Value == null) ? "" : single_row.Cells[6].Value.ToString(),
_vendorid = Convert.ToInt32(single_row.Cells[7].Value),
_employee_name = "default default",
_employeeid = Employee_manager.find_ID("default", "default"),
date = DateTime.Now
};

ux_assigned_asset.Rows.RemoveAt(single_row.Index);

Asset_manager.update_by_asset_package(returned_asset);
asset_assignment_manager.return_asset(returned_asset);
}
refresh_asset_asignment_datagridview();
}

//refresh employee asset
private void button3_Click(object sender, EventArgs e)
{

Employee_asset.DataSource = asset_assignment_manager.find_employee_asset(ux_employee.Text);

Employee_asset.Columns[5].Visible = false;
Employee_asset.Columns[7].Visible = false;
Employee_asset.Columns[9].Visible = false;
}

private void refresh_asset_asignment_datagridview()
{
ux_assigned_asset.DataSource = null;
ux_availabe_asset.DataSource = null;
ux_availabe_asset.Rows.Clear();
ux_assigned_asset.Rows.Clear();

ux_assigned_asset.DataSource = new BindingList<asset_package>( asset_assignment_manager.assigned_asset);
ux_availabe_asset.DataSource = new BindingList<asset_package>( asset_assignment_manager.unassigned_asset);

ux_assigned_asset.Columns[5].Visible = false;
ux_assigned_asset.Columns[7].Visible = false;
ux_assigned_asset.Columns[9].Visible = false;

ux_availabe_asset.Columns[5].Visible = false;
ux_availabe_asset.Columns[7].Visible = false;
ux_availabe_asset.Columns[9].Visible = false;
}

private void ux_employee_SelectedIndexChanged(object sender, EventArgs e)
{
button3.PerformClick();
}

private void ux_availabe_asset_CellClick(object sender, DataGridViewCellEventArgs e)
{
ux_availabe_asset.CurrentRow.Selected = true;
}

private void ux_assigned_asset_CellClick(object sender, DataGridViewCellEventArgs e)
{
ux_assigned_asset.CurrentRow.Selected = true;
}

//display assets by categories
private void button4_Click(object sender, EventArgs e)
{
asset_by_category.DataSource = null;
asset_by_category.Rows.Clear();

asset_by_category.DataSource = Asset_manager.get_all().
Where(x=>x._categoryid == Convert.ToInt32( ux_category.Text.Split(' ')[0])).
Select(x => new
{
id = x.id,
name = x.name,
description = x.description,
category_name = x._category_name,
vendor_name = x._vendor_name,
employee_name = x._employee_name,
date = x.date
}).ToList();
}

private void radioButton1_Click(object sender, EventArgs e)
{
groupBox_asset_assignment.BringToFront();
}

private void radioButton2_Click(object sender, EventArgs e)
{
groupBox_employee.BringToFront();
button3.PerformClick();
}

private void radioButton3_Click(object sender, EventArgs e)
{
groupBox_asset_category.BringToFront();
button4.PerformClick();
}

private void ux_category_SelectedIndexChanged(object sender, EventArgs e)
{
button4.PerformClick();
}
}
}

----------------------------------------------------------------------

//asset_assignment_manager

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

using CPRG254.Assets.Domain;

using CPRG254.Assets.Data;

namespace CPRG254.Assets.Respositories

{

public class asset_assignment_manager

{

public static IList<asset_package> assigned_asset = new List<asset_package>(), unassigned_asset = new List<asset_package>();

public static void assign_asset(asset_package _asset)

{

assigned_asset.Add(_asset);

unassigned_asset.Remove(_asset);

}

public static void return_asset(asset_package _asset)

{

assigned_asset.Remove(_asset);

unassigned_asset.Add(_asset);

}

public static void refresh_asset_assignment()

{

var all_assets = Asset_manager.get_all();

var default_employee_id = Employee_manager.find_ID("default", "default");

assigned_asset.Clear();

unassigned_asset.Clear();

foreach(var single_asset in all_assets)

{

if(single_asset._employeeid == default_employee_id)

{

unassigned_asset.Add(single_asset);

}

else

{

assigned_asset.Add(single_asset);

}

public static IList<asset_package> find_employee_asset(string combox_value)

{

int employee_id = Convert.ToInt32( combox_value.Split(' ')[0]);

string employee_name = combox_value.Split(' ')[1] + " " + combox_value.Split(' ')[2];

var all_assets = Asset_manager.get_all();

var single_employee_asset = all_assets.

Where(x => x._employeeid == employee_id).

Select(x => new asset_package

{

id = x.id,

_employee_name = employee_name,

_employeeid = employee_id,

name = x.name,

description = x.description,

serial_number = x.serial_number,

_category_name = x._category_name,

_categoryid = x._categoryid,

_vendor_name = x._vendor_name,

_vendorid = x._vendorid,

date = x.date

}).ToList();

return single_employee_asset;

}

public static bool check_if_employee_has_asset_in_this_category(int category_id, int employee_id)

{

var asset_of_a_category_of_an_employee = assigned_asset.

Where(x => x._categoryid == category_id && x._employeeid == employee_id).

Select(x => new

{

id = x.id

})

.ToList();

if(asset_of_a_category_of_an_employee.Count == 0)

{

return false;

}

else

{

return true;

}

------------------------------------------------------------------------------

//asset_form

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using CPRG254.Assets.Respositories;
using CPRG254.Assets.Domain;

namespace CPRG254.Assets.UI
{
public partial class Asset_form : Form
{
public Asset_form()
{
InitializeComponent();

vendor_column = construct_vendor_combobox_column();
category_column = construct_category_combobox_column();
}

public static string asset_name, asset_description, asset_serial_number, asset_category_name, asset_vendor_name;
public static bool cancel_add;

private DataGridViewColumn vendor_column, category_column;

private void displayRefreshToolStripMenuItem_Click(object sender, EventArgs e)
{
refresh();
}

private void addToolStripMenuItem_Click(object sender, EventArgs e)
{
cancel_add = false;
var asset_data_entry = new Asset_sub_form();
asset_data_entry.ShowDialog();

if(cancel_add == true) { MessageBox.Show("canceled"); return; }

var vendor_id = vendor_manager.find_ID(asset_vendor_name);
var category_id = Category_manager.find_ID(asset_category_name);

var asset = new Asset
{
name = asset_name,
description = asset_description,
serial_number=asset_serial_number,
_categoryid = category_id,
_vendorid = vendor_id
};

bool data_added = Asset_manager.add(asset);

if (data_added)
{
MessageBox.Show("new asset added");
}
else
{
MessageBox.Show("asset already exists");
}

refresh();
}

private void updateSingleToolStripMenuItem_Click(object sender, EventArgs e)
{
var _name = ux_asset.CurrentRow.Cells[1].Value.ToString();
var _description = ux_asset.CurrentRow.Cells[2].Value.ToString();
var _serial_number = ux_asset.CurrentRow.Cells[3].Value.ToString();
var _date = Convert.ToDateTime(ux_asset.CurrentRow.Cells[12].Value);

var _id = Convert.ToInt32(ux_asset.CurrentRow.Cells[0].Value);

int _category_id, _vendor_id;

if (ux_asset.CurrentRow.Cells[5].Value == null)
{
_category_id=Convert.ToInt32(ux_asset.CurrentRow.Cells[6].Value);
}
else
{
_category_id = Convert.ToInt32(ux_asset.CurrentRow.Cells[5].Value.ToString().Split(' ')[0]);
}

if (ux_asset.CurrentRow.Cells[8].Value == null)
{
_vendor_id = Convert.ToInt32(ux_asset.CurrentRow.Cells[9].Value);
}
else
{
_vendor_id = Convert.ToInt32(ux_asset.CurrentRow.Cells[8].Value.ToString().Split(' ')[0]);
}

var _employee_id = Convert.ToInt32(ux_asset.CurrentRow.Cells[11].Value);

var _asset = new Asset
{
name = _name,
serial_number = _serial_number,
date = _date,
description = _description,

id = _id,
_categoryid = _category_id,
_vendorid = _vendor_id,
_employeeid=_employee_id
};

Asset_manager.update(_asset);

MessageBox.Show("ID " + _id + " updated");
}

private void updateAllToolStripMenuItem_Click(object sender, EventArgs e)
{
var total_row = ux_asset.RowCount;

for (int i = 0; i < total_row; i++)
{
var _name = ux_asset[1,i].Value.ToString();
var _description = ux_asset[2,i].Value.ToString();
var _serial_number = ux_asset[3,i].Value.ToString();
var _date = Convert.ToDateTime(ux_asset[12,i].Value);

var _id = Convert.ToInt32(ux_asset[0,i].Value);

int _category_id, _vendor_id;

if (ux_asset[5,i].Value == null)
{
_category_id = Convert.ToInt32(ux_asset[6,i].Value);
}
else
{
_category_id = Convert.ToInt32(ux_asset[5,i].Value.ToString().Split(' ')[0]);
}

if (ux_asset[8,i].Value == null)
{
_vendor_id = Convert.ToInt32(ux_asset[9,i].Value);
}
else
{
_vendor_id = Convert.ToInt32(ux_asset[8,i].Value.ToString().Split(' ')[0]);
}

var _employee_id = Convert.ToInt32(ux_asset[11,i].Value);

var _asset = new Asset
{
name = _name,
serial_number = _serial_number,
date = _date,
description = _description,

id = _id,
_categoryid = _category_id,
_vendorid = _vendor_id,
_employeeid = _employee_id
};

Asset_manager.update(_asset);

}

MessageBox.Show("All updated");
}

private void refresh()
{
ux_asset.Columns.Clear();
ux_asset.DataSource = Asset_manager.get_all();

ux_asset.Columns.Insert(5, category_column);
ux_asset.Columns.Insert(8, vendor_column);

ux_asset.Columns["_vendorid"].Visible=false;
ux_asset.Columns["_categoryid"].Visible = false;

var total_row = ux_asset.RowCount;

for (int i = 0; i < total_row; i++)
{
ux_asset[0, i].ReadOnly = true;
ux_asset[0, i].Style.BackColor = Color.LightGray;

ux_asset[4, i].ReadOnly = true;
ux_asset[4, i].Style.BackColor = Color.LightGray;

ux_asset[11, i].ReadOnly = true;
ux_asset[11, i].Style.BackColor = Color.LightGray;

ux_asset[12, i].ReadOnly = true;
ux_asset[12, i].Style.BackColor = Color.LightGray;

ux_asset[7, i].ReadOnly = true;
ux_asset[7, i].Style.BackColor = Color.LightGray;

ux_asset[10, i].ReadOnly = true;
ux_asset[10, i].Style.BackColor = Color.LightGray;

}

}

private DataGridViewColumn construct_vendor_combobox_column ()
{
DataGridViewComboBoxCell cell = new DataGridViewComboBoxCell();

var vendors = vendor_manager.get_all();

foreach(var vendor in vendors)
{
cell.Items.Add(vendor.id + " " + vendor.name);
cell.FlatStyle = FlatStyle.Flat;
}

var column = new DataGridViewColumn(cell);

column.HeaderText = "change vendor";

return column;
}

private DataGridViewColumn construct_category_combobox_column()
{
DataGridViewComboBoxCell cell = new DataGridViewComboBoxCell();

var categories = Category_manager.get_all();

foreach (var category in categories)
{
cell.Items.Add(category.id + " " + category.name);
cell.FlatStyle = FlatStyle.Flat;
}

var column = new DataGridViewColumn(cell);

column.HeaderText = "change category";

return column;
}

}
}

------------------------------------------------------------------------

//asset manager

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

using CPRG254.Assets.Domain;

using CPRG254.Assets.Data;

namespace CPRG254.Assets.Respositories

{

public class Asset_manager

{

public static IList<asset_package> get_all()

{

var db = new DBContext();

var assets = db.Asset.

Select(x => new asset_package

{

id = x.id,

name =x.name,

description =x.description,

serial_number=x.serial_number,

_category_name = x._category.name,

_categoryid = x._categoryid,

_vendor_name = x._vendor.name,

_vendorid=x._vendorid,

_employee_name=x._employee.first_name+" "+x._employee.last_name,

_employeeid = x._employeeid,

date = x.date

}).ToList();

return assets;

}

public static bool add(Asset _asset)

{

var db = new DBContext();

var new_asset = db.Asset.

SingleOrDefault(x => x.name == _asset.name && x.serial_number == _asset.serial_number);

if (new_asset == null)

{

new_asset = new Asset

{

name = _asset.name,

description = _asset.description,

serial_number = _asset.serial_number,

_categoryid = _asset._categoryid,

_vendorid = _asset._vendorid,

_employeeid = Employee_manager.find_ID("default","default"),

date = new DateTime(2000, 1, 1)

};

db.Asset.Add(new_asset);

db.SaveChanges();

return true;

}

return false;

}

public static void update(Asset _asset)

{

var db = new DBContext();

var new_asset = db.Asset.SingleOrDefault(x => x.id == _asset.id);

new_asset.name = _asset.name;

new_asset.description = _asset.description;

new_asset.serial_number = _asset.serial_number;

new_asset._categoryid = _asset._categoryid;

new_asset._vendorid = _asset._vendorid;

new_asset._employeeid = _asset._employeeid;

new_asset.date = _asset.date;

db.SaveChanges();

}

public static void update_by_asset_package(asset_package _asset)

{

var db = new DBContext();

var new_asset = db.Asset.SingleOrDefault(x => x.id == _asset.id);

new_asset.name = _asset.name;

new_asset.description = _asset.description;

new_asset.serial_number = _asset.serial_number;

new_asset._categoryid = _asset._categoryid;

new_asset._vendorid = _asset._vendorid;

new_asset._employeeid = _asset._employeeid;

new_asset.date = _asset.date;

db.SaveChanges();

}

public class asset_package

{

public int id { get; set; }

public string name { get; set; }

public string description { get; set; }

public string serial_number { get; set; }

public string _category_name { get; set; }

public int _categoryid { get; set; }

public string _vendor_name { get; set; }

public int _vendorid { get; set; }

public string _employee_name { get; set; }

public int _employeeid { get; set; }

public DateTime date { get; set; }

}

--------------------------------------------------------------------------

//asset_sub_form

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

using System.Windows.Forms;

using CPRG254.Assets.Respositories;

namespace CPRG254.Assets.UI

{

public partial class Asset_sub_form : Form

{

public Asset_sub_form()

{

InitializeComponent();

var vendors = vendor_manager.get_all();

ux_vendor.DataSource = vendors;

ux_vendor.DisplayMember = "name";

ux_vendor.ValueMember = "id";

var categories = Category_manager.get_all();

ux_category.DataSource = categories;

ux_category.DisplayMember = "name";

ux_category.ValueMember = "id";

}

private string get_vendor_name_from_combobox()

{

string vendor_name = ux_vendor.GetItemText(ux_vendor.SelectedItem);

return vendor_name;

}

private string get_category_name_from_combobox()

{

string category_name = ux_category.GetItemText(ux_category.SelectedItem);

return category_name;

}

private void button1_Click(object sender, EventArgs e)

{

if(ux_category.Text=="" || ux_description.Text=="" || ux_name.Text==""

||ux_number.Text==""||ux_vendor.Text=="")

{

MessageBox.Show("information not complete");

return;

}

Asset_form.asset_name = ux_name.Text;

Asset_form.asset_description = ux_description.Text;

Asset_form.asset_serial_number = ux_number.Text;

Asset_form.asset_vendor_name = get_vendor_name_from_combobox();

Asset_form.asset_category_name = get_category_name_from_combobox();

this.Close();

}

private void button2_Click(object sender, EventArgs e)

{

Asset_form.cancel_add = true;

this.Close();

}

---------------------------------------------------------------------------

//employee_form

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using CPRG254.Assets.Respositories;
using CPRG254.Assets.Domain;

namespace CPRG254.Assets.UI
{
public partial class Employee_form : Form
{
public Employee_form()
{
InitializeComponent();

var departments = department_manager.get_all();

ux_department.DataSource = departments;

ux_department.DisplayMember = "name";

ux_department.ValueMember = "id";
}

private string get_department_name_from_combobox()
{
string department_name = ux_department.GetItemText(ux_department.SelectedItem);

return department_name;
}

//display employees in seledted department
private void button1_Click(object sender, EventArgs e)
{
ux_employee.DataSource = Employee_manager.get_all_from_department
(get_department_name_from_combobox());

form_style();
}

//display all employees
private void button2_Click(object sender, EventArgs e)
{
ux_employee.DataSource = Employee_manager.get_all();

form_style();
}

private void form_style()
{
var total_row = ux_employee.RowCount;

for (int i = 0; i < total_row; i++)
{
ux_employee[0, i].ReadOnly = true;
ux_employee[0, i].Style.BackColor = Color.LightGray;

ux_employee[3, i].ReadOnly = true;
ux_employee[3, i].Style.BackColor = Color.LightGray;
}
}

//add a new employee
private void button3_Click(object sender, EventArgs e)
{
if(ux_first_name.Text==""||ux_last_name.Text==""||ux_department.Text==""
||ux_phone.Text==""||ux_desk.Text=="")
{
MessageBox.Show("information not completed");
return;
}

var department_id = department_manager.find_ID(get_department_name_from_combobox());

var employee = new Emplyee
{
first_name = ux_first_name.Text,
last_name = ux_last_name.Text,

//_department = department,
//no need to create a new department, just assign employee to existing department id

_departmentid = department_id,
hire_date = Convert.ToDateTime( ux_hire_date.Value.ToShortDateString()),
phone = ux_phone.Text,
desk = ux_desk.Text
};

bool data_added = Employee_manager.add(employee);

if (data_added)
{
MessageBox.Show("new employee added");
}
else
{
MessageBox.Show("employee already exists");
}

button1.PerformClick();
}

//update single row
private void button4_Click(object sender, EventArgs e)
{
var _first_name = ux_employee.CurrentRow.Cells[1].Value.ToString();
var _last_name = ux_employee.CurrentRow.Cells[2].Value.ToString();
var _hire_date = Convert.ToDateTime( ux_employee.CurrentRow.Cells[5].Value);
var _phone = ux_employee.CurrentRow.Cells[6].Value.ToString();
var _desk = ux_employee.CurrentRow.Cells[7].Value.ToString();

var _department_id = Convert.ToInt32(ux_employee.CurrentRow.Cells[4].Value);
var _id = Convert.ToInt32(ux_employee.CurrentRow.Cells[0].Value);

var _employee = new Emplyee
{
id = _id,
_departmentid = _department_id,
first_name =_first_name,
last_name = _last_name,
hire_date = _hire_date,
phone = _phone,
desk = _desk
};

Employee_manager.update(_employee);

MessageBox.Show("ID " + _id + " updated");
}

//update all
private void button5_Click(object sender, EventArgs e)
{
var total_row = ux_employee.RowCount;

for (int i = 0; i < total_row; i++)
{
var _first_name = ux_employee[1,i].Value.ToString();
var _last_name = ux_employee[2,i].Value.ToString();
var _hire_date = Convert.ToDateTime(ux_employee[5,i].Value);
var _phone = ux_employee[6,i].Value.ToString();
var _desk = ux_employee[7,i].Value.ToString();

var _department_id = Convert.ToInt32(ux_employee[4,i].Value);
var _id = Convert.ToInt32(ux_employee[0,i].Value);

var _employee = new Emplyee
{
id = _id,
_departmentid = _department_id,
first_name = _first_name,
last_name = _last_name,
hire_date = _hire_date,
phone = _phone,
desk = _desk
};

Employee_manager.update(_employee);

}

MessageBox.Show("All updated");
}

private void updateSelectedRowToolStripMenuItem_Click(object sender, EventArgs e)
{
button4.PerformClick();
}

private void addANewEmployeeToolStripMenuItem_Click(object sender, EventArgs e)
{
button3.PerformClick();
}

private void displayAllToolStripMenuItem_Click(object sender, EventArgs e)
{
button2.PerformClick();
}

private void displayEmployeesInSelectedDepartmentToolStripMenuItem_Click(object sender, EventArgs e)
{
button1.PerformClick();
}

private void updateAllToolStripMenuItem_Click(object sender, EventArgs e)
{
button5.PerformClick();
}
}
}

-------------------------------------------------------------------------

//employee_manager

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

using CPRG254.Assets.Domain;

using CPRG254.Assets.Data;

namespace CPRG254.Assets.Respositories

{

public class Employee_manager

{

public static IList<employee_package> get_all_from_department(string _name)

{

var db = new DBContext();

var employees = db.Employee.

Where(x=>x._department.name == _name).

Select(x=> new employee_package

{

id = x.id,

first_name = x.first_name,

last_name=x.last_name,

department_name = x._department.name,

_departmentid = x._departmentid,

hire_date = x.hire_date,

phone = x.phone,

desk = x.desk

}).ToList();

return employees;

}

public static IList<employee_package> get_all()

{

var db = new DBContext();

var employees = db.Employee.

Select(x => new employee_package

{

id = x.id,

first_name = x.first_name,

last_name = x.last_name,

department_name = x._department.name,

_departmentid = x._departmentid,

hire_date = x.hire_date,

phone = x.phone,

desk = x.desk

}).ToList();

return employees;

}

public static bool add(Emplyee _employee)

{

var db = new DBContext();

var new_employee = db.Employee.

SingleOrDefault(x => (x.first_name == _employee.first_name && x.last_name==_employee.last_name));

if (new_employee == null)

{

new_employee = new Emplyee

{

first_name = _employee.first_name,

last_name = _employee.last_name,

//_department =_employee._department,

//no need to create a new department, just assign employee to existing department id

_departmentid = _employee._departmentid,

hire_date = _employee.hire_date,

phone = _employee.phone,

desk = _employee.desk

};

db.Employee.Add(new_employee);

db.SaveChanges();

return true;

}

return false;

}

public static void update(Emplyee _employee)

{

var db = new DBContext();

var new_employee = db.Employee.SingleOrDefault(x => x.id == _employee.id);

new_employee.last_name = _employee.last_name;

new_employee.first_name = _employee.first_name;

new_employee.hire_date = _employee.hire_date;

new_employee.phone = _employee.phone;

new_employee._departmentid = _employee._departmentid;

new_employee.desk = _employee.desk;

db.SaveChanges();

}

public static int find_ID(string _first_name, string _last_name)

{

var db = new DBContext();

var default_employee = db.Employee.SingleOrDefault(x => x.first_name == _first_name && x.last_name == _last_name);

return default_employee.id;

}

public class employee_package

{

public int id { get; set; }

public string first_name { get; set; }

public string last_name { get; set; }

public string department_name { get; set; }

public int _departmentid { get; set; }

public DateTime hire_date { get; set; }

public string phone { get; set; }

public string desk { get; set; }

}

-------------------------------------------------------------------

//vendor_form

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

using System.Windows.Forms;

using CPRG254.Assets.Domain;

using CPRG254.Assets.Respositories;

namespace CPRG254.Assets.UI

{

public partial class vendor_form : Form

{

public vendor_form()

{

InitializeComponent();

}

private void displayRefreshToolStripMenuItem_Click(object sender, EventArgs e)

{

refresh();

}

public static string vendor_name, vendor_phone;

private void addToolStripMenuItem_Click(object sender, EventArgs e)

{

var sub_form = new vendor_sub_form();

sub_form.ShowDialog();

var _vendor = new Vendor { name = vendor_name, phone=vendor_phone };

bool data_added = vendor_manager.add(_vendor);

if (data_added)

{

MessageBox.Show("new vendor added");

}

else

{

MessageBox.Show("vendor already exists");

}

refresh();

}

private void updateToolStripMenuItem_Click(object sender, EventArgs e)

{

var total_row = ux_vendor.RowCount;

for (int i = 0; i < total_row; i++)

{

var _name = ux_vendor[1, i].Value.ToString();

var _phone = ux_vendor[2, i].Value.ToString();

var _id = Convert.ToInt32(ux_vendor[0, i].Value);

var _vendor = new Vendor { id = _id, name = _name,phone=_phone };

vendor_manager.update(_vendor);

}

MessageBox.Show("All updated");

}

private void refresh()

{

ux_vendor.DataSource = vendor_manager.get_all();

var total_row = ux_vendor.RowCount;

for (int i = 0; i < total_row; i++)

{

ux_vendor[0, i].ReadOnly = true;

ux_vendor[0, i].Style.BackColor = Color.LightGray;

}

---------------------------------------------------

//vendor_manager

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

using CPRG254.Assets.Domain;

using CPRG254.Assets.Data;

namespace CPRG254.Assets.Respositories

{

public class vendor_manager

{

public static void update(Vendor _vendor)

{

var db = new DBContext();

var new_vendor = db.Vendor.SingleOrDefault(x => x.id == _vendor.id);

new_vendor.name = _vendor.name;

new_vendor.phone = _vendor.phone;

db.SaveChanges();

}

public static IList<Vendor> get_all()

{

var db = new DBContext();

var vendors = db.Vendor.ToList();

return vendors;

}

public static bool add(Vendor _vendor)

{

var db = new DBContext();

var new_vendor = db.Vendor.SingleOrDefault(x => x.name == _vendor.name);

if (new_vendor == null)

{

new_vendor = new Vendor { name = _vendor.name, phone = _vendor.phone };

db.Vendor.Add(new_vendor);

db.SaveChanges();

return true;

}

return false;

}

public static int find_ID(string vendor_name)

{

var db = new DBContext();

var new_vendor = db.Vendor.SingleOrDefault(x => x.name == vendor_name);

return new_vendor.id;

}

------------------------------------------------------

//vendor_sub_form

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

using System.Windows.Forms;

namespace CPRG254.Assets.UI

{

public partial class vendor_sub_form : Form

{

public vendor_sub_form()

{

InitializeComponent();

}

private void button1_Click(object sender, EventArgs e)

{

if (ux_vendor_name.Text == "" || ux_vendor_phone.Text == "")

{

MessageBox.Show("information is not completely filled out");

}

else

{

vendor_form.vendor_name = ux_vendor_name.Text;

vendor_form.vendor_phone = ux_vendor_phone.Text;

this.Close();

}

-----------------------------------------------------------------

//DBcontext

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

using System.Data.Entity;

using CPRG254.Assets.Domain;

namespace CPRG254.Assets.Data

{

public class DBContext:DbContext

{

public DBContext():base("name = Connection") { }

public DbSet<Asset> Asset { get; set; }

public DbSet<Category> Category { get; set; }

public DbSet<Department> Department { get; set; }

public DbSet<Emplyee> Employee { get; set; }

public DbSet<Vendor> Vendor { get; set; }

}

--------------------------------------------------------------------

//migration configration

namespace CPRG254.Assets.Data.Migrations

{

using System;

using System.Data.Entity;

using System.Data.Entity.Migrations;

using System.Linq;

using CPRG254.Assets.Domain;

internal sealed class Configuration : DbMigrationsConfiguration<CPRG254.Assets.Data.DBContext>

{

public Configuration()

{

AutomaticMigrationsEnabled = false;

ContextKey = "CPRG254.Assets.Data.DBContext";

}

protected override void Seed(CPRG254.Assets.Data.DBContext context)

{

// This method will be called after migrating to the latest version.

// You can use the DbSet<T>.AddOrUpdate() helper extension method

// to avoid creating duplicate seed data. E.g.

// context.People.AddOrUpdate(

// p => p.FullName,

// new Person { FullName = "Andrew Peters" },

// new Person { FullName = "Brice Lambson" },

// new Person { FullName = "Rowan Miller" }

// );

//construct new department

var department = new Department[]

{

new Department {name = "Calgary" },

new Department {name = "Edmonton" },

new Department {name = "Vancouver" },

new Department {name = "Toronto" }

};

context.Department.AddOrUpdate(x => x.name, department);

context.SaveChanges();

//construct new category

var category = new Category[]

{

new Category {name = "Laptop" },

new Category {name = "Desktop" },

new Category {name = "Smart Phone" }

};

context.Category.AddOrUpdate(x => x.name, category);

context.SaveChanges();

//construct new vendor

var vendor = new Vendor[]

{

new Vendor {name = "Bestbuy", phone = "4234" },

new Vendor {name = "Memory Express", phone = "65756" },

new Vendor {name = "Telus", phone = "9808" }

};

context.Vendor.AddOrUpdate(x => x.name, vendor);

context.SaveChanges();

//construct new employee

var department_1 = context.Department.SingleOrDefault(x => x.name == "Calgary");

var department_2 = context.Department.SingleOrDefault(x => x.name == "Edmonton");

var department_3 = context.Department.SingleOrDefault(x => x.name == "Vancouver");

var department_4 = context.Department.SingleOrDefault(x => x.name == "Toronto");

var employee = new Emplyee[]

{

new Emplyee

{ first_name="default",

last_name ="default",

_departmentid = department_1.id,

hire_date = new DateTime(2015, 1, 18),

phone = "4031234567",

desk = "101"

new Emplyee

{ first_name="John",

last_name ="Doe",

_departmentid = department_2.id,

hire_date = new DateTime(2015, 1, 18),

phone = "123321123",

desk = "9"

new Emplyee

{first_name="Mark",

last_name ="Jordan",

_departmentid = department_3.id,

hire_date = new DateTime(2015, 1, 18),

phone = "2342665",

desk = "657"

new Emplyee

{first_name="Frank",

last_name ="Eistan",

_departmentid = department_4.id,

hire_date = new DateTime(2015, 1, 18),

phone = "6778698",

desk = "5"

}

};

context.Employee.AddOrUpdate(x =>new { x.first_name, x.last_name }, employee);

context.SaveChanges();

//construct new asset

var category_1 = context.Category.SingleOrDefault(x => x.name == "Laptop");

var vendor_1 = context.Vendor.SingleOrDefault(x => x.name == "Bestbuy");

var employee_1 = context.Employee.SingleOrDefault(x => x.first_name == "default" && x.last_name == "default" );

var category_2 = context.Category.SingleOrDefault(x => x.name == "Desktop");

var vendor_2 = context.Vendor.SingleOrDefault(x => x.name == "Memory Express");

var employee_2 = context.Employee.SingleOrDefault(x => x.first_name == "John" && x.last_name == "Doe");

var category_3 = context.Category.SingleOrDefault(x => x.name == "Smart Phone");

var vendor_3 = context.Vendor.SingleOrDefault(x => x.name == "Telus");

var employee_3 = context.Employee.SingleOrDefault(x => x.first_name == "Mark" && x.last_name == "Jordan");

var employee_4 = context.Employee.SingleOrDefault(x => x.first_name == "Frank" && x.last_name == "Eistan");

var asset = new Asset[]

{

new Asset

{

name = "dell laptop",

date = new DateTime(2000, 1, 1),

_categoryid = category_1.id,

_vendorid = vendor_1.id,

_employeeid = employee_1.id,

description = "11 inch screen",

serial_number = "123"

new Asset

{

name = "Asus laptop",

date = new DateTime(2000, 1, 1),

_categoryid = category_1.id,

_vendorid = vendor_2.id,

_employeeid = employee_2.id,

description = "ultra book",

serial_number = "2432"

new Asset

{

name = "Hp desktop",

date = new DateTime(2000, 1, 1),

_categoryid = category_2.id,

_vendorid = vendor_1.id,

_employeeid = employee_3.id,

description = "i7",

serial_number = "234242"

new Asset

{

name = "msi desktop",

date = new DateTime(2000, 1, 1),

_categoryid = category_2.id,

_vendorid = vendor_2.id,

_employeeid = employee_4.id,

description = "gaming",

serial_number = "456546"

new Asset

{

name = "apple smartphone",

date = new DateTime(2000, 1, 1),

_categoryid = category_3.id,

_vendorid = vendor_3.id,

_employeeid = employee_1.id,

description = "ios 10",

serial_number = "43535"

new Asset

{

name = "samsung smartphone",

date = new DateTime(2000, 1, 1),

_categoryid = category_3.id,

_vendorid = vendor_3.id,

_employeeid = employee_3.id,

description = "LTE",

serial_number = "64766"

}

};

context.Asset.AddOrUpdate(x => x.name, asset);

context.SaveChanges();

}

---------------------------------------------------------------------

//app.config

<?xml version="1.0" encoding="utf-8"?>

</configSections>

</parameters>

</defaultConnectionFactory>

</providers>

</entityFramework>

<add connectionString="server=DESKTOP-EUFGQAP;database=XYZ;Trusted_Connection=Yes"

name="Connection"

providerName="System.Data.SqlClient"/>

</connectionStrings>

</configuration>

----------------------------------------------------------------------

//asset

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

namespace CPRG254.Assets.Domain

{

public class Asset

{

public int id { get; set; }

public string name { get; set; }

public string description { get; set; }

public string serial_number { get; set; }

public Category _category { get; set; }

public int _categoryid { get; set; }

public Vendor _vendor { get; set; }

public int _vendorid { get; set; }

public Emplyee _employee { get; set; }

public int _employeeid { get; set; }

public DateTime date { get; set; }

}

----------------------------------------------------

//employee

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

namespace CPRG254.Assets.Domain

{

public class Emplyee

{

public int id { get; set; }

public string first_name { get; set; }

public string last_name { get; set; }

public Department _department { get; set; }

public int _departmentid { get; set; }

public DateTime hire_date { get; set; }

public string phone { get; set; }

public string desk { get; set; }

}

----------------------------------------------------

//vendor

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

namespace CPRG254.Assets.Domain

{

public class Vendor

{

public int id { get; set; }

public string name { get; set; }

public string phone { get; set; }

}

chuanshuoge

Saturday 15 October 2016

ADO.NET final

No comments:

Post a Comment

Chuanshuoge, Calgary, Canada, Earth, Solar system, Milky Way Galaxy

_ChuanShuo^Ge - _传说^哥