public override void LoadStructure() { tables.Clear(); using (QMySql s = new QMySql()) { s.Open("select table_name, table_type from information_schema.tables where table_type in ('BASE TABLE','VIEW') and table_schema='" + databaseName + "'"); while (s.GetRow()) { DbTable t = new DbTable("", s[0], s[1]); tables.Add(t.name, t); using (QMySql sFields = new QMySql()) { sFields.Open("describe " + t.name); // List<string> tableFieldNames = new List<string>(); while (sFields.GetRow()) { DbColumn c = new DbColumn(t, sFields[0], sFields[1], sFields[3] == "PRI", sFields[2] == "YES", sFields[4]); t.columns.Add(c.name, c); tablesByColumnName.Add(c.name, t); } } } } }
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 Plant GetPlant(int id) { using (QMySql s = new QMySql()) { s.Open(@" select * from plants where id=@1", id); if (s.GetRow()) { Plant p = new Plant(); QObject.PopulateFromRow(s, p); p.habitats = QMySql.SqlList(@" select h.name from habitats h inner join plantHabitat p on p.habitat_id=h.id where p.plant_id=" + id).ToList(); p.uses = QMySql.SqlList(@" select h.name from uses h inner join plantUse p on p.use_id=h.id where p.plant_id=" + id).ToList(); p.soilPreferences = QMySql.SqlList(@" select h.name from soilPreferences h inner join plantSoilPreference p on p.soilPreference_id=h.id where p.plant_id=" + id).ToList(); p.shadePreferences = QMySql.SqlList(@" select h.name from shadePreferences h inner join plantShadePreference p on p.shadePreference_id=h.id where p.plant_id=" + id).ToList(); p.images = QMySql.SqlList(@" select p.image from plantImages p where p.plant_id=" + id + " order by p.seq").ToList(); p.heatMapData = GetHeatMapData(p.habitats); return(p); } } throw new ArgumentException(); }
public IEnumerable <PlantSummary> GetPlantSummaries(string sql, string typ) { List <PlantSummary> result = new List <PlantSummary>(); using (QMySql s = new QMySql()) { s.Open(sql, typ); while (s.GetRow()) { PlantSummary p = new PlantSummary(); QObject.PopulateFromRow(s, p); p.image = GetFirstImage(p.id); result.Add(p); } } return(result); }
static void Main(string[] args) { QMySql.Connect("192.168.0.105", "plants", "dev", "willys"); QMySql.Exec("delete from plantImages;delete from plantShadePreference;delete from plantSoilPreference;delete from plantHabitat;delete from plants"); Parse g = new Parse(); g.GetUrls(); // Console.ReadLine(); foreach (string u in g.urls.Keys) { g.ParsePlant(u); g.SavePlant(); Console.WriteLine(""); // Console.ReadLine(); } foreach (string s in g.soils) { Console.WriteLine(s); } Console.ReadLine(); }
public void Connect(string host, string name, string user, string password) { QMySql.ConnectString = "Server=" + host + ";Database=" + name + ";Uid=" + user + ";Pwd=" + password + ";Allow User Variables=True;"; QMySql.SqlInt("select count(*) FROM INFORMATION_SCHEMA.TABLES"); databaseName = name; }
public override int SqlInt(bool useRawValues, string sql, params object[] args) { return(QMySql.SqlInt(useRawValues, sql, args)); }
public override int SqlInt(string sql) { return(QMySql.SqlInt(sql)); }
public static void Main(string[] args) { QMySql.Connect("192.168.0.105", "plants", "dev", "willys"); BuildWebHost(args).Run(); }
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) { } } } }
public IEnumerable <string> GetShadePreferences() { return(QMySql.SqlList(@" select name from shadePreferences order by 1")); }
public IEnumerable <string> GetUses() { return(QMySql.SqlList(@" select name from uses order by 1")); }
public PlantAttributeList GetHabitats() { return(new PlantAttributeList(QMySql.SqlList(@" select name from habitats order by 1"))); }
public PlantAttributeList GetPlantTypes() { return(new PlantAttributeList(QMySql.SqlList(@" select distinct plantType from plants order by 1"))); }
public string GetFirstImage(int plantId) { return(QMySql.SqlString("select image from plantImages where plant_id=" + plantId + " order by seq")); }