how to write a update query for sqlite C#

Question

i have a task in which i need to change java code to uwp c# code i have no idea how to change this code in which i need to update the completed count and not completed count and pending count the for each employee

public void UpdateCountsinTraineeTable(long SiteId) {
String empQuery, countQuery;
Cursor cur_completed, cur_notcompleted, cur_pending;
SQLiteDatabase db = getReadDatabase();

    String whereClause = EMPLOYEE_SITEID + "=?";  
    String[] whereArgs = new String[]{  
            String.valueOf(SiteId)  
    };  

    empQuery = "SELECT " + EMPLOYEE_ID + ","  
            + EMPLOYEE_UNIQUEID + ","  
            + EMPLOYEE_FIRSTNAME + ","  
            + EMPLOYEE_LASTNAME + ","  
            + EMPLOYEE_DOB + ","  
            + EMPLOYEE_ISACTIVE + ","  
            + EMPLOYEE_TSTATUS + ","  
            + EMPLOYEE_IMAGEPATH + ","  
            + EMPLOYEE_DIRTYFLAG + ","  
            + EMPLOYEE_ACT_COUNT + ","  
            + EMPLOYEE_COMPLETED_COUNT + ","  
            + EMPLOYEE_NOTCOMPLETED_COUNT + ","  
            + EMPLOYEE_PENDING_COUNT + " FROM "  
            + TABLE_EMPLOYEE + " WHERE "  
            + EMPLOYEE_ID + " IN (SELECT "  
            + GRP_EMPLOYEE_USERID + " FROM "  
            + TABLE_GRP_EMPLOYEE + " WHERE "  
            + GRP_EMPLOYEE_SITEID + " =? and "  
            + GRP_EMPLOYEE_ISACTIVE + " ==1)";  

    /* Cursor cursor = db.query(TABLE_EMPLOYEE, new String[]{EMPLOYEE_ID,  
                    EMPLOYEE_UNIQUEID, EMPLOYEE_FIRSTNAME, EMPLOYEE_LASTNAME, EMPLOYEE_DOB, EMPLOYEE_ISACTIVE,  
                    EMPLOYEE_TSTATUS, EMPLOYEE_IMAGEPATH, EMPLOYEE_DIRTYFLAG,  
                    EMPLOYEE_ACT_COUNT, EMPLOYEE_COMPLETED_COUNT, EMPLOYEE_NOTCOMPLETED_COUNT, EMPLOYEE_PENDING_COUNT}, whereClause,  
            whereArgs, null, null, null, null);*/  
    Cursor cursor = db.rawQuery(empQuery, whereArgs);  

    countQuery = "SELECT " + TRAINING_PLAN_TRAINEEID + ", count(*)  from " + TABLE_TRAINING_PLAN + " WHERE "  
            + TRAINING_PLAN_TRAINEEID + " IN (SELECT "  
            + GRP_EMPLOYEE_USERID + " FROM " + TABLE_GRP_EMPLOYEE + " WHERE " + GRP_EMPLOYEE_SITEID + " =? and "  
            + GRP_EMPLOYEE_ISACTIVE + " ==1) and "  
            + TRAINING_PLAN_STATUS + " ==2 and  " + TRAINING_PLAN_ISACTIVE + " ==1 group by "  
            + TRAINING_PLAN_TRAINEEID;  
    //countQuery = "SELECT * FROM " + TABLE_TRAINING_PLAN + " WHERE " + TRAINING_PLAN_SITEID + " == '" + SiteId + "'";  
    cur_completed = db.rawQuery(countQuery, whereArgs);  


    countQuery = "SELECT " + TRAINING_PLAN_TRAINEEID + ", count(*)  from " + TABLE_TRAINING_PLAN + " WHERE "  
            + TRAINING_PLAN_TRAINEEID + " IN (SELECT "  
            + GRP_EMPLOYEE_USERID + " FROM " + TABLE_GRP_EMPLOYEE + " WHERE " + GRP_EMPLOYEE_SITEID + " =? and "  
            + GRP_EMPLOYEE_ISACTIVE + " ==1) and "  
            + TRAINING_PLAN_STATUS + " !=2 and " + TRAINING_PLAN_ISACTIVE + " ==1 group by "  
            + TRAINING_PLAN_TRAINEEID;  
    cur_notcompleted = db.rawQuery(countQuery, whereArgs);  


    countQuery = "SELECT " + TRAINING_PLAN_TRAINEEID + ", count(*)  from " + TABLE_TRAINING_PLAN + " WHERE "  
            + TRAINING_PLAN_TRAINEEID + " IN (SELECT "  
            + GRP_EMPLOYEE_USERID + " FROM " + TABLE_GRP_EMPLOYEE + " WHERE " + GRP_EMPLOYEE_SITEID + " =? and "  
            + GRP_EMPLOYEE_ISACTIVE + " ==1) and "  
            + TRAINING_PLAN_STATUS + " ==4 and " + TRAINING_PLAN_ISACTIVE + " ==1 group by "  
            + TRAINING_PLAN_TRAINEEID;  
    cur_pending = db.rawQuery(countQuery, whereArgs);  

    if (cursor.moveToFirst()) {  
        do {  
            int comp_count = 0, notcomp_count = 0, act_count = 0, pending_count = 0;  

            String Str_curtrainee = cursor.getString(0);  
            if (cur_completed.moveToFirst()) {  
                do {  

                    if (Str_curtrainee.equalsIgnoreCase(cur_completed.getString(0))) {  
                        comp_count = Integer.parseInt(cur_completed.getString(1));  
                        break;  
                    }  
                } while (cur_completed.moveToNext());  
            }  

            if (cur_notcompleted.moveToFirst()) {  
                do {  

                    if (Str_curtrainee.equalsIgnoreCase(cur_notcompleted.getString(0))) {  
                        notcomp_count = Integer.parseInt(cur_notcompleted.getString(1));  
                        break;  
                    }  
                } while (cur_notcompleted.moveToNext());  
            }  

            if (cur_pending.moveToFirst()) {  
                do {  

                    if (Str_curtrainee.equalsIgnoreCase(cur_pending.getString(0))) {  
                        pending_count = Integer.parseInt(cur_pending.getString(1));  
                        break;  
                    }  
                } while (cur_pending.moveToNext());  
            }  

            act_count = comp_count + notcomp_count;  

            ContentValues updateTrainee = new ContentValues();  
            updateTrainee.put(EMPLOYEE_ACT_COUNT, act_count);  
            updateTrainee.put(EMPLOYEE_COMPLETED_COUNT, comp_count);  
            updateTrainee.put(EMPLOYEE_NOTCOMPLETED_COUNT, notcomp_count);  
            updateTrainee.put(EMPLOYEE_PENDING_COUNT, pending_count);  

            db.update(TABLE_EMPLOYEE, updateTrainee, EMPLOYEE_ID + "=?",  
                    new String[]{Str_curtrainee});  
        } while (cursor.moveToNext());  
    }  


    cur_completed.close();  
    cur_pending.close();  
    cur_notcompleted.close();  
    cursor.close();  
    closeDatabase();  
}  

this is my reference code

Answer 1

it looks like the java lib use "?" and positional args, the sqllite driver uses formal parameter and $name. see:

https://learn.microsoft.com/en-us/dotnet/standard/data/sqlite/parameters

also the reader is fussy about null. you need to check the column is not dbnull, before accessing. or know it is not null.

Answer 2

Here is the complete documentation https://learn.microsoft.com/en-us/dotnet/standard/data/sqlite/?tabs=netcore-cli

using sdk