public long ComputeScore(DataSet Ds_Exam) { DataTable Dt_Question = Ds_Exam.Tables[0]; DataTable Dt_Question_Answer = Ds_Exam.Tables[1]; StringBuilder Sb_Query = new StringBuilder(); Sb_Query.Append(@" Select * From RecruitmentTestQuestionAnswers "); Sb_Query.Append(@" Where "); Sb_Query.Append(@" Lkp_RecruitmentTestQuestionsID = @QuestionID "); Sb_Query.Append(@" And IsNull(IsAnswer,0) = 1 And IsNull(IsDeleted,0) = 0"); ClsPreparedQuery Pq = new ClsPreparedQuery(); Pq.pQuery = Sb_Query.ToString(); Pq.Add_Parameter("QuestionID", SqlDbType.BigInt); Pq.Prepare(); Int64 Score = 0; foreach (DataRow Dr in Dt_Question.Rows) { Pq.pParameters["QuestionID"].Value = Layer01_Methods.IsNull(Dr["RecruitmentTestQuestionsID"], 0); DataTable Inner_Dt = Pq.ExecuteQuery().Tables[0]; Int64 Ct_Answer = Inner_Dt.Rows.Count; Int64 Ct_ExamAnswer = 0; DataRow[] Arr_Dr_Answers = Dt_Question_Answer.Select(@"Lkp_RecruitmentTestQuestionsID = " + ((Int64)Dr["RecruitmentTestQuestionsID"]).ToString()); foreach (DataRow Inner_Dr in Arr_Dr_Answers) { bool IsExamAnswer = (bool)Layer01_Methods.IsNull(Inner_Dr["IsAnswered"], false); bool IsAnswer = false; DataRow[] Inner_Arr_Dr = Inner_Dt.Select(@"Lkp_RecruitmentTestAnswersID = " + ((Int64)Inner_Dr["Lkp_RecruitmentTestAnswersID"])); if (Inner_Arr_Dr.Length > 0) { IsAnswer = true; } if (IsAnswer && (IsExamAnswer == IsAnswer)) { Ct_ExamAnswer++; } } if (Ct_Answer == Ct_ExamAnswer) { Score++; } } return Score; }
public void AddSelected( DataTable Dt_Target , List<long> Selected_IDs , string Selected_DataSourceName , string Selected_KeyName , string Target_Key , bool HasTmpKey , List<Layer01_Constants.Str_AddSelectedFields> List_Selected_Fields = null , List<Layer01_Constants.Str_AddSelectedFieldsDefault> List_Selected_FieldsDefault = null) { if (Selected_IDs == null) { return; } if (Selected_IDs.Count == 0) { return; } ClsPreparedQuery Pq = new ClsPreparedQuery(); Pq.pQuery = @"Select * From " + Selected_DataSourceName + @" Where " + Selected_KeyName + @" = @ID"; Pq.Add_Parameter("ID", SqlDbType.BigInt); Pq.Prepare(); foreach (Int64 Selected_ID in Selected_IDs) { Pq.pParameters["ID"].Value = Selected_ID; DataTable Dt_Selected = Pq.ExecuteQuery().Tables[0]; if (Dt_Selected.Rows.Count > 0) { DataRow Dr_Selected = Dt_Selected.Rows[0]; DataRow[] ArrDr; DataRow Dr_Target = null; ArrDr = Dt_Target.Select(Target_Key + @" = " + Methods.Convert_Int64(Dr_Selected[Selected_KeyName])); if (ArrDr.Length > 0) { Dr_Target = ArrDr[0]; } else { Dr_Target = Dt_Target.NewRow(); Dt_Target.Rows.Add(Dr_Target); Dr_Target[Target_Key] = Dr_Selected[Selected_KeyName]; if (HasTmpKey) { Int64 Ct = 0; ArrDr = Dt_Target.Select("", "TmpKey Desc", DataViewRowState.CurrentRows); if (ArrDr.Length > 0) { Ct = Methods.Convert_Int64(ArrDr[0]["TmpKey"]); } Ct++; Dr_Target["TmpKey"] = Ct; Dr_Target["Item_Style"] = ""; } } if (List_Selected_Fields != null) { foreach (Layer01_Constants.Str_AddSelectedFields Selected_Field in List_Selected_Fields) { Dr_Target[Selected_Field.Field_Target] = Dr_Selected[Selected_Field.Field_Selected]; } } if (List_Selected_FieldsDefault != null) { foreach (Layer01_Constants.Str_AddSelectedFieldsDefault Selected_FieldDefault in List_Selected_FieldsDefault) { Dr_Target[Selected_FieldDefault.Field_Target] = Selected_FieldDefault.Value; } } } } }
public DataTable GetQuery(Interface_Connection Connection, string ViewObject, string Fields, ClsQueryCondition Condition, string Sort = "", long Top = 0, int Page = 0) { string Query_RowNumberSort = Sort; if (Query_RowNumberSort.Trim() == "") Query_RowNumberSort = "(Select 0)"; string Query_Top = ""; if (Top > 0) Query_Top = "Top " + Top.ToString(); Int64 PageCondition = 0; if (Page > 0) { PageCondition = Top * (Page - 1); } if (ViewObject.Trim() != "") ViewObject = " From " + ViewObject + " "; if (Fields.Trim() == "") Fields = " * "; if (Sort.Trim() != "") Sort = " Order By " + Sort; ClsPreparedQuery Pq = new ClsPreparedQuery((ClsConnection_SqlServer)Connection); Pq.Add_Parameter("ViewObject", SqlDbType.VarChar, 8000, 0, 0, ViewObject); Pq.Add_Parameter("Fields", SqlDbType.VarChar, 8000, 0, 0, Fields); Pq.Add_Parameter("Sort", SqlDbType.VarChar, 8000, 0, 0, Sort); string Query_Condition = ""; if (Condition != null) { Query_Condition = " Where 1 = 1 "; Query_Condition += " And " + Condition.GetQueryCondition(); Pq.Add_Parameter(Condition.GetParameters()); } Pq.pQuery = @"Select " + Query_Top + @" [Tb].* From ( Select Row_Number() Over (Order By " + Query_RowNumberSort + @") As [RowNumber], " + Fields + " " + ViewObject + " " + Query_Condition + @" ) As [Tb] Where [Tb].RowNumber > " + PageCondition + " " + Sort; Pq.Prepare(); return Pq.ExecuteQuery().Tables[0]; }
public DataTable GetQuery( Interface_Connection Connection , string ViewObject , string Fields = "" , string Condition = "" , string Sort = "") { if (ViewObject.Trim() != "") ViewObject = " From " + ViewObject + " "; if (Fields.Trim() == "") Fields = " * "; if (Condition.Trim() != "") Condition = " Where " + Condition; if (Sort.Trim() != "") Sort = " Order By " + Sort; ClsPreparedQuery Pq = new ClsPreparedQuery((ClsConnection_SqlServer)Connection); Pq.pQuery = @"Declare @Query As VarChar(Max); Set @Query = 'Select ' + @Fields + ' ' + @ViewObject + ' ' + @Condition + ' ' + @Sort; Exec(@Query)"; Pq.Add_Parameter("ViewObject", SqlDbType.VarChar, 8000, 0, 0, ViewObject); Pq.Add_Parameter("Fields", SqlDbType.VarChar, 8000, 0, 0, Fields); Pq.Add_Parameter("Condition", SqlDbType.VarChar, 8000, 0, 0, Condition); Pq.Add_Parameter("Sort", SqlDbType.VarChar, 8000, 0, 0, Sort); Pq.Prepare(); return Pq.ExecuteQuery().Tables[0]; }
public static DataTable GetQueryWithPage(ClsConnection_SqlServer Da, string ViewObject, string Fields, string Condition, string Sort, Int64 Top, Int32 Page) { string Query_RowNumberSort = Sort; if (Query_RowNumberSort.Trim() == "") Query_RowNumberSort = "(Select 0)"; string Query_Top = ""; if (Top > 0) Query_Top = "Top " + Top.ToString(); Int64 PageCondition = 0; if (Page > 0) { PageCondition = Top * (Page - 1); } if (ViewObject.Trim() != "") ViewObject = " From " + ViewObject + " "; if (Fields.Trim() == "") Fields = " * "; if (Condition.Trim() != "") Condition = " Where " + Condition; if (Sort.Trim() != "") Sort = " Order By " + Sort; ClsPreparedQuery Pq = new ClsPreparedQuery(Da); Pq.pQuery = @"Declare @Query As VarChar(Max); Set @Query = 'Select ' + @Top ' + [Tb].* From ( Select Row_Number() Over (Order By ' + @RowNumberSort + ') As [RowNumber], ' + @Fields + ' ' + @ViewObject + ' ' + @Condition + ' ' + @Sort + ' ) As [Tb] Where [Tb].RowNumber >= ' + @PageCondtion + ''; Exec(@Query)"; Pq.Add_Parameter("ViewObject", SqlDbType.VarChar, 8000, 0, 0, ViewObject); Pq.Add_Parameter("Top", SqlDbType.VarChar, 8000, 0, 0, Query_Top); Pq.Add_Parameter("RowNumberSort", SqlDbType.VarChar, 8000, 0, 0, Query_RowNumberSort); Pq.Add_Parameter("PageCondtion", SqlDbType.BigInt, 0, 0, 0, PageCondition); Pq.Add_Parameter("Fields", SqlDbType.VarChar, 8000, 0, 0, Fields); Pq.Add_Parameter("Condition", SqlDbType.VarChar, 8000, 0, 0, Condition); Pq.Add_Parameter("Sort", SqlDbType.VarChar, 8000, 0, 0, Sort); Pq.Prepare(); return Pq.ExecuteQuery().Tables[0]; }