SQLite database query with multiple WHERE conditions
Assuming you are using SQLite-Net as your ORM, you can just pass in the parameters after the query. As far as I know there is no support for anonymous classes, as in your example. Try this:
var existingmaintenance = db.Query<maintenance>(
"select * from maintenance where VehID = ? AND MaintID = ?",
Maintenance.Vehicleid, Maintenance.Maintid).FirstOrDefault();
You can also use a linq query, like so:
var existingmaintenance = db.Table<maintenance>().Where
(c => c.VehID == Maintenance.Vehicleid &&
c.MaintID == Maintenance.Maintid).FirstOrDefault();
wheezer
Updated on August 02, 2022Comments
-
wheezer almost 2 years
Trying to develop a Windows 8.1 Store App. Amongst other difficulties, I need to retrieve records from a sqlite database with two parameters on the where clause. I can successfully query with one parameter in the where clause but it crashes everything when I try to use two parameters. Here is my code for this:
public string SaveMaint(MaintViewModel Maintenance) { string result = string.Empty; using (var db = new SQLite.SQLiteConnection(App.DBPath)) { string change = string.Empty; try { var existingmaintenance = db.Query<maintenance> ("select * from maintenance where VehID = ? AND MaintID = ?", new String[] {Maintenance.Maintid, Maintenance.Vehicleid}); // var existingmaintenance = (db.Table<maintenance>().Where // (c => c.MaintID == Maintenance.Maintid).SingleOrDefault()); if (existingmaintenance != null) { existingmaintenance.VehID = Maintenance.Vehicleid; existingmaintenance.MaintID = Maintenance.Maintid; existingmaintenance.ServiceDate = Maintenance.Servicedate; existingmaintenance.ServiceCost = Maintenance.Servicecost; existingmaintenance.ServiceLocation = Maintenance.Servicelocation; existingmaintenance.ServiceNote = Maintenance.Servicenote; existingmaintenance.ServiceOdom = Maintenance.Serviceodom; int success = db.Update(existingmaintenance); } else { int success = db.Insert(new maintenance() { VehID = Maintenance.Vehicleid, MaintID = Maintenance.Maintid, ServiceDate = Maintenance.Servicedate, ServiceCost = Maintenance.Servicecost, ServiceLocation = Maintenance.Servicelocation, ServiceNote = Maintenance.Servicenote, ServiceOdom = Maintenance.Serviceodom }); } result = "Success"; } catch { result = "This project was not saved."; } } return result; }
Please refer to the line in which
existingmaintenance
variable is defined. The commented out version of this line works fine. When I substitute the variable definition with the two parameter query (obtained using a different method because I couldn't figure out how to add a second parameter to the Table query approach), it crashes.Thanks for any help you can give. Sorry that I only half understand what I'm doing.
-
wheezer almost 10 yearsI am using sqlite-net. I thought I tried every possible way to do a query like your second suggestion and I failed. But yours worked like a charm! Thank you so much! I was not putting the "FirstOrDefault()" part on it. Can I do something to give you some points for this? I just clicked the check mark. I don't have enough hutspa to give your answer a vote up, apparently.
-
wheezer almost 10 yearsThanks for your suggestion, Ashish. Chue answer did the trick so I didn't need to try yours.
-
chue x almost 10 years@user3798080, accepting my answer gives me points. Upvoting also would give me points but as you say, you don't have enough "hutspa" yet.