void InsertAttributes(QMySql s, string table, List <string> values, ref string sqlList)
{
foreach (string v in values)
{
sqlList = sqlList.AppendTo("'" + v + "'", ",");
s.Execute("insert ignore into " + table + " (name) values (@1)", v);
}
}
public void SavePlant()
{
string habitats = "";
string shades = "";
string soils = "";
string uses = "";
using (QMySql s = new QMySql())
{
InsertAttributes(s, "habitats", GetContentValues("habitat"), ref habitats);
InsertAttributes(s, "shadePreferences", GetContentValues("shadePreference"), ref shades);
InsertAttributes(s, "soilPreferences", GetSoilPreferences(Content("soilTolerance")), ref soils);
InsertAttributes(s, "uses", GetPlantUses(Content("specialUses")), ref uses);
int id = s.Execute(@"
insert into plants (habitatDescription, scientificName, description, specialUses, soilTolerance, matureHeight,
commonName,plantType) values
(@1, @2, @3,
@4, @5, @6,
@7,@8)
", Content("habDescription"), Content("scientificName"), Content("description"),
Content("specialUses"), Content("soilTolerance"), Content("matureHeight"),
Content("commonName"), Content("plantType"));
int minHeight = 0;
int maxHeight = 0;
if (GetHeights(Content("matureHeight"), ref minHeight, ref maxHeight))
{
s.Execute(@"
update plants
set matureHeightMin=@1,matureHeightMax=@2
where id=@3", minHeight, maxHeight, id);
}
if (habitats != "")
{
s.Execute(@"
insert ignore into plantHabitat (plant_id, habitat_id)
select @1 as plant_id, id from habitats where name in (" + habitats + ")", id);
}
if (shades != "")
{
s.Execute(@"
insert ignore into plantShadePreference (plant_id, shadePreference_id)
select @1 as plant_id, id from shadePreferences where name in (" + shades + ")", id);
}
if (soils != "")
{
s.Execute(@"
insert ignore into plantSoilPreference (plant_id, soilPreference_id)
select @1 as plant_id, id from soilPreferences where name in (" + soils + ")", id);
}
if (uses != "")
{
s.Execute(@"
insert ignore into plantUse (plant_id, use_id)
select @1 as plant_id, id from uses where name in (" + uses + ")", id);
}
int i = 0;
List <string> images = GetContentValues("image");
foreach (string u in images)
{
i++;
string fileName = u.Substring(u.LastIndexOf('/') + 1);
string filePath = @"C:\dev\GetPlants\GetPlants\images\" + fileName;
try
{
WebClient webClient = new WebClient();
webClient.DownloadFile(u, filePath);
s.Execute(@"
insert ignore into plantImages (plant_id, image, seq) values (@1, @2, @3)", id, fileName, i);
}
catch (Exception e)
{
}
}
}
}