How can I make a partial table update using OrmLite's UpdateOnly method?

Question

I am trying to update two fields on my table. I have tried several things, but the updates I have tried affect other fields. Here my code:

// Updates a row in the PatientSession table. Note that this call updates
public string UpdateEndPatientSession(PatientSession p)
{       
    // Update fields I set but reset data other fields
    _dbConnection.Update<PatientSession>(p);

    // Rename all fields of the column
    _dbConnection.UpdateOnly(new PatientSession { Id = p.Id, PatientEndSessionTime = p.PatientEndSessionTime, PatientEndSessionByUserId = p.PatientEndSessionByUserId }, (PatientSession patient) => new { patient.Id, patient.PatientEndSessionTime, patient.PatientEndSessionByUserId });
    _dbConnection.Update<PatientSession>(new { PatientSessionId = 10, PatientEndSessionTime = sessiontime, PatientEndSessionByUserId = 159 });
    string IdInsert = "{\"PatientSessionId\":" + p.Id + "}";
    return IdInsert;
}

I followed the instruction on the ServiceStack tutorial but I can't make it work. I don't understand either how you can update a row without selected the row with the primary Id.

I tried a basic SQL request but I get a SQLiteException:

string patientendsessiontime = p.PatientEndSessionTime.ToString();
string patientendsessionbyuserid = p.PatientEndSessionByUserId.ToString();
string patientsessionid = p.Id.ToString();

string SQLraw = "UPDATE PatientSession SET PatientEndSessionTime = " + patientendsessiontime + ", PatientEndSessionByUserId =  " + patientendsessionbyuserid + " WHERE PatientSessionId = " + patientsessionid + "";
_dbConnection.ExecuteSql(SQLraw);

enter image description here

I just want to make a partial update.

Scott · Accepted Answer · 2014-07-08T11:08:58.473

3

You need to specify a WHERE clause in the UpdateOnly statement.

public string UpdateEndPatientSession(PatientSession p)
{
    _dbConnection.UpdateOnly(
        p, 
        onlyFields: ps => new { ps.PatientEndSessionTime, ps.PatientEndSessionByUserId },
        where: ps => ps.Id == p.Id
    );
    ...

Syntax:

_dbConnection.UpdateOnly(
    p, // The source data object
    onlyFields: ps => ... , // The fields you want from the source data object
    where: ps => ... // The record match condition
);

See the update documentation for more examples.

As an aside, I see you are returning a string type and then building a JSON response:

string IdInsert = "{\"PatientSessionId\":" + p.Id + "}"

Did you know you can simply set the return as object and then do:

return new { PatientSessionId = p.Id };

Seems to me you don't want to be handling the serialising of JSON yourself.

edited Jul 08 '14 at 11:08

answered Jul 08 '14 at 10:57

Scott

21,211
8
65
72

Thank you very much @Scott to make my post understandable and for your example the update works perfectly! You right I tried to generate manually an JSON response, I tried your solution but it's not working. I tried this (return new PatientSession { PatientSessionId = p.Id };) but it return a json response with all fields null. Should I create a new post as "how generate an Json response with only the Primary Key"? – Ben Jul 09 '14 at 13:56
@BenmasterTraining If you are updating a record based on the primary key you are already providing, there isn't really any point returning it as the response. I.e. if you made the request to `/something/123` then returning `123` is redundant, you know it already. When I do an `Update` method, I choose to the return type of `void` and don't return anything. If an error occurs then an exception will be thrown and this will set an error status for the response, so a specific response to an update is not required. But if you want I'll look at your issue in another question. – Scott Jul 09 '14 at 14:08
My function Update and Add are linked, if the value of my primary Id is different than null I Update else I add a new record. And I need to return the Id generated. I work with a team and it's more convenient if I have only one URL to add or update. I will create a new post. Thanks @Scott – Ben Jul 09 '14 at 14:47
It's an answer for the question to serialize a JSON response with only the PK here: http://stackoverflow.com/a/24930443/3017741 – Ben Jul 24 '14 at 10:11

score 0 · Answer 2 · answered Jul 08 '14 at 10:21

OK I found a solution for the SQLite expression:

        string patientendsessiontime = p.PatientEndSessionTime.ToString();
        string patientendsessionbyuserid = p.PatientEndSessionByUserId.ToString();
        string patientsessionid = p.Id.ToString();

        string SQLraw = "UPDATE PatientSession SET PatientEndSessionTime = '" + patientendsessiontime + "', PatientEndSessionByUserId =  " + patientendsessionbyuserid + " WHERE Id = " + patientsessionid + "";

        _dbConnection.ExecuteSql(SQLraw);

I made mistakes with the syntax, but I still don't how do this with anonymous functions if someone have an other example than the doc I'm sure it's can't be super useful :)

How can I make a partial table update using OrmLite's UpdateOnly method?

2 Answers2