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.
The AND operator allows the existence of multiple conditions in a SQLite statement's WHERE clause. While using AND operator, complete condition will be assumed true when all the conditions are true. For example, [condition1] AND [condition2] will be true only when both condition1 and condition2 are true.
The SQL AND & OR operators are used to combine multiple conditions to narrow data in an SQL statement. These two operators are called as the conjunctive operators.
There are databases interfaces which do not permit query stacking or executing multiple queries in a single function call. If you try to stack queries, the call fails but SQLite and PostgreSQL, happily perform stacked queries, executing all of the queries provided in one string and creating a serious security problem.
SQLite allows multiple processes to have the database file open at once, and for multiple processes to read the database at once. When any process wants to write, it must lock the entire database file for the duration of its update. But that normally only takes a few milliseconds.
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();
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With