// Инициализыция формы
public FormRowStatistic(string table, DataGridViewSelectedCellCollection selectedCells)
{
InitializeComponent();
using (var context = new AquariumContext())
{
int key = int.Parse(selectedCells[0].Value.ToString());
switch (table)
{
case "Fish":
DataGridView.DataSource = context.Statistics.Where(s => s.FishId == key).ToList();
break;
case "Plant":
DataGridView.DataSource = context.Statistics.Where(s => s.PlantId == key).ToList();
break;
}
DataGridView.Columns["Fish"].Visible = false;
DataGridView.Columns["Plant"].Visible = false;
DataGridView.Columns["Plant"].Visible = false;
DataGridView.Columns["Id"].HeaderText = "№";
DataGridView.Columns["DateTime"].HeaderText = "Дата/Время";
DataGridView.Columns["LivingСonditions"].HeaderText = "Условия обитания";
DataGridView.Columns["Nutrition"].HeaderText = "Питание";
DataGridView.Columns["Population"].HeaderText = "Популяция";
DataGridView.Columns["FishId"].Visible = false;
DataGridView.Columns["PlantId"].Visible = false;
DataGridView.Refresh();
}
}
static void Main(string[] args)
{
var db = new AquariumContext();
//selects all from database as a list
//var allAquaLife = db.AquaticLife.ToList();
//var allOcean = db.Ocean.ToList();
//var allAquarium = db.Aquarium.ToList();
//adding to database for oceans
//var someOcean = new Oceans
//{
// Name = "Atlantic",
// AverageTemperature = 65.1
//};
//db.Ocean.Add(someOcean);
//db.SaveChanges();
//adding to database for Aquariums
var someAquarium = new Aquariums
{
Name = "Davey Jones' Locker",
City = "Bikini Bottom"
};
db.Aquarium.Add(someAquarium);
db.SaveChanges();
////adding to database
//var aquaticLife = new AquaticLifes
//{
// Type = "Oyster",
// Color = "Metalic Grey",
// Name = "Cloyster",
// DateAddedToTank = DateTime.Now.Date,
// IsInQuarantee = false
//};
//db.AquaticLife.Add(aquaticLife);
//db.SaveChanges();
////select the name of all aquatic lifes of type fish
//var fish = db.AquaticLife.Where(w => w.Type == "Fish").Select(s => s.Name);
////updating a fish name
//var shell = db.AquaticLife.First(f => f.Name == "Cloyster");
//shell.Name = "Sheldor";
//db.SaveChanges();
////deleting an aquaticlife
//var deletingYou = db.AquaticLife.First(f => f.Type == "Oyster");
//db.AquaticLife.Remove(deletingYou);
//db.SaveChanges();
}
// Сохранение данных
private void BSave_Click(object sender, EventArgs e)
{
var dateTime = TBDateTime.Text;
var livingСonditions = TBLivingСonditions.Text;
var nutrition = TBNutrition.Text;
var population = TBPopulation.Text;
var key = TreeView.SelectedNode != null?int.Parse(TreeView.SelectedNode.Name) : -1;
if (dateTime.Equals("") || livingСonditions.Equals("") || nutrition.Equals("") || population.Equals("") || key == -1)
{
MessageBox.Show("Укажите все параметры", "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
var stat = new Statistic();
using (var context = new AquariumContext())
{
switch (param)
{
case ActionParameters.AddStatistics:
stat.DateTime = Convert.ToDateTime(dateTime);
stat.LivingСonditions = livingСonditions;
stat.Nutrition = nutrition;
stat.Population = int.Parse(population);
if (RBFish.Checked)
{
stat.FishId = key;
}
if (RBPlant.Checked)
{
stat.PlantId = key;
}
context.Statistics.Add(stat);
break;
case ActionParameters.EditStatistics:
stat = context.Statistics.Find(selectedCells[0].Value);
stat.DateTime = Convert.ToDateTime(dateTime);
stat.LivingСonditions = livingСonditions;
stat.Nutrition = nutrition;
stat.Population = int.Parse(population);
if (RBFish.Checked)
{
stat.FishId = key; stat.PlantId = null;
}
if (RBPlant.Checked)
{
stat.PlantId = key; stat.FishId = null;
}
break;
}
context.SaveChanges();
}
Hide();
fsClass.InitDGVStatistics();
}
public void InitDGVPlants()
{
using (var context = new AquariumContext())
{
context.Plants.Load();
DGVPlant.DataSource = context.Plants.Local.ToBindingList();
DGVPlant.Columns["Statistics"].Visible = false;
DGVPlant.Columns["Id"].HeaderText = "№";
DGVPlant.Columns["Name"].HeaderText = "Название";
DGVPlant.Columns["Type"].HeaderText = "Разновидность";
DGVPlant.Refresh();
}
}
// Действие при смене RadioButton
private void RadioButton_CheckedChanged(object sender, EventArgs e)
{
using (var context = new AquariumContext())
{
TreeView.Nodes.Clear();
if (RBFish.Checked)
{
var listFish = context.Fishes.ToList();
switch (param)
{
case ActionParameters.AddStatistics:
listFish.ForEach(item => { TreeView.Nodes.Add(item.Id.ToString(), item.Id + " | " + item.Name + " | " + item.Type); });
break;
case ActionParameters.EditStatistics:
var fish = selectedCells[5].Value;
listFish.ForEach(item =>
{
var tn = TreeView.Nodes.Add(item.Id.ToString(), item.Id + " | " + item.Name + " | " + item.Type);
if (fish != null && item.Id == (int)fish)
{
SelectedNode(tn);
}
});
break;
}
}
if (RBPlant.Checked)
{
var listPlants = context.Plants.ToList();
switch (param)
{
case ActionParameters.AddStatistics:
listPlants.ForEach(item => { TreeView.Nodes.Add(item.Id.ToString(), item.Id + " | " + item.Name + " | " + item.Type); });
break;
case ActionParameters.EditStatistics:
var plants = selectedCells[7].Value;
listPlants.ForEach(item =>
{
var tn = TreeView.Nodes.Add(item.Id.ToString(), item.Id + " | " + item.Name + " | " + item.Type);
if (plants != null && RBPlant.Checked && item.Id == (int)plants)
{
SelectedNode(tn);
}
});
break;
}
}
}
}
// Кнопка сохранения данных
private void ButtonSave_Click(object sender, EventArgs e)
{
var name = TBName.Text;
var type = TBType.Text;
using (var context = new AquariumContext())
{
switch (param)
{
case ActionParameters.AddFish:
context.Fishes.Add(new Fish {
Name = name, Type = type
});
break;
case ActionParameters.AddPlant:
context.Plants.Add(new Plant {
Name = name, Type = type
});
break;
case ActionParameters.EditFish:
var fish = context.Fishes.Find(selectedCells[0].Value);
fish.Name = name;
fish.Type = type;
context.Entry(fish).State = EntityState.Modified;
break;
case ActionParameters.EditPlant:
var plant = context.Plants.Find(selectedCells[0].Value);
plant.Name = name;
plant.Type = type;
context.Entry(plant).State = EntityState.Modified;
break;
}
context.SaveChanges();
}
Hide();
switch (param)
{
case ActionParameters.AddFish:
case ActionParameters.EditFish:
fsClass.InitDGVFish();
break;
case ActionParameters.AddPlant:
case ActionParameters.EditPlant:
fsClass.InitDGVPlants();
break;
}
}
static void Main(string[] args)
{
var db = new AquariumContext();
var query5 = db.AquariumAquaticLifeOcean
.Include(i => i.Aquariums)
.Include(i => i.AquaticLifes)
.Include(i => i.Oceans)
.Where(w => w.Aquariums.Name == "Monterey");
foreach (var item in query5)
{
Console.WriteLine(item.AquaticLifes.Name);
}
}
private void BDelete_Click(object sender, EventArgs e)
{
using (var context = new AquariumContext())
{
DialogResult dr = MessageBox.Show("Подтверждение удаления!", "Удаление", MessageBoxButtons.OKCancel, MessageBoxIcon.Question);
if (dr == DialogResult.OK)
{
switch (TabControl.SelectedIndex)
{
case 0:
foreach (DataGridViewRow row in DGVFish.SelectedRows)
{
var deleteFish = context.Fishes.Find(row.Cells[0].Value);
deleteFish.Statistics.Clear();
context.Fishes.Remove(deleteFish);
}
context.SaveChanges();
InitDGVFish();
break;
case 1:
foreach (DataGridViewRow row in DGVPlant.SelectedRows)
{
var deletePlant = context.Plants.Find(row.Cells[0].Value);
deletePlant.Statistics.Clear();
context.Plants.Remove(deletePlant);
}
context.SaveChanges();
InitDGVPlants();
break;
case 2:
foreach (DataGridViewRow row in DGVStatistics.SelectedRows)
{
var deleteStatistics = context.Statistics.Find(row.Cells[0].Value);
context.Statistics.Remove(deleteStatistics);
}
context.SaveChanges();
InitDGVStatistics();
break;
}
}
}
}
public void InitDGVStatistics()
{
using (var context = new AquariumContext())
{
context.Statistics.Load();
DGVStatistics.DataSource = context.Statistics.Local.ToBindingList();
DGVStatistics.Columns["Fish"].Visible = false;
DGVStatistics.Columns["Plant"].Visible = false;
DGVStatistics.Columns["Plant"].Visible = false;
DGVStatistics.Columns["Id"].HeaderText = "№";
DGVStatistics.Columns["DateTime"].HeaderText = "Дата/Время";
DGVStatistics.Columns["LivingСonditions"].HeaderText = "Условия обитания";
DGVStatistics.Columns["Nutrition"].HeaderText = "Питание";
DGVStatistics.Columns["Population"].HeaderText = "Популяция";
DGVStatistics.Columns["FishId"].HeaderText = "№ рыбы";
DGVStatistics.Columns["PlantId"].HeaderText = "№ водоросли";
DGVStatistics.Refresh();
}
}
static void Main(string[] args)
{
var db = new AquariumContext();
//Gets all Aquariums from database so they can be worked with here
var allAquariums = db.Aquariums.ToList();
//Gets all AquaticLifes from database
var allAquaticLifes = db.AquaticLifes.ToList();
//Gets all Oceans from database
var allOceans = db.Oceans.ToList();
//Creates new Aquarium
var aquarium = new Aquarium
{
Name = "San Fran Aquarium",
City = "San Francisco"
};
db.Aquariums.Add(aquarium);
db.SaveChanges();
//READs from Database
var aquariums = db.Aquariums.ToList();
var cali = db.Aquariums.Where(w => w.City == "San Francisco");
//Calls San Fran Aquarium so it can be used in program
var sanFranAquarium = db.Aquariums.First(f => f.Name == "San Fran Aquarium");
//DELETEs from Database
db.Aquariums.Remove(sanFranAquarium);
//For Assignment:
//A SQL Query that given an Aquarium Name, What AquaticLife is there
var database = new AquariumContext();
var query1 = database.AquariumAquaticLifeOcean
.Include(i => i.Aquarium) //joins
.Where(w => w.Aquarium.Name == "Fish Tank");
foreach (var item in query1)
{
Console.WriteLine(item.AquaticLife.Name);
}
//A SQL Query that, given an Ocean, What Aquariums have fish from that ocean
//A SQL Query that Returns Only the Distinct(new topic) Cities that have aquariums
// var citiesWithAquariums = db.Aquariums.Distinct().Where(Aquarium.City);
//A SQL Query that Gives the Count(new topic) of How many species of AquaticLife live in each Ocean
var totalNumOfSpecies = db.AquaticLifes.Count();
}
static void Main(string[] args)
{
//connection with Db context
var db = new AquariumContext();
//Create Ocean OR Update Ocean
db.Oceans.AddOrUpdate(
a => a.Name,
new Oceans
{
Name = "Pacific Ocean",
AvgTemp = 86
},
new Oceans
{
Name = "Atlantic Ocean",
AvgTemp = 57
},
new Oceans
{
Name = "Indian Ocean",
AvgTemp = 77
},
new Oceans
{
Name = "Artic Ocean",
AvgTemp = 31
},
new Oceans
{
Name = "Southern Ocean",
AvgTemp = 20
});
//Read Oceans
var allOceans = db.Oceans.ToList();
////Delete Ocean
//var oceanToDelete = db.Oceans.First(f => f.Name == "Southern Ocean");
//db.Oceans.Remove(oceanToDelete);
//Save to DB
db.SaveChanges();
//Create Aquarium OR Update Aquarium
db.Aquariums.AddOrUpdate(
a => a.Name,
new Aquariums
{
Name = "Georgia Aquarium",
City = "Georgia"
},
new Aquariums
{
Name = "Montery Bay Aquarium",
City = "Montery"
},
new Aquariums
{
Name = "Aquarium of Genoa",
City = "Genova"
},
new Aquariums
{
Name = "New England Aquarium",
City = "Boston"
},
new Aquariums
{
Name = "The Florida Aquarium",
City = "Channelside"
},
new Aquariums
{
Name = "Shanghai Ocean Aquarium",
City = "Shanghai Shi"
});
//Read Aquariums
var allAquariums = db.Aquariums.ToString();
////Delete Aquarium
//var aquariumToDelete = db.Aquariums.First(f => f.Name == "Shanghai Ocean Aquarium");
//db.Aquariums.Remove(aquariumToDelete);
//Save to DB
db.SaveChanges();
//Add AquaticLife OR Update AquaticLife
db.MarineLife.AddOrUpdate(
a => a.Name,
new MarineLife
{
Type = "Fish",
Color = "Yellow",
Name = "Angelfish",
IsInQuarenteen = false
},
new MarineLife
{
Type = "Fish",
Color = "Orange",
Name = "Clown Fish",
IsInQuarenteen = false
},
new MarineLife
{
Type = "Fish",
Color = "Green",
Name = "Electric Eel",
IsInQuarenteen = false
},
new MarineLife
{
Type = "Fish",
Color = "Grey",
Name = "Piranha",
IsInQuarenteen = true
},
new MarineLife
{
Type = "Fish",
Color = "Blue",
Name = "Tang",
IsInQuarenteen = false
},
new MarineLife
{
Type = "Mammal",
Color = "Black",
Name = "Killer Whale",
IsInQuarenteen = false
},
new MarineLife
{
Type = "Mammal",
Color = "White",
Name = "Polar Bear",
IsInQuarenteen = false
},
new MarineLife
{
Type = "Mammal",
Color = "Brown",
Name = "Fur Seal",
IsInQuarenteen = false
});
//Read MarineLife
var allMarineLife = db.MarineLife.ToList();
//Delete Aquarium
//var marineLifeToDelete = db.MarineLife.First(f => f.Name == "Clown Fish");
//db.MarineLife.Remove(marineLifeToDelete);
//Save to DB
db.SaveChanges();
//Add MarineLife to GA Aquarium
var ga = db.Aquariums.First(f => f.Name == "Georgia Aquarium");
var ang = db.MarineLife.First(f => f.Name == "Angelfish");
//ga.MarineLife.Add(ang);
var clo = db.MarineLife.First(f => f.Name == "Clown Fish");
//ga.MarineLife.Add(clo);
var eel = db.MarineLife.First(f => f.Name == "Electric Eel");
//ga.MarineLife.Add(eel);
//Add MarineLife to Montery Bay Aquarium
var mb = db.Aquariums.First(f => f.Name == "Montery Bay Aquarium");
var fur = db.MarineLife.First(f => f.Name == "Fur Seal");
//mb.MarineLife.Add(fur);
var tan = db.MarineLife.First(f => f.Name == "Tang");
//mb.MarineLife.Add(tan);
//Add MarineLife to Aquarium of Genoa
var ge = db.Aquariums.First(f => f.Name == "Aquarium of Genoa");
var pir = db.MarineLife.First(f => f.Name == "Piranha");
//ge.MarineLife.Add(pir);
//ge.MarineLife.Add(clo);
//Add MarineLife to New England Aquarium
var ne = db.Aquariums.First(f => f.Name == "New England Aquarium");
//ge.MarineLife.Add(pir);
//ge.MarineLife.Add(ang);
//ge.MarineLife.Add(eel);
//Add MarineLife to The Florida Aquarium
var fl = db.Aquariums.First(f => f.Name == "The Florida Aquarium");
var kil = db.MarineLife.First(f => f.Name == "Killer Whale");
//fl.MarineLife.Add(kil);
//fl.MarineLife.Add(tan);
//db.SaveChanges();
//Add MarineLife to Atlantic Ocean
var atl = db.Oceans.First(f => f.Name == "Atlantic Ocean");
//atl.MarineLife.Add(ang);
//atl.MarineLife.Add(clo);
//atl.MarineLife.Add(eel);
//Add MarineLife to Arctic Ocean
var arc = db.Oceans.First(f => f.Name == "Artic Ocean");
var pol = db.MarineLife.First(f => f.Name == "Polar Bear");
//arc.MarineLife.Add(fur);
//arc.MarineLife.Add(pol);
//arc.MarineLife.Add(kil);
//Add MarineLife to Indian Ocean
var ind = db.Oceans.First(f => f.Name == "Indian Ocean");
//ind.MarineLife.Add(clo);
//ind.MarineLife.Add(ang);
//Add MarineLife to Pacific Ocean
var pac = db.Oceans.First(f => f.Name == "Pacific Ocean");
//pac.MarineLife.Add(clo);
//pac.MarineLife.Add(ang);
//db.SaveChanges();
//Query that given an Aquarium Name, What AquaticLife is there
var marineLifeInFlAquarium = fl.MarineLife.ToList();
//Query given an Ocean, What Aquariums have fish from that ocean
//Query that Returns Only the Distinct Cities that have aquariums
//Query Display Count: How many spp live in each Ocean
//Query Dispaly Count:How many spp live in each Ocean, Order: Most to least
//given: Aquarium, What Oceans are represented in that aquarium.
}
static void Main(string[] args)
{
var db = new AquariumContext();
// SELECT * FROM Aquarium
var allAquariums = db.Aquariums.ToList();
// CREATE!
db.Aquariums.AddOrUpdate(
a => a.AquariumName,
new Aquarium
{
AquariumName = "Shedd Aquarium",
City = "Chicago"
},
new Aquarium
{
AquariumName = "Monterey Bay Aquarium",
City = "Monterey"
},
new Aquarium
{
AquariumName = "Audubon Aquarium of America",
City = "New Orleans"
},
new Aquarium
{
AquariumName = "National Aquarium",
City = "Baltimore"
});
// READ
var aquariums = db.Aquariums.ToList();
// CREATE OCEAN
db.Oceans.AddOrUpdate(
a => a.OceanName,
new Ocean
{
OceanName = "Pacific Ocean",
AverageTemp = 86
},
new Ocean
{
OceanName = "Atlantic Ocean",
AverageTemp = 57
},
new Ocean
{
OceanName = "Indian Ocean",
AverageTemp = 77
},
new Ocean
{
OceanName = "Artic Ocean",
AverageTemp = 31
});
// CREATE AQUATIC LIFE
db.AquaticLifes.AddOrUpdate(
a => a.AquaticLifeName,
new AquaticLife
{
Type = "Amphibian",
Color = "Black",
AquaticLifeName = "Poison Dart Frog",
IsInQuarantine = true
},
new AquaticLife
{
Type = "Reptiles",
Color = "Brown",
AquaticLifeName = "African Pancake Tortoise",
IsInQuarantine = true
},
new AquaticLife
{
Type = "Bony Fish",
Color = "Silver",
AquaticLifeName = "Banded Archerfish",
IsInQuarantine = false
});
//Data for OceanAquaticLife Table -so linking waht aquatic life is in each ocean
//var indian = db.Oceans.First(f => f.OceanName == "Indian Ocean");
//var pdf = db.AquaticLifes.First(f => f.AquaticLifeName == "Poison Dart Frog");
//indian.AquaticLife.Add(pdf);
//var atlantic = db.Oceans.First(f => f.OceanName == "Atlantic Ocean");
//var aft = db.AquaticLifes.First(f => f.AquaticLifeName == "African Pancake Tortoise");
//atlantic.AquaticLife.Add(aft);
//var arctic = db.Oceans.First(f => f.OceanName == "Indian Ocean");
//var ba = db.AquaticLifes.First(f => f.AquaticLifeName == "Banded Archerfish");
//arctic.AquaticLife.Add(ba);
//Data for AquaticLifeAquarium Table -so linking what aquatic life is in each aquarium
//var GeorgiaPDF = db.Aquariums.First(f => f.AquariumName == "Georgia Aquarium");
//var pdfGA = db.AquaticLifes.First(f => f.AquaticLifeName == "Poison Dart Frog");
//GeorgiaPDF.AquaticLife.Add(pdfGA);
//var GeorgiaAPT = db.Aquariums.First(f => f.AquariumName == "Georgia Aquarium");
//var aftGA = db.AquaticLifes.First(f => f.AquaticLifeName == "African Pancake Tortoise");
//GeorgiaAPT.AquaticLife.Add(aftGA);
//var GeorgiaBA = db.Aquariums.First(f => f.AquariumName == "Georgia Aquarium");
//var baGA = db.AquaticLifes.First(f => f.AquaticLifeName == "Banded Archerfish");
//GeorgiaBA.AquaticLife.Add(baGA);
//var Monterey = db.Aquariums.First(f => f.AquariumName == "Monterey Bay Aquarium");
//var aftCA = db.AquaticLifes.First(f => f.AquaticLifeName == "African Pancake Tortoise");
//Monterey.AquaticLife.Add(aftCA);
//var National = db.Aquariums.First(f => f.AquariumName == "National Aquarium");
//var baMD = db.AquaticLifes.First(f => f.AquaticLifeName == "Banded Archerfish");
//National.AquaticLife.Add(baMD);
//var NationalAFT = db.Aquariums.First(f => f.AquariumName == "National Aquarium");
//var aftMD = db.AquaticLifes.First(f => f.AquaticLifeName == "African Pancake Tortoise");
//NationalAFT.AquaticLife.Add(aftMD);
db.SaveChanges();
//A SQL Query that given an Aquarium Name, What AquaticLife is there
//You should have to do a DB.aquariums.first(f => f.Name = "Atlanta").Aquaticlife
//var GAAquaticLife = db.Aquariums.First(f => f.AquariumName == "Georgia Aquarium").AquaticLife;
//var GeorgiaAquaticLife = db.Aquariums.Include(i => i.AquaticLifeName).First(w => w.AquariumName == "Georgia Aquarium");
//foreach (var item in GAAquaticLife)
//{
// Console.WriteLine(item.AquaticLifeName);
//}
var qry = db.Aquariums.Where(Aqua => Aqua.AquariumName == "Georgia Aquarium")
.Select(Aqua => new { Aqua.Id, Aqua.AquariumName });
//var GAAquaticLife = db.Aquariums.Where(f => f.AquariumName == "Georgia Aquarium").Select(s => s.AquaticLife.Aqu;
//foreach (var item in GAAquaticLife)
//{
// Console.WriteLine(item.AquaticLifeName);
//}
//A SQL Query that, given an Ocean, What Aquariums have fish from that ocean
//var query = db.Aquariums.Join(db.AquaticLifes,
//aqName => aqName.AquariumName,
//aquaticLifeName => aquaticLifeName.AquaticLifeName,
//(aqName, aquaticLifeName) => new { AquariumName = aqName, AquaticLifeName = aquaticLifeName });
Console.WriteLine();
Console.WriteLine(qry);
}
