我需要到转换java sql查询到c#查询

q8l4jmvw 于 2022-12-21 发布在 Java

关注(0)|答案(1)|浏览(105)

我有一个java sql查询，我需要转换为c#查询是否有任何转换或任何人知道如何转换它
java查询

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);

我试过这个，但是不起作用

SqliteCommand COMPLETED = new SqliteCommand("SELECT TRAINING_PLAN_TRAINEEID,count(TRAINING_PLAN_TRAINEEID) from TABLE_TRAINING_PLAN " +
                                "WHERE TRAINING_PLAN_TRAINEEID IN(SELECT GRP_EMPLOYEE_USERID FROM TABLE_GRP_EMPLOYEE WHERE GRP_EMPLOYEE_SITEID=GRP_EMPLOYEE_SITEID and GRP_EMPLOYEE_ISACTIVE ==1) " +
                                "and TRAINING_PLAN_STATUS ==2 and TRAINING_PLAN_ISACTIVE ==1 group by TRAINING_PLAN_TRAINEEID", db);
                                        var cur_completed = COMPLETED.ExecuteReader();

完整代码为

using (SqliteConnection db =
                   new SqliteConnection($"Filename={dbpath}"))
                {
                    db.Open();

                    var command = db.CreateCommand();
                    command.CommandText = ("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 = GRP_EMPLOYEE_SITEID  and GRP_EMPLOYEE_ISACTIVE ==1)");
                    using (var empQuery = command.ExecuteReader())
                    {
                        while (empQuery.Read())
                        {

                            SqliteCommand COMPLETED = new SqliteCommand("SELECT TRAINING_PLAN_TRAINEEID,count(TRAINING_PLAN_TRAINEEID) from TABLE_TRAINING_PLAN " +
                                "WHERE TRAINING_PLAN_TRAINEEID IN (SELECT GRP_EMPLOYEE_USERID FROM TABLE_GRP_EMPLOYEE WHERE GRP_EMPLOYEE_SITEID=GRP_EMPLOYEE_SITEID and GRP_EMPLOYEE_ISACTIVE ==1) " +
                                "and TRAINING_PLAN_STATUS ==2 and TRAINING_PLAN_ISACTIVE ==1 group by TRAINING_PLAN_TRAINEEID", db);
                            var cur_completed = COMPLETED.ExecuteReader();

                            SqliteCommand notcompleted = new SqliteCommand("SELECT TRAINING_PLAN_TRAINEEID,COUNT(TRAINING_PLAN_TRAINEEID) from TABLE_TRAINING_PLAN " +
                        "WHERE TRAINING_PLAN_TRAINEEID IN (SELECT GRP_EMPLOYEE_USERID from TABLE_GRP_EMPLOYEE WHERE GRP_EMPLOYEE_SITEID=GRP_EMPLOYEE_SITEID and GRP_EMPLOYEE_ISACTIVE ==1) " +
                        "And TRAINING_PLAN_STATUS !=2 And TRAINING_PLAN_ISACTIVE ==1 group by TRAINING_PLAN_TRAINEEID", db);
                            var cur_notcompleted = notcompleted.ExecuteReader();

                            SqliteCommand pending = new SqliteCommand("SELECT TRAINING_PLAN_TRAINEEID,COUNT(TRAINING_PLAN_TRAINEEID) from TABLE_TRAINING_PLAN WHERE TRAINING_PLAN_TRAINEEID IN" +
                        "(SELECT GRP_EMPLOYEE_USERID FROM TABLE_GRP_EMPLOYEE WHERE GRP_EMPLOYEE_SITEID=GRP_EMPLOYEE_SITEID and GRP_EMPLOYEE_ISACTIVE == 1)" +
                        "and TRAINING_PLAN_STATUS == 4 and TRAINING_PLAN_ISACTIVE == 1 group by TRAINING_PLAN_TRAINEEID", db);
                            var cur_pending = pending.ExecuteReader();

Java

来源：https://stackoverflow.com/questions/74862412/i-need-to-convert-java-sql-query-to-c-sharp-query

1条答案

按热度按时间

yacmzcpb1#

string 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  =@siteId and 
                    GRP_EMPLOYEE_ISACTIVE =1) and 
                     TRAINING_PLAN_STATUS =2 and
  TRAINING_PLAN_ISACTIVE  =1 group by 
                    TRAINING_PLAN_TRAINEEID;"
      using (SqlCommand cmd = new SqlCommand(countQuery, conn))
       {
         cmd.Parameters.AddWithValue("@siteId", whereArgs[0]);
         using (SqlDataReader reader = cmd.ExecuteReader())
         {
             // process the results
         }
       }

赞(0）回复(0）举报 2022-12-21

我来回答

我需要到转换java sql查询到c#查询

1条答案

相关问题

热门标签

最新问答