public List <DistrictViewModel> Search(string keyword = "", string cityCode = "")
{
using (var dbContext = new MyCountryEntities())
{
//using Join
////var query = (from d in dbContext.Districts
//// join c in dbContext.Cities on d.CityCode equals c.CityCode
//// select new DistrictViewModel()
//// {
//// DistrictCode = d.DistrictCode,
//// DistrictName = d.Name,
//// CityCode = c.CityCode,
//// CityName= c.Name
//// });
//not use join
var query = dbContext.Districts.Select(d => new DistrictViewModel {
DistrictCode = d.DistrictCode, DistrictName = d.Name, CityCode = d.CityCode, CityName = d.City.Name
});
if (!string.IsNullOrEmpty(keyword))
{
query = query.Where(d => d.DistrictCode.Contains(keyword) || d.DistrictName.Contains(keyword));
}
if (!string.IsNullOrEmpty(cityCode))
{
query = query.Where(c => c.CityCode == cityCode);
}
var result = query.OrderBy(x => x.DistrictCode).ToList();
return(result);
}
}
public District GetByCode(string districtCode)
{
using (var dbContext = new MyCountryEntities())
{
return(InnerGetDistrictByCode(dbContext, districtCode));
}
}
private void DistrictDetailForm_Load(object sender, EventArgs e)
{
var cities = new MyCountryEntities();
cbbCity.DataSource = cities;
cbbCity.DisplayMember = nameof(City.Name);
if (!_isAddNew)
{
cbbCity.Enabled = false;
txtCode.Enabled = false;
var dbContext = new MyCountryEntities();
var editingDistrict = dbContext.Districts.FirstOrDefault(x => x.DistrictCode == _selectedCode);
if (editingDistrict != null)
{
txtName.Text = editingDistrict.Name;
txtCode.Text = editingDistrict.DistrictCode;
txtType.Text = editingDistrict.Type;
var city = cities.Districts.FirstOrDefault(x => x.CityCode == editingDistrict.CityCode);
cbbCity.SelectedItem = city;
}
else
{
MessageBox.Show(@"Cannot found district.", @"Message", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
btnSave.Enabled = false;
}
}
}
public List <CityInfomation> GetCityInformations()
{
//1
//var cityInfomations = dbContext.Districts.GroupBy(d => d.City.Name).Select(
// c => new CityInfomation()
// {
// CityName = c.Key,
// DistrictNames = c.Select(x => x.Name).ToList()
// }).ToList();
//2
//var cityInfomations = dbContext.Cities.GroupBy(d => d.Name).Select(
// c => new CityInfomation()
// {
// CityName = c.Key,
// DistrictNames = c.SelectMany(x=> x.Districts).Select(x=> x.Name).ToList()
// }).ToList();
//3
//var cityInfomations = (from c in dbContext.Cities
// join d in dbContext.Districts on c.CityCode equals d.CityCode into cityDistricts
// from cd in cityDistricts
// group cd by cd.City.Name into g
// select new CityInfomation()
// {
// CityName = g.Key,
// DistrictNames = g.Select(x => x.Name).ToList()
// }).ToList();
//4
//var cityQuery = from c in dbContext.Cities
// join d in dbContext.Districts on c.CityCode equals d.CityCode
// select new
// {
// CityName = c.Name,
// DistrictName = d.Name
// };
//var cityInfomations = (from c in cityQuery
// group c by c.CityName
// into g
// select new CityInfomation()
// {
// CityName = g.Key,
// DistrictNames = g.Select(x => x.DistrictName).ToList()
// }).ToList();
//Lazy loading
using (var dbContext = new MyCountryEntities())
{
var cityInfomations = dbContext.Cities.Select(x => new CityInfomation
{
CityName = x.Name,
DistrictNames = x.Districts.Select(d => d.Name).ToList()
}).ToList();
return(cityInfomations);
}
}
private void btn_delete_Click(object sender, EventArgs e)
{
if (grdDistrict.SelectedRows.Count > 0)
{
var districtViewModel = grdDistrict.SelectedRows[0].DataBoundItem as DistrictViewModel;
if (districtViewModel != null)
{
var dialogResult = MessageBox.Show($@"Do you really want to delete the District {districtViewModel.DistrictName}?", @"Confirmation", MessageBoxButtons.YesNo, MessageBoxIcon.Exclamation);
if (dialogResult == DialogResult.Yes)
{
var dbContext = new MyCountryEntities();
var districtCode = districtViewModel.DistrictCode;
var district = dbContext.Districts.FirstOrDefault(x => x.DistrictCode == districtCode);
if (district != null)
{
dbContext.Districts.Remove(district);
dbContext.SaveChanges();
SearchDistrictInformations();
MessageBox.Show(@"Successfully deleted the district.", @"Message", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
}
}
else
{
MessageBox.Show(@"Please select a district to delete", @"Message", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
}
public List <City> GetCities()
{
using (var dbContext = new MyCountryEntities())
{
return(dbContext.Cities.ToList());
}
}
public List <DistrictViewModel> SearchDistricts(string keyword = "", string cityCode = "")
{
var dbContext = new MyCountryEntities();
var query = (from d in dbContext.Districts
join c in dbContext.Cities on d.CityCode equals c.CityCode
select new DistrictViewModel()
{
DistrictCode = d.DistrictCode,
DistrictName = d.Name,
CityCode = c.CityCode,
CityName = c.Name
});
if (!string.IsNullOrEmpty(keyword))
{
query = query.Where(d => d.DistrictCode.Contains(keyword) || d.DistrictName.Contains(keyword));
}
if (!string.IsNullOrEmpty(cityCode))
{
query = query.Where(c => c.CityCode == cityCode);
}
var result = query.OrderBy(x => x.DistrictCode).ToList();
return(result);
}
public void Add(District districtParameter)
{
using (var dbContext = new MyCountryEntities())
{
var currentDateTime = DateTimeUtil.GetCurrentDateTime();
var existingDistrict = InnerGetDistrictByCode(dbContext, districtParameter.DistrictCode);
if (existingDistrict != null)
{
throw new DistrictValiationException($@"District code {existingDistrict.DistrictCode} exists.");
}
var district = new District
{
Name = districtParameter.Name,
DistrictCode = districtParameter.DistrictCode,
Type = districtParameter.Type,
CityCode = districtParameter.CityCode,
CreatedDate = currentDateTime,
CreatedBy = ServiceContext.UserName,
ModifiedDate = currentDateTime,
ModifiedBy = ServiceContext.UserName
};
dbContext.Districts.Add(district);
dbContext.SaveChanges();
}
}
public void Add(District districtInfo)
{
using (var dbContext = new MyCountryEntities())
{
var existingDistrict = InnerGetDistrictByCode(dbContext, districtInfo.DistrictCode);
if (existingDistrict != null)
{
//raise duplicated district error here
throw new DistrictValidationException($"District code {existingDistrict.DistrictCode} exists.");
}
var district = new District
{
DistrictCode = districtInfo.DistrictCode,
Name = districtInfo.Name,
Type = districtInfo.Type,
CityCode = districtInfo.CityCode,
CreatedDate = DateTime.Now,
CreatedBy = ServiceContext.UserName,
ModifiedDate = DateTime.Now,
ModifiedBy = ServiceContext.UserName
};
dbContext.Districts.Add(district);
dbContext.SaveChanges();
}
}
public List <City> GetCities()
{
var dbContext = new MyCountryEntities();
var cities = dbContext.Cities.ToList();
return(cities);
}
public void Update(District districtInfo)
{
using (var dbContext = new MyCountryEntities())
{
var existingDistrict = InnerGetDistrictByCode(dbContext, districtInfo.DistrictCode);
if (existingDistrict != null)
{
existingDistrict.Name = districtInfo.Name;
existingDistrict.Type = districtInfo.Type;
existingDistrict.ModifiedDate = DateTime.Now;
existingDistrict.ModifiedBy = ServiceContext.UserName;
dbContext.SaveChanges();
}
}
}
private void btnlogin_Click(object sender, EventArgs e)
{
var userName = txtUserName.Text;
var pass = txtPassword.Text;
var dbContext = new MyCountryEntities();
var user = dbContext.Users.FirstOrDefault(x => x.UserName == userName && x.Password == pass);
if (user != null)
{
MessageBox.Show(@"Login successful", @"Message", MessageBoxButtons.OK);
this.UserName = user.UserName;
DialogResult = DialogResult.OK;
}
else
{
MessageBox.Show(@"Login failed", @"Message", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
}
public List <DistrictViewModel> SearchDistricts(string search, string cityCode = "")
{
var dbContect = new MyCountryEntities();
var query = dbContect.Districts.Select(x => new DistrictViewModel
{
DistrictCode = x.DistrictCode,
DistrictName = x.Name,
CityCode = x.CityCode,
CityName = x.City.Name//Join
});
if (!string.IsNullOrEmpty(search))
{
query = query.Where(x => x.DistrictCode.Contains(search) || x.DistrictName.Contains(search));
}
if (!string.IsNullOrEmpty(cityCode))
{
query = query.Where(x => x.CityCode.Contains(cityCode));
}
//if (!string.IsNullOrEmpty(search))
//{
// if (!String.IsNullOrEmpty(cityCode))
// {
// query = query.Where(x => (x.DistrictCode.Contains(search) || x.Name.Contains(search)) && x.CityCode.Contains(cityCode));//
// }
// else
// {
// query = query.Where(x => x.DistrictCode.Contains(search) || x.Name.Contains(search));
// }
//}
//else
//{
// if (!String.IsNullOrEmpty(cityCode))
// {
// query = query.Where(x => x.CityCode.Contains(cityCode));
// }
//}
var result = query.OrderBy(x => x.DistrictCode).ToList();
return(result);
}
public void Update(District districtParameter)
{
using (var dbContext = new MyCountryEntities())
{
var currentDateTime = DateTimeUtil.GetCurrentDateTime();
var existingDistrict = InnerGetDistrictByCode(dbContext, districtParameter.DistrictCode);
if (existingDistrict == null)
{
throw new DistrictValiationException(@"Cannot found district");
}
existingDistrict.Name = districtParameter.Name;
existingDistrict.Type = districtParameter.Type;
existingDistrict.ModifiedDate = currentDateTime;
existingDistrict.ModifiedBy = ServiceContext.UserName;
dbContext.SaveChanges();
}
}
public List <CityInfomation> GetCityInfomations()
{
var dbContect = new MyCountryEntities();
//var cityInfomations = dbContect.Districts.GroupBy(d => new { d.CityCode, d.City.Name }).Select(
// c => new CityInfomation()
// {
// CityCode = c.Key.CityCode,
// CityName = c.Key.Name,
// DistrictNames = c.Select(x => x.Name).ToList()
// }).ToList();
var cityInfomations = dbContect.Cities.Select(x => new CityInfomation()
{
CityName = x.Name,
DistrictNames = x.Districts.Select(d => d.Name).ToList(),
}).ToList();
return(cityInfomations);
}
private void btnSave_Click(object sender, EventArgs e)
{
try
{
if (_isAddNew)
{
var dbContext = new MyCountryEntities();
var city = cbbCity.SelectedItem as City;
var district = new District
{
Name = txtName.Text,
DistrictCode = txtCode.Text,
Type = txtType.Text,
CityCode = city != null ? city.CityCode : string.Empty
};
dbContext.Districts.Add(district);
dbContext.SaveChanges();
}
else
{
//Todo: Implement editing district
var dbContext = new MyCountryEntities();
var district = dbContext.Districts.FirstOrDefault(x => x.DistrictCode == _selectedCode);
if (district != null)
{
district.Name = txtName.Text;
district.Type = txtType.Text;
dbContext.SaveChanges();
}
}
}
catch (Exception exception)
{
MessageBox.Show(exception.Message);
}
}
private static District InnerGetDistrictByCode(MyCountryEntities dbContext, string districtCode)
{
return(dbContext.Districts.FirstOrDefault(x => x.DistrictCode == districtCode));
}
